2018年3月16日 星期五

Excel-根據成績計算各班最高分和最低分和各科之頂標、前標、均標、後標、底標(INDIRECT,MAX,MIN,SMALL,陣列公式)

(練習題)最近又有一項大考放榜了!學校取得考試成績後,如何利用 Excel 來稍微分析(例如:計算各班最高分和最低分和各科之頂標、前標、均標、後標、底標等),以利後續應用?
本篇簡單的做了一個範例來練習。(本例數據內容為模擬數值)
Excel-根據考試原始成績計算各班最高分和最低分和各科之頂標、前標、均標、後標、底標(INDIRECT,MAX,MIN,SMALL,陣列公式)

【公式設計與解析】
1. 定義名稱
Excel-根據考試原始成績計算各班最高分和最低分和各科之頂標、前標、均標、後標、底標(INDIRECT,MAX,MIN,SMALL,陣列公式)
假設原始資料置於儲存格A1:H420。
選取儲存格A1:H420,按 Ctrl+Shift+F3 鍵,勾選「頂端列」。定義名稱:班級、座號、國文、英文、數學、社會、自然、總級分。

2. 計算各班最高分和最低分
Excel-根據考試原始成績計算各班最高分和最低分和各科之頂標、前標、均標、後標、底標(INDIRECT,MAX,MIN,SMALL,陣列公式)
最高→儲存格K3:{=MAX(IF(班級=$J3,INDIRECT(K$1),""))}
最低→儲存格L3:{=MIN(IF(班級=$J3,INDIRECT(K$1),""))}
以上式是陣列公式,輸入完成要按Ctrl+Shift+Enter鍵,Excel 自動加上「{}」。
複製儲存格K3:L3,貼至儲存格K3:V14。
(1) INDIRECT(K$1)
公式中利用 INDIRECT 將儲存格K1內文字轉換為儲存格範圍(已定義名稱:國文)。
(2) IF(班級=$J3,INDIRECT(K$1),"")
在陣列公式中找出班級陣列和儲存格J3相同者,傳回對應的國文成績。
平均→儲存格K16:=AVERAGE(INDIRECT(K1))
(3) MAX(IF(班級=$J3,INDIRECT(K$1),""))
將第(2)式傳回的國文成績陣列,利用 MAX 函數取其最大值。(可利用 MIN 函數取其最小值)

3. 計算各科之頂標、前標、均標、後標、底標
Excel-根據考試原始成績計算各班最高分和最低分和各科之頂標、前標、均標、後標、底標(INDIRECT,MAX,MIN,SMALL,陣列公式)
頂標→儲存格K20:=SMALL(INDIRECT(K$18),COUNT(INDIRECT(K$18))*88%)
前標→儲存格K21:=SMALL(INDIRECT(K$18),COUNT(INDIRECT(K$18))*75%)
均標→儲存格K22:=SMALL(INDIRECT(K$18),COUNT(INDIRECT(K$18))*50%)
後標→儲存格K23:=SMALL(INDIRECT(K$18),COUNT(INDIRECT(K$18))*25%)
底標→儲存格K24:=SMALL(INDIRECT(K$18),COUNT(INDIRECT(K$18))*12%)
複製儲存格K20:K24,貼至儲存格M20:M24、O20:O24、Q20:Q24、S20:S24。
(1) COUNT(INDIRECT(K$18))*88%
利用 COUNT 函數計算國文成績的數量,「*88%」乃取總數的第88個。
(2) SMALL(INDIRECT(K$18),COUNT(INDIRECT(K$18))*88%)
利用 SMALL 函數取該科成績最小的第 88 個即為頂標成績。其餘各標做法依此類推。
(如果把 SMALL 函數換為 LARGE 函數,該如何處理?)

沒有留言:

張貼留言

檢視其他文章

好康東東