请求修复Google Sheets复杂Query公式，实现多条件数据提取

结论：单个Query无法实现全部需求 #

Query函数的分组聚合能力有限，无法同时处理分组取对应字段（如最晚日期的Status）、字符串提取、条件映射这些复杂逻辑，最优方案是组合使用ARRAYFORMULA、BYROW、XLOOKUP等函数实现。

最优实现方案 #

以下公式基于你的数据源txn表，可直接在新工作表的A1单元格输入（先手动添加表头：email*, Contact*, Product name*, Billing amt, Status, Last billed*, Cycle, Charges*）：

完整数组公式 #

=ARRAYFORMULA(
  LET(
    // 筛选符合产品条件的唯一邮箱列表
    unique_emails, UNIQUE(FILTER(txn!E:E, (txn!G:G="Not Your Average Membership")+(txn!G:G="The Not So Average Membership"))),
    // 计算每个邮箱的最晚账单日期
    max_dates, BYROW(unique_emails, LAMBDA(email, MAXIFS(txn!F:F, txn!E:E=email))),
    // 生成对应字段
    contact, BYROW(unique_emails, LAMBDA(email, XLOOKUP(1, (txn!E:E=email)*(txn!F:F=MAXIFS(txn!F:F, txn!E:E=email)), txn!D:D, "N/A"))),
    product, BYROW(unique_emails, LAMBDA(email, XLOOKUP(1, (txn!E:E=email)*(txn!F:F=MAXIFS(txn!F:F, txn!E:E=email)), txn!G:G, "N/A"))),
    billing_amt, BYROW(unique_emails, LAMBDA(email, MAXIFS(txn!H:H, txn!E:E=email, txn!C:C="charge"))),
    status, BYROW(max_dates, LAMBDA(date, XLOOKUP(date, txn!F:F, txn!C:C, "N/A", 0, 1))),
    cycle, BYROW(unique_emails, LAMBDA(email, 
      LET(pricing_option, XLOOKUP(1, (txn!E:E=email)*(txn!F:F=MAXIFS(txn!F:F, txn!E:E=email)), txn!I:I, ""),
        IF(REGEXMATCH(pricing_option, "monthly"), "monthly", IF(REGEXMATCH(pricing_option, "One-time payment"), "annual", "N/A"))
      )
    )),
    charges, BYROW(unique_emails, LAMBDA(email, REGEXEXTRACT(XLOOKUP(1, (txn!E:E=email)*(txn!F:F=MAXIFS(txn!F:F, txn!E:E=email)), txn!B:B, ""), "-(.*)"))),
    // 合并所有字段输出
    HSTACK(unique_emails, contact, product, billing_amt, status, max_dates, cycle, charges)
  )
)

公式逻辑拆解 #

• unique_emails: 筛选出购买指定两款会员产品的所有唯一邮箱，确保email*唯一
• max_dates: 计算每个邮箱对应的最晚账单日期，用于匹配Status和Last billed*
• contact/product: 通过XLOOKUP匹配最晚账单日期对应的full_name和relevant_item_name
• billing_amt: 提取该邮箱所有event=charge记录中的total金额（取最大值，若每个邮箱仅一条charge记录，结果一致）
• status: 通过最晚账单日期反向匹配对应的event值（charge/rebill）
• cycle: 匹配最晚账单记录的relevant_item_pricing_option，通过正则判断输出monthly/annual
• charges: 用正则表达式提取order_id中-后的部分

替代简化方案（分步处理） #

如果需要分步验证数据，可先在辅助表用Query筛选基础数据：

=QUERY(txn!A:I, "SELECT E,D,G,H,C,F,I,B WHERE G='Not Your Average Membership' OR G='The Not So Average Membership'", 1)

再基于辅助表的数据，用上述UNIQUE+BYROW+XLOOKUP逻辑处理后续字段。

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