在excel中提取前5个最大值（基于组） [英] Extracting top 5 maximum values (based on group) in excel

问题描述

我有一个excel文件，其中有3列对应于团队名称，球员名称和得分。我想根据每个队伍的得分来提取前5名玩家。如果团队没有分组，我找到了一个解决方案。前5名的解决方案。

我尝试添加一个if语句来过滤团队（如if（TEAM_COLUMN = TEAM_NAMES_CONSTANTS，SCORE_COLUMN，0）），但似乎不起作用。

  = INDEX（$ B $ 2：$ B $ 28，MATCH（1，INDEX（（$ A $ 2：$ A $ 28 = LARGE（$ A $ 2：$ A $ 28，ROWS（D $ 1：D1）））*（COUNTIF（D $ 1：D1，$ B $ 2：$ B $ 28）= 0），），0））
  

解决方案

我建议使用ROWS中的团队名称和播放器名称创建数据透视表然后使用值过滤器前10 ...（可以更改为任何整数）显示最高得分手，还可以将团队名称添加到FILTERS以分别查看每个团队。 p>

I have an excel file with 3 columns corresponding to Team name, Player name, and score. I would like to extract the top 5 players based on score in each team. I found a solution when there's not a grouping by team involved Solution for top 5.

I tried to add an "if" statement to filter for teams (like "if(TEAM_COLUMN=TEAM_NAMES_CONSTANTS, SCORE_COLUMN, 0), but it doesn't seem to work.

=INDEX($B$2:$B$28,MATCH(1,INDEX(($A$2:$A$28=LARGE($A$2:$A$28,ROWS(D$1:D1)))*(COUNTIF(D$1:D1,$B$2:$B$28)=0),),0))

解决方案

I would suggest creating a PivotTable with the Team Name and Player Name in ROWS and Score in Values, then use the Value Filter "Top 10..." (which can be changed to any integer) to display the 'top scorers'. You could also add Team Name to FILTERS to view each Team separately.

这篇关于在excel中提取前5个最大值（基于组）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！