在 Excel 中自動排序資料 (上)

我們利用 Excel 的排序功能時，每一次想要看到排序資料，就得手動點選排序功能 Excel 才會幫忙排序。

如果希望 Excel 自動排序，每打一筆資料就顯示即時的排序結果，目前 Excel 並沒有提供直接的工具讓我們使用。要嘛就是得寫 VBA 程式，要嘛就是得轉幾個彎來達成這個目的。

以下的方法是我在 How to Sort with Formula 這個網頁所學到的。不過原作者寫的比較簡略，可能不是那麼容易就看懂，所以我試著從他的基礎出發再寫得更詳細一些，希望能幫助理解。

Excel 自動排序的原理解說

假設現在有三個數字 77、32、58，請問一下，這三個數字當中最大的是哪個數字？77？很好。那第二大的數字呢？58？不錯。第三大是哪個呢？32？很好。

現在我們把最大的數字、第二的數字以及第三大的數字連著一起看，77、58、32，ㄟ，這樣是不是等於已經將前面這三個數字由大至小排序排好了？

在 Excel 中，我們可以用 Large(N) 這個函數幫我們找出排行第 N 大的數字；所以上述的例子，我們可以用 Large(1) 找出最大的數字；Large(2) 找出排行第二的數字；Large(3) 找到第三大的數字，間接幫我們把數字由大排到小。

有 Large 就有 Small，Small(1) 會找出最小的數字 32；Small(2) 找出排行第二小的數字 58；Small(3) 找到第三小的數字 77，把這三個數字連著一起看，32、58、77，這就完成從小到大的排序。

因此利用 Large & Small 這兩個會自動更新的函數我們可以做出自動排序功能，解決 Excel 沒有提供自動排序功能的困擾。

加工原始資料

在開工之前我們得要做一些設定。為了不要動到原始資料，我們將資料移往其他的格子再繼續處理。我在 G2 這一格輸入 =E2，之後祇針對 G 這一欄做處理，這樣就不會改動到原始的資料了。

不過我們把資料移到其他地方去排序，順序亂了之後想要與原始資料對照的話可能會有點麻煩。為了方便日後與原始資料相對照，我們要在原始資料的最後方加上一個值，讓所有的資料都是唯一的，日後要尋找原始資料比較簡單。

我們可以在原始資料的最後方加上 ROW( )*0.000001 ，這樣加的值很小，不會影響排序的結果，但是加上它之後，日後要找原始資料會容易的多。

ROW( ) 函數說明

功能：傳回括號中格子位於第幾列。
用法：ROW(儲存格)
例子：ROW(E3) 會顯示 3；ROW(D44) 會顯示 44。括號中不填任何資料時，顯示目前位於第幾列。

小幫手：搞不清楚什麼是行？什麼是列嗎？喵凡告訴你！

因為要加上 ROW() * 0.000001 這個特徵碼，所以 G2 這一格要輸入的是 =E2 + ROW() * 0.000001 。

圖、利用 ROW( ) 函數將原始資料加上特徵碼

按下 Enter 鍵之後，G2 這一格顯示的數值……ㄟ，與原本 E2 沒有兩樣啊？！

不用擔心，那是因為我們加的數字實在是太小了，所以沒有顯示出來。

圖、新資料看起來與原始資料完全相同

雖然我們加上的微小值沒有顯示出來，不過完全不影響 Excel 的計算，所以不用管它。

如果你堅持要看到完整的資料才能安心，那麼也很簡單，祇要設定一下儲存格格式就可以讓它顯示出來了。

首先，將 G 這一整欄都標記起來，然後按右鍵，選擇『儲存格格式』，就可以設定這一整欄的顯示方式。

圖、設定儲存格格式

在儲存格格式的設定畫面中，設定這一整欄的資料都是數值，而且要顯示出 6 位小數。

圖、設定儲存格格式為 6 位小數

按下確定鍵之後，畫面上顯示的就是完整具有 6 位小數的數值了。

圖、儲存格格式已設定顯示 6 位小數

把 G2 的公式複製好之後我們要開始來排序了，先假設我們要從大排到小好了，所以這就要用到 Large( ) 功能。

Large( ) 函數：

功能：找出在某一範圍中第 N 大的數值
用法：Large(範圍, N)

假設我們要排序 G2:G9 這個範圍的資料，所以就把這個範圍填入 Large 函數中。但是如果祇寫 G2:G9，那麼公式複製到下一格時範圍會自動變成 G3:G10，這樣就錯了。為了避免 Excel 自動改變範圍，記得要加 $ 字號，變成 $G$2:$G$9，這樣複製公式時資料就不會錯了 (不懂為什麼要加 $ 字號？請看 Excel 定位：$ 字號的功用)。

解決了範圍，我們還要提供 N 值給 Large 函數。你要手動輸入 1、2、3、4 也是可以，不過我們還可以應用一下 ROW()。

我在 H2 這一格輸入的完整公式是： =Large($G$2:$G$9, ROW()-ROW($H$1))

H1 是標題，輸入 ROW(H1) 會得到 1；而我現在位在 H2 這一格，輸入 ROW()-ROW(H1) 會變成 2-1 = 1；在 H3 這一格輸入 ROW()-ROW(H1) 變成 3-1 = 2。

利用 $ 字號固定住 $H$1，再把 ROW()-ROW($H$1) 複製到底下的格子就可以依序查詢第 1 大、第 2 大……的資料了。

圖、利用 Large & Row 函數找排名第 N 大的數值

輸完公式，按下 Enter 就立即找到最大值 175。

圖、立即找到第 1 大的數值

把公式往下複製，馬上就依照數值大小依序排好。

圖、利用 Large 函數排序數字

現在已經利用 Large 函數將 G 欄數字排好次序了，但是有個小問題，就是我怎麼知道這些數字原本在哪邊呢？比方說，現在我知道總分 175 是最高分，但是這個 175 原本是在 G 欄的哪個位置啊？

當然利用工人智慧，以肉眼一一比對還是找的到，但這太辛苦了，有沒有快一點的方法？

有，我們可以利用 Match 函數找出它原本的位置。

Match( ) 函數：

功能：找出在特定範圍中是否有我們想要的值
用法：Match(想要查詢的值, 查詢範圍, 比較的方式)

比較方式：
1：找比較小的值；
0：找完全相同的值；
-1：找比較大的值

我想找的是 H2 這一格現在的 175 到底原本排在 G2:G9 的哪邊，利用 =Match(H2, $G$2:$G$9,0) 就可以得到 6 這個答案。表示說 175 這個數字排列在 G2:G9 這個範圍的第 6 個位置。

G 欄的資料是從 E 這一欄一對一的複製過來的，所以在 G2:G9 這個範圍的第 6 個位置，那麼也會在 E 這一欄的第 6 個位置。知道這一點，我們就可以更進一步的利用 Index 函數查詢原始資料。

Index( ) 函數：

功能：找出在某一範圍中第 N 列，第 M 欄的數值
用法：Index(範圍, N 列, M 欄)

剛剛找到 175 是第 6 列的資料，所以我輸入 =Index($B$2:$E$9, 6, 1) 就會去找 B2:E9 這個範圍的第 6 列，第 1 欄的資料，得到『小健』。好，把 Match() 及 Index() 組合起來，輸入：

=Index($B$2:$E$9, Match(H2, $G$2:$G$9,0), 1) 可以得到最高分學生的名字

圖、利用 Index() 及 Match() 查詢原始資料

按下 Enter 鍵，果然把第一名的榮耀歸給了小健。

圖、利用 Index() 及 Match() 回推原始資料姓名

好，現在公式都不變，祇把最後的 1 改成 4，代表我要查詢的是 $B$2:$E$9 這個範圍的第 4 欄，也就是總分的資料

圖、利用 Index() 及 Match() 查詢原始資料的特定欄位

按下 Enter 鍵，我們把第 1 名的小健的總分給查出來了。

圖、利用 Index() 及 Match() 查詢原始資料的特定欄位

接著把公式複製下來就完成了。

圖、複製公式至其他儲存格

隱藏不需要的欄位

截至目前為止，我們已經可以讓 Excel 依據原始資料自動排序了，不過中間有幾個輔助欄，讓我們要看排序結果時不太方便。其實這是小問題，我們把這些輔助欄隱藏掉就是了。

假設你想要把 G & H 這兩欄隱藏起來，利用滑鼠在 G 上面按一下，滑鼠不放，拉動至 H 欄再放開滑鼠，這樣就把這兩欄都選取起來了。接著按滑鼠右鍵，選擇隱藏，這兩欄資料就暫時看不到了。

圖、標記欄位後選擇隱藏

執行後 G & H 兩欄就不見了。把這兩欄隱藏後，我們可以一眼就看到原始資料以及排序後的資料，方便多了。

圖、輔助欄位被隱藏看不見了

終於全部完工。從此之後，我們祇要更動了左邊的原始資料，右邊就會立即排序，可以看到即時的排序結果。

比方說小王的國文成績輸錯了，應該 99 分才對；小芬的國文、英文分別是 74、86，我們之前也都輸入錯誤。把資料更正過來後，右邊的排序資料馬上幫小王 & 小芬的排名往上提，太棒了！

圖、改變原始資料，Excel 立即更新排序

利用 Row()、Large()、Match()、Index() 這四個函數，我們達成了 Excel 沒有提供的自動排序功能，讓我們可以知道一群資料間的『即時戰況』，解決了我們一些困擾。

『不不不，一點也沒有解決！這四個函數好複雜，我記不起來！每次都還要查，真不方便。』

ㄟ，如果你有這樣的困擾的話，那，我還有個稍微簡便一點點的方法啦！我下次再教你，期待一下吧！:)

參考資料：

• How to Sort with Formula
• 行與列
• Excel 的定位方式：$ 字號有什麼用？

Technorati : Office, 資訊化教育, 資訊教育, 資訊融入教學, 資訊課程