用活函数 员工信息巧管理

1. 实际需求

公司员工的原始登记表是按照员工入职时间依次登记的，每个部门使用不同的起始编号（需要保留离职员工的工号）。现在需要在I 列中计算起始工号段，L列中计算出分配给新员工的工号，M和N列中统计离职人数、在职人数，然后以此为依据和O列核准人数对比，并在P列中判断员工人数是否超编（图1）。了解了需求后，下面根据这些需求使用不同的函数来统计和分析。

2. 分配工号

由图1可以知道，由于每个部门的起始编号不同，因此需要先根据部门在C、D列中找出该部门的起始编号，然后根据结束编号+1，求得入职该部门的新员工的工号数据。

首先计算部门起始编号的数据。定位到J2单元格并输入公式“=MINIFS（$D$2：$D$99，$C$2：$C$99，H2）”，然后下拉公式到J6单元格，这样在J列中即可显示每个部门的开始编号（图2）。

公式解释：

这里使用MINIFS函数判断最小编号数值，$D$2：$D$99（绝对引用）是确定最小值所在的区域（即每个部门的工号牌数据，请根据实际情况设置区域）;$C$2：$C$99为用于条件的单元格区域（即与工号对应的部门）;H2用于确定最小值的条件（即指定查询的具体部门）。

继续定位到K 2单元格并输入公式“=MA XIFS（$D$2：$D$99，$C$2：$C$99，H2）”。这里使用MA XIFS函数查询对应部门员工的最大编号数值，公式的含义与MI N I FS函数类似，下拉公式后可以将对应部门的结束编号查询出来（图3）。

知道了每个部门的起始编号后，对应部门的新员工编号就是“目前最大编号+1”，因此定位到L 2单元格并输入公式“= K 2+1”并下拉即可。员工的号段数值则可以定位到I 2单元格，输入公式“=J2&-K 2”并下拉获得（图4）。

在实际使用中，HR部门只要在A、B、C列中输入员工的入职信息，然后根据L列显示的信息，按照不同部门填入分配的工号数字即可。填入新员工的信息后，上述的数据会同步更新，后续员工的数据录入操作类似。

3. 统计离职和

在职人数在原始数据中，离职人员在E列进行了备注，因此可以使用COUNTIFS函数来统计。定位到M2单元格并输入公式“=COUNTIFS（$E$2：$E$ 3 4，"已离职"， C2：C34，H2）”，下拉后即可完成统计（图5）。

公式解释：

这里使用COUNTIFS函数执行条件计数，$E$2：$E$34为计数区域1，计数条件1为包含“已离职”字符的单元格;计数区域2为$C $2：$C$34（即部门区域），计数条件2则为H列显示的部门。这样可以根据部门计算出对应的离职员工人数。

继续定位到N2单元格并输入公式“=COUNTIFS （$C$2：$C$34，H2）-M2”，下拉填充。同样使用COUNTIFS函数对部门人数进行统计（统计该部门总的人数），然后将其和离职人数相减，即为剩余的在职人数（图6）。

4. 核编人数

先在O列中输入公司规定的部门核编人数，接着在P2单元格中输入公式“=IF（N2<=O2，"正常"，"超編"）”并下拉填充。这里使用IF函数对N列和O列（即在职和核编人数）进行比较，并根据结果显示“正常”还是“超编”（图7）。

为了能够更醒目地显示超编结果，选中P列数据，依次点击“开始→条件格式→突出显示单元格规则→文本包含”，设置包含“超编”的单元格自动填充红色底纹。

5. 实际使用

完成上述的操作后，员工信息就一目了然了。由于上述的数据都使用了公式引用，这些数据会根据员工信息的变化同步变化，无论是录入新员工编号、查看离职和在职员工人数，还是判断是否超编（如超编需要裁员）都非常清晰（图8）。