Question

求大神指点Excel函数公式，谢谢。

上图问题如下：
1、D列排名小陈没有成绩不参与排名，D9显示空白就可以了，公式如何？
2、求F1和G1分别在F2:F4和G2:G4显示对应名字，注意有并列排名

Answer 1

D2输入

=IF(C2>0,SUMPRODUCT(N(C$2:C$11>C2))+1,"")

=OFFSET(B$1,SMALL(IF(D$2:D$11<4,ROW($1:$10),10^4),ROW(A1)),)&""

按【CTRL+SHIFT+回车】后，下拉填充

G2输入

=OFFSET(B$1,SMALL(IF((D$2:D$11<>"")*(D$2:D$11>=LARGE(D$2:D$11,3)),ROW($1:$10),10^4),ROW(A1)),)&""

F2\G2有可能不止三个人，所以，公式应尽可能多拉几行。

追问

谢谢大神的及时解答，麻烦你了。

1、D列排名没有问题了，F列排名从第一名开始排下来可以吗？

2、F2\G2确实有可能不止三个人，但我拉下来后还是只显示三个人，是什么原因呢？

追答

F列如果要按照排名进行显示，公式就会非常复杂。

F2输入

=IF(SMALL(D$2:D$20,ROW(A1))<4,OFFSET(B$1,MOD(SMALL(IF(D$2:D$20<>"",D$2:D$20+ROW($1:$19)%),ROW(A1)),1)/1%,),"")

按【CTRL+SHIFT+回车】后，下拉填充公式。如果G列也需要按排名次序显示姓名，公式将更加复杂。

由于排名时，将成绩相同的排在一个名次，如小明和小黄都是第8名，所以，倒数第一名是小黄（或小明），倒数第二名是小明（或小黄），倒数第三名是小王，所以，公式只能取到这三个人，除非还有与小王相同分数的。

追问

对于“1、D列排名没有问题了，F列排名从第一名开始排下来可以吗？”修改一下C列原始数据，0改为空白后，公式又怎么样呢？

追答

一样，试一试不就全清楚了吗？

追问

已试过，如分数我0的变为空时，是会计排名的。

Answer 2

F2公式：
=INDEX(B$2:B$100,MATCH(SMALL(IF(D$2:D$100="",9^9,D$2:D$100+10^(-ROW($1:$99))),ROW(A1)),D$2:D$100+10^(-ROW($1:$99)),0))
数组公式，按CTRL+SHIFT+ENTER完成公式，下拉到F4；

G2公式：
=INDEX(B$2:B$100,MATCH(LARGE(D$2:D$100+10^(-ROW($1:$99)),ROW(A1)),D$2:D$100+10^(-ROW($1:$99)),0))
数组公式，按CTRL+SHIFT+ENTER完成公式，下拉到G4。

追问

C列当分数为空白时，D列排名小陈没有成绩不参与排名，D9显示空白就可以了，公式又如何？

追答

当D9为空时，公式可以满足你的要求，能够得到正确结果。

Answer 3

1、用if嵌套公式解决=if(c:c=0,"",排名公式)
2、

Answer 4

如果有四个人并列第一随意显示其中3个人吗？

Answer 5

答案马上出来了