在使用Excel进行数据处理时，排名计算是常见的需求。但大家都知道，Excel自带的RANK排名函数返回的是美式排名，呈现 “1，1，3，4…” 这样的序列，与我们中国常用的排名习惯不符，无法直接使用。别担心，今天就为大家分享两个能完美解决这一问题的中式排名公式，让排名计算轻松符合我们的使用习惯。
一、通用公式
（1）公式展示与适用范围
通用公式为：=SUMPRODUCT((B$2:B$12>B2)/COUNTIF(B$2:B$12,B$2:B$12))+1。这个公式的优势在于它具有良好的兼容性，在各个版本的Excel中都能正常使用。不过，它也有一个小小的缺点，就是公式相对来说略显复杂。
（2）公式原理剖析
构建比较数组：公式中的B$2:B$12>B2这部分，会将B2单元格的值与B2到B12单元格区域中的每一个值进行比较。如果大于，就返回TRUE，在Excel中TRUE被当作数值1处理；如果小于，就返回FALSE，在Excel中FALSE被当作数值0处理。例如，假设B2到B12单元格区域中的数据为 {80, 80, 80, 70, 70, 60, 60, 60, 50, 50, 40}，当B2单元格的值为80时，这部分返回的数组类似这样 {FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}。
构建重复计数数组：COUNTIF(B$2:B$12,B$2:B$12) 这部分，会对B2到B12单元格区域中的每个值进行重复计数。如果某个值没有并列的名次，就返回 1；如果有 2 个相同的成绩，就返回 2；3个相同就返回3。以上述数据为例，返回的数组可能是 {3; 3; 3; 2; 2; 3; 3; 3; 2; 2; 1}。
计算超过该成绩的人数：将上述两部分得到的数组相除，即 (B$2:B$12>B2)/COUNTIF(B$2:B$12)，就可以得到超过该成绩的人数。例如，上述两数组相除的结果可能是 {0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0}。这里需要注意的是，由于数组间的除法是对应位置元素相除，所以得到的结果数组长度与原数组相同。
得出当前排名：最后，将这个相除结果得到的数组进行相加，再加上1，就可以得到当前单元格数值的中式排名。例如，上述结果数组相加后为0，再加1，那么对于当前B2单元格值为80的情况，其中式排名就是1。
二、新版本公式
（1）公式呈现与版本要求
新版本公式为：=MATCH(B2,SORT(UNIQUE($BB$12),,-1),) 。这个公式最大的优点就是简洁明了，容易理解。然而，它的使用受到软件版本的限制，只能在Excel 2021及以上版本或最新版的 WPS中使用。
（2）公式原理详解
提取不重复值：公式中的UNIQUE(B2:B12)这部分，作用是从B2到B12单元格区域中提取不重复的值。例如，对于前面提到的 {80, 80, 80, 70, 70, 60, 60, 60, 50, 50, 40} 这样的数据，经过 UNIQUE 函数处理后，会得到 {80, 70, 60, 50, 40} 这样一个不重复的值的数组。
降序排列不重复值：SORT (UNIQUE (B2:B12),,-1) 这部分，是使用SORT函数对提取出的不重复值进行降序排列。这里的第三个参数 -1 表示降序排列，如果是1则表示升序排列。继续以上述数据为例，经过这一步处理后，会得到一个按降序排列的不重复分值数组，即 {80, 70, 60, 50, 40}。
返回排名位置：最后，使用MATCH函数，将B2单元格的值在这个按降序排列的不重复分值数组中进行匹配，返回其所处的位置。这个位置就是中式排名。例如，如果B2单元格的值为70，那么在 {80, 70, 60, 50, 40} 这个数组中，MATCH函数返回的位置是2，即该成绩的中式排名为2。
通过以上两个公式的详细介绍，相信大家已经掌握了在Excel中进行中式排名的方法。大家可以根据自己使用的软件版本以及对公式的偏好，灵活选择合适的公式进行排名计算。无论是处理学生成绩排名、销售业绩排名，还是其他需要排名的场景，都能轻松应对。
评论 (0)