admin管理员组文章数量:1568566
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://www.elefans.com/dianzi/1721814864a898904.html,
本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容,一经查实,本站将立刻删除。
发表评论