手把手教你，学会排名

中国式排名

使用RANK类函数排名时，如果出现并列的情况，并列者将占用名次，因此会导致部分名次出现空缺。而按 照中国人习惯的排名计算方法，无论有几个并列第1名，之后的排名仍然是第2名，即并列排名不占用名次。例如，对100、100、90统计的中国式排名结果分别为第一名、第一名和第二名。

示例27-3    中国式排名

图27-3展示的是某银行营业网点存款余额记录表的部分内容，需要根据当日存款余额进行排名，相同数值不占用名次。

在C2单元格中输入以下公式，并向下复制到C13单元格。

=SUMPRODUCT((B$2:B $13>=B2)/COUNTIF(B$2:

B$13,B$2:B$13))

公式的过程相当于计算B$2:B$13单元格区域中大于等于B2单元格中数值的不重复个数。

首先用“B$2:B$13>=B2”分别比较B2:B13单元格区域中每个单元格中的数值与B2单元格中数值的大小，结果如下。

{TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;FALSE}

在Excel四则运算中，逻辑值TRUE 和FALSE分别相当于1和0，因此该部分可以看作是{1;1;1;0;1;0;1;1;1;0;1;0}。

“COUNTIF(B$2:B$13,B$2:B$13)”部分用于分别统计B2:B13单元格区域中每个元素出现的次数，计算结果为{2;1;1;1;1;2;1;2;1;1;1;2}。

用 {1;1;1;0;1;0;1;1;1;0;1;0} 除以 COUNTIF 函数返回的内存数组，也就是如果“ B$2:B$13>=B2 ”的条件成立，就对该数组中对应的元素取倒数，得到如下内存数组。

{0.5;1;1;0;1;0;1;0.5;1;0;1;0}

如果使用分数表示内存数组中的小数，结果如下。

{1/2;1;1;0;1;0;1;1/2;1;0;1;0}。

对照B2:B13单元格区域中的数值可以看出，如果数值小于B2单元格中的数值，该部分的计算结果为0。

如果数值大于等于B2单元格中的数值，并且仅出现一次，则该部分计算结果为1。

如果数值大于等于B2单元格中的数值，并且出现了多次，则计算出现次数的倒数（例如，960 000重复了两次，则每个960 000对应的结果是1/2，两个1/2合计起来还是1）。

最后使用SUMPRODUCT函数求和，得到中国式排名结果。

分组排名统计

示例27-4    分组美式排名计算

图27-4展示的是某银行各营业部柜员日均存款任务的增长记录，需要按 每个柜员的所在营业部进行排名。

在D2单元格中输入以下数组公式，按<Ctrl+Shift+Enter>组合键，将公式向 下复制到D16单元格。

{=SUM(1*(IF(A$2:A$16=A2,C$2:C$16>C2)))+1}

公式使用IF函数，判断A2:A16单元格区域中的内容是否等于A2单元格中的营业部，如果条件成立，就用C列对应的净增百分比与C2单元格中的净增值进行比较，结果如下。

{FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;……;FALSE}

然后使用乘1的方法，将比较后的逻辑值转换为数值。再使用SUM函数求和，结果为同一营业部中净增百分比大于当前净增百分比的个数。

最后将结果加1，得到排名结果。

示例27-5    分组统计中国式排名

上一个示例中的计算结果为美式排名，即相同数值占用名次，如“大洋路”的营业部有两个第4名和一个第6名，现在需要计算中国式排名的结果。

在E2单元格中输入以下公式，并向下复制到E16单元格。

=SUMPRODUCT((A$2:A$16=A2)*(C$2:C$16>=C2)/COUNTIF(C$2:C$16,C$2:C$16))

公式与示例27-3中的计算思路相同，只是加上了分组的判断条件“A$2:A$16=A2”。

百分比排名

百分比排名主要用于成绩分数等统计计算。例如，张三的考试分数为85分，百分比排名是95%，就是将张三的成绩与其他所有参加考试的人的成绩进行比较，张三的成绩比95%的人的成绩要高。

Excel 2016中用于百分比排位的函数包括PERCENTRANK.EXC、PERCENTRANK.INC和PERCENTRANK函数。

3个函数都用于返回某个数值在一个数据集中的百分比排位，区别在于 PERCEN-TRANK.EXC函数返回的百分比值的范围不包含0和1，PERCENTRANK.INC函数返回的百分比值的范围包含0和1。

PERCENTRANK.EXC函数的计算规则如下。

PERCENTRANK函数与PERCENTRANK.INC函数的作用相同，保留PERCENTRANK函数是为了保持与Excel早期版本的兼容性。两个函数的计算规则如下。

函数基本语法如下。

array为必需参数，定义相对位置的数值数组或数值数据区域。

x为必需参数，需要得到其排位的值。如果 x 与数组中的任何一个值都不匹配，则函数将进行插值以返回正确的百分比排位。

significance为可选参数，用于标识返回的百分比值的有效位数。如果省略，则函数结果使用3位小数(0.xxx)。如果该参数小于1，则函数返回错误值#NUM!。

示例27-6    对员工考核成绩进行百分比排名

图27-6展示的是某公司员工综合评分表的部分内容，需要对员工评分进行百分比排名。

选中C2:F2单元格区域，将单元格格式设置为百分比，

小数位数设置为1位，然后在C2单元格中输入 以下公式，并向下复制到C10单元格。

=PERCENTRANK.INC(B$2:B$10,B2)

在D2单元格中输入以下公式进行验证，并向下复制到D10单元格。

=COUNTIF(B$2:B$10,<'&B2)/(COUNT(B$2:B$10)-1)

在E2单元格中输入以下公式，并向下复制到E10单元格。

=PERCENTRANK.EXC(B$2:B$10,B2)

在F2单元格中输入以下公式进行验证，并向下复制到F10单元格。

=(COUNTIF(B$2:B$10,'<'&B2)+1)/(COUNT(B$2:B$10)+1)

如图27-7所示，F列的验证结果与E列的函数计算结果完全相同。D列的验证结果与C列的函数计算结果也相同。