问题描述
我有一个下表，它有更多列，长40行但这是数据的一个例子.该表按团队#
排序数据表
我正在尝试创建第二个表，以显示前10名球队交付的齿轮.我也想为其他专栏做这件事.我正在尝试不使用VBA进行此操作.
我使用了此功能，效果很好:
=INDEX(TT_Team,MATCH(LARGE(TT_Tele_Gears,$A3),TT_Tele_Gears,0))
问题是所送齿轮数量的重复数据如果两支球队交付了我想展示的相同数量的装备他们两个，但不在乎哪个是#1或#2目前，我得到了这个:
前10名表
关于修复的任何想法吗?
预先感谢
您可以尝试这样的解决方案:
向下复制F2中的公式只是:
=LARGE(B$2:B$12,D2)
在E2中，如图所示:
=INDEX(A$2:A$12,LARGE(IF(B$2:B$12=F2,ROW(B$2:B$12)-ROW(B$2)+1),COUNTIF(F2:F$6,F2)))
与CTRL+SHIFT+ENTER确认并抄下
最重要的是最后的COUNTIF部分.这是从当前行开始计算的，因此对于重复项，当您在列中向下移动时，COUNTIF值会更改，因此您将获得每个重复项
I have a the following table, it has more columns and is 40 rows longbut this is an example of the data. The table is sorted by Team #
Data Table
I am trying to create a 2nd table that shows the top 10 teams thatdelivered gears. I want to do this for the other columns as well.I am trying to do this without VBA.
I used this function and it worked well:
=INDEX(TT_Team,MATCH(LARGE(TT_Tele_Gears,$A3),TT_Tele_Gears,0))
The problem is the duplicate data for the amount of gears deliveredIF two teams have delivered the same number of gears I want to showthem both, but do not care about which is #1 or #2Currently I get this:
Top 10 Table
Any ideas on a fix ?
Thanks in Advance
You could try a solution like this:
The formula in F2 copied down is just:
=LARGE(B$2:B$12,D2)
and in E2 as shown it's this:
=INDEX(A$2:A$12,LARGE(IF(B$2:B$12=F2,ROW(B$2:B$12)-ROW(B$2)+1),COUNTIF(F2:F$6,F2)))
confirm with CTRL+SHIFT+ENTER and copy down
It's the COUNTIF part at the end that makes the difference. This is counting from the current row, so for duplicates as you go down the column the COUNTIF value changes, so you get each duplicate
这篇关于Excel-使用重复数据查找第n个最大值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！