为什么MATCH函数不能正常工作在Excel中使用数组公式的情况? (示例附后) [英] Why is the MATCH function not working how anticipated in Excel with array formulas? (example attached)
问题描述
说明和背景:我在G栏(动物)和H栏(颜色)中列出了动物及其颜色的列表.我在A列中有一个独特的颜色列表，在D列中有一个独特的动物列表.在B列中，在独特颜色列表旁边，我需要了解所有动物，哪种动物最常具有该颜色(原始数字，而不是比例.)我不能使用任何其他辅助单元.
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.
我已经用公式{=MAX(COUNTIFS(H:H,A2,G:G,$D$2:$D$20))}为每种颜色确定了动物的最大值，结果是7，但这是我能得到的.我可以将COUNTIFS语句设置为MAX语句，如下所示:COUNTIFS(H:H,A2,G:G,E1:E19)=MAX(COUNTIFS(H:H,A2,G:G,E1:E19))可以在数组公式中用作TRUE/FALSE数组.最后，我尝试将MATCH与先前的公式一起用作数组，并寻找TRUE值以尝试检索数组中唯一TRUE值的位置，但似乎无法找到它，而是给了我19 ，这是整个数组的长度.
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.
当我遍历公式时，这是得出19之前的步骤:
When I step through the formula, here's the step right before resulting in 19:
最终结果之前的步骤
为什么该MATCH不起作用?
Why does this MATCH not work?
推荐答案
在B2中尝试此数组公式，然后复制到B3:B10:
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 ) )
= 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屋！
