.hd-box .hd-fr

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

2023-01-19 21:24秋叶Excel(小花)10评

原文标题:《Excel 表格还能自动排序?这 2 种方法让你竖起大拇指!》

你知道吗?365 版 Excel 新增了一个神级函数,SORT!

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

然而问题是,很多小伙伴所使用的的 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 (ID:excel100),作者:小花

广告声明:文内含有的对外跳转链接(包括不限于超链接、二维码、口令等形式),用于传递更多信息,节省甄选时间,结果仅供参考,IT之家所有文章均包含本声明。

下载IT之家APP,分享赚金币换豪礼
相关文章
大家都在买广告
热门评论
查看更多评论