編按：在上一期的文章《由打卡記錄一鍵生成考勤匯總表，加班遲到早退全統(tǒng)計好！（上）》中，主要介紹了如何對打卡記錄表的數據進行整理。在這一期中，我們介紹如何在匯總表模板中添加公式、如何運用模板，以便實現自動匯總。

哈嘍，大家好。

今天繼續(xù)來給大家講解：如何在匯總表模板中添加公式、運用模板，最后實現自動匯總。

一、匯總表設置

Step1：引用打卡表中歸類好的數據

打開“11月匯總表.xlsm”，找到“匯總表”工作表。

從D5開始選擇D列，設置格式為“貨幣”。

在D5中輸入公式“=IFERROR(INDIRECT(D$2&"!J"&ROW(A2)),"")”并向下填充。

公式解析：

用“&”將D2單元格、嘆號！、字母J和ROW（A2）連接起來，形成“20221001!J2”字符，作為INDIRECT函數的引用地址，實現對“20221001”工作表J2單元格數據的引用。公式中ROW（A2）函數下拉后會自動變?yōu)?span>ROW（A3）、ROW（A4）……，可以依次引用“20221001”工作表J3、J4……單元格的數據。

看到這里，可以明白為何上篇文章提醒匯總表中第二行的日期盡可能與導出的每日打卡記錄工作表名稱一致的原因——方便通過匯總表日期生成各表引用地址。

在E5單元格輸入公式“=IFERROR(INDIRECT(E$2&"!K"&ROW(A2)),"")”；

在F5單元格輸入公式“=IFERROR(INDIRECT(F$2&"!L"&ROW(B2)),"")”；

在G5單元格輸入公式“=IFERROR(INDIRECT(G$2&"!M"&ROW(E2)),"")”；

都下拉填充，實現對“20221101”工作表的K、L、M列數據的引用。

再選中D5:G28區(qū)域，向右進行填充，依次完成對“20221102”、“20221103”……工作表的數據引用。

Step2： 計算員工當月扣款/加班數據

從C5單元格開始向下選中C列，設置單元格格式為“貨幣”。

在C5單元格輸入公式“=SUMIF($D$4:$DW$4,$D$4,D5:DW5)”，然后下拉填充完成統(tǒng)計。

至此，完成11月考勤數據的匯總。

由于遲到/早退、加班數據敏感性高，所以有需要的話，可以使用條件格式使其突出顯示。

Step3： 突出顯示遲到/早退、加班數據（非必須）

選中E5單元格新建兩則條件格式。

在“新建格式規(guī)則”對話框中，“選擇規(guī)則類型”均選擇“只為包含以下內容的單元格設置格式”，然后在“編輯規(guī)則說明”中選擇“特定文本”和“包含”，并分別輸入“加班”“遲到”字樣。最后單擊“格式”設置不同的填充顏色。

第一則，針對加班的：

第二則，針對遲到早退的：

用格式刷將E5單元格的條件格式應用到其他單元格中。最終效果如下。

保存文檔（但不要關閉）。

二、繼續(xù)完成匯總表模板

Step1： 刪除多余的打卡記錄表

刪除“11月匯總表.xlsm”文檔中除“匯總表”“整理模板表”外的所有工作表。

Step2： 覆蓋前方保存的匯總表模板文件

執(zhí)行“文件→另存為”菜單命令，在“另存為”對話框中選擇前方保存的“匯總表模板.xlsm”文件，然后單擊“保存”按鈕進行覆蓋。

匯總表模板文件制作完成。

補充說明：

在實際工作中，考勤匯總表還會涉及到出差、曠工、請假等沒有打卡的數據。這些只要根據實際情況，在匯總表上增加列項目手動添加數據即可。

三、匯總表模板的使用

模板建立好了，以后就可以利用模板快速匯總各月的考勤數據了。

以2022年12月考勤為例。

第一步：打開“匯總表模板.xlsm”和“202212.xlsx”工作簿。將“匯總表模板.xlsm”的“匯總表”和“整理模板表”添加到“202212.xlsx”工作簿，并確保它們分別排在第1、第2位。

第二步：修改“202212.xlsx”中“匯總表”A1單元格標題，把“11月”改成“12月”。

第三步：根據實際情況增刪、修改“匯總表”中的員工姓名和編號。

第四步：切換到“匯總表模板.xlsm”窗口，按下Alt+F11打開VBA編輯器，復制右側的VBA代碼。

復制后，關閉“匯總表模板.xlsm”文件。

第五步：在左側的工程對話框單擊“202212.xlsx”，然后執(zhí)行“插入→模塊”命令，添加“模塊1”，并在右側粘貼代碼。修改代碼中的文件名稱，將“11月匯總表.xlsm”改成“12月匯總表.xlsm”。

第六步：另存“202212.xlsx”文檔，選擇文件格式為“Excel啟用宏的工作簿”，設置名稱為“12月匯總表.xlsm”。該名稱與上一步驟代碼中的名稱保持一致。

第七步：單擊“開發(fā)工具”菜單下“宏”按鈕，在彈出的“宏”對話框中選擇“遍歷工作表”，單擊“執(zhí)行”。

Ok，大功告成！保存文檔完成12月考勤匯總。

怎么樣？利用模板，只需7步操作，一兩分鐘搞定所有匯總。

您是不是也正在為匯總考勤表而煩惱？那么就好好學習一下上面的教程吧。用好Excel，讓我們一起對加班說“不”！

本文配套的練習課件請加入QQ群：902294808下載。

做Excel高手，快速提升工作效率，部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你！

掃下方二維碼關注公眾號，可隨時隨地學習Excel：

相關推薦：

別怕，VBA入門級教程來了，條件語句很簡單！

優(yōu)秀員工組別查找？INDEX、OFFSET、LOOKUP……我有100個函數可以解決這個問題

Excel教程：如何制作帶有層次和透視感的圖表？

八大查找函數公式，輕松搞定數據中的多條件查找

版權申明：

本文作者ITfans；同時部落窩教育享有專有使用權。若需轉載請聯系部落窩教育。