☰

按多个标准列排名

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2016
按多个标准列排名

在此处输入图片描述

我尝试按照 2 个标准对我的学生进行排名。首先来自RESULT然后POINT。

在示例中，最终的顺序应为 NUNU、NENE、NONO、NANA 和 NINI。

不能通过将RESULT和相加来完成POINT。我在网上搜索了一下，但不太明白如何进行排名。

答案1

我想向您推荐一种采用不同方法的解决方案：

在此处输入图片描述

在单元格 J2（排名列）中写入此公式并填写完毕，最后按排名列升序对数据进行排序：

=COUNTIF($G$2:$G$6,">"&G2)+1+SUMPRODUCT(--($G$2:$G$6=G2),--($H$2:$H$6>H2))

答案2

解决方案很简单：

工作表截图

输入以下公式I2并按 ctrl-enter/复制粘贴/填充列：

=G2*10^3+H2

请注意，仅当列中允许的最大值POINT是 999 时，此方法才会正常工作。

对于较大的值，10^3公式中的 需要更改。例如，如果最大允许值为 9999，则10^4需要 。


如果您想要一个更强大的公式，无论列中的值如何都能起作用H，请使用以下命令：

=G2*10^CEILING(LOG10(MAX($H$2:$H$6)),1)+H2

如果您想要“实际”排名，很遗憾，无法将上述公式与函数一起使用RANK()，因为它仅允许引用第二个参数，而不允许引用数组。需要完全不同的公式：

工作表截图

输入以下公式I2并按 ctrl-enter/复制粘贴/填充列：

=1+SUMPRODUCT(($G$2:$G$6>G2)+($G$2:$G$6=G2)*($H$2:$H$6>H2))

等效数组输入（Ctrl++ ）Shift的Enter公式为：

{=SUM(1,--($G$2:$G$6>G2),($G$2:$G$6=G2)*($H$2:$H$6>H2))}

当然，如果你的要求是对表进行排序，最简单的解决方案就是进行多列排序：

工作表截图

答案3

因此，查看您之前的问题以及这个问题，很明显您希望根据 A 的数量对学生进行排名，然后根据 B 的数量等对学生进行排名，并根据每门科目的分数总和来确定排名。

考虑到这一点，下面是无需辅助列的公式来做到这一点：

工作表截图

数组中输入（Ctrl++ Shift）Enter以下公式M5并将其复制粘贴/填充到其余列中（不要忘记删除{和}）：

{=
SUM(
  1,
  --(
    MMULT(($B$5:$L$9="A")*6^2+($B$5:$L$9="B")*6^1+($B$5:$L$9="C")*6^0,--(0<ROW(INDEX(M:M,1):INDEX(M:M,COLUMNS($B$5:$L$9)))))
    >INDEX(MMULT(($B$5:$L$9="A")*6^2+($B$5:$L$9="B")*6^1+($B$5:$L$9="C")*6^0,--(0<ROW(INDEX(M:M,1):INDEX(M:M,COLUMNS($B$5:$L$9))))),1+ROW()-ROW($B$5:$L$9))
  ),
  (
    MMULT(($B$5:$L$9="A")*6^2+($B$5:$L$9="B")*6^1+($B$5:$L$9="C")*6^0,--(0<ROW(INDEX(M:M,1):INDEX(M:M,COLUMNS($B$5:$L$9)))))
    =INDEX(MMULT(($B$5:$L$9="A")*6^2+($B$5:$L$9="B")*6^1+($B$5:$L$9="C")*6^0,--(0<ROW(INDEX(M:M,1):INDEX(M:M,COLUMNS($B$5:$L$9))))),1+ROW()-ROW($B$5:$L$9))
  )
  *(
    MMULT(IFERROR(--$B$5:$L$9,0),--(0<ROW(INDEX(M:M,1):INDEX(M:M,COLUMNS($B$5:$L$9)))))
    >INDEX(MMULT(IFERROR(--$B$5:$L$9,0),--(0<ROW(INDEX(M:M,1):INDEX(M:M,COLUMNS($B$5:$L$9))))),1+ROW()-ROW($B$5:$L$9))
  )
)}

以下是上述公式的等效缩小版本。我强烈建议不是使用它，而是使用美化后的公式版本。这样做会使公式更加很多 更易于维护。

{=SUM(1,--(MMULT(($B$5:$L$9="A")*6^2+($B$5:$L$9="B")*6^1+($B$5:$L$9="C")*6^0,--(0<ROW(INDEX(M:M,1):INDEX(M:M,COLUMNS($B$5:$L$9)))))>INDEX(MMULT(($B$5:$L$9="A")*6^2+($B$5:$L$9="B")*6^1+($B$5:$L$9="C")*6^0,--(0<ROW(INDEX(M:M,1):INDEX(M:M,COLUMNS($B$5:$L$9))))),1+ROW()-ROW($B$5:$L$9))),(MMULT(($B$5:$L$9="A")*6^2+($B$5:$L$9="B")*6^1+($B$5:$L$9="C")*6^0,--(0<ROW(INDEX(M:M,1):INDEX(M:M,COLUMNS($B$5:$L$9)))))=INDEX(MMULT(($B$5:$L$9="A")*6^2+($B$5:$L$9="B")*6^1+($B$5:$L$9="C")*6^0,--(0<ROW(INDEX(M:M,1):INDEX(M:M,COLUMNS($B$5:$L$9))))),1+ROW()-ROW($B$5:$L$9)))*(MMULT(IFERROR(--$B$5:$L$9,0),--(0<ROW(INDEX(M:M,1):INDEX(M:M,COLUMNS($B$5:$L$9)))))>INDEX(MMULT(IFERROR(--$B$5:$L$9,0),--(0<ROW(INDEX(M:M,1):INDEX(M:M,COLUMNS($B$5:$L$9))))),1+ROW()-ROW($B$5:$L$9))))}

解释：

看看美化公式的结构，很明显它与我之前的回答中的“正确”等级数组输入公式基本相同

{=SUM(1,--($G$2:$G$6>G2),($G$2:$G$6=G2)*($H$2:$H$6>H2))}

和辅助列被替换为函数，并且对这些列RESULT的单个单元格引用被替换为POINTMMULT(…)INDEX(MMULT(…),1+ROW()-ROW($B$5:$L$9))

如果你想了解MMULT()这里如何使用该函数，可以先查看一个更简单的用法我的答案另一个问题。

如何调整公式以获得更多等级应该相当明显。例如，要添加 a D，将 a 附加+($B$5:$L$9="D")*6^0到其他的末尾，并增加其他的幂。

正如上一个问题的答案中所提到的，如果表格扩展了更多的主题，则6必须增加，以便至少比表格中的新主题数多一个。

相关内容