考勤表模板excel教程（用Excel函数制作动态考勤表）

用Excel函数制作自动统计考勤表

一、考勤表框架设计

怎样用Excel函数及嵌套函数设计一个自动统计的考勤表，制作成一个方便使用通用模板，达到一劳永逸效果，是我们需要思考和解决的问题。

1、在首行写好标题“考勤表”，在第二行填写“年、月”类目，用数据有效性制作下拉菜单，方便自由选择年、月。如图：

2、实现每月天数自动显示。每月天数通常大月31天，小月30天；另外2月份平年28天，闰年29天。故日期1-28日每个月都要用到，直接输入；

单元格“31”日，大月出现，小月没有，公式为：

=IF(OR(C2=1,C2=3,C2=5,C2=7,C2=8,C2=10,C2=12),31,"")

单元格“30”日，除2月份其他月份显示，公式为：

=IF(OR(C2<>2),30,"")

单元格“29”日，闰年（能被4整除而不能被100整除或能被400整除）显示，公式为：

=IF(OR(AND(MOD(B2,4)=0,MOD(B2,100)<>0),MOD(B2,400)=0,C2<>2),29,"")

星期判断公式：=TEXT($B$2&-$C$2&-Q$5,"aaa")，即用TEXT公式根据日期判断星期。

二、根据考勤标识符号统计各类出勤

1、定义考勤标识符号。如记录符号：探亲年休假~年、病假~病、产假~产、婚假~婚、丧假~丧、事假~事、护理假~护、出差~差、工伤~伤、旷工~0、补休~补、迟到~迟、早退~退、脱岗~脱、早班~早、中班~中、晚班~晚，通、白班~/、工休~Δ等。也可以用特殊符号标识，结合实际情况而定。

2、单项考勤标识符用COUNTIF统计，如：=COUNTIF($Q6:$AU6,"/")，$Q6:$AU6为1-31日统计区域，“/”为白班标识。

3、多个考勤标识符用SUM嵌套COUNTIF实现统计，如：

=SUM(COUNTIF($Q6:$AU6,{"迟","退","脱"}))，即把迟到、早退、脱岗按一类统计合计。

三、完整考勤表如下：

四、最后，设置“显示”、“隐藏”29、30、31日期对应行。如对“31”单元格填充为白色，因31字颜色为白色，即可达到隐藏目的。设置：AU5-条件格式-新建格式规则-使用公式确定要设置的单元格-在条件设置写入公式“=AU5="",填充为白色。