admin管理员组文章数量:1530912
2024年7月24日发(作者：)
用Excel排名次（名次号连续；分组排名）(中国式排名)
一、用rank()函数排名次，相同数据有相同位次，但位次号不连续，解决这一问题的方法：
分
数
88
77
59
85
59
67
88
名
公 式
次
1
=SUMPRODUCT((A$2:A$8>=A2)/COUNTIF(A$2:A$8,A$2:A$8))
3
=SUMPRODUCT((A$2:A$8>=A3)/COUNTIF(A$2:A$8,A$2:A$8))
5
=SUMPRODUCT((A$2:A$8>=A4)/COUNTIF(A$2:A$8,A$2:A$8))
2
=SUMPRODUCT((A$2:A$8>=A5)/COUNTIF(A$2:A$8,A$2:A$8))
5
=SUMPRODUCT((A$2:A$8>=A6)/COUNTIF(A$2:A$8,A$2:A$8))
4
=SUMPRODUCT((A$2:A$8>=A7)/COUNTIF(A$2:A$8,A$2:A$8))
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("nary")
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 = + 1 Else rankchina = "超出范围"
Else
Set d = CreateObject("nary")
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 = + 1 Else rankchina = "超出范围"
End If
End Function
二、 分组排序方法：
数据
1
2
3
4
5
组名
a
a
a
a
b
组内名次
公 式
5
=SUMPRODUCT((B$2:B$20=B2)*(A2 4 =SUMPRODUCT((B$2:B$20=B3)*(A3 3 =SUMPRODUCT((B$2:B$20=B4)*(A4 2 =SUMPRODUCT((B$2:B$20=B5)*(A5 4 =SUMPRODUCT((B$2:B$20=B6)*(A6 6 7 8 9 10 11 12 13 14 15 16 17 18 19 b a b b c c c c d d d d d d 3 1 2 1 4 3 2 1 6 5 4 3 2 1 =SUMPRODUCT((B$2:B$20=B7)*(A7 =SUMPRODUCT((B$2:B$20=B8)*(A8 =SUMPRODUCT((B$2:B$20=B9)*(A9 =SUMPRODUCT((B$2:B$20=B10)*(A10 =SUMPRODUCT((B$2:B$20=B11)*(A11 =SUMPRODUCT((B$2:B$20=B12)*(A12 =SUMPRODUCT((B$2:B$20=B13)*(A13 =SUMPRODUCT((B$2:B$20=B14)*(A14 =SUMPRODUCT((B$2:B$20=B15)*(A15 =SUMPRODUCT((B$2:B$20=B16)*(A16 =SUMPRODUCT((B$2:B$20=B17)*(A17 =SUMPRODUCT((B$2:B$20=B18)*(A18 =SUMPRODUCT((B$2:B$20=B19)*(A19 =SUMPRODUCT((B$2:B$20=B20)*(A20 注：用“工具/公式审核/公式求值：步入、步出、求值……”可窥探公式的奥秘。
版权声明：本文标题：用Excel排名次-中国式排名 内容由热心网友自发贡献，该文观点仅代表作者本人，
转载请联系作者并注明出处：https://m.elefans.com/dianzi/1721814864a898904.html，
本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。
发表评论