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

2023-04-05 01:10:10 文档大全网 [ 字体：小中大 ] [ 阅读： ]

【#文档大全网# 导语】以下是®文档大全网的小编为您整理的《用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="",填充为白色。

至此考勤表设计完成，用此表可以实现选择年月自动统计考勤。