Excel如何用公式批量标记指定日期区间为休假？

现有公式回顾 #

你当前使用的假日判断公式为：

=IFERROR(VLOOKUP(E3;Sheet1!$Q$22:$R$35;2;FALSE);0)

返回值定义：S=工作日、F=公共假日、FS=周末，现有公共假日表仅14条记录，维护成本低。

以下是三种无需手动逐个录入的批量标记方案：

方案1：修改原有公式，直接嵌入区间判断 #

直接在原有公式前加入日期区间判断逻辑，优先标记目标区间为休假（F），其余日期沿用原有判断规则：

=IF(AND(E3>=DATE(2024,10,20),E3<=DATE(2024,11,3)),"F",IFERROR(VLOOKUP(E3;Sheet1!$Q$22:$R$35;2;FALSE),0))

优化点：动态区间设置 #

如果需要频繁调整目标日期区间，可将起止日期存入固定单元格（例如Sheet1!$A$1=开始日期，Sheet1!$A$2=结束日期），公式修改为：

=IF(AND(E3>=Sheet1!$A$1,E3<=Sheet1!$A$2),"F",IFERROR(VLOOKUP(E3;Sheet1!$Q$22:$R$35;2;FALSE),0))

后续只需修改A1和A2的日期即可批量更新标记。

方案2：动态数组批量生成日期+标记（适用于Excel 365/2021） #

利用Excel动态数组功能，一键生成整个目标区间的日期及对应休假标记，无需下拉公式：

=LET(
    StartDate, DATE(2024,10,20),
    EndDate, DATE(2024,11,3),
    DateList, SEQUENCE(EndDate-StartDate+1,1,StartDate),
    OriginalMark, IFERROR(VLOOKUP(DateList,Sheet1!$Q$22:$R$35,2,FALSE),0),
    FinalMark, IF((DateList>=StartDate)*(DateList<=EndDate),"F",OriginalMark),
    HSTACK(DateList, FinalMark)
)

输入公式后会自动扩展生成两列数据：第一列为区间内所有日期，第二列为对应的休假标记。

方案3：批量更新公共假日表（永久生效） #

若希望将目标区间的日期永久加入现有公共假日表，可通过以下步骤批量录入：

1. 在空白单元格生成区间日期序列：

   =SEQUENCE(DATE(2024,11,3)-DATE(2024,10,20)+1,1,DATE(2024,10,20))
   

2. 选中生成的日期序列，按Ctrl+C复制，右键粘贴到Sheet1!$Q$22:$Q$35的空白行（接原有记录后），选择粘贴值。
3. 选中对应R列的空白行，输入"F"后按Ctrl+Enter，批量填充休假标记。
   完成后原有VLOOKUP公式会自动识别这些日期为公共假日。

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