Excel-使用公式執行多欄位排序(SUMPRODUCT)
教育
03-28
在 Excel 中如何設計公式來執行多欄位的排序?
以下圖為例,欄位:國文、英文、數學、社會、自然在總分相同時的比序順序為:國文>數學>英文>自然>社會,要設計公式以得到這樣的結果。(本例假設有 5 個欄位的數據要比序,且每一個數據為 0~100 之間的數值。)
下圖是利用 RANK 函數在H欄中產生的排序結果:
下圖是以排序欄位由小至大的排序結果。(先觀察其中排序相同者,目前尚未依指定比序來排序。)
本例需要一個輔助欄位:
單元格H3:=G3+SUMPRODUCT(B3:F3*0.01^$B$1:$F$1)
複製單元格H3,貼至單元格H3:H27。
排序結果如下圖,排序已依指定的比序:國文>數學>英文>自然>社會,產生不同的排序結果。
公式:=G3+SUMPRODUCT(B3:F3*0.01^$B$1:$F$1)
其中 0.01^$B$1:$F$1 在 SUMPRODUCT會產生:
國文18, 英文19, 數學18, 社會19, 自然19
=93+0.01*18+0.000001*19+0.0001*18+0.0000000001*19+0.00000001*19
=93.1818191919(小)
國文19, 英文18, 數學19, 社會18, 自然19
=93+0.01*19+0.000001*18+0.0001*19+0.0000000001*18+0.00000001*19
=93.1919181918(大)
輔助欄位和原始數據的對應關係如下圖:
本例每筆數據均為0~100之間,所以設計方式如此。如果你的數據是更大的數值,或是比較欄位為更多筆時,該如何處理?先自己想一想。