中如何提取公式_Excel如何用函数公式提取每天第一次和最后一次打卡时间？你会吗...

1

上一期跟大家分享过用数据透视表的方法来提取考勤表中每天第一次和最后一次的打卡时间，这一期我教大家用函数公式的方法来实现！

下图中，A:B两列数据就是自己从后台导出的打卡记录，我们需要将这2列数据提取成D:F中所示的单元格区域。

具体操作步骤如下：

一、提取打卡日期。

在D2单元格中输入公式：=LOOKUP(,0/FREQUENCY(0,ISNA(MATCH(A$2:A$81,D$1:D1,))-1),A$2:A3)，按回车键回车并将公式下拉填充至D22单元格即可。

公式解析：

①MATCH(A$2:A$81,D$1:D1,)：

MATCH函数：返回指定数值在指定区域中的位置。

该公式中：第一个参数A$2:A$81表示要查找的值，第二个参数D$1:D1表示要查找的区域，第三个参数省略，默认会查找小于或等于要查找值的最大值，该公式的意思是：A列的打卡日期在D列的打卡日期中首次出现的位置，如果有，就返回当前单元格所在的行号，否则返回错误值#N/A。

②ISNA(MATCH(A$2:A$81,D$1:D1,))-1：

ISNA函数：用于判断值是否为错误值#N/A，如果是，返回TRUE，否则，返回FALSE。在逻辑运算中，TRUE等于1，FALSE等于0，所以该公式后面减去1是把公式的结果转成以0和1显示的数组。

③FREQUENCY(0,ISNA(MATCH(A$2:A$81,D$1:D1,))-1)：

FREQUENCY函数：表示计算值在某个区域内出现的频率，然后返回一个垂直数组。第一个参数0表示要对其频率进行计数的一个数值，第二个参数由第②步可知，返回的是一组由0和1组成的数组，该公式的意识是：计算0在由0和1组成的数组中出现的频率。

④LOOKUP(,0/FREQUENCY(0,ISNA(MATCH(A$2:A$81,D$1:D1,))-1),A$2:A3)：

LOOKUP函数：LOOKUP函数有两种用法：向量形式和数组形式，这里用的是向量形式。该公式中，第2个参数是由1和0组成的数组，用0来除以这组数组，当分母为0的时候，计算结果是错误值#DIV/0!，所以返回的是一组由0和错误值#DIV/0!组成的数组，第一个参数省略，默认是0，表示在由0和#DIV/0!组成的数组中查找小于或等于0的值，最后根据0的位置得到第三个参数对应位置的数据。

二、提取第一次打卡时间。

在E2单元格中输入公式：=SMALL(IF($A$2:$A$81=D2,$B$2:$B$81,9^9),1)，按“Ctrl+Shift+Enter”三键结束数组公式的输入并将公式下拉填充至E22单元格即可。

公式解析：

①IF($A$2:$A$81=D2,$B$2:$B$81,9^9)：

用IF函数来判断$A$2:$A$81的日期是否与D2单元格日期相等，如果相等，返回$B$2:$B$81对应单元格的值，否则返回一个很大的数据。

②SMALL(IF($A$2:$A$81=D2,$B$2:$B$81,9^9),1)：

因为“打卡日期”同一天的数据有多个，因此我们用IF函数求得的结果也有多个数据，此时我们用SMALL函数取出第1个小的值，也就是第一次打卡时间。

三、提取最后一次打卡时间。

在F2单元格中输入公式：=MAX(IF($A$2:$A$81=D2,$B$2:$B$81,0))，按“Ctrl+Shift+Enter”三键结束数组公式的输入并将公式下拉填充至F22单元格即可。

公式解析：

①IF($A$2:$A$81=D2,$B$2:$B$81,0)：

用IF函数来判断$A$2:$A$81的日期是否与D2单元格日期相等，如果相等，返回$B$2:$B$81对应单元格的值，否则返回0。

②MAX(IF($A$2:$A$81=D2,$B$2:$B$81,0))：

因为“打卡日期”同一天的数据有多个，因此我们用IF函数求得的结果也有多个数据，此时我们用MAX函数可以在一组数据中取出最大的值，也就是最后一次打卡时间。