Top 5 formula with a tiebreaker using LARGE [dupli

问题:

This question already has an answer here:

• Extracting the top 5 maximum values in excel 5 answers

I have spreadsheet that have a list of items in column A and according values in column B, something like:

A        B
item1    2
item2    3
item3    3
item4    5
item5    6

Now in column C I created a formula that would return the names connected to the top 5 values that looks like:

=INDEX(A:B;MATCH(LARGE(B:B;1);B:B;0);1)
=INDEX(A:B;MATCH(LARGE(B:B;2);B:B;0);1)
=INDEX(A:B;MATCH(LARGE(B:B;3);B:B;0);1)
=INDEX(A:B;MATCH(LARGE(B:B;4);B:B;0);1)
=INDEX(A:B;MATCH(LARGE(B:B;5);B:B;0);1)

Unfotunately this formula is not tie-sensitive. So if there are more than one item with the same value it would return the name of only one of them. So in my example =INDEX(A:B;MATCH(LARGE(B:B;3);B:B;0);1) and =INDEX(A:B;MATCH(LARGE(B:B;4);B:B;0);1) would return the same value: item2. I would need to modify this function so it would return different values. It doesn't matter for me if item2 would be before or after item3, I just want them both to be displayed. Could anyone help me with this one?

回答1:

If you like using LARGE(), then just de-duplicate the scores.

With data in columns A and B, in C1 enter:

=B1

and in C2 enter:

=B2+(COUNTIF($B$1:B1,B2))/100

and copy C2 downwards:

Now LARGE() can be used on column C as column C will have only unique values !