求基于发货与交货日期条件查找最晚有效发货日期的公式

需求逻辑 #

需要找出发货日期所属当月内，所有满足「交货日期不晚于该月月底」的发货日期中的最大值，结果可批量输出到指定列（如示例中的C列）。

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

使用BYROW+FILTER+MAX组合公式，可一次性生成所有行的结果，无需下拉填充：

=BYROW(A2:A5, LAMBDA(x, MAX(FILTER($A$2:$A$5, (EOMONTH($A$2:$A$5,0)=EOMONTH(x,0))*($B$2:$B$5<=EOMONTH(x,0))))))

注：如果只需要计算当前日期所在月份的结果，可把公式中的x替换为TODAY()，简化为：

=MAX(FILTER(A:A, (EOMONTH(A:A,0)=EOMONTH(TODAY(),0))*(B:A<=EOMONTH(TODAY(),0))))

针对旧版Excel（无动态数组支持） #

使用数组公式，在C2单元格输入后按Ctrl+Shift+Enter确认，再下拉填充到其他行：

=MAX(IF((EOMONTH($A$2:$A$5,0)=EOMONTH(A2,0))*($B$2:$B$5<=EOMONTH(A2,0)),$A$2:$A$5))

公式拆解 #

• EOMONTH(date,0)：返回指定日期所在月份的最后一天，用于界定当月的截止日期。
• (EOMONTH($A$2:$A$5,0)=EOMONTH(x,0))：筛选出与当前行发货日期同属一个月份的所有记录。
• ($B$2:$B$5<=EOMONTH(x,0))：进一步筛选出交货日期不超过当月月底的记录。
• FILTER/IF：保留同时满足上述两个条件的发货日期，最后用MAX提取其中的最大值。

示例验证 #

以你提供的表格为例，所有发货日期均属于2023年1月，当月月底为2023/1/31：

• 前3条记录的交货日期均≤2023/1/31，对应的发货日期为01/24/2023、01/25/2023、01/26/2023。
• 第4条记录的交货日期为02/01/2023，超出当月月底，被排除。
• 最终取最大值01/26/2023，与示例结果完全匹配。

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