考勤監(jiān)控是每個(gè)公司或單位都非常關(guān)心的事情,。下面就來講解一下如何用excel制作專業(yè)考勤表。

1.打開一個(gè)空白的EXCEL工作表,，如下圖所示進(jìn)行繪制,。

圖中M1:P1為填寫“年”的合并單元格，S1:T1為填寫“月”的合并單元格,，設(shè)置為淺藍(lán)色底紋進(jìn)行醒目,。

2.為了在第二行自動(dòng)顯示“周”,需要如下設(shè)置公式：

在D2單元格中輸入公式=if (WEEKDAY(DATE($M$1，$S$1,，D3)，2)=7,，' DAY 'WEEKDAY(DATE($M$1,，S$1，D3),，2)),。

這時(shí)，您可以看到在D2單元格中出現(xiàn)了一個(gè)代表星期的單詞“day”(這意味著2011年5月1日是星期天),。

公式的含義：首先用DATE函數(shù)將M1的年,、S1的月,、D3的日合成一個(gè)計(jì)算機(jī)能識(shí)別的日期；然后使用WEEKDAY函數(shù)將這個(gè)“日期”轉(zhuǎn)換成用星期表示的數(shù)字,。

參數(shù)“2”被添加到WEEKDAY函數(shù)之后,，以便將星期一顯示為“1”，將星期二顯示為“2”.周日是“7”,。

因?yàn)槲覀儾涣?xí)慣把星期天叫做“第7周”,，所以最后用IF函數(shù)做一個(gè)判斷，自動(dòng)把“7”的顯示改為“日”,。

提示：EXCEL提供的幫助中詳細(xì)描述了函數(shù)DATE和WEEKDAY,。想了解他們的朋友可以參考一下。

為了方便人的習(xí)慣,，我們不得不把顯示為阿拉伯小寫數(shù)字的星期改成中文數(shù)字,，也就是把“星期一”改成“星期一”的格式。這需要通過定義單元格格式來實(shí)現(xiàn),。

選擇D2單元格,，右鍵單擊單元格格式，在出現(xiàn)的格式窗口中選擇數(shù)字選項(xiàng)卡,，在左側(cè)的類別框中選擇特殊,，在右側(cè)的類型框中選擇中文小寫數(shù)字，按確定退出,。

這些完成后,，可以用鼠標(biāo)選中D2單元格，按住右下角的“填充柄”復(fù)制AH2單元格,，效果如下：

在AI單元格中,，當(dāng)月的總天數(shù)可以通過公式=DAY(DATE(M1，S1 1,，1)-1)來顯示,。

公式含義：首先通過日期函數(shù)“DATE(M1，S1 1,，1)”得到本月下個(gè)月第一天的日期,。在這種情況下，這個(gè)月是5月,，下個(gè)月的第一天是6月1日,。

然后減1得到本月最后一天的日期，也就是5月31日,。最后由day函數(shù)取出代表本月天數(shù)的“31”,。

3.先設(shè)置一些考勤符號(hào)，放在AR欄，如圖：

這些符號(hào)并不統(tǒng)一,，可以根據(jù)自己的習(xí)慣和喜好自行設(shè)定,，也可以用漢字來表示?？傊?，就看你的習(xí)慣了。

如何方便快捷地將這些符號(hào)輸入到考勤表中的D4:AH33區(qū)域,？我們使用下拉框方法,。

選擇D4:AH33區(qū)域，在上面的工具欄中按Data-Validity打開有效性設(shè)置對(duì)話框,，選擇Settings選項(xiàng)卡,，在Allow中選擇Sequence，在Source中點(diǎn)擊右端的collapse按鈕,，然后用鼠標(biāo)選擇表格中的AR1:AR8區(qū)域,，再次點(diǎn)擊collapse按鈕，返回有效性設(shè)置窗口,，按,。

完成后，當(dāng)選擇考勤表D4:AH33區(qū)域的任意單元格時(shí),，會(huì)出現(xiàn)一個(gè)下拉框按鈕,。點(diǎn)擊按鈕彈出下拉框，可以用鼠標(biāo)方便地選擇要輸入的考勤符號(hào),。

4.可以輸入出勤,。怎么統(tǒng)計(jì)大家的出勤情況？或者通過公式來自動(dòng)統(tǒng)計(jì),。

先畫一個(gè)區(qū)域進(jìn)行考勤統(tǒng)計(jì),，如下圖紅圈所示：

該區(qū)域需要設(shè)置多個(gè)合并單元格，AK4:AK5合并,，AL4:AL5合并.AP4:AP5被合并,。也就是每個(gè)名字對(duì)應(yīng)的上下線需要合并，方便在一個(gè)網(wǎng)格內(nèi)統(tǒng)計(jì)上午和下午,。

在完成AL4:AP5區(qū)域的合并操作后,，選中該區(qū)域右下角的填充柄，按住鼠標(biāo)左鍵向下拉,，直到釋放單元格AP33,，這樣就可以快速合并后面的單元格。(其實(shí)是抄襲了AL4:AP5的風(fēng)格)

因?yàn)榈谝粋€(gè)人的考勤記錄區(qū)域是D4:AH5區(qū)域,，所以需要統(tǒng)計(jì)這個(gè)區(qū)域的考勤符號(hào)的次數(shù)，這樣就可以知道這個(gè)人的考勤情況,。

在AK3:AP3中輸入出勤符號(hào),，然后在AK4單元格中輸入公式=countif ($ d43360 $ ah5,，AK $3)。

公式含義：COUNTIF函數(shù)用于統(tǒng)計(jì)AK3網(wǎng)格中的符號(hào)在D4:AH5區(qū)域出現(xiàn)的次數(shù),。

使用拖動(dòng)復(fù)制功能將該公式復(fù)制到AK4:AP4區(qū)域,。

選擇AK4:AP4區(qū)域，按住AP4右下角的填充柄,，向下拖動(dòng)直到到達(dá)單元格AP33,。

現(xiàn)在統(tǒng)計(jì)區(qū)域的每個(gè)單元格都有一個(gè)公式。因?yàn)楣降哪承┎糠质褂昧私^對(duì)引用符號(hào)“$”,，所以每個(gè)單元格的公式在拖動(dòng)和復(fù)制時(shí)是不同的,。

提示：在這張考勤表中，多次使用了“拖拽復(fù)制”的方法,，可以大大簡化輸入公式和設(shè)置格式的操作,。而且公式中可以靈活使用絕對(duì)參考符號(hào)“$”，將變化規(guī)律的公式快速輸入?yún)^(qū)域,，避免了一個(gè)一個(gè)輸入的麻煩,。

現(xiàn)在讓我們看看有一個(gè)統(tǒng)計(jì)公式的效果。

在統(tǒng)計(jì)結(jié)果中,，會(huì)出現(xiàn)很多零值,，這意味著對(duì)應(yīng)的考勤符號(hào)沒有出現(xiàn)在考勤區(qū)域。零值太多的話會(huì)覺得“亂”,。我們通過設(shè)置這些零值來“隱藏”它們,。

按工具欄中的“工具-選項(xiàng)”打開選項(xiàng)窗口。按下下面顯示的設(shè)置,，并取消“零值”前的復(fù)選標(biāo)記,，使這些零值不顯示。

5.這里,，考勤表基本完成,。細(xì)心的朋友會(huì)發(fā)現(xiàn)一個(gè)小問題，就是AF3,、AG3,、AH3這三個(gè)盒子里總是出現(xiàn)29、30,、31這三個(gè)日期,，即使2月份只有28天，也讓他們覺得很不開心,。

我們可以利用條件格式的方法,，使它們根據(jù)月份的變化自動(dòng)顯示或隱藏，即當(dāng)月份小時(shí)，單元格AH3變?yōu)榭瞻?，?dāng)月份大時(shí),，顯示31。如果二月不是閏月,，則單元格AF3,、AG3和AH3中的數(shù)字不會(huì)顯示。

選擇AF3:AH3區(qū)域,，按“格式-條件格式”并按如下所示進(jìn)行設(shè)置：

利用這種條件格式的方法,，還可以設(shè)置D2:AH2區(qū)域，讓它們?cè)谥芰苋兆兂刹煌念伾?，可以更直觀的顯示一周的情況,。設(shè)置方法可以自己琢磨。

以上用excel制作考勤表的步驟基本屬于通用型,，適用于很多公司,，你也可以根據(jù)自己的情況單獨(dú)設(shè)置。