學不完．教不停．用不盡

網友問到：如何根據 Excel 中的打卡資料清單（如下圖左），列出每日每個人的上班時間和下班時間（如下圖右）？

上班時間和下班時間的規範：

上班卡：介於07:00～09:30最先一個時間

下班卡：介於17:30～24:00最後一個時間

【公式設計與解析】

選取儲存格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))*(打卡<TIME(9,30,0))

條件：判斷打卡時間是否在上班時間的區間中。

(4) ((日期=E2)*(人員=F2)*(打卡>=TIME(7,30,0))*(打卡<TIME(9,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 找出下時間區間中的最大值。