求Excel公式：按从上到下累加匹配前缀值的单元格数量

需求说明 #

要统计列区域中，从上到下累加和首次大于或等于指定前缀值时对应的单元格数量；如果所有单元格的总和仍小于前缀值，则返回区域的总单元格数。

适用Excel 365/2021及以上版本的公式 #

假设：

• 指定前缀值存于单元格B1
• 目标数据区域为A1:A5（可根据实际修改）

公式：

=IF(SUM(A1:A5)<=B1,COUNTA(A1:A5),MATCH(TRUE,SCAN(0,A1:A5,LAMBDA(a,b,a+b))>=B1,0))

公式拆解

1. SCAN(0,A1:A5,LAMBDA(a,b,a+b))：生成区域的累积和数组，示例中会得到[23, 58, 94, 161, 241]
2. MATCH(TRUE,...>=B1,0)：定位数组中第一个大于等于前缀值的元素位置，也就是首次达标时的单元格个数
3. IF(SUM(A1:A5)<=B1,...)：判断区域总和是否小于等于前缀值，若是则返回区域的非空单元格总数（用COUNTA兼容非连续/含空值的情况，若为连续区域也可替换为ROWS(A1:A5)）

适用旧版Excel（无LAMBDA/SCAN支持）的数组公式 #

=IF(SUM(A1:A5)<=B1,COUNTA(A1:A5),MATCH(TRUE,MMULT(--(ROW(A1:A5)>=TRANSPOSE(ROW(A1:A5))),A1:A5)>=B1,0))

注：输入完成后需按Ctrl+Shift+Enter触发数组计算（旧版Excel）

公式验证

• 示例场景：前缀值88，区域A1:A5为23、35、36、67、80
  累积和数组为[23,58,94,161,241]，第一个≥88的是第3个值94，公式返回3
• 前缀值250（大于总和241），公式返回区域总单元格数5

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