Google Sheets QUERY函数问题：按月筛选PROJECTED为TRUE的最大日期对应余额

我在Google Sheets中有包含DATE、PROJECTED、BALANCE列的数据源，需求是用QUERY函数获取每个月内PROJECTED为TRUE的最大日期对应的BALANCE，预期得到1、2、3月的对应数据。

尝试过程中遇到以下问题：

• 编写的子QUERY能返回三个月份的最大日期，但用TEXT格式化后仅返回1月日期
• 将格式化后的结果代入主QUERY后，最终仅显示1月数据，2、3月数据缺失

相关公式如下：

初始公式 #

=QUERY('2025'!A2:C,
"SELECT MONTH(Col1)+1, MAX(Col1), SUM(Col3)
WHERE Col2=TRUE  
GROUP BY MONTH(Col1)
LABEL MONTH(Col1)+1 'Month', MAX(Col1) 'Max Date', Sum(Col3) 'Balance'")

子QUERY #

=QUERY('2025'!A2:C,
"SELECT MAX(Col1)
WHERE Col2=TRUE
GROUP BY MONTH(Col1)
LABEL MAX(Col1) '' ")

格式化后的公式 #

=TEXT(QUERY('2025'!A2:C,
"SELECT MAX(Col1)
WHERE Col2=TRUE
GROUP BY MONTH(Col1)
LABEL MAX(Col1) '' "),
"yyyy-MM-dd")

最终组合公式 #

=QUERY('2025'!A2:C,
"SELECT MONTH(Col1)+1, MAX(Col1), SUM(Col3)
WHERE Col2=TRUE  
AND Col1 MATCHES DATE '"&TEXT(QUERY('2025'!A2:C,"SELECT MAX(Col1) WHERE Col2=TRUE GROUP BY MONTH(Col1) LABEL MAX(Col1) '' "),"yyyy-mm-dd")&"'
GROUP BY MONTH(Col1)
LABEL MONTH(Col1)+1 'Month', MAX(Col1) 'Max Date', Sum(Col3) 'Balance'")

1. TEXT函数的数组处理限制：当子QUERY返回多单元格数组时，TEXT函数默认仅处理数组的第一个元素，因此只会输出1月的格式化日期，导致后续匹配条件仅能匹配到1月的数据。
2. MATCHES条件的使用错误：QUERY的MATCHES用于正则匹配单值或固定多值，无法动态匹配子QUERY返回的多日期数组；同时公式中的字符串拼接逻辑存在语法错误，无法正确生成多日期匹配规则。
3. 初始公式的逻辑偏差：初始公式中SUM(Col3)会将当月所有PROJECTED为TRUE的BALANCE求和，而非获取最大日期对应行的BALANCE，不符合需求。

方法一：使用MAP+MAXIFS+XLOOKUP组合（更直观） #

=MAP(UNIQUE(MONTH('2025'!A2:A)+1), LAMBDA(month, 
  LET(
    max_date, MAXIFS('2025'!A2:A, '2025'!B2:B, TRUE, MONTH('2025'!A2:A), month),
    balance, XLOOKUP(max_date, '2025'!A2:A, '2025'!C2:C,,0),
    HSTACK(month, max_date, balance)
  )
))

• 逻辑说明：
  1. UNIQUE(MONTH('2025'!A2:A)+1)提取所有存在有效数据的月份
  2. MAP遍历每个月份，用MAXIFS筛选出该月PROJECTED为TRUE的最大日期
  3. XLOOKUP根据最大日期匹配对应的BALANCE值
  4. HSTACK将月份、最大日期、BALANCE组合成结果行

方法二：修正QUERY关联逻辑（纯QUERY实现） #

=QUERY(
  '2025'!A2:C,
  "SELECT MONTH(Col1)+1, Col1, Col3
   WHERE Col2=TRUE AND Col1 IN (
     SELECT MAX(Col1)
     WHERE Col2=TRUE
     GROUP BY MONTH(Col1)
   )
   ORDER BY MONTH(Col1)",
  1
)

• 逻辑说明：
  1. 子查询SELECT MAX(Col1) WHERE Col2=TRUE GROUP BY MONTH(Col1)先获取各月的最大有效日期
  2. 主QUERY用IN条件筛选出这些日期对应的行
  3. 直接选取对应行的BALANCE，而非求和，最后按月份排序输出

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