求Excel公式：统计员工连续病假独立发生次数（含周六规则）

嘿，这个需求确实需要精准的逻辑判断，咱们一步步来搞定它。核心思路是：统计每个病假日期是否是「新病假周期的起点」——也就是要么它是该员工的第一个病假，要么它的前一个有效工作日（跳过周六，除非周六本身是病假）没有病假。

适用Excel 365/2021的动态数组公式（简洁版） #

如果你的Excel支持LET和BYCOL函数，用这个公式最清晰，直接放在员工行的右侧单元格（比如AA2）即可：

=LET(
    dates, B$1:Z$1,  // 引用首行的日期区域
    sick, B2:Z2,     // 当前员工的病假标记行
    // 第一步：标记哪些是「有效病假」——非周六的病假，或者周六标注了病假的情况
    valid_sick, (sick="病假")*(WEEKDAY(dates,2)<>6) + (sick="病假")*(WEEKDAY(dates,2)=6),
    // 第二步：获取每个日期的「前一个有效工作日」的病假状态（前一天是周六的话，取周五的状态）
    prev_valid, BYCOL(SEQUENCE(1,COLUMNS(dates)), LAMBDA(i, 
        IF(i=1, 0,  // 第一个日期没有前序，默认0（无病假）
            IF(WEEKDAY(INDEX(dates,1,i-1),2)=6, 
                INDEX(valid_sick,1,i-2),  // 前一天是周六，取两天前的状态
                INDEX(valid_sick,1,i-1)   // 否则取前一天的状态
            )
        )
    )),
    // 第三步：统计「有效病假且前序无病假」的数量，就是独立病假次数
    SUM(--(valid_sick=1)*(prev_valid=0))
)

适用旧版Excel的兼容公式 #

如果你的Excel不支持动态数组函数，用这个SUMPRODUCT版本：

=SUMPRODUCT(
    // 标记有效病假：非周六病假 或 周六病假
    --((B2:Z2="病假")*(WEEKDAY(B1:Z1,2)<>6)+(B2:Z2="病假")*(WEEKDAY(B1:Z1,2)=6)),
    // 判断是否是新周期起点：要么是第一个日期，要么前序有效工作日无病假
    --(
        COLUMN(B2:Z2)=COLUMN(B2)
        +
        (COLUMN(B2:Z2)>COLUMN(B2))*IF(
            WEEKDAY(OFFSET(B1,0,COLUMN(B2:Z2)-COLUMN(B2)-1),2)=6,
            OFFSET(B2,0,COLUMN(B2:Z2)-COLUMN(B2)-2)<>"病假",
            OFFSET(B2,0,COLUMN(B2:Z2)-COLUMN(B2)-1)<>"病假"
        )
    )
)

注意事项 #

• 如果你的病假标记是数字（比如1代表病假，0代表正常），把公式里的"病假"替换成1，<>"病假"替换成=0即可。
• 公式中的B$1:Z$1和B2:Z2请根据你的实际数据范围调整，确保覆盖所有日期和员工的病假列。

验证你的例子 #

• 分析师1：01/05、03/05病假（中间02/05上班）→ 两个日期都是新周期起点，统计结果为2，符合预期。
• 分析师2：03/05、06/05病假（周六未排班）→ 06/05的前序有效工作日是03/05（病假），只有03/05是新起点，统计结果为1，符合预期。
• 分析师3：周六排班且病假→ 该日期是新起点，统计结果为1，符合预期。

内容的提问来源于stack exchange，提问作者Hulk Smash 93