Excel表格自动排序的两种方法

原标题:《Excel表格也能自动排序。这两个方法让你竖起大拇指！》

你知道吗你知道吗

这是一个专门用于排序的函数。

但问题是很多小伙伴用的Excel不是365版本，而是19版本甚至更早。

没有神功加持，能否实现数据自动排序。

今天小华就给大家分享两个Excel低版本的特殊排序公式。

查找+排名方法

查找函数的二分法可以解决几乎所有与查询相关的问题，自动排序可以认为是按排序值查询，自然不是问题！

=LOOKUP=ROW—1)，$A:$A)

①配方说明:

等级

很明显，这是一个数组公式因为LOOKUP有自己的数组操作属性，所以可以让排名函数rank返回一组排名值，而不需要按执行操作

排名函数的排名值和排名范围参数是B2:B8通过数组运算，返回表示B2:B8中每个值对应的排名大小的序数值组23，1，5，4，6，7

②配方说明:

0/—1)

ROW—1表示当前行号—1，从E2到E8的顺序是1—7，即E2寻找的排序值是1。

会导致①中的2，3，1，5，4，6，7与它相比，等于返回真，不等于返回假，即:

假的，假的，真实，假的，假的，假的，假的，

然后用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/—1)的核心目的，只有这样查找函数才能正确找到。

③配方说明:

检查

LOOKUP将查询范围②与查询值1进行匹配，找到②中小于查询值且最接近查询值的数值位置，返回结果范围A2:A8中对应位置的值，过程中自动忽略②中的错误值。

因为②中只有第三个值是0，其他都是错误值#DIV/0！于是LOOKUP返回A2:A8中的第三个值，也就是A4单元格陶海波。

LOOKUP+RANK方法的核心是查询范围的构建使用RANK函数生成一组排名值，然后应用LOOKUP二分法完成查询计算你学会了吗

索引+大方法

使用LOOKUP+RANK的方法进行自动排序有一个明显的漏洞，就是当出现相同的排名时，公式结果会是错误的。

这时候可以用INDEX+LARGE函数再构造一个数组公式。

PS数组公式输入后，需要按才能正确计算

=INDEX%，ROW—1)，1)*100)

①配方说明:

$ B $ 2:$ B $ 8+行%

ROW返回一个从1到7的有序数组，表示每个值的序号，最终将作为INDEX的索引值。

%是/100的简称，所以$B:$B+ROW%相当于把0.01—0.07的尾数加到B2:B8的每个数上，我们得到:

64.01，74.02，74.03，37.04，46.05，19.06，2.07

因为案例中的值都是整数，所以添加不同的尾数可以确保这些值彼此不相等。

PS事实上，只要加入的尾数始终小于待排序值的有效位数，就可以避免因等值而导致的公式错误

②配方说明:

大型①，第1排)

LARGE函数用于返回数据组从大到小排列中指定秩的数值。

E2单元格公式中的ROW —1返回当前行号减1，为1，表示LARGE函数返回64.01，74.02，74.03，37.04，46.05，19.06，2.07中的第一个最大值是74.03，E3:E8单元格类推，取第二到第七个最大值。

③配方说明:

指数*100

MOD函数是余数函数，MOD，就是把②除以1，得到我们在段①到ROW (:)%中加到B2:B8上的尾数，把这个尾数乘以100还原到ROW (:)本身，它代表了B2:B8中每个值的序号。

在E2单元格中，用74.03除以1，取余数为0.03，再乘以100得3，也就是说最大的数是B2的第三个数:B8。

这时可以用INDEX来提取A2的第三个数:A8。

我们知道，第二个数B3和第三个数B4都是74，是最大的。

但是，由于第%行添加的尾数分别为0.02和0.03，因此B3在E2单元格中被排列为最大的数字，而B4在E3单元格中被视为第二大的数字。

这样就解决了值相等，不能按顺序排序的问题这就是INDEX+LARGE方法的秘密你学会了吗

以上是小花在Excel下版本中分享的两个特殊排序公式。要点如下:

通过rank函数的数组运算生成一组排序值，然后构造LOOKUP的1/0查询结构，实现数据的自动排序。

ROW%用于在原始数据上加一个代表其序数的尾数，使数据互不相等，然后用LARGE取指定秩的数值，再用MOD函数取余数* 100恢复序数，最后用INDEX实现排序。

以上公式虽然略显复杂，但经过小花的详细分析，相信朋友们一定能整理出来，收入囊中。