學不完．教不停．用不盡

學校取得某次考試的成績，如果想要自行計算各科成績的五標(底標, 後標, 均標, 前標, 頂標)，並標示學生的程度，該如何處理？以下以 320 個學生，五個科目的成績為例。

首先，選取儲存格C1:H321，按一下 Ctrl+Shift+F3 鍵，定義名稱：國文、英文、數學、社會、自然、總分。參考下圖，來計算五標及標示學生成績的程度。

(一) 若考試成績的五標如下定義：

• 底標：該科成績位於第12百分位數之考生分數
• 後標：該科成績位於第25百分位數之考生分數
• 均標：該科成績位於第50百分位數之考生分數
• 前標：該科成績位於第75百分位數之考生分數
• 頂標：該科成績位於第88百分位數之考生分數

【輸入公式】

(1) 底標(第12百分位)　

儲存格C323：=SMALL(INDIRECT(C1),320*12%)

儲存格C323：=LARGE(INDIRECT(C1),320*(1-12%))

INDIRECT(C1)：將儲存格C1的內容「國文」轉成已定義的名稱(位址)。

320*12%：320 個學生的12%人數。

(2) 後標(第25百分位)　

儲存格C324：=SMALL(INDIRECT(C1),320*25%)

儲存格C324：=LARGE(INDIRECT(C1),320*(1-25%))

(3) 均標(第50百分位)　

儲存格C325：=SMALL(INDIRECT(C1),320*50%)

儲存格C325：=LARGE(INDIRECT(C1),320*(1-50%))

(4) 前標(第75百分位)　

儲存格C326：=SMALL(INDIRECT(C1),320*75%)

儲存格C326：=LARGE(INDIRECT(C1),320*(1-75%))

(5) 頂標(第88百分位)　

儲存格C327：=SMALL(INDIRECT(C1),320*88%)

儲存格C327：=LARGE(INDIRECT(C1),320*(1-88%))

複製儲存格C323:C327，貼至儲存格C323:H727。

接著，在列323建立一個輔助列資料。

儲存格I2：=VLOOKUP(H2,CHOOSE({1,2},$H$322:$H$327,$A$322:$A$327),2,TRUE)

使用查表方式找出學生總分對照的五標程度。(請參閱：利用CHOOSE函數來輔助查表)

(二) 若考試成績的五標如下定義：

• 底標：該學科後25%考生成績的平均分數
• 低標：該學科後50%考生成績的平均分數
• 均標：該學科全體考生成績的平均分數
• 高標：該學科前50%考生成績的平均分數
• 頂標：該學科前25%考生成績的平均分數

【輸入公式】

(1) 底標(後25%平均)　

儲存格C323：{=AVERAGE(LARGE(INDIRECT(C1),ROW(241:320)))}

儲存格C323：{=AVERAGE(SMALL(INDIRECT(C1),ROW(1:80)))}

這是陣列公式，輸入完成要按 Ctrl+Shift+Enter 鍵。

ROW(241:320)：產生 241~320 的數字陣列。

(2) 低標(後50%平均)　

儲存格C324：{=AVERAGE(LARGE(INDIRECT(C1),ROW(161:320)))}

儲存格C324：{=AVERAGE(SMALL(INDIRECT(C1),ROW(1:160)))}

(3) 均標(全體平均)　

儲存格C325：{=AVERAGE(LARGE(INDIRECT(C1),ROW(1:320)))}

儲存格C325：{=AVERAGE(SMALL(INDIRECT(C1),ROW(1:320)))}

(4) 高標(前50%平均)　

儲存格C326：{=AVERAGE(LARGE(INDIRECT(C1),ROW(1:160)))}

儲存格C326：{=AVERAGE(SMALL(INDIRECT(C1),ROW(161:320)))}

(5) 頂標(前25%平均)　

儲存格C327：{=AVERAGE(LARGE(INDIRECT(C1),ROW(1:80)))}

儲存格C327：{=AVERAGE(SMALL(INDIRECT(C1),ROW(241:320)))}

複製儲存格C323:C327，貼至儲存格C323:H727。

【延伸閱讀】

* Excel-以符號標示學生平均成績是否及格

* Excel-依指定欄位自動排序成績表

* Excel-分組教學的二個成績表合併成一個成績表(VSTACK,SORT)

* Excel-列出成績表上N科不及格的清單(MMULT,INDEX)

* Excel-成績表中列出不及格科目的名稱(TEXTJOIN)

* Excel-列出成績及格者並依成績遞減排序名單

* Excel-成績依性別、組別排名

* Excel-列出指定次數最佳成績的平均

* Excel-會考成績處理(FILTER,SUMPRODUCT)

* Excel-設定格式化條件來顯示學期成績被調整過

* 如何將所有考生的個別成績資料寄到考生個人的郵件地址

* Excel-如何擇優或勾選或劃記不列入以計算平均

* Excel-如何計算加權分數和加權平均分數

* Excel-計算A++,A+,A,B++,B+,B,C的個數(SUMPRODUCT,SUBSTITUE)

* Excel-消除極端值再計算平均(TRIMMEAN) 

​​

【延伸學習】

你也可以使用 PERCENTILE 函數來找出第幾百分位上的分數：

(1) 底標(第12百分位)　

儲存格C323：=PERCENTILE(INDIRECT(C1),12%)

(2) 後標(第25百分位)　

儲存格C324：=PERCENTILE(INDIRECT(C1),25%)

(3) 均標(第50百分位)　

儲存格C325：=PERCENTILE(INDIRECT(C1),50%)

(4) 前標(第75百分位)　

儲存格C326：=PERCENTILE(INDIRECT(C1),75%)

(5) 頂標(第88百分位)　

儲存格C327：=PERCENTILE(INDIRECT(C1),88%)