如何實現(xiàn)自動考勤和月份動態(tài)更新，老師這樣干的

刀鋒一九三八 2023-06-30 發(fā)布于山東

展開全文

——EXCEL2016系列教程之“智能考勤表"

#COUNTIF函數(shù)計數(shù)#粉絲問我，如何制作一個可以自動生成考勤記錄的考勤表？于是我就制作了這期內(nèi)容，本次課包含的內(nèi)容很豐富，有自定義格式，有函數(shù)，還有數(shù)據(jù)有效性的驗證等知識，干貨多多，希望讀完。如果有問題，給我留言。視頻教程將陸續(xù)集結(jié)，敬請期待~??！

一、設(shè)置自動更新的“考勤表頭”

1.設(shè)定在“AM2”單元格輸入當前的年月日（例如2021年2月1日），——快捷鍵“CTRL+1”——打開“設(shè)置單元格格式”對話框——選擇“自定義”——在“類型”中輸入“yyyy"年"m"月"”，單擊“確定”，顯示結(jié)果為2021年2月，如圖。

2.在“A1”單元格輸入“=AM2”——快捷鍵“CTRL+1”——打開“設(shè)置單元格格式”對話框——選擇“自定義”——在“類型”中輸入“"某""某""單""位"yyyy"年"m"月""員""工""考""勤""表"”——單擊“確定”即可，如圖。

3.完成上述操作后，每月只需要修改考勤時間，考勤表表頭就會自動更新，如圖。

二、設(shè)置考勤“日期”

1.在C4單元格輸入“=AJ2”——選中該單元格——快捷鍵“CTRL+1”——打開“設(shè)置單元格格式”對話框——選擇“自定義”——在“類型”中輸入“"d"”——單擊“確定”，結(jié)果顯示為當月第一天的日期，如圖。

2. 在d4單元格輸入“=C4+1”——橫向拖動填充柄，填充其他單元格，如圖。

3.考勤天數(shù)界定

每月的天數(shù)不一樣，有的月份有31日，2月只有28或29天，等情況，這是需要根據(jù)月份設(shè)定考勤天數(shù)。

（1）單擊“開始”——在“樣式”選項組中選擇“條件格式”——在彈出的下列列表中，單擊“新建規(guī)則”，如圖。

（2）打開了“編輯格式規(guī)則”對話框——在“選擇規(guī)則類型”中選擇“使用公式確定要設(shè)置格式的單元格”——在“編輯規(guī)則說明”中輸入表達式“=MONTH(AE4)>MONTH(AB4)”，——單擊“格式”按鈕，如圖。

（3）打開“設(shè)置單元格格式”對話框——選擇“自定義”——在“類型”中輸入“;;;”（英文狀態(tài)下的分號，隱藏單元格的內(nèi)容），單擊確定，如圖。

（4）這時返回 “編輯格式規(guī)則”對話框，單擊“確定”。這時根據(jù)輸入的月份不同，考勤天數(shù)發(fā)生變化，看效果。

補充：打開“條件格式規(guī)則管理器”將“應用于”設(shè)置為“=$AE$4:$AG$5”，（目的是將對應的星期也進行隱藏）如圖。

三、設(shè)置考勤日期對應的“星期”

在C5單元格輸入“=C4”——打開“設(shè)置單元格格式”對話框——選擇“自定義”——在“類型”中輸入“aaa”（“aaa”格式表示，將對應的日期顯示為“星期”），單擊確定，如圖6。

四、設(shè)置周六和周日“高亮顯示”

為了便于數(shù)據(jù)統(tǒng)計，我們將周六和周日，設(shè)置成高亮顯示。

1.選定所有“日期”單元格（C4:CAG）——打開了“編輯格式規(guī)則”對話框——在“選擇規(guī)則類型”中選擇“使用公式確定要設(shè)置格式的單元格”——在“編輯規(guī)則說明”中輸入表達式“=WEEKDAY(C$4,2)>5”，——單擊“格式”按鈕，——打開“設(shè)置單元格格式”對話框——選擇“填充”——在“背景顏色”中，選擇一種顏色——單擊確定，如圖。

2.這時返回 “編輯格式規(guī)則”對話框，單擊“確定”。這時對應周六和周日的“日期”，高亮顯示，如圖。

3. 打開“條件格式規(guī)則管理器”將“應用于”設(shè)置為“=$C$4:$AG$25”，（將條件格式運用與考勤填寫區(qū)域），實現(xiàn)了周六和周日高亮顯示，如圖。

五、設(shè)定“考勤數(shù)據(jù)的有效性”

1.在sheet2表中，設(shè)置考勤表的《填報說明》，如圖。

2.選中考勤表中的單元格區(qū)域（C6:AG25）——點擊“數(shù)據(jù)”菜單——在“數(shù)據(jù)工具”選項組中——單擊“數(shù)據(jù)驗證”按鈕——在彈出的下列表中，單擊“數(shù)據(jù)驗證”，如圖。

3.在打開的“數(shù)據(jù)驗證”對話框中——選擇 “設(shè)置”標簽——在驗證條件“將允許(A)”——設(shè)置為調(diào)為“序列”——單擊“來源”選擇右側(cè)標記——選擇需要設(shè)置“序列“的內(nèi)容，設(shè)置完畢后——點擊確定，即可，如圖。

演示（圖7）：

六、設(shè)置“自動考勤統(tǒng)計”

1.選中統(tǒng)計考勤天數(shù)的單元格（AH6）——輸入函數(shù)“=COUNTIF(C6:AG6,"O")”,即可統(tǒng)計標記“O”出勤天數(shù),如圖。

2.同理設(shè)置好其他考勤的公式，分別如下：

事假：“=COUNTIF(C6:AG6,"△")”;

病假：“=COUNTIF(C6:AG6,"B")”;