問題:

老師們讓學生繳交作業時是透過Google表單上傳資料,Google表單也會在Google試算表中儲存學生的繳交資訊,包含班級和座號等。如果老師教授的班級較多,要在短時間內找出尚未繳交作業的學生,一筆一筆的核對,會造成老師的負擔,有沒有較快的方式?

參考下圖,如果取得了班級和座號的資訊,可以利用這個欄位,在一個班級和座號矩陣中可以尚未繳交的班級、座號上做標記。

Excel-從已繳交清單中找出尚未繳交作業的學生(SUMPRODUCT)

 

【公式設計與解析】

假設共有五個班級,每個班級20個學生,假設學生可能會重覆繳交。

1. 先選取儲存格A1:B300(為何比所有學生總和多?因為學生可能會重覆繳交。),按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:班級、座號。

2. 在儲存格E1:J21中,建立班級、座號的矩陣。

3. 輸入公式,儲存格F2:=IF(SUMPRODUCT((班級=F$1)*(座號=$E2)),"","X")

在 SUMPRODUCT 函數使用兩個條件:「班級=F$1」和=座號=$E2」。其中「*」運算子相當於執行邏輯 AND 運算

(1) 若完全符合條件者會傳回數值 1。

(2) 若同一學生繳交多次,則會傳回大於 1 的數值。

(3) 若學生未繳交,則會傳回 0。

最後,利用這個傳回值,只要是大於或等於 1 者表示有繳交,顯示空字串;若是未繳交者,則顯示「X」記號。

相同的操作和公式,可以套用在 Google 試算表中,請自行練習囉!

Excel-從已繳交清單中找出尚未繳交作業的學生(SUMPRODUCT)

創作者介紹
創作者 vincent 的頭像
vincent

學不完.教不停.用不盡

vincent 發表在 痞客邦 留言(2) 人氣()