有老師問到:想要利用 Excel 的表格來排課程(如下圖),根據教師、日期和上課時間,如何能標示是否把老師的課排成衝堂了?

同一個教師在相同日期的相同時間裡,只能被安排一門課,超過一節就是衝堂。

Excel-檢查課程是否衝堂(SUMPRODUCT)

 

【公式設計與解析】

現在要設計如果有衝堂時,則在該節位置設定淺綠色儲存格底色。

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 陣列。

Excel-檢查課程是否衝堂(SUMPRODUCT)

每一個儲存格的運算結果如下:

Excel-檢查課程是否衝堂(SUMPRODUCT)

arrow
arrow
    文章標籤
    Excel SUMPRODUCT
    全站熱搜

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