动态考勤表格制作excel教程：月份切换与出勤统计怎么联动

动态考勤表格制作的关键，是让月份参数、日期表头、考勤明细和出勤统计共用同一组起止日期。最稳妥的做法不是在会变化的月历区域里直接保存考勤，而是把原始记录放进独立明细表；切换年份或月份后，展示区按姓名与日期读取记录，统计区再按相同日期边界汇总。

下面以参数区的年份在 B1、月份在 C1 为例。月份使用 1 至 12 的数字，日期单元格保存真正的 Excel 日期值，只通过单元格格式显示“1日”“2日”。这样 DATE、EOMONTH、COUNTIFS 和 NETWORKDAYS 才能顺利联动。

先搭好四个区域，避免换月后记录错位

建议把工作簿分成参数区、考勤明细表、月度展示区和统计区。参数区只负责选择年份与月份；考勤明细表负责长期保存记录；展示区显示当月每天的状态；统计区计算出勤、迟到、请假和出勤率。

选中原始记录后按 Ctrl+T 转成 Excel 表格，并把表格名称改成 考勤明细。至少保留三列：

如果直接在 B6:AF20 输入状态，再把 B4:AF4 切换到下个月，原来的状态不会自动属于新日期，却会看起来像新月份记录。独立明细表以“人员+日期”为主键，换月只改变查询条件，不会覆盖历史数据。

设置年份和月份参数

在 B1 输入年份，在 C1 输入月份。可以给 C1 设置数据验证，允许的序列为 1,2,3,4,5,6,7,8,9,10,11,12。如果想显示完整月份标题，可在 E1 输入：

=TEXT(DATE($B$1,$C$1,1),"yyyy年m月")

所有后续公式都引用 B1 和 C1。不要在日期表头、统计公式中分别手写月份，否则某一处漏改就会出现“表头已换月、统计仍是上月”的问题。

让日期表头随月份自动变化

兼容多数版本的横向填充公式

假设日期表头从 B4 开始，最多预留 31 列。在 B4 输入下面的公式，再向右填充到 AF4：

=IF(COLUMNS($B:B)<=DAY(EOMONTH(DATE($B$1,$C$1,1),0)),DATE($B$1,$C$1,COLUMNS($B:B)),"")

DATE($B$1,$C$1,1) 生成当月第一天，EOMONTH 找到月末，DAY 得出当月天数。二月只有 28 天或 29 天时，多余列自动显示为空；四月、六月等月份也会在第 30 天后停止。

把 B4:AF4 设置为自定义格式 d日。在 B5 输入星期公式并向右填充：

=IF(B4="",",",CHOOSE(WEEKDAY(B4,2),"一","二","三","四","五","六","日"))

支持动态数组的 Excel 版本

如果当前 Excel 支持 SEQUENCE，也可以在 B4 用一个公式生成整月日期：

=SEQUENCE(1,DAY(EOMONTH(DATE($B$1,$C$1,1),0)),DATE($B$1,$C$1,1),1)

公式会向右溢出，因此溢出区域必须保持为空。若出现 #SPILL!，先检查 B4 右侧是否有内容或合并单元格。

按姓名和日期回填考勤状态

在 A6:A20 放员工姓名，B6 对应 A6 这名员工在 B4 日期的考勤状态。支持 XLOOKUP 的版本可在 B6 输入：

=IF(B$4="",",",XLOOKUP(1,(考勤明细[姓名]=$A6)*(考勤明细[日期]=B$4),考勤明细[状态],""))

再把公式向右、向下填充。月份变化时，B4:AF4 的日期先变化，查询条件随之变化，展示结果就会自动读取新月份记录。

同一个人同一天原则上只能保留一条最终状态。如果明细中存在重复记录，XLOOKUP 只返回第一个匹配值，统计却可能把多条都计入。可以在明细表增加“重复检查”列：

=COUNTIFS(考勤明细[姓名],[@姓名],考勤明细[日期],[@日期])

结果大于 1 时应先核对。若公司允许一天多次打卡，原始打卡表与日状态表应分开：先按规则形成每天唯一状态，再供月度考勤表读取。

让出勤统计与月份使用同一边界

在统计区给每名员工设置“出勤”“迟到”“事假”“病假”“旷工”“应出勤”和“出勤率”等列。假设 AG6 统计 A6 员工当月“出勤”次数，公式为：

=COUNTIFS(考勤明细[姓名],$A6,考勤明细[日期],">="&DATE($B$1,$C$1,1),考勤明细[日期],"<"&EDATE(DATE($B$1,$C$1,1),1),考勤明细[状态],AG$5)

其中 AG5 写“出勤”。把公式向右复制到其他状态列，只要标题与明细表中的状态文字完全一致，就能统计迟到、事假等项目。日期上限采用“下月第一天之前”，比手写 28、30 或 31 更可靠。

如果工作制度为周一至周五，可用 NETWORKDAYS 计算应出勤天数。先在独立区域维护法定休息日或公司假期，并把该区域命名为 节假日，然后输入：

=NETWORKDAYS(DATE($B$1,$C$1,1),EOMONTH(DATE($B$1,$C$1,1),0),节假日)

单双休、轮班或调休制度不适合直接套用 NETWORKDAYS。此时应建立“工作日历”表，按每个日期标记是否应出勤，再使用 SUMIFS 按月求和。这样节日调休、周末补班等情况才不会被固定星期规则误判。

假设 AG6 是出勤次数，AM6 是应出勤天数，出勤率可写为：

=IFERROR(AG6/AM6,0)

将结果设置为百分比。迟到是否计入出勤、请假是否从应出勤中扣除，属于企业考勤口径，应先确定规则再调整分子或分母，不能只改显示格式。

用条件格式增强查看效率

可对 B6:AF20 设置条件格式，例如状态等于“迟到”时使用醒目底色，等于“事假”时使用另一种底色。周末列也可根据第 5 行的星期值设置规则。条件格式的应用范围要覆盖完整的 31 列和人员区域，规则公式中的行列引用要与左上角 B6 对齐。

条件格式只负责显示，不参与统计。统计仍应读取 考勤明细 的状态字段，避免依赖单元格颜色计数。颜色可能由人工修改或规则覆盖，不能作为可靠的数据源。

切换月份后不联动，按这几项排查

1. 检查日期是否为真日期。任选一个明细日期，把格式临时改成常规；若不能显示日期序列值，原内容可能是文本。可用分列、DATEVALUE 或重新导入进行转换。
2. 检查参数引用。日期表头与统计公式都应引用 $B$1、$C$1。年份或月份引用未锁定时，公式横向复制后可能跑到其他单元格。
3. 检查 Excel 表格名称。公式中的 考勤明细[姓名]、考勤明细[日期]、考勤明细[状态] 必须与实际表名和列名一致。
4. 检查状态文字。“出勤”和“正常出勤”、“事假”和“事 假”会被视为不同值。用数据验证限定选项，并清理前后空格。
5. 检查重复记录。同一人员同一天存在两条状态时，查询结果与计数结果可能不一致，应先确定唯一日状态。
6. 检查计算模式。如果公式没有立即更新，在“公式”选项中确认工作簿计算设为自动。

完成后做一次边界测试

依次切换到 2 月、4 月和 12 月，核对日期列数、月末日期和统计结果；再测试闰年二月。向明细表新增一条选定月份的记录，展示区和对应状态次数应同时变化；把月份切到没有记录的月份，展示区应为空，统计应为 0。

这套联动方式的核心可以概括为：参数区产生月初与月末，明细表保存唯一事实，展示区按“姓名+日期”查询，统计区按同一日期范围汇总。只要日期字段是真日期、状态名称统一、每人每日记录唯一，月份切换与出勤统计就能保持同步。