Excel如何实现动态范围A#内连续数值簇的累计求和

适用场景 #

• 适配Excel 365/2021及以上支持动态数组、溢出引用的版本
• 输入为带A#标识的动态数组范围，A列存储0/1两类数值
• 公式一次输入后自动适配动态范围的长度变化，无需手动调整引用、无需下拉填充

可用公式 #

在B列与A#范围起始行对齐的单元格（例如A#从A1开始则选中B1），输入以下公式即可自动溢出全量计算结果：

=LET(
  arr, A#,
  row_cnt, ROWS(arr),
  idx, SEQUENCE(row_cnt),
  SCAN(0, idx, LAMBDA(acc,i,
    LET(
      cur_val, INDEX(arr,i),
      IF(i=1, cur_val, IF(cur_val=INDEX(arr,i-1), acc+cur_val, cur_val))
    )
  ))
)

计算逻辑 #

• 先用LET函数定义中间变量，把动态范围A#赋值给arr，统计数组总行数、生成逐行索引序列，减少重复计算提升运行效率
• 用SCAN函数做逐行遍历累计，初始累计值设为0
• 遍历到范围第一行时，直接返回当前单元格数值，作为第一个连续段的累计起始值
• 遍历到非首行时，对比当前行数值和上一行数值：两者相等则在上一次累计值基础上加当前值，属于同一段连续值的累加；两者不相等则重置累计值为当前值，作为新一段连续值的累计起始

效果参考 #

当A列动态范围的数值依次为1,1,1,0,0,1,0,0时，B列自动输出的结果为1,2,3,0,0,1,0,0，完全匹配连续同值段逐行累计的要求：

• 前三行连续1：累计值逐行+1，依次为1、2、3
• 第四、第五行连续0：0累加结果始终为0
• 第六行单独1：累计值重置为1
• 最后两行连续0：累计值重置为0后保持不变

补充说明 #

• 公式不限制输入值必须为0/1，只要是需要按连续同值段逐行累计的场景，不管A列存储什么数值都可以直接用
• A列动态范围后续新增、删除数据时，B列结果会自动同步更新，不需要额外调整公式

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