二維碼 購物車
部落窩在線教育歡迎您!

學(xué)會這2個公式,整理考勤數(shù)據(jù)只要一分鐘

?

作者:老菜鳥來源:部落窩教育發(fā)布時間:2018-07-04 14:42:00點擊:28135

分享到:
0
收藏    收藏人氣:1人
版權(quán)說明: 原創(chuàng)作品,禁止轉(zhuǎn)載。

小編有話說:小編自己以前也做過人事,深知作為人事每到月初是多么的痛苦,大公司幾千人,就全靠幾個人事挨著挨著地核對。所以,今天特地給大家推送一篇整理考勤數(shù)據(jù)的教程,就簡單兩個公式,學(xué)會后,不管你公司有多少人都不帶怕的。


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

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

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

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

上下班時間規(guī)定如下:

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

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


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

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

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

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

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


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

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

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

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

2、12點以后打卡視為上午下班,考慮到還有下午上班這個因素,人為規(guī)定12點到1230之間打卡為上午下班;

3、同理,人為規(guī)定13點到1330之間打卡為下午上班;

41730以后打卡為下午下班;

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

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,""),雙擊填充可實現(xiàn)圖中的效果。

第一個IF=IF(D2<=$K$1,$K$2,"")

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

第二個ifIF(AND(D2>=$L$1,D2<=$O$1),$L$2,"")

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

第三個ifIF(AND(D2>=$P$1,D2<=$M$1),$M$2,"")

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

第四個ifIF(D2>=$N$1,$N$2,"")

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

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


三、填充時間

在K2單元格輸入公式:

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

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

這個公式用到了兩個函數(shù),textsumifs,來看看公式的原理吧。

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

因此公式為:

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

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

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

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

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

關(guān)于text函數(shù)今天只是了解在本例的用法即可,如果大家對于這個函數(shù)有興趣可以留言,我們會單獨寫一篇教程。


小結(jié):

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

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


本文配套的練習(xí)課件請加入QQ群:316492581下載。

如果您因工作所需使用到Excel,不妨關(guān)注部落窩教育的《一周Excel直通車》視頻課或《Excel極速貫通班》直播課系統(tǒng)學(xué)習(xí)。

相關(guān)推薦:

 008號是誰?電話多少?你需要一張員工信息動態(tài)查詢表!