Excel中按条件计算行内最长连续0 streak：排除首尾起始0段

需求说明 #

给定Excel单元格区域D2:BH2（包含非0数字和0），需计算最长连续0的长度，但需满足：

• 忽略从行首开始的连续0段
• 忽略在行尾结束的连续0段

示例验证 #

• 序列 0 0 0 1 5 6 0 0 1 → 最长有效长度：2（排除开头的3个0）
• 序列 1 0 0 0 1 5 6 0 0 1 → 最长有效长度：3
• 序列 0 1 5 6 0 0 1 0 0 0 0 → 最长有效长度：2（排除结尾的4个0）
• 序列 0 1 5 6 0 0 1 0 0 0 0 1 → 最长有效长度：4

解决方案 #

方案1：Excel 365/2021（动态数组公式） #

使用LET函数简化逻辑，直接回车即可生效，无需组合键：

=LET(
    rng, D2:BH2,
    first_nonzero, XMATCH(TRUE, rng<>0, 0),
    last_nonzero, XMATCH(TRUE, rng<>0, 0, -1),
    filtered, IFERROR(DROP(TAKE(rng, last_nonzero), first_nonzero-1), ""),
    MAX(IFERROR(FREQUENCY(IF(filtered=0, COLUMN(filtered)), IF(filtered<>0, COLUMN(filtered))), 0))
)

逻辑说明：

1. first_nonzero/last_nonzero：定位区域内第一个和最后一个非0值的位置
2. filtered：截取两个非0值之间的区域，直接排除首尾的连续0
3. FREQUENCY统计过滤后区域内的连续0区间长度，MAX取最大值

方案2：兼容旧版Excel（数组公式） #

需按Ctrl+Shift+Enter组合键确认输入（不可直接回车）：

=MAX(IFERROR(FREQUENCY(IF(COLUMN(D2:BH2)>=MATCH(TRUE,D2:BH2<>0,0),IF(COLUMN(D2:BH2)<=MATCH(TRUE,D2:BH2<>0,0,-1),IF(D2:BH2=0,COLUMN(D2:BH2)))),IF(COLUMN(D2:BH2)>=MATCH(TRUE,D2:BH2<>0,0),IF(COLUMN(D2:BH2)<=MATCH(TRUE,D2:BH2<>0,0,-1),IF(D2:BH2<>0,COLUMN(D2:BH2))))),0))

逻辑说明：

1. 通过MATCH函数锁定第一个和最后一个非0值的列范围
2. 仅在该范围内统计连续0的长度，自动忽略首尾的无效0段
3. IFERROR处理全0、无中间非0值等特殊情况，返回0

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