excel做好一个考勤表的详细步骤（你会用Excel做考勤表）

现在公司都是用打卡机考勤，兰色一直想把打卡记录直接转换成考勤表，可手懒一直没有做。昨天遇到一同学提问，便试了试果然可以，而且功能很强大。

员工打卡信息表：

生成后的员工考勤表：

可以根据月份、部门进行筛选，而且可以用红色、绿色显示迟到、早退。

当然，也可以统计是迟到、早退、未打卡次数

制作步骤：

1、设置公式

设置公式，从日期中提取月份、天数和上午/下午。

E2: =MONTH(D2)

F2: =DAY(D2)

G2: =IF(HOUR(D2)<12,"上午","下午")

2、插入数据透视表

插入数据透视表把

• 姓名、上午/下午拖入行标签
• 日期拖入列标签
• 日期时间拖入值标签。

3、整理数据透视表

• 用表格样式显示
• 隐藏汇总行
• 删除总计行
• 把计数更改为最小值（重复打卡只算第1次）

再把小数更改时间样式并把下午行填充颜色、设置下边框。

5、设置条件格式

设置迟到(8:30后打卡)、早退（下午17：30前打卡）颜色自动填充：

选取上午行 - 开始 - 条件格式- 新建规则 - 使用公式.... - 输入公式：

=TEXT(b5,"h:m")-"8:30">0

公式说明：使用text提取时间和8：30对比

注意：设置后再打开条件格式管理时，B5会自动变为C5，这是透视表和一般表格设置条件格式不同的。

设置后，晚于8：30打开的记录都会自动填充红色。

再设置下午早退颜色

公式：

=AND(C6<>"",TEXT(C6,"h:m")-"17:30"<0)

设置后早于17：30的时间会自动填充绿色

6、设置统计公式

迟到次数（y5）

=IF(B5="","",SUMPRODUCT((TEXT(C5:X5,"h:m")-TIME(8,30,0)>0)*1))

未打开次数(z5)

=IF(B5="","",COUNTBLANK(C5:X6))

早退次数(AA5)

=IF(B5="","",SUMPRODUCT((TEXT(C6:X6,"h:m")-TIME(17,30,0)<0)*(C6:X6<>"")))

最后：不同的单位有不同的考勤制作，比如有的单位一天打6次卡，这时你只需修改源表G列公式，增加一时间区间。

作者：兰色幻想赵志东

,