2017年4月16日 星期日

Excel-依打卡時間清單找出上班和下班時間(TIME,陣列公式)

網友問到:如何根據 Excel 中的打卡資料清單(如下圖左),列出每日每個人的上班時間和下班時間(如下圖右)?
上班時間和下班時間的規範:
上班卡:介於07:00~09:30最先一個時間
下班卡:介於17:30~24:00最後一個時間
Excel-依打卡時間清單找出上班和下班時間(TIME,陣列公式)

【公式設計與解析】
選取儲存格A1:C13,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:人員、日期、打卡。
1. 找出上班時間
儲存格G1:{=MIN(IF((日期=E2)*(人員=F2)*(打卡>=TIME(7,30,0))*(打卡<
TIME(9,30,0)),打卡,""))}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 自動輸入「{}」。
複製儲存格G1,往下各列貼上。
(1) TIME(7,30,0):用以產生7:30。
(2) TIME(9,30,0)):用以產生9:30
(3) (打卡>=TIME(7,30,0))*(打卡
條件:判斷打卡時間是否在上班時間的區間中。
(4) ((日期=E2)*(人員=F2)*(打卡>=TIME(7,30,0))*(打卡
判斷『日期、人員和上班時間區間』三個條件是否都符合,傳回 TRUE/FALSE 陣列。其中『*』運算,相當於執行邏輯 AND 運算。
(5) IF(第(4)式,打卡,"")
在陣列公式中判斷符合第(4)式者,傳回打卡陣列,否則傳回空字串『""』。
(6) MIN(IF(第(4)式,打卡,""))
在傳回的打卡陣列中,利用 MIN 函數取出其中的最小值。

2. 找出下班時間
儲存格H1:{=MAX(IF((日期=E2)*(人員=F2)*(打卡>=TIME(17,30,0))*(打卡<=
TIME(23,59,59)),打卡,""))}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 自動輸入「{}」。
複製儲存格H1,往下各列貼上。
原理同 1. 找出上班時間,時間區間設為 TIME(17,30,0) 和 TIME(23,59,59)。
利用 MAX 找出下時間區間中的最大值。

沒有留言:

張貼留言

檢視其他文章

好康東東