2012年8月14日 星期二

在 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( ) 函數將原始資料加上特徵碼
圖、利用 ROW( ) 函數將原始資料加上特徵碼

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

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

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

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

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

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

設定儲存格格式
圖、設定儲存格格式

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

設定儲存格格式為 6 位小數
圖、設定儲存格格式為 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 大的數值
圖、利用 Large & Row 函數找排名第 N 大的數值

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

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

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

利用 Large 函數排序數字
圖、利用 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() 查詢原始資料
圖、利用 Index() 及 Match() 查詢原始資料

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

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

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

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

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

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

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

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

隱藏不需要的欄位

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

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

標記欄位後選擇隱藏
圖、標記欄位後選擇隱藏

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

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

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

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

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

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

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

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

參考資料:

Technorati : , , , ,