|
考勤機(jī)數(shù)據(jù)和請(qǐng)休假情況數(shù)據(jù)處理完成后，需要對(duì)兩種數(shù)據(jù)進(jìn)行匯總，本節(jié)中主要是學(xué)習(xí)如何將兩種數(shù)據(jù)匯總。如果前3節(jié)考慮的周全，表格設(shè)計(jì)的比較好，這節(jié)訓(xùn)練還是比較輕松的，否則還需要去修改前面的數(shù)據(jù)及表格。
1、第2節(jié)和第3節(jié)的數(shù)據(jù)處理完成后，如何將數(shù)據(jù)導(dǎo)入預(yù)想的表格結(jié)構(gòu)中？檢驗(yàn)一下是否能達(dá)到，如果不能達(dá)到，需要再借助什么樣的表格來達(dá)到？
2、預(yù)想的表格結(jié)構(gòu)是如何的，請(qǐng)將完善后的表格列出？ 如表6是預(yù)想的表格最終效果。
表6 3、如何將兩種數(shù)據(jù)匯總起來？需要借助什么樣的方法？如果不知道可以借助網(wǎng)絡(luò)搜索等方法。
4、如果借助函數(shù)，同前2節(jié)的訓(xùn)練一下，請(qǐng)借助網(wǎng)絡(luò)、幫助文件或EXCEL頁面上“公式”選項(xiàng)卡的“函數(shù)庫”功能區(qū)去一一查找具體函數(shù)，并將函數(shù)定義、語法、各參數(shù)的詳細(xì)解釋等列出來并掌握。
5、如果借助其它方法，請(qǐng)掌握此方法及其它基礎(chǔ)操作方法。
本課作業(yè)
———————————如下是基于以上思路，我自己寫的訓(xùn)練作業(yè)——————————— 本節(jié)主要訓(xùn)練考勤機(jī)數(shù)據(jù)和請(qǐng)休假的情況匯總到一起生成最終的考勤表，難點(diǎn)是如何實(shí)現(xiàn)表格的自動(dòng)匯總。
通過第2節(jié)考勤機(jī)源數(shù)據(jù)的處理和第3節(jié)各種請(qǐng)休假數(shù)據(jù)的處理，如果這些數(shù)據(jù)僅給工資表提供數(shù)據(jù)支持，那么通過簡(jiǎn)單匯總就可以實(shí)現(xiàn)目的，但處理考勤數(shù)據(jù)并不僅僅是為了做工資用，還有其它作用，需要我們將考勤機(jī)打卡數(shù)據(jù)和請(qǐng)休假的情況匯總起來才可以。
現(xiàn)在回過頭來看，我們?cè)诘?/span>1節(jié)的時(shí)候計(jì)劃將考勤表設(shè)計(jì)成如下格式。
在這個(gè)表格中，我們?cè)谇懊鎺坠?jié)中解決了員工編號(hào)的問題、各種請(qǐng)休假的問題、遲到早退的問題等，考勤記錄除了正常出勤，其它的在前面幾節(jié)中都有體現(xiàn)?，F(xiàn)在唯一沒有解決的是正常出勤按什么記錄？我們可以用“出勤”來記錄，也可以什么也不記錄即表示出勤等，結(jié)合員工打卡的情況，我們會(huì)發(fā)現(xiàn)如果員工全天沒打卡也沒有各種請(qǐng)休假的記錄的話，我們?cè)谇懊鎺坠?jié)中都沒有進(jìn)行判斷，所以建議如果正常出勤的話，用打卡次數(shù)來表示。原因如下：
1、打卡次數(shù)足夠后，就代表正常出勤，否則就是考勤異常（如曠工）。我們?cè)谇懊嬉惨恢痹谡f，讓EXCEL實(shí)現(xiàn)自動(dòng)運(yùn)算匯總，那么通過打卡次數(shù)來判斷的話，公式設(shè)置比較方便。
2、打卡次數(shù)不足的，并不一定是員工未打卡，有可能是考勤機(jī)的原因，也有可能是員工因公出差或外出沒來得及打卡等，這種異常情況還需要二次處理，在上圖的考勤表中查看不足打卡次數(shù)的人員，再對(duì)比相關(guān)單據(jù)進(jìn)行修改。
至此，我們會(huì)有一個(gè)判斷，之前設(shè)計(jì)的表格中還是有缺陷的，如下圖是第2節(jié)中的圖，我們統(tǒng)計(jì)曠工是按打卡為1次的進(jìn)行統(tǒng)計(jì)，但一天未打卡的我們是根本沒辦法去判斷，通過第3節(jié)統(tǒng)計(jì)各種請(qǐng)休假情況的公式，我們可以判斷：打卡不足2次的我們?cè)谝胱罱K考勤表時(shí)，通過打卡次數(shù)用IF函數(shù)來判斷。這也是我們?cè)谠O(shè)計(jì)表格時(shí)，先前考慮的和實(shí)踐的不同之處。
所以我們將上圖中的“曠工”列可以更改為“打卡次數(shù)”，或者直接增加一列標(biāo)題為“打卡次數(shù)”，如下圖，我們?cè)?/span>I2單元格中輸入公式“=COUNT(E2:F2)”，雙擊填充公式。 
這樣我們手里會(huì)有兩份最終表格，一份是上圖，一份是請(qǐng)休假情況統(tǒng)計(jì)的表格。這兩張表格如何匯總呢？我們經(jīng)過判斷會(huì)得出，當(dāng)天有請(qǐng)休假情況的應(yīng)該優(yōu)先記錄請(qǐng)休假的情況，打卡次數(shù)我們要忽略；當(dāng)天沒有請(qǐng)休假情況的，我們?cè)倥袛?，如果打卡次?shù)為2次的，直接顯示2，如果不足2次的，顯示曠工。 現(xiàn)在我們?nèi)绾紊勺罱K表格呢？在第3節(jié)中已經(jīng)有公式了，用VLOOKUP函數(shù)。 STEP 1：我們將員工編號(hào)和姓名粘貼到最終考勤表中。 STEP 2：假定考勤機(jī)數(shù)據(jù)保存的工作表名為“第4節(jié)-1”，請(qǐng)休假情況保存的工作表名為“第3節(jié)-5”，在C2單元格輸入公式“=IF(WEEKDAY(DATE(2015,7,C$1),2)>5,"",IF('第3節(jié)-5'!C2<>"",'第3節(jié)-5'!C2,IF(IFERROR(VLOOKUP($A2&C$1,IF({1,0},'第4節(jié)-1'!$A$2:$A$24&DAY('第4節(jié)-1'!$C$2:$C$24),'第4節(jié)-1'!$I$2:$I$24),2,0),0)<2,"曠",2)))”，右拉下拉。 STEP 3：用COUNTIF函數(shù)統(tǒng)計(jì)各種請(qǐng)休假的情況。 STEP 4：用SUMIF函數(shù)在處理過的考勤機(jī)數(shù)據(jù)里面引用遲到、早退的數(shù)據(jù)。
最終表格效果如下：
這樣最終的考勤表算是初步成功了，在這里講解的只是一個(gè)思路，還有很多問題值得我們?nèi)タ紤]，思路請(qǐng)?jiān)?/span>1-3節(jié)和本節(jié)中找。
1、我們?cè)谇懊嬷v解到了，每天的考勤情況中有遲到早退的情況，但在本節(jié)講解中，遲到早退的情況沒有加入。 2、如果一天有多種考勤情況的，如何匯總到最終考勤表中，如有遲到、早退、請(qǐng)休假、曠工等情況。 3、如果每天有請(qǐng)小時(shí)假或者半天假的，如何處理。 4、如果有調(diào)休的情況如何處理。 5、有加班情況的如何記錄，如何統(tǒng)計(jì)，如果同時(shí)存在加班和調(diào)休，如何處理。 6、如果有法定節(jié)假日，如何處理。 ………… 這些問題都是需要結(jié)合公司情況來考慮的，不在一一解決，但所有的解決思路都在可以在本節(jié)和1-3節(jié)中尋找，處理方法幾乎大同小異，函數(shù)應(yīng)用也是類似的，不再占用篇幅來寫。
公式講解 =IF(WEEKDAY(DATE(2015,7,C$1),2)>5,"",IF('第3節(jié)-5'!C2<>"",'第3節(jié)-5'!C2,IF(IFERROR(VLOOKUP($A2&C$1,IF({1,0},'第4節(jié)-1'!$A$2:$A$24&DAY('第4節(jié)-1'!$C$2:$C$24),'第4節(jié)-1'!$I$2:$I$24),2,0),0)<2,"曠",2)
這個(gè)函數(shù)是嵌套函數(shù)，“IFERROR(VLOOKUP($A2&C$1,IF({1,0},'第4節(jié)-1'!$A$2:$A$24&DAY('第4節(jié)-1'!$C$2:$C$24),'第4節(jié)-1'!$I$2:$I$24),2,0),0)”不再解釋，請(qǐng)看第3節(jié)內(nèi)容。
整個(gè)函數(shù)來看，通過用IF和WEEKDAY函數(shù)判斷是否屬于周六日，如果屬于單元格返回空值。通過IF函數(shù)判斷請(qǐng)休假情況統(tǒng)計(jì)表中相對(duì)應(yīng)的單元格是否是空值，如果不是，返回單元格的值（肯定是各種請(qǐng)休假情況）。通過IF函數(shù)判斷打卡次數(shù)是否小于2，如果小于則曠工，否則返回2（代表出勤）。
|
|
|
