現在公司都是用打卡機考勤,我一直想把打卡記錄直接轉換成考勤表,可手懶一直沒有做。昨天遇到一同學提問,便試了試果然可以,而且功能很強大。
員工打卡信息表:
生成後的員工考勤表:
可以根據月份、部門進行篩選,而且可以用紅色、綠色顯示遲到、早退。
當然,也可以統計是遲到、早退、未打卡次數
製作步驟:
設置公式
設置公式,從日期中提取月份、天數和上午/下午。
E2: =MONTH(D2)
F2: =DAY(D2)
G2: =IF(HOUR(D2)<12,"上午","下午")
插入數據透視表
插入數據透視表把
- 姓名、上午/下午拖入行標籤
- 日期拖入列標籤
- 日期時間拖入值標籤。
整理數據透視表
- 用表格樣式顯示
- 隱藏彙總行
- 刪除總計行
- 把計數更改為最小值(重複打卡只算第1次)
再把小數更改時間樣式並把下午行填充顏色、設置下邊框。
設置條件格式
設置遲到(8:30後打卡)、早退(下午17:30前打卡)顏色自動填充:
選取上午行 - 開始 - 條件格式- 新建規則 - 使用公式.... - 輸入公式:
=TEXT(b5,"h:m")-"8:30">0
公式說明:使用text提取時間和8:30對比
注意:設置後再打開條件格式管理時,B5會自動變為C5,這是透視表和一般表格設置條件格式不同的。
設置後,晚於8:30打開的記錄都會自動填充紅色。
再設置下午早退顏色
公式:
=AND(C6<>"",TEXT(C6,"h:m")-"17:30"<0)
設置後早於17:30的時間會自動填充綠色
設置統計公式
遲到次數(y5)
=IF(B5="","",SUMPRODUCT((TEXT(C5:X5,"h:m")-TIME(8,30,0)>0)*1))
未打開次數(z5)
=IF(B5="","",COUNTBLANK(C5:X6))
早退次數(AA5)
=IF(B5="","",SUMPRODUCT((TEXT(C6:X6,"h:m")-TIME(17,30,0)<0)*(C6:X6<>"")))
不同的單位有不同的考勤製作,比如有的單位一天打6次卡,這時你只需修改源表G列公式,增加一時間區間。
趕快保存下來,這個月考勤表就能輕鬆應對了!
閱讀更多 輕鬆工作快樂生活 的文章