求助：计算元素与节假日日期区间的重叠天数（函数报错）

嗨，我来帮你解决这个日期重叠天数计算的问题，还有你碰到的!VALUE错误~ 先拆解下你当前公式出错的可能原因：大概率是单元格范围维度不匹配，或者部分单元格不是标准日期格式，另外你用ROW生成日期序列的逻辑里，要是$D$20-$C$4+1算出负数，INDEX直接就返回错误了，这也会触发VALUE报错。

下面给你一个更靠谱的公式，同时一步步讲清楚逻辑：

单个元素区间 vs 所有节假日区间的正确公式 #

先明确一下单元格引用（你可以根据自己的实际数据调整）：

• 单个元素的起始日期在C4，结束日期在D4
• 节假日的起始日期范围是C20:C40，结束日期范围是D20:D40

直接用这个公式：

=MMULT(--(TRANSPOSE(SEQUENCE(D4-C4+1,,C4))>=C20:C40)*(TRANSPOSE(SEQUENCE(D4-C4+1,,C4))<=D20:D40),SEQUENCE(ROWS(C20:C40),,1,0))

为啥这么写？拆解每一步逻辑 #

1. 生成元素的完整日期序列：用SEQUENCE(D4-C4+1,,C4)代替你原来的C4+ROW(...)，这个函数直接生成从C4到D4的所有日期，简洁还不容易出错，不用再绕ROW和INDEX。
2. 转置序列适配对比维度：TRANSPOSE(...)把纵向的日期序列转成横向，这样就能和纵向的节假日区间列表形成二维对比矩阵。
3. 判断日期是否在节假日区间内：(TRANSPOSE(...)>=C20:C40)*(TRANSPOSE(...)<=D20:D40)会生成一个0和1组成的二维数组，1代表这个日期落在对应节假日区间里，0则相反。
4. 求和得到重叠天数：最后用MMULT配合全1的序列（SEQUENCE(ROWS(C20:C40),,1,0)），对每个节假日区间对应的列求和，就能得到每个节假日和元素区间的重叠天数了。

批量计算多个元素的方法 #

如果你有一批元素（比如元素起始日期在C4:C10，结束日期在D4:D10），用这个数组公式就行（Excel 365/2021直接回车，旧版本要按Ctrl+Shift+Enter）：

=BYROW(C4:C10&D4:D10,LAMBDA(x,MMULT(--(TRANSPOSE(SEQUENCE(RIGHT(x)-LEFT(x)+1,,LEFT(x)))>=C20:C40)*(TRANSPOSE(SEQUENCE(RIGHT(x)-LEFT(x)+1,,LEFT(x)))<=D20:D40),SEQUENCE(ROWS(C20:C40),,1,0))))

最后给你几个错误排查小技巧 #

• 先检查所有日期单元格是不是标准日期格式，如果是文本格式，日期比较肯定会出错。
• 确认所有区间都是合理的：D4>=C4、D20:D40>=C20:C40，要是结束日期比起始日期早，SEQUENCE会直接返回错误。
• 要是用的是旧版Excel，不支持SEQUENCE和BYROW，可以用ROW(INDIRECT(C4&":"&D4))替代SEQUENCE生成日期序列，但要注意绝对引用和相对引用的设置。

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