Excel員工薪資計算系統二遲到彙總表，快捷鍵成道，公式爭鋒

翌日，

黴黴

正準備去吃飯，收到主管發來的資訊：上次做的表格ok，現在需要計算出員工每月遲到的次數以及統計出他每個月所扣金額，遲到1小時以內算遲到，大於1小時算曠工一天按200計算（無特殊原因），遲到1次者不扣20元，2次以每次30元計即60，3次者以上者每次50元計算即N*50；最好今天能把表給我！

黴黴

回了條：資訊我一收到，我盡力完成。發完訊息就直奔食堂了，因為在哪裡能找到幫她的人！

轉眼到了食堂，看見“三劍客”。迅速打了一份飯菜，直接過去坐下，還沒有等黴黴開口，

小皮先開口了說

：吆，這不是黴黴嗎？來找你師傅啊，我們用迴避嗎？要不我們去別地吃啊！

小度

：是呢，我們不不當1000W的大燈泡，哪得多礙眼啊！

小陸正要說什麼時候，

黴黴開口了

：別拿我尋開心了，我這都快火上房了，也不知道我是不是上輩子欠我們主管很多錢，專門派來折磨我的嗎？又給我派硬茬活了，這不來找大神關照小妹啦，小妹這廂有禮啦！說完了就把主管發的資訊給他三看了。

小皮

：你這回答很官方啊，跟誰學的，“盡力完成”就是你不加班都挑不理來，因為你盡力啦！

小陸

：別說沒有用，就是把活派給你半天你能完成嗎？還不加班！

小皮

：什麼半天啊，我最多半個小時的好嗎！

小度

：什麼半個小時，哪麼長時間啊，我只需15分鐘就能搞定！

小陸

：我怎麼覺得天上有牛在飛，我怎麼也得2-3個小時，你們行嗎？

小皮和小度

：快點吃，一會我們就讓你知道什麼大神！

小陸偷偷跟黴黴說

：趕緊吃飯吧，看來今天不用加班了！

黴黴正要說話，

小皮和小度異口同聲的說

：吃完了，走啊，讓你見識見識什麼才是高手啊！

黴黴一愣，接著說

：真快真快，我也吃完了，走吧！說這就和他們三一起回到黴黴的工位上，小皮二話沒有說，直接坐下開始了他一貫的騷操作！

員工開啟資訊表

首先

將選擇上班打卡時間列第1個數據，然後按ctrl+shift+↓，然後ctrl+c，然後按幾下鍵盤→鍵，然後ctrl+v，粘貼後再按→鍵，並在單元格輸入9：00後，回車後ctrl+c，按←鍵，按ctrl+shift+↓選擇貼上範圍，按鍵盤屬性鍵（108鍵盤才有的鍵）或滑鼠右鍵，彈出選單中“選擇性貼上”，選擇相減，然後確定，然後旁邊新建輔助列，輸入公式=IF（HOUR（G2）>=1，IF（HOUR（G2）<=8，“曠工”，“正常”），“遲到”），回車，滑鼠移至右下角，變實心的＋，雙擊完成整列公式填充；

不用滑鼠操作步驟圖

其次

：框選表格資料範圍，點【插入】命令中的【資料透視表】，選擇建立新表格內建立，選擇建立的檢視的位置，然後確定，就彈出【資料透視表字段】選擇姓名欄位拖入行，型別拖入列，西格瑪值選擇型別，就會出現計數，這樣就能彙總遲到，曠工，正常個數。接著增加遲到扣款列，輸入遲到計算公式：=IF（B3>=3，（B3-1）*50，IF（B3>1，50，20）），增加曠工列，輸入計算公式==C3*200。搞定！

時間函式hour，minute，second語法結構圖

小皮對小陸說

：你可以看好了，我這有半個小時嗎？我前面的操作幾乎都沒有用滑鼠啊！

黴黴

：真是太快了，我還沒有怎麼反應過來就完成了，你太牛了，快跟我講講思路吧！

小皮

：不過這要從如何設定多少時或分或秒說起，如果我們想算出給定時間N的1小時前的時間有三種寫法，N-“1：00”、N-time（1，0，0）或N-1/24，我用的是第一種的寫法；我用打卡時間減去上9小時，如果9-10點之間開啟的，返回都是<1點的時間，>=1點有的兩種情況需要處理：

1。如果9點前來公式的會返回前一天的時間，10幾到24之間的數字

2。遲到最壞的情況是下班時間打上班的卡，最大也就8而已

歸結為if結果返回>=1，需進一步判斷是否<=8，為真返回“曠工”，否則為“正常”，而小於1的則為遲到。搞定這個之後，就是遲到扣款的計算了，條件：=1次，為20，=2次則60，>=3次是N*50，公式有多種寫法比如：=if（b3=1，20，if（b3=2，60，b3*50）），你可以寫=if（b3>=3，b3*50，if（b3=1，20，60））；

小度

：技巧用的挺溜啊，不過公式就寫的馬馬虎虎啦，if函式有什麼好的，看我不用if照樣搞定！

小度的步驟如下：建立標誌列，然後輸入公式=（HOUR（C2）=9）*（MINUTE（C2）>=1）*1+（HOUR（C2）>=10）*（HOUR（C2）<12）*2；然後快速填充公式，新建一個工作表-遲到，建立姓名，複製考勤表的姓名，貼上去重，b2輸入公式=COUNTIFS（考勤表！$A：$A，遲到！$A2，考勤表！$F：$F，COLUMN（）-2），拖拽公式完成填充，在遲到扣款輸入公式：=（C2=1）*20+（C2=2）*60+（C2>=3）*（C2*50），在曠工扣款輸入d2*200，完事。

小度對小皮說

：5分鐘搞定，你可看好了，我可以一個if都沒有用，照樣能解決問題！

黴黴

：太牛了，你是怎麼想的，能教教我嗎？

小度

：沒有問題啊，其實思路很簡單，遲到1小時為遲到，我們只需排查9點01到10點的時間，即小時為9，且分鐘數>=1就行了，如果>=10，則肯定超過1小時就算曠工了，不過應該小於吃飯吃飯時間12點，超過12點打卡的可能是請半天假的人，我用0代表正常上班，1為遲到，2為曠工。

黴黴

：我挺你說的也有條件是且，用函式什麼的啊？

小度

：其實你有沒有發現，“且”是不是像極了“×”乘號，左右運算元，1個為0，結果為0，2個同時為1均為1，為了保證左右運算元均為0或1，所以運算元均為比較表示式，這樣就能代替if的部分使用場景，比如如果a<3，返回10，否則返回5，數學表示式=（a<3）*5+5，就和if（a<3，10，5）的結果是一樣的！現在能看明白遲到扣款的計算公式了嗎？

黴黴

：有點眉目了，countifs函式是什麼意思啊？

小度

：功能可以對多組條件結果進行計數統計，必填為至少1組條件：1個為篩選的範圍，1個為條件，可以表示式，數字，文字，引用單元格等，並按條件的最終返回值進行計數

碼語為：在一個範圍內容，滿足條件的資料個數總和，語法結構如下：

countifs函式語法結構圖

你要的做的表中，需要按人名統計遲到：1，曠工：2，正常：0的次數，所以函式的第1個引數為姓名所在的列，條件為對應姓名的單元格，接著範圍遲到型別所在列，條件為1，就能統計出遲到的次數，透過調整第4個引數就能分別求出曠工次數和正常次數。

黴黴

：按照你的思路，我們要區分一下上午打卡遲到後下午打卡遲到的情況啊？

小陸

：至於做到什麼程度需要按你們主管的要求來做，就現在給你的資料和讓你做的內容，並沒有要你完成這部分的功能的意思，你先把他們做的整理一下，調整一下樣式，給主管發過再說，不過別現在發，要等快下班再發哦。

黴黴

：為什麼呢？我一會就能給她發過去啊！

小陸

：從給你的資訊來看，主管的預期是今天能完成就很好了，太早的發過去，未必就好，因為現在本來就不是你完成的，正好透過這段時間，你按照他們的步驟再做一遍，等你練好了，再給主管發也不遲，太早的發過去，主管還會給安排其他更難的任務，這樣下去，你不但沒有時間學，反而工作會越來越重，反而不利你成長。

小皮

：你這個老油條，別把你徒弟帶壞了，怎麼我們沒有吹牛吧！

小度

：哪我們這次誰更高一籌呢？你給評評！

小陸

：不用了吧，你們倆都這麼強，還有什麼好比的啊，我是自愧不如啊！

小皮

：黴黴，以後你就別跟你師傅學了，跟我們倆得了，你師傅也不行啊！

小度

：就是，行了我也該走了，我回去忙了。小皮：走了，之涵怎麼，你再待會啊！

黴黴

：我找我師傅還有別的事啦，兩位大神慢走，回頭請你們吃飯啊！

小皮酸酸的說

：別跟你師傅不學好啊，我們這外人走啦！

等兩人走遠了，

黴黴說

：師傅，他們兩的方法都挺快的，我該先學誰的啊？

小陸：

你可以先學曉皮的技巧做應急之用，簡單有效，如果你想玩通Excel，還是需要學函式，學思路，小皮的技巧只是開發者將常用好用的功能做了統一封裝整合，方便快捷使用，如果沒有原理，思路的加持，這些技巧你也很難用的流暢，而且整合的功能再強大，也不是萬能，總有需要自己設計製作的時候，不懂函式思路，你還怎麼玩啊！

黴黴：

知道了，是否，回頭那天有時間再給補補課吧，我發現我欠缺的太多了。

小陸：

好啊，不過這次我想吃飯後去看電影，你有時間嗎？

黴黴

：師傅，你是不是有事要忙，我也整理一下剛才的表，多練幾遍！然後就把陸之涵推走了。

小陸還說：我就當你答應了，時間你定一下，我安排啊。。。。。（文章完）

本故事純屬虛構，如有雷同純屬巧合！

演員/角色：陸之涵/小陸，郭知黴 /黴黴 姜度華/小度 陳曉皮/小皮