實用Excel技巧分享：快速整理考勤資料！

在先前的文章《Excel圖表學習之創建有目標值的多系列多條件柱狀圖》中，我們了解了一個Excel圖表教程，而今天我們分享一篇整理考勤資料的教程，就簡單兩個公式，學會後，不管你公司有多少人都不帶怕的。

考勤機幾乎是每個公司都會使用的，而每到月初月末，整理考勤機導出的考勤數據，需要耗費我們大量的時間，今天教給大家兩個公式，一分鐘就可以整理出考勤數據。

我們平時從考勤機導出的考勤數據，透過Excel打開後基本上都是這樣的格式：

為了進一步統計考勤數據，希望將上面的這種格式整理為這樣的效果：

每人每天的打卡記錄在同一行，並且根據上下班時間比對後標註出異常情況。

上下班時間規定如下：

上午上班時間8:00，上午下班時間12:00，下午上班時間13:30，下午下班時間17:30

#要實現這種效果的轉換，感覺是非常麻煩的一件事，其實只要掌握兩個公式和一些基本的操作技巧，一分鐘就可以完成，下面就來看看如何實現吧。

一、基礎資料整理

在基礎資料的右邊新增幾列，將時間分為上午上班、上午下班、下午上班和下午下班四列，並且標註出對應的時間：

將卡號、人員和日期三列複製到右邊對應的位置，然後使用“刪除重複項”功能：

點擊確定後會刪除重複的內容，每人每天只保留一行：

##接下來的任務就是將對應的打卡時間填入對應的位置，且對異常資料不顯示具體時間，只顯示異常兩個字。為了達成這個目的，需要使用兩個公式來配合，以下先看第一個公式。

二、使用公式備註打卡時間

#為了方便對打卡時間進行統計，首先要根據上下班時間進行備註，實現下圖中的效果：

根據上下班時間需要分為四種情況：

1、8點以前打卡視為上午上班；

2、12點以後打卡視為上午下班，考慮到還有下午上班這個因素，人為規定12點到12點30之間打卡為上午下班；

#3、同理，人為規定13點到13點30之間打卡為下午上班；

4、17點30以後打卡為下午下班；

5、除此之外的時間打卡均為無效，顯示空白。

E2單元格公式為：

=IF(D2=$L$1,D2< ;=$O$1),$L$2,"")&IF(AND(D2>=$P$1,D2=$N$1,$ N$2,"")，雙擊填滿可達到圖中的效果。

第一個IF為：=IF(D2

當d2（打卡時間）小於等於k1（上午上班時間）時，if函數的結果為k2（上午上班這四個字），否則回傳空值；

第二個if為 ：IF(AND(D2>=$L$1,D2

當d2 （打卡時間）大於等於L2（上午下班時間）同時小於等於o1（人為規定下班打卡截止時間）時，if函數的結果為L2（上午下班這四個字），否則傳回空值。

第三個if為：IF(AND(D2>=$P$1,D2

當d2（打卡時間）大於等於p2（人為規定上班打卡開始時間）同時小於等於M1（下午上班時間）時，if函數的結果為M2（下午下班這四個字），否則傳回空值。

第四個if為：IF(D2>=$N$1,$N$2,"")

當d2（打卡時間）大於等於N1（下午下班時間）時，if函數的結果為N2（下午下班這四個字），否則傳回空值。

完成了備註資訊之後，就該把對應的時間填入對應的區域內，這時候可以用一個公式右拉下拉就能完成時間的填充，一起來看看是哪個神奇的公式吧。

三、填入時間

在K2單元格輸入公式：

=TEXT(SUMIFS($ D:$D,$C:$C,$J3,$A:$A,$H3,$E:$E,K$2),"hh:mm:ss;;異常;")

右拉下拉即可完成時間的填滿。

這個公式用到了兩個函數，text和sumifs，來看看公式的原理吧。

sumifs函數的結構為sumifs（要求和的資料區域，條件區域1，條件1，條件區域2，條件2…），在今天的例子我們用了三個條件，實際求和的是D列，三個條件分別是日期、卡號和備註訊息，符合三個條件的數字都是唯一的，所以求和結果和引用結果是一致的。

因此公式為：

SUMIFS($D:$D,$C:$C,$J3,$A:$A,$H3,$E:$E ,K$2)

使用公式得到結果是這樣的一些數字，因為在Excel中，日期和時間的本質都是數字，整數代表日期，而小數就代表時間，將上述區域單元格格式改為時間再看看效果，數字都變成了具體的時間，如下所示：

##實際上在進行了單元格格式設定後基本上就達到目的了，為了完善顯示效果，同時強制顯示為時間格式，我們在sumifs外面再加了一個text函數，即使在常規格式下，也是按時間來顯示的，同時0所在的位置顯示為異常。

簡單解釋text的用法，text（數據，指定的格式），在本例中，第二參數格式定義為時分秒的顯示方式，字母h 、m和s分別表示時分秒，都是兩位數字顯示。

格式程式碼中的分號，可以依照資料型別單獨設定顯示方式，text規定將資料分成四種：正數;負數;零;文字。本例中正數依時間格式顯示，負數和文字沒有指定格式就不顯示，而零顯示為異常兩個字。

關於text函數今天只是了解在本例的用法即可。

小結：

1、合理利用輔助列：考勤資料的整理歷來都是比較麻煩的問題，一步到位往往非常困難，此時合理的利用輔助列就能將問題的難度一下子降低不少。

2、公式不能萬能的：考勤的基礎數據就是時間，還有相關的規定共同組成了數據之間的邏輯關係，本例適合比較規範的情況，如果是多種班次並存的情況，就不能透過這種方法來實現了，還要結合每個人的班次對應的上下班時間來綜合考慮，就需要考勤機的配套軟體來完成數據的統計匯總。

相關學習推薦：

excel教學

以上是實用Excel技巧分享：快速整理考勤資料！的詳細內容。更多資訊請關注PHP中文網其他相關文章！