如何在Google Sheets中用嵌套查询实现课时账单与消耗汇总？

需求回顾 #

需要基于两个工作表生成客户课时汇总表：

• CustomerInvoices：统计「Group training」类型的已开票课时（按客户ID、姓名分组求和）
• CalendarEvents：统计「Group training」且未取消（cancelled=FALSE）的已消耗课时（按客户ID计数）
• 最终计算剩余课时（已开票课时 - 已消耗课时）

可行公式实现 #

直接使用ARRAYFORMULA结合QUERY与COUNTIFS，将已消耗、剩余课时整合到已有的开票统计结果中：

=ARRAYFORMULA(
  QUERY(
    {
      QUERY(
        CustomerInvoices,
        "SELECT E, F, SUM(G) WHERE D = 'Group training' GROUP BY E, F LABEL SUM(G) 'Invoiced'",
        1
      ),
      COUNTIFS(
        CalendarEvents!B, INDIRECT("A2:A"&COUNTA(A:A)),
        CalendarEvents!D, "Group training",
        CalendarEvents!G, FALSE
      ),
      INDIRECT("C2:C"&COUNTA(A:A)) - COUNTIFS(
        CalendarEvents!B, INDIRECT("A2:A"&COUNTA(A:A)),
        CalendarEvents!D, "Group training",
        CalendarEvents!G, FALSE
      )
    },
    "SELECT * LABEL Col4 'Consumed', Col5 'Remaining'",
    1
  )
)

公式说明 #

1. 内层QUERY：复用你已实现的已开票课时统计逻辑，通过LABEL给求和列命名为Invoiced，参数1表示保留表头。
2. 已消耗课时列：通过COUNTIFS动态匹配每个客户ID，统计CalendarEvents中符合「Group training + 未取消」条件的课程数量。
3. 剩余课时列：用已开票课时减去已消耗课时，自动计算剩余值。
4. 外层QUERY：合并所有数据列后，给新增的两列命名为Consumed和Remaining，参数1保留完整表头。

简化替代方案（分步骤关联） #

如果觉得合并公式复杂，可拆分操作：

1. 先单独生成已消耗课时统计：

=QUERY(
  CalendarEvents,
  "SELECT B, COUNT(B) WHERE D = 'Group training' AND G = FALSE GROUP BY B LABEL B 'CustomerID', COUNT(B) 'Consumed'",
  1
)

2. 再关联两个统计结果并计算剩余：

=ARRAYFORMULA(
  {
    QUERY(CustomerInvoices, "SELECT E, F, SUM(G) WHERE D = 'Group training' GROUP BY E, F LABEL E 'CustomerID', F 'CustomerName', SUM(G) 'Invoiced'", 1),
    IFERROR(VLOOKUP(A2:A, 已消耗统计单元格范围, 2, FALSE), 0),
    C2:C - IFERROR(VLOOKUP(A2:A, 已消耗统计单元格范围, 2, FALSE), 0)
  }
)

最后手动给新增列添加Consumed和Remaining表头即可。

结果验证 #

使用测试数据后，会生成你期望的汇总结果：

CustomerID  CustomerName         Invoiced   Consumed    Remaining
CHUCK       Chuck Norris            20         2           18
JEAN        Jean Claude Vandamme    5          1           4

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