如何利用 Excel 計算個別學生成績在全體學生的百分比，並換算成等級？（一點通系列 - MVP 撰寫）

最近工作伙伴提了一個 Excel 問題：如何利用 Excel 將學生的絕對成績換算成在同一年級中全體學生的相對百分比？因為教務處為了激勵學生在課業的學習，希望將學生的絕對成績換算為相對等級成績(全年級前百分之 15 )給予獎勵，讓學生了解自己在同一年級中的學習狀況，不以絕對成績高低作為獎勵，是不希望變成惡性競爭，讓學生斤斤計較於 1 分兩分考試成績，而又能達到鼓勵學生學業的目的，尤其像本校學生人數夠多，樣本母體夠大，這種相對成績比較具有參考性。

雖然這個問題看似簡單，但是在 Excel2003 與 2007 中仔細去挖它的各種功能，只有"分析工具箱"增益集有類似的功能，在 Excel 2003 與 2007 中並沒有提供類似的函數，使用者必須要用 vba 自己寫函數，而在 Excel 2010 中新提供兩個函數 PERCENTRANK.INC 與 PERCENTRANK.EXC 可以達成這個目的。這兩個函數都可以把學生的個別成績轉換成佔全體成績的百分比，差別只在於 PERCENTRANK.INC 把唯一最高分當成 1 與把所有最低分當成 0，其他的為(所勝過的人數/總人數)，而 PERCENTRANK.EXC 把最高分與最低分不當成 1 與 0，把(總人數+1-(大於等於該分數的總人數))/(總人數+1) 。兩者的算法雖不同，其實當樣本數夠大差異有限，以下列假想的學生名單成績為例：





要利用 Excel2010 把學生的總分成績換算成佔全部同年級學生的百分比，再依百分比範圍分成三個等級:精熟(大於等於 85%)、基礎(小於 85% 大於 25%)、待加強(小於 25%)即要製作成如下表，多了兩欄百分等第與級別步驟:

步驟如下: 
1.在【總分】欄位右邊 2 欄(F 與 G)加入【百分等第】與【級別】兩個欄位標題。

2.先點選 F2 此儲存格，再點選【公式】功能區、【其他函數】\【統計】\【PERCENTRANK.INC】。





3.在【函數引數】表單，Array 引數代表全體學生的成績範圍，x 代表要計算的成績，Signaficance 代表計算取的小數位數(不設定預設取 3 位)，以下圖參數為例，是要計算 9 年 1 班 1 號的成績在全部 9 年級共 648 位學生中所佔的高低百分比，取到小數點第 3 位。





4.計算的結果為 0.825，代表 9 年 1 班 1 號的成績贏過 82.5%的人。





5.接下來我們要把其他學生計算出來，難道要一個個重複剛才的動作嗎？當然不是，有 648 個學生不瘋掉才怪，我們要利用 Excel 中我個人認為最方便資料輸入的功能【自動填滿】，來幫助我們快速完成這個動作，在執行【自動填滿】前首先我們要改一下我們建立的公式的一個小地方，公式如下：

=PERCENTRANK.INC(E2:E649,E2)

如果要透過【自動填滿】的功能，我們必須將 Array 這個資料範圍參數改成絕對參照，也就是將公式改成如下：

=PERCENTRANK.INC($E$2:$E$649,E2)

將 Array 的引數由 E2:E649 改成$E$2:$E$649，代表在執行【自動填滿】時不會變更此範圍，如果是 E2:E649 則沿欄位下拉自動填滿【自動填滿】，程式會自動變動參照的列位，例如變成 E3:E650，這樣就無法產生正確的計算。





6.【級別】的部分，依照前述步驟改用 IF 函數，函數的內容如下，即可完成我們的目的。

=IF(F2>=0.85,"精熟",IF(F2<0.25,"待加強","基礎"))

範例檔案

7.如果要拿這個這個統計公式來評估學生的學習成效，只適合母體數量夠大，且同分的人數不多的狀況下才適用，否則會產生學生分數只差一點而所佔全體人數百分比的差距很大!