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.注意單元格絕對引用和相對引用。

總結：實現在這個排序器公式看起來很長，但是其實並不複雜。公式中有很多重複的部分，當然為了使公式更簡潔可以將重複的部分定義成名稱。如果朋友們有不懂的地方歡迎給我留言或者在評論區一起探討，覺得有用麻煩幫助點讚轉發。