如何实现自动考勤和月份动态更新，老师这样干的

刀锋一九三八 2023-06-30 发布于山东

展开全文

——EXCEL2016系列教程之“智能考勤表"

#COUNTIF函数计数#粉丝问我，如何制作一个可以自动生成考勤记录的考勤表？于是我就制作了这期内容，本次课包含的内容很丰富，有自定义格式，有函数，还有数据有效性的验证等知识，干货多多，希望读完。如果有问题，给我留言。视频教程将陆续集结，敬请期待~！！

一、设置自动更新的“考勤表头”

1.设定在“AM2”单元格输入当前的年月日（例如2021年2月1日），——快捷键“CTRL+1”——打开“设置单元格格式”对话框——选择“自定义”——在“类型”中输入“yyyy"年"m"月"”，单击“确定”，显示结果为2021年2月，如图。

2.在“A1”单元格输入“=AM2”——快捷键“CTRL+1”——打开“设置单元格格式”对话框——选择“自定义”——在“类型”中输入“"某""某""单""位"yyyy"年"m"月""员""工""考""勤""表"”——单击“确定”即可，如图。

3.完成上述操作后，每月只需要修改考勤时间，考勤表表头就会自动更新，如图。

二、设置考勤“日期”

1.在C4单元格输入“=AJ2”——选中该单元格——快捷键“CTRL+1”——打开“设置单元格格式”对话框——选择“自定义”——在“类型”中输入“"d"”——单击“确定”，结果显示为当月第一天的日期，如图。

2. 在d4单元格输入“=C4+1”——横向拖动填充柄，填充其他单元格，如图。

3.考勤天数界定

每月的天数不一样，有的月份有31日，2月只有28或29天，等情况，这是需要根据月份设定考勤天数。

（1）单击“开始”——在“样式”选项组中选择“条件格式”——在弹出的下列列表中，单击“新建规则”，如图。

（2）打开了“编辑格式规则”对话框——在“选择规则类型”中选择“使用公式确定要设置格式的单元格”——在“编辑规则说明”中输入表达式“=MONTH(AE4)>MONTH(AB4)”，——单击“格式”按钮，如图。

（3）打开“设置单元格格式”对话框——选择“自定义”——在“类型”中输入“;;;”（英文状态下的分号，隐藏单元格的内容），单击确定，如图。

（4）这时返回 “编辑格式规则”对话框，单击“确定”。这时根据输入的月份不同，考勤天数发生变化，看效果。

补充：打开“条件格式规则管理器”将“应用于”设置为“=$AE$4:$AG$5”，（目的是将对应的星期也进行隐藏）如图。

三、设置考勤日期对应的“星期”

在C5单元格输入“=C4”——打开“设置单元格格式”对话框——选择“自定义”——在“类型”中输入“aaa”（“aaa”格式表示，将对应的日期显示为“星期”），单击确定，如图6。

四、设置周六和周日“高亮显示”

为了便于数据统计，我们将周六和周日，设置成高亮显示。

1.选定所有“日期”单元格（C4:CAG）——打开了“编辑格式规则”对话框——在“选择规则类型”中选择“使用公式确定要设置格式的单元格”——在“编辑规则说明”中输入表达式“=WEEKDAY(C$4,2)>5”，——单击“格式”按钮，——打开“设置单元格格式”对话框——选择“填充”——在“背景颜色”中，选择一种颜色——单击确定，如图。

2.这时返回 “编辑格式规则”对话框，单击“确定”。这时对应周六和周日的“日期”，高亮显示，如图。

3. 打开“条件格式规则管理器”将“应用于”设置为“=$C$4:$AG$25”，（将条件格式运用与考勤填写区域），实现了周六和周日高亮显示，如图。

五、设定“考勤数据的有效性”

1.在sheet2表中，设置考勤表的《填报说明》，如图。

2.选中考勤表中的单元格区域（C6:AG25）——点击“数据”菜单——在“数据工具”选项组中——单击“数据验证”按钮——在弹出的下列表中，单击“数据验证”，如图。

3.在打开的“数据验证”对话框中——选择 “设置”标签——在验证条件“将允许(A)”——设置为调为“序列”——单击“来源”选择右侧标记——选择需要设置“序列“的内容，设置完毕后——点击确定，即可，如图。

演示（图7）：

六、设置“自动考勤统计”

1.选中统计考勤天数的单元格（AH6）——输入函数“=COUNTIF(C6:AG6,"O")”,即可统计标记“O”出勤天数,如图。

2.同理设置好其他考勤的公式，分别如下：

事假：“=COUNTIF(C6:AG6,"△")”;

病假：“=COUNTIF(C6:AG6,"B")”;