Google Sheets 中如何让 SUMPRODUCT 随表单提交自动逐行计算？

2026-11-04数据阿明

可以直接用公式，无需先写 Google Apps Script。要让 SUMPRODUCT 对每一行分别计算，可以用 MAP 遍历各行，统计当前行之前的记录。仅在原公式外套一层 ARRAYFORMULA，不能自动得到这种逐行计算结果。

原公式统计的是什么？

以第 10 行为例，公式检查第 1～9 行，统计同时满足以下条件的记录数：

F 列等于当前行的 F10。
R 列等于 "Approved"。
S 列大于 AllocationFreeBooking。

公式没有对 S 列求和，而是将每个满足全部条件的行计为 1，再相加。改写后的公式需要为每一行返回一个结果，并且只检查当前行之前的记录。

原始粘贴内容中的 \* 和 \> 如果只是文本转义，输入 Google Sheets 时应分别写成 * 和 >。反斜杠不属于公式语法。

用 MAP 为每一行执行 SUMPRODUCT

假设第 1 行是表头，数据从第 2 行开始，AllocationFreeBooking 是已经定义好的单个阈值。在存放结果的空列第 2 行输入：

=MAP(
  F2:F1000,
  ROW(F2:F1000),
  LAMBDA(currentF,currentRow,
    IF(
      currentF="",
      "",
      SUMPRODUCT(
        ($F$1:INDEX($F:$F,currentRow-1)=currentF)*
        ($R$1:INDEX($R:$R,currentRow-1)="Approved")*
        ($S$1:INDEX($S:$S,currentRow-1)>AllocationFreeBooking)
      )
    )
  )
)

其中，1000 是示例处理范围，可按实际数据量调整。公式会向下展开结果；在处理范围内，原本为空的 F 单元格填入值后，对应行就会参与计算。

公式中：

MAP 同时取得当前行的 F 值和行号。
INDEX(...,currentRow-1) 将每个统计区间的终点设为上一行。
SUMPRODUCT 为当前行返回一个计数。

例如，处理第 10 行时，三个统计区间分别是 F1:F9、R1:R9 和 S1:S9，与原公式一致。处理第 11 行时，区间终点会变成第 10 行。

条件之间的乘法会将 TRUE/FALSE 转成 1/0，因此不需要再为每个条件单独添加 *1。

为什么单独加 ARRAYFORMULA 不够？

SUMPRODUCT 能处理数组，但它会将参与运算的数组汇总成一个值。ARRAYFORMULA 不会自动让它对每一行重新确定历史范围并分别计算。

这里由 MAP 负责逐行调用，SUMPRODUCT 负责每次调用中的条件统计，无需再套 ARRAYFORMULA。

使用时需要注意什么？

公式所在单元格下方的展开区域必须留空，否则可能出现数组结果无法展开的错误。不要在这一区域逐行填写公式或其他内容。

示例使用 currentF="" 跳过空行，前提是有效记录的 F 列一定有值。如果 F 列允许为空，应改用能判断记录是否存在的字段作为空行判断依据。

示例保留了原公式从第 1 行开始检查的范围。如果只希望检查第 2 行起的数据，还需要处理第一条数据没有历史记录的情况，避免直接生成起点晚于终点的区间。

AllocationFreeBooking 应能解析为单个可比较的阈值。如果尚未定义，需要先建立命名范围，或将其替换成实际阈值单元格的绝对引用。S 列中的值也应与阈值使用可比较的数据类型。

这种写法会反复扫描各行之前的记录，数据量较大时可能变慢。可以先设置合理的处理范围，在数据接近范围上限时扩展；如果实际响应表的插行方式影响公式位置或展开区域，再考虑用表单提交触发器写入计算结果。