Excel表格自动排序的两种方法LOOKUP+RANK法
LOOKUP函数的两分法几乎能解决查询相关的所有问题,而自动排序,可以认为是按排序值查询,自然也不在话下!
=LOOKUP(1,0/(RANK($B$2:$B$8,$B$2:$B$8)=ROW()-1),$A$2:$A$8)
①公式说明:
RANK($B$2:$B$8,$B$2:$B$8)
显然,这是一个数组公式,由于LOOKUP自带数组运算属性,所以无需按【Ctrl+Shift+Enter】来执行运算也可以使得排名函数RANK返回一组排名值。
RANK函数的排序值和排序范围参数都是B2:B8,通过数组运算,返回表示B2:B8中的每一个值对应排序大小的序数值组{2;3;1;5;4;6;7}。
②公式说明:
0/(①=ROW()-1)
ROW-1表示当前行号-1,从E2到E8依次为1-7,即E2所要查找的排名值为1。
将①中结果{2;3;1;5;4;6;7}与之比对,相等返回TRUE,不相等返回FALSE,即:
{FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE};
再使用0除以这组数,除法运算中,TRUE=1,FALSE=0,即得出:
0/{0;0;1;0;0;0;0},
由于0不能作为除数,进一步得到:
{#DIV/0!;#DIV/0!;0;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!}。
注意:该数组仅在当前所要查找的排名值1所对应位置处为0,其余均为错误值。
这是我们构建0/(①=ROW()-1)这一查询范围的核心目的,只有这样,LOOKUP函数才能正确查找。
③公式说明:
LOOKUP(1②,$A$2:$A$8)
LOOKUP通过将查询范围②与查询值1匹配,找到②中小于且最接近于查询值的数值位置,返回结果范围A2:A8对应位置的值,且过程中自动忽略②中的错误值。
由于②中仅有第三个值为0,其余均为错误值#DIV/0!,所以LOOKUP返回A2:A8中的第三个值,即A4单元格「陶海波」。
LOOKUP+RANK法中的核心是查询范围(公式片段②)的构建,使用RANK函数生成一组排名值,再套用LOOKUP的两分法来完成查询计算,你学会了吗?
INDEX+LARGE法
使用LOOKUP+RANK法进行自动排序,有一个明显的漏洞,那就是当出现相同排名时,公式结果就会出错。
这时候,我们可以用INDEX+LARGE函数来构建另一个数组公式。
PS.数组公式输入后,需按【Ctrl+Shift+Enter】才能正确计算。
{=INDEX($A$2:$A$8,MOD(LARGE($B$2:$B$8+ROW($1:$7)%,ROW()-1),1)*100)}
①公式说明:
$B$2:$B$8+ROW($1:$7)%
ROW($1:$7)返回一组1到7的有序数组,表示每一个数值的序号,该序号值最终还将作为INDEX的索引值。
「%」是"/100"的简写,于是$B$2:$B$8+ROW($1:$7)%相当于给B2:B8的每个数以此加上尾数0.01-0.07,得到:
{64.01;74.02;74.03;37.04;46.05;19.06;2.07}
由于案例中的数值都为整数,加上不同的尾数可以确保这些数值彼此不等。
PS.事实上,只需保证所加的尾数始终小于需要排序数值的有效数位,就能避免数值相等导致公式错误。
②公式说明:
LARGE①,ROW()-1)
LARGE函数用于返回数据组从大到小排列中指定位次的数值。
E2单元格公式中的ROW()-1返回当前行号减1,即为1,表示通过LARGE函数返回{64.01;74.02;74.03;37.04;46.05;19.06;2.07}中第1大的数值74.03,E3:E8单元格则以此类推,取第2到第7大的数值。
③公式说明:
INDEX($A$2:$A$8,MOD②1)*100
MOD函数为取余函数,MOD(②,1),即对②除以1取余数,得到我们在片段①中通过ROW($1:$7)%给B2:B8加上的尾数,将这个尾数乘以100,可还原为ROW($1:$7)本身,它表示B2:B8中每一个数值的序号。
E2单元格中,对74.03除以1取余数为0.03,乘以100,得到3,它表示最大的数是B2:B8的第3个数。
此时再用INDEX提取A2:A8的第3个数即可。
我们知道,第二个数B3和第三个数B4都是74,都最大。
但由于ROW($1:$7)%为二者所加上的尾数分别为0.02和0.03,于是B3作为最大的数排列在E2单元格,B4被处理为第2大的数排列在E3单元格中。
由此解决了数值相等无法依次排序的问题,这就是INDEX+LARGE法的秘诀,你学会了吗?
以上,就是小花分享的两个低版本Excel专用排序公式,要点如下:
❶通过RANK函数的数组运算生成一组排名值,再构建LOOKUP的1/0查询结构,实现对数据的自动排序;
❷通过ROW%来为原数据添加表示其序数的尾数,使数据彼此不等,然后用LARGE取指定位次的数值,再用MOD函数取余*100还原序数值,最后用INDEX实现排序。
|