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

 

學不完.教不停.用不盡文章列表

arrow
arrow
    全站熱搜

    vincent 發表在 痞客邦 留言(2) 人氣()