爱华网
EXCEL多字段排名次问题
一、问题：
有一个成绩数据库，需要对总分进行排名，获得考生的总分排名。但考生数量大，总分有重复，比如全省高考成绩中，并列同一分数的有时多达近千人。所以须按某种规定，“总分”如重复，再按其他学科成绩再进行小名次排名。比如，某地排名次规则为：总分如相同，再按“语数外”之和排，如重复再按“综合”，再重复则再按“C1”，最后按“C2”。如以上全部重复，则视同排名一致，录取时同等对待。这样，重复名次将大大减少。
不要指望在EXCEL中使用“排序”功能就能排定名次，主要是并列名次处无法反映出来。且按上述规定，有5个字段需要参与排序，因EXCEL2003仅支持3个字段排序，所以无法实现上述要求（EXCEL2010貌似有10个字段可用于排序）。但即使EXCEL2010版本能够完成5字段排序，也没有获得“名次号”。如果排序后用手工拖拉法生成一正整数序列，因涉及到重名次，该序列不能作为正确的名次号。
看来只能寻求EXCEL内置函数来解决，经过对EXCEL内置函数的一番学习并进行测试，终于找到了比较简单的方法进行排名次。
二、示例：
说明：左侧绿色标记的A列—K列为原始数据，右侧粉色标记的L列—P列是排名次。
L列“排名1”，公式 =RANK(D2,$D$2:$D$115)D2列数据为总分，此公式仅对总分排名次
RANK是排名函数，参数“D2”表示当前参与排名的数据，“$D$2:$D$115”表示绝对引用D2—D115单元格。
表中数据已按名次排序，显露了该函数排名结果，如总分相同则名次相同，但名次号不连续。
M列“TEMP1”，公式 =D2*1000+E2+I2*0.001+J2*0.00001+K2*0.0000001
这是一个临时字段，把参与排名的5个成绩字段合成一个数字，EXCEL的小数可以有15位有效数字，此处用到了13位；
使用这一临时字段的目的，是把5个需要排序的字段合并与一个数字，排名时可以减少重名（5字段完全一致的除外）；
N列“排名2”，公式 =RANK(M2,$M$2:$M$115)
可见用TEMP1字段排名后，原许多同名次的已有区分。如“排名1”第13名重复4人，但在“排名2”中已区分开；
O列“全名次”，公式 =L2+(N2-L2+1)*0.01 或=L12&"-"&(N12-L12+1)（注：二种不同显示形式）
为了既能反映并列名次，又能反映并列名次内小排名，本列通过一简单运算对此二种排名进行合成。
P列“分班排名”，公式 =SUMPRODUCT(($A$2:$A$115=$A2)*($D$2:$D$115>$D2))+1
这个公式稍显复杂，函数SUMPRODUCT的功能是对各数组对应元素相乘并求和，上例中该函数的参数是二个数组，即（数组1）*（数组2），其中：
数组1为“$A$2:$A$115=$A2”，表示A2的值即班级号码是否与A列对应单元格是否相等，其结果为一布尔值，0或1。
数组2为“$D$2:$D$115>$D2”，表示D2值即总分是否小于等于D列对应单元格的值，结果也为布尔类型。
函数SUMPRODUCT在计算时，以 P5=SUMPRODUCT(($A$2:$A$115=$A5)*($D$2:$D$115>$D5))为例，共需分以下步骤来完成运算：（注意：此公式计算D5单元格在D列中的名次）
1、先判断第2行：先求数组1，即A5＝A2否？（示例中为A5、A2的值均为“301”，所以表达式的值为1）
再求数组2，即D2>D5否？（712>704？）得到值1；
2、将上述数组1的值和数组2的值相乘，即1*1，结果是1。这个结果是对第二行对应相乘得到第1个中间结果（积）；
3、然后按上述方法判断第3行，并将判断结果对应相乘，得到第2个中间结果；
4、如此一直判断到结束行（115行），这样数组1和数组2各得到114个值，相乘后得到114个中间结果；
5、最后这114个中间结果相加得到最终结果，并填入P5单元格。
以上公式表示的含义可大致描述为：A列中所有与A5相等的行中，对应D列单元格的值大于D5单元格的有多少个，实际上就是D5单元格的排名。当然计算出来的排名不包括D5自己，所以在例表中的公式还需加1。
以上函数使用时，均可在数据不排序的情况下应用。如下图：
上例中，如果需排序的关键字段列（M列）不是数字，而是文本格式，那就不能简单使用RANK函数，而需要使用SUMPRODUCT函数了。考虑到这个函数计算量比较大，所以如果排名次的数据量比较大，如大于5000，可能会使EXCEL反应速度降低，特别是刚打开数据或更新数据时。大概有稍几秒的反应时间。如果讨厌这种迟延，可以把公式区域复制成数据。
