5步搞定动态考勤表！标记节假日、调休日？Excel自动变色！

今天教你用 「动态考勤表」 一招解决所有问题！

只需输入月份，自动变色、自动更新节假日，从此告别加班，效率翻倍！

2. 合并 A1:AF1 区域，右键→【设置单元格格式】→【自定义】，输入格式代码：”yyyy年m月 考勤表”。

效果：修改A1日期，标题秒变“2025年5月考勤表”！

 步骤2：生成日期姓名的斜表头

1、A2单元格 输入日期姓名，把光标放到日期跟姓名之间，按ALT+ENTER键进行换行。

2、制作斜表头

选中A2跟A3单元格，合并单元格

再按CTRL+1，在【设置单元格格式】→【边框】

在对齐方式里改成左对齐跟垂直居中，然后在日期前面敲空格调节一下位置，效果如图所示：

 步骤3：生成日期序列

目标：自动生成当月所有日期及星期。

1、日期行：在 B2单元格 输入公式 =A1，

C3单元格里输入B2 1，公式向右填充至AF2。

技巧：选中B2:AF2→【自定义格式】→输入 “d”，仅显示日期数字（如1、2）。

2、星期行：在 B3单元格 输入公式 =B2，同样向右填充至AF3。

同理：B3:AF3格式设为 “aaa”，显示星期简称（一、二）。

选中区域增加边框。

解决方案：动态生成本月日期

当我们把标题变成6月时，6月只有30天，就会多出一天。

这时要把B2单元格里的公式改成：

=IF(COLUMN(A1) > DAY(EOMONTH($A$1, 0)), “”, DATE(YEAR($A$1), MONTH($A$1), COLUMN(A1)))

（1）公式解析：

COLUMN(A1)：获取当前列号（向右拖动时自动变为B1、C1等）。

DAY(EOMONTH($A$1,0))：计算当月总天数（如5月返回31）。

IF函数：若列号超过总天数，显示空白；否则生成对应日期。

（2）向右拖动填充至AF2：自动生成1号到最后一天的日期，超范围显示空白。

效果演示：

• 若A1为 2025-02-01（28天），B2到AD2显示1~28日，AE2及之后为空白。
• 若A1为 2025-05-01（31天），B2到AF2完整显示1~31日。

 步骤4：标识节假日

目标：自动为节假日填充特定颜色。

1. 选中区域 B2:AF10 →【条件格式】→【新建规则】→【使用公式】。

2. 输入公式：

=ISNUMBER(MATCH(B$2, 节假日!$B$2:$B$29,0))

3、设置填充色（如粉色），点击确定。

节假日的表格：（不考虑调休）

函数拆解

（1）MATCH(B$2, 节假日!$B$2:$B$29,0)

功能：在「节假日」工作表的 B2:B29 范围内，精确查找当前单元格 B2 的日期。

返回值：若找到：返回该日期在列表中的位置序号（如第3行找到，返回数字 3）。

若未找到：返回错误值 #N/A。

（2）ISNUMBER(…)

功能：检查 MATCH 函数的结果是否为数字。

返回值：若 MATCH 返回数字（找到日期）：ISNUMBER 返回 TRUE。

若 MATCH 返回错误值（未找到）：ISNUMBER 返回 FALSE。

效果：所有节假日自动标红，一目了然！

 步骤5：智能标记双休日

目标：仅标记无需调休的真正休息日。

1. 再次选中 B2:AF19 →【条件格式】→【新建规则】→【使用公式】。

2. 输入公式：

=AND(WEEKDAY(B$2,2)>=6, ISERROR(MATCH(B$2, 节假日!$B$2:$B$29,0)) )

3. 设置填充色（如绿色）。

标识“正常休息的双休日”，即同时满足以下两个条件：

日期是周六或周日（通过 WEEKDAY 函数判断）；

日期未被标记为节假日（通过 MATCH 函数验证）。

函数逐层拆解

1.WEEKDAY(B$2,2)>=6

• 功能：判断单元格 B$2 的日期是否为 周六（6）或周日（7）。
  • 参数说明：
    • B$2：目标日期单元格（行绝对引用，列相对引用）。
    • 2：指定一周从周一开始（2=周一，1=周日）。
  • 返回值：
    • 若为周六或周日 → TRUE；
    • 否则 → FALSE。

2.ISERROR(MATCH(B$2, 节假日!$B$2:$B$29,0))

• 功能：判断 B$2 的日期 是否不在“节假日”表中。
  • 参数说明：
    • MATCH(B$2, 节假日!$B$2:$B$29,0)：在“节假日”表的 B2:B29 范围内精确查找 B$2 的日期。
      • 若找到 → 返回位置序号（如 3）；
      • 若未找到 → 返回错误值 #N/A。
    • ISERROR(…)：检查 MATCH 结果是否为错误值。
      • 若未找到（即非节假日） → TRUE；
      • 若找到（即节假日） → FALSE。

3.AND(条件1, 条件2)

• 功能：要求 两个条件同时满足。
  • 仅当日期是周六/日 且 非节假日时 → 返回 TRUE，触发格式标记。

步骤6：美化表格

按你想要的格式，再动手美化一下。

从此告别手动标记、调休混乱，Excel自动为你打工！

无论是春节、国庆，还是大小调休，智能识别零失误。

职场小白也能3分钟晋升效率达人！