學(xué)會(huì)這2個(gè)公式,整理考勤數(shù)據(jù)只要一分鐘
?
作者:老菜鳥(niǎo)來(lái)源:部落窩教育發(fā)布時(shí)間:2018-07-04 14:42:00點(diǎn)擊:28050
小編有話說(shuō):小編自己以前也做過(guò)人事,深知作為人事每到月初是多么的痛苦,大公司幾千人,就全靠幾個(gè)人事挨著挨著地核對(duì)。所以,今天特地給大家推送一篇整理考勤數(shù)據(jù)的教程,就簡(jiǎn)單兩個(gè)公式,學(xué)會(huì)后,不管你公司有多少人都不帶怕的。
考勤機(jī)幾乎是每個(gè)公司都會(huì)使用的,而每到月初月末,整理考勤機(jī)導(dǎo)出的考勤數(shù)據(jù),需要耗費(fèi)我們大量的時(shí)間,今天教給大家兩個(gè)公式,一分鐘就可以整理出考勤數(shù)據(jù)。
我們平時(shí)從考勤機(jī)導(dǎo)出的考勤數(shù)據(jù),通過(guò)Excel打開(kāi)后基本都是這樣的格式:
為了進(jìn)一步統(tǒng)計(jì)考勤數(shù)據(jù),希望將上面的這種格式整理為這樣的效果:
每人每天的打卡記錄在同一行,并且根據(jù)上下班時(shí)間比對(duì)后標(biāo)注出異常情況。
上下班時(shí)間規(guī)定如下:
上午上班時(shí)間8:00,上午下班時(shí)間12:00,下午上班時(shí)間13:30,下午下班時(shí)間17:30
要實(shí)現(xiàn)這種效果的轉(zhuǎn)換,感覺(jué)是非常麻煩的一件事,其實(shí)只要掌握兩個(gè)公式和一些基本的操作技巧,一分鐘就可以完成,下面就來(lái)看看如何實(shí)現(xiàn)吧。
一、基礎(chǔ)數(shù)據(jù)整理
在基礎(chǔ)數(shù)據(jù)的右邊添加幾列,將時(shí)間分為上午上班、上午下班、下午上班和下午下班四列,并且標(biāo)注出對(duì)應(yīng)的時(shí)間:
將卡號(hào)、人員和日期三列復(fù)制到右邊對(duì)應(yīng)的位置,然后使用“刪除重復(fù)項(xiàng)”功能:
點(diǎn)擊確定后會(huì)刪除重復(fù)的內(nèi)容,每人每天只保留一行:
接下來(lái)的任務(wù)就是將對(duì)應(yīng)的打卡時(shí)間填入對(duì)應(yīng)的位置,并且對(duì)異常數(shù)據(jù)不顯示具體時(shí)間,只顯示異常兩個(gè)字。為了實(shí)現(xiàn)這個(gè)目的,需要使用兩個(gè)公式來(lái)配合,下面先看第一個(gè)公式。
二、使用公式備注打卡時(shí)間
為了便于對(duì)打卡時(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í)間打卡均為無(wú)效,顯示空白。
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ī)定上班打卡開(kāi)始時(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è)字),否則返回空值。
完成了備注信息之后,就該把對(duì)應(yīng)的時(shí)間填入對(duì)應(yīng)的區(qū)域內(nèi),這時(shí)候可以用一個(gè)公式右拉下拉就能完成時(shí)間的填充,一起來(lái)看看是哪個(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,來(lái)看看公式的原理吧。
sumifs函數(shù)的結(jié)構(gòu)為sumifs(要求和的數(shù)據(jù)區(qū)域,條件區(qū)域1,條件1,條件區(qū)域2,條件2……),在今天的例子里我們用了三個(gè)條件,實(shí)際求和的是D列,三個(gè)條件分別是日期、卡號(hào)和備注信息,符合三個(gè)條件的數(shù)字都是唯一的,所以求和結(jié)果和引用結(jié)果是一致的。
因此公式為:
SUMIFS($D:$D,$C:$C,$J3,$A:$A,$H3,$E:$E,K$2)
使用公式得到結(jié)果是這樣的一些數(shù)字,因?yàn)樵?span style="font-family: 宋體; font-size: 12pt;">Excel中,日期和時(shí)間的本質(zhì)都是數(shù)字,整數(shù)代表日期,而小數(shù)就代表時(shí)間,將上述區(qū)域單元格格式改為時(shí)間再看看效果,數(shù)字都變成了具體的時(shí)間,如下所示:
實(shí)際上在進(jìn)行了單元格格式設(shè)置后基本就達(dá)到目的了,為了完善顯示效果,同時(shí)強(qiáng)制顯示為時(shí)間格式,我們?cè)?span style="font-family: 宋體; font-size: 12pt;">sumifs外面再加了一個(gè)text函數(shù),即使在常規(guī)格式下,也是按時(shí)間來(lái)顯示的,同時(shí)0所在的位置顯示為異常。
簡(jiǎn)單解釋一下text的用法,text(數(shù)據(jù),指定的格式),在本例中,第二參數(shù)格式定義為時(shí)分秒的顯示方式,字母h、m和s分別表示時(shí)分秒,都是兩位數(shù)字顯示。
格式代碼中的分號(hào),可以按照數(shù)據(jù)類型單獨(dú)設(shè)置顯示方式,text規(guī)定將數(shù)據(jù)分成四種:正數(shù);負(fù)數(shù);零;文本。本例中正數(shù)按照時(shí)間格式顯示,負(fù)數(shù)和文本沒(méi)有指定格式就不顯示,而零顯示為異常兩個(gè)字。
關(guān)于text函數(shù)今天只是了解在本例的用法即可,如果大家對(duì)于這個(gè)函數(shù)有興趣可以留言,我們會(huì)單獨(dú)寫(xiě)一篇教程。
小結(jié):
1、合理利用輔助列:考勤數(shù)據(jù)的整理歷來(lái)都是比較麻煩的問(wèn)題,一步到位往往非常困難,此時(shí)合理的利用輔助列就能將問(wèn)題的難度一下子降低不少。
2、公式不能萬(wàn)能的:考勤的基礎(chǔ)數(shù)據(jù)就是時(shí)間,還有相關(guān)的規(guī)定共同組成了數(shù)據(jù)之間的邏輯關(guān)系,本例適合比較規(guī)范的情況,如果是多種班次并存的情況,就不能通過(guò)這種方法來(lái)實(shí)現(xiàn)了,還要結(jié)合每個(gè)人的班次對(duì)應(yīng)的上下班時(shí)間來(lái)綜合考慮,就需要考勤機(jī)的配套軟件來(lái)完成數(shù)據(jù)的統(tǒng)計(jì)匯總。
本文配套的練習(xí)課件請(qǐng)加入QQ群:316492581下載。
如果您因工作所需使用到Excel,不妨關(guān)注部落窩教育的《一周Excel直通車(chē)》視頻課或《Excel極速貫通班》直播課系統(tǒng)學(xué)習(xí)。
相關(guān)推薦:
最熱教程
- 像綠皮火車(chē)一樣長(zhǎng)像珠穆拉瑪峰一樣高的Excel表怎么操作才方便?
- Power Query實(shí)戰(zhàn):按指定次數(shù)遞增數(shù)據(jù)
- 2019年全網(wǎng)最全—excel提取身份證信息合集?。ńㄗh收藏)-下篇
- 明明沒(méi)有重復(fù),Excel卻判定數(shù)據(jù)重復(fù),這是怎么回事?
- 文本格式的求和,及求和中最容易出現(xiàn)的問(wèn)題解疑
- 致命缺陷:不懂一維表!
- 函數(shù)組合思維,你有嗎?
- 學(xué)會(huì)這2個(gè)公式,整理考勤數(shù)據(jù)只要一分鐘
- 就算被說(shuō)是拍馬屁也成,今天你應(yīng)該這樣發(fā)Excel報(bào)表……
- 如何計(jì)算Excel單元格中的算式,四種求和方法請(qǐng)收好!
最新教程
- SHEETSNAME,一鍵獲得所有工作表名稱完成目錄制作
- 延遲退休落地,快速查詢你上幾年才退休!
- 快速將PDF電子發(fā)票數(shù)據(jù)提取到發(fā)票明細(xì)登記表的方法
- 提取、查找、替換數(shù)據(jù)的王者——REGEXP正則函數(shù)
- Excel表10萬(wàn)行數(shù)據(jù),篩選卡頓怎么辦?
- 兩個(gè)不用嵌套的萬(wàn)能提取公式,含用REGEXP函數(shù)提取
- 用Excel制作刷題器,備考無(wú)憂 !
- 如何用LET+LAMBDA直接寫(xiě)遞歸公式?
- 用函數(shù)合并多個(gè)工作表數(shù)據(jù)
- 用LAMBDA自定義顏色求和函數(shù)