秋无痕淘宝天猫优惠券网	秋无痕常用软件全功能装机光盘 2023年金秋版	秋无痕一键优化Windows 10专版	秋无痕一键优化Windows 11专版
秋无痕论坛官方QQ群	获取无痕币和提高等级

主题 : 用Excel排名次（名次号连续；分组排名）(中国式排名)

使用道具 | 复制链接 | 浏览器收藏 | 打印

加为好友

wdygy

级别: 五分秋意

作者资料发送短消息

UID: 110727
精华: 0
发帖: 189
威望: 7570 点
无痕币: 781 WHB
贡献值: 0 点
在线时间: 69(时)
注册时间: 2011-01-09
最后登录: 2018-05-16

0 发表于: 2011-09-22 08:00｜请将IE368导航设置为首页，支持论坛

只看楼主 | 小中大

0 用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	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