如何设置Conditional Formatting规则高亮14天内超8次排班的时段

核心思路 #

通过动态滚动窗口统计（结合OFFSET和COUNTIF函数），判断每个单元格所在的14天周期内工作次数是否超过8，精准定位需要高亮的时段，避免整行高亮或统计失效的问题。

具体操作步骤 #

1. 选中日程表中需要应用格式的数据区域（例如B2:AF30，假设A列为人员姓名，B至AF列为日期列）
2. 点击「条件格式」→「新建规则」→ 选择「使用公式确定要设置格式的单元格」
3. 输入以下公式（根据你的实际标记和表格结构调整）：

   =COUNTIF(OFFSET($B2,0,MAX(0,COLUMN()-COLUMN($B$2)-13),1,MIN(14,COLUMN()-COLUMN($B$2)+1)),"✓")>8
   

   公式说明：
   • OFFSET($B2,0,MAX(0,COLUMN()-COLUMN($B$2)-13),1,MIN(14,COLUMN()-COLUMN($B$2)+1))：生成当前单元格向左回溯13天（含当天共14天）的滚动窗口，若当前是前13天内的日期，则自动取从当月第一天到当天的范围
   • COUNTIF(..., "✓")：统计窗口内的工作标记数量（替换"✓"为你实际使用的标记，比如"上班"、数字1等）
   • >8：判断该窗口内工作次数是否超过8次
4. 设置你需要的高亮格式（比如填充色、字体加粗等），点击「确定」完成规则创建

常见问题修正 #

• 若出现整行高亮：检查公式中的引用范围，确保OFFSET的行参数是$B2（锁定列，允许行变化），而非整行引用
• 若出现统计失效：确认COUNTIF的条件与表格中的工作标记完全一致，同时检查OFFSET的窗口范围是否正确对应你的日期排列（横向用COLUMN()，纵向用ROW()）

内容的提问来源于stack exchange，提问作者Max M