Excel动态SUM公式需求：按1后接0规则实现行内分段求和

核心需求 #

基于指定行的1-0序列，动态计算目标行对应区域的求和：

• 当出现1后接0的连续序列时，求和1到最后一个0对应位置的数值
• 若0之后出现新的1，则保持前一段的求和结果，直到新的1开启下一个求和区间

公式实现（适用于Excel 365及以上版本） #

假设控制行（含1/0序列）为$E$5:$Z$5，求和目标行为$E$6:$Z$6，在结果单元格（如F6）输入以下公式后，可向右/向下填充：

=LET(
    curr_col, COLUMN(),
    control_range, $E$5:$Z$5,
    sum_range, $E$6:$Z$6,
    start_col, MAX(IF(OFFSET(control_range,0,0,1,curr_col-COLUMN(control_range)+1)=1, COLUMN(OFFSET(control_range,0,0,1,curr_col-COLUMN(control_range)+1)), 0)),
    next_1_col, XLOOKUP(1, OFFSET(control_range,0,start_col-COLUMN(control_range)+1), COLUMN(OFFSET(control_range,0,start_col-COLUMN(control_range)+1)), curr_col),
    IF(OR(OFFSET(control_range,0,curr_col-COLUMN(control_range))=1, curr_col=next_1_col), SUM(OFFSET(sum_range,0,start_col-COLUMN(sum_range),1,next_1_col-start_col+1)), OFFSET(F6,0,-1))
)

逻辑说明 #

1. 变量定义：用LET函数封装常用变量，简化公式结构
2. 确定区间起点：start_col定位当前单元格左侧最近的1所在列，作为求和区间的起始点
3. 确定区间终点：next_1_col找到起点之后第一个1的列号，若不存在则以当前列作为终点
4. 动态求和/延续结果：
   • 若当前列是1所在列，或是下一个1的前一列（区间结束点），则计算区间内的总和
   • 否则直接延续左侧单元格的求和结果

旧版Excel兼容方案（无LET/XLOOKUP） #

如果使用旧版Excel，可替换为以下嵌套公式（以F6为例，需调整范围后填充）：

=IF(OR(E5=1, COLUMN()=MATCH(1, OFFSET($E$5,0,MAX(IF($E$5:F5=1,COLUMN($E$5:F5),0))-COLUMN($E$5)+1,1,COLUMNS($E$5:$Z$5)-MAX(IF($E$5:F5=1,COLUMN($E$5:F5),0))+COLUMN($E$5)),0)+MAX(IF($E$5:F5=1,COLUMN($E$5:F5),0))-1), SUM(OFFSET($E$6,0,MAX(IF($E$5:F5=1,COLUMN($E$5:F5),0))-COLUMN($E$6),1,MATCH(1, OFFSET($E$5,0,MAX(IF($E$5:F5=1,COLUMN($E$5:F5),0))-COLUMN($E$5)+1,1,COLUMNS($E$5:$Z$5)-MAX(IF($E$5:F5=1,COLUMN($E$5:F5),0))+COLUMN($E$5)),0)), OFFSET(F6,0,-1))

注：输入后需按Ctrl+Shift+Enter触发数组计算

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