编按：

制作人员统计表是HR的工作之一。性别、学历、司龄、工龄、员工人数等是构成人员统计表的基本元素，如何在繁杂的系统导出信息的基础上做一张简洁明了的人员统计表，我们一起来学习一下~

小李是公司人资部的专员，平时负责在人事系统中维护员工档案，也会做一些和人员信息相关的数据统计工作，比如下图这样的在职人员统计表。

这样的统计表往往需要从系统中导出人员明细，然后用Excel加工制作，导出的明细表往往有好几十列，看上去密密麻麻的。

如何利用这样的一份数据源做成需要的统计表呢？下面一步一步和大家介绍。

首先是基础数据整理，从需要的结果来看，需要用到以下信息：部门，性别，学历，年龄和工龄，因此只保留这五列信息就够了，其他的可以都删掉。

整理后的数据源如图所示：

是不是清爽了很多？

相对于统计结果来说，年龄和司龄是用了区间统计法，因此还需要对数据源加工一下，把年龄段和司龄段填进去。

年龄区间分成了三段，25岁以下、25-35岁、35岁以上，用两个IF嵌套或者用LOOKUP函数都可以实现。

IF嵌套：=IF(D2<25,"25岁以下",IF(D2<35,"25-35岁","35岁以上"))

LOOKUP函数：=LOOKUP(D2,{0,25,35},{"25岁以下","25-35岁","35岁以上"})

两个公式的结果是一样的，对于IF嵌套的用法，之前的教程有过介绍，也属于比较基础的知识了，这里就不再啰嗦了。

关于LOOKUP函数在这里的用法，我们结合司龄区间的公式详细解释一下。

司龄的划分比工龄复杂一些，分成了半年以下、半年-1年、1-2年、2-3年、3年以上五个区间，如果用IF嵌套的话就得4个IF，比较麻烦，推荐使用LOOKUP函数来实现。

对于新手来说，直接用LOOKUP做区间引用是有难度的，下面介绍一个比较容易学会的方法。

在表格的空白处做一个对照表（如下图）。司龄段是按照实际统计的需要填写，关键是司龄下限的填写，表示的是每个司龄段所对应的司龄的最小值。

有了这样一个对照表，再来写LOOKUP的公式就非常简单。

公式为：=LOOKUP(E2,$K$2:$L$6)

完成后选中公式中的$K$2:$L$6，按一下F9键，公式会变成这样的：

=LOOKUP(E2,{0,"半年以下";0.5,"半年-1年";1,"1-2年";2,"2-3年";3,"3年以上"})

这样即使删除辅助的对照表结果也不会受影响了。

至此我们完成了数据源的优化，删除了多余的无用信息，又添加了需要统计的信息。

现在就可以来完成统计表了。

经过这样处理的数据源，要形成最终的统计表只需要用到两个函数：COUNTIF和COUNTIFS。下面，分别来看看每个项目是如何使用公式的。

员工总数：=COUNTIF(数据源!A:A,A4)

按照数据源中A列的部门，统计出汇总表中对应部门的人数。

性别：=COUNTIFS(数据源!$A:$A,$A4,数据源!$B:$B,C$3)

按性别统计时涉及到两个条件，部门和性别，公式不难理解，注意公式中$的用法，因为这个公式既要考虑到下拉的情况，还要考虑右拉的情况，所以对于$混合引用的用法要求是比较高的。

学历：=COUNTIFS(数据源!$A:$A,$A4,数据源!$C:$C,E$3)

学历的统计与性别类似，只是将条件区域从B列改成C列，同样需要注意$在公式中的作用。

年龄：=COUNTIFS(数据源!$A:$A,$A4,数据源!$F:$F,I$3)

工龄：=COUNTIFS(数据源!$A:$A,$A4,数据源!$G:$G,L$3)

因为在数据源有了年龄段和工龄段，年龄和工龄的统计就变得非常方便。

总结一下：很多同学在遇到问题的时候，往往忽视了对数据源的处理，直接拿着系统导出的数据就开始干活，干扰项太多不说，有时候数据源里缺少了什么东西也不清楚。所以按照最终统计的要求对数据源做精简是非常有必要的。另一方面，是否有必要增加年龄段和工龄段，可能有的同学会说，不加这两个也可以用公式直接统计的，这当然没问题，但是公式就会更复杂一点。

最后，这个问题其实也可以用数据透视表来完成，不过数据透视表可能无法严格按照最终需要的顺序来呈现，总之是各有利弊。

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

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

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

相关推荐：

７个Excel小技巧，提高表格查看效率

Excel运用规范1：一个单元格只记录一条信息

快速整理不规范的Excel表格的7个公式

9条最实用的计算excel中关于日期的公式！（建议收藏）

版权申明：

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