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

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)

{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.注意单元格绝对引用和相对引用。

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