Google Sheets中实现数值总和不超过指定值的相邻数据分组方法

核心思路 #

我们需要先给每一行数据分配组号，规则是：相邻行尽量合并，直到加入下一行的数值后总和超过26，就开启新组。然后根据组号聚合名称、计算总和和成员数，最终生成你要的输出表格。

完整公式（新版Google Sheets） #

直接在D2单元格输入以下公式，会自动生成D-G列的所有结果：

=ArrayFormula(LET(
  data, FILTER(A2:B, A2:A<>""),
  names, INDEX(data,,1),
  values, INDEX(data,,2),
  -- 追踪当前组的累计和，超过26则重置为当前值（新组开始）
  sums, SCAN(0, values, LAMBDA(s, v, IF(s + v > 26, v, s + v))),
  -- 生成组ID：当累计和等于当前值时，说明是新组，组号+1
  group_ids, SCAN(1, sums, LAMBDA(g, s, IF(s = INDEX(values,ROW(s)-ROW(values)+1), g + 1, g))) - 1,
  -- 按组号聚合数据
  grouped, GROUPBY(group_ids, {names, values, group_ids}, 
    LAMBDA(x, TEXTJOIN(", ",TRUE,x)),  -- 拼接成员名称
    LAMBDA(x, SUM(x)),                -- 计算数值总和
    LAMBDA(x, COUNT(x))               -- 统计成员数量
  ),
  -- 拼接组序号和聚合结果
  HSTACK(SEQUENCE(ROWS(grouped)), INDEX(grouped,,1), INDEX(grouped,,2), INDEX(grouped,,3))
))

旧版Google Sheets兼容公式（无GROUPBY） #

如果你的Sheets版本没有GROUPBY函数，用QUERY替代：

=ArrayFormula(LET(
  data, FILTER(A2:B, A2:A<>""),
  names, INDEX(data,,1),
  values, INDEX(data,,2),
  sums, SCAN(0, values, LAMBDA(s, v, IF(s + v > 26, v, s + v))),
  group_ids, SCAN(1, sums, LAMBDA(g, s, IF(s = INDEX(values,ROW(s)-ROW(values)+1), g + 1, g))) - 1,
  query_data, HSTACK(group_ids, names, values),
  query_result, QUERY(query_data, "select Col1, GROUP_CONCAT(Col2), SUM(Col3), COUNT(Col2) where Col1 is not null group by Col1 order by Col1", 0),
  HSTACK(SEQUENCE(ROWS(query_result)), INDEX(query_result,,2), INDEX(query_result,,3), INDEX(query_result,,4))
))

公式拆解说明 #

1. LET函数：把重复使用的数据定义成变量，让公式更易读和维护。
2. FILTER：只处理A、B列非空的行，避免空值干扰。
3. SCAN计算累计和：遍历B列数值，累计当前组的总和，一旦加下一个数值超过26，就重置为当前数值（代表新组开始）。
4. SCAN生成组号：根据累计和判断新组，每当累计和等于当前行的数值时，说明是新组的第一行，组号加1。
5. GROUPBY/QUERY聚合：按组号把同一组的名称拼接起来，计算总和和成员数，最后用HSTACK把组序号和聚合结果合并成你要的表格结构。

验证示例 #

对应你给出的示例数据：

• A列：A, B, C, D, E
• B列：12, 13, 14, 13, 14

公式会生成：

完全符合你的需求~

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