在索引（match）数组中查找最小值 [EXCEL]

Question

这是我的简单表格

A                           B               C
tasmania                   hobart            21
queensland                 brisbane          22
new south wales            sydney            23
northern territory         darwin            24
south australia            adelaide          25
western australia          perth             26
tasmania                   hobart            17
queensland                 brisbane          18
new south wales            sydney            19
northern territory        darwin             11
south australia           adelaide           12
western australia         perth              13

索引匹配数组公式:

=INDEX(A2:C9,MATCH(1,(H4=$A:$A)*(I4=$B:$B),0),3)

基本上，A和B是我的查找条件，而C是我想要获取的值。我希望C是匹配的C值中最小的值。

例如，如果我有塔斯马尼亚州和霍巴特作为我的标准，我希望获得17，因为它是最小值，而不是21。

我尝试在索引匹配数组(H4=$A:$A)*(I4=$B:$B)*(MIN($C:$C))中嵌套MIN，但结果只会出现错误。

- mindblues

Answer 1

这更像是一个MIN(IF...而不是INDEX。在Excel实现SUMIF或COUNTIF之前，甚至必须使用SUM(IF...或COUNT(IF...。

由于至今没有MINIFS，因此我们必须进一步使用：

{=MIN(IF($A$1:$A$1000=H4,IF($B$1:$B$1000=I4,$C$1:$C$1000,NA())))}

这是一个数组公式。将其输入到单元格中，不要使用花括号，并按[Ctrl]+[Shift]+[Enter]确认。然后花括号会自动出现。

- Axel Richter

---

既然我们现在有了MINIFS，你能否使用MINIFS更新答案？ - Vylix

@Vylix：谁有MINIFS？只有那些拥有最新版Excel的人。对于这些人来说，MINIFS函数已经被很好地记录了，不是吗？ - Axel Richter

看起来我误解了问题，并认为这是非常复杂的公式。实际上只是带有两个条件的MIN函数。谢谢！ - Vylix

Answer 2

使用聚合函数作为标准公式，

=AGGREGATE(15, 6, C2:INDEX(C:C, MATCH(1E+99,C:C ))/((A2:INDEX(A:A, MATCH(1E+99,C:C ))=F2)*(B2:INDEX(B:B, MATCH(1E+99,C:C ))=G2)), 1)

作为一个较旧的标准公式，您的原始代码看起来像这样：

    

但是，如果您想要更加简洁和易于阅读的代码，可以使用以下新的语法：

=MIN(INDEX(C2:C13+((H4<>A2:A13)+(I4<>B2:B13))*1E+99, , ))

- user4039065

---

聪明的公式，但为什么不直接使用=MIN(IF((F2=A2:INDEX(A:A,MATCH(1E+99,C:C)))*(G2=B2:INDEX(B:B,MATCH(1E+99,C:C))),C2:INDEX(C:C,MATCH(1E+99,C:C))))作为数组呢？ - Dirk Reichel

Axel在另一个答案中使用了数组公式。而我则坚持非数组风格。 - user4039065

你的公式也是数组样式（只是没有CSE）。真正的技巧应该是 C2:INDEX(C:C, MATCH(1E+99,C:C ))，它允许在不冻结Excel的情况下使用整个列作为数组 ;) - Dirk Reichel

是的，说得好。我的两个公式都使用了数组风格的函数。我应该说明CSE和非CSE。 - user4039065