2013年1月12日 星期六

Excel-分組報告成績輸入統計表

老師們常在課堂上讓學生分組上台報告,並且互打分數,例如每個學生取得以下的打成績表單,自己組別無需打分數。老師取回班上所有同學的成績表單,該如何在 Excel 中算出分組成績呢?

成績輸入系統要求:(參考下圖)

1. 自己組別不需打成績,自己組別儲存格以深色表示,若強行輸入,則顯示錯誤訊息。

2. 同組每個人的成績相同,即為全班給分的平均。若有人某些組別不給分,則計算平均時,不以 0 分列入平均。

3. 每班人數不一,而且每組人數也不一定相同。

 

【做法】

1. 選取儲存格D2,執行設定格式化的條件,設定規則:

使用公式來決定要格式哪些儲存格,輸入公式:「=D$1=$C2」,設定儲存格色彩:深綠色。

2. 執行「資料驗證」,在[設定]標準下設定:

資料驗證準則:自訂,公式:「=$C2<>COLUMN(A:A)」

3. 在[錯誤提醒]標籤下設定:

標題:輸入錯誤,訊息內容:自己這一組不用打成績!

強行輸入時,顯示錯誤訊息:

4. 複製儲存格C2,貼至儲存格I27。

5. 處理學生的成績(同組學生分數相同):

因為每班人數可能不一樣,而且每組人數也不一定相同。

儲存格J2:=AVERAGE(OFFSET($D$2,,$C2-1,COUNT($C$2:$C$27),))

COUNT($C$2:$C$27):找出學生的人數。

OFFSET($D$2,,$C2-1,COUNT($C$2:$C$27),):判斷學生如果為第1,2,3,… 組,則取第D,E,F,...欄來計算。

透過 AVERAGE 函數來計算平均時,若有人某些組別不給分,則計算平均時,不會以 0 分列入平均。

6. 複製儲存格J2,貼至儲存格J2:J27。

7. 當不同班級人數時,只要刪除/複製各列即可使用。

沒有留言:

張貼留言

檢視其他文章

好康東東