[EXCEL] 用公式做資料排序

　
　　如果可以用公式來篩選資料，那麼可不可以用公式來排序？也是可以的。

　　要把資料排序，可以在〔資料〕〔排序〕，然後選擇你要的鍵值和排序方法。但是，一樣會有一些不方便的地方。

• 第一，如果資料有所變動，都要重新排序一次，無法自動排序。
• 第二，排序會破壞原始資料的順序。
• 第三，不能只顯示部份欄位，所有欄位都會全部顯示。

　　如果想跳脫上述的限制，我們可以自己設計公式來做排序的動作。

##CONTINUE##

　　如上圖，要從學生的成績單中自動做分數的遞增排序，可以在 E2 輸入陣列公式

=INDEX(B:B,MOD(SMALL($C$2:$C$6*100+ROW($C$2:$C$6),ROW(A1)),100))

　　公式說明：

• $C$2:$C$6*100+ROW($C$2:$C$6): 將分數和所在列數編碼成一個數字，並形成一個陣列。此數字的百位數以上就是分數，百位數以下則是所在列數，將此數字陣列排序後，可以維持分數的正確大小順序，而且可以推算出所在列數。
• SMALL(數字陣列,ROW(...)): 依序從數字陣列中傳回第一小，第二小...的數字。
• MOD(SMALL(...),100): 從數字中回推出所在列數。
• INDEX(顯示資料欄位，SMALL(...)): 從顯示資料欄位中取出所在列數所對應的值。

　　在 F2 輸入陣列公式

=INDEX(C:C,MOD(SMALL($C$2:$C$6*100+ROW($C$2:$C$6),ROW(A1)),100))

　　再直接往下複製即可。這是一個陣列公式，記得用 CTRL+SHIFT+ENTER 來完成輸入。

　　這樣，我們可以任意在 A:C 欄變動資料，E:F 欄會馬上自動顯示出最新的排序結果，而且可以任意指定要顯示那些欄位。

　　如果想遞減排序，只要把 SMALL() 改成 LARGE() 就可以了。如 H2 輸入公式

=INDEX(B:B,MOD(LARGE($C$2:$C$6*100+ROW($C$2:$C$6),ROW(A1)),100))


2008/10/06 補充：

　　原文中的公式，如果分數有小數點，或是資料超過 100 列，就無法正常運作。故修正公式如下：

　　=INDEX(C:C,MOD(SMALL($C$2:$C$6*(10^(X+Y))+ROW($C$2:$C$6),ROW(A1)),10^X))

　　其中，資料最大列數必須小於 10^X；Y 則是分數的小數點位數。

　　它的原理和原文中的說明是一樣的，只是把它具體化為公式，比較容易運用。如原文中的例子，資料列數 =6 ＜100 (=10^2)，分數都是整數 (小數 0 位)，則 X+Y=2+0=2。

　　如果資料列數 =101 ＜1000 (=10^3)，分數有兩位小數，則 X=3，Y=2，公式就要變成

　　=INDEX(C:C,MOD(SMALL($C$2:$C$6*(10^(3+2))+ROW($C$2:$C$6),ROW(A1)),10^3))

　　或是直接把 10 的次方算出來，變成

　　=INDEX(C:C,MOD(SMALL($C$2:$C$6*100000)+ROW($C$2:$C$6),ROW(A1)),1000))