Excel无辅助列求排除苹果橙子后成本前三水果的购买日期

原公式问题 #

你用的IFS(B:B,"<>Apple",B:B,"<>Orange")逻辑完全错了：IFS函数必须条件和结果成对设置，你只写了排除条件，没指定满足条件时要返回C列的成本值；而且两个条件是“或”的判断逻辑，IFS会优先触发第一个排除Apple的条件，根本不会处理第二个排除Orange的规则，导致LARGE无法获取正确的筛选后成本数组。

修正后的公式 #

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

单个公式就能一次性返回前3个日期，无需手动下拉：

=INDEX(A:A,MATCH(LARGE(IF((B:B<>"Apple")*(B:B<>"Orange"),C:C),{1,2,3}),C:C,0))

输入后直接按回车，公式会自动溢出3个结果。

针对旧版Excel（需数组公式） #

要逐个输入公式，并且按Ctrl+Shift+Enter完成数组确认（公式会自动带上大括号{}，别手动加）：

• 第1高成本对应的日期：

=INDEX(A:A,MATCH(LARGE(IF((B:B<>"Apple")*(B:B<>"Orange"),C:C),1),C:C,0))

• 第2高成本对应的日期：

=INDEX(A:A,MATCH(LARGE(IF((B:B<>"Apple")*(B:B<>"Orange"),C:C),2),C:C,0))

• 第3高成本对应的日期：

=INDEX(A:A,MATCH(LARGE(IF((B:B<>"Apple")*(B:B<>"Orange"),C:C),3),C:C,0))

公式逻辑说明 #

1. (B:B<>"Apple")*(B:B<>"Orange")：用乘法实现“同时满足”的筛选，挑出既不是Apple也不是Orange的行，返回由TRUE/FALSE组成的数组；
2. IF(...,C:C)：给满足筛选条件的行返回对应的成本值，不满足的返回FALSE；
3. LARGE(...,{1,2,3})：从筛选后的成本数组里提取第1、2、3高的数值；
4. MATCH(...,C:C,0)：找到这些高成本值在C列的位置；
5. INDEX(A:A,...)：根据位置返回A列对应的购买日期。

补充：如果有多个水果成本相同，MATCH会返回第一个匹配到的日期；要是需要避免重复匹配，可在公式里加入行号判断来区分。

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