Excel公式修正：查找最近指定工作日的9点累计销售数据

需求 #

从销售数据表的G列提取指定数值，需同时满足两个规则：

• E列对应时段值为9pm，即当日累计销售额的截止统计值
• C列对应日期为全量历史数据中，距离数据集最新日期最近的周五，不受最新日期本身的星期属性限制

原有方案缺陷 #

原有公式采用三条件并列强匹配逻辑：

1. E列值为9pm
2. C列日期等于数据集全局最大日期
3. D列星期值为Friday
   该逻辑存在互斥问题：仅当数据集最新日期恰好是周五时才能返回正确结果。例如数据更新至周一时，只有把星期匹配条件改为Monday才能取到值，匹配Friday时直接返回0，无法满足跨星期找最近历史周五的需求。
   原有存在问题的公式（还存在条件间缺逗号的语法错误）：

=AVERAGEIFS(All!$G$1:$G$2593,All!$E$1:$E$2593,"9pm" All!$C$1:$C$2593, MAX(All!$C$1:$C$2593),All!$D$1:$D$2593,"Friday")

=SUMIFS(All!$G$1:$G$2593,All!$E$1:$E$2593,"9pm" All!$C$1:$C$2593, MAX(All!$C$1:$C$2593),All!$D$1:$D$2593,"Friday")

可用公式方案 #

核心思路是先单独算出「不晚于数据集最新日期的最近一个周五」的日期值，再用这个日期作为C列的匹配条件，搭配9pm的时段条件取值即可。

全版本通用公式 #

兼容所有Excel版本，2019及更早版本输入后需按Ctrl+Shift+Enter三键确认数组公式，365/2021及以后版本直接回车生效：

=SUMIFS(All!$G:$G,All!$E:$E,"9pm",All!$C:$C,MAX(IF((All!$C:$C<=MAX(All!$C:$C))*(All!$D:$D="Friday"),All!$C:$C)))

=AVERAGEIFS(All!$G:$G,All!$E:$E,"9pm",All!$C:$C,MAX(IF((All!$C:$C<=MAX(All!$C:$C))*(All!$D:$D="Friday"),All!$C:$C)))

如果需要控制计算范围提升性能，可以把整列引用$G:$G替换成实际数据范围比如$G$1:$G$2593即可。

Excel 365/2021简化公式 #

支持MAXIFS、XLOOKUP的新版本可以用更简洁的写法，不需要数组三键：

=XLOOKUP(1,(All!$E:$E="9pm")*(All!$C:$C=MAXIFS(All!$C:$C,All!$C:$C,"<="&MAX(All!$C:$C),All!$D:$D,"Friday")),All!$G:$G,0)

逻辑拆解 #

1. 先取数据集的最新日期：MAX(All!$C:$C)
2. 筛选所有不晚于最新日期、且星期为周五的日期，取其中的最大值，就是距离当前最新数据最近的周五
3. 以「时段为9pm」「日期为上一步算出的最近周五」两个条件匹配，提取G列对应值即可，和最新日期本身是周几没有关联。

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