考勤表精講

大家好 今天呢準備給大家再來聊一下考勤表 在這個考勤表當中呢 我們會要講到若干個功能 比如說當我們在打勾打叉的時候呢 這邊能夠實現一個自動的統計 然後呢當我們在更改日期的時候呢 啊所有的日期呢 都能夠達到一個同步的更新 同樣的啊 我們這邊呢 會有一個週六和週末 這樣的一個標記顏色 當我們在更改日期的時候呢 我們的週六和週末呢 也會得到一個重新的標記 那整個應該怎麼樣來做呢 我們一步一步來講 首先啊我們需要一個員工姓名系列 放在我們的a列 並且呢在這個表格的最上方呢 留下大概四五行左右 以備不時之需 如果到時候我們不需要的話呢 可以把它給刪除掉 接下來呢 我們可以選擇到這個單元格 輸入一個一 然後選擇到這個單元格 在開始選項卡下面 選擇到我們的填充序列 行選擇到行啊 然後他的這個終止值呢是31 因爲我們按照一個月最大的31天 來進行一個計算 好接下來呢 我們可以到這邊來輸入一個年份 比如說在這個位置來寫到2020 然後後面呢我們可以寫這個年最好呢 我們可以把這邊來做一個合併單元格 然後寫到一個年 這個地方呢 我們可以來寫到多少個月對吧 可以寫到一個2月份月 接下來呢 我們可以到這邊來寫一些函數 我們可以在1號這一天哈 比如說現在呢就是2020年的2月1號 那這邊我們可以寫到哈等於date 函數中間呢會有三個參數 第一個參數呢是年份 我們可以選擇到2020年2月 並且這一天一定是1號 確定 那後面的單元格呢 就應該等於前一個單元格加一對不對 所以這邊就應該等於它加上一 然後我們可以把整個內容呢 把它給拉扯到最後面 中間有一些部分看不見啊 沒有關係 我們已經知道了年份和月份 那我們下方呢就沒有必要啊 到這邊來 顯示到我們完整的這樣一個年月日了 所以我們可以選擇到這個區域當中 然後呢對它來進行一個設置啊 我們只需要來顯示到我們的天數就行 因爲上方已經有年和月了對吧 所以選擇到自定義啊 在這個中間呢 大家可以看到 目前它所默認的狀態呢就是那YYY m d 那d它所代表的呢也就是天 所以在這個時候呢 我們只需要來輸入一個d就行了 我們只需要它的天 然後上方呢就會有我們的年月啊 這個呢就是2月1號 2月2號等等 接下來呢 我們可以把所有的這樣一些單元格呢 稍微變窄一些哈 所以可以選擇到我們的這31天 然後呢啊 我們來拽扯一下中間的這樣一個呃 列句啊 好這樣的話呢 實質上呢 也就是 我們最後想要的這樣一個結果了 大家或許呢也注意到了 在我們整個2月份的最末尾呢 會顯示一個一和一個2 一呢 實質上它所代表的是3月1號 這邊呢代表的是3月2號 那比如說我們把它給切換到了3月份 你就會發現 3月份呢 它會有 31天如果是2019年 同樣會有31天 但是2019年的2月份呢 你會發現它只有28天 所以我們可以總結出來一個道理啊 也是一個常理啊 也就是在一年當中 我們一個月份當中最小的天數呢 是28天或者是29天 最大的天數呢就是31天 所以從28號往後走的每一天 我們都需要來做一個逐步的判斷 於是我們可以選擇 到第28天之後的這樣一天 然後呢到中間來輸入一個函數 這個函數呢 我們可以到上方來輸入等於if函數 如果28天 28號 它這一天它所在的月份 我們可以用到一個month函數 如果它等於下一天 也就是等於29號嘛 因爲2月份它有可能是28天 有可能是29天 所以我們同樣到這邊來 寫到一個month函數 我們的 28號這一天加上一 如果他們倆相等的話 那也就證明 他們同樣是處於同樣的月份 譬如說20292020年他的2月份29天和28天 這兩天他們的月份當然是相等的 因爲2020年2月份他會有29天 所以我們可以做這樣一個判斷 當它們倆相等的時候 則我們會顯示到什麼呢 a C 5+1了就等於來它加上一了 如果它不成立呢 我們則會讓它顯示空值括弧回來確定 啊 好吧這樣的話呢 我們就知道 那譬如說我們現在切換到2020年 那這邊就是29天嘛 如果呢 我們把這邊呢切換到2019年的話呢 這邊呢啊 第29號就不見了對吧 因爲2019年2月份他只有28天 我們可以把這個函數呢 把它給往右邊拉 你會發現這邊這兩個值呢 同樣會出現一個錯誤 爲什麼會出現錯誤呢 因爲這一天它不存在 我們現在在下一個單元格當中 我們所對比的值呢是a d單元格 而不是之前我們在這個地方所輸入的 a C 5單元格 當這個單元格爲空值的時候 這個後面它必然會出現錯誤 因爲它沒有辦法去加一了 沒有辦法去用month了 在這個位置它就是一個空值而已 所以呢 我們這個函數它完整的輸入方法呢 應該是在前方再加一個if error函數 當它出現錯誤的時候呢 我們則會再給它一個雙引號 來代替我們的錯誤 確定然後再把它給往後面拉 這樣的輸入方法呢纔是對的啊 希望大家呢 能夠把這塊的邏輯關係 把它給理解清楚了 好我們可以到這邊來測試一下 2020年的2月份 然後呢如果是6月份 如果呢是3月份 他都只會顯示當月所有的日期 而不會顯示到下個月的日期 接下來呢 我們可以把我們的後面的網格線呢 先把它給取消掉啊 然後呢再選擇啊 我們需要重新再添加網格線的部分呢 把它給框選起來在這個位置 然後呢選擇到右鍵設置單元格格式 邊框藍色 確定 然後呢 我們需要把這個年份和月份這邊呢 也來做一個顏色的標記 這邊呢我們可以給到一個淺黃色啊 好吧那這樣的話呢 當我們在更改我們的年份 2019年 2018年這邊都可以發生一個相應的變化 好接下來呢 我們需要到每一個單元格當中 來設置一 個打勾打叉 來代表我們的這樣一個出勤狀況 所以我們可以把這一段呢 把它給複製過來 放置在這個位置 好然後呢 我們再選擇到所有的這樣一個單元格 單元格當中呢 我們需要來做一個數據驗證 數據驗證呢它的值來源呢 在我們的勾勾叉叉呀 等等這樣一些範圍中間 每一個符號呢所代表的意義呢 上方呢都會有這樣一個註解 點擊到確定 這樣的話呢 我們就可以實現勾叉啊 它的這樣一個快速選擇 接下來呢 我們可以把這邊來做一個美化 我們可以把上方呢呃 我們整個平齊的部分哈 來進行一個合併居中 並且呢選擇到合併居中的區域 來設置一下單元格格式 在填充當中我們選擇到填充效果 然後呢可以給到他這樣一個藍色 然後呢整個陰影呢 就是偏白的部分是朝下走啊 點擊到確定 確定好我們可以在中間呢來輸入啊 我們自己想要輸入內容 譬如說這邊可以寫到簡單的考勤表 然後呢可以把考勤表呢呃 加一個字眼呢 把它給變成我們整個變成墨然雅黑哈 並且呢把這一塊呢字體呢加大 加粗 好這一塊呢算是做完了 接下來呢我們可以到這邊呃 再來做一個呢 你看這個3月份這塊哈 可能他沒有體現出來 我們可以把它給拉伸開來一些些 接下來我們想要對我們打勾打叉的 這樣一些部分呢 來進行一個呃實時的統計啊 所以我們可以把這個部分呢 再把它給進行一個複製並 且呢把它給放置在這個位置 放置在這個位置吧 好同樣的道理啊 我們可以把這邊呢 把它給稍微 再把它給稍微進行個縮進啊 並且讓它趨於一個居中的位置 把這一塊呢 可以稍微再把它給拉過來一些些 那同樣的道理了 我們這個下方呢 同樣是需要來做一個表格 所以我們可以拉扯到平齊的到最下方 然後呢設置一下單元格格式 在中間呢 選擇到邊框藍色 外邊框和內邊框和藍色 點擊到確定 好接下來呢 我們需要來做一些函數在旁邊 在這個地方呢 咱們可以輸入一個什麼樣的函數呢 譬如說我們想要統計一下那周筆暢啊 她出勤的狀況 或者呢她還會有這個曠工的狀況 那這邊呢 我們其實啊可以用一個計數函數啊 叫做count if 之前呢咱們也說過啊 然後我們可以選擇到range呢 在哪地方呢 在這個區域當中 然後呢我們的確認值呢 因爲這個單元格它所對應到的呢 是出勤對吧 所以說我們可以選擇到出勤 它所在的呃 這樣一個符號哈 當然在這個過程當中 等一下我們會往右邊拉 也會往下面拉 所以在出勤這個地方 他的這個標識所在的這一行當中呢 我們要確保他的行位不發生變化 但是他的列位一定會發生變化 所以我們可以來鎖行而不鎖列 而在我們的這樣一個確認區域當中呢 在這塊區域當中呢 我們應該是來鎖列而不鎖行 因爲等下會往下拉 確定然後整 個呢把它給往右邊拉 然後呢雙擊下拉 啊當然了 中間沒有連接哈 我們只能手動下達 那現在呢 大家可以看到這邊哈 當我們在畫勾的時候 畫勾的時候 那這邊呢都會得到一個實時的統計 但是呢這邊看上去哈 似乎呃這個0比較多 那我們只要顯示大於0的部分 所以我們可以在第一 第一個單元格這邊呢 來輸入一個函數啊 在這個地方我們可以輸一個if函數 如果它這樣一個值呢 統計出來的值呢 如果是大於0 我們則會顯示到 我們的這樣一個COUNTIF的值 否則呢會顯示空值 這樣看上去呢 就會稍微要好看一些些 你看這樣呢就會不會啊 就讓你看上去就特別的麻密好吧 接下來呢 我們可以來做一些條件格式 那比如說啊 在這個過程當中 我們並不知道週六和週日在什麼地方 在此之前呢 我們啊需要來了解到一個函數 這個函數呢叫做Weekday函數 我們先可以等於哈 譬如說是3月4號這一天 3月4號這一天哈 我們可以把它給更換成 我們的一個格式啊 更換成新奇格式 我們先來看一下日期 我們可以選擇到這邊會有個周幾啊 點擊到確定週一嘛 那我們可以到這邊呢 來用Weekday函數來測試一下 我們可以選擇到下方這個單元格 然後呢這個時候他會問你一個問題 說 你現在排列你新奇的方式是怎麼樣的 我們可以選擇到2啊 也就是說週一呢 它會顯示一 因爲從1到7來進行 個排列確定好 我們知道哈 呃 3月4號這天是週一 如果我們選擇到3月11號呢 或者說這邊呢 選擇到3月15號呢 那週五他就排五對吧 大家就可以想到一個問題哈 如果是週六的話呢 他就是六 週日的話呢 那就是7 所以我們可以選擇到這個範圍中間 然後呢再選擇到條件格式 新建規則使用公式啊 到這個地方 我們來輸入到等於Weekday函數 並且呢 我們選擇到我們的第一個單元格哈 也就是我們的日期所在的 這樣一個單元格 B5單元格 然後呢我們的參數是2 當它如果是大於5的話 那也就是6或者是7 對吧 當他滿足這個條件的時候呢 我們則會給他一個不一樣的顏色 但是呢這個地方大家注意啊 我們需要鎖的呢是B5哈 鎖的是行 而不是鎖b 因爲它會不停地往這邊來 進行一個延伸 這邊呢實質上哈 它也是一個絕對引用和相對引用的 這樣同樣一個思路 好吧然後給他一個格式 我們可以選擇到一個淺藍色 然後點擊到確定 點擊到確定 這樣的話呢 大家就可以發現啊 那這個就是週六 這個就是週日 週六週日 當我們在更換月份的時候呢 這個色調呢 也可以發生一個相應的變化 最後呢我們還可以再來補充一道 自動聚焦的條件格式 我們可以選擇到整個這個區域當中 這個呢也是咱們之前說過的 新建規則使用公式 到這個地方呢 我們直接輸入到等於哈cell 然後中間呢 可以打上一 個row啊 當我們的cell row呢 等於我們的當前row的時候 則我們會要給到它一個黃色 點擊到確定 確定同樣的道理 我們還需要來制定一個條件格式 就是我們的列啊 中間輸入COL括回來 等於我們的當前我們的例數的時候 我們同樣會顯示到剛纔的這個黃色 點擊到確定 確定好 現在呢當我們在更改內容的時候呢 這邊呢都可以達到一個自動的聚焦 然後在我們的右邊呢 它也能夠達到一個自動的統計 好吧那今天這章呢 就這樣哈 那課件下載呢 在整個文章最下方啊 在我的公衆號啊