Google Sheets QUERY函数按月统计的格式与排序问题求解

问题根因 #

• 原公式直接PIVOT A引用原始日期列，会把每条记录的具体日期作为独立列维度，不会自动按整月聚合，导致列头出现原始日期值拼接的异常格式
• QUERY函数的PIVOT列默认按字段值升序排列，原生语法不支持直接在PIVOT子句中修改排序方向
• 仅用MONTH()函数做日期筛选未匹配年份维度，跨年统计时会把不同年份的同月份数据错误合并

可直接复用的修复公式 #

将以下公式粘贴到统计表的起始单元格即可，公式会自动以A2单元格的日期为截止月，统计最近4个月的司机数量，源数据更新时结果自动同步：

=LET(
  raw_res,QUERY(
    ARRAYFORMULA(IF(Data!A:A="",,{EOMONTH(Data!A:A,0),Data!B:B})),
    "SELECT Col2, Count(Col2)
     WHERE Col2 != '' 
       AND Col1 <= DATE '"&TEXT(EOMONTH(A2,0),"yyyy-MM-dd")&"'
       AND Col1 >= DATE '"&TEXT(EOMONTH(EDATE(A2,-3),0),"yyyy-MM-dd")&"'
     GROUP BY Col2
     PIVOT Col1
     ORDER BY Col2 ASC
     LABEL Col2 'Drivers'",
    1
  ),
  col_count,COLUMNS(raw_res),
  // 反转月份列，实现最新月份居左
  reverse_col,CHOOSECOLS(raw_res,1,SORT(SEQUENCE(col_count-1,1,2,1),1,FALSE)),
  // 格式化列头为「月份 年份」格式
  ARRAYFORMULA(IF(ROW(reverse_col)=1,IF(COLUMN(reverse_col)=1,"Drivers",TEXT(INDEX(reverse_col,1,COLUMN(reverse_col)),"MMMM YYYY")),reverse_col))
)

核心改动说明 #

• 预处理源数据时用EOMONTH()把所有日期统一转换为对应月份的最后一天，从根源避免同月份不同日期被拆分为独立列的问题，同时日期范围直接按整月边界比对，彻底解决跨年统计的匹配错误
• 用CHOOSECOLS()搭配SORT()将PIVOT生成的升序月份列反转，无需修改QUERY逻辑即可实现最新月份居左、更早月份居右的排序要求
• 最后统一对表头行的日期值做格式转换，直接输出MMMM YYYY格式的列名（如May 2022），不会出现原始日期值拼接的异常显示
• 所有引用为动态范围，新增、修改Data工作表的源数据，或是调整A2单元格的基准截止日期，统计结果都会自动刷新

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