自动化员工详细花名册

综合办公室办公自动化教程

作者：程有德

一、员工花名册信息

首先，我们要确定单位的花名册要有哪些信息，数据要如何分开，确保不同工作簿之间相互引用而不影响使用。在我工作中需要用的的补充信息有如下三点：

（一）是工资相关材料，用于关联在工资表上，防止漏给员工涨工龄工资。

（二）是员工工作状态，状态分为在职和离职，因为员工更换过快，而且又是可能要用到已离职员工信息，所以在不影响正常统计的情况下，我们划分在职和离职，通过更改工作状态，离职的员工信息背景自动变成红色以示提醒。然后我们只需要对“状态”列进行降序排序和筛选，就能够自动过滤掉离职人员信息，等需要调用时在筛选出来即可。

（三）工会统计生日信息，这一列是用来便捷提醒工会成员统计和为当月过生日的员工安排生日聚会。

1

2

下面，我们开始对表格一些内容进行逐列分析

第1列：序号自动更新

序号能自动更新，避免每次有人员离职都要再重新排序。

如左图所示，自动排序号的公式是=ROW()-3,其中row()的作用是返回当前行的数字号，

而-3的意思是减去上边不用排号的3行，最终所得数字就是1了，然后把下边的单元格

全部拉长和=ROW()-3单元格一样的就行了。这个主要是动手实验，大家可以只输入

=ROW()或者随意更改下后边的数字试试效果。

第5列：出生年月自动提取

出生年月这一列是用来自动计算员工的生日和摘取月份供工会管理成员安排给职工定期举办生日聚餐。

它的公式是：=TEXT(MID(J4,7,6),"0-00") ，公式中MID(J4,7,6)是用来从身份证号J4列中摘取第

7

3

位开始的6位数字，即是年和月如198806，”0-00”是用来规范年和月的显示模式。

当然，最重要的一步是把出生年月单元格的格式设置成下图格式

第6列：年龄自动运算

年龄这一列的用途是用来筛选某些时刻上级要求统计的信息，例如18-30、40-60岁的员工各有多少人。公式为=YEAR(NOW())-YEAR(E4)，其中NOW()函数是用来提取当前日期，YEAR(NOW())

是用来

提取当前日期的年数值，已知 E列是出生年月列，所以YEAR(E4)就是出生年的数值。

其实，第五列可以省去，通过公式的嵌套组合直接显示出年龄来，这个大家可以自己试验。

第18列：涨工龄月份

这一列的公式为=MONTH(O4)，即提取O4入职时间那一列的月份，其作用是用来提醒劳资员几月份该给这一员工涨工龄工资了。

第19列：工龄

这一列是本工作表中公式最复杂的，其作用是通过当前时间减去职工入职时间，来算出员工什么时候凑整该涨工龄工资，其实第18、19、20列是一个组合，其最终目的是能够简介明了的提醒劳资员给职工涨工龄工资。

其公式为=ROUND(((YEAR(NOW())-YEAR(O4))*12+MONTH(NOW())-MONTH(O4))/12,1)，乍一看这公式让人头疼，那么我们在下边对其进行逐层分解解释：

=ROUND( ,1)

((YEAR(NOW())-YEAR(O4))*12+MONTH(NOW())-MONTH(O4))/12

4

5

该公式中 O4为入职日期列，其他照搬公式即可，round （X,1）函数是自动四舍五入的公式，目的是保证计算结果和使用事物计算器结果一致，其中X 便是计算的对象函数，1表示小数点后保留1位。

第20列：涨工龄提醒

我赋予这一列的意义是文字提醒劳资员涨工资，和全列变成

色想配合，如果只让该列变色而不给予文字提醒的话，时间长了制表人自己也会忘记变色提醒的意义，起着重提醒的作用。

其公式为=IF(S6=INT(S6),"提醒","")，其中S6列为工龄列，INT(S6)是对该数值进行向下取整数，S6=INT(S6)的意思的当工龄列的数值为整数时，返回数据为“提醒”。

那么如何让全行变底色呢，这就涉及条件格式了，我们需要对全数据标设定一个条件格式来完成这一工作，具体曹组如下：

选中数据表，在excel/wps “开始”页里的格式下拉菜单中选

择条件格式，会出来下图

醒”，然后就是设定满足条件后显示的格式，即单元格底纹选择颜色样式。

6

第21列：状态，即职工在职状态

该列具体也是引用到条件格式，参照第20的教程执行即可，此列的作用是用于筛选出离职员工，并通过降序排列把离职员工排到最后而不影响整体序号，最后通过对该列的筛选将离职员工信息隐藏。

第22列：职工生日

函数内容为=MONTH(E4)，即提取E4出生年月列的月份数值以方便统计。

本表教程结束，EXCEL是很强大的工具，大家只要勤加使用和联系，会发现它能做很多东西。其实本表还有好几列可以直接去掉，将公式嵌套组合，大家自行摸索吧！

记住，公式最多只能嵌套7级哦。

7