龙空技术网

Excel表格利用函数制作排序器(可依据不同字段、升降序排序)

办公小技巧007 501

前言:

此刻看官们对“排序的关键字段有哪些”大概比较讲究,朋友们都需要剖析一些“排序的关键字段有哪些”的相关知识。那么小编同时在网上网罗了一些对于“排序的关键字段有哪些””的相关知识,希望小伙伴们能喜欢,兄弟们一起来了解一下吧!

Excel表格中的排序功能是我们经常使用的一个功能,排序的方式主要有升序排序、降序排序和自定义排序。前两种排序方式默认的排序依据都是所选排序区域的第一列数据,自定义排序用户可以自定排序的主要依据和次要依据。在我们的日常工作中可能会遇到根据不同要求来排序一份数据。如果每一次都手动的根据不同的字段值依据和排序方式重新排序,那么效率会非常低。这篇文章将为朋友们分享一个完全使用函数制作的排序器。这个排序器可以根据不同字段依据、以升序或降序对的方式对数据进行排序。

一.实例要求:

在下图中要求根据不同字段值(套餐1、套餐2...)以升序或降序的方式对这份数据排序。

二.动态效果演示:

首先给朋友们演示一下已经制作完成的动态效果图,一起来感受一下这个排序器的强大。

三.制作过程

1.为每一个数值型数据都加一个非常小的数值,以免存在重复项。

操作步骤:

Ctrl+A选择数据→到一个空单元格中粘贴→点开粘贴完数据区域下方的倒三角(粘贴选项)→选择粘贴链接(这种方式的粘贴是对原始数据单元的引用)→选择所有的数值→Ctrl+H打开替换窗口→查找=,替换为=ROW()/10000000+→确定(因为ROW()返回的是当前单元格所在的行数,所以每一行单元格中增加的非常小的数值都不同,这样就可以避免重复数据的出现)→选择粘贴完的数据→复制→粘贴为数值→将原始数据删除。

2.制作排序依据和排序方式下拉列表。

利用数据选项卡下的数据验证制作简单的下拉列表,以方便选择排序依据和排序方式。

3.在H列制作一个辅助列,用函数对排序依据字段下所有的数据按照排序方式进行排序。

(1)在H2单元格输入公式:

=IF($G$2="升序",SMALL(INDIRECT(CHAR(64+MATCH($G$1,$A$1:$E$1,0)) & "2:" & CHAR(64+MATCH($G$1,$A$1:$E$1,0)) &COUNTA($A:$A)),ROW(A1)),LARGE(INDIRECT( CHAR(64+MATCH($G$1,$A$1:$E$1,0)) & "2:" & CHAR(64+MATCH($G$1,$A$1:$E$1,0)) &COUNTA($A:$A)),ROW(A1)))

(2)公式解析:

❶MATCH($G$1,$A$1:$E$1,0)返回的是排序依据字段在表头中的位置。

❷CHAR(64+MATCH($G$1,$A$1:$E$1,0))是将(1)查找到到的位置转化成以字母ABC...的形式表述。

❸COUNTA($A:$A)返回的是A列非空单元格的个数。

❹INDIRECT(CHAR(64+MATCH($G$1,$A$1:$E$1,0)) & "2:" & CHAR(64+MATCH($G$1,$A$1:$E$1,0)) &COUNTA($A:$A))返回的是排序依据下所有的数据构成的数组。

❺用Large或Small函数根据ROW()函数返回的123...的数字序列来提取(4)数组对应的第N个最值。

❻最后用if函数判断排序的方式是升序还是降序排序。

4.根据步骤3制作的辅助列来反向查找每个数据所对应的姓名:

(1)在I2单元格输入公式:

=VLOOKUP(H2,IF({1,0},INDIRECT(CHAR(64+MATCH($G$1,$A$1:$E$1,0)) & "2:" & CHAR(64+MATCH($G$1,$A$1:$E$1,0)) &COUNTA($A:$A)),$A$2:$A$17),2,0)

(2)公式解析:

❶{1,0}是一个由数字1和0构造成的简单的数组。

❷IF({1,0},INDIRECT(CHAR(64+MATCH($G$1,$A$1:$E$1,0)) & "2:" & CHAR(64+MATCH($G$1,$A$1:$E$1,0)) &COUNTA($A:$A)),$A$2:$A$17)构造了一个排序依据字段在前、姓名在后的数组。

❸最后使用Vlookuo函数就可以查找到每一个数据所对应的姓名。

5.将步骤3与步骤4整合只需要在H2单元格输入公式:

=VLOOKUP(IF($G$2="升序",SMALL(INDIRECT(CHAR(64+MATCH($G$1,$A$1:$E$1,0)) & "2:" & CHAR(64+MATCH($G$1,$A$1:$E$1,0)) &COUNTA($A:$A)),ROW(A1)),LARGE(INDIRECT( CHAR(64+MATCH($G$1,$A$1:$E$1,0)) & "2:" & CHAR(64+MATCH($G$1,$A$1:$E$1,0)) &COUNTA($A:$A)),ROW(A1))),IF({1,0},INDIRECT(CHAR(64+MATCH($G$1,$A$1:$E$1,0)) & "2:" & CHAR(64+MATCH($G$1,$A$1:$E$1,0)) &COUNTA($A:$A)),$A$2:$A$17),2,0)

6.根据姓名查找其他所有对应的数据:

(1)在I2单元格输入公式:=VLOOKUP($H2,$A:$E,COLUMN(B:B),0),向右向下拖动填充。

(2)公式解析:COLUMN(B:B)返回的是B列所在的列数字2,向右拖动填充时会转变成234....,以确保准确的查找出其他数据。

(3)为不同列的数据分别添加一个数据条直观的显示数据。

注意事项:

1.涉及到数组公式的部分确定公式时一定要按住Ctrl+Shift+Enter三键确定。

2.注意单元格绝对引用和相对引用。

总结:实现在这个排序器公式看起来很长,但是其实并不复杂。公式中有很多重复的部分,当然为了使公式更简洁可以将重复的部分定义成名称。如果朋友们有不懂的地方欢迎给我留言或者在评论区一起探讨,觉得有用麻烦帮助点赞转发。

标签: #排序的关键字段有哪些