考勤機數據整理（老師示范作業(yè)已發(fā)布，歡迎學習）

2016.07.25

第1節(jié)分析的結果并不一定全部能應用，還需要實踐的檢驗。本節(jié)是學習考勤機數據的整理，大部分公司都用考勤機，因市場上考勤機的品種多種多樣，導出的數據格式也多種多樣，有的考勤機能直接達到想要的考勤數據效果，這節(jié)課可略過。

在第1節(jié)也分析了數據如何處理，但在實際處理時，需要對設想的結果進行印證：

1、考勤機數據能否通過簡單處理，得到想要的結果，如果不能，需要對其進行怎樣的處理才能達到想要的效果。提示：有的公司考勤機數據只記錄上下班時間，通過處理即可得到遲到、早退、加班時長等數據。但有的公司打一次卡就記錄一次時間，有的員工一天會打N次卡，如何提取出上班最晚打卡時間和下班最早打卡時間？

如將考勤原始數據處理成表4的格式，再進行下一步的處理。

表4

2、方法應用。如何對考勤機的原始數據進行處理，需要采用一定的方法。大部分同學會想到用函數，如果用函數，需要什么樣的函數（不一定是將函數列出來，有可能是想到要用哪一類的函數，如要用到能處理時間類的函數）。還有可能不需要函數，不需要函數的采用什么樣的方法去處理。

3、函數選擇。用函數是主流，這一步是針對使用函數的同學。如果知道使用具體的函數，直接列出即可。如果不知道使用具體函數，只知道使用某一類的函數，請同學們借助網絡、幫助文件或EXCEL頁面上“公式”選項卡的“函數庫”功能區(qū)去一一查找具體函數。

例如：不知道該如何對比兩個時間的大小，就可以借助網絡搜索，檢索內容如“EXCEL 如何對比兩個時間大小”。

4、方法選擇。這一步是針對不使用函數的同學或既使用函數又使用其它方法的同學。如果知道具體方法，直接列出即可。如果不知道具體方法，請借助網絡去查找。

5、函數或操作方法掌握。

使用函數的列出需要使用的具體函數，并將查找到的函數定義、語法、各參數的詳細解釋等列出來并掌握，同時用自己的語言去記住此函數。如IF函數，可以用自己的語言記錄為IF(條件判斷，條件為真時返回的數值，條件不為真時返回的數值)，甚至更簡化“如果為真返回XX，否則返回XX”，無論科學與否，只要能讓你記住并掌握此函數就可以。

使用其它方法的同學，請將此方法掌握，并通過幫助文件或網絡搜索，掌握這個方法的其它基本應用，如果有能力掌握它的高級應用。

6、數據處理。通過掌握的函數或方法將需要處理的數據進行處理，同時檢驗函數或方法掌握的程度。如果用函數得不到自己想要的結果，請借助網絡搜索去借鑒其他人的經驗。

本節(jié)的內容，對熟悉函數或其它操作方法的同學來說比較簡單。不熟悉的同學，請學會如何借助網絡等手段搜索到自己想要的結果，并將其掌握，只有這樣才能掌握學習的方法以及更快的掌握知識。

——————————————————————————————————————————

本課作業(yè)

依據上述課程介紹方法，說說你學到的數據處理方法和技巧，并將原始數據和處理后的數據貼出來供大家學習（保密信息請?zhí)幚恚?/span>

———————————如下是基于以上思路，我自己寫的訓練作業(yè)———————————

本節(jié)主要訓練函數的查找、基本用法的掌握。

一、原始數據是否符合設計表格要求

從第1節(jié)中設計的表格來看，考勤源數據需要經過處理后才能達到我們設計表格的要求，如下圖。

第1節(jié)也分析過，員工編號需要在員工信息表中導入；星期根據日期可以計算；遲到、早退、曠工情況根據打卡時間和次數來得出。再根據上圖，我們還可以看到，“刷卡時間”一列中，即包括刷卡日期，也包括刷卡的具體時間，而且刷卡時間是按日期和時間早晚順次往下排的，所以根據這一列我們可以想辦法將“日期”、“上班卡”、“下班卡”統(tǒng)計出來。

二、方法應用

在前面的內容中我們只分析了通過哪些方法或數據得到哪些數據，但不清楚用什么方法，通常能想到的是函數法，這是一個解決辦法，但并不是唯一的辦法。

如果沒有解決思路，我們可以借助網絡搜索自己想要的答案。在不清楚如何描述問題去搜索的時候，我們可能得需要多次搜索，逐漸的縮小搜索范圍，這就需要我們去學習如何在有限的信息中得到自己的答案，或者根據別人的方法開拓自己的思路。通過搜索來學習，我們還可以發(fā)現(xiàn)很多額外的知識或技巧來學習。這樣知識會從點到線，從線到面，逐漸掌握的全面起來。

例如“員工編號”和“姓名”對應的問題，我們不知道問題如何具體描述的，可以在百度中輸入“EXCEL 員工編號和姓名如何對應”。切記，想要用EXCEL來解決這個問題，前面最好帶上EXCEL這個詞。在搜索的答案中，我們會看到用VLOOKUP函數來解決這個問題。

又如，不知道如何根據日期得出星期，在百度中搜索“EXCEL 根據日期得出星期”，我們會查到幾種解決方法，如TEXT函數、WEEKDAY函數。

再如，“刷卡時間”列中日期和時間是在一個單元格中，如何將它們分開，通過搜索“EXCEL 日期和時間分開”，我們會得到很多方法，不再一一列出，大家可以搜索一下。

至于如何將上下排列的時間分成左右排列的，以及如何統(tǒng)計遲到、早退和曠工，不再講述，大家可以借助網絡搜索。

這一部分，基本是針對如何用網絡搜索得到自己的答案進行講解，適用于初學者，如果掌握考勤數據處理方法的，也可以再練習一次，通過搜索，我們能發(fā)現(xiàn)很多處理方法。

三、方法選擇

通過網絡搜索或自己思考，我們基本能確定使用的方法。

（一）函數法

包括：VLOOKUP、IF、TEXT、HOUR、MINUTE、SECOND、TIME、MAX、MIN、INDEX、COUNTIF、COUNTIFS、COUNT、COUNTA、WEEKDAY、DATE、YEAR、MONTH、DAY、LEFT、MID、RIGHT等函數，考勤源數據不同，設計的表格不同，需要的函數就不同。

（二）其它方法

排序、分列、數據透視表的操作等

四、函數或操作方法掌握

（一）函數

通過查詢或搜索，我們通常會得到處理方法，不要急于應用到你的數據表中，這一點一定要切記，因為那是別人的知識，我們需要先將函數或操作方法掌握，將知識轉換為自己的。學習函數建議首先通過EXCEL的幫助文件來學習，在EXCEL中，按F1，出現(xiàn)“Excel幫助”窗口，在“搜索”下拉列表中選擇“Excel幫助”，如下圖所示。然后在搜索框中輸入搜索的內容，例如“IF”，就會出現(xiàn)IF函數的幫助。

Excel幫助文件中，每個函數的語法中會有很長的英文單詞或詞組，很多人會感覺頭大，不要被這些嚇到，不要管那些單詞或詞組，只要記住每個函數中各個參數是什么概念、怎么用等，然后轉換成自己的語言，自己的語言說出來不可能很科學，但自己知道什么意思就可以。

例如VLOOKUP函數，它的語法和各參數解釋有很多，如下圖。

我們先借助下面的事例理解這些參數的意思，然后將它轉換成自己的語言，如：

VLOOKUP（查找值，查找值在第一列的數據區(qū)域，從查找值那列起對應的第幾列，精確查找還是模糊查找）。

這只是一個記憶方法，一定要轉換為自己的語言來記憶這個函數，這樣就會印象深刻，進而掌握這個函數。

對每個函數都用自己的語言和記憶方式去加深印象，函數可以不背過，首先需要記憶的是這個函數有什么作用，對這個函數有個大概印象，07及以上版本，我們輸入“=”號后，輸入一個字母，就會提示以這個字母為首字母的所有函數（隱藏函數除外，很少），而且鼠標左鍵單擊各個函數，還會出現(xiàn)這個函數的作用。如我們輸入“=V”，結果如下圖。

千萬要記住，得到的解決問題的方法只能解決你的一個問題，要想將這個方法融會貫通，就得需要自己多努力，網絡搜索不可能一次就能得到正確答案，幫助文件只解決基本的問題，我們需要借助多種方法來掌握一些知識，千萬不要怕麻煩，EXCEL操作永遠不會提高，操作方法一直會很“僵硬”，不會變通。

（二）操作方法

以“分列”來舉例。

EXCEL中，每個功能要掌握都不是太難，掌握了基本功能就能滿足我們工作的需要，有時間再去掌握那結復雜的操作，所以碰到一項操作，我們需要反復的練習這項操作，一直到掌握它。

如有的朋友的“打卡時間”是文本格式，我們通過分列功能，出現(xiàn)分列窗口后，直接點確定就可以將其轉換為時間格式，日期和時間在一個單元格中，我們需要可能需要設置分隔符號來分開，將考勤數據粘貼到一個新的工作表中，一項項去試驗，去練習，直到掌握了為止。

對于排序，相信很多人都會，一般是升序降序，當然還有自定義排序，不過在工作中應用還不是太多，這個很簡單，自己試著操作一下。

和數據透視表，好多人一聽這個頭大，感覺難，但是在處理大數據時是相當方便的一個工作，它只需要我們拖動幾下鼠標，就可以得到想要的結果。

在我們應用函數或一些操作方法的時候，有時候會出現(xiàn)自己的結果和搜索的示例的結果是不一樣的，仔細去核對，我們還能發(fā)現(xiàn)很多問題，例如公式中的絕對引用和相對引用、出現(xiàn)數值型日期或數值型時間、單元格變動后公式也會改變等等，有好多小問題會出現(xiàn)。此時的解決方法也是想辦法去查找原因，然后將如何講解這些問題的方法記下來，這樣才能夯實基礎。

五、數據處理

現(xiàn)在到了真正的數據處理階段，相信很多人在網上搜索了方法后，就急不可待在考勤數據里試驗，但這并不是一個好的學習方法，而是掌握了相關的知識后，自己思考著如何去解決這些問題。如前面提到了姓名和員工編號對照，我們搜索到用VLOOKUP函數解決后，不要拿搜索到的方法急急忙忙的去套入自己的數據表中，而是去將VLOOKUP函數掌握，然后自己根據學到的知識應用到數據表中，只有這樣，才能算是自己解決了問題，我想這樣掌握的知識會更深。

（一）導入員工編號

在考勤源數據中將“考勤號”修改為“員工編號”，在A2單元格輸入公式“=VLOOKUP(B2,員工信息表!A:B,2,0)”，其中員工信息表假定如下：

這是姓名在前，員工編號在后，但一般情況是我們將員工編號放在姓名前面。我們在學習VLOOKUP函數的時候知道它是向后查找，在不改變工作表結構的情況下，能否讓VLOOKUP向前查找？先不要否定，可以再去搜索一下。

通過搜索是有解決方法的，但對于初學者來說，這個公式比較難理解，能掌握先把它掌握，當前掌握不了，將它背下來慢慢理解。將員工姓名和員工編號兩列調換一下位置，也是很方便的，試試組合鍵<>< span="">鼠標左鍵>拖動整列。<>

<>< span=""> <>

（二）將打卡日期和打卡時間分開

通過綜合對比，我們選定用分列的方法提取日期和時間，因為這樣是最簡便的。但不要想著最簡便就一直應用它，試著用多種方法將日期和時間分開。

我們選中C列，在“數據”選項卡“數據工具”功能區(qū)選擇“分列”，將日期和時間分開。

除了分列，有沒有其它方法來解決這個問題呢？可能有朋友通過搜索或自己想出來了。

用日期函數（DATE、YEAR、MONTH、DAY）和時間函數（HOUR、MINUTE、SECOND、TIME）分別提取出來。

有的源數據還可以用LEFT、MID、RIGHT函數來提取。

（三）處理上下班打卡時間

打卡時間里面也會出現(xiàn)問題需要注意，如如何判定是上班卡還是下班卡、員工休半天假的時候如何去判定等，我們可以設定中午12點以前打卡的都為上班卡，12點以后打卡的都為下班卡，當然這個設定是根據公司日常的普遍情況而來的（我們一樣可以設置10點以前算上班卡，15點以后算下班卡，這些都是可以的，既符合實際情況又能得到正確結果）。這樣如果上午請假的，一天中會出現(xiàn)兩個下班卡，我們可以通過排序或其它方法查找來解決這些問題，我想這種情況應該還不算多，很好解決的。

我們在E2單元格輸入公式“=IF(D2<12/24,D2,"")”，在F2單元格輸入公式“=IF(D2>12/24,D2,"")”，雙擊填充，結果如下。

這樣我們會發(fā)現(xiàn)上班卡和下班卡分成兩列了，但是同一日期沒在一行，我們可以在G2單元格輸入公式“=IF(C2=C3,F3,"")”，雙擊填充，將G1輸入“下班卡”。將E:G列復制并選擇性粘貼為數值格式，刪除F列，通過對E列、F列排序，刪除E列和F列都為空白的行，再對C列進行排序，結果如下。