在 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 函數排序

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

Right( ) 函數：

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

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

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

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

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

圖、以 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( ) 函數顯示其他儲存格的資料

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

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

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

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

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

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

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

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

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

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