用Excel函数制作自动统计考勤表
1、考勤表框架设计
怎么样用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公式按照日期判断星期。
2、按照考勤标识符号统计各类出勤
1、定义考勤标识符号。如记录符号:探亲年休假~年、病假~病、产假~产、婚假~婚、丧假~丧、事假~事、护理假~护、出差~差、工伤~伤、旷工~0、补休~补、迟到~迟、早退~退、脱岗~脱、早班~早、中班~中、晚班~晚,通、白班~/、工休~Δ等。亦能够用特殊符号标识,结合实质状况而定。
2、单项考勤标识符用COUNTIF统计,如:=COUNTIF($Q6AU6,"/"),$Q6AU6为1-31日统计区域,“/”为白班标识。
3、多个考勤标识符用SUM嵌套COUNTIF实现统计,如:
=SUM(COUNTIF($Q6AU6,{"迟","退","脱"})),即把迟到、早退、脱岗按一类统计合计。
3、完整考勤表如下:
4、最后,设置“表示”、“隐匿”29、30、31日期对应行。如对“31”单元格填充为白色,因31字颜色为白色,即达到到隐匿目的。设置:AU5-要求格式-新建格式规则-运用公式确定要设置的单元格-在要求设置写入公式“=AU5="",填充为白色。
至此考勤表设计完成,能够实现用Excel函数自动统计指定年月考勤。
|