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

關於作者:

本文由微軟最有價值專 MVP 楊煥謀 提供。微軟十分感謝 MVP 主動地將他們的經驗與上百萬名其他技術使用者交流。MVP 楊煥謀 同時也將此篇文章放在他的部落格內,歡迎您按 此處  瀏覽 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.如果要拿這個這個統計公式來評估學生的學習成效,只適合母體數量夠大,且同分的人數不多的狀況下才適用,否則會產生學生分數只差一點而所佔全體人數百分比的差距很大!

請讓我們知道

還滿意我們寫的內容嗎?無論您是遇到了以下任何一種情況:
「太好了!問題解決了。」
「問題沒有解決,我有更好的建議。」
都歡迎您利用本篇文章底部的意見調查表,寫下您寶貴的意見。也期待您的鼓勵,讓我們為您創造更多實用的技術文章。

Need more help?

Expand your skills
Explore Training
Get new features first
Join Microsoft Insiders

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×