2011年6月19日 星期日

Excel-為不同組別計算平均分數(OFFSET)

一個老師將學生分成多組輪流上台報告,每個學生都要為各組給一個分數,而自己所在的組別不評分,每組成員的分數是相同的,都是全班其他組所給分數的平均。如何來設計這個表格呢?(參考下圖)

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

複製儲存格J2,往下貼在儲存格J2:J25。

COUNT($C$2:$C$25):計算所有人員共有幾列。

OFFSET($D$2,,$C2-1,COUNT($C$2:$C$25),):取得各組的位址,例如第1組為儲存格D2:D25,第2組為E2:E25,…。

再使用AVERAGE函數計算平均。

現在,要利用格式化規則來設定自己組別的儲存格為較深的綠色,以提醒輸入成績時不要輸入。

先選取儲存格D2:I25,設定格式化規則為:

選取「使用公式來決定要格式化哪些儲存格」,輸入公式:「=$C2=COLUMN(A:A)」,格式為較深的綠色。

如果還是怕不小心輸入,則可以使用「資料驗證」方式,將不小心輸入的數值,顯示錯誤訊息(如下圖)。

先選取儲存格D2:I25,設定資料驗證為:

儲存格允許:自訂,公式:=$C2<>COLUMN(A:A)

並設錯誤提醒訊息,如下所示:

沒有留言:

張貼留言

好康東東