7.Excel数据分析-员工考勤表

文章目录1.制定年2.制定月3.制定日4.制定星期5.颜色设置6.出勤率7.病8.事9.平时加班10.周末加班制作一个员工考勤表包含年月日星期的所有内容如图所示1.制定年先确定年这里以20192020,2021三年为例选择单元格–数据–数据验证序列–来源–2019,2020,2021显示结果如下2.制定月也是同样的道理效果如下3.制定日每年的每个月多少填都是变化的如何制作来确定每个月有多少天呢这里的话就需要用到我们之前学到的内容了不知道大家是否还记得前面**5.Excel日期时间函数类应用**的内容了不懂的可以回去看一下这里就不重复介绍了先上代码IF(H25DAY(EOMONTH(DATE($N23 , 23,23,P$23,1),0)),H251,“”)思路注释首先判断当前单元格地址是否小于当月的最后一天采用IF判断格式第一步采用eomonth函数获取函数当月的最后一天日期格式为eomonthA1,00表示当前月1表示下一个月。 如何获取最后一天呢A1这里就得采用第二步了第二步利用上面提供的年月可以确定当前单元格是哪一年哪一月即date函数获取标准的日期格式格式为date年月日这里不需要具体的几号直接默认填1号所以date年单元格月单元格1这样就获取了当前的年月日了第三步当前单元格小于最后一天时就默认加一否则就输出空格。补充一点这里行和列都需要固定不然会报错。固定单元格行与列。IF(H25DAY(EOMONTH(DATE($N23 , 23,23,P$23,1),0)),H251,“”)4.制定星期如何确定当前单元格是星期几这个需要用到weekday函数了首先获取当前的年月日这个上面都已经提供出了信息直接用就好了采用date 函数获取标准的日期格式格式为date年月日代码为DATE($N$23, $P$23,H25)第二步的话获取星期几这里采用weekday函数标准为weekday(A1,1)1是国外标准2是国内标准这里采用国外标准后面需要用得到完整代码如下IF(H25“”,“”,WEEKDAY(DATE($N23 , 23,23,P$23,H25),1))思路解析判断当前单元格是否为空格是空格输出空格不是空格输出为星期几然后就出现数字星期几了这里还得修改一下显示方式选中单元格点击右键设置单元格格式类型改为aaa效果如下星期就改成好了完整的考勤表如下5.颜色设置选定区域点击条件格式–新建规则先筛选星期六列不固定行固定采用**H$247**效果如下同理星期天的话是H$241空白区域的话是H$24“”6.出勤率COUNTIFS($ H $ 24: $ AL $ 24,“7”, $ H $ 24:$AL$24,“1”,H26:AL26,“8”)7.病COUNTIF(H26:AL26,“病”)8.事COUNTIF(H26:AL26,“事”)9.平时加班SUMIFS(H26:AL26,$H24 : 24:24:AL24 , 7 , 24,7,24,7,H24 : 24:24:AL$24,“1”)-B26*810.周末加班SUMIF($H24 : 24:24:AL$24,1,H26:AL26)SUMIF( $H $ 24:$AL$24,7,H26:AL26)