一個老師將學生分成多組輪流上台報告，每個學生都要為各組給一個分數，而自己所在的組別不評分，每組成員的分數是相同的，都是全班其他組所給分數的平均。如何來設計這個表格呢？(參考下圖)
儲存格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)
並設錯誤提醒訊息，如下所示：
【延伸閱讀】
Excel-利用Google表單讓學生依評分量表實施分組互評並計算分數
Excel-依類別成績代碼轉換為分數(SUMPRODUCT)
Google Classroom-使用評分量表對學生作業評分
Excel-設計Google表單測驗多選題並在Excel中自動帶入結果得到分數
Excel-設計Google表單測驗多選題並以Excel計算分數
Excel-取用Google表單的線上測驗結果自行計算分數(SUMPRODUCT)
全站熱搜
留言列表