使用INDEX / MATCH返回值和逻辑测试

I've got a table with unique values displayed in rows. I need to return the value the correct first login time for the day

我有一个表格,行中显示唯一值。我需要返回值为当天正确的首次登录时间

     A        B         C            D
1    Session  User      Date         Time
2    1000     U1        3/6/2017     10:01
3    1234     U1        3/6/2017     12:00

As you can see, it's possible for one agent to log in twice for the day, and I only need the first time.

正如您所看到的,一天有一个代理可以登录两次,我只需要第一次。

I figured I could use INDEX and MATCH with multiple criteria for the User and the Date, but how do I include a logical test to check the session with the lower number (later sessions are always with a higher session ID number)?

我想我可以使用INDEX和MATCH为用户和日期使用多个条件,但是如何使用逻辑测试来检查具有较低编号的会话(以后的会话总是具有更高的会话ID编号)?

My formula so far looks something like this:

到目前为止,我的公式看起来像这样:

=INDEX(A:D,MATCH("U1"&"3/6/2017",B:B&C:C,0),D:D)

I'm not actually using U1 and the date directly, but with cell references, so the syntax error is not a problem.

我实际上并没有直接使用U1和日期,而是使用单元格引用,因此语法错误不是问题。

Also, I know this is an array formula, so it's confirmed with Ctrl+Shift+Enter.

另外,我知道这是一个数组公式,所以用Ctrl + Shift + Enter确认。

However, I don't know how to continue from here and say check U1 in B:B and Date in C:C, but return only D;D for the lowest Session in A:A

但是,我不知道如何继续从这里继续说并检查B:B中的U1和C:C中的日期,但只返回A; A中最低会话的D; D

5 个解决方案

#1

You can include an extra helper column IsFisrtLogin with formula like this:

您可以包含一个额外的帮助列IsFisrtLogin,其公式如下:

=COUNTIFS($B:$B,$B2,$C:$C,$C2,$A:$A,"<"&$A2)=0

Then filter out only the rows where this formula evaluates to TRUE and you will get only the first logins per corresponding user and day (i.e. the lowest session number).

然后仅过滤掉此公式计算结果为TRUE的行,并且每个相应的用户和日期只会获得第一次登录(即最低会话数)。

#2

Your instinct to use INDEX and MATCH was right. If they are in time order there's no need to find a minimum anything, just find the first occurrence - which is exactly what MATCH does.

你使用INDEX和MATCH的直觉是正确的。如果它们按时间顺序排列,则无需找到最小值,只需找到第一个出现的 - 这正是MATCH的作用。

=INDEX($B$2:$B$3,MATCH(G2&H2,$C$2:$C$3&$D$2:$D$3,0))

Entered as an array formula (Ctrl+Shift+Enter rather than just Enter)

输入为数组公式(Ctrl + Shift + Enter而不是Enter)

The result looks like this (see column I)

结果如下(见第一栏)

#3

Enter this as an array formula (confirmed with Ctrl+Shift+Enter) :

输入此数组公式(使用Ctrl + Shift + Enter确认):

=INDEX(A:D,
    MATCH(
        MIN(INDEX(A:D,MATCH("U1"&"3/6/2017",B:B&C:C,0))&"U1"&"3/6/2017"
        ,D:D&B:B&C:C,0)
        ,D:D)

In fact it's just getting the minimum of your initial function and matching it with the user and the day, to get your desired result! ;)

事实上,它只是获得初始功能的最小值,并将其与用户和当天相匹配,以获得您想要的结果! ;)

#4

As an alternative to the formulas, you can create a PivotTable that automatically shows you the Session number and Time of the first login for each user each day, regardless of the order of the data:

作为公式的替代方法,您可以创建一个数据透视表,无论数据的顺序如何,每天都会自动显示每个用户首次登录的会话编号和时间:

Insert PivotTable
Add User, Date and Sessions to Row Labels (You can swap User and Date, but Sessions must be last)

将用户,日期和会话添加到行标签(您可以交换用户和日期,但会话必须是最后的)

Add Time to Values

添加时间到值

Summarize Time by Min (Sum/Max actually have same result if Sessions are unique, because there's only 1 value per Session. Min just sounds nicer here.)

通过Min汇总时间(如果Sessions是唯一的,则Sum / Max实际上具有相同的结果,因为每个Session只有1个值.Min在这里听起来更好。)

Change Number format of Min of Time field to Time (Right click on Field, Value Field Settings, Number Format, Time)

将最小时间字段的数字格式更改为时间(右键单击字段,值字段设置,数字格式,时间)

Change Report Layout to Tabular (On PivotTable Tools > Design tab)

将报表布局更改为表格(在数据透视表工具>设计选项卡上)

Change Report Layout to Repeat all Item Labels

更改报告布局以重复所有项目标签

Remove Subtotals
Remove Grand Totals

删除Grand Totals

Right click on Session field and select Filter > Top 10...

右键单击Session字段,然后选择Filter> Top 10 ...

Set to: Show Bottom 1 Items by Min of Time (This causes only the first Session to be displayed for each User/Date combination based on the earliest Time)

设置为:按时间最短显示底部1项(这将导致仅根据最早时间为每个用户/日期组合显示第一个会话)

#5

Try,

=INDEX(D:D, AGGREGATE(15, 6, ROW($1:$99)/((B$1:B$99="U1")*(C$1:C$99=DATE(2017, 3, 6))*(A$1:A$99=AGGREGATE(15, 6, (A$1:A$99)/((B$1:B$99="U1")*(C$1:C$99=DATE(2017, 3, 6))), 1))), 1))

This retrieves the lowest numerical session number for the supplied date then uses that in a similar wrapping formula to retrieve the time associated with the session, the user and the date. Remember to format the result as time; it will be originally retrieved as a raw number.

这将检索所提供日期的最低数字会话编号,然后在类似的包装公式中使用该编号来检索与会话,用户和日期相关联的时间。记得将结果格式化为时间;它最初将作为原始数字检索。

The "U1" and DATE(2017, 3, 6) could refer to a cell(s) containing the value. For the second, third, etc match, replace the , 1) (the k of AGGREGATE's SMALL subfunction) with the appropriate ordinal.

“U1”和DATE(2017,3,6)可以指代包含该值的单元格。对于第二,第三等匹配,用适当的序数替换1)(AGGREGATE的SMALL子函数的k)。

#1