問題情境
掌握職工的生日信息,及時送出生日祝福,是每位HR必備的技能。某公司要求將公司的員工生日信息實現查詢,可方便的查看本月及下月過生日的員工信息,效果如下動圖:
效果中可以看出:勾選”本月生日“,本月過生日的員工所在行添加一種顏色;勾選”下月生日“,下月過生日的員工所在行添加另一種顏色,
實現方法
第一步:從身份證號碼中提取生日
在D3單元格輸入公式:=DATE(MID(C3,7,4),MID(C3,11,2),MID(C3,13,2)),確定,並向下填充,得所有員工的出生日期。
用DATE函數提取的出生日期才是真正的日期格式。如下圖:
可以根據自己需要更改日期格式,如下圖:
第二步:插入表單控件
1、在”開發工具“菜單中選擇”插入“-”表單控件“中的複選框,並輸入控件說明文字,如下圖:
過程如下動圖:
2、在複選框上點擊右鍵,選擇”設置控件格式“,如下圖:
3、將表單控件單元格鏈接到新建的”輔助“工作表中的B1單元格,如下圖:
過程如下動圖:
第三步:設置條件格式
1、選中出生年月所在列,選擇“開始”菜單中的“條件格式”——新建規則,如下圖:
2、選擇“使用公式確定要設置格式的單元格”,在“為符合此公式的值設置格式”中輸入公式:=and(month($d1)=month(today()),輔助!$B$1=TRUE),並設置“格式”,填充一種背景顏色,如下圖:
該公式的含義是:D1單元格的月份與今天的月份一致,並且輔助工作表中B1單元格的值為TRUE.
效果如下動圖:
3、如果想將本月生日的員工所在行都添加顏色,可以選擇“條件格式”——“管理規則,如下圖:
將規則的應用範圍改為A列到E列,如下圖:
效果如下動圖:
4、設置”下月生日“條件格式的過程同”本月生日“設置,只不過公式改為:=and(month($d1)=month(today())+1,輔助!$B$2=TRUE),如下圖:
效果如下:
第四步:隱藏”輔助“工作表
此步,可以省略。
經以上步驟設置,可以自由選擇查看本月或者下月過生日的員工信息。
------------------------------------------------------
以下這本《EXCEL數據處理與可視化》是我自己寫的書,請廣大粉絲朋友們指正:
我的專欄:
閱讀更多 韓老師講office 的文章