如何用Query与Pivot自动生成Google Sheets销售付款关联透视表？

一、创建动态关联数据表 #

首先生成包含所有销售记录及其对应付款（支持多笔付款）的动态数据表，后续基于此创建透视表。假设：

• Sales表：列A为SaleID，列B为SaleDate，列C为SaleAmount
• Payments表：列A为PaymentID，列B为SaleID，列C为PaymentDate，列D为PaymentAmount

在新工作表（比如命名为Combined）的A1单元格输入以下公式：

=LET(
  sales, FILTER(Sales!A:C, Sales!A:A<>""),
  payments, FILTER(Payments!B:D, Payments!B:B<>""),
  saleIDs, INDEX(sales,,1),
  REDUCE({"SaleID", "SaleDate", "SaleAmount", "PaymentID", "PaymentDate", "PaymentAmount"}, saleIDs, LAMBDA(acc, id,
    LET(
      saleData, FILTER(sales, INDEX(sales,,1)=id),
      paymentData, FILTER(payments, INDEX(payments,,1)=id),
      IF(ROWS(paymentData)=0,
        VSTACK(acc, HSTACK(saleData, {"", "", ""})),
        VSTACK(acc, HSTACK(REPT(saleData, ROWS(paymentData)), paymentData))
      )
    )
  ))
)

公式说明：

• FILTER自动排除空行，只处理有效数据
• REDUCE遍历每条销售记录，匹配对应所有付款记录
• 无付款的销售自动填充空付款字段；有多笔付款的销售，会将销售信息重复对应次数后与付款数据拼接
• 新增/修改销售或付款记录时，数据表会实时更新

二、创建自动更新的透视表 #

1. 选中Combined表的全部数据（含表头）
2. 点击菜单栏「数据」→「数据透视表」，选择透视表放置位置
3. 在右侧编辑器配置字段：
   • 行：添加SaleID（可嵌套SaleDate等字段）
   • 值：添加PaymentAmount（可选「求和」或「显示原值」）
   • 列：若需按付款顺序展示，先添加付款序号辅助列（见下方），再将序号加入列字段
4. 开启自动刷新：点击编辑器右上角「设置」→ 勾选「打开文件时刷新数据」

可选：添加付款序号辅助列

若要在透视表中按顺序展示多笔付款，在Payments表的E2单元格输入：

=ARRAYFORMULA(IF(Payments!B2:B<>"", COUNTIFS(Payments!B2:B, Payments!B2:B, Payments!A2:A, "<="&Payments!A2:A), ""))

此公式会为每笔销售的付款生成递增序号（1、2、3...），方便透视表按顺序排列付款项。

三、常见问题解决 #

• 空行处理：公式通过FILTER(Sales!A:C, Sales!A:A<>"")自动过滤空行，确保数据源干净
• 多笔付款适配：通过REPT(saleData, ROWS(paymentData))将销售信息重复对应次数，实现一笔销售对应多笔付款的行展开
• 自动更新：动态数据表实时响应源表修改，透视表开启「打开文件时刷新」后自动同步最新数据

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