Excel自动化值班表后续行公式开发需求（含Index/Match/Counta）

工作表核心结构 #

• B列：序号
• C列：工牌编号
• D列：姓名
• E列：休假开始日期
• F列：休假结束日期
• G列：休假状态（休假标记为L）
• H列：周休息日（全员做六休一，存储星期值如Sun）
• I至AM列：当月1-31日的值班安排单元格
• 顶部固定行：
  • I6:AM6：每日值班起始员工的下拉数据验证列表
  • I7:AM7：当日星期（Sun/Mon等）
  • I8:AM8：当日日期（1/2/3等）

已实现的前三行员工公式 #

第10行（第1位员工）公式 #

=IF(OR(I$6="", I$8=""), "", IF(AND(I$8>=$E10, I$8<=$F10), $G10, IF(AND($H10=I$7, $H10<>""), "Off", IF($I9="", I$6, INDEX(Duty, IF(MATCH(I$9, Duty, 0) + 1 > COUNTA(Duty), 1, MATCH(I$9, Duty, 0) + 1))))))

第11行（第2位员工）公式 #

=IF(OR(I$6="", I$8=""), "", IF(AND(I$8>=$E11, I$8<=$F11), $G11, IF(AND($H11=I$7, $H11<>""), "Off", IF(I$10="L", IF($I$6="", INDEX(Duty, IF(MATCH(I10, Duty, 0) + 1 > COUNTA(Duty), 1, MATCH(I10, Duty, 0) + 1)), $I$6), IF(I10="Off", IF($I$6="", INDEX(Duty, IF(MATCH(I10, Duty, 0) + 1 > COUNTA(Duty), 1, MATCH(I10, Duty, 0) + 1)), $I$6), INDEX(Duty, IF(MATCH(I10, Duty, 0) + 1 > COUNTA(Duty), 1, MATCH(I10, Duty, 0) + 1)))))))

第12行（第3位员工）公式 #

=IF(OR(I$6="", I$8=""), "", IF(AND(I$8>=$E12, I$8<=$F12), $G12, IF(AND($H12=I$7, $H12<>""), "Off", IF(OR(I$10="L", I$10="Off"), IF(OR(I$11="L", I$11="Off"), INDEX(Duty, 1), INDEX(Duty, IF(MATCH(I$11, Duty, 0) + 1 > COUNTA(Duty), 1, MATCH(I$11, Duty, 0) + 1))), IF(OR(I$11="L", I$11="Off"), INDEX(Duty, IF(MATCH(I$10, Duty, 0) + 1 > COUNTA(Duty), 1, MATCH(I$10, Duty, 0) + 1)), INDEX(Duty, IF(MATCH(I$11, Duty, 0) + 1 > COUNTA(Duty), 1, MATCH(I$11, Duty, 0) + 1)))))))

第13行（第4位员工）公式方案 #

遵循自上而下循环逻辑：优先检查自身是否休假/轮休，若正常则承接前一位可值班员工的下一个循环值；若前三位均为L或Off，则从值班列表Duty第一个值开始。

=IF(OR(I$6="", I$8=""), "", 
    IF(AND(I$8>=$E13, I$8<=$F13), $G13, 
        IF(AND($H13=I$7, $H13<>""), "Off", 
            IF(OR(I$10="L", I$10="Off"), 
                IF(OR(I$11="L", I$11="Off"), 
                    IF(OR(I$12="L", I$12="Off"), 
                        INDEX(Duty, 1), 
                        INDEX(Duty, IF(MATCH(I$12, Duty, 0)+1>COUNTA(Duty),1,MATCH(I$12,Duty,0)+1))
                    ), 
                    IF(OR(I$12="L", I$12="Off"), 
                        INDEX(Duty, IF(MATCH(I$11, Duty, 0)+1>COUNTA(Duty),1,MATCH(I$11,Duty,0)+1)), 
                        INDEX(Duty, IF(MATCH(I$12, Duty, 0)+1>COUNTA(Duty),1,MATCH(I$12,Duty,0)+1))
                    )
                ), 
                IF(OR(I$11="L", I$11="Off"), 
                    IF(OR(I$12="L", I$12="Off"), 
                        INDEX(Duty, IF(MATCH(I$10, Duty, 0)+1>COUNTA(Duty),1,MATCH(I$10,Duty,0)+1)), 
                        INDEX(Duty, IF(MATCH(I$12, Duty, 0)+1>COUNTA(Duty),1,MATCH(I$12,Duty,0)+1))
                    ), 
                    IF(OR(I$12="L", I$12="Off"), 
                        INDEX(Duty, IF(MATCH(I$11, Duty, 0)+1>COUNTA(Duty),1,MATCH(I$11,Duty,0)+1)), 
                        INDEX(Duty, IF(MATCH(I$12, Duty, 0)+1>COUNTA(Duty),1,MATCH(I$12,Duty,0)+1))
                    )
                )
            )
        )
    )
)

公式优化建议 #

1. 封装循环逻辑为自定义函数
   用VBA编写自定义函数替代重复的INDEX+MATCH嵌套，简化公式结构。示例代码：

   Function NextDuty(prevDuty As String, dutyList As Range) As String
       Dim dutyArr As Variant
       dutyArr = dutyList.Value
       Dim i As Integer
       For i = 1 To UBound(dutyArr)
           If dutyArr(i, 1) = prevDuty Then
               NextDuty = IIf(i = UBound(dutyArr), dutyArr(1, 1), dutyArr(i + 1, 1))
               Exit Function
           End If
       Next i
       NextDuty = dutyArr(1, 1)
   End Function
   

   调用时直接用=NextDuty(I$12, Duty)替代复杂的循环判断。

2. 使用数组公式简化自上而下检查
   利用FILTER函数（Excel 365及以上版本）筛选前序员工中正常值班的记录，配合自定义函数大幅减少嵌套层级：

   =IF(OR(I$6="",I$8=""),"",
       IF(AND(I$8>=$E13,I$8<=$F13),$G13,
           IF(AND($H13=I$7,$H13<>""),"Off",
               LET(
                   validPrev,FILTER(I$10:I$12,NOT(I$10:I$12={"L","Off"})),
                   lastValid,INDEX(validPrev,COUNTA(validPrev)),
                   IF(COUNTA(validPrev)=0,INDEX(Duty,1),NextDuty(lastValid,Duty))
               )
           )
       )
   )
   

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