2010年10月26日 星期二

Excel-統計各種入學管道的人數及平均成績

如果你取得以各種入學管道入學的資料表,而想計算各個來源學校的各種入學管道的人數及平均成績,該如何計算?

如果想要求像下圖的人數和平均成績,試試以下的做法:

(一)計算各校各種管道的人數

儲存格B2:{=SUM(IF(Sheet1!$C$2:$C$428=Sheet2!$A3,IF(Sheet1!$B$2:$B$428=Sheet2!B$2,1,0)))}

陣列公式,輸入公式後按 Ctrl+Shift+Alt 鍵。複製儲存格B2至儲存格B2:D2。

其原理是找出合於學校名稱,並且合於入學管道名稱者,給予1,否則給予0。計算所有1的總合即為人數。

如果不想使用陣列公式,也可以輸入以下的公式:

儲存格B2:=SUMPRODUCT((Sheet1!$C$2:$C$428=Sheet2!$A3)*1,(Sheet1!$B$2:$B$428=Sheet2!B$2)*1)

(二)計算各種各種管道的平均成績

儲存格E3:

{=IF(ISERR(AVERAGE(IF(Sheet1!$C$2:$C$428=Sheet2!$A3,IF(Sheet1!$B$2:$B$428=Sheet2!B$2,Sheet1!$D$2:$D$428)))),"",AVERAGE(IF(Sheet1!$C$2:$C$428=Sheet2!$A3,IF(Sheet1!$B$2:$B$428=Sheet2!B$2,Sheet1!$D$2:$D$428))))}

陣列公式,輸入公式後按 Ctrl+Shift+Alt 鍵。複製儲存格E2至儲存格E2:G2。

原理類似(一),找出合於學校名稱並且合於入學管道名稱者所對應的成績,再計算其平均。因為有些入學管道為0人,會造成除數為0的錯誤,所以利用ISERR函數來判斷是否出錯,如果出錯,則設定為空白內容。

沒有留言:

張貼留言

好康東東