如何对多个条件使用 Index 和 Match 公式

如何对多个条件使用 Index 和 Match 公式

我有一个包含奥运会信息的电子表格,其中包含以下列字段:

A) 运动员,B) 年龄,C) 国家,D) 年份,E) 闭幕式日期,F) 运动项目 G) 金牌。

我想回答一下有关数据的这个问题:

任何一年中,为以下国家赢得最多金牌的运动员的名字是什么?

这个国家是挪威(在D38)。有什么帮助吗?

我最初使用的公式是:

 =INDEX(OlympicAthletes!A:A,MATCH(MAX(OlympicAthletes!G:G),OlympicAthletes!G:G,0),1)

但是,这只会返回迈克尔·菲尔普斯。我需要一个公式来返回挪威获得金牌最多的运动员。谢谢!

答案1

您可以使用重复数组公式来执行此操作。虽然不太美观,但可以得到您想要的结果。输入以下内容并按Ctrl+ Shift+回车Enter

=INDEX(A:A,MAX(IF(C:C="Norway",IF(G:G=MAX(IF(C:C="Norway",G:G,-1)),ROW(G:G),-1),-1)))

它的作用(除了让你了解 SQL):

  • IF(C:C="Norway",G:G,-1)表示一个长度为 C 列的数组,如果该记录的国家/地区是挪威,则该数组包含 G 列的值,-1如果该国家/地区不是挪威,则该数组包含 G 列的值。
  • MAX(IF(C:C="Norway",G:G,-1))返回该数组的最大值,该值应该是表中挪威人赢得的最大奖牌数。
  • IF(C:C="Norway",IF(G:G=MAX(IF(C:C="Norway",G:G,-1)),ROW(G:G),-1),-1)检查所有行是否符合两个条件:第一,运动员是挪威人;第二,该运动员赢得的金牌数等于挪威人赢得的最高金牌数。如果两个条件都满足或都不满足,则此表达式的结果是一个长度为 C 列的数组,其中包含行号-1
  • MAX(IF(C:C="Norway",IF(G:G=MAX(IF(C:C="Norway",G:G,-1)),ROW(G:G),-1),-1))返回此数组中的最大值。这将是匹配的行号。如果恰好有多行符合条件,则将返回行号最大的行。
  • 最后,INDEX(A:A,MAX(IF(C:C="Norway",IF(G:G=MAX(IF(C:C="Norway",G:G,-1)),ROW(G:G),-1),-1)))返回符合条件的行号中的 A 列的值(名称)。

快速警告:

请注意,在数组公式中使用完整列引用(例如)A:A会对计算速度产生明显的负面影响。如果可能的话,您应该使用有限的范围,例如A1:A10,尤其是当您在工作簿中创建多个此类公式时。

答案2

您可能想考虑将数据放入数据透视表中。它允许您以多种方式汇总和查询数据。具体来说,如果您创建了一个数据透视表,行标题为年份,然后是国家/地区,然后是运动员,然后将金牌添加到数据区域。这将非常精确地为您分解数据,并让您可以访问可用于更轻松地查询数据的过滤器。

相关内容