如何简化Excel多日期识别公式并支持灵活增减起止日期输入？

原公式固定了三组起止区间，增减区间时需要手动添加/删除AND语句，冗余且不灵活，以下几种方法可以解决这个问题：

兼容旧版Excel的方案（SUMPRODUCT） #

用SUMPRODUCT动态统计符合条件的区间组数，公式如下：

=SUMPRODUCT(--(H$1>=A2:F2),--(H$1<=OFFSET(A2:F2,0,1))*(MOD(COLUMN(A2:F2)-COLUMN(A2),2)=0))>0

• MOD(COLUMN(A2:F2)-COLUMN(A2),2)=0 用来定位所有起始列（A、C、E…这类偶数列偏移的列）
• OFFSET(A2:F2,0,1) 对应每个起始列的下一列（即结束列B、D、F…）
• 只要统计结果大于0，就说明当前日期落在任意一个起止区间内

Excel 365/2021动态数组方案 #

利用动态数组函数自动识别所有两列一组的区间，无需手动调整每组判断：

=LET(
  range,A2:F2,
  start_cols,SEQUENCE(,COLUMNS(range)/2,1,2),
  end_cols,SEQUENCE(,COLUMNS(range)/2,2,2),
  OR(H$1>=INDEX(range,,start_cols)*H$1<=INDEX(range,,end_cols))
)

后续增减起止列时，只需要把A2:F2改成扩展后的区域（比如A2:H2），公式会自动识别新增的区间。

Excel 365专属：自定义LAMBDA函数（复用性更强） #

先定义一个可复用的LAMBDA函数（点击「公式」→「定义名称」，名称设为ISINANYRANGE，引用位置输入以下内容）：

=LAMBDA(date,range,
  LET(
    start_cols,SEQUENCE(,COLUMNS(range)/2,1,2),
    end_cols,SEQUENCE(,COLUMNS(range)/2,2,2),
    starts,INDEX(range,,start_cols),
    ends,INDEX(range,,end_cols),
    OR(date>=starts*date<=ends)
  )
)

之后在单元格中直接调用：

=ISINANYRANGE(H$1,A2:F2)

不管后续增减多少组起止列，只要修改A2:F2为对应的输入区域即可，函数会自动处理所有两列一组的区间判断。

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