Google Sheets Query函数问题：统计时无法显示无结果月份的零值

要实现无数据月份显示0的需求，核心思路是先生成完整的月份序列，再将QUERY的统计结果与这个序列匹配，缺失项补0。以下是具体实现步骤和公式：

1. 调整原QUERY函数，按月份统计 #

原函数按具体日期分组，需要修改为按月份分组，同时返回可识别的月份标识（比如每月第一天）：

=QUERY('2022'!A1:Z,
"select date(year(B), month(B), 1), count(E)
where E= 'Burgers' and B >= date """&TEXT(D2,"yyyy-MM-dd")&""" and B <= date """&TEXT(D3,"yyyy-MM-dd")&"""
group by date(year(B), month(B), 1)
order by date(year(B), month(B), 1)
label date(year(B), month(B), 1) '月份', count(E) '汉堡数量'")

2. 生成完整月份序列并匹配结果 #

使用LET函数整合逻辑，先生成目标时间范围内的所有月份，再通过VLOOKUP匹配统计结果，缺失项自动补0：

=ARRAYFORMULA(
  LET(
    start_date, D2,
    end_date, D3,
    // 生成时间范围内的所有月份（每月第一天）
    all_months, DATE(YEAR(start_date), MONTH(start_date)+SEQUENCE(DATEDIF(start_date, end_date, "M")+1,1,0), 1),
    // 获取按月份统计的汉堡数量
    query_data, QUERY('2022'!A1:Z,
      "select date(year(B), month(B), 1), count(E)
       where E= 'Burgers' and B >= date """&TEXT(start_date,"yyyy-MM-dd")&""" and B <= date """&TEXT(end_date,"yyyy-MM-dd")&"""
       group by date(year(B), month(B), 1)"),
    // 匹配完整月份与统计数据，缺失项补0
    matched_data, IFERROR(VLOOKUP(all_months, query_data, {1,2}, FALSE), {all_months, 0}),
    // 添加表头并输出结果
    VSTACK({"月份", "汉堡数量"}, matched_data)
  )
)

3. 可选：调整月份显示格式 #

如果需要将月份显示为中文格式（如「2022年01月」），只需修改月份生成和QUERY的对应部分：

=ARRAYFORMULA(
  LET(
    start_date, D2,
    end_date, D3,
    // 生成中文格式的完整月份
    all_months, TEXT(DATE(YEAR(start_date), MONTH(start_date)+SEQUENCE(DATEDIF(start_date, end_date, "M")+1,1,0), 1), "YYYY年MM月"),
    // 修改QUERY返回中文月份
    query_data, QUERY('2022'!A1:Z,
      "select text(date(year(B), month(B), 1), 'YYYY年MM月'), count(E)
       where E= 'Burgers' and B >= date """&TEXT(start_date,"yyyy-MM-dd")&""" and B <= date """&TEXT(end_date,"yyyy-MM-dd")&"""
       group by date(year(B), month(B), 1)"),
    // 匹配并补0
    matched_data, IFERROR(VLOOKUP(all_months, query_data, {1,2}, FALSE), {all_months, 0}),
    VSTACK({"月份", "汉堡数量"}, matched_data)
  )
)

关键说明 #

• DATEDIF(start_date, end_date, "M")计算起始日期到结束日期的月份差，确保生成的月份序列覆盖整个时间范围。
• IFERROR(VLOOKUP(...), {all_months, 0})实现了「找不到匹配项时显示月份和0」的逻辑。

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