用Excel排名次（名次号连续；分组排名）(中国式排名)
          一、用rank()函数排名次，相同数据有相同位次，但位次号不连续，解决这一问题的方法：
分数
名次
公    式
88
1
=SUMPRODUCT((A$2:A$8>=A2)/COUNTIF(A$2:A$8,A$2:A$8))
77
3
=SUMPRODUCT((A$2:A$8>=A3)/COUNTIF(A$2:A$8,A$2:A$8))
59
5
=SUMPRODUCT((A$2:A$8>=A4)/COUNTIF(A$2:A$8,A$2:A$8))
85
2
=SUMPRODUCT((A$2:A$8>=A5)/COUNTIF(A$2:A$8,A$2:A$8))
59
5
=SUMPRODUCT((A$2:A$8>=A6)/COUNTIF(A$2:A$8,A$2:A$8))
67
4
=SUMPRODUCT((A$2:A$8>=A7)/COUNTIF(A$2:A$8,A$2:A$8))
88
1
=SUMPRODUCT((A$2:A$8>=A8)/COUNTIF(A$2:A$8,A$2:A$8))
            或使用函数：rankchina(数据，范围，参数）。参数为1时顺序排名，0为逆序排名。
            Public Function rankchina(data, data_area, ref)
              Dim d As Object, e As Object, rng, i As Integer
              If ref = 1 Then
                      Set d = CreateObject("scripting.dictionary")
                      For Each rng In data_area
                          If rng = data Then i = i + 1 Else If rng < data Then d(rng * 1) = 1
                      Next
                      If i > 0 Then rankchina = d.Count + 1 Else rankchina = "超出范围"
              Else
                      Set d = CreateObject("scripting.dictionary")
                      For Each rng In data_area
                          If rng = data Then i = i + 1 Else If rng > data Then d(rng * 1) = 1
                      Next
                      If i > 0 Then rankchina = d.Count + 1 Else rankchina = "超出范围"
   
   
              End If
            End Function
          二、 分组排序方法：
数据
组名
组内名次
公        式
1
a
5
=SUMPRODUCT((B$2:B$20=B2)*(A2<A$2:A$20))+1
2
a
4
=SUMPRODUCT((B$2:B$20=B3)*(A3<A$2:A$20))+1
3
a
3
=SUMPRODUCT((B$2:B$20=B4)*(A4<A$2:A$20))+1
4
a
2
=SUMPRODUCT((B$2:B$20=B5)*(A5<A$2:A$20))+1
5
b
4
=SUMPRODUCT((B$2:B$20=B6)*(A6<A$2:A$20))+1
6
b
excel利用rank排名次公式
3
=SUMPRODUCT((B$2:B$20=B7)*(A7<A$2:A$20))+1
7
a
1
=SUMPRODUCT((B$2:B$20=B8)*(A8<A$2:A$20))+1
8
b
2
=SUMPRODUCT((B$2:B$20=B9)*(A9<A$2:A$20))+1
9
b
1
=SUMPRODUCT((B$2:B$20=B10)*(A10<A$2:A$20))+1
10
c
4
=SUMPRODUCT((B$2:B$20=B11)*(A11<A$2:A$20))+1
11
c
3
=SUMPRODUCT((B$2:B$20=B12)*(A12<A$2:A$20))+1
12
c
2
=SUMPRODUCT((B$2:B$20=B13)*(A13<A$2:A$20))+1
13
c
1
=SUMPRODUCT((B$2:B$20=B14)*(A14<A$2:A$20))+1
14
d
6
=SUMPRODUCT((B$2:B$20=B15)*(A15<A$2:A$20))+1
15
d
5
=SUMPRODUCT((B$2:B$20=B16)*(A16<A$2:A$20))+1
16
d
4
=SUMPRODUCT((B$2:B$20=B17)*(A17<A$2:A$20))+1
17
d
3
=SUMPRODUCT((B$2:B$20=B18)*(A18<A$2:A$20))+1
18
d
2
=SUMPRODUCT((B$2:B$20=B19)*(A19<A$2:A$20))+1
19
d
1
=SUMPRODUCT((B$2:B$20=B20)*(A20<A$2:A$20))+1
          注：用“工具/公式审核/公式求值：步入、步出、求值……”可窥探公式的奥秘。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。