2011年11月2日 星期三

Excel-學生成績統計分析(INDIRECT+陣列+定義名稱)

有網友問到,在 Excel 中取得一個成績表(參考下圖右),想要分析這些班級高(等)於平均和低於平均的人數分別為多少,該如何處理?希望能使用定義名稱的方式來操作。

【準備工作】

先定義要用的「名稱」。選取儲存格A1:F26,按一下 Ctrl+Shift+F3 鍵,勾選「頂端列」,分別定義了:「座號、三年1班、三年2班、三年3班、三年4班、三年5班」等名稱。

【輸入公式】

儲存格I2:{=SUM(IF(INDIRECT(H2)>=AVERAGE(INDIRECT(H2)),1,0))}

這是陣列公式,輸入完成請按 Ctrl+Shfit+Enter 鍵。複製儲存格I2,貼至儲存格I2:I6。

公式中以儲存格H2的內容(例:三年1班)來計算,而該內容已經定義好名稱(例:三年1班),所以可以代表一個儲存格範圍。所以使用時,以 INDIRECT 函數,將儲存格中的文字轉成 Excel 可用的儲存格範圍。

公式的意義是找出儲存格範圍中大於或等於平均數者給予1,否則給予0。透過這個 1/0 的陣列將其加總,所得的數字即為大於或等於平均成績的個數。

其結果和自行輸入以下公式是相同結果的(和上式比較):

儲存格I2:{=SUM(IF(三年1班>=AVERAGE(三年1班),1,0))}

同理:

儲存格J2:{=SUM(IF(INDIRECT(H2)<AVERAGE(INDIRECT(H2)),1,0))}

這是陣列公式,輸入完成請按 Ctrl+Shfit+Enter 鍵。複製儲存格J2,貼至儲存格J2:J6。

 

【延伸學習】

(1) 參考另一篇文章:火星人的天空之城- Excel-統計各班某個成績範圍的人數

(2) 設定大於或等於平均者,顯示紅色字。

利用格式化條件設定,選取「使用公式來決定要格式化哪儲存格」,設定公式:

=B2>=AVERAGE(B$2:B$26)

沒有留言:

張貼留言

檢視其他文章

好康東東