excel 利用用Sumproduct函数实现中国式排名

Excel函数 2023-01-08

什么是“中国式排名”

什么是“中国式排名”，与国际通用排名方式有什么区别？以下面两张图为例说明，其中E列是成绩总分，F列和G列都是按照学生成绩从高到低进行的排名，我们可以看到第7行和第8行，有两个学生的总分是一样的，排名当然也该一样，但是对于有并列名次后面的同学，F列和G列，就有区别。

用Sumproduct实现中国式排名

中国式排名-图1

以F列为例，1，2，3，4，5，6，6，8，9，10，其中重点在两个并列第6名后面，跳过了第7名，接着的是第8名，这种方法，是国际上通用的排名方式，即使在奥运会上也是如此，假如有两个并列第1名，就发2枚金牌+1枚铜牌，而不会颁发银牌（没有第2名，两个第1名后面就是第3名）；假如是两个并列第2名，就颁发1枚金牌+2枚银牌（1个第1名，两个第2名，后面就是第4名，没有第3名），就没有了铜牌。

而G列中是我们中国的排名方式，就是1，2，3，4，5，6，6，7，8，9，也就是说，按照我们中国人的习惯，排名只占名额不占名次，这个就是区别。

像国际通用的这种排名方式，Excel系统已经内置了函数Rank.EQ（Rank.Avg），直接使用，就可以了，而对于中国式排名，我们就需要自己采用函数嵌套的方法来处理；当然使用函数来进行中国式排名的方法很多，我们这里讲，使用Sumproduct函数来如何排名。

基本思想:

如果按照总分的高低来排序，比如我们想排罗伟同学的名次，先可以让罗伟同学对应的名次为1，然后用罗伟同学的总分和成绩表中的每一位同学的总分进行一一比较，如果发现某位同学总分大于罗伟同学总分，那么罗伟同学的名次就应该下降一位，即就在罗伟同学对应名次上面+1，当整个成绩表比较完成后，+1的数量罗伟同学的名次，但是我们这样找觉得比较麻烦，所以想个办法，先把区域中把某一分数出现的次数标记出来，最后来统一比较统一相加，比如说只有一个271，就在271后面标记为1，有两个247，后面就标记为2。要实现这个功能，我们可以使用Countif函数，进行条件计数来处理（注意此时函数的两个参数是一样的，两个都是成绩这一列），见示例（表格使用了格式化引用，所以公式中没有显示行列号，而是显示的的名称）：

用Sumproduct实现中国式排名

中国式排名-例1

注意到第7行和第8行，两个总分一致，所以函数的结果都是2，而其他的行，成绩只出现了一次，所以都是1。

在统计完成分数出现的以后，我们就可以采用条件判断，比如罗伟同学，有3名同学总分比他高，他就相当于第4名，那么计算的他的名次就应该是3个1相加，然后再加上他自己本身占的名次1，所以就得到4。这样的方法，在没有遇到有并列名次前，结果都是OK的，但是在遇到了前面有并列名次的时候，就不一样了，比如说要排名杨志勇同学，他前面有7名同学比他分数高，按照国际通用惯例，他就应该是第8名，但是，我们中国排名方式，相同名次，只占名额不占名次，他就是第7名。如果我们还是按照刚才的方法相加，得到的结果就是1+1+1+1+1+1+2+2=10，相当于多加了3，原因就是出在前面相同名次里面的，记数为2，本来只占1个名次的，但这加起来，就相当于有占了4个名次。这个时候我们就需要调整下，可以这样考虑，如果所有计数都取倒数，那么1的倒数就是1，2的倒数就是1/2，两个1/2相加，他们还是1，还是只占据1个名次。同样的道理，如果有3个并列名次，他们计数3，取倒数1/3，在进行相加的时候,3个1/3的和，也是1，也就是说，不论多少个相同的排名，他们都只占1个名次。有了这个思想以后，我们就可以使用sumproduct来进行条件判断，如果满足条件的，就把相应位置上的值求和，达到我们中国式排名的目的，见示例:

用Sumproduct实现中国式排名

说明

=SUMPRODUCT(([总分]>=[@总分])*1,1/[Countif])

其中第一个参数是对比总分里面，比当前行的总分高的行，如果等于或者是高于当前行的总分，则返回TRUE，否则就返回FALSE（返回结果要*1，将逻辑值转换成数字，否则sumproduct函数会讲逻辑值当成0来处理），第二个参数是返回计数项的倒数，也就是1/1，……，1/2，1/2，……然后把使用Sumproduct函数，把对应位置上的数相乘再相加，就得到我们的中国式排名，另外同学也可以讲countif嵌套进第二个参数，这样的话，函数就更加紧凑。

上一篇：excel中的财务函数 FV终值函数使用实例教程下一篇：excel 利用Subtotal函数实现自动编号

excel 利用用Sumproduct函数实现中国式排名

什么是“中国式排名”

基本思想:

说明

热门文章

最新文章

标签列表