Excel无VBA实现多条件自定义日期时间轴生成技术问询

前置设置 #

先在工作表中指定输入区域（可根据自身需求调整单元格位置）：

• A1：时间轴起始日期（如2024/1/1）
• A2：时间轴结束日期（如2024/12/31）
• A3：数据展示类型（下拉选择days/weeks/months）
• A4：排除的周末类型（下拉选择Friday & Saturday/Saturday & Sunday/No Days off）
• A5：纳入时间轴的周末日期区域（如F:F，存放加班的周末日期）
• A6：需排除的假期日期区域（如G:G，存放法定假期）

整合式公式（支持三种展示类型） #

在目标单元格（比如C1）输入以下公式，按回车即可生成符合规则的时间轴：

=IFS(
  A3="days", FILTER(SEQUENCE(A2-A1+1,1,A1),((--(WEEKDAY(SEQUENCE(A2-A1+1,1,A1),CHOOSE(MATCH(A4,{"Friday & Saturday","Saturday & Sunday","No Days off"},0),1,2,3))<6)+ISNUMBER(MATCH(SEQUENCE(A2-A1+1,1,A1),A5,0))>0)*(--(ISNA(MATCH(SEQUENCE(A2-A1+1,1,A1),A6,0))+ISNUMBER(MATCH(SEQUENCE(A2-A1+1,1,A1),A5,0))>0)),
  A3="weeks", FILTER(SEQUENCE(ROUNDUP((A2-A1)/7,0),1,A1-(WEEKDAY(A1,2)-1),7),((--(WEEKDAY(SEQUENCE(ROUNDUP((A2-A1)/7,0),1,A1-(WEEKDAY(A1,2)-1),7),CHOOSE(MATCH(A4,{"Friday & Saturday","Saturday & Sunday","No Days off"},0),1,2,3))<6)+ISNUMBER(MATCH(SEQUENCE(ROUNDUP((A2-A1)/7,0),1,A1-(WEEKDAY(A1,2)-1),7),A5,0))>0)*(--(ISNA(MATCH(SEQUENCE(ROUNDUP((A2-A1)/7,0),1,A1-(WEEKDAY(A1,2)-1),7),A6,0))+ISNUMBER(MATCH(SEQUENCE(ROUNDUP((A2-A1)/7,0),1,A1-(WEEKDAY(A1,2)-1),7),A5,0))>0)),
  A3="months", FILTER(EDATE(A1,SEQUENCE(DATEDIF(A1,A2,"m")+1,1,0)),((--(WEEKDAY(EDATE(A1,SEQUENCE(DATEDIF(A1,A2,"m")+1,1,0)),CHOOSE(MATCH(A4,{"Friday & Saturday","Saturday & Sunday","No Days off"},0),1,2,3))<6)+ISNUMBER(MATCH(EDATE(A1,SEQUENCE(DATEDIF(A1,A2,"m")+1,1,0)),A5,0))>0)*(--(ISNA(MATCH(EDATE(A1,SEQUENCE(DATEDIF(A1,A2,"m")+1,1,0)),A6,0))+ISNUMBER(MATCH(EDATE(A1,SEQUENCE(DATEDIF(A1,A2,"m")+1,1,0)),A5,0))>0))
)

核心逻辑说明 #

1. 日期序列生成：

   • days类型：用SEQUENCE(A2-A1+1,1,A1)生成起始到结束的所有单日日期
   • weeks类型：生成每周一的日期序列（步长7），确保周维度起始统一
   • months类型：用EDATE生成每月第一天的日期序列

2. 周末规则判断：

   • 通过MATCH+CHOOSE匹配选中的周末排除类型，自动切换WEEKDAY函数参数，判断日期是否为工作日
   • 若日期在A5指定的纳入周末列表中，直接判定为符合保留条件

3. 假期规则处理：

   • 优先判断日期是否在纳入周末列表：如果是，即使在假期列表也保留
   • 若不在纳入周末列表，则检查是否在假期列表，是则排除，否则保留

单独维度公式（可选） #

如果只需要单一维度的时间轴，可直接使用对应公式：

天维度 #

=FILTER(SEQUENCE(A2-A1+1,1,A1),((--(WEEKDAY(SEQUENCE(A2-A1+1,1,A1),CHOOSE(MATCH(A4,{"Friday & Saturday","Saturday & Sunday","No Days off"},0),1,2,3))<6)+ISNUMBER(MATCH(SEQUENCE(A2-A1+1,1,A1),A5,0))>0)*(--(ISNA(MATCH(SEQUENCE(A2-A1+1,1,A1),A6,0))+ISNUMBER(MATCH(SEQUENCE(A2-A1+1,1,A1),A5,0))>0)))

周维度 #

=FILTER(SEQUENCE(ROUNDUP((A2-A1)/7,0),1,A1-(WEEKDAY(A1,2)-1),7),((--(WEEKDAY(SEQUENCE(ROUNDUP((A2-A1)/7,0),1,A1-(WEEKDAY(A1,2)-1),7),CHOOSE(MATCH(A4,{"Friday & Saturday","Saturday & Sunday","No Days off"},0),1,2,3))<6)+ISNUMBER(MATCH(SEQUENCE(ROUNDUP((A2-A1)/7,0),1,A1-(WEEKDAY(A1,2)-1),7),A5,0))>0)*(--(ISNA(MATCH(SEQUENCE(ROUNDUP((A2-A1)/7,0),1,A1-(WEEKDAY(A1,2)-1),7),A6,0))+ISNUMBER(MATCH(SEQUENCE(ROUNDUP((A2-A1)/7,0),1,A1-(WEEKDAY(A1,2)-1),7),A5,0))>0)))

月维度 #

=FILTER(EDATE(A1,SEQUENCE(DATEDIF(A1,A2,"m")+1,1,0)),((--(WEEKDAY(EDATE(A1,SEQUENCE(DATEDIF(A1,A2,"m")+1,1,0)),CHOOSE(MATCH(A4,{"Friday & Saturday","Saturday & Sunday","No Days off"},0),1,2,3))<6)+ISNUMBER(MATCH(EDATE(A1,SEQUENCE(DATEDIF(A1,A2,"m")+1,1,0)),A5,0))>0)*(--(ISNA(MATCH(EDATE(A1,SEQUENCE(DATEDIF(A1,A2,"m")+1,1,0)),A6,0))+ISNUMBER(MATCH(EDATE(A1,SEQUENCE(DATEDIF(A1,A2,"m")+1,1,0)),A5,0))>0)))

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