点击蓝字【秋叶 Excel】👆发送【交流】立即进【秋叶同学会】交流Excel！
本文作者：小花本文编辑：雅梨子、竺兰你知道吗？365 版 Excel 新增了一个神级函数，SORT！它是一个专门用于排序的函数。
对 SORT 函数或是它的兄弟函数 SORTBY 函数有兴趣的小伙伴，可以看下小爽老师的相关文章：《效率翻倍！用 Excel 按自定义顺序排序，想怎么排就怎么排~》！然而问题是，很多小伙伴所使用的的 Excel 并非 365 版，而是 19 版甚至更早期的版本。
没有神级函数加持，能不能实现对数据的自动排序呢？
今天小花就分享两个低版本 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 实现排序。
以上公式虽略显复杂，但经过小花详细解析，相信小伙伴们一定能理清吃透，收入囊中你还想知道哪些新函数的平替公式，不妨留言告诉我们，让我们为你安排教程，何乐不为？对了，如果你想系统性学习 Excel正好，我们秋叶家的。
《秋叶 Excel 3 天集训营》专为职场人准备，全部基于职场真实表格案例设计，还有很多超实用 Excel 技巧教学每天学习大概 30 分钟，从日常的功能出发，全程演示，一课一练，夯实进阶每一步秋叶 Excel 3 天集训营。
原价 99 元 现在 只需 1 元 每天学习 30 分钟你也有可能成为 Excel 高手！现在就扫码报名吧！👇👇👇
▲ 报名成功后将自动弹出班主任二维码，切勿提前退出若未能添加，请联系公众号客服获取
↓↓↓
遇到有价值的文章点点在看支持一下 !👇👇👇动动小手分享给朋友~👇👇👇
发表评论:
◎欢迎参与讨论，请在这里发表您的看法、交流您的观点。