在之前的文章《Excel圖表學(xué)習(xí)之創(chuàng)建有目標(biāo)值的多系列多條件柱狀圖》中，我們了解了一個(gè)Excel圖表教程，而今天我們分享一篇整理考勤數(shù)據(jù)的教程，就簡單兩個(gè)公式，學(xué)會后，不管你公司有多少人都不帶怕的。

考勤機(jī)幾乎是每個(gè)公司都會使用的，而每到月初月末，整理考勤機(jī)導(dǎo)出的考勤數(shù)據(jù)，需要耗費(fèi)我們大量的時(shí)間，今天教給大家兩個(gè)公式，一分鐘就可以整理出考勤數(shù)據(jù)。

我們平時(shí)從考勤機(jī)導(dǎo)出的考勤數(shù)據(jù)，通過Excel打開后基本都是這樣的格式：

為了進(jìn)一步統(tǒng)計(jì)考勤數(shù)據(jù)，希望將上面的這種格式整理為這樣的效果：

每人每天的打卡記錄在同一行，并且根據(jù)上下班時(shí)間比對后標(biāo)注出異常情況。

上下班時(shí)間規(guī)定如下：

上午上班時(shí)間8:00，上午下班時(shí)間12:00，下午上班時(shí)間13:30，下午下班時(shí)間17:30

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

一、基礎(chǔ)數(shù)據(jù)整理

在基礎(chǔ)數(shù)據(jù)的右邊添加幾列，將時(shí)間分為上午上班、上午下班、下午上班和下午下班四列，并且標(biāo)注出對應(yīng)的時(shí)間：

將卡號、人員和日期三列復(fù)制到右邊對應(yīng)的位置，然后使用“刪除重復(fù)項(xiàng)”功能：

點(diǎn)擊確定后會刪除重復(fù)的內(nèi)容，每人每天只保留一行：

接下來的任務(wù)就是將對應(yīng)的打卡時(shí)間填入對應(yīng)的位置，并且對異常數(shù)據(jù)不顯示具體時(shí)間，只顯示異常兩個(gè)字。為了實(shí)現(xiàn)這個(gè)目的，需要使用兩個(gè)公式來配合，下面先看第一個(gè)公式。

二、使用公式備注打卡時(shí)間

為了便于對打卡時(shí)間進(jìn)行統(tǒng)計(jì)，首先要根據(jù)上下班時(shí)間進(jìn)行備注，實(shí)現(xiàn)下圖中的效果：

根據(jù)上下班時(shí)間需要分為四種情況：

1、8點(diǎn)以前打卡視為上午上班；

2、12點(diǎn)以后打卡視為上午下班，考慮到還有下午上班這個(gè)因素，人為規(guī)定12點(diǎn)到12點(diǎn)30之間打卡為上午下班；

3、同理，人為規(guī)定13點(diǎn)到13點(diǎn)30之間打卡為下午上班；

4、17點(diǎn)30以后打卡為下午下班；

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

E2單元格公式為：

=IF(D2<=$K$1,$K$2,"")&IF(AND(D2>=$L$1,D2<=$O$1),$L$2,"")&IF(AND(D2>=$P$1,D2<=$M$1),$M$2,"")&IF(D2>=$N$1,$N$2,"")，雙擊填充可實(shí)現(xiàn)圖中的效果。

第一個(gè)IF為：=IF(D2<=$K$1,$K$2,"")

當(dāng)d2（打卡時(shí)間）小于等于k1（上午上班時(shí)間）時(shí)，if函數(shù)的結(jié)果為k2（上午上班這四個(gè)字），否則返回空值；

第二個(gè)if為：IF(AND(D2>=$L$1,D2<=$O$1),$L$2,"")

當(dāng)d2（打卡時(shí)間）大于等于L2（上午下班時(shí)間）同時(shí)小于等于o1（人為規(guī)定下班打卡截止時(shí)間）時(shí)，if函數(shù)的結(jié)果為L2（上午下班這四個(gè)字），否則返回空值。

第三個(gè)if為：IF(AND(D2>=$P$1,D2<=$M$1),$M$2,"")

當(dāng)d2（打卡時(shí)間）大于等于p2（人為規(guī)定上班打卡開始時(shí)間）同時(shí)小于等于M1（下午上班時(shí)間）時(shí)，if函數(shù)的結(jié)果為M2（下午下班這四個(gè)字），否則返回空值。

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

當(dāng)d2（打卡時(shí)間）大于等于N1（下午下班時(shí)間）時(shí)，if函數(shù)的結(jié)果為N2（下午下班這四個(gè)字），否則返回空值。

完成了備注信息之后，就該把對應(yīng)的時(shí)間填入對應(yīng)的區(qū)域內(nèi)，這時(shí)候可以用一個(gè)公式右拉下拉就能完成時(shí)間的填充，一起來看看是哪個(gè)神奇的公式吧。

三、填充時(shí)間

在K2單元格輸入公式：

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

右拉下拉即可完成時(shí)間的填充。

這個(gè)公式用到了兩個(gè)函數(shù)，text和sumifs，來看看公式的原理吧。

sumifs函數(shù)的結(jié)構(gòu)為sumifs（要求和的數(shù)據(jù)區(qū)域，條件區(qū)域1，條件1，條件區(qū)域2，條件2……），在今天的例子里我們用了三個(gè)條件，實(shí)際求和的是D列，三個(gè)條件分別是日期、卡號和備注信息，符合三個(gè)條件的數(shù)字都是唯一的，所以求和結(jié)果和引用結(jié)果是一致的。

因此公式為：

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

使用公式得到結(jié)果是這樣的一些數(shù)字，因?yàn)樵贓xcel中，日期和時(shí)間的本質(zhì)都是數(shù)字，整數(shù)代表日期，而小數(shù)就代表時(shí)間，將上述區(qū)域單元格格式改為時(shí)間再看看效果，數(shù)字都變成了具體的時(shí)間，如下所示：

實(shí)際上在進(jìn)行了單元格格式設(shè)置后基本就達(dá)到目的了，為了完善顯示效果，同時(shí)強(qiáng)制顯示為時(shí)間格式，我們在sumifs外面再加了一個(gè)text函數(shù)，即使在常規(guī)格式下，也是按時(shí)間來顯示的，同時(shí)0所在的位置顯示為異常。

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

格式代碼中的分號，可以按照數(shù)據(jù)類型單獨(dú)設(shè)置顯示方式，text規(guī)定將數(shù)據(jù)分成四種：正數(shù);負(fù)數(shù);零;文本。本例中正數(shù)按照時(shí)間格式顯示，負(fù)數(shù)和文本沒有指定格式就不顯示，而零顯示為異常兩個(gè)字。

關(guān)于text函數(shù)今天只是了解在本例的用法即可。

小結(jié)：

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

2、公式不能萬能的：考勤的基礎(chǔ)數(shù)據(jù)就是時(shí)間，還有相關(guān)的規(guī)定共同組成了數(shù)據(jù)之間的邏輯關(guān)系，本例適合比較規(guī)范的情況，如果是多種班次并存的情況，就不能通過這種方法來實(shí)現(xiàn)了，還要結(jié)合每個(gè)人的班次對應(yīng)的上下班時(shí)間來綜合考慮，就需要考勤機(jī)的配套軟件來完成數(shù)據(jù)的統(tǒng)計(jì)匯總。

相關(guān)學(xué)習(xí)推薦：excel教程

以上是實(shí)用Excel技巧分享：快速整理考勤數(shù)據(jù)！的詳細(xì)內(nèi)容。更多信息請關(guān)注PHP中文網(wǎng)其他相關(guān)文章！