2012年8月16日 星期四

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

上一次利用 Row()、Large()、Match()、Index() 這四個函數,我們讓 Excel 也能夠自動排序。

祇不過 Match() 與 Index() 並不是那麼直覺,如果比較少用的話,臨時使用時會想不起來要怎麼寫公式。

後來我想到,利用 Right() & Indirect() 函數來代替 Match() 和 Index() 應該會更容易一些。

原理說明

上回我們把 E 欄原始資料複製到 G 欄時,為了讓日後得以反查回去,所以在複製時特地加上了 ROW()*0.000001 做為特徵碼,排序後再用 Match() 反查資料。

因為後面加的特徵碼不一樣 (每個學生擁有獨立的一列,既然所在的列不相同,那麼 ROW()*0.000001 的結果一定不一樣),就算是遇到了總分相同的情況,我們還是能夠正確反查。

如果偷懶沒加特徵碼,遇到同分狀況,我們的反查就永遠祇能查到第一筆資料。比方說小黑 & 小昕都是 159 分,不加特徵碼的情況下,我們會看到 Excel 列了兩筆小黑的資料,但是沒有小昕的資料,祇有加了特徵碼才能免除這個錯誤。

我們利用 Match() 反查時,因為加了特徵碼後的每筆資料都是獨一無二的,可以查到排序後的資料在 G 欄的位置,然後再從這個位置找出最原始資料。

但是何必這麼麻煩呢?

既然我們加的特徵碼是原始資料所在的列號,那麼我祇需看一下後面的特徵碼,就知道這筆資料原本是在哪裡了啊。比方說我看到其中一筆資料是 166.000009,最後面加的數字是 .000009,表示它來自於第 9 列,馬上可以找出原始資料。就是這麼簡單,不必再用複雜的 Match() 來比對了。

Excel 自動排序更新版

雖然新的方法比較簡便,但是一開始的資料複製 & Large() 排序還是得做,所以前兩個步驟與上一回的教學是相同的。

複製資料並用 Large 函數排序
圖、複製資料並用 Large 函數排序

排序後,我們要看看這些資料原本是來自於哪裡。這時我們可以用 Right( ) 函數抓出最後的幾位數,就可以知道資料來源。

Right( ) 函數:

功能:找出字串最右邊數回來的 N 個字元
用法:Right(字串, N)

因為我們加的特徵碼是整個數值的最右邊 6 碼,所以我下 =Right(H2, 6) 這樣的指令就能把 H2 這一格資料的最後 6 位數截取下來。

利用 Right( ) 函數截取最後 N 個字元
圖、利用 Right( ) 函數截取最後 N 個字元

其實用 =Right(H2, 2) 截取 2 個字元就可以了,因為一個班級的學生頂多就是 30~40 位,所以祇截取最後 2 位數就好了。截取 6 位數完全祇是做一下示範而已。

截取下來的 6 位數字是 000007,表示 H2 這個總分排名第一的資料是來自於第 7 列。

以 Right( ) 截取的 6 個字元
圖、以 Right( ) 截取的 6 個字元

OK,我已經知道第一名的學生是第 7 列的學生,所以我看 B 這一欄的第 7 列,就可以知道考第一名的是小健。所以我輸入 ="B"&I2 ,因為 I2 現在是 000007 ,我這樣寫希望是希望 Excel 幫我顯示 B7 的資料,就像我們一開始在 G2 這一格寫 =E2 一樣。

但是我錯了,我輸入 ="B"&I2 之後顯示的是 B000007 ,變成了一串文字,這跟我要的答案完全不一樣啊!

& 符號:

功能:將 & 符號前後的字串連接起來
用法:"字串"&"字串" ,字串前後要加上引號
範例:="32"&"45"&"13" 會串成 324513 這樣的字串;="你"&"好" 會變成你好

後來找了一下,Indirect( ) 函數可以達到我想要的結果。

Indirect( ) 函數:

功能:顯示文字串所對應的儲存格
用法:Indirect(字串)

既然 Indirect( ) 可以把一串文字解讀要對應的儲存格,那麼我就把剛剛的 "B"&I2 寫在 Indirect 的括號裡面,讓 Indirect 顯示 & 符號組合出來的字串所對應的儲存格資料吧。

利用 Indirect( ) 函數顯示其他儲存格的資料
圖、利用 Indirect( ) 函數顯示其他儲存格的資料

好,正確的找到總分 175 分的考生小健了!再來是要顯示小健的總分。

因為在原始資料中總分是放在 E 這一欄,所以我祇要用 "E" 串上 Right( ) 找到的列號,再用 Indirect( ) 就可以得到總分資料。

再利用 Indirect( ) 函數查總分
圖、再利用 Indirect( ) 函數查總分

結果相當讓人開心,我們用簡單的 Right( ) 與 Indirect( ) 同樣能找到排名第一的學生以及他的考試分數。

利用 Right( ) 與 Indirect( ) 查得原始資料
圖、利用 Right( ) 與 Indirect( ) 查得原始資料

既然公式正確的幫我們找到排名第一的學生了,那麼就把公式複製到其他格,依序排列第二名、第三名的學生吧。

將公式複製至其他儲存格得到完整的排序結果
圖、將公式複製至其他儲存格得到完整的排序結果

這次我們利用 Right( )、Indirect( ) 得到了與上回使用 Match( )、Index( ) 時相同的結果。換用 Right( ) 與 Indirect( ) 讓我們的做法變的比較直覺,很容易理解。

更重要的是,我們要輸入的公式變得簡短很多,不用擔心儲存格要不要加 $ 的問題,所以用新的公式出錯的機會應該會大大降低。希望這個新的方法能讓你更快速的解決問題。

Technorati : , , , ,