如何利用Excel 計算個別學生成績在全體學生的百分比,並換算 ...
文章推薦指數: 80 %
1.在【總分】欄位右邊2 欄(F 與G)加入【百分等第】與【級別】兩個欄位標題。
2.先點選F2 此儲存格,再點選【公式】功能區、【其他函數】\【統計】\ ...
跳到主要內容
產品
Microsoft365
Office
Outlook
MicrosoftTeams
OneDrive
OneNote
Windows
MicrosoftEdge
更多...
裝置
Surface
電腦配件
行動裝置
Xbox
電腦遊戲
HoloLens
硬體保固
新增功能
帳戶與計費
帳戶
MicrosoftStore與計費
範本
更多支援
社群論壇
Microsoft365系統管理員
小型企業系統管理員
開發人員
教育
回報支援詐騙
Excel2010More...Less
關於作者:
本文由微軟最有價值專MVP楊煥謀提供。
微軟十分感謝MVP主動地將他們的經驗與上百萬名其他技術使用者交流。
MVP 楊煥謀同時也將此篇文章放在他的部落格內,歡迎您按 此處 瀏覽MVP楊煥謀更多的技術文章、經驗分享與觀點。
問題的來龍去脈
最近工作伙伴提了一個Excel問題:如何利用Excel將學生的絕對成績換算成在同一年級中全體學生的相對百分比?因為教務處為了激勵學生在課業的學習,希望將學生的絕對成績換算為相對等級成績(全年級前百分之15)給予獎勵,讓學生了解自己在同一年級中的學習狀況,不以絕對成績高低作為獎勵,是不希望變成惡性競爭,讓學生斤斤計較於1分兩分考試成績,而又能達到鼓勵學生學業的目的,尤其像本校學生人數夠多,樣本母體夠大,這種相對成績比較具有參考性。
問題的發生原因
雖然這個問題看似簡單,但是在Excel2003與2007中仔細去挖它的各種功能,只有"分析工具箱"增益集有類似的功能,在Excel2003與2007中並沒有提供類似的函數,使用者必須要用vba自己寫函數,而在Excel2010中新提供兩個函數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.如果要拿這個這個統計公式來評估學生的學習成效,只適合母體數量夠大,且同分的人數不多的狀況下才適用,否則會產生學生分數只差一點而所佔全體人數百分比的差距很大!
請讓我們知道
還滿意我們寫的內容嗎?無論您是遇到了以下任何一種情況:「太好了!問題解決了。
」「問題沒有解決,我有更好的建議。
」都歡迎您利用本篇文章底部的意見調查表,寫下您寶貴的意見。
也期待您的鼓勵,讓我們為您創造更多實用的技術文章。
SUBSCRIBERSSFEEDS
Needmorehelp?
Expandyourskills
ExploreTraining
Getnewfeaturesfirst
JoinMicrosoftInsiders
Wasthisinformationhelpful?
Yes
No
Thankyou!Anymorefeedback?(Themoreyoutellusthemorewecanhelp.)
Canyouhelpusimprove?(Themoreyoutellusthemorewecanhelp.)
Howsatisfiedareyouwiththetranslationquality?
Whataffectedyourexperience?
Resolvedmyissue
Clearinstructions
Easytofollow
Nojargon
Pictureshelped
Translationquality
Didn'tmatchmyscreen
Incorrectinstructions
Tootechnical
Notenoughinformation
Notenoughpictures
Translationquality
Anyadditionalfeedback?(Optional)
Submitfeedback
Thankyouforyourfeedback!
×
延伸文章資訊
- 1等第制平常成績處理— Excel 相關小技巧 - 教學發展中心
將等第評分轉換為數值,等第績分(GPA) 、百分制分數或其他,以便進行計算。以下示範如何轉換為等第績分: 1. 將先前的「成績對照表」定義一名稱:
- 2excel等第換算分數相關資訊 - 哇哇3C日誌
excel等第換算分數,Excel LOOKUP 函數教學:查詢表格,找出對應資料- GT Wang,2018年1月25日— 這裡介紹如何使用Excel 的LOOKUP 查找函數查詢對應表格,將...
- 3Excel-判定成績等第(MATCH+CHOOSE)
在Excel 中依成績判定等第的作法常見使用VLOOKUP 函數,這次要使用其他函數來練習。在下圖右為設定好的分數區間對應評等,其定義為100~85:A ...
- 4Excel 依照成績計算等第(甲乙丙丁)教學與範例 - Office 指南
若要將資料依據成績排序,可以使用Excel 的自訂排序功能。 ... 選取資料範圍。 ... 在資料上按下滑鼠右鍵,選擇「排序」中的「自訂排序」。 ... 選擇以「成績」欄位,依據「儲存 ...
- 5Excel 2010考試成績的名次和等第@ 軟體使用教學 - 隨意窩
201105162201Excel 2010考試成績的名次和等第 · 1.開啟Excel 2010的成績檔案,點選「J3」儲存格,準備排列名次。 · 2.點選顯示函數的按鈕「▽」,選擇「其它函數...