僅5分鐘,將數千條打卡記錄做成考勤統計表

2021-02-15 Excel函數與公式

點擊上方藍字  免費關注

置頂公眾號或設為星標，否則可能收不到文章

個人微信號 | （ID：LiRuiExcel520）

微信服務號 | 跟李銳學Excel（ID：LiRuiExcel）

微信公眾號 | Excel函數與公式（ID：ExcelLiRui）

工作中經常會遇到從幾千條記錄中提取統計結果報表的需求，如果不掌握點化繁為簡的技術，哭都來不及了！

就拿很多企業都有的員工打卡記錄來說吧，每個員工會在不同時段打卡，有的人還經常多次重複打卡，你既要按照時段區分出哪些是上班卡，哪些是下班卡，還要進行多次打卡的排重處理，怎麼辦才能兼顧所有要求搞定問題呢？

看完本教程，你就能多掌握一個化腐朽為神奇的大招。

看完教程還想進一步系統學習的同學，從「跟李銳學Excel」底部菜單，或下方二維碼進知識店鋪。

更多不同內容、不同方向的Excel視頻課程

長按識別二維碼↓獲取

（長按識別二維碼）

咱們先來看一下6月份的數據源，包含各個員工在每天的多次打卡記錄。

每人每天打卡次數不固定，有可能一個時段內多次打卡，也可能某個時段缺卡沒有打卡記錄，見下圖所示。

將上述數千條打卡記錄按本文教程所授方法處理，僅需5分鐘，即可得到所有員工當月的考勤統計表，如下圖所示。

處理任何問題，動手之前先構建思路，然後選擇最合適的方法，最後才是動手落地執行。

拿這個案例來說，至少分為三步處理：

1、將零散的打卡記錄按企業規定，歸屬到對應的打卡區間，標識出早上打卡、中午打卡、晚上打卡；

2、將整月各員工每天的打卡標識合併同類項，雙條件分類匯總統計，一個條件是按打卡區間分類匯總、一個條件是按天分類匯總；

3、將上述中間結果處理為想要的統計結果，比如每天有打卡記錄的位置用√標識，缺卡處留空白等。

思路捋順之後，再選擇最合適的方法按思路步步拆解，用到的技術包含函數公式、數據透視表等，具體見下文步驟說明。

由於數據源中C列的打卡時間很零散，首先要根據企業規定歸類。

企業打卡規定如下：

1、一天分為3個打卡區間分別打卡

2、打卡區間劃分如下：

（1）早9:30前：早上打卡

（2）午12:00至13:30：中午打卡

（3）晚16:30後：晚上打卡

3、其餘時間段打卡無效。

在D列添加輔助列，輸入下列公式。

=IF(C2<=VALUE("09:30"),"早上打卡",IF(AND(C2>=VALUE("12:00"),C2<=VALUE("13:30")),"中午打卡",IF(C2>=VALUE("16:30"),"晚上打卡","")))

這樣就可以把C列的打卡時間自動歸類，對應到早上打卡、中午打卡、晚上打卡，沒在指定打卡時段的無效打卡就返回空。如下圖所示。

將數據源規範整理好以後，繼續下一步，將數據按需求分類匯總統計。

在規範打卡記錄數據源的基礎上使用數據透視表批量分類匯總。

透視表布局如下所示。

篩選有效打卡區間，設置日期自定義格式為d，將2019/6/1顯示為1，取消姓名分類匯總行，效果如下所示。

這時每個員工在每天的各時段打卡次數已經統計出來了，還需要進一步轉換顯示效果。

在數據透視表中設置數字格式，自定義格式代碼如下所示。

[>=1]√;G/通用格式

轉換後，數據透視表效果如下所示。

為了快捷篩選，可以插入切片器，按姓名或者按打卡區間快速切換報表布局。

如果你想重點查看早上打卡情況，可以藉助切片器快速使報表僅顯示對應數據。

這樣就將數千條零散的打卡記錄做成想要的考勤統計表了，以後每月數據源更新後，刷新透視表即可自動更新結果。

更多打卡記錄及考勤管理技術在HR人資實戰特訓營講解，進知識店鋪查看。

希望這篇文章能幫到你！

這麼多內容擔心記不全的話，可以分享到朋友圈給自己備份一份。

此案例僅為十三期特訓營內容的1%，數據透視表進階班裡面有此案例的超清視頻講解，結合100個案例傳授透視表進階必備的實戰技術。

100案例詳細介紹進透視表進階班查看

長按下圖 識別二維碼，永久擁有超值課程

（長按識別二維碼）

希望這篇文章能幫到你！

這麼多內容擔心記不全的話，可以分享到朋友圈給自己備份一份。

更多經典的實戰技能，已整理成超清視頻的系統課程，方便你系統提升。

如果你喜歡超清視頻同步演示講解的課程，下方掃碼查看↓

（點擊圖片可放大查看）

長按識別二維碼↓進知識店鋪

（長按識別二維碼）

今天就先到這裡吧，希望這篇文章能幫到你！更多乾貨文章加下方小助手查看。

如果你喜歡這篇文章

歡迎點個在看，分享轉發到朋友圈

乾貨教程 · 信息分享

歡迎掃碼↓添加小助手進朋友圈查看

>>推薦閱讀 <<

（點擊藍字可直接跳轉）

史上最全VLOOKUP函數套路大全

Excel萬能函數SUMPRODUCT

IF函數強大卻不為人知的實戰應用技術

SUM函數到底有多強大，你真的不知道！

史上最全條件求和函數SUMIF教程

最具價值日期函數DATEDIF套路大全

Excel高手必備函數INDIRECT的神應用

飛簷走壁的函數裡數她輕功最好！她就是...

COUNTIF，堪比統計函數中的VLOOKUP，你會用嗎？

這個函數堪稱統計之王，會用的都是高手！

更多的Excel實戰技術，我已經整理到Excel特訓營中以超清視頻演示並同步講解，不但有具體場景，還講解思路和方法，更有配套的課件下載和社群互動。

想系統學習的同學長按下圖識別二維碼。

長按下圖  識別二維碼，進入知識店鋪

按上圖↑識別二維碼，查看詳情

請把這個公眾號推薦給你的朋友：）

長按下圖 識別二維碼

關注微信公眾號（ExcelLiRui），每天有乾貨

關注後置頂公眾號或設為星標

再也不用擔心收不到乾貨文章了

▼

關注後每天都可以收到Excel乾貨教程

請把這個公眾號推薦給你的朋友

↓↓↓點擊「閱讀原文」進知識店鋪

     全面、專業、系統提升Excel實戰技能

相關焦點

  • EXCEL在考勤記錄中的應用
    ，考勤方式發展從人工手動籤到到打卡機、磁卡打卡、指紋打卡，到如今移動考勤，各有優缺點。公式=IF(實際下班時間-應下班時間>30,TEXT(實際下班時間-應下班時間,"[m]"),"")【要求下班超半個小時後計算加班時間】4、根據請假單對未打卡的空白情況進行填寫說明5、統計每人當月各類假期的請假天數函數COUNTIF(range
  • excel怎麼通過打卡記錄簡單統計考勤?
    很多公司上班是需要打卡的，我們公司打卡是採用刷臉的辦法，每個月需要根據打卡的記錄來算工資，那麼如何利用打卡記錄來大致統計下每個月哪些人哪些天到了呢？如何確定是否遲到呢？1、我們打卡記錄導出的表是一個txt文檔，將它用excel打開後，刪除不需要的內容，比如該打卡記錄的第一行。
  • 7345條考勤記錄,10分鐘核對完,你能嗎?
    60秒get一個HR實用小技巧最近有人在後臺留言說：每個月初最不想面對的其實是成千上萬條的考勤數據，早班、夜班、正常班、周末班，簡直要被逼瘋！有什麼Excel方法能一次性處理好所有員工的考勤信息麼？貼心如小薪，今天就幫各位請來了「大表姐」Coco老師，拜託Coco老師結合她平時工作中的實際案例，給大家詳細介紹一下怎麼用Excel處理打卡記錄，輕鬆搞定每月考勤！
  • Excel教程:幾分鐘搞定考勤統計分析表!
    如果涉及的統計有一千多行數據，哭的心思都有了：什麼時候才能下班喲！今天老菜鳥通過考勤統計分析表實例分享自己面對新統計需求的解決方法：簡化數據、找數據規律、做輔助列。任何複雜的統計，只要採取這樣的方法，大多幾分鐘就會搞定。
  • excel拆分實例：如何快速製作考勤統計分析表
    今天老菜鳥通過考勤統計分析表實例分享自己面對新統計需求的解決方法：簡化數據、找數據規律、做輔助列。任何複雜的統計，只要採取這樣的方法，大多幾分鐘就會搞定。考勤管理，是企業勞動紀律管理的最基本工作，公司領導要求人力資源每周按部門匯總一份考勤打卡情況通報，格式如下：
  • 考勤打卡統計,這才是最簡單的方法!
    蘭色看了這樣一個關於考勤打卡的求助：從打卡機中導出的2016-2019年員工考勤信息問：如何統計2018
  • 疫情常態下,企業應如何選擇合適的考勤管理系統和考勤打卡工具?
    專業考勤管理系統相較於考勤機附贈的免費版，不僅功能全面，可以為不同部門自定義配置考勤規則，還可以將統計考勤數據進行更深層次的分析和處理。而對於一些對考勤排班要求特別高的行業，如工廠，集團型企業和酒店等，通過專業考勤系統能實現智能排班。
  • 員工找人代打卡偽造考勤記錄,可以開除嗎?
    被告公司辯稱，原告以偽造考勤記錄的方式騙取加班工資，被告與其解除勞動合同的規章制度依據為《員工手冊》第九章，其中「獎懲規定」第2.2.3條的規定：「給予『解除勞動合同』處分的行為包括：……利用任何欺騙的手段為個人或小團體謀取利益者……」根據被告公司考勤記錄系統，2018年4月1日星期天，原告薛某進門刷卡時間為8:11，出門刷卡時間為20:24；案外人張某進門刷卡時間為
  • 如何用手機打卡考勤軟體打卡?
    當你進入一家新公司時，需要做的第一件事就是考勤籤到。作為一款不花錢就能用的的考勤管理神器，「打卡助手」不僅可以隨時隨地記錄你的考勤，重要的是幫助客戶實現考勤統計、輕簡辦公、溝通協作、企業管理，能夠極大地節省企業日常考勤、辦公、溝通、管理的成本。
  • excel員工考勤表中漏打卡員工太多,如何統一在表格中標記出來?
    文職類工作最常遇到的問題，在excel中批量給空白單元格填充相同內容，只需兩分鐘就能學會，學習excel小技巧，讓辦公效率翻倍提升，告別天天加班做表格。做人事的同事對於員工考勤表應該最熟悉不過了，上下班打卡都要記錄在每一位同事的考勤表中，如果公司很大，員工很多，每天漏打卡的同事也相對較多，員工漏打卡或者請假都需要備註在考勤表中統一交給財務最後計算工資，那麼如何快速統一給漏打卡的同事添加標記呢？
  • 員工考勤管理制度實施細則
    四、員工必須使用手機「釘釘」軟體親自打卡考勤，如有代打卡行為的，每次對委託人和代辦人各給予500-1000元罰款。五、實行標準工時制的，上班和下班各打卡一次，中間時段不用打卡。實行多個班次的，每個班次的上班和下班均要打卡。採用紙質籤到方式記錄考勤的，每個工作日手工籤到兩次，上班和下班各籤到一次。
  • Excel怎麼製作考勤表模板?能自動更新日期和統計考勤
    考勤表大家都不陌生，可以記錄上班考勤情況，今天教大家製作動態考勤表模板，可以自動更新日期，還可以統計考勤天數，HR人事必會的知識！演示效果A、更改月份星期日期也會變化；B、下拉菜單可以選擇考勤符號。C、自動統計考勤天數。
  • 震驚 | 指紋打卡居然不能作為考勤依據,企業敗訴賠錢的原因竟然是……
    企業的考勤方式也從籤到，變為打卡鐘、IC卡、指紋、人臉，再到「釘釘」等考勤軟體，考勤記錄越來越難以證實。發生加班費糾紛時，企業提供的電子考勤記錄往往不被採納。案例小白2015年5月1日入職廣州一家工廠，擔任生產工作，基本工資為3000元/月，每周工作6天，每天8小時。該廠通過指紋打卡方式考勤，小白入職時錄入指紋。
  • 這也太神了,整個部門的異常考勤記錄,用1個公式就能搞定?
    又到了月初該統計員工考勤的時候了，每到這個時候，總能聽到很多HR寶寶的嚎叫——「考勤記錄太亂了！我算一天也算不過來呀！你們誰Excel算得快幫我看一下！」還有很多HR寶寶因為Excel用得不好，經常會花好幾天時間算考勤，甚至為了避免出錯，還得一邊對著電腦、一邊手裡拿著計算器核算，因為效率比較低，常常要加班到深夜！
  • HR必會,Excel快速統計考勤
    考勤統計是每個HR日常工作之一，是HR必備技能。如果你不會用函數、技巧進行處理，而只是用最原始的手工做法，那麼你將花很多時間處理考勤，且容易出錯。今天我們就一起來學習如何快速統計考勤。以上是三國公司員工考勤數據這是最簡單的考勤機數據，是老式打卡機的數據。其實處理這種數據很簡單。
  • 5個步驟製作可以自動更新星期、自動統計的考勤表
    先看看效果，這個考勤表至少有以下兩個作用。設置了員工考勤狀態後，會自動統計本月所有員工考勤異常的天數。自動標記周末為紅色，修改年份和月份後，會自動更新星期。現在就讓我們一起來製作考勤表吧。步驟1先輸入基本的一些數據，包括員工姓名、日期等。日期只要輸入數字「1-31」即可。為了操作的方便，日期輸入完畢，建議先隱藏部分列。
  • 法治課|工資單、考勤表沒有員工籤字,公司將承擔不利後果
    庭審中，華翔公司為了證明賈女士已休年假，提交了賈女士離職請假情況表列印件，稱其根據賈女士籤到表、指紋打卡記錄、打卡機記錄的出勤情況匯總而成，但上面並無賈女士的籤字。此外，華翔公司還提交了賈女士工資月實發明細表複印件、考勤記錄，但上面均無賈女士籤字。對於這三份證據，賈女士均不予認可。
  • 自學EXCEL小技巧012→考勤表之快速錄入考勤狀況及匯總統計
    操作實例二、統計考勤狀況考勤統計這塊，我分兩個部分，第一部分是個人月份考勤情況、統計，這塊的統計情況我放在「基礎考勤表」當中；第二部分為公司一個月考勤統計基本情況（一）個人月份考勤統計情況第一步：1.在第一個統計「正常」考勤狀態的單元格內輸入公式「=COUNTIF(C5:AG5,"正")」；2. 在第一個統計「請假」考勤狀態的單元格內輸入公式「=COUNTIF(C5:AG5,"請")」；3.
  • 網絡考勤機打卡軟體的考勤記錄提到電腦方法
    網絡考勤機打卡軟體的考勤記錄提到電腦方法網絡考勤機打卡軟體網絡考勤機打卡軟體的考勤記錄提到電腦詳細的步驟1、首把網絡考勤機打卡軟體的計算機操作程序安裝在計算機上，應該在買機子的時候朽有的，登錄後，一般沒有密碼，如果有默認123456或是1235、進入網絡考勤機打卡軟體後在【設備】或是【管理】中增加網絡考勤機打卡軟體
  • 考勤系統使用攻略
    溫馨提示：1、加班申請與打卡記錄缺一不可，除03原因（休息日或法定假日出差）外其他原因加班，必須有相應的打卡記錄加班方可生效！2、當月加班當月發起，逾期無效。3、加班原因要符合實際情況。    點擊異常統計，如無異常記錄，考勤則正常無需處理，如有異常記錄，點擊該條記錄，選擇相應的異常原因並確認提交！