2013年9月20日 星期五

Excel-計算成績的五標(底標,後標,均標,前標,頂標)

學校取得某次考試的成績,如果想要自行計算各科成績的五標(底標, 後標, 均標, 前標, 頂標),並標示學生的程度,該如何處理?以下以 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。

 

【延伸學習】

你也可以使用 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%)

沒有留言:

張貼留言

檢視其他文章

好康東東