扫码加入数据分析学习群

1.4 动态考勤表的制作

相关函数：

（1）填写日期：=IF(H$24<DAY(EOMONTH(DATE($I$22,$K$22,1),0)),H$24+1,"")

（2）填写星期：

方法一：=(IF(H$24="","",

IF(WEEKDAY(DATE($I$22,$K$22,H$24),2)=1,"一",

IF(WEEKDAY(DATE($I$22,$K$22,H$24),2)=2,"二",

IF(WEEKDAY(DATE($I$22,$K$22,H$24),2)=3,"三",

IF(WEEKDAY(DATE($I$22,$K$22,H$24),2)=4,"四",

IF(WEEKDAY(DATE($I$22,$K$22,H$24),2)=5,"五",

IF(WEEKDAY(DATE($I$22,$K$22,H$24),2)=6,"六","日"))))))))

方法二：=IF(H$24="","",WEEKDAY(DATE($I$22,$K$22,H$24),1))

（单元格自定义：aaa类型，此类型会把1转换为日、2转换一、……7转换为六）

方法三：=CHOOSE(IF(H$24="","8",WEEKDAY(DATE($I$22,$K$22,H$24),1)),"日","一","二","三","四","五","六","")

（3）相同的方式制作动态甘特图：达到的效果是打开Excel，第一个单元格显示的是当天，然后以此往后30天的一张二维表格

相关函数：

（a）第一个单元格：=TODAY()

（单元格自定义：dd类型，此类型会把yyyy-m-d的日期形式数据转换为只显示dd的显示形式）

（b）第二个单元格开始：依次+1

（c）填写星期：=WEEKDAY(日期单元格地址,1)

（单元格自定义：aaa类型，此类型会把1转换为日、2转换一、……7转换为六）

1.5 考勤表的统计

（1）出勤天数：=COUNTIFS($H$3:$AL$3,">1",$H$3:$AL$3,"<7",H5:AL5,">=8")

（2）病假天数：=COUNTIF(H5:AL5,"病")

（3）事假天数：=COUNTIF(H5:AL5,"事")

（4）平时加班时长：=SUMIFS(H5:AL5,$H$3:$AL$3,">1",$H$3:$AL$3,"<7",H5:AL5,">=8")-B5*8

（5）周末加班时长：=SUMIF($H$3:$AL$3,1,H5:AL5)+SUMIF($H$3:$AL$3,7,H5:AL5)

（6）合并11&12月考勤汇总（类似：当x和y的内容、数量均一致的情况下，顺序不一致不影响）

方法一：用vlookup将两张表的数据进行SUM运算

方法二：使用“数据”选项卡下的“合并计算”功能（更为便捷）

（a）点击“数据”选项卡下的“合并计算”；

（b）函数：求和（还有其他函数可选）；

（c）引用位置：点击小箭头，选取范围，然后添加到所有引用位置，依次将需要合并的数据区域全部添加完毕；

（d）根据合并前表格格式，是否含有标题及列名，如有则需要将“首行”及“最左列”的√需要打上；

（e）点击确定即可。