Excel公式优化需求：统计最近10个连续0右侧的违规次数

需求明确 #

• B列需统计公会成员的总违规次数（strikes），仅计算最近一组10个连续0右侧的所有数值之和
• 忽略空白单元格，但空白列填充数据后自动纳入统计范围
• 若最近的10个连续0之后无违规记录，计数直接重置为0

现有公式缺陷 #

原公式及ChatGPT提供的替代公式仅能识别位于数据末尾的10个连续0并重置计数，若10连零组之后仍有违规值，无法准确定位到该组位置，导致统计整行所有数据，不符合需求。

修正方案 #

方案1：适用于Excel 365/2021（支持动态数组） #

=LET(
    data, FILTER(C2:CN2, C2:CN2<>""),
    len_data, COUNTA(data),
    zero_runs, SCAN(0, data, LAMBDA(a,b, IF(b=0, a+1, 0))),
    last_10zero_pos, XLOOKUP(10, zero_runs, SEQUENCE(len_data), 0, 0, -1),
    IF(last_10zero_pos=0, SUM(data), SUM(OFFSET(data, 0, last_10zero_pos, 1, len_data-last_10zero_pos)))
)

公式原理

1. data：过滤当前行的空白单元格，仅保留有数据的列，确保空白列不干扰统计
2. zero_runs：通过SCAN函数逐列计算连续0的长度，遇到非0值时重置计数
3. last_10zero_pos：使用XLOOKUP从后往前查找第一个连续0长度为10的结束位置
4. 最后根据找到的位置，计算该位置右侧所有数值的和；若未找到10连零，则统计所有数据的和

方案2：适用于旧版Excel（无动态数组支持） #

=IFERROR(
    SUM(OFFSET(C2:CN2,0,MATCH(2,1/(FREQUENCY(IF(C2:CN2=0,COLUMN(C2:CN2)),IF(C2:CN2<>0,COLUMN(C2:CN2)))=10))+9,1,COUNTA(C2:CN2)-MATCH(2,1/(FREQUENCY(IF(C2:CN2=0,COLUMN(C2:CN2)),IF(C2:CN2<>0,COLUMN(C2:CN2)))=10))-9)),
    SUM(C2:CN2)
)

公式原理

1. FREQUENCY(...)：计算当前行中所有连续0区间的长度
2. MATCH(2,1/(...=10))：定位最后一组长度为10的连续0的起始列位置
3. OFFSET：定位到该组10连零右侧的区域，对该区域求和；若未找到10连零，则直接统计所有非空白数据的和

验证结果 #

针对需求中的示例数据，两个修正公式均能输出预期结果：

• 数据行1 2 3 4 → 结果10
• 数据行1 2 3 4 0 0 0 0 0 0 0 0 0 0 0 0 → 结果0
• 数据行1 2 3 4 0 0 0 0 0 0 0 0 1 → 结果11
• 数据行1 2 3 4 0 0 0 0 0 0 0 0 0 0 0 1 2 3 4 → 结果10
• 数据行1 2 3 4 0 0 0 0 0 0 0 0 0 0 0 1 2 3 4 0 0 0 0 0 0 0 0 0 0 0 1 → 结果1

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