![识别首次登录和最后一次注销的条目](https://linux22.com/image/1565636/%E8%AF%86%E5%88%AB%E9%A6%96%E6%AC%A1%E7%99%BB%E5%BD%95%E5%92%8C%E6%9C%80%E5%90%8E%E4%B8%80%E6%AC%A1%E6%B3%A8%E9%94%80%E7%9A%84%E6%9D%A1%E7%9B%AE.png)
我有一个电子表格,需要确定用户首次登录系统的时间和最后一次注销的时间。
登录和注销报告来自另一个系统,我将数据从该系统复制并粘贴到我的电子表格中。此报告未按时间顺序排序。
基本上,由于用户使用系统登录和注销,因此他们每天都会有多条登录和注销记录。我需要找到每个用户的第一个登录记录和最后一个注销记录。
我尝试使用INDEX()
和MATCH()
来执行此操作,但我只能返回每个用户的第一个条目。正如我之前提到的,提取的报告不是按时间顺序排序的。
答案1
该解决方案需要使用两个数组公式:
数组中输入(Ctrl++ )以下公式并将Shift其复制粘贴/填充到表格列的其余部分(不要忘记删除 and ):EnterF2
{
}
{=MIN(IF($A$2:$A$15=$E2,$B$2:$B$15,MAX($B$2:$B$15)))}
数组中输入(Ctrl++ )以下公式并将Shift其复制粘贴/填充到表格列的其余部分(不要忘记删除 and ):EnterG2
{
}
{=MAX(IF($A$2:$A$15=$E2,$C$2:$C$15,MIN($C$2:$C$15)))}
这两个公式的工作原理是,当某行与相应用户匹配时,生成一个相关时间数组,当某行与相应用户不匹配时,生成一个特殊值。诀窍是选择一个特殊值,使其不会干扰用于提取“首次登录”和“上次注销”的=MIN(…)
和函数。=MAX(…)
这些特殊值是该案例所有登录时间的最大值MIN
,以及该案例所有注销时间的最小值MAX
。
当然,你也可以使用任意大的MIN
数字
{=MIN(IF($A$2:$A$15=$E2,$B$2:$B$15,10^9))}
为零MAX
{=MAX(IF($A$2:$A$15=$E2,$C$2:$C$15,0))}
编辑:
如果报告中的任何数据(User
、Login
或Logout
)缺失,则需要调整公式。最简单的方法是修改上面的最后两个公式。
公式F2
变为
{=IFERROR(1/(1/(1/(1/MIN(IF($A$2:$A$15=$E2,$B$2:$B$15,10^9)))-1/(1/10^9)))+10^9,"")}
公式G2
变为
{=IFERROR(1/(1/MAX(IF($A$2:$A$15=$E2,$C$2:$C$15,0))),"")}
要了解这些修改如何起作用,最好从第二个公式开始。
将1/(1/MAX(…))
零结果转换为#DIV/0!
错误,但保留所有其他值*。如果指定用户没有登录时间,或者该用户根本没有记录,则会出现零结果。
然后IFERROR()
将错误转换为空白。(请注意,如果需要,您可以用任何字符串替换空白。)
第一个公式稍微复杂一些,因为当不存在匹配的记录时,10^9
将返回结果而不是零。(不过,如果没有注销时间,仍然会返回零。)
因此,10^9
需要转换为空白以及零转换。第二次转换是通过10^9
从第一个零到错误的转换结果中减去,如果结果为零,则将其转换为错误来完成的。然后通过添加 来恢复初始结果10^9
。当然,如果部分结果是错误,则保留错误。
需要用-1/(1/10^9)
而不是-10^9
来解决浮点错误。
然后IFERROR()
将错误转换为空白,就像在第二个公式中一样。