在索引(匹配)数组中查找最小值[EXCEL] [英] Finding minimum value in index(match) array [EXCEL]

问题描述

这是我的简单桌子

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值中的最小值.

Basically A and B are my lookup criteria while C is the value I want to get. I want C to be the minimum value among the matched C value.

例如 如果以塔斯马尼亚和霍巴特为标准，我希望得到17，因为它是最小值，而不是21.

Ex. If I have tasmania and hobart as my criteria, I would want to get 17 because it is the minimum value and not 21.

我尝试将MIN嵌套在索引匹配数组(H4=$A:$A)*(I4=$B:$B)*(MIN($C:$C))中，但只会导致错误

I tried nesting MIN inside the index match array (H4=$A:$A)*(I4=$B:$B)*(MIN($C:$C)) but it only results in errors

推荐答案

这是MIN(IF...而不是INDEX.在Excel中实现SUMIF或COUNTIF之前，甚至必须以这种方式使用SUM(IF...或COUNT(IF....

This is rather a MIN(IF... than a INDEX. Before SUMIF or COUNTIF was implemented in Excel even SUM(IF... or COUNT(IF... had to be used this way.

由于到目前为止还没有MINIFS，为此，我们必须进一步使用:

Since there is not a MINIFS until now, for this we must further use:

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

这是一个数组公式.将其输入到没有大括号的单元格中，然后按[Ctrl] + [Shift] + [Enter]确认.大括号应该会自动出现.

This is an array formula. Input it into the cell without the curly brackets and press [Ctrl]+[Shift]+[Enter] to confirm. The curly brackets should then appear automatically.

这篇关于在索引(匹配)数组中查找最小值[EXCEL]的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！