为什么 MATCH 函数在 Excel 中使用数组公式无法正常工作?(附示例) [英] Why is the MATCH function not working how anticipated in Excel with array formulas? (example attached)

问题描述

为什么这个 MATCH 不起作用?

解决方案

在B2中试试这个数组公式，然后复制到B3:B10:

= INDEX( $G$1:$G$510,匹配(最大(如果($H$1:$H$510 = A2，COUNTIFS( $G$1:$G$510, $G$1:$G$510, $H$1:$H$510, $H$1:$H$510), "" ) ),如果($H$1:$H$510 = A2，COUNTIFS( $G$1:$G$510, $G$1:$G$510, $H$1:$H$510, $H$1:$H$510), "" ), 0 ) )

File Located Here (using Google Drive)

Explanation and background: I have a list of animals and their color in column G (animal) and H (color). I have a list of the unique colors in column A, and a list of unique animals in column D. In column B, next to the list of unique colors, I need to know of all the animals, which animal has that color most often (raw number, not proportion.) I cannot use any additional helper cells.

I have established the max by animal for each color with the formula {=MAX(COUNTIFS(H:H,A2,G:G,$D$2:$D$20))} with the result being 7, but that's as far as I can get. I can set the COUNTIFS statement to the MAX statement like so: COUNTIFS(H:H,A2,G:G,E1:E19)=MAX(COUNTIFS(H:H,A2,G:G,E1:E19)) which can be used as a TRUE/FALSE array in an array formula. Finally I try to use MATCH with the previous formula as my array, and look for a TRUE value to attempt to retrieve the position of the only TRUE value in the array, but it seems to not be able to find it and instead gives me 19, which is the length of the entire array.

When I step through the formula, here's the step right before resulting in 19:

step before final result

Why does this MATCH not work?

解决方案

Try this array formula in B2 then copy to B3:B10:

= INDEX( $G$1:$G$510, MATCH( MAX( IF( $H$1:$H$510 = A2, COUNTIFS( $G$1:$G$510, $G$1:$G$510, $H$1:$H$510, $H$1:$H$510), "" ) ), IF( $H$1:$H$510 = A2, COUNTIFS( $G$1:$G$510, $G$1:$G$510, $H$1:$H$510, $H$1:$H$510), "" ), 0 ) )

这篇关于为什么 MATCH 函数在 Excel 中使用数组公式无法正常工作?(附示例)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！