hello，大家好，今天跟大家分享下如何製作考勤表，我們先來看下今天製作的考勤表都能達到那些效果
1. 表頭依所選月份自動變動
2. 自動判斷當月天數星期
3. 週六週日自動填色，選擇單休僅週日填色
4. 自動計算當月應出勤天數，遇到假日需手動新增假日
5. 自動新增邊框，公式自動填充，下拉
6. 自動匯總當月考勤
7. 總結表缺勤自動填滿顏色
以上就是今天我們需要製作的考勤表的大致內容，製作的方法大多使用的是公式以及條件格式，都是我們比較常用的功能，下面就讓我們來一起操作下吧
一、表頭依所選月份自動變動
首先我們需要在第二列製作需要資料的內容，如下圖，然後我們在第一列中建立合併儲存格，然後在裡面輸入公式：=D2&"年"&H2&"月"&"考勤表"
這裡d2是年份，h2是月份然後我們使用連結符號將資料連結起來，這樣的話就能達到表格自動變化的效果
二、自動判斷當月天數，星期
在號數的第一個儲存格中輸入=--(D2&-H2)，然後在挨著的儲存格中輸入公式=IFERROR(IF(MONTH(B3+1)=$H$2,B3+1, ""),"")向右拖動，在這裡我們一共拖動30個格子即可，因為月份最多30天，然後我們選擇日期這個區域然ctrl+1調出格式窗口然後選擇自定義，在類型中輸入d號，點選確定，這樣的話就變成了號數
緊接著我們在下面一行的單元格對應的位置中輸入=b3然後向右填充數據，然後按ctrl+1調出格式窗口，選擇自定義將類型設置為aaa點擊回車，這樣的話就變為了星期顯示
三、依單雙休自動填色
首先我們選擇星期這一行數據，然後點擊條件格式，選擇新規則然後選擇使用公式確定要設定的格式，我們將公式設為：IF($L$2="雙休",WEEKDAY(B$4,2) >5,WEEKDAY(B$4,2)>6)然後點擊格式在填充中選擇一個自己喜歡的顏色即可，這裡我們使用if函數判斷l2的值是不是雙休，如果是就回傳第一個條件（星期數大於5），如果不是就回傳第二個條件（星期數大於6）
設定完成後我們只需要選擇星期這一行資料向下填充，在填充柄中選擇僅填充格式即可，這樣的話我們就批量的向下填充了格式
四、自動計算當月的應出勤
自動計算當月出勤會根據單雙休自動計算，當將單雙休設定為雙休預設一週休息兩天，設定為單休預設休息1天，因為還牽扯到法定的假日，這個使用公式計算比較麻煩，所以在這裡設定為了手動輸入，如果遇到法定假日直接輸入休息天數即可
公式為：
=IF(L2="雙休",NETWORKDAYS.INTL(B3,EOMONTH(B3,0),1)-P2,NETWORKDAYS.INTL(B3,EOMONTH(B3,0),11)-P2)
這個公式在主體上是if函數，首先使用if函數判斷單雙休，然後使用NETWORKDAYS.INTL函數自訂休息日，在這我們使用EOMONTH函數取得當月的最後1天的日期，最後我們將結果減去p2，也就是法定假日的天數即可
五、自動新增邊框，公式自動填充，下拉
可以先對幾行資料區域設定了下拉來代表對應的考勤狀態，然後使用countif函數對各種考勤狀態進行匯總，設定完畢後我們選擇設定的區域，然後按ctrl+t插入表，將表格的標題行隱藏，然後將樣式更改為無即可，這樣的話當我們向下資料輸入，公式下拉以及格式都會自動的填充
六、自動匯總當月考勤
新建一個sheet，並且設定好表頭，我們在第一個姓名的位置中輸入函數：=IFERROR(IF(考勤明細!A6="","",考勤明細!A6),"")這個的作用是判斷考勤明細的表中姓名a6這個單元格的位置是不是空白的，如果是就返回空白，如果不是就返回這個名字，當返回名字後我們就使用vlookup在考勤明細中查找出勤天數，請假天數等信息，這個是vlookup的常規用法就不多做介紹了
然後我們選擇這幾列數據，選擇使用公式確定格式然後輸入公式：=$A1<>""，緊接著點擊格式，選則外邊框即可，點擊確定，這樣的話每當讀取到一個名字就會自動的為整行添加邊框
七、總表缺勤自動填色
同樣的我們按住ctrl鍵先選擇第一行的遲到天數早退天數兩個單元格，然後選擇出勤天數以及請假天數，最後選擇缺勤天數，這樣做是為了將缺勤天數設置為活動單元格，然後點擊條件格式，選擇使用公式確定格式輸入公式：=IF($D2="",FALSE,$D2>0)我們這麼做為了判斷缺勤天數這個單元格是不是空值，如果是不顯示設定的格式，如果不是則顯示設定的格式
我們需要向下多填充一些。滿足我們使用即可
怎麼樣？你覺得這些功能滿足你日常的使用嗎？
我是excel從零到一，關注我持續分享更多excel技巧
文章為用戶上傳，僅供非商業瀏覽。發布者：Lomu，轉轉請註明出處： https://www.daogebangong.com/zh-Hant/articles/detail/zhi-zuo-zi-dong-hua-kao-qin-biao-zi-dong-pan-duan-tian-shu-hui-zong-zhou-mo-zi-dong-tian-chong-yan-se.html
支付宝扫一扫 
评论列表（196条）
测试