2011年9月6日 星期二

Excel-計算各班的平均、最大和最小值(AVERAGEIF+陣列+INDIRECT)

在 Excel 中取得一個全年級共十個班(101~110)的學生資料(如下圖),若要做出統計分析報表含有各班的平均、最大值、最小值,該如何處理?

(1) 所有學生資料放在同一個資料表

所有學生資料放在同一個資料表,各班學生由上而下排列。

先選取全部的學生資料,按一下 Ctrl+Shift+F3 鍵,勾選「頂端列」,建立「班級、座號、姓名、身高、體重、BMI」等名稱的範圍。輸入以下公式:

● 計算身高的平均值 儲存格K2:=AVERAGEIF(班級,H3,身高)

使用 AVERAGEIF 函數,透過指定班級當為條件,即可計算出符合條件的平均值。

● 計算身高的最大值 儲存格I2:{=MAX(IF(班級=H3,身高,FALSE))}

● 計算身高的最小值 儲存格J2:{=MIN(IF(班級=H3,身高,FALSE))}

此二公式為陣列公式,輸入完成請按 Ctrl+Shift+Enter 鍵。

公式的意義為在班級陣列中找出符合儲存格H3的班級,所對應的身高值形成的陣列,再由這個陣列中找找最大值(MAX)或最小值(MIN)。

複製以上的儲存格,往下各列貼上。其餘體重和 BMI 值的最大值、最小值和平均值計算方式完全相同,請自行練習。

 

(2) 各班學生資料放在不同資料表中

如果各班學生資料放在不同資料表中,請以班級名稱做為資料表名稱,而且每個資料表的格式也設計一致。

如果資料分散在多個資料表中,可以透過 INDIRECT 指令來使用資料表中的內容:

● 計算身高的平均值 儲存格I3:=MAX(INDIRECT(H3&"!"&"D2:D36"))

公式中的INDIRECT(H3&"!"&"D2:D36"),乃將儲存格H3的內容做為工作表名稱,「!」為路徑的一部分。假設這10個班的最多人數為35人,即身高的資料放在儲存格D2:D36中。該公式相當於「101!D2:D36」。

● 計算身高的最大值 儲存格J3:=MIN(INDIRECT(H3&"!"&"D2:D36"))

● 計算身高的最小值 儲存格K3:=AVERAGE(INDIRECT(H3&"!"&"D2:D36"))

複製以上的儲存格,往下各列貼上。其餘體重和 BMI 值的最大值、最小值和平均值的計算方式完全相同,請自行練習。

詳細函數說明請參閱微軟網站:

INDIRECT:http://office.microsoft.com/zh-tw/excel-help/HP010342609.aspx

沒有留言:

張貼留言

好康東東