合并连续日期假期并判断额外假期资格的Excel公式需求

一、合并同一员工的连续假期 #

假设原始数据结构：

• A列：User_ID（员工ID）
• B列：Start_Date（假期开始日期）
• C列：End_Date（假期结束日期）

方法1：适用于Excel 365/2021（支持动态数组函数） #

1. 添加辅助列D（标记连续假期组）
   在D2单元格输入公式，下拉填充：

   =IF(AND(A2=A1,C1+1=B2),"",1)
   

   逻辑：如果当前行员工ID与上一行相同，且上一行假期结束日期+1等于当前行开始日期，说明是连续假期，标记为空；否则标记为1，作为新假期组的起始。

2. 生成假期组ID（辅助列E）
   在E2单元格输入公式，自动填充所有行：

   =SCAN(0,D2:D100,LAMBDA(a,b,IF(b="",a,a+1)))
   

   逻辑：通过SCAN累计分组ID，连续假期会继承同一ID，新假期组ID自动递增。

3. 批量生成合并后的假期数据
   在新工作表中输入以下公式，直接生成所有员工的合并后假期记录：

   =LET(
       raw_data, 原表!A2:C100,
       users, UNIQUE(INDEX(raw_data,,1)),
       groups, UNIQUE(HSTACK(INDEX(raw_data,,1),原表!E2:E100)),
       BYROW(groups,LAMBDA(x,
           HSTACK(
               INDEX(x,1),
               MINIFS(INDEX(raw_data,,2),INDEX(raw_data,,1),INDEX(x,1),原表!E2:E100,INDEX(x,2)),
               MAXIFS(INDEX(raw_data,,3),INDEX(raw_data,,1),INDEX(x,1),原表!E2:E100,INDEX(x,2))
           )
       ))
   )
   

   逻辑：用LET简化公式，通过UNIQUE提取唯一员工+假期组，再用MINIFS/MAXIFS提取每组的最早开始、最晚结束日期。

方法2：适用于旧版Excel（无动态数组） #

1. 添加辅助列D（生成假期组ID）
   在D2单元格输入数组公式（按Ctrl+Shift+Enter确认），下拉填充：

   =IF(ROW(A2)=2,1,IF(AND(A2=A1,C1+1=B2),D1,D1+1))
   

2. 提取合并后的假期数据

   • 先提取唯一的User_ID+假期组组合到新表；
   • 新表B列（合并后开始日期）输入数组公式：

     =MIN(IF((原表!A:A=新表!A2)*(原表!D:D=新表!D2),原表!B:B))
     

   • 新表C列（合并后结束日期）输入数组公式：

     =MAX(IF((原表!A:A=新表!A2)*(原表!D:D=新表!D2),原表!C:C))
     

二、判断是否符合额外假期发放条件 #

假设：

• 合并后的假期数据在新表的A（User_ID）、B（Start_Date）、C（End_Date）列；
• 周中假日（非周末的法定假日）存放在$F$2:$F$100区域。

在新表D列输入以下公式，判断是否符合条件：

=AND(
    COUNTIFS($F$2:$F$100,">="&B2,$F$2:$F$100,"<="&C2,WEEKDAY($F$2:$F$100,2),"<=5")>0,
    OR(
        AND(NETWORKDAYS.INTL(B2-1,B2-1,"0000011",$F$2:$F$100)=1,NETWORKDAYS.INTL(C2+1,C2+2,"0000011",$F$2:$F$100)=2),
        AND(NETWORKDAYS.INTL(B2-2,B2-1,"0000011",$F$2:$F$100)=2,NETWORKDAYS.INTL(C2+1,C2+1,"0000011",$F$2:$F$100)=1)
    )
)

逻辑拆解：

1. 判断假期内是否包含周中假日：用COUNTIFS统计假期区间内属于周中（周一至周五，WEEKDAY(...,2)<=5）的假日数量，大于0则满足。
2. 判断假期前后工作日条件：
   • 第一种情况：假日前1天是工作日（NETWORKDAYS.INTL返回1），且假期后2天都是工作日（返回2）；
   • 第二种情况：假日前2天都是工作日（返回2），且假期后1天是工作日（返回1）；
     两种情况满足其一即可。
3. 最终用AND确保两个条件同时满足，返回TRUE则符合额外假期发放条件，FALSE则不符合。

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