使用大决胜局的前5个公式 [英] Top 5 formula with a tiebreaker using LARGE

问题描述

我有一个电子表格，其中在A列中有一个项目列表，在B列中有相应的值，例如:

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

现在在C列中，我创建了一个公式，该公式将返回连接到前5个值的名称，如下所示:

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)

不幸的是，该公式不敏感.因此，如果有多个具有相同值的项目，则将仅返回其中一项的名称.因此，在我的示例中，=INDEX(A:B;MATCH(LARGE(B:B;3);B:B;0);1)和=INDEX(A:B;MATCH(LARGE(B:B;4);B:B;0);1)将返回相同的值:item2.我将需要修改此函数，以便它将返回不同的值.对我来说，item2是在item3之前还是之后都没有关系，我只希望它们都显示出来.有人可以帮我吗?

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?

推荐答案

如果您喜欢使用LARGE()，则只需删除分数即可.

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

在 A 和 B 列中包含数据，在 C1 中输入:

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

=B1

，然后在 C2 中输入:

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

并向下复制 C2 :

现在LARGE()可以在 C 列上使用，因为 C 列将只有唯一的值！

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

这篇关于使用大决胜局的前5个公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！