有老師問到:想要利用 Excel 的表格來排課程(如下圖),根據教師、日期和上課時間,如何能標示是否把老師的課排成衝堂了?
同一個教師在相同日期的相同時間裡,只能被安排一門課,超過一節就是衝堂。
【公式設計與解析】
現在要設計如果有衝堂時,則在該節位置設定淺綠色儲存格底色。
1. 選取儲存格F2:M11。
2. 設定格式化的條件。
規則類型:使用公式來決定要格式化哪些儲存格
規則:
=SUMPRODUCT(($B$2:$B$11=$B2)*($E$2:$E$11=$E2)*(F$2:F$11="V"))>1
格式:儲存格底色設定為淺綠色
合於三個條件:(($B$2:$B$11=$B2)*($E$2:$E$11=$E2)*(F$2:F$11="V")者,如果加總結果大於 1,表示有 1 個以上的衝堂。
每一個條件(例如:$B$2:$B$11=$B2)會傳回 TRUE/FALSE 的陣列,而運算式中的「*」運算子,相當於執行邏輯 AND 的運算。運算時會將 TRUE/FALSE 的陣列轉換為 1/0 陣列。
每一個儲存格的運算結果如下: