我有一个包含奥运会信息的电子表格,其中包含以下列字段:
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
您可能想考虑将数据放入数据透视表中。它允许您以多种方式汇总和查询数据。具体来说,如果您创建了一个数据透视表,行标题为年份,然后是国家/地区,然后是运动员,然后将金牌添加到数据区域。这将非常精确地为您分解数据,并让您可以访问可用于更轻松地查询数据的过滤器。