學不完．教不停．用不盡

網友問到：如果根據一個上下班打卡的資料表，如何在 Excel 中計算各個班別的數量？

如下圖，共有三種班別，起迄時間都不相同，如何根據打卡時間的清單，自動判斷各個班別(早班、晚班、全天)的數量？

【公式設計與解析】

選取B欄和C欄有資料的儲存格，按 Ctrl+Shfit+F3 鍵，勾選「頂端列」，定義名稱：上班打卡、下班打卡。

由於實際狀況多樣，所以，以下的範例不見得實用。

因為上班打卡不見得會準時，可能提早打卡或是延後打卡，因此假設上班前後一個小時內的打卡都接受。

1. 計算全天班

儲存格H3：=SUMPRODUCT(1*((下班打卡-上班打卡)>=12/24))

由於在 Excel 中一天24小時被定義為『1』，因此一小時以 1/24 計。而 12/24 表示 12 小時。

(下班打卡-上班打卡)>=12/24：當下班打卡和上班打卡時間相減時，必須大於 12 小時。在 SUMPRODUCT 函數，判斷會傳回是否大於 12/24 的邏輯運算結果(TRUE／FALSE)。

而公式中的『1*』，用意在於將 (下班打卡-上班打卡)>=12/24 傳回的 TRUE／FALSE 陣列轉換為 1／0 陣列。

最後透過 SUMPRODUCT 函數計算乘績和，即為所求。

2. 計算晚班

儲存格H4：=SUMPRODUCT(1*(ABS(14/24-上班打卡)<=1/24))

ABS(14/24-上班打卡)<=1/24：其由 14/24 表示一天的 14 時。14/24-上班打卡為計算上班打卡時與 14 時的差距，該式用以判斷該差距是否小於 1/24(1小時)。如果『是』，則為 14 時附近打卡，如果『否』，則不是在 14 時左右來打卡。

ABS 函數用以取數值的絶對值，而公式中的『1*』，用意在於將 ABS(14/24-上班打卡)<=1/24 傳回的 TRUE／FALSE 陣列轉換為 1／0 陣列。

最後透過 SUMPRODUCT 函數計算乘績和，即為所求。

3. 計算早班

儲存格H2：=SUMPRODUCT(1*(ABS(9/24-上班打卡)<=1/24))-(上式計算全天班)

因為早班和全日班的上班時間重覆，所以當計算上班時間是否在 9 時左右時，必須扣掉全天班的數量。

儲存格H2：=SUMPRODUCT(1*(ABS(9/24-上班打卡)<=1/24))-SUMPRODUCT(1*((下班打卡-上班打卡)>=12/24))

【延伸練習】

儲存格D2顯示的是每個儲存格判斷的各日班別。

儲存格D2：=IF(C2-B2>=12/24,"全天",IF(ABS(9/24-B2)<=1/24,"早班","晚班"))