编按：

对于HR来说，一张专业的Excel档案表，其中包含了很多Excel实用技巧，也必不可少的会应用到一些函数和公式。一份足够专业的Excel表可以让HR的日常工作效率提高数倍，减轻办公压力！今天小E和大家介绍的就是这样一份Excel员工档案表的制作方法！

员工档案是用人单位了解员工情况的重要资料。因此一个企业在人事管理中，首先要制作员工档案表，这样才能提供人员调动和分配的基础数据。

笔者下面做了一张又规范又省时的员工档案表，表中有公式和函数，也涉及到数据有效性。大家一起来学习！

一、规划表格框架

在制作员工档案表时，首先要创建档案数据的记录表格，即基础数据表，制作时注意不要合并单元格。

1.在第一行输入制表时间和制表人，在第二行输入表格各列标题。

选择A1：R1单元格区域，单击“开始”选项卡，选择“对齐方式”组中的“合并后居中”按钮，在弹出的下拉列表中选择“合并单元格”选项。选择A1单元格，把“字体”字号设置为10磅，加粗，字体颜色设置为淡蓝色。

2.设置表头。选择A2：R2单元格区域，设置字体字号为12磅、加粗，设置字体颜色为白色，为单元格填充蓝色，设置对齐方式为居中，调整第二行单元格的高度至合适。

3.选择可能输入员工档案数据的单元格区域，点击“开始”选项卡，选择“字体”组中右下角的“对话框启动器”，选择“边框”，在“颜色”下拉列表中选择“蓝色”，对外边框设置“粗线”，对内边框设置“细线”。

二、设置数据有效性

在完成表格框架的制作后，需要输入数据，为了保证表格中输入数据的准确性和统一性，可以为这些有规律的数据分类设置单元格的数据有效性。

1.在A3单元格输入文本数据类型的第一个员工编号0001，向下拖动填充柄填充其它员工的编号数据，此时点击出现的“自动填充选项”按钮，在弹出的下拉列表中选择“不带格式填充”，再继续向下填充。

2.选择“所在部门”列中D3:D100单元格区域，选择“数据”选项卡下的“数据工具”组中的“数据验证”按钮，在“设置”选项卡下的“允许”下拉列表中选择“序列”，在“来源”参数框中输入“总经办,人事部,财务部,销售部,生产部,技术部,行政办,市场部”。这样能够规范D列的输入内容。

3.选择“身份证号”列中的G3:G100单元格区域，点击“数据验证”，选择“设置”，在“允许”下拉列中选择“自定义”，在“公式”参数框中输入“=LEN(G3)=18”，在“输入信息”中输入“请输入18位的身份证号码！”。

4.选择“最高学历”列中的J3:J100单元格区域，点击“数据验证”，选择“设置”，在“允许”下拉列中选择“序列”，在“来源”参数框中输入“中专,大专,本科,硕士,硕士以上,高中及以下”。

5.选择H、L、M三列单元格，设置为“短日期”；选择G列单元格，设置为“文本”；选择所有包含数据的列，设置为“自动调整列宽”。

三、使用公式返回相关信息

档案中部分基础数据存在联系，当某一信息填入后，另一个信息即可通过公式计算出来。比如，可以通过函数提取身份证号中的数据得到性别、生日、年龄。

1.选择C3单元格，输入公式“=IF(MOD(MID(G3,17,1),2)=0,"女","男")”，拖动鼠标，填充公式，即可判断该表所有员工的性别。

公式解析：身份证号的倒数第二位数为性别编码。当性别编码为奇数时，代表男性，为偶数则代表女性。使用MIN函数截取号码中相应的位数，再使用MOD函数判断所截取的位数的奇偶性。

2.选择H3单元格，输入公式“=DATE(MID(G3,7,4),MID(G3,11,2),MID(G3,13,2))”，提取员工的出生日期。然后拖动鼠标，向下填充公式。

公式解析：MIN函数可以截取出生日期信息，DATE函数可以将这些信息转变为日期数据。

3.选择I3单元格，输入公式“=INT((NOW()-H3)/365)”，可以计算出员工当前的年龄。然后拖动鼠标，向下填充公式。

公式解析：NOW函数返回系统当前的日期，然后INT函数对计算后的数据取整处理。

4.选择N3单元格，输入公式“=IF(M3<>"",YEAR(M3)-YEAR(L3),(INT((NOW()-L3)/365)))”，计算员工工龄。然后拖动鼠标，向下填充公式。

公式解析：首先使用IF函数判断员工是否离职，即M3单元格中是否填入了离职日期；然后通过YEAR函数把两个日期相减；或者先用NOW函数返回系统日期，然后使用INT函数向下取整。

5.QQ号加上“@qq.com”就是QQ邮箱地址，选择Q3单元格，输入公式“=P3&"@qq.com"”，就可得到员工的QQ邮箱地址。拖动鼠标，填充公式。

四、修饰表格

1.由于数据较多，使用冻结窗格将前两行和前两列进行冻结，方便查看表头与距离表头较远的数据的关系。选择C3单元格，用鼠标点击“视图”选项卡下“窗口”组中的“拆分”按钮，点击“冻结窗格”按钮，在弹出的下拉列表中选择“冻结拆分窗格”选项。

2.选择包含数据的A2：R100单元格区域，用鼠标点击“开始”选项卡下“样式”组中的“套用表格格式”按钮，选择需要的样式。打开“套用表格格式”，在表格工具下的设计选项中的 “表格样式选项”组里，取消选中“筛选按钮”复选卡，完成表格制作。

OK，创建员工档案记录表其实不难，但这里面涉及到很多知识点，包括，数据有效性、公式、冻结窗格、套用表格样式等内容，都是大家会经常用到的内容，一定要掌握。小伙伴们，还有别的什么想法，欢迎扫描下方二维码，到公众号下留言。

本文配套的练习课件请加入QQ群：902294808下载。

做Excel高手，快速提升工作效率，部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你！

扫下方二维码关注公众号，可随时随地学习Excel：

相关推荐：

Excel全自动调查问卷表怎么做最炫酷

7个例子全面掌握数据有效性

2019年全网最全—excel提取身份证信息合集！（建议收藏）-上篇

2019年全网最全—excel提取身份证信息合集！（建议收藏）-下篇

版权申明：

本文作者赋春风；同时部落窝教育享有专有使用权。若需转载请联系部落窝教育。