考勤表是辦公中經常用到的表格，不管是小公司還是大企業，隻要有員工，基本均會涉及考勤。而考勤表則記錄了員工每個月的考勤情況，既是員工每天上班的憑證，也是員工領取工資的憑證。

考勤表基本上每個月都需要制作，為了減少考勤表制作工序，提高工作效率，很多企業或個人都會先制作考勤表模闆，然後每月根據考勤表模闆填寫相關的考勤表數據即可。

既然考勤表模闆這麼重要，那麼，一個規範、深得領導喜愛的考勤表模闆，應該是怎樣的呢？

• 表頭日期和星期根據年份、月份的變化自動更新。
• 周六、周日會自動标注顔色，這樣能更好地區分上班日和周末節假日。
• 所有單元格裡面收集的數據都可以通過下拉菜單填充。

考勤表模闆的具體制作方法如下：

在Excel表格中搭建好考勤表框架，然後在表頭部分設置年份和月份的可選擇區域，通過公式自動創建不同年份、月份的考勤表，具體操作步驟如下。

Step 01 根據考勤表内容搭建好考勤表框架，如下圖所示。

Step 02 輸入輔助數據。在當前工作表中估計不會用到的空白區域中輸入多個年份（本例中輸入年份為2015～2025）及月份數據作為輔助列，方便後面調用，如下圖所示。

Step 03 設置年份序列。選擇C1單元格，單擊【數據】選項卡【數據工具】組中的【數據驗證】按鈕，打開【數據驗證】對話框，在【設置】選項卡的【允許】下拉列表中選擇【序列】選項，在【來源】參數框中通過引用設置該單元格數據允許輸入的序列，這裡引用表格中輸入年份的單元格區域，單擊【确定】按鈕，如下圖所示。

Step 04 設置月份序列。選擇E1單元格，打開【數據驗證】對話框，在【設置】選項卡的【允許】下拉列表中選擇【序列】選項，在【來源】參數框中通過引用設置該單元格數據允許輸入的序列，這裡引用表格中輸入月份的單元格區域，單擊【确定】按鈕，如下圖所示。

Step 05 計算應出勤天數。選擇P1單元格，輸入公式【=NETWORKDAYS (DATE($C$1,$E$1,1),EOMONTH(DATE($C$1,$E$1,1),0))】，計算出所選月份應該出勤的總天數，如下圖所示。

技術看闆

本例中采用 NETWORKDAYS函數計算所選月份應該出勤的總天數時，用了DATE函數将C1、E1單元格和數字1轉換為日期數據，作為統計工作日的開始日期；用EOMONTH函數讓轉換為日期的 DATE($C$1,$E$1,1)數據返回當月的最後一天，作為統計工作日的結束日期。

Step 06 計算得出表格标題。在C1和E1單元格中分别選擇一個年份和月份，即可在P1單元格中查看到根據剛剛輸入公式計算出的當月考勤天數。選擇A2單元格，輸入公式【=TEXT(DATE (C1,E1,1),"e年M月份考勤表")】，即可在A2單元格中根據C1、E1單元格中選擇的年份和月份自動顯示當前工作表的名稱，如下圖所示。

Step 07 計算日期序号。選擇D4單元格，輸入公式【=IF(MONTH(DATE ($C$1,$E$1,COLUMN(A1)))=$E$1,DATE ($C$1,$E$1,COLUMN(A1)),"")】，返回當前選擇的年份和月份的第一天對應的日期序号，右擊D4單元格，在彈出的快捷菜單中選擇【設置單元格格式】命令，如下圖所示。

技術看闆

公式首先用DATE函數将C1、E1單元格和通過COLUMN(A1)提取的A1單元格的列号，轉換為日期數據，然後用MONTH函數提取這個組合日期的月份數，讓得到的結果與E1單元格的月份數進行比較，如果等于，就返回DATE($C$1,$E$1, COLUMN(A1))，否則返回空值。這樣，後面通過複制公式，公式中的 COLUMN(A1) 就會自動進行相對位置的改變，從而實現依次返回指定月份的日期數。

Step 08 設置日期格式。打開【設置單元格格式】對話框，在【數字】選項卡的【分類】列表框中選擇【自定義】選項，在【類型】文本框中輸入【d】，單擊【确定】按鈕，如下圖所示。

Step 09 複制公式。經過上步操作，即可讓D4單元格中的日期數據僅顯示為日。向右拖動填充控制柄，複制公式到E4:AH4單元格區域，返回當前選擇的年份和月份的其他天對應的日期序号，如下圖所示。

Step 10 計算日期對應的星期。選擇 D5單元格，輸入與 D4 單元格中相同的公式【=IF(MONTH(DATE($C$1,$E$1, COLUMN(A1)))=$E$1,DATE($C$1,$E$1,COLUMN(A1)),"")】，按【Enter】鍵計算出結果，如下圖所示。

Step 11 複制公式。将D5單元格中的數字格式自定義為"aaa"，即可讓D5單元格中的日期數據僅顯示為星期中的序号。向右拖動填充控制柄，複制公式到 E5:AH5 單元格區域，即可返回當前選擇的年份和月份的其他天對應的星期序号，如下圖所示。

2．突出周六、周日

表格中每個月的記錄數據密密麻麻，不便查看，加上該公司的星期六和星期日都不上班，所以可以對表格中的星期六和星期日數據突出顯示，以區分出不同星期的數據。

Step 01 選擇菜單命令。選擇 D4:AH5單元格區域，單擊【開始選項卡】【樣式】組中的【條件格式】按鈕，在彈出的下拉菜單中選擇【新建規則】命令，如下圖所示。

Step 02 設置條件格式。打開【新建格式規則】對話框，在【選擇規則類型】列表框中選擇【使用公式确定要設置格式的單元格】選項，在【為符合此公式的值設置格式】參數框中輸入公式【=WEEKDAY(D4,2)=6】，單擊【格式】按鈕，如下圖所示。

Step 03 設置單元格填充效果。打開【設置單元格格式】對話框，選擇【字體】選項卡，在【字形】列表框中選擇【加粗】選項，在【顔色】下拉列表中選擇【白色】選項，再選擇【填充】選項卡，在列表框中選擇需要填充的綠色，單擊【确定】按鈕，如下圖所示。

Step 04 突出顯示周末。返回【新建格式規則】對話框，單擊【确定】按鈕，返回工作表，即可看到已經為所選區域中的星期六數據設置了綠色填充色。使用相同的方法突出顯示星期天。為保證表格中的自動數據無誤，可以重新設置 C1、E1 單元格中的年份和月份，并驗證指定年月應出勤天數、表格标題、當月所有日期及對應的星期數是否出錯，如下圖所示。

3．規範考勤數據

考勤數據有多種表達形式，為規範輸入的考勤數據，本例中假定一些規則，并通過設置考勤數據區的數據有效性使考勤表的制作更加便利。同時設置了凍結窗格，方便後期填寫和

查閱數據明細，最後将作為輔助列的數據進行隐藏，讓整個表格更加規範。

設置規範的考勤數據，具體操作步驟如下。

Step 01 設置數據驗證。重命名工作表名稱為【考勤表模闆】，為表格區域添加相應的邊框，選擇D6:AH50單元格區域，打開【數據驗證】對話框，A在【設置】選項卡的【允許】下拉列表中選擇【序列】選項，在【來源】參數框中輸入【√,事,病,差,年,婚,遲1,遲2,遲3,曠】，單擊【确定】按鈕，如下圖所示。

技術看闆

本例假定全勤用【√】作為标記，事假用【事】作為标記，病假用【病】作為标記，出差用【差】作為标記，年假用【年】作為标記，婚假用【婚】作為标記，遲到 10 分鐘以内用【遲 1】作為标記，遲到半小時以内用【遲 2】作為标記，遲到 1 小時以内用【遲 3】作為标記，曠工用【曠】作為标記。這個考勤符号可以根據公司要求進行設置。

Step 02 凍結表格行和列。考勤表中的數據很多，為便于查看，可以将有用信息固定在窗口中。當窗口大小不變時，讓不必要的細節數據可以随着拖動滾動條來選擇顯示的部分。本例需要固定表格的上面 5 行和左側 3 列數據，選擇 D6 單元格，單擊【視圖】選項卡【窗口】組中的【凍結窗格】按鈕 ，在彈出的下拉列表中選擇【凍結窗格】選項，即可凍結所選單元格前面的多行和多列，完成考勤表模闆的制作，如下圖所示。

,

更多精彩资讯请关注tft每日頭條，我们将持续为您更新最新资讯!

查看全部