SUMPRODUCT多条件排名.xls

1、年级 班级 学号或姓名成绩Rank总排名本年级排名本班级排名总排名2 11 A8866436 11 B8944324 11 C7210106410 11 D6516168516 11 E9722212 12 F4817179417 12 G47181810518 12 H7799529 12 I6615157315 12 J10011111 21 K8944214 21 L4219199519 21 M6812126412 21 N7888438 21 O8866326 22 P9233113 22 Q6714148414 22 R6812126312 22 S7111115211 本例函数用

2、于在不改变原有数据顺序的基础上按分数（或金额）实现排名。 解析： 总排名是个基本公式，效果与Rank()函数相同，F2单元格为例 =SUMPRODUCT($C$2:$C$20C2)*($D$2:$D$20D2)+1 步骤：公式段落结果值 A$C$2:$C$20C2FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE B$D$2:$D$20D2FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;TR

3、UE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE CA*B0;1;0;0;1;0;0;0;0;1;1;0;0;0;0;1;0;0;0相乘运算已经把布尔值转换成数值了,*相当于逻辑运算符and并且 DSUMPRODUCT(C)5除本人外所有比他分数高的人加起来有5个人 ED+16他就排第6了 A作为限制条件把本人排除在外，B判断分数比本人高，A*B的就是别人比本人分数高，条件成立为1，否则为0； 布尔值FALSE(假)相当于0，TRUE(真)相当于1； A*B的运算请参照骗你爱上数组公式等帖子 本年级排名G2为例 =SUMPRODUCT($A$2

4、:$A$20=A2)*($C$2:$C$20C2)*($D$2:$D$20D2)+1 A$A$2:$A$20=A2TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE B$C$2:$C$20C2FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE C$D$2:$D$20D2FALSE;TRUE;FALSE;

5、FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE B和C的运算与上面的相同 A用来限制年级与本人的年级相同 DA*B*C0;1;0;0;1;0;0;0;0;1;0;0;0;0;0;0;0;0;0同年级比他分高的人取值为1，否则为0 ESUMPRODUCT(D)3加起来3个人 FE+14该人排第4 本班级排名H2为例 =SUMPRODUCT($A$2:$A$20=A2)*($B$2:$B$20=B2)*($C$2:$C$20C2)*($D$2:$D$20D2)+1

6、 与年级排名相比，增加了一个限制条件，即班级与本人的班级相同（$B$2:$B$20=B2） 如果进一步增加限制条件，如本乡排名，本县排名，本市排名，可参照上述公式用*连接起来即可。 本排名不是中国式排名。如果有2个人分数相同都排第2，那么下一个分数排第4，而不是第3。 学号或姓名重复问题： 如果学号或姓名唯一，即每个人只有一个成绩，年级排名和班级排名公式可以把($C$2:$C$20C2)去掉 总排名公式如果省略，由于没有了*运算符，需要用-转成数值，参I列公式。 FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;

7、TRUE;TRUE;TRUE;TRUE;TRUE;TRUE FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE 相乘运算已经把布尔值转换成数值了,*相当于逻辑运算符and并且 除本人外所有比他分数高的人加起来有5个人 A作为限制条件把本人排除在外，B判断分数比本人高，A*B的就是别人比本人分数高，条件成立为1，否则为0； TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE

8、;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE 同年级比他分高的人取值为1，否则为0 =SUMPRODUCT($A$2:$A$20=A2)*

9、($B$2:$B$20=B2)*($C$2:$C$20C2)*($D$2:$D$20D2)+1 与年级排名相比，增加了一个限制条件，即班级与本人的班级相同（$B$2:$B$20=B2） 如果进一步增加限制条件，如本乡排名，本县排名，本市排名，可参照上述公式用*连接起来即可。 本排名不是中国式排名。如果有2个人分数相同都排第2，那么下一个分数排第4，而不是第3。 如果学号或姓名唯一，即每个人只有一个成绩，年级排名和班级排名公式可以把($C$2:$C$20C2)去掉 年级班级姓名或学号 成绩Rank总排名本年级排名本班级排名 11 A886532 11 B894432 11 C72101064 11 D65161685 11 A972221 12 F48171794 12 G471818105 12 H779952 12 I66151573 12 J1001111 21 K894421 21 L42191995 21 M68121264 21 N788843 21 O886632 22 P923311 22 Q67141484 22 R68121263 22 S71111152 A有2个成绩时的结果 公式中的($C$2:$C$20C2)限制本成绩与其他人成绩比较，不与自己的成绩比较 公式中的($C$2:$C$20C2)限制本成绩与其他人成绩比较，不与自己的成绩比较