EXCEL 排名（函数+VBA)两人种方法

Question

我要找两种方法来解决排名，要求两种方法排名都是依次排名，如：第二名有两人，那应该显示的1、2、2、3名，而不是RANK做出来的会空了一个名次1、2、2、4 。求两人种方法。
这个我已经看到了，但是不理解" =sumproduct((b$2:b$10>b2)/countif(b$2:b$10,b$2:b$10))+1” 谁能解释一下。

Answer 1

举例说明。

例如有表格如图：

第一步：在D2输入公式：=RANK(C2,C$2:C$7).然后将该公式下拉至C7，结果如图：

第二步：创建宏“排名”，编制代码如下：

Sub 排名()

Dim MYARR(1 To 100, 1 To 3)

Dim I, J, K, L As Integer

Dim BJ, XM As String

Dim CJ As Double

I = 2

Do While Cells(I, 3) <> ""

For J = 1 To 3

    MYARR(I - 1, J) = Cells(I, J)

Next J

I = I + 1

Loop

I = I - 2

For J = 1 To I - 1

    For K = J + 1 To I

        If MYARR(J, 3) < MYARR(K, 3) Then

           BJ = MYARR(J, 1)

           XM = MYARR(J, 2)

           CJ = MYARR(J, 3)

           MYARR(J, 1) = MYARR(K, 1)

           MYARR(J, 2) = MYARR(K, 2)

           MYARR(J, 3) = MYARR(K, 3)

           MYARR(K, 1) = BJ

           MYARR(K, 2) = XM

           MYARR(K, 3) = CJ

        End If

    Next K

Next J

Cells(1, 6) = "班级"

Cells(1, 7) = "姓名"

Cells(1, 8) = "成绩"

For J = 1 To I

    For K = 1 To 3

        Cells(J + 1, K + 5) = MYARR(J, K)

    Next K

Next J

End Sub

执行代码后结果如图：

Answer 2

先给一组数据,这样可以更直观地解释这个函数

=sumproduct((b$2:b$10>b2)/countif(b$2:b$10,b$2:b$10))+1

公式在C2单元格的时候,来解释这个公式

首先看(b$2:b$10>b2)拿B2:B10单元格与B2单元格的数据进行比较,如果大于B2,就返回TRUE

这一段可以返回一个数组

{FALSE;TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE}

可以看做是{0;1;1;1;1;0;1;1;0}

再来看这一段countif(b$2:b$10,b$2:b$10)

分别统计B2:B10中每个数据的个数

得到这样一个数组

{3;2;1;1;1;3;1;2;3}

然后计算这两个数组的商

(b$2:b$10>b2)/countif(b$2:b$10,b$2:b$10)

即

{0;1;1;1;1;0;1;1;0}/{3;2;1;1;1;3;1;2;3}

得到一个新数组

{0;0.5;1;1;1;0;1;0.5;0}

然后再用SUMPRODUCT函数对这些数进行求和

得出结果为 5

再 +1

所以结果就是6

数组公式,需要一步一步地去理解,逻辑思维要强才能正解公式

可以在公式中按F9分别查看每一次运算的值

追问

能不能再解释一下这个函数高手“=SUM(IF($A$3:$A$19>A3,1/COUNTIF($A$3:$A$19,$A$3:$A$19)))+1”

追答

这个函数和那个函数是一样的,只不过,前面那个函数输入后直接按回车
这个公式输入后需要按SHIFT+CTRL+ENTER三键
因为SUMPRODUCT是数组函数
而SUM只是普通求和函数

本回答被提问者和网友采纳

Answer 3

其实你把公式这样看就简单了 =sumproduct(1/countif(b$2:b$10,b$2:b$10)) 是求b2:b10中一共有几个不重复的数据（在这个数组中看以看成是一个固定数）
sumproduct（(b$2:b$10>b2)/countif(b$2:b$10,b$2:b$10)）统计出比b2大的数有几个，也就是b2的排名，但是由于实际上的分母应该是实际个数（肯定比10大）而不是不重复的个数10，所以+1

Answer 4

楼主，你这样理解是不科学的，1、 2 、2 已经是三个人了，接下来就是第4名，你这样要求的和大家平时常识上的东西不吻合，单靠表格函数肯定不行了，想达到这样的效果要自己编vba才行追问

我必须要这样的结果呀！

追答

那要写一个vba的

追问

会我就不发贴了。

Answer 5

这个你直接查着两个单词的意义就明白了