實(shí)用Excel技巧分享：制作一張智能的考勤表

在之前的文章《實(shí)用excel技巧分享：如何讓條形圖表更生動(dòng)形象！》中，我們了解了讓excel條形圖表更形象，與眾不同的方法。而今天我們來(lái)看看怎么制作一張智能的考勤表，快來(lái)學(xué)習(xí)學(xué)習(xí)！

如果考勤機(jī)沒(méi)那么智能需要手工輔助統(tǒng)計(jì)，如果公司還沒(méi)有使用考勤機(jī)，制作當(dāng)月的考勤表是一個(gè)復(fù)雜而又冗長(zhǎng)的過(guò)程。因此如何在有限的時(shí)間內(nèi)提高工作效率完成每月的考勤統(tǒng)計(jì)，這對(duì)人事部的MM來(lái)說(shuō)是一件大事。

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

• 簡(jiǎn)化輸入考勤項(xiàng)目

• 醒目顯示周末數(shù)據(jù)

• 能自動(dòng)匯總數(shù)據(jù)

今天老菜鳥(niǎo)教你制作一份智能考勤表，同時(shí)具備以上三點(diǎn)，快來(lái)看看吧！

在動(dòng)手制作之前，先來(lái)看看最終的效果展示：

在演示中，可以發(fā)現(xiàn)以下幾點(diǎn)：

• 標(biāo)題輸入非常簡(jiǎn)單，只需要輸入一個(gè)月份數(shù)字；

• 當(dāng)月天數(shù)會(huì)自動(dòng)變化，日期會(huì)根據(jù)月份自動(dòng)調(diào)整顯示，同時(shí)周六周日會(huì)自動(dòng)變色；

• 考勤數(shù)據(jù)使用下拉菜單完成輸入，同時(shí)有提示信息；

• 表格右側(cè)有考勤結(jié)果統(tǒng)計(jì)區(qū)，根據(jù)填寫(xiě)內(nèi)容自動(dòng)產(chǎn)生統(tǒng)計(jì)數(shù)據(jù)。

下面就來(lái)教大家如何做出這樣一個(gè)表格。

一、考勤表主體的制作

首先，做出一個(gè)最基本的表格，如圖所示：

這個(gè)表格的做法非常簡(jiǎn)單，相信大家都可以完成，接下來(lái)就是一點(diǎn)點(diǎn)的細(xì)化工作，一定要用心看哦。

將第一行進(jìn)行合并，第二行最后五個(gè)單元格，分別三個(gè)合并起來(lái)、兩個(gè)合并起來(lái)，完成后效果如圖：

在第一行的合并單元格里點(diǎn)擊右鍵，選擇“設(shè)置單元格格式”。在彈出的對(duì)話(huà)框中自定義格式為：鑫盛公司0月份考勤統(tǒng)計(jì)表。

注意，這里是數(shù)字0不是字母O。

設(shè)置完成后點(diǎn)擊確定，然后在第一行輸入一個(gè)數(shù)字看看效果吧。

是不是很神奇，其實(shí)這就是利用了自定義格式的特性。

第二行里的“當(dāng)月天數(shù)”直接輸入即可，后面填入公式：=DAY(Edate(DATE(2018,A1,1),1)-1)

簡(jiǎn)單介紹一下這個(gè)公式的含義，用到了三個(gè)日期函數(shù)：

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

• EDATE函數(shù)的格式為：EDATE（開(kāi)始日期，相隔月數(shù)），也就是根據(jù)指定日期和相隔月數(shù)得到一個(gè)新的日期，本例中的開(kāi)始日期就是當(dāng)月1日，相隔月數(shù)為1表示次月1日這個(gè)日期。

• DAY函數(shù)的格式為：DAY（日期），得到指定日期是當(dāng)月的第幾天，在本例中指定日期是次月1日前1天（EDATE函數(shù)后面有個(gè)減1），換句話(huà)說(shuō)，就是當(dāng)月的最后一天。再通過(guò)DAY函數(shù)得到當(dāng)月一共多少天。

注：日期函數(shù)往往都是幾個(gè)函數(shù)配合使用，而且對(duì)于同一個(gè)問(wèn)題，會(huì)有多種思路，有些公式的思路非常巧妙，這也是學(xué)習(xí)函數(shù)的樂(lè)趣之一。

接下來(lái)要設(shè)置的就是星期了，在C4單元格填入公式：=TEXT(DATE(2018,$A1,C3),”aaa”)，右拉即可。

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

DATE函數(shù)剛才已經(jīng)講過(guò)了，在這個(gè)公式中，年還是2018，月用的A1，因?yàn)楣揭蛴依瑸榱朔乐褂依臅r(shí)候A1發(fā)生變化，所以在列號(hào)前面加了$鎖定，日就用第三行對(duì)應(yīng)的數(shù)字表示，這樣就得到了當(dāng)月所有的日期。

TEXT函數(shù)的格式為：TEXT（要指定格式的數(shù)據(jù)，格式代碼）。這個(gè)函數(shù)算是一個(gè)比較高級(jí)的函數(shù)了，雖然結(jié)構(gòu)比較簡(jiǎn)單，但是格式代碼非常多，所以也是一個(gè)多功能函數(shù)。本例中的格式代碼為“aaa”，就是用一個(gè)字來(lái)顯示星期，有興趣的讀者可以自己試試代碼“aaaa”“ddd”和“dddd”分別是什么效果吧。

注：在使用TEXT函數(shù)的時(shí)候，格式代碼必須加引號(hào)（引號(hào)在英文狀態(tài)輸入）。

至此，表格大體上已經(jīng)完成了，有些單位可能不區(qū)分上午和下午，但有些單位是區(qū)分的，如何將每個(gè)人的一行變成兩行，難道一行一行插入么？肯定不是，這里用到了一個(gè)非常簡(jiǎn)單的小技巧，一起通過(guò)動(dòng)畫(huà)演示來(lái)看看吧：

在這個(gè)操作中，涉及到幾個(gè)技巧：批量插入空行的技巧、使用格式刷的技巧、快速填充重復(fù)內(nèi)容的技巧，希望大家能夠多練習(xí)，熟練掌握這些技巧。

至此，這個(gè)考勤表的主題已經(jīng)做好了，效果是這樣的：

二、使用條件格式和數(shù)據(jù)有效性加工表格

接下來(lái)需要完成兩項(xiàng)工作：

• 讓表格中的日期根據(jù)實(shí)際情況顯示，同時(shí)周六周日會(huì)自動(dòng)變色；

• 考勤數(shù)據(jù)使用下拉菜單完成輸入，同時(shí)有提示信息。

來(lái)看看如何實(shí)現(xiàn)這些效果。對(duì)于第一項(xiàng)內(nèi)容，要用到條件格式這個(gè)功能，步驟雖然不多，但是很多細(xì)節(jié)地方，大家慢慢跟著來(lái)：

（1）選擇數(shù)據(jù)區(qū)域，點(diǎn)擊【條件格式】-【新建規(guī)則】：

進(jìn)一步選擇【使用公式確定要設(shè)置格式的單元格】，輸入公式：=C$3>$AF$2，然后點(diǎn)擊【格式】按鈕：

公式解析：很簡(jiǎn)單，就是比較第三行的每個(gè)日期是否大于當(dāng)月天數(shù)，如果大的話(huà)，按照設(shè)置的格式執(zhí)行，這一步要注意$的位置。

選擇【自定義】，在類(lèi)型中輸入三個(gè)分號(hào)：;;;，注意是英文狀態(tài)下的分號(hào)，完成后點(diǎn)確定。

格式代碼解釋?zhuān)壕唧w含義比較復(fù)雜，記住輸入三個(gè)分號(hào)代表不顯示單元格的內(nèi)容。

再點(diǎn)確定，這時(shí)候可以發(fā)現(xiàn)，不屬于當(dāng)月日期的數(shù)據(jù)已經(jīng)看不到了：

進(jìn)一步設(shè)置周六周日自動(dòng)變色，還是選擇數(shù)據(jù)區(qū)域，新建規(guī)則，輸入公式=OR(C$4=”六”,C$4=”日”)后設(shè)置格式：

這個(gè)公式也很好理解，OR為滿(mǎn)足兩個(gè)條件中任意一個(gè)就按設(shè)置的格式執(zhí)行，也就是第四行為“六”或“日”，就顯示指定的顏色。

點(diǎn)擊設(shè)置單元格格式按鈕后，選擇【填充】，選擇一個(gè)顏色，點(diǎn)確定。

再次點(diǎn)擊確定即可看到效果：

咦，后面空的為什么也涂色了，是哪里出錯(cuò)了嗎？

仔細(xì)看看，發(fā)現(xiàn)這個(gè)地方正好是周六，只不過(guò)被前面設(shè)置的格式隱藏了，但是涂色的時(shí)候還是生效的，怎么處理這個(gè)問(wèn)題呢？很簡(jiǎn)單，選擇數(shù)據(jù)區(qū)域后打開(kāi)條件格式的管理規(guī)則：

在下面一條規(guī)則后打鉤勾選“如果為真則停止”，然后點(diǎn)擊上移按鈕，點(diǎn)擊確定。

看到效果了吧。

下面要對(duì)考勤數(shù)據(jù)進(jìn)行有效性設(shè)置，在進(jìn)行這一步之前，讓我們先來(lái)完成統(tǒng)計(jì)區(qū)域的制作。

三、統(tǒng)計(jì)區(qū)域的制作

這一塊的制作就很簡(jiǎn)單了，根據(jù)自己的實(shí)際需要，添加備注一欄，填寫(xiě)考勤項(xiàng)目和圖例，在每個(gè)圖例下面使用COUNTIF函數(shù)完成統(tǒng)計(jì)，公式為=COUNTIF($C5:$AG6,AI$4)/2：

COUNTIF函數(shù)的格式為：COUNTIF（統(tǒng)計(jì)區(qū)域，要統(tǒng)計(jì)的內(nèi)容），通俗的說(shuō)，就是每個(gè)人對(duì)應(yīng)的兩行（統(tǒng)計(jì)區(qū)域）中每個(gè)考勤項(xiàng)目（要統(tǒng)計(jì)的內(nèi)容）出現(xiàn)的次數(shù)，因?yàn)槔又忻刻斓臄?shù)據(jù)是兩行，所以統(tǒng)計(jì)結(jié)果要除以2。

至于這些合并單元格的處理非常簡(jiǎn)單，合并一個(gè)再用格式刷去刷就好了。

現(xiàn)在回過(guò)頭來(lái)設(shè)置考勤數(shù)據(jù)的下拉菜單。設(shè)置有效性之前，先做一個(gè)準(zhǔn)備工作，將圖例以及說(shuō)明復(fù)制出來(lái)備用，然后再去設(shè)置有效性。這部分操作看動(dòng)畫(huà)演示吧：

至此，這個(gè)考勤表就完成了。相信通過(guò)今天的內(nèi)容，你一定有很多收獲吧！

相關(guān)學(xué)習(xí)推薦：實(shí)用excel技巧分享：如何讓條形圖表更生動(dòng)形象！