如何使用Excel自动按日期区间筛选任务填充周议程

公式失效核心原因 #

你之前使用的公式报错/匹配不准，基本是以下3个问题导致的：

• 整列引用的空值干扰：直接引用A:A/I:I/J:J整列时，列尾数千个空白单元格会被识别为值0（对应日期1900/1/0），会打乱布尔判断的匹配逻辑，返回大量无效空值或者直接报错。
• 日期格式不统一：如果周议程表第25行的日期是文本格式、或带隐藏时间戳的日期值，和甘特表中纯日期格式的起止时间做大小比较时，会出现判定失效。
• 引用范围未锁定：没有加绝对引用标记的话，拖拽填充公式时引用范围会发生偏移，导致匹配数据源错误。

可直接复用的实现步骤 #

1. 前置格式校验
   选中GanttChart表的I列（开始日期）、J列（截止日期），以及Plan-Semana (2)表第25行的所有周日期单元格，将单元格格式设置为「短日期」；如果有文本格式的日期，选中对应列走「数据-分列」流程，直接点击完成即可批量转为可计算的数值型日期。
2. 输入匹配公式
   在Plan-Semana (2)表的C28单元格（对应周一列的第一个任务填充位）输入以下公式：

   =FILTER(
       'GanttChart'!$A$2:$A$1000,
       (C$25>='GanttChart'!$I$2:$I$1000)*
       (C$25<='GanttChart'!$J$2:$J$1000)*
       ('GanttChart'!$A$2:$A$1000<>""),
       "no results"
   )
   

   公式调整点说明：
   • 将整列引用替换为$A$2:$A$1000的固定范围（可根据实际任务总量把1000调整为更大的数值，只要覆盖所有任务行即可），避开表头和空白行干扰
   • 新增任务名称非空判断，自动过滤无任务名的无效行
   • 所有数据源范围加$做绝对引用，拖拽时不会偏移
3. 填充生效
   • 如果你用的是Microsoft 365/2021及以上支持动态数组的Excel版本，输入公式后会自动溢出当前日期下的所有匹配任务，直接把C28的公式向右拖拽到T列，即可完成整周所有日期的任务匹配
   • 如果你用的是旧版Excel，输入公式后需要按Ctrl+Shift+Enter三键确认数组公式，再向下拖拽足够行数（比如拖到第100行，覆盖单天最多的任务量），再向右拖拽到T列即可。

校验小技巧：如果公式返回异常值，可以在空白单元格输入=ISNUMBER(待校验日期单元格)，返回FALSE就说明对应单元格的日期是不可计算的文本格式，转格式后即可恢复正常。

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