Excel中经常需要使用到自动求和公式，自动求和具体该如何操作呢?接下来小编举例简单的例子告诉大家excel设置自动求和公式的方法。excel设置自动求和公式的方法设置自动求和公式步骤1：将鼠标放在要求和数据区域的紧挨着的下面一个单元格，如下图的D10单元格，然后点击【开始】-【自动求和】按钮就可以自动求D......
excel 利用用Sumproduct函数实现中国式排名
Excel函数
2021-11-25 09:05:39
什么是“中国式排名”
什么是“中国式排名”，与国际通用排名方式有什么区别？以下面两张图为例说明，其中E列是成绩总分，F列和G列都是按照学生成绩从高到低进行的排名，我们可以看到第7行和第8行，有两个学生的总分是一样的，排名当然也该一样，但是对于有并列名次后面的同学，F列和G列，就有区别。
中国式排名-图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函数，进行条件计数来处理（注意此时函数的两个参数是一样的，两个都是成绩这一列），见示例（表格使用了格式化引用，所以公式中没有显示行列号，而是显示的的名称）：
中国式排名-例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(([总分]>=[@总分])*1,1/[Countif])
其中第一个参数是对比总分里面，比当前行的总分高的行，如果等于或者是高于当前行的总分，则返回TRUE，否则就返回FALSE（返回结果要*1，将逻辑值转换成数字，否则sumproduct函数会讲逻辑值当成0来处理），第二个参数是返回计数项的倒数，也就是1/1，……，1/2，1/2，……然后把使用Sumproduct函数，把对应位置上的数相乘再相加，就得到我们的中国式排名，另外同学也可以讲countif嵌套进第二个参数，这样的话，函数就更加紧凑。
标签： SUMPRODUCT函数
相关文章