打卡数据快速整理，3分钟搞定你3小时工作！

本文作者：农夫

本文审核：小爽

本文编辑：竺兰

PS：文末留言送配套练习文件~

小伙伴们，大家好，我是农夫，专治疑难杂「数」的农夫~

我想很多从事行政工作的小伙伴，可能经常遇到这样的问题：

 

总有很多人一天内打卡很多次，每到月底核算考勤数据，财务面对员工多次打卡的数据时，怎么看都有点头疼。

如何将这些数据规整好呢？也就是将下图中，左边的长数据，处理成右边的规范数据？

其实，在 Excel 中，用数据透视表，3 步就能搞定！

❶ 数据检查

❷ 建立辅助列

❸ 插入数据透视表

数据检查

我们使用函数方法或者数透方法一定要注意两个前提条件：

❶ 数据去重；

❷ 时间升序排序。

因此，我们导出来的考勤数据，先要看下是否存在重复值、时间数据列是否升序排列。

如果存在重复值，则需要使用【数据】选项卡下的【删除重复值】进行去重处理；

如不是按升序排列，则需要使用【数据】选项卡下的【排序】进行组内升序处理~

建立辅助列

数据准备完毕后，在 H 列建立辅助列「打卡列」，根据每日打卡次数升序编号。

你以为这是手动数出来的吗？哪能呀，可别忘了 Excel 是个技术活哦！

通过 COUNTIFS 多条件计数公式，可以轻松实现自动编号！

打卡列输入公式：

=COUNTIFS($E$2:$E2,E2,$F$2:$F2,F2)

COUNTIFS 多条件计数公式，就是对区域中同时满足多个条件的单元格，进行计数。

因此，当我们固定住第一行单元格时，随着区域不数的扩大，COUNTIFS 函数就会计算区域中符合条件（如员工刘备和 7 月 1 日两个条件同时满足）的数据出现的次数。

即员工当日在该时间点第几次打卡数（如刘备在 7 月 1 日 9:00 是一天中的第二次打卡）~

COUNTIFS 函数的写法是：

=COUNTIFS（条件区域 1，条件 1，条件区域 2，条件 2）

▲ 左右滑动查看

条件区域 1：第一个条件的区域范围：如姓名列；

条件 1：符合条件 1 的参数：如姓名列中的刘备；

条件区域 2：第二个条件的区域范围：如日期列；

条件 2：符合条件 2 的参数：如日期列中的 7 月 1 日。

多个条件之间是且（AND）的关系，即只有两个条件都满足的时候才为真（TRUE)~

其中，$E$2:$E2 和$F$2:$F2 的混合引用，实现了随着光标往下拉动，不断扩大区域的多条件计数的范围。

而当我们计算出数值的次数后，如何展示第 X 次这种标准的格式？难道要手动更改第 X 次？

No No No！两种方法轻松解决！

▋第一种：公式法

通过在公式前后添加「第」和「次」，并用&连接即可。

='第'&COUNTIFS($E$2:$E2,E2,$F$2:$F2,F2)&'次'

▲ 左右滑动查看

但是，要注意这样得到的结果是字符型哦~

▋第二种：自定义格式

只需选中整列，点击数字格式的「自定义格式」，选择第二个 0，在前面输入「第」，后面输入「次」，确定即可。

（0 其实在自定义格式中，为 Excel 中的占位符。）

这样，我们获得的结果则是数值型。

下面，我们用动图展示下：

插入数据透视表

选中所有数据，建立数据透视表。

❶ 打卡列放入「列」中，姓名和日期放入「行」中，时间放入「值」中。

这时小伙伴的数据透视表可能是这个样子的~

这是因为数据透视表的默认的计算方式是对时间进行【计数】，所以，我们需要将计算方式更改为【求和】。

而更改后我们发现，表格中的怎么都是小数形式呢？

答案就是数据的呈现方式出了问题，只需将其格式改为【时间】格式即可。

于是就形成了如下图的数据：

❷ 选中数据透视后的表格，选择【设计】选项卡——【报表布局】——选择【以表格形式显示】——再选择【重复所有项目标签】；

这样，数据基本就处理好了，只是多了「行列总计」项和姓名「汇总」。

 

❸ 去掉「行列总计」这个就更简单了，选择【设计】选项卡——【总计】——【对行和列禁用】即可！

 

❹ 对于姓名「汇总」情况，只需选择【设计】选项卡下的【分类汇总】——【不显示分类汇总】就行。

步骤太多记不住？动图呈上~

总结一下

整理多次打卡的考勤数据，用数据透视表，只需 2 步就解决了，是不是很简单！