Google Sheets中基于SCAN函数扩展双液体罐杯填充匹配需求

问题概述 #

有若干不同容量的空罐子，以及一批标注了A/B液体类型的小容量杯子。需按顺序将同类型液体倒入罐子，为每个杯子匹配对应的「开始填充罐子」和「结束填充罐子」。

单液体场景已实现公式 #

仅处理A液体时，已成功实现以下公式，可返回每个A杯子的起止罐子：

=ARRAYFORMULA(LET(x,XLOOKUP(SCAN(,{1;ARRAY_CONSTRAIN(A2:A,ROWS(A2:A)-1,1)},LAMBDA(a,c,a+c)),SCAN(,{F2:F},LAMBDA(a,c,a+c)),{E2:E},,1,2),y,XLOOKUP(SCAN(,A2:A,LAMBDA(a,c,a+c)),SCAN(,{F2:F},LAMBDA(a,c,a+c)),{E2:E},,1,2),IF(A2:A="",,HSTACK(x,y))))

当前遇到的问题 #

扩展到双液体场景时，尝试以下方法未完全解决：

• 在SCAN的LAMBDA中添加液体类型条件，无法实现分组累加
• 用MMULT替代SCAN仅完成了「结束填充罐子」列，「开始填充罐子」列无法实现

解决方案 #

通过分组前缀和计算区分A、B液体的累计容量，再匹配罐子区间，公式如下：

=ARRAYFORMULA(LET(
  // 定义数据列（请根据实际表格调整列号）
  type_col, C2:C,    // 液体类型列（A/B）
  vol_col, B2:B,     // 杯子容量列
  jar_id_col, E2:E,  // 罐子ID列
  jar_cap_col, F2:F, // 罐子容量列
  
  // 计算罐子的累计容量区间（起始点为0）
  jar_cum_sum, SCAN(0, jar_cap_col, LAMBDA(acc, cap, acc + cap)),
  jar_start_points, VSTACK(0, jar_cum_sum),
  
  // 分别计算A、B液体的累计容量（前缀和）
  cum_A, SCAN(0, IF(type_col="A", vol_col, 0), LAMBDA(acc, vol, acc + vol)),
  cum_B, SCAN(0, IF(type_col="B", vol_col, 0), LAMBDA(acc, vol, acc + vol)),
  
  // 计算每个杯子的起始/结束累计量
  start_cum, INDEX(VSTACK(0, IF(type_col="A", cum_A, cum_B)), 1:ROWS(cum_A)),
  end_cum, IF(type_col="A", cum_A, cum_B),
  
  // 匹配对应的起止罐子
  start_jar, XLOOKUP(start_cum, jar_start_points, jar_id_col, , 1, 2),
  end_jar, XLOOKUP(end_cum, jar_start_points, jar_id_col, , 1, 2),
  
  // 返回结果，跳过空行
  IF(vol_col="",, HSTACK(start_jar, end_jar))
))

关键逻辑说明 #

1. 分组累加：通过IF(type_col="A", vol_col, 0)筛选对应液体的容量，用SCAN分别计算A、B液体的前缀和，实现独立累计
2. 区间匹配：
   • jar_start_points存储每个罐子的起始累计容量（从0开始）
   • 开始罐子：匹配当前杯子倒入前的累计量对应的罐子
   • 结束罐子：匹配当前杯子倒入后的累计量对应的罐子
3. 空值过滤：通过IF(vol_col="",, ...)跳过无容量的空行

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