怎么将表格设置三等分?(表格三等分线怎么做)
4240
2022-06-08
在 Excel 中,Rank函数用于返回指定数字在数字列表中的排位,应用此功能可实现把数字列表按升序或降序排序或排名次。在 Excel 新版本中,已用Rank.EQ函数和Rank.AVG函数取代了Rank函数。
Rank函数对于有重复数字的排名会把重复数字并排且会留出间隔,如果要求重复数字后的数字紧跟重复数字排名,需要用 Sum + If + CountIf、SumProduct + CountIf 或 Sum + If + FreQuency 组合实现。以下是Rank函数怎么用的11个实例,包含Rank函数的基本用法,Rank 与 Rank.EQ、Rank.AVG 的区别,有重复数字的并排或顺排自动排名、分段排序、把数字列表排序放到行和中国式排名。
1、表达式:RANK(Number, Ref, [Order])
中文表达式:RANK(要排位的数字, 数字列表, [排序方式])
2、说明:
A、Ref 为对包含数字列表的数组或单元格区域的引用;如果 Ref 中有非数字值将被忽略。
B、Order 为可选项,用于指定数字的排位方式;如果 Order 省略或为 0,则Rank函数返回的排位是基于数字列表按降序排列;如果Order 为非 0,则Rank函数返回的排位是基于数字列表按升序排列。
C、如果数字列表中有相同的数字,它们如何排位见“Rank函数怎么用的实例”。
Rank.EQ函数与Rank.AVG函数用于取代Rank函数,它们的区别如下:
1、Rank.EQ 与 Rank 的功能完全一样,即 Rank.EQ 继承了 Rank 的所有功能,Rank.EQ 的表达式如下:
RANK.EQ(Number, Ref, [Order])
2、Rank.AVG 与 Rank(Rank.EQ) 的区别主要表现在对相同数字的排位上;对相同数字,Rank.AVG 返回平均排位,Rank(Rank.EQ) 返回最佳排序,即并列排序。Rank.AVG 的表达式为:
RANK.AVG(Number, Ref, [Order])
(一)无重复数字且数字列表按降序或升序排列的实例
1、假如返回销量为 2685 按降序和按升序的排位。双击 D8 单元格,把公式 =RANK(2685,D2:D7) 复制到 D8,按回车,返回 2;双击 D9,把同一公式复制到 D9,然后在 D2:D7 后输入“,1”,按回车,返回 5;操作过程步骤,如图1所示:
图1
2、说明:
A、公式 =RANK(2685,D2:D7) 中,2685 为要排位的数字,D2:D7 为数字列表,公式省略了排序方式 Order 参数,D2:D7 默认按降序排列;返回 2685 在 D2:D7 中的排位为 2,正是 D2:D7 按降序排列的结果。
B、公式 =RANK(2685,D2:D7,1) 中,把排序方式设置为 1,则 D2:D7 按升序排位,返回 2685 在 D2:D7 中的排位为 5 正是 D2:D7 按升序排列的结果。
(二)无重复数字的自动排序实例
1、假如要把销量按降序排位。双击 E2 单元格,把公式 =RANK(D2,D:D) 复制到 E2,按回车,返回 2;选中 E2,把鼠标移到 E2 右下角的单元格填充柄上,鼠标变为加号后,双击左键,则返回剩余数值的排位,销量列也按降序排列好了;操作过程步骤,如图2所示:
图2
2、公式 =RANK(D2,D:D) 说明:
A、D2 是对列和行的相对引用,往下拖时,D2 会变为 D3、D4 等;D:D 中,$ 表示对行的绝对引用,往下拖时,D 不会变为 D3、D4 等,D 与 D 一样。
B、当公式在 D2 时,返回 D2 在 D2:D7 中的排位 2;当公式在 D3 时,返回 D3 在 D2:D7 中的排位 1;其它的以此类推。
(三)重复数字并排的自动排名次实例
1、假如要把价格按升序排名次。双击 E2 单元格,把公式 =RANK(C2,C:C,1) 复制到 E2,按回车,返回 4;选中 E2,用双击单元格填充柄的方法返回剩余数值的排位,价格列也随之排好了名次;操作过程步骤,如图3所示:
图3
2、公式 =RANK(C2,C:C,1) 说明:
A、公式 =RANK(C2,C:C,1) 与上例的公式 =RANK(D2,D:D) 是一个意思,只是这里把排序方式设置为 1,即 C2:C7 按升序排列。
B、C2:C7 中有一个重复数字 5,两个 5 都排在第 4 位,说明 Rank函数把重复数字并排。
(四)重复数字顺排的自动排名次实例
1、同样以把价格按升序排名次为例。双击 E2 单元格,把公式 =RANK(C2,C:C,1)+COUNTIF(C:C2,C2)-1 复制到 E2,按回车,返回4;选中 E2,同样用双击单元格填充柄的方法返回剩余数值的排位,价格列也随之按升序顺排好了名次;操作过程步骤,如图4所示:
图4
2、公式 =RANK(C2,C:C,1)+COUNTIF(C:C2,C2)-1 说明:
A、C:C2 用于返回 C2 至 C2 的数值,往下拖时,C2 会变 C3、C4 等,而 C 不会变,也就实现了从 C2 单元格到拖到单元格数字个数的统计。那么CountIf函数是怎么实现把重复数字顺排的?
B、当公式在 E2 时,COUNTIF(C:C2,C2) 在 C2 统计 C2(即 5)的个数,结果为 1;RANK(C2,C:C,1) 返回 C2 在 C2:C7 中的排位为 4,4 + 1 - 1 恰好等于 4。
C、当公式在 E3 时,COUNTIF(C:C3,C3) 在 C2:C3 统计 C3(即 3)的个数,结果也为 1;RANK(C3,C:C,1) 返回 C3 在 C2:C7 中的排位为 2,2 + 1 - 1 恰好等于 2。
D、当公式在 E4 时,COUNTIF(C:C4,C4) 在 C2:C4 统计 C4 的个数,结果为 2(C2:C4 有两个 5),而 RANK(C4,C:C,1) 返回 C4 在 C2:C7 中的排位为 4,4 + 2 - 1 恰好等于 5;这样就实现了重复数字 5 的顺排(一个排 4,另一个排 5)。
(一)返回重复数字的排位
1、假如用三个函数分别返回 5 在“价格”中的排位。双击 E2 单元格,把公式 =RANK(5,C2:C7) 复制到 E2,按回车,返回 2;双击 F2,把公式 =RANK.EQ(5,C2:C7) 复制到 F2,按回车,也返回 2;双击 G2,把公式 =RANK.AVG(5,C2:C7) 复制到 G2,按回车,返回 2.5;操作过程步骤,如图5所示:
图5
2、公式说明:
从演示可知,Rank函数与Rank.EQ函数返回的结果一样,而Rank.AVG函数返回 2.5,这个 2.5 是怎么来的?C2:C7 中共有两个 5,按降序排列,一个 5 排在第 2 位,另一个 5 排在第 3 位,Rank.AVG 返回相同数值的平均排位,2 与 3 的平均值为 2.5,因此 5 的排位为 2.5。
(二)重复数字自动排序
1、假如用三个函数分别把“价格”按降序排位。双击 E2 单元格,把公式 =RANK(C2,C:C) 复制到 E2,按回车,返回 2;用双击单元格填充柄的方法返回剩余价格的排位。双击 F2,把公式 =RANK.EQ(C2,C:C) 复制到 F2,按回车,返回 2,用同样方法返回剩余价格的排位;再把公式 =RANK.AVG(C2,C:C) 复制到 G2,按回车,返回 2.5,用同样方法返回剩余价格的排位;操作过程步骤,如图6所示:
图6
2、公式说明:
从演示可知,Rank函数与Rank.EQ函数的排序结果完全一致,而Rank.AVG函数对于重复数字取平均值排位,两个 5 都排 2.5。
(一)Rank + OffSet + Row 组合实现分段排序
1、假如要求把销量按季度分段排序。双击 C2 单元格,把公式 =RANK(B2,OFFSET(B:B,INT((ROW()-2)/3)*3,),1) 复制到 C2,按回车,返回 2;选中 C2,用双击单元格填充柄的方法返回其它数字的排位;操作过程步骤,如图7所示:
图7
2、公式 =RANK(B2,OFFSET(B:B,INT((ROW()-2)/3)*3,),1) 说明:
A、公式中的 3 表示每段 3 个数字;ROW() 用于返回公式所在行的行号;Int函数用于取整;INT((ROW()-2)/3)*3,) 用于计算 OffSet 函数要往下移的行数。
B、B:B 为第一段;OFFSET(B:B,INT((ROW()-2)/3)*3,) 用于移到一下段,它是怎么实现移到下一段的?
第一段(B:B):
当公式在 C2 时,ROW() 返回 2,INT((ROW()-2)/3)*3 变为 INT((2-2)/3)*3,结果为 0;则 OFFSET(B:B,INT((ROW()-2)/3)*3,)变为 OFFSET(B:B,0,),意思是把 B:B 都下移 0 行,即不下移,则 OFFSET(B:B,0,) 返回 B:B;则公式变为 =RANK(B2,B:B),最后在 B2 到 B4 中返回 B2 的排位 2。
当公式在 C3 时,ROW() 返回 3,(ROW()-2)/3 变为 INT(1/3),接着取整,结果为 0,0 * 3 结果也是 0;OffSet 变为 OFFSET(B:B,0,);则公式变为 =RANK(B3,B:B),最后返回 B3 在 B2 至 B4 的排位 3。
当公式在 C4 时,ROW() 返回 4,(ROW()-2)/3 变为 INT(2/3),接着取整,结果为 0,0 * 3 结果也是 0;OffSet 变为 OFFSET(B:B,0,);则公式变为 =RANK(B4,B:B),最后返回 B4 在 B2 至 B4 的排位 1。
第二段(B:B):
当公式在 C5 时,ROW() 返回 5,(ROW()-2)/3 变为 INT(3/3),接着取整,结果为 1,1 * 3 结果也是 3;OffSet 变为 OFFSET(B:B,3,),意思是把 B:B 都下移三行,则 B:B 变为 B:B,也就移到了第二段;则公式变为 =RANK(B5,B:B),最后返回 B5 在B:B 中的排位 1;其它的以此类推。
OFFSET(B:B,3,) 把 B:B 都下移三行的执行过程:第一次从 B2:B4 中取出 B2,然后返回 B2 下距 B2 3 行的引用,即返回 B5;第二次从 B2:B4 中取出 B3,然后返回 B3 下距 B3 3 行的引用,即返回 B6;第三次也是这样返回对 B7 的引用,从而实现把 B:B 都下移三行。
(二)Rank + OffSet + Column 组合实现把数字列表排序放到行
1、假如要把 A 列的数字排位放到行。双击 C2 单元格,把公式 =RANK(OFFSET($A1,COLUMN()-3,),$A1:$A6) 复制到 C2,按回车,返回 6;选中 C2,把鼠标移到 C2 右下角的单元格填充柄上,鼠标变为加号后,按住左键并往右拖,一直拖到排完所有数字;操作过程步骤,如图8所示:
图8
2、公式 =RANK(OFFSET($A1,COLUMN()-3,),$A1:$A6) 说明:
A、$A1 表示对列的绝对引用,当往右拖时,A1 不会变为 B1、C1 等;COLUMN() 用于返回公式所有列的列号。
B、OFFSET($A1,COLUMN()-3,) 用于返回 A1 至 A6 中的数值,具体如下:
当公式在 C2 时,COLUMN() 返回 3,则 OffSet 变为 OFFSET($A1,3-3,),意思是返回距 A1 0 行的引用,即返回对 A1 的引用,也就是返回 6;则公式变为 =RANK(6,$A1:$A6),接着用Rank函数返回 6 在 A1:A6 中的排位 6。
当公式在 D2 时,COLUMN() 返回 4,则 OffSet 变为 OFFSET($A1,4-3,),意思是返回距 A1 1 行的引用,即返回对 A2 的引用,也就是返回 24;则公式变为 =RANK(24,$A1:$A6),接着用Rank函数返回 24 在 A1:A6 中的排位 4;其它的以此类推。
如果要求重复数字并排,重复数字下一个数字接着重复数字排位而不是隔一位排,例如 5、3、3、2,若按降序排名次,要求两个 3 都排第 2 名,而 2 排第 3 名;这种排名方式通常称为中国式排名,用Rank函数无法实现,但用其它函数组合能实现,具体如下:
(一)Sum + If + CountIf
1、同样以把“价格”列降序与升序自动排名为例。双击 E2 单元格,把公式 =SUM(IF(C$2:C$7>=C2,1/COUNTIF(C$2:C$7,C$2:C$7),"")) 复制到 E2,按 Ctrl + Shift + 回车,返回 2;选中 E2,用双击 E2 右下角的单元格填充柄的方法返回剩余数字的排名;再双击 F2,把公式=SUM(IF(C$2:C$7<=C2,1/COUNTIF(C$2:C$7,C$2:C$7),"")) 复制到 F2,按 Ctrl + Shift + 回车,返回 4,同样用双击单元格填充柄的方法返回剩余排名;操作过程步骤,如图9所示:
图9
2、降序排名公式 =SUM(IF(C$2:C$7>=C2,1/COUNTIF(C$2:C$7,C$2:C$7),"")) 说明:
A、中国式排名的关键有两点,一点是找出重复数字,另一点是把重复数字后的排名上升一位。
B、C$2:C$7 以数组形式返回 C2 至 C7 中的值,$ 是对行的绝对引用,作用是往下拖时,C2 不会变 C3、C4 等,C7 不会变 C8、C9 等,以确保始终取 C2:C7 的数字。
C、1/COUNTIF(C$2:C$7,C$2:C$7) 用于找出重复数字;第一次执行时,从 C2:C7 中取出 C2(即 5),然后统计 C2 在 C2:C7 中的个数,结果为 2;第二次执行时,从 C2:C7 中取 C3(即 3),然后统计 C3 在 C2:C7 中的个数,结果为 1;其它的以此类推,最后返回 {2;1;2;1;1;1}。
D、则 1/COUNTIF(C$2:C$7,C$2:C$7) 变为 1/{2;1;2;1;1;1},进一步计算,用 1 除以数组中的每个元素,结果为 {0.5;1;0.5;1;1;1},这样就把重复的数字(两个 5)变为了一个,后面的步骤会把两个 0.5 相加,这其实就是过滤重复数字,在前面的篇章已经介绍过。
E、C$2:C$7>=C2 为 If 条件,用于找出 C2:C7 中所有大于等于 C2 的数值;执行时,第一次取出 C2(即 5),C2 >= C2 成立,返回 True;第二次取出 C3(即 3),C3 >= C2 不成立,也返回 False;其它的以此类推,最后返回 {TRUE;FALSE;TRUE;TRUE;FALSE;FALSE}。
F、则公式变为 =SUM(IF({TRUE;FALSE;TRUE;TRUE;FALSE;FALSE},{0.5;1;0.5;1;1;1},"")),执行时,第一次从条件数组中取出第一个元素True,由于它为真,所以返回第二个数组中与条件数组对应的元素,即返回第二个数组第一个元素 0.5;第二次从条件数组取出第二个元素 FALSE,由于它为假,所以返回 If 的第三个参数 "";其它以此类推,最后返回 {0.5;"";0.5;1;"";""}。
G、则公式变为 =SUM({0.5;"";0.5;1;"";""}),最后用 Sum 把数组中的每个元素相加,由于忽略空值只把数字相加,所以结果为 2;两个0.5 是重复数字 C2 与 C4 中 5 的值,1 是大于 5 的值(即 C5 中的 12 ),把它们相加恰好是重复数字 5 的排名。
H、当公式在 C5 时,公式变为 =SUM(IF(C$2:C$7>=C5,1/COUNTIF(C$2:C$7,C$2:C$7),"")),1/COUNTIF(C$2:C$7,C$2:C$7) 同样返回{0.5;1;0.5;1;1;1};C$2:C$7>=C5 返回 {FALSE;FALSE;FALSE;TRUE;FALSE;FALSE},即只有 C5(即 12)的位置为真;则公式变为:=SUM(IF
(FALSE;FALSE;FALSE;TRUE;FALSE;FALSE},{0.5;1;0.5;1;1;1},"")),进一步计算变为 =SUM({"";"";"";1;"";""}),数组中只有 C5 的位置为1,其它的都为 "",最后返回 1,也就是最大值 C5 的排名。
另外,用这个公式 =SUM(IF(C$2:C$7<=C2,"",1/COUNTIF(C$2:C$7,C$2:C$7)))+1 也可以实现降序排名,即把 >= 改为 <=,同时 If 的后面两个参数要调换次序,并且还要在公式后加 1。
3、升序排名公式 =SUM(IF(C$2:C$7<=C2,1/COUNTIF(C$2:C$7,C$2:C$7),"")) 说明:
升序排名公式与降序排名公式的区别仅是 If 条件中的 >= 变为 <=,即由找出所有大于等于当前单元格(C2)的数字变为找出所有小于等于当前单元格的数字。
(二)SumProduct + CountIf
1、同样以把“价格”列降序与升序自动排名为例。双击 E2 单元格,把公式 =SUMPRODUCT((C$2:C$7>=C2)/COUNTIF(C$2:C$7,C$2:C$7)) 复制到 E2,按回车,返回 2;选中 E2,用双击单元格填充柄的方法返回剩余数字的排名;再双击 F2,把公式 =SUMPRODUCT((C$2:C$7<=C2)/COUNTIF(C$2:C$7,C$2:C$7)) 复制到 F2,按回车,返回 4,再用双击单元格填充柄的方法返回剩余排名;操作过程步骤,如图10所示:
图10
2、降序排名公式 =SUMPRODUCT((C$2:C$7>=C2)/COUNTIF(C$2:C$7,C$2:C$7)) 说明:
A、公式与 Sum + If + CountIf 是一个意思,只是这里用 SumProduct 代替了 Sum + If。
B、COUNTIF(C$2:C$7,C$2:C$7) 返回 {2;1;2;1;1;1};(C$2:C$7>=C2) 返回 {TRUE;FALSE;TRUE;TRUE;FALSE;FALSE}。
C、则公式变为 =SUMPRODUCT({TRUE;FALSE;TRUE;TRUE;FALSE;FALSE}/{2;1;2;1;1;1}),进一步计算,第一次分别取出分子与分母数组的第一个元素 TRUE 和 2,计算时,TRUE 被转为 1、FALSE 被转为 0,1/2 = 0.5;第二次再分别取出它们的第二元素 FALSE 和 1,0/2 = 0;其它的以此类推。
D、则公式变为 =SUMPRODUCT({0.5;0;0.5;1;0;0}),最后对数组中的元素求和,结果为 2,即重复数字 5 的排名为 2。
另外,用这个公式 =SUMPRODUCT((C$2:C$7>C2)*(1/COUNTIF(C$2:C$7,C$2:C$7)))+1 也可以实现降序排名,也就是把条件 C$2:C$7>C2 提取出作为一个独立的条件,再用 1 比 COUNTIF(C$2:C$7,C$2:C$7) 作为第二个条件,并用 * 把两个条件连接起来表示“与”的关系,最后还要在公式后面加 1。
3、升序排名公式 =SUMPRODUCT((C$2:C$7<=C2)/COUNTIF(C$2:C$7,C$2:C$7)) 说明:
升序排名公式与降序排名公式的区别仅是把分子 C$2:C$7<=C2 中的 >= 改为 <=,即由找出所有大于等于当前单元格(C2)的数字变为找出所有小于等于当前单元格的数字。
(三)Sum +If + FreQuency
1、同样以把“价格”列降序与升序自动排名为例。双击 E2 单元格,把公式 =SUM(--(IF(FREQUENCY(C$2:C$7,C$2:C$7),C$2:C$7>C2)))+1 复制到 E2,按 Ctrl + Shift + 回车,返回 2;选中 E2,双击 E2 的单元格填充柄返回剩余数字的排名;再双击 F2,把公式 =SUM(--(IF(FREQUENCY(C$2:C$7,C$2:C$7),C$2:C$7<=C2))) 复制到 F2,按回车,返回 4,再双击 F2 的单元格填充柄返回剩余排名;操作过程步骤,如图11所示:
图11
2、降序排名公式 =SUM(--(IF(FREQUENCY(C$2:C$7,C$2:C$7),C$2:C$7>C2)))+1 说明:
A、FREQUENCY(C$2:C$7,C$2:C$7) 用于找出 C2:C7 中每个数字在 C2:C7 出现的频率,功能有点像 COUNTIF(C$2:C$7,C$2:C$7),但FreQuency 对重复数字只统计一次,而 CountIf 统计两次。
执行时,第一次先从第二个参数中取 C2(即 5),然后在 C2:C7 中统计小于等于 5 且大于 3.5 的数值个数(结果为 2),因为直接把C2:C7 作为 Frequency函数的 Bins_Array 参数(即对要统计的数值划分区间参数),而比 5 小的值是 3.5,因此 3.5 与 5 为一个区间;第二次先从第二个参数中取 C3(即 3),同样在 C2:C7 中统计小于等于 3 且大于 2.5 的数值个数(结果为 1);第三次取出 5,由于 5 已经统计过不再统计,因此结果为 0;其它的以此类推,最后返回数组 {2;1;0;1;1;1;0}。
C2:C7 中只有 6 个数字,返回的数组为什么会有 7 个元素,因为FreQuency函数返回的数组会比 Bins_Array 参数多一个元素。
B、C$2:C$7>C2 返回 {FALSE;FALSE;FALSE;TRUE;FALSE;FALSE}。
C、IF(FREQUENCY(C$2:C$7,C$2:C$7),C$2:C$7>C2) 变为 IF({2;1;0;1;1;1;0},{FALSE;FALSE;FALSE;TRUE;FALSE;FALSE});执行时,第一次从条件数组取第一个元素 2,由于大于 0 的视为真、小于 0 的视为假,所以返回 If 的第二个参数,即第二个数组中与 2 对应的元素 FALSE;第二次从条件数组取第二个元素 1,由于为真,返回第二个数组中第二个元素 FALSE;第三次从条件数组取第三个元素 0,由于为假,所以返回 If 的第三个参数,由于 If 省略了第三个参数,默认返回 FALSE;其它的以此类推,最后返回{FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE}。
D、则公式变为 =SUM(--{FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE})+1,进一步计算,把数组的所有元素转为数值,则公式变为 =SUM({0;0;0;1;0;0;0})+1,-- 用于把文本或逻辑值转为数值,相当于Value函数,最后用 Sum 把数组中的元素加起来再加 1,结果为 2。
3、升序排名公式 =SUM(--(IF(FREQUENCY(C$2:C$7,C$2:C$7),C$2:C$7<=C2))) 说明:
升序排名公式与降序排名公式的区别仅为把 C$2:C$7>C2 中的 > 改为 <=,即由找出所有大于当前单元格(C2)的数字变为找出所有小于等于当前单元格的数字,并且把公式后面的 1 去掉。
版权声明:本文内容由网络用户投稿,版权归原作者所有,本站不拥有其著作权,亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容,请联系我们jiasou666@gmail.com 处理,核实后本网站将在24小时内删除侵权内容。