Excel-使用重复数据查找第n个最大值 [英] Excel - Finding nth largest value with duplicate data

问题描述

我有一个下表，它有更多列，长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 long but 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 that delivered 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 delivered IF two teams have delivered the same number of gears I want to show them both, but do not care about which is #1 or #2 Currently 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个最大值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！