1 考勤表效果展示 如下图所示,为制作完成的考勤表。 该考勤表有以下三个特点: 1、日期和星期自动更新 当AC2单元格的月份变化时,C3:AG4的日期和星期会自动变化。 2、通过下拉菜单选择不同的考勤符号 如下图所示,可以通过下拉菜单选择不同的考勤符号,方便快捷。 3、自动统计考勤结果 在AH列至AL列自动统计每种考勤状态对应的天数。 2 考勤表制作过程 首先制作如下图所示的表格: 接下来我们将针对考勤表的三个特点,分别介绍制作过程。 一、如何实现日期和星期自动更新 1、在C3单元格输入公式:=DATE($Z$2,$AC$2,1) Z2单元格的数据是年,AC2单元格的数据是月,该公式表示返回考勤表当年当月的第1天的日期。 2、在D3单元格输入公式:=C3+1 拖动填充柄向右复制公式,一直到1月31日。 3、选中C3:AG3,按【Ctrl+1】打开【设置单元格格式】对话框,选择【自定义】,将【类型】框的内容修改为“d”。 修改完成后,日期就仅显示日,不显示年和月,如下图所示。 4、在C4单元格中输入公式:=C3,拖动填充柄向右复制公式。 5、选中C4:AG4,按【Ctrl+1】打开【设置单元格格式】对话框,选择【自定义】,将【类型】框的内容修改为“aaa”。 这样就可以将C4:AG4中的日期显示为星期,如下图所示: 经过上述设置后的考勤表可以实现日期的自动更新。但是还有一个小问题,当某月天数不足31天时,该月的考勤表会同时显示下一个月的日期和星期。 例如下图中,当将AC2单元格的月份修改为“2”时,AE3:AG4显示的是3月的日期。 我们可以使用条件格式,使下一个月的日期不显示。 (1)选中AE3:AG4,单击【开始】-【条件格式】-【新建规则】,打开【新建格式规则】对话框。 (2)【为符合此公式的值设置格式】中输入以下公式: =month(AE$3)=$AC$2+1 该公式用于判断AE3:AG4中的月份是否为下一个月。 (3)单击【格式】按钮,选择【自定义】,在【类型】框中输入“;;;”,用于隐藏单元格中的内容。 通过上述条件格式的设置,当AE3:AG4的日期为下一个月时,AE3:AG4区域的内容不显示。 单击确定后,我们可以发现,当填写2月份考勤表时,3月份的日期不再显示。 二、制作可以选择考勤状态的下拉菜单 选中需要填写考勤状态的单元格区域,单击【数据】-【数据验证】,打开【数据验证】对话框。 在【允许】中选择“序列”;【来源】选择“AH3:AL3”,即不同考勤状态所在的单元格区域。 单击确定,这样就设置完成了。 三、自动统计考核结果 在AH5单元格输入公式:=COUNTIF($C5:$AG5,AH$3) 拖动填充柄向右填充公式,这样就可以统计出每种考勤状态对应的天数。 |
|