Excel中总分并列时按数学成绩自定义排名位次的方法求助

现有学生成绩原始数据如下：

本次需求排名规则：第一优先级按总分降序排序，总分相同则按数学成绩降序排序，不保留并列位次，按排序结果顺次分配排名，最终要实现E排名为3、C为4、D为5的效果。 #

方法1：COUNTIFS公式法（全Excel版本兼容） #

该方法可以直接在原表每行直接计算排名，不需要调整原有数据顺序，适配所有Excel版本。

• 约定数据范围：成绩数据行是第2行到第6行，F列为Total Marks（总分）列，B列为Mathematics（数学）列，要计算第2行对应排名的话，在排名列的G2单元格输入如下公式：

=COUNTIFS(F$2:F$6,">"&F2) + COUNTIFS(F$2:F$6,F2,B$2:B$6,">"&B2) + 1

• 公式逻辑说明：
  • 第一部分COUNTIFS(F$2:F$6,">"&F2)：统计总分比当前行高的总人数
  • 第二部分COUNTIFS(F$2:F$6,F2,B$2:B$6,">"&B2)：统计总分和当前行相同、但数学成绩比当前行高的人数
  • 两部分数值相加后加1，即为当前行的顺次排名
• 输入公式后按回车，下拉填充到G6单元格即可得到符合要求的排名结果。

方法2：动态数组法（仅Excel 365/2021及以上版本适用） #

如果需要直接输出完整的排序后带排名的新表格，可以用该方法，仅需输入一次公式即可自动溢出所有结果。

• 在任意空白单元格（例如I2）输入如下公式：

=LET(sorted_data,SORT(A2:F6,{6,2},{-1,-1}),HSTACK(sorted_data,SEQUENCE(ROWS(sorted_data))))

• 公式逻辑说明：
  • SORT函数先对A2:F6范围内的所有成绩数据，按第6列（总分）降序、第2列（数学）降序排序
  • SEQUENCE函数生成和数据行数一致的连续排名序列
  • HSTACK函数把排序后的成绩数据和排名序列拼接为完整表格，自动溢出所有行的结果

内容的提问来源于stack exchange，提问作者Najmul Hossain