相信，我们经常会对一组数据进行排序，不管是升序，或者是倒序。这时，我们通常会采用，EXCEL的筛选和排序功能。这样很方便，而且一下就能获得我们所需的结果。但是，有时，你要分析的数据是按照一定顺序的排列的。这时这种做法可能筛选完排序以后，回不到之前的顺序了。这时，我们可以利用RANK和COUNTIF函数来达到我们的目的，老规矩，上案例。说的再多。也不如一个案例来的实在。
图1 案例完成效果
在案例中，我们点进单元格中，然后点击“Enter”，我们可以看到黄色区域，在销售额变化的同时，黄色区域中姓名和销售额都在依据排名进行相应变化。
如何实现上面的案例，先给大家讲如何做，案例中所需的函数和讲解在最后。
第一步，我们来准备了一个只有姓名的空表格
图1 只有姓名的空表格
C列的销售额的公式：=1000*(RANDBETWEEN(1,5))
图2 生成随机数
D列的公式为：=RANK(C3,$C$3:$C$8) 注意，公式中红色部分为绝对引用，绝对引用不会因公式 的自动填充而改变。
效果图：
图3 RANK函数排序完成效果
E列公式为：=RANK.EQ(C3,$C$3:$C$8,0)
F列公式为：=RANK.EQ(C3,$C$3:$C$8,1)
效果图：
图4 RANK.EQ的两种排序完成效果
G列公式为：=RANK.AVG(C3,$C$3:$C$8,1)
H列公式为：=RANK.AVG(C3,$C$3:$C$8,0)
效果图：
图5 RANK.AVG的两种排序完成效果
下面的I列和J列的需求，是我们今天要讲的重点。如何利用RANK或者RANK.EQ和COUNTIF实现不重复的排名。
上公式
I3：=RANK(C3,$C$3:$C$8) COUNTIF(C3:$C$8,C3)-1
I4：=RANK(C4,$C$3:$C$8) COUNTIF(C4:$C$8,C4)-1
J3：=RANK(C3,$C$3:$C$8) COUNTIF(C3:$C$3,C3)-1
J4：=RANK(C4,$C$3:$C$8) COUNTIF($C$3:C4,C4)-1
效果图：
图6 不允许有重复排名的正序和倒序效果
本例中，我们一共贴出了4个公式。不同的地方，我已经给大家用红色标记出来了
这里我们分析I列的COUNTIF(C3:$C$8,C3)和COUNTIF(C4:$C$8,C4)的代表的意义。
COUNTIF(C3:$C$8,C3) 代表的是C3到C8单元格中的值等于C3的个数。
COUNTIF(C4:$C$8,C4) 代表的是C4到C8单元格中的值等于C4的个数。
现在我们来假设一种情况。C3到C8中只有C4和C3是相等的。
这样我们可以得出COUNTIF(C3:$C$8,C3)=2 而COUNTIF(C4:$C$8,C4) =1 。
我们可以延伸出在公式：
I3：=RANK(C3,$C$3:$C$8) COUNTIF(C3:$C$8,C3)-1
I4：=RANK(C4,$C$3:$C$8) COUNTIF(C4:$C$8,C4)-1
当C3和C4 值相等的情况下，I3始终会比I4大1。因为自动填充公式的特性，我们可以知道单元格I5的公式：I5：=RANK(C5,$C$3:$C$8) COUNTIF(C5:$C$8,C5)-1 到这里，大家已经发现规律了。在C3、C4、C5的相等的情况下。I3 = I4 1=I5 2 这就实现了我们排名不重复的排序要求。
这里，就不对J列进行分析了，给大家留个小作业。大家可以试着对J列进行分析，如果有更好的方法，可以在评论区告诉我。
我们接着往下实现案例。
K3：="第" & I3 & "名"
A3：=I3
N3：=VLOOKUP(M3,$A$3:$B$8,2,0)
O3：=VLOOKUP(M3,$A$3:$C$8,3,0)
实现效果如下：
图7 最后实现效果图
看到这里，相信大家对于RANK系列函数排序有一定的了解了。
我们来看一下我们实现上述案例所需要熟悉的函数，同时我们来简单了解一下：
1、RANK系列 RANK、RANK.EQ、RANK.AVG
在EXCEL中RANK、RANK.EQ、RANK.AVG前2个参数是一样的。
=RANK(NUMBER,REF) 返回NUMBER在REF中的排名，正序
=RANK.EQ(NUMBER,REF[,ORDER]) 返回NUMBER在REF中的排名，默认正序，Order= 0 倒序，1或隐藏为正序。相同排名，显示相同排名（整数）
=RANK.AVG(NUMBER,REF[,ORDER]) 返回NUMBER在REF中的排名，默认正序，Order= 0 倒序，1或隐藏为正序。相同排名，显示相同排名（平均数）
NUMBER：所需排列的单个数据
REF：排列的一组数据
ORDER：可省略，代表的是排序方式，值等于0时降序，1为正序或者省略默认为正序
2、RANDBETWEEN(bottom,top) 返回bottom和top之间的整数
bottom：可为整数或小数，要比参数top小否则会报错，整数时包含bottom，小数时，比如11.5，
top：可为整数或小数整数时包含top。小数时，范围为top整数部分加1
3、COUNTIF(range,criteria) 返回满足条件的单元格个数
range：单元格区域
criteria：条件
4、VLOOKUP(lookup_value,Table_array,col_index_num,[range_lookup])
lookup_value：所需查找的值
Table_array：查询的数据表
col_index_num：列号
range_lookup：可以省略。代表的是匹配方式，当值为0或FALSE是精确匹配，值为1、TRUE或者省略是近似匹配
最后，感谢大家的观看，我是EK。如果上述内容能够帮助到你们，希望你们能点赞，关注，评论，你们不清楚的地方我会尽力为大家解答，谢谢大家的支持。我会给大家带来更多关于EXCEL的小技巧。
评论 (0)