Google Sheets Query日期矩阵构建受阻求助

步骤1：确认基础数据格式 #

• 确保DateRange工作表的A列是所有需要覆盖的起始日期候选，B列是所有结束日期候选，格式统一为yyyy-mm-dd，范围包含业务起始月到2026-12-31。
• 检查Agreements表的G（合同起始）、H（合同结束）列日期格式，必须和DateRange保持一致。

步骤2：生成日期矩阵表头 #

在新工作表（或指定区域）设置矩阵的行列标题：

• 第一行（从B1开始）依次粘贴DateRange!B:B的所有结束日期作为列标题
• 第一列（从A2开始）依次粘贴DateRange!A:A的所有起始日期作为行标题

步骤3：核心公式填充匹配标记 #

在B2单元格输入以下数组公式，自动填充整个矩阵：

=ARRAYFORMULA(IF(COUNTIFS(Agreements!$G:$G, "<="&$A2:$A, Agreements!$H:$H, ">="&B$1:$1), "X", ""))

• 原理：通过COUNTIFS统计同时满足「合同起始日期≤当前行候选起始日期」且「合同结束日期≥当前列候选结束日期」的合同数量，只要有匹配就标记X。
• 注意：Excel需选中整个矩阵区域后输入公式，按Ctrl+Shift+Enter执行；Google Sheets直接回车即可自动溢出填充。

步骤4：关联合同详情（可选） #

如果需要在矩阵旁展示对应合同的具体信息，可在矩阵右侧空白区域输入你原有的Query公式：

=QUERY(Agreements!A2:K," select A, C, I, G, H where G is not null order by A asc limit 50000 label A 'Company', C 'Agreement', I 'Billing Amount', G 'AGR Start Date', H 'AGR End Date' ")

该公式会输出整理后的合同列表，方便和矩阵标记对照查看。

优化提示 #

• 若数据量较大，建议把Query的结果存为一个辅助工作表，再用辅助表的数据做COUNTIFS，能提升计算速度。
• 可通过条件格式给标记X的单元格添加底色，让矩阵更直观。

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