讓同事看呆!製作根據實際休息日期,自動填充顏色的考勤表,文末有驚喜

Hello,大家好,之前跟大家分享了我們如何讓考勤表根據單休與雙休自動的填充顏色,最近有粉絲問到:能不能讓考勤表根據實際的休息日自動的填充顏色呢?可以是可以,只不過因為牽扯到假期調休,我們每年的休息日都不是固定的。想要實現這樣的效果,最簡單的方法就需要建立一個輔助的休息日表,根據休息日表格中的日期來填充顏色,它的製作其實非常的簡單,下面就讓我們來一起操作下吧

一、構建輔助表

這個輔助表其實就是一列資料,我們可以新建一個sheet,然後將表頭設定為休息日期,在下面輸入當月的休息日期,隨後按下快捷鍵Ctrl+T將普通錶轉換為超級表即可,我們還需要記得這個超級表的名稱,可以在【表設計】功能組下找到表名稱,預設是表1

讓同事看呆!製作根據實際休息日期,自動填充顏色的考勤表,文末有驚喜

二、構建考勤表

我們在D2這個單元格中輸入年份2021,然後在H2這個單元格中輸入月份6,我們需要根據這兩個資料來構建表頭和當月的日期

1.構建表頭

表頭公式為:=D2&“年”&H2&“月”&“考勤表”,在這裡我們利用連線符號將年月與文字連線在一起,當更改年月表頭就會自動發生變化

讓同事看呆!製作根據實際休息日期,自動填充顏色的考勤表,文末有驚喜

2.構建日期

在B3單元格中輸入=DATE(D2,H2,1)來構建每月的1號,然後在C2單元格中輸入=IFERROR(IF(MONTH(B3+1)=$H$2,B3+1,“”),“”)向右拖29個格子,因為月份最多是有31天。隨後選擇這一行資料,按ctrl+1調出格式視窗點選自定義在型別中輸入D號然後點選確定就會變為號數顯示

3.構建星期

在號數下面對應的單元格中輸入=B3向右填充,然後直接著按ctrl+1調出格式視窗,在【自定義】將【型別】設定為AAA然後點選確定,就會變為星期數

三、自動填充顏色

1.設定輔助資料

首先我們需要在日期的前面插入兩個空白行,將第一行的公式設定為:=DAY(B5),來獲取每天的號數

將第二行的公式設定為:= =VLOOKUP(TRUE,B3=表1,1,0),表1就是剛才設定的日期表,這樣的話,表1中存在的號數就會顯示true,否則的話就會顯示為#N/A這個錯誤值

讓同事看呆!製作根據實際休息日期,自動填充顏色的考勤表,文末有驚喜

2.填充顏色

隨後我們選擇需要設定的資料區,然後點選【條件格式】選擇【新建規則】找到【使用公式確定格式】將公式設定為:=B$4=TRUE,這個b4就是資料區域中的第一個單元格,在這裡需要注意的是b4這個單元格需要鎖定資料不鎖定字母,最後將上面的2行輔助列隱藏掉就可以了,至此就製作完畢了

讓同事看呆!製作根據實際休息日期,自動填充顏色的考勤表,文末有驚喜

我們只需要在休息日表中將休息日更改為當月的休息日,考勤表就能自動的填充顏色,以上就是今天分享的全部內容,怎麼樣?你學會了嗎?

我是Excel從零到一,關注我,持續分享更多Excel技巧

頂部