在Excel中使用多个条件进行排名

Question

例如，我需要基于总分（列C）和数学成绩（列B）的高低来创建一份学生优胜名单。

A        B  C   D
-------------------------
Student1 80 220 1
Student2 88 180 3
Student3 90 180 2

在列 D 中给出了预期的优势位置。

我可以使用 RANK 函数，但我只能为一个列（总数）执行此操作。如果多个学生的总数相等，则我无法找到任何解决方案。

- user3717550

---

如果列C和列B的值都相同怎么办？ - Gary's Student

Answer 1

您可以在D1中尝试这个。

=COUNTIF($C$1:$C$99,">"&C1)+1+SUMPRODUCT(--($C$1:$C$99=C1),--($B$1:$B$99>B1))

然后复制/向下填充。
如果有帮助，请告诉我。

解释

您的第一个条件位于列C，第二个条件位于列B。基本上，首先它会计算比该条目本身($C1)更大的条目($C$1:$C$99)的数量。对于排名中的第一个条目，您将获得零，因此您需要为每个结果添加1(+1)。 到这里为止，如果您有两个相同的值，您将获得重复的排名。
因此，您需要添加另一个参数来根据第二个条件进行一些额外的计算：
为了解决并列的情况，您需要sumproduct两个数组公式并将结果添加到前面的参数中，目标是找到与此条目相等且在第二个条件列$B$1:$B$99>B1中具有更大值的条目数：

您需要添加--以将TRUE和FALSE转换为0和1，以便您可以将它们相乘：
SUMPRODUCT(--($C$1:$C$99=C1),--($B$1:$B$99>B1))
第一个数组是用于查看第一个条件中有多少并列的条目。第二个数组是用于查找比该条目本身更大的值的数量。

注意您可以向列中添加任意数量的条目，但请记住更新公式中的范围，目前设置为99，您可以将其扩展到所需的行数。

- Marcel

---

它运行良好。非常感谢... :) 如果您能简要描述算法，那就太好了。如果我想添加另一个科目的数字，公式会是什么？ - user3717550

Answer 2

有时候，使用一个辅助列可以提供快速和计算效率高的解决方案。将数学分数添加到总分数中作为小数应该会产生一个按照您的标准排名的数字。在右侧的未使用列中，在第二行使用以下公式：

=C2+B2/1000

根据需要向下填充。现在，您可以在这个辅助列上使用传统的RANK函数，例如=RANK(D2, D$2:D$9)，用于排名序号。

- user4039065

---

如果我只是添加两列并应用排名函数，它会给出预期的结果。为什么要将B2除以1000？这与直接添加B2（D2=C2+B2）有什么区别？如果我需要添加另一个科目怎么办？ - user3717550

仅仅将这两列相加会在某些情况下产生正确的结果，而不是所有情况下。你希望总分列具有最高优先级（也称为主键），数学成绩应该只被用作在平局时的次要关键字。如果Student4的总分是210，数学成绩是95呢？那么他/她将排名高于Student1，这与我理解的你的标准相矛盾。 - user4039065

如果您需要另一个主题，请将第三个“关键字”添加到先前的总数中，作为一个较小的分数，不会干扰次要关键字作为决定标准的角色。您教数学吗？ - user4039065

Answer 3

非常简单的（或者至少比“最佳答案”提供的更简单）“数学”解决方案：使用权重进行线性组合。 做类似于

weighted_marks = 10*colC + colB

然后使用简单排名函数对加权分数进行排序。这确实解决了您的问题，构建了所需的排名。

- Tomaso Neri

Answer 4

如果您不想限制行数或标准中使用的数字，可以扩展Jeeped's approach。假设有三个标准，第一个在A列，第二个在B列，第三个在C列，您可以在单元格D2到L2中使用以下公式：

=RANK($A2,$A:$A,1)
=RANK($B2,$B:$B,1)
=D2*2^27+E2
=RANK(F2,F:F,1)
=RANK($C2,$C:$C,1)
=G2*2^27+H2
=RANK(I2,I:I,1)
=J2*2^27-ROW()
=RANK(K2,K:K,0)

需要复制公式。结果在L列中。使用行号来打破平局。

如果您想添加第四个标准，可以在放置上述公式后执行以下操作：

1. 在C和D列之间添加新标准。
2. 在I和J列之间插入三个新列。
3. 将G:I列复制到新的J:L列。
4. 将G列复制到M列，覆盖其内容。
5. 更改L列中的公式以指向新标准。

公式中使用的因子2^27平衡了双精度数字中可用的53位精度。这足以覆盖当前版本Excel的行限制。

- Andreas Flueckiger