Excel函數(shù)在考勤表制作中的應(yīng)用

匡 煜

（雅安職業(yè)技術(shù)學(xué)院，四川 625000）

0 引言

考勤記錄是登記職工出勤情況的原始記錄，甚至直接關(guān)系到職工的工資結(jié)算問題，然而考勤機(jī)導(dǎo)出的打卡數(shù)據(jù)相對比較冗雜，如何將考勤機(jī)導(dǎo)出的打卡數(shù)據(jù)制作成考勤表卻成了很多考勤記錄員的一大難題。

1 數(shù)據(jù)表準(zhǔn)備

1.1 打卡記錄表

本文數(shù)據(jù)來自某公司11 月份考勤打卡機(jī)導(dǎo)出的打卡數(shù)據(jù)，命名為“打卡記錄表”，其Excel 數(shù)據(jù)表結(jié)構(gòu)如表1 所示，不同的打卡機(jī)導(dǎo)出的打卡記錄的數(shù)據(jù)格式可能會略有差異，稍做處理即可。

表1 打卡記錄表

1.2 考勤記錄表

本文需要制作的考勤表結(jié)構(gòu)如表2所示，在同一工作簿中新建工作表并命名為“考勤記錄表”，并按表2所示的結(jié)構(gòu)制作備用。

表2 考勤記錄表

2 相關(guān)函數(shù)介紹

2.1 IF函數(shù)

IF 函數(shù)執(zhí)行過程是根據(jù)指定條件的不同計算結(jié)果來執(zhí)行返回不同的值，其語法和應(yīng)用說明如下。

語法格式：IF（logical_test，value_if_true，value_if_false）。

使用說明：根據(jù)參數(shù)logical_test的計算結(jié)果來決定IF 函數(shù)的執(zhí)行結(jié)果，如果logical_test 計算的結(jié)果為真（true），IF 函數(shù)則執(zhí)行表達(dá)式value_if_true 的結(jié)果，如果logical_test 計算的結(jié)果為假（false），IF 函數(shù)則執(zhí)行表達(dá)式value_if_false 的結(jié)果。值得注意的是，IF 函數(shù)中的三個參數(shù)都可以是表達(dá)式，因此可以將其他函數(shù)和IF 函數(shù)進(jìn)行嵌套使用，以便完成更多、更復(fù)雜的數(shù)據(jù)操作。

例如：在函數(shù)表達(dá)式IF（A2>=60，“及格”，“不及格”）中，如果A2 單元格中的值大于等于60，則顯示“及格”字樣，否則顯示“不及格”字樣。

2.2 DAY函數(shù)

DAY 函數(shù)返回指定日期中對應(yīng)“年月日”中“日”的天數(shù)日期，其語法和應(yīng)用說明如下。

語法格式：DAY（serial_number）

使用說明：參數(shù)serial_number 必須是一個日期值，其中包含要查找的天數(shù)日期。

例如：函數(shù)表達(dá)式DAY（“2021-11-1”）的結(jié)果為1。

2.3 COUNTIF函數(shù)

COUNTIF 函數(shù)是用來統(tǒng)計指定區(qū)域中滿足指定條件的單元格的個數(shù)，其語法和應(yīng)用說明如下。

語法格式：COUNTIF（range，criteria）

使用說明：參數(shù)range 表示需要統(tǒng)計的單元區(qū)域，參數(shù)criteria表示統(tǒng)計的條件。

例如：函數(shù)表達(dá)式COUNTIF（B2:B5，“>55”）返回的結(jié)果表示在單元格區(qū)域B2～B5 中值大于55的單元格的個數(shù)。

2.4 COUNTIFS函數(shù)

COUNTIFS 函數(shù)是用來統(tǒng)計指定區(qū)域中滿足多個指定條件的單元格的數(shù)量，其語法和應(yīng)用說明如下。

語法格式：COUNTIFS（criteria_range1，criteria1，criteria_range2，criteria2，…）。

使用說明：參數(shù)criteria_range1 表示第一個條件關(guān)聯(lián)的第一個區(qū)域，參數(shù)criteria1則表示統(tǒng)計的第一個條件，參數(shù)criteria_range2 表示第二個條件關(guān)聯(lián)的第二個區(qū)域，參數(shù)criteria2則表示統(tǒng)計的第二個條件，以此類推，需要注意的是每一個附加的區(qū)域必須與參數(shù)criteria_range1 具有相同的行數(shù)和列數(shù)。

例如：函數(shù)表達(dá)式COUNTIFS（A2:A7，“>80”，B2:B7，“<100”）返回的結(jié)果表示在單元格區(qū)域A2～A7 中包含大于80 且同時滿足在單元格區(qū)域B2～B7中包含小于100的數(shù)的行數(shù)。

3 制作過程

3.1 新建輔助列

輔助列用于獲取每位職工的具體出勤日期。考勤記錄表中需要體現(xiàn)每個職工的具體出勤情況，因此需要在打卡記錄的工作表中添加一列輔助列來標(biāo)記職工打卡的日期，用于與考勤記錄表中的具體日期相對應(yīng)，也便于后期用COUNTIFS 函數(shù)來統(tǒng)計，本文命名該輔助列為“打卡日期”，則表1變?yōu)楸?。

表3 打卡記錄表（含輔助列）

3.2 新建輔助表

輔助表用于獲取所有打卡且非重復(fù)職工姓名。在考勤記錄表的準(zhǔn)備過程中，需要將導(dǎo)出的打卡記錄表中的姓名填寫到考勤記錄表中的姓名處，但打卡記錄表中的數(shù)據(jù)會有很多重復(fù)記錄數(shù)據(jù)，因此可以新建一個工作表來作為輔助，將打卡記錄表中的姓名列和證件編號列復(fù)制到該輔助表中并以證件編號為基準(zhǔn)刪除重復(fù)項，之后將姓名列按升序排序，排序后將姓名列復(fù)制到考勤記錄表的姓名處并對序號列重新編號。此處排序的目的是為了方便職工在簽字時可以更快地找到對應(yīng)的考勤信息，完成考勤記錄表中的姓名編輯后可以刪除該輔助表。

3.3 函數(shù)實現(xiàn)

第一步：計算打卡記錄中職工的打卡日期。在打卡記錄的輔助列“打卡日期”列對應(yīng)的單元格（如G2）中應(yīng)用函數(shù)表達(dá)式IF（E2>0，DAY（E2），DAY（F2）），并向下填充至最后一條打卡記錄，得到所有職工打卡的具體日期，與“考勤記錄表”中具體出勤情況中的具體日期相對應(yīng)，結(jié)果如圖1所示。

圖1 考勤記錄

第二步：計算考勤記錄表中職工的考勤日期。在考勤記錄表中第一位職工對應(yīng)1號日期所在的單元格（如C4）中應(yīng)用函數(shù)表達(dá)式COUNTIFS（打卡記錄!$B:$B,考勤記錄!$B4,打卡記錄!$G:$G,考勤記錄!C$3）并向右填充至31號日期，得到該職工在對應(yīng)日期的打卡記錄數(shù)。這里的結(jié)果我們只做兩種情況討論，一種是值為0的情況，表示該職工在對應(yīng)這一天沒有打卡，視為缺勤，另一種是值大于0的情況，表示該職工當(dāng)天完成打卡，視為出勤。再以第一位職工的具體出勤情況為基準(zhǔn)，向下填充至最后一位職工，得到所有職工的具體出勤情況，結(jié)果如圖2所示。

圖2 出勤情況

第三步：計算考勤記錄表中每位職工的合計出勤天數(shù)。在考勤記錄表中第一位職工對應(yīng)合計出勤天數(shù)所在的單元格（如AH4）中應(yīng)用函數(shù)表達(dá)式COUNTIF（C3:AG4,">0"）并向下填充至最后一位職工，得到所有職工的合計出勤天數(shù)，結(jié)果如圖3所示。

圖3 合計出勤天數(shù)

第四步：設(shè)置出勤的顯示方式，即用符號“√”來表示出勤，缺勤不顯示任何符號。自定義設(shè)置具體出勤情況欄目下對應(yīng)的單元格區(qū)域（如C4:AG16）的單元格格式，將其單元格格式設(shè)置為自定義，并在自定義“類型”設(shè)置為“√;;”（格式為：“正數(shù)；負(fù)數(shù)；零”，此處簡單說明一下這樣設(shè)置的作用，即表示在該單元格區(qū)域正數(shù)用“√”表示，負(fù)數(shù)和零則忽略，不用任何符號表示），設(shè)置完畢后結(jié)果如圖4所示。

圖4 出勤情況一覽表

3.4 打印設(shè)置

考勤記錄表制作完成后需要打印出來讓職工簽字確認(rèn)，如果需要打印的篇幅比較長，不能在一頁以內(nèi)打印完，打印時應(yīng)注意設(shè)置每頁打印標(biāo)題才能方便職工對照查看，具體設(shè)置參照頁面布局中的“打印標(biāo)題”功能,打印區(qū)域即整個列表區(qū)域，頂端標(biāo)題行即為需要設(shè)置打印的標(biāo)題區(qū)域。

4 結(jié)語

考勤是人力資源管理的重要內(nèi)容，也是員工績效考核的重要依據(jù)。與手工統(tǒng)計相比，利用Excel 進(jìn)行考勤統(tǒng)計明顯提高了工作效率。本文詳細(xì)介紹了利用Excel 制作考勤表的相關(guān)函數(shù)及具體過程，希望為廣大人事考勤工作人員或工資核算人員提供參考。