如何在Google Sheets/Excel中实现项目记录回推3个工作日（跳过周末）

我们有一份记录同事参与项目日期的电子表格，包含日期列和多个项目列：当有人参与某项目时，对应项目单元格会填写X。

现在需要实现将项目列的记录往回追溯3个工作日（自动跳过周末），例如周五参与项目，则周二至周五均视为参与项目，示例如下：

规则说明 #

• 2023-01-05的项目A列有X（或TRUE），因中间无周末，故2023-01-02至2023-01-05均填充X；
• 2023-01-10有X，往回追溯时遇到周末，因此需填充2023-01-10、2023-01-09、2023-01-06、2023-01-05。

此前尝试过新增三列分别检查前1、2、3天的单元格，再用OR函数生成结果，但这种方法无法处理周末的情况。

Google Sheets 方案 #

假设日期列在A列，项目A数据在C列，结果列从D2开始计算，可使用以下公式：

=IF(ISBLANK(A2),"",IF(COUNTIFS($A$2:$A,"<="&A2,$A$2:$A,">="&WORKDAY(A2,-3),$C$2:$C,"X")>0,"X",""))

如果需要一键填充整列（无需下拉），可以用数组公式：

=ARRAYFORMULA(IF(A2:A="","",IF(COUNTIFS(A$2:A,"<="&A2:A,A$2:A,">="&WORKDAY(A2:A,-3),C$2:C,"X")>0,"X","")))

Excel 方案 #

普通版本（下拉填充） #

假设数据从第2行开始，日期列A，项目A列C，在D2输入以下公式后下拉：

=IF(A2="","",IF(COUNTIFS($A$2:$A$11,"<="&A2,$A$2:$A$11,">="&WORKDAY(A2,-3),$C$2:$C$11,"X")>0,"X",""))

动态数组版本（Excel 365及以上） #

输入一次即可自动填充整列：

=IF(A2:A="","",IF(COUNTIFS(A$2:A,"<="&A2:A,A$2:A,">="&WORKDAY(A2:A,-3),C$2:C,"X")>0,"X",""))

核心逻辑说明 #

1. WORKDAY(A2, -3)：计算当前日期往前推3个工作日的日期，自动跳过周末；
2. COUNTIFS：统计同时满足三个条件的记录数：
   • 日期≤当前行日期
   • 日期≥当前行日期往前推3个工作日的日期
   • 对应项目列的值为X
3. 若统计结果大于0，说明当前日期在某个X记录的追溯区间内，填充X，否则为空。

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