Excel里如何实现中国式排名？你可能一直做错了！

点击蓝字【秋叶 Excel】

发送【工具】

免费领 3 大 Excel 高效工具！

本文作者：小花

本文审核：玛奇鹅

本文编辑：紫瑜、竺兰

继上一篇关于排名问题的文章（见文末）后，小花如约与大家继续探索排名的另一个重要问题——中国式排名。

什么是中国式排名呢？

它与美式排名不同的地方在于，当出现两个或两个以上相同排名值时，美式排名会出现向下占位的跳跃式排名值，

如下图：

而中国式排名则会从下一个顺位继续连续排名。

如果我们从三个方面对比中国式排名和美式排名，可能会让各位读者更为清楚二者的区别：

在上一篇文章中，小花提到，

中国式排名的数据逻辑是取满足条件的不重复值个数。

这在 Excel 要如何实现呢？

方法有很多，排序法、数据透视法和 PQ 法等都可以完成，

但这些方法都无一不需要数据的重新排列布局。

而小花今日所分享的这些函数方法，才是补天之漏的灵石！

初生牛犊不怕虎，

UNIQUE 大放异彩

去除重复值一直是 Excel 使用者的梦魇。

幸运的是，Office 365 中特意推出了专用于去重的新函数 UNIQUE。

需要去重的数据列经 UNIQUE 处理后便可变为唯一值列表，此时再进行条件判断求取大于（或小于）或等于当前值的数目，即可得到当前值的降序排名（或升序排名）。

=SUMPRODUCT((UNIQUE($B$2:$B$6)>=B2)*1)

▲ 左右滑动查看

公式说明：

❶ UNIQUE：去除重复值

通过 UNIQUE 去重，得到 B2:B6 唯一值列表，且保留原顺序不变。

即{172.11;35.38;75.89;5.48}，传统的去重难题迎刃而解！

❷ UNIQUE>=B2：比较逻辑判断

唯一值列表与 B2 值 172.11 进行比较，得到一组逻辑值{TRUE;FALSE;FALSE;FALSE}。

在这个逻辑值数组中，TRUE 的数量就是唯一值列表中大于或等于 B2 的单元格数目，即为排名。

也就是说，有「1」个数≥B2，则「1」为对应排名，有「2」个数≥B2，则排名为「2」，以此类推。

于是，问题转化为如何计算该逻辑数组中 TRUE 的数量。

❸ 逻辑值转化为可以求和的数值

要计算逻辑值 TRUE 的数量，就需要求和，但逻辑值是无法直接求和的。

于是我们将步骤 2 中的逻辑值数组与 1 相乘，将逻辑值数组转变为数值数组（TRUE 为 1，FALSE 为 0）{1;0;0;0}。

❹ SUMPRODUCT：数组运算与求和

乘积和函数 SUMPRODUCT 将数值数组{1;0;0;0}相加，便得到了大于或等于 B2 的唯一值数目 1，即 B2 在 B2:B6 中的降序排名值（值越大排名越靠前）。

此处 UNIQUE 是一个动态数组函数，常规操作无法得到内存数组，而 SUMPRODUCT 函数自带数组运算功能，这就是此处使用 SUMPRODUCT 函数原因。

打铁还需自身硬，

COUNTIF 妙计连环

「梁园虽好非吾乡。」

UNIQUE 之能无需赘言，但目前它仅能在 Office 365 中使用，这显然让混迹于 07、13 乃至 16 版的职场人士望之却步。

职场 Excel 中，中国式排名最常用的套路当属于条件计数函数 COUNTIF。

=SUMPRODUCT(($B$2:$B$6>B3)*(1/COUNTIF($B$2:$B$6,$B$2:$B$6)))+1

▲ 左右滑动查看

公式说明：

❶ ($B$2:$B$6>B3)：条件判断数组

该公式片段将生成一组判断，计数区域 B2:B6 中的每一个数值，是否大于 B3 的逻辑值。

{TRUE;FALSE;TRUE;FALSE;TRUE}，这一点理解起来应该没难度。

❷ 1/COUNTIF($B$2:$B$6,$B$2:$B$6)：条件计数数组运算

与常规运算中执行单一返回值运算不同，此处 COUNTIF 函数将同时执行 5 个条件计数运算。

它分别以 B2:B6 中的每一个单元格为计数条件，求取计数区域 B2:B6 中与之相等的单元格数目，得到一组条件计数结果{1;1;2;1;2}。

再使用 1/COUNTIF 来将计数结果数组转换为其倒数数组{1;1;1/2;1;1/2}。

至于为什么要用倒数，下一段会详细讲解。

❸ SUMPRODUCT(条件判断数组*倒数数组)+1：加权去重

这怎么理解？

我愿意将它解释为加权游戏，游戏规则，有两个：

① 重复 N 次的值，其对应的权重总是 1/N。这 N 个重复值加权后的总和总是等于重复值本身，从而实现去重的作用。
② 通过乘法运算，将逻辑值转化为 0 或 1，满足条件的为 1，不满足条件为 0，让这些 0 或 1 参与上述的去重求和运算，其结果为＞B2 的数值个数，+1 即为排名值。

以 B4 和 B6 为例，重复次数为 2，权重 1/COUNTIF 则为 1/2，它们与 B3 比较的结果都为 TRUE，也就是 1，于是它们的乘积和为 1*1/2+1*1/2=1。

也就是说，在计算比 B3 大的数值个数过程中，B4 和 B6 只计数 1 个，这就是加权去重游戏。

公式中的 SUMPRODUCT 函数起到数组运算、乘积和两个作用，它也可以使用 SUM 函数的数组运算（Ctrl+Shift+Enter）来替代。

{=SUM(IF($B$2:$B$6>B2,1/COUNTIF($B$2:$B$6,$B$2:$B$6)))+1}

▲ 左右滑动查看

于无声处听惊雷，

MATCH 老而弥坚

「廉颇老矣，尚能饭否？」

MATCH 算得上 Excel 函数中的老面孔了，可当你以为它只能作为 VLOOKUP 的辅助时，它却爆冷，在中国式排名问题上惊雷乍起，硬生生打出了高额输出！

快看看，这还是不是你认识的 MATCH 函数？

{=-SUM(-(IF($B$2:$B$6>=B2,MATCH($B$2:$B$6,$B$2:$B$6,))=ROW($1:$5)))}

▲ 左右滑动查看

公式说明：

MATCH=ROW 这一片段是该公式的核心部分。

它使用 MATCH 函数来匹配某一个值首次出现的位置。

通过与该值所在的行序进行比较，从而确定该值是否为首次出现（首次出现的都是唯一值），返回 TRUE，非首次出现则为重复，返回 FALSE，这就构成了一组仅唯一值返回 TRUE 的数组。

再配合 IF 函数进行条件判断，使得只有满足条件的 TRUE 被保留下来。

最后用-SUM(-，先做减法转换逻辑值为 0/1，随后求和，再求相反数，最终得到计数结果。

一朝成名天下知，

FREQUENCY 振聋发聩

FREQUENCY 函数是个冷门函数，它通常用来计算频率分布。

但在求取连续重复次数问题上大放异彩后，它成功引起小花的注意。

每一个函数都有无限可能，FREQUENCY 也是如此，在中国式排名问题上，它振聋发聩！

{=-SUM(-(FREQUENCY($B$2:$B$6,IF($B$2:$B$6>=B2,$B$2:$B$6))>0))}

▲ 左右滑动查看

公式说明：

FREQUENCY 有一个特性，即对于第一次出现的特定值，返回该值对应的频数， 对于第一个后出现相同值的每个重复匹配项，返回零。

也就是说，该函数本身就含有去重的功能。

我们通过 IF 函数判断，取得一组满足条件的间隔值，FREQUENCY 函数求排名区域被该组间隔值分拆成的各个值域中数值的个数，这组频数中的非 0 值个数，即为排名值。

这理解起来颇有难度，建议小伙伴们多花精力，细细咀嚼，定能有所收获。

细心的你一定发现了，MATCH 法和 FREQUENCY 法这两个中国式排名公式与前两个公式存在两个明显不同之处：

❶ 将逻辑值转换为数值 0 或 1 这步运算，前两个公式使用乘号「*」来实现，而后两个公式使用的是负号「-」；
❷ 后两个公式都不需要「+1」即可计算排名值，而前两个公式却需要+1」。

失之毫厘，谬以千里，小伙伴们一定要留意这两点差异哦！

总结一下

本文小花一共分享了中国式排名的四种函数解法，让我们一起来回顾一下：

❶ 去重新兵 UNIQUE，初出茅庐便建奇功；

❷ 排名嫡系 COUNTIF，主力地位不容撼动；

❸ 万年辅助 MATCH，惊雷狂轰炸开脑洞；

❹ 冷门高能 FREQUENCY，登高一呼震耳欲聋。

看完本文的详细解读，你学会做中国式排名了吗？

本文中使用的函数公式都有一定难度，结合练习案例慢慢咀嚼消化，效果更佳哦！

上一篇关于排名问题的文章，点击下方链接即可查看↓↓↓

留下你对排名的疑问或心得，即可收获配套练习，赶快行动起来吧！

---

为感谢同学们对秋叶 Excel 的陪伴和支持，我们决定建立秋叶 Excel 读者群，欢迎大家扫描下方二维码加入哦~

群内不仅能互相交流学习 Excel，还会掉落各种学习资源，助力大家提升办公效率~

↓↓↓

点点在看

干货不断 !

赠人玫瑰

手有余香~