原来带有法定节假日的动态考勤表也很简单

一、高亮显示星期和法定节假日

• 选中D4：AH5区域点击开始菜单下的条件格式——新建规则——使用公式确定单元格的规则输入公式=OR(D$5='六',D$5='日')

• 法定假日格式输入公式=SUMPRODUCT((D$4>=数据源!$K$6:$K$12)*(D$4<=数据源!$L$6:$L$12))

• 点击格式——填充——选择一个颜色确定即可

二、确定考勤项目

• 1、出勤统计：点击数据——数据验证——允许下拉序列——在来源处输入考勤项目即可——点击'输入信息'——输入自己想提醒的内容

• 2、说明行：按照截图中的类型输入，后面所带的数字代表小时数或者天数，更加清晰显示实际出勤情况，比如迟到了1个小时就是C1，休年假就是N1，出勤就是输入√，法定节假日加班2小时就是G2，平时加班3小时就是P3等等

三、统计各个员工出勤加班情况

• 1、在AI6中输入公式 =SUMPRODUCT(-(0&REPLACE($D6:$AH6,1,1,'')),-ISNUMBER(FIND(AI$5,$D6:$AH6,1)))

即统计各个单元格中包含考勤项目中后所带数字之和

• 2、周末出勤天数：在AR6中输入公式=SUM(COUNTIFS($D$5:$AH$5,{'六','日'},D7:AH7,'√'))-AS6

注意：这里的周末，不包括法定节假日中的周六/日

• 3、法定假日出勤：在AO6中输入公式=SUMPRODUCT(($D$4:$AH$4>=数据源!$K$6:$K$12)*($D$4:$AH$4<=数据源!$L$6:$L$12)*(D7:AH7<>''))

即是考勤表中带有红色标记的区域内不为空值的个数！

四、统计每个月正常工作日（可跟随控件下年月变化）

• ①去除周末：在AO3中输入公式

=SUMPRODUCT(--(MOD(ROW(INDIRECT(DATE(C2,F2,1)&':'&DATE(C2,F2+1,0))),7)>1))

• ②去除周末和自定义国假，在AO2中输入公式

=VLOOKUP($F$2,自定义节假日!$A$2:$C$13,3,)

其他月份的考勤表只需要复制一份并链接主页就OK啦

五、制作动态图表

• 1、定义名称：选中考勤区域——公式——名称管理器——定义名称任意输入一个名字data——作为vlookup函数的第二参数：查找区域

• 2、在AY10中输入公式=VLOOKUP($AY$7,data,ROW(AI35),0)下拉填充即可

• 3、选中区域点击插入——选择一个图表类型——点击确定

• 4、选中图表标题——在编辑栏中输入=N3回车即可引用动态图表，只要控件下的年月份变化的时候，图表也会随之变化！