今天教你用 「动态考勤表」 一招解决所有问题！
只需输入月份，自动变色、自动更新节假日，从此告别加班，效率翻倍！
动态考勤表的优势：
自动变色：节假日、双休日一键标记，颜色分明。
一表多用：修改月份即可自动更新全年数据！
步骤1：设置动态标题
目标：输入月份后，标题自动显示年月。
- 在 A1单元格 输入月份首日（如 2025-5-1）。
- 合并 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：标识节假日
目标：自动为节假日填充特定颜色。
- 选中区域 B2:AF10 →【条件格式】→【新建规则】→【使用公式】。
- 输入公式：
=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：智能标记双休日
目标：仅标记无需调休的真正休息日。
- 再次选中 B2:AF19 →【条件格式】→【新建规则】→【使用公式】。
- 输入公式：
=AND(WEEKDAY(B$2,2)>=6, ISERROR(MATCH(B$2, 节假日!$B$2:$B$29,0)) )
- 设置填充色（如绿色）。
标识“正常休息的双休日”，即同时满足以下两个条件：
日期是周六或周日（通过 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。
- MATCH(B$2, 节假日!$B$2:$B$29,0)：在“节假日”表的 B2:B29 范围内精确查找 B$2 的日期。
- 参数说明：
3.AND(条件1, 条件2)
- 功能：要求 两个条件同时满足。
- 仅当日期是周六/日 且 非节假日时 → 返回 TRUE，触发格式标记。
步骤6：美化表格
按你想要的格式，再动手美化一下。
从此告别手动标记、调休混乱，Excel自动为你打工！
无论是春节、国庆，还是大小调休，智能识别零失误。
职场小白也能3分钟晋升效率达人！
评论 (0)