2018年3月5日 星期一

Excel-使用公式執行多欄位排序(SUMPRODUCT)

在 Excel 中如何設計公式來執行多欄位的排序?
以下圖為例,欄位:國文、英文、數學、社會、自然在總分相同時的比序順序為:國文→數學→英文→自然→社會,要設計公式以得到這樣的結果。(本例假設有 5 個欄位的資料要比序,且每一個資料為 0~100 之間的數值。)
Excel-使用公式執行多欄位排序(SUMPRODUCT)
下圖是利用 RANK 函數在H欄中產生的排序結果:
Excel-使用公式執行多欄位排序(SUMPRODUCT)
下圖是以排序欄位由小至大的排序結果。(先觀察其中排序相同者,目前尚未依指定比序來排序。)
Excel-使用公式執行多欄位排序(SUMPRODUCT)
本例需要一個輔助欄位:
儲存格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(大)
Excel-使用公式執行多欄位排序(SUMPRODUCT)
輔助欄位和原始資料的對應關係如下圖:
image
本例每筆資料均為0~100之間,所以設計方式如此。如果你的資料是更大的數值,或是比較欄位為更多筆時,該如何處理?先自己想一想。

沒有留言:

張貼留言

檢視其他文章

好康東東