Google Sheets：如何修改IF函数以检查所有日期时间不可用范围

问题背景 #

需要在Google Sheets中实现：输入人员姓名（A17）、日期（B2）和开始时间（B17）后，自动核查该人员在不可用表（Unavailability Sheet）中的所有不可用时间段（姓名列C，起始时间列D，结束时间列E），判断目标时间是否落在任意一个不可用时段内。

原公式仅能检查匹配姓名的首个不可用时间段，无法覆盖所有符合条件的记录：

=IF(B17="","No",IF(AND(B$2+B17>=FILTER('Unavailability Form'!$D$2:$D$116,'Unavailability Sheet'!$C$2:$C$116=$A17),B$2+B17<=FILTER('Unavailability Form'!$E$2:$E$116,'Unavailability Sheet'!$C$2:$C$116=$A17)),"Yes","No"))

优化方案 #

使用COUNTIFS或SUMPRODUCT函数遍历所有匹配姓名的记录，统计存在时间重叠的记录数，只要有1条满足条件就返回"Yes"：

公式1（简洁推荐） #

=IF(B17="","No",IF(COUNTIFS(
    'Unavailability Sheet'!$C$2:$C$116, $A17,
    'Unavailability Sheet'!$D$2:$D$116, "<="&B$2+B17,
    'Unavailability Sheet'!$E$2:$E$116, ">="&B$2+B17
)>0,"Yes","No"))

公式2（SUMPRODUCT实现） #

=IF(B17="","No",IF(SUMPRODUCT(
    --('Unavailability Sheet'!$C$2:$C$116=$A17),
    --(B$2+B17<='Unavailability Sheet'!$E$2:$E$116),
    --(B$2+B17>='Unavailability Sheet'!$D$2:$D$116)
)>0,"Yes","No"))

原理说明 #

• 原公式的FILTER会返回所有匹配姓名的时间段，但AND函数只能处理单个值，因此仅会判断第一个返回的时段
• COUNTIFS/SUMPRODUCT可以批量检查所有匹配姓名的记录，判断目标时间（B$2+B17）是否落在任意一个不可用时段（D≤目标时间≤E）内，只要存在符合条件的记录就返回"Yes"

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