Excel中如何按连续非零区间分组求和并提取结果？

给定Excel行数据（如A2:L2）：|31|28|00|00|07|00|00|00|05|31|10|00|，需要提取所有连续非零区域的求和结果，分别返回59、7、46这类分段总和，且支持多行、可变位置和长度的连续区间。

你的原公式问题分析 #

• 仅能定位第一个非零区间，无法处理多个分段；
• 使用ROW(A2:L2)会返回统一行号（都是2），导致零值位置匹配逻辑完全失效；
• 即便按数组公式规则确认，也只能输出单个结果，满足不了多分段需求。

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

直接在单个单元格输入，自动溢出所有分段结果：

=LET(
    data, A2:L2,
    non_zero, data>0,
    group, SCAN(0, non_zero, LAMBDA(a,b, IF(b, a, a+1))),
    FILTER(BYROW(UNIQUE(group, TRUE), LAMBDA(g, SUM(FILTER(data, group=g)))), group<>MAX(group))
)

简单解释： #

1. data：指定要处理的目标区域；
2. non_zero：标记出所有非零的单元格；
3. group：给每个连续非零区间分配相同组号，遇到零就递增组号；
4. 最后按组求和，过滤掉全零的组，得到所有分段和。

输入后会自动在单元格下方弹出三个结果：59、7、46，下拉公式就能处理多行数据。

方法2：旧版Excel 逐段提取公式 #

如果没有动态数组功能，只能逐个提取分段和：

• 提取第一个分段和（按Ctrl+Shift+Enter确认数组公式）：

=SUM(INDIRECT(ADDRESS(ROW(A2),MATCH(TRUE,A2:L2>0,0))&":"&ADDRESS(ROW(A2),MATCH(TRUE,INDEX(A2:L2,MATCH(TRUE,A2:L2>0,0)):L2=0,0)+MATCH(TRUE,A2:L2>0,0)-2)))

• 提取第二个分段和（同样按Ctrl+Shift+Enter）：

=SUM(INDIRECT(ADDRESS(ROW(A2),MATCH(TRUE,OFFSET(A2,0,MATCH(TRUE,A2:L2=0,0)):L2>0,0)+MATCH(TRUE,A2:L2=0,0))&":"&ADDRESS(ROW(A2),MATCH(TRUE,OFFSET(A2,0,MATCH(TRUE,OFFSET(A2,0,MATCH(TRUE,A2:L2=0,0)):L2>0,0)+MATCH(TRUE,A2:L2=0,0)):L2=0,0)+MATCH(TRUE,OFFSET(A2,0,MATCH(TRUE,A2:L2=0,0)):L2>0,0)+MATCH(TRUE,A2:L2=0,0)-2)))

这种方法需要手动调整公式提取后续分段，适合区间数量少的场景。

方法3：VBA自定义函数（批量处理首选） #

要批量处理多行，写个VBA函数更方便：

1. 按Alt+F11打开VBA编辑器，插入模块；
2. 粘贴以下代码：

Function SumNonZeroRanges(rng As Range) As Variant
    Dim arr() As Double, cell As Range
    Dim currentSum As Double, inRange As Boolean
    Dim count As Integer
    
    currentSum = 0
    inRange = False
    count = 0
    
    For Each cell In rng
        If cell.Value > 0 Then
            currentSum = currentSum + cell.Value
            inRange = True
        Else
            If inRange Then
                count = count + 1
                ReDim Preserve arr(1 To count)
                arr(count) = currentSum
                currentSum = 0
                inRange = False
            End If
        End If
    Next cell
    
    ' 处理末尾的非零区间
    If inRange Then
        count = count + 1
        ReDim Preserve arr(1 To count)
        arr(count) = currentSum
    End If
    
    If count > 0 Then
        SumNonZeroRanges = arr
    Else
        SumNonZeroRanges = "无有效非零区间"
    End If
End Function

3. 返回Excel，在单元格输入=SumNonZeroRanges(A2:L2)，按Ctrl+Shift+Enter（旧版）或直接回车（365），就能得到所有分段和，下拉填充即可处理多行。

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