2016年2月29日 星期一

Excel-依據打卡時間判斷上班的班別(SUMPRODUCT)

網友問到:如果根據一個上下班打卡的資料表,如何在 Excel 中計算各個班別的數量?
如下圖,共有三種班別,起迄時間都不相同,如何根據打卡時間的清單,自動判斷各個班別(早班、晚班、全天)的數量?
Excel-依據打卡時間判斷上班的班別(SUMPRODUCT)

【公式設計與解析】
選取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,"早班","晚班"))

沒有留言:

張貼留言

檢視其他文章

好康東東