贊助廠商

///本部落格所有文章列表///

搜尋本部落格文章資料

廣告贊助

網友問到:如何根據 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))*(打卡<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 找出下時間區間中的最大值。

文章標籤
創作者介紹

學不完.教不停.用不盡

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


留言列表 (3)

發表留言
  • 婷婷
  • 老師您好~我有一個excel的問題想要請您解答~寄到您gmail的信箱~請問有收到嗎?
  • he41
  • 非常的謝謝您, 我會再好好的依您的教學方式重新編製,如果沒有好的ERP,真的要有好的excel學習力,真的能幫工作增加效率 (我會將您的文章,放在104職涯社群,把好的exceln解答也分享給大家使用,因為我提出了問題,也希望大家能有更多方式解決~)謝謝您的無私
  • 謝謝你光臨我的網站。

    vincent 於 2017/04/19 15:16 回覆

  • ning
  • 老師您好:
    老師我可以請教您excel的問題嗎?
找更多相關文章與討論

您尚未登入,將以訪客身份留言。亦可以上方服務帳號登入留言

請輸入暱稱 ( 最多顯示 6 個中文字元 )

請輸入標題 ( 最多顯示 9 個中文字元 )

請輸入內容 ( 最多 140 個中文字元 )

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼