如何用数组公式判断DATE_VALUE是否在START_DATE与END_DATE区间并标记HIT

Google Sheets 数组公式方案 #

假设你的数据结构为：

• START_DATE 在A列（从A2开始）
• END_DATE 在B列（从B2开始）
• DATE_VALUE 在D列（从D2开始）

在C2单元格输入以下数组公式，可自动批量处理整列：

=ARRAYFORMULA(IF(A2:A="",,IF(MMULT(--(D2:D>=A2:A)*(D2:D<=B2:B),SEQUENCE(COUNTA(D2:D),1,1,0))>0,"HIT","")))

公式逻辑： #

• ARRAYFORMULA：实现整列批量计算，无需手动下拉公式
• --(D2:D>=A2:A)*(D2:D<=B2:B)：生成二维判断数组，每个元素为1（DATE_VALUE日期在当前行起止区间内）或0（不在）
• MMULT(..., SEQUENCE(...))：对每行的判断结果求和，只要有一个符合条件的日期，求和结果就大于0
• 外层IF：根据求和结果判断，大于0则返回HIT，否则返回空值

Excel 方案（分版本） #

Excel 365/2021（支持动态数组） #

在C2单元格输入公式，自动溢出到整列：

=IF(A2:A="",,IF(BYROW(A2:A, LAMBDA(start, MAX((D2:D>=start)*(D2:D<=OFFSET(start,0,1)))))>0,"HIT",""))

旧版Excel（需按Ctrl+Shift+Enter触发数组公式） #

在C2单元格输入公式后按Ctrl+Shift+Enter，再下拉到目标行：

=IF(MAX(--(D$2:D$100>=A2)*(D$2:D$100<=B2))>0,"HIT","")

公式逻辑： #

• BYROW + LAMBDA：逐行处理A列的起始日期，匹配对应行的结束日期（OFFSET(start,0,1)）
• MAX(--(...))：判断当前行区间内是否存在DATE_VALUE日期，只要有一个符合条件，结果为1，否则为0
• 外层IF：根据结果返回HIT或空值

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