實用Excel技巧分享：製作一張智慧的考勤表

在之前的文章《實用Excel技巧分享：如何讓長條圖更生動形象！在 》中，我們了解了讓excel長條圖更形象，與眾不同的方法。而今天我們來看看怎麼製作一張智慧的考勤表，快來學習學習！

如果考勤機沒那麼智慧需要手動輔助統計，如果公司還沒有使用考勤機，製作當月的考勤表是一個複雜而又冗長的過程。因此如何在有限的時間內提高工作效率完成每月的考勤統計，這對人事部的MM來說是一件大事。

要提高效率就必須有一張比較好的考勤表。這張考勤表至少要具備以下幾點：

• #簡化輸入考勤項目

• 醒目顯示週末資料

• 能自動彙總資料

今天老菜鳥教你製作一份智慧考勤表，同時具備以上三點，快來看看吧！

在動手製作之前，先來看看最終的效果展示：

在示範中，可以發現以下幾點：

• #標題輸入非常簡單，只需要輸入一個月份數字；

• 當月天數會自動變化，日期會根據月份自動調整顯示，同時週六週日會自動變色；

• ##考勤資料使用下拉式選單完成輸入，同時有提示訊息；

• 表格右邊有考勤結果統計區，根據填寫內容自動產生統計資料。

下面就來教大家如何做出這樣一個表格。

一、考勤表主體的製作

#首先，做出一個最基本的表格，如圖所示：

這個表格的做法非常簡單，相信大家都可以完成，接下來就是一點點的細化工作，一定要用心看哦。

將第一行合併，第二行最後五個儲存格，分別三個合併起來、兩個合併起來，完成後效果如圖：

在第一行的合併單元格里點選右鍵，選擇「設定儲存格格式」。在彈出的對話方塊中自訂格式為：鑫盛公司0月份考勤統計表。

注意，這裡是數字0不是字母O。

設定完成後點選確定，然後在第一行輸入一個數字看看效果吧。

是不是很神奇，其實這就是利用了自訂格式的特性。

第二行裡的「當月天數」直接輸入即可，後面填入公式：=DAY(EDATE(DATE(2018,A1,1),1)-1)

#簡單介紹這個公式的意義，用到了三個日期函數：

• #DATE函數的格式為：DATE(年、月、日) ，也就是根據指定的年月日得到一個日期，在本例中，年份為2018（如果是下一年度的話，改為2019即可），月份取的是第一行輸入的數字，日就取1，這樣得到的日期就是考勤表當月的1號。

• EDATE函數的格式為：EDATE（開始日期，相隔月數），也就是根據指定日期和相隔月數得到一個新的日期，本例中的開始日期就是當月1日，相隔月數為1表示次月1日這個日期。

• DAY函數的格式為：DAY（日期），得到指定日期是當月的第幾天，在本例中指定日期是次月1日前1天（EDATE函數後面有個減1），換句話說，就是當月的最後一天。再透過DAY函數得到當月一共多少天。

註：日期函數往往都是幾個函數配合使用，而且對於同一個問題，會有多種思路，有些公式的思路非常巧妙，這也是學習函數的樂趣之一。

接下來要設定的就是星期了，在C4儲存格填入公式：=TEXT(DATE(2018,$A1,C3),"aaa")，右拉即可。

公式=TEXT(DATE(2018,$A$1,C$3),"aaa")中用到了兩個函數TEXT和DATE，分別解釋一下這兩個函數的作用：

DATE函數剛才已經講過了，在這個公式中，年還是2018，月用的A1，因為公式要向右拉，為了防止右拉的時候A1發生變化，所以在列號前面加了$鎖定，日就用第三行對應的數字表示，這樣就得到了當月所有的日期。

TEXT函數的格式為：TEXT（要指定格式的數據，格式代碼）。這個函數算是比較高階的函數了，雖然結構比較簡單，但是格式程式碼非常多，所以也是一個多功能函數。本例中的格式代碼為“aaa”，就是用一個字來顯示星期，有興趣的讀者可以自己試試代碼“aaaa”“ddd”和“dddd”分別是什麼效果吧。

附註：使用TEXT函數的時候，格式代碼必須加引號（引號在英文狀態輸入）。

至此，表格大體上已經完成了，有些單位可能不區分上午和下午，但有些單位是區分的，如何將每個人的一行變成兩行，難道一行一行插入麼？肯定不是，這裡用到了一個非常簡單的小技巧，一起透過動畫演示來看看：

#在這個操作中，涉及到幾個技巧：批量插入空行的技巧、使用格式刷的技巧、快速填入重複內容的技巧，希望大家能多練習，熟練這些技巧。

至此，這個考勤表的主題已經做好了，效果是這樣的：

#二、使用條件格式和資料有效性加工表格

接下來需要完成兩項工作：

• #讓表格中的日期根據實際情況顯示，同時週六週日會自動變色；

• 考勤資料使用下拉式選單完成輸入，同時有提示資訊。

來看看如何實現這些效果。對於第一項內容，要用到條件格式這個功能，步驟雖然不多，但是很多細節地方，大家慢慢跟著來：

（1）選擇資料區域，點選【條件格式】-【新規則】：

進一步選擇【使用公式決定要設定格式的儲存格】，輸入公式：=C$3>$AF$2 ，然後點選【格式】按鈕：

公式解析：很簡單，就是比較第三行的每個日期是否大於當月天數，如果大的話，按照設定的格式執行，這一步要注意$的位置。

選擇【自訂】，在類型中輸入三個分號：;;;，注意是英文狀態下的分號，完成後點確定。

格式程式碼解釋：具體意義比較複雜，記住輸入三個分號代表不顯示儲存格的內容。

再點確定，這時候可以發現，不屬於當月日期的資料已經看不到了：

進一步設定週六週日自動變色，還是選擇資料區域，新規則，輸入公式=OR(C$4="六",C$4="日")後設定格式：

這個公式也很好理解，OR為滿足兩個條件中任意一個就按設定的格式執行，也就是第四行為“六”或“日”，就顯示指定的顏色。

點選設定儲存格格式按鈕後，選擇【填滿】，選擇一個顏色，點選確定。

再次點擊確定即可看到效果：

#咦，後面空的為什麼也塗色了，是哪裡出錯了嗎？

仔細看看，發現這個地方剛好是星期六，只不過被前面設定的格式隱藏了，但是塗色的時候還是生效的，怎麼處理這個問題呢？很簡單，選擇資料區域後打開條件格式的管理規則：

在下面一條規則後打鉤勾選“如果為真則停止”，然後點擊上移按鈕，點選確定。

看到效果了。

下面要對考勤資料進行有效性設置，在進行這一步之前，讓我們先來完成統計區域的製作。

三、統計區域的製作

這一塊的製作就很簡單了，根據自己的實際需要，加上備註一欄，填寫考勤項目和圖例，在每個圖例下方使用COUNTIF函數完成統計，公式為=COUNTIF($C5:$AG6,AI$4)/2：

#COUNTIF函數的格式為：COUNTIF（統計區域，要統計的內容），通俗的說，就是每個人對應的兩行（統計區域）中每個考勤項目（要統計的內容）出現的次數，因為範例中每天的資料是兩行，所以統計結果要除以2。

至於這些合併儲存格的處理非常簡單，合併一個再用格式刷去刷就好了。

現在回過頭來設定考勤資料的下拉式選單。在設定有效性之前，先做一個準備工作，將圖例以及說明複製出來備用，然後再去設定有效性。這部分操作看動畫示範吧：

至此，這個考勤表就完成了。相信透過今天的內容，你一定有很多收穫吧！

相關學習推薦：excel教學

以上是實用Excel技巧分享：製作一張智慧的考勤表的詳細內容。更多資訊請關注PHP中文網其他相關文章！