【特邀】HR必备高阶Excel技巧

注意，纯干货在文章结尾，千万不要忽略哦。

在所有工具中，用的Word、Excel、PPT是最多的，而在这三个中，我认为Excel的掌握是HR的重中之中，原因有两点：一是能提高HR的工作效率，二是人力资源数据化管理是未来发展的一个重要方向，用Excel可以满足它的需求，来处理HR工作中产生了各种数据，以支撑工作决策和公司战略，例如用仪表盘来进行决策(，是时候建立数据仪表盘了>和<人力资源仪表盘心得及设计思路>)。

Excel的技巧太多了，分享操作技巧到不如分享一下学习Excel的技巧，了解我的人知道，我经常说的一句话是：操作技巧远没有操作意识和基础知识重要。要想学习EXCEL技巧，我的文章和课程中涉及很多，可以自行查看。

下面来介绍一下学习Excel的技巧。

首先，我们要有学习Excel的场景

对HR来说，学习EXCEL的场景就是我们的工作场景。职场中不同职业的人，要紧密结合着本职工作来学习EXCEL，只有这样才能学的最快。毕竟在工作中，你所用的EXCEL知识都是为了解决工作，非解决工作的知识，平时不用忘的也快，除非你把EXCEL知识融会贯通，刻在脑子里才不容易忘记。

所以，与其开始就学EXCEL各种知识，到不如针对自己的本职工作来学习EXCEL，以此作为切入点，逐渐的掌握越来越多的EXCEL知识。

EXCEL本身是没有任何属性的，它可以用在各个工作场景，但是与HR工作结合，它就具备了人力资源管理的属性，有时候我们就不能用通用技能来解决HR的工作问题，需要从人力资源工作本身出发来解决问题。

举两个例子：

会数据透视表的人应该知道，我们可以方便的得出月平均工资，但是这样是正确的吗？来想一想月平均工资的计算公式，分母是月平均人数，而并非所有发工资人数之和，这样的话用数据透视表得出月平均工资肯定是不正确的。(可参考我的课程<工作中常见EXCEL错误应用及解决办法>)

日期提醒，这项操作技能在网上能搜到很多，绝大多数是是通过条件格式提醒或者辅助列提醒，但放到工作环境中，这样提醒能有什么实质性的作用？日期提醒的最大作用是要给我们的庆生会、转正、劳动合同续签等工作提供支持，而这种支持都是要有员工信息来做一些工作的，只是提醒出来不行，我们还需要将这些员工的信息提取出来。那前面所说的这两种方法就非常不适用了，当然如果你的公司员工总数不到20人可自行忽略这一点。(可参考我的课程<日期提醒这样玩>)

此类的事例还有很多，不再一一举例。用这些例子说明，只是告诉大家学习EXCEL一定要有工作场景，而我们要明确学习的目的，是为了提高我们的工作效率，不能看到一个炫酷的技巧就要用到工作中，要看看应用这个技能是否能提高我们的工作效率，要知道工作是连续的，而技巧解决的问题只是某一个片断。(想看技巧或干货，点击这里<绝对干货，HR必备的15个EXCEL公式>)

其次，要告诉大家EXCEL知识学习遵循先少后多的原则

前面我的内容也提到了以工作内容学习EXCEL作为切入点，但这并不是先少后多的主要办法。

少，除了内容少之外，我们还要用这些用限的知识来解决我们工作中至少80%的问题。

我想很多人在网上看到这样的文章或课程：HR必学有N个函数，HR掌握这些技能就够了……诸如此类的标题总是吸引着我们，包括我写的文章或开发的课程标题也有这样的，但是HR到底要掌握多少基础知识，到底要学习多少个函数才够呢？我最开始学的时候也迷信这个，以为学会了18个函数就能解决工作中大部分问题，结果一直到学成我才发现我的工作中足足应用了50多个函数，缺少一个我的工作效率会降低。那你们是不是也要学50多个呢？这个是不一定的，还是需要和自己的工作场景相结合。

说这些的意思只是告诉大家不要迷信于别人所说的掌握多少知识、学习多少函数就能解决工作大多问题，这就像小马过河，谁说的都有道理，但实际到底要掌握多少呢？这个是没有标准答案的，我们可以围绕着数据处理的一系列动作来学习。

我们要知道，工作中用EXCEL来处理数据，它是一系列的动作，包括数据录入、数据存储、数据汇总、数据分析展示、报表输出。除去针对这一系列动作的操作，其它的功能我们先不要急着去掌握。例如EXCEL的自选形状、SmartArt图片应用、快捷键应用、VBA知识等。我们只需要先掌握这些主干，逐渐的去掌握分支，不要胡子眉毛一把抓，到头来感觉需要学习的知识太多而打击自己的自信心(可以看我的电子书<资深HR教你如何用好Excel>)。

再次，也是本文的重中之重，告诉你EXCEL该怎样学习

EXCEL的知识很多，基础操作、函数公式、数据透视表、图表、VBA、POWER PIVOT、POWER BI等，要将这些知识学会将灵活应用于工作中，这需要很长的时间，毕竟学和用是两个不同的概念，而会用和用好也是不同的层次。

如果你按部就班的学习EXCEL的通用知识，我想你从学习到应用的周期是很长的。而我们变一种学习方法，那么这个周期会大大的缩短。

1、我们先要学习的是EXCEL的数据类型

对很多人来说，数据类型太简单了，它包括文本型数据和数值型数据，这个还有什么可学的。EXCEL是为数据处理而生的，由此可见它的各种功能都是围绕数据而开发的。如果你仔细思考，你会发现EXCEL的各项功能都是按数据类型进行区别对待的，例如自动筛选，针对文本、数字和时间、日期，它有不同的功能。所以在学习EXCEL的过程中，我们要始终以数据类型来区分EXCEL的各项功能，这样会让我们的学习更加的快捷。当然有的功能不会有明显的区分痕迹，这就代表它针对所有数据类型是同样的操作。

具体数据类型如何学习，大家看我的文章，里面有详细解释(传送门，请自行向后查看相关文章)。

2、我们要学会表格设计思路

表格设计思路是什么思路？就是设计最合理的表格结构，能让我们用最短的时间和最简单的办法来处理数据。

一般来说我们用的表格最主要有两类，一类是数据源表，一类是最终报表，这两类是我们必须要掌握的。数据源表经过各种操作可以得到最终报表，而我们学习和应用EXCEL的过程，就是把数据源表处理成最终报表的过程。

数据源表是什么样的呢？大家可以听本课程<数据高效处理的基础>，不再详细解释。

知道了数据源表不代表我们能设计出合理的数据源表结构，还是那句话，工作是连续的，EXCEL只能解决片断的问题，只有让各个片断联系起来才能形成连续的工作。

通常我们所用的数据源表结构有哪几种呢？基本的只有两种，一种是流水账式的表格结构，一种是基本信息式的表格结构。通过这两种再组合，又有流水账式+流水账式、基本信息式+基本信息式、基本信息式+流水账式三种系列表格。

什么是流水账式表格呢？流水账是指每天记载金钱或货物出入的，不分类别的账目。流水账式表格和流水账相似，它是依据日期或时间不分类别的来记录数据内容的，它至少包含3项内容，即日期或时间、人物(事物或事件)、数字。

例如下表是记录员工加班情况的表格，它能清晰的显示员工每天加班情况，也能汇总员工各种类型加班时长。如果让你输入这个表格，会不会有眼花缭乱的感觉，我们需要查找员工姓名，再查找日期，然后再行与列的交叉点输入员工加班时间。

但是如果我们用下面的流水账式表格呢？使用数据验证做下拉菜单，利用输入法的词库用首拼字母生成姓名、用组合键生成当前日期，我们需要做的，只是敲击几下键盘而已，比上述方法要简单很多，尤其公司员工越多的，这种方法的效果就越明显。

而用流水账式表格汇总数据也相当方便，数据透视表就可以快速解决。

流水账式表格在HR工作中应用很广泛，例如加班统计、请休假统计、员工培训情况统计、培训班情况统计、招聘计划统计等。

什么是基本信息式表格呢？它的主要代表就是员工信息表，一行数据将某个对象(员工)的基本情况详细列举。这种表格是职场中常用表格，也是很多人在设计表格时第一印象想到的表格结构。它还用于招聘员工信息统计、培训班情况统计等。汇总方法主要也是数据透视表。

系列表格是为了能把某方面工作的数据详细解决，如培训班基本情况表+员工培训情况，可以统计培训工作中的数据，如果再细分，我们还可以在系列表格中加上培训讲师评价表、培训成本统计表等。

作为HR，我想2种基本表格形式和3种系列表格，基本可以把我们的工作数据全部处理。而脱离了HR的工作内容，例如行政人员、仓储人员等，他们还有其它表格形式应用于工作中。

3、函数公式学习方法

将函数公式的学习方法单独列出来，是因为很多人都感觉函数公式很强大，学习EXCEL仿佛就是来学习函数公式的。

在本篇中我不可能告诉大家各个函数如何学习，仅提供思路供大家来参考。学习函数公式的基础有很多，例如绝对引用和相对引用、F9键作用、参数、函数嵌套等，这些不再讲解，毕竟是通用技能，在哪里都能学到，我的文章<掌握这10项基础，学习函数公式事半功倍>中也有讲到。今天我要讲的是你在外面所学不到的。

我们先要学习5个基本函数，分别IF、SUM、DATEDIF、VLOOKUP、ROUND。这5个基本函数要详细的来学习，从帮助文件中，将里面的说明讲解要逐字逐句的了解透彻，然后要学会函数的详细用法，以解决工作问题为主。

为什么是这5个基本函数呢？

学习了SUM以后，COUNT、MAX、MIN、AVERAGE等几个函数我们就可以学会了，知道了函数的意思，它们的用法大同小异，只需要记住每个函数的不同部分即可。

学习了IF函数后，我们可以结合来掌握AND、OR、TRUE、FALSE函数，这些在学习IF函数过程中是必然涉及的，而且都是简单的函数。

学习了DATEDIF函数后，我们对日期函数就有了大概的了解，这样关于日期的函数DATE、YEAR、MONTH、DAY、EDATE、EOMONTH、NOW、TODAY、WEEKDAY等我们就可以联想或者对照来学习了，它们的参数有相似之处，而同属于日期函数又可以组合来用。同时时间函数同样可以对照学习，如TIME、HOUR、MINUTE、SECOND等函数。

学习了ROUND函数后，我们可以将各种舍入函数一起来学习，具体可以参照我的文章<HR，这些EXCEL舍入函数你必须要了解>。

学习了VLOOKUP函数后，查找和引用函数可以一起来学习，如OFFSET、INDEX、CHOOSE、INDIRECT、MATCH等函数。同时一些函数组合也可以进行学习，如VLOOKUP+MATCH、IFERROR+VLOOKUP、OFFSET+MATCH、INDEX+MATCH、INDEX+SMALL+IF等。

而SUM和IF函数掌握后，SUMIF函数我们就可以方便的来掌握了，同时SUMIFS、COUNTIF、COUNTIFS、AVERAGEIF、AVERAGEIFS甚至SUMPRODUCT都可以一起学习，毕竟它们通用的知识还是很相似的，掌握了一个其它就很容易掌握。

另外文本函数是一个集合，我们可以单独来学习。如果自己把数据和表格规范了，这个在工作中应用不是太多，主要如通过身份证提取出生日期和性别。文本函数相对是比较简单的，如LEFT、RIGHT、MID、LEN、FIND、TEXT等。

另外我们要注意，有一些成对的函数可以一起来学习，例如TRUE和FALSE、ROW和COLUMN、LEFT和RIGHT、AND和OR等。

还有一些前缀或后缀类似的可以一起来理解，例如以IF或IFS结尾的：SUMIF、SUMIFS、COUNTIF、COUNTIFS、AVERAGEIF、AVERAGEIFS；以B结尾按字节处理的：LENB、MIDB、FINDB、LEFTB、RIGHTB等；以IS开头进行判断的：ISERROR、ISNUMBER、ISTEXT、ISEVEN、ISODD、ISNA等。

函数公式的学习，最终还是要多学多练多用，对每一个函数的作用，我们先期要死记硬背，针对什么情况用什么函数，至少在脑子里有这个印象。

4、其它知识的学习

基础知识的学习，我们通过四种基本数据类型把各项基础操作进行拆分来学习。

数据透视表，和我们的几种表格结构相结合，它是一个相当犀利的技能。如果对它的功能进行拆分，它就是字段拖拽+功能组合，把数据透视表的各个菜单了解清楚了，掌握基本的用法是很简单的。

图表应用，这个是用图表来展示数据，除了它的基本图形，在HR工作中我们用到的比较多的是柱形+折线组合来表示数量与百分比、数量与平均值的关系；用两个柱形来对比实际与预算之间的差距。具体图表应用，我有几篇文章讲解，<这种EXCEL柱形图，告别你的选择症>、<组合图表：带平均线的柱形走势图>、<做薪酬怎能不会这个Excel图表>。

文章所限，不可能对每种情况一一展开来讲，在我的课程和我的文章中，有很多EXCEL实际应用案例、应用技巧，请大家到我的主页中查看。专门推荐6篇HR应用Excel的文章：

《用EXCEL整理员工培训档案》

《用EXCEL规划求解工具进行人力资源优化配置》

《招聘管理工作中的数据统计》

《年薪制工资最优避税方案设计》

《EXCEL在宽带薪酬调整中的应用》

《如何快速将员工信息导入2号人事部》

最后给大家分享一个我收费课程中的技巧，如何快速统计人事月报数据。

如下表是截至2017年4月30日的员工状态。

我们统计如下人事月报数据：

具体操作步骤：

在“插入”选项卡，点击“现有连接”，弹出的窗口中点击“浏览更多”。

在员工信息表存放的位置点击“打开”。

出现的窗口中直接点击“确定”

然后在弹出的窗口中选择“数据透视表”和“新工作表”，点击“属性”。

在“连接属性”窗口中，选择“定义”标签，在“命令文本”的文本框中输入代码：

select 员工编号,部门,入职时间,离职时间,性别,'性别统计' AS 分类 from [数据$] where 员工状态='在职'

union all

select 员工编号,部门,入职时间,离职时间,年龄分段,'年龄统计' AS 分类 from [数据$] where 员工状态='在职'

union all

select 员工编号,部门,入职时间,离职时间,工龄分段,'工龄统计' AS 分类 from [数据$] where 员工状态='在职'

union all

select 员工编号,部门,入职时间,离职时间,学历,'学历统计' AS 分类 from [数据$] where 员工状态='在职'

union all

select 员工编号,部门,入职时间,离职时间,'入职','入离职情况' AS 分类 from [数据$] where 入职时间 between #2017-4-1# and #2017-4-30#

union all

select 员工编号,部门,入职时间,离职时间,'离职','入离职情况' AS 分类 from [数据$] where 离职时间 between #2017-4-1# and #2017-4-30#

注意：

SQL语句中各个字段和员工信息表中一致，有单引号的代表新字义的字段。

[数据$]代表存放员工信息表的工作表名。

#2017-4-1# and #2017-4-30#代表统计的时间段，可自由更改。

在出现的窗口中点击确定，最终出现数据透视表的操作界面。

将字段拖拽到各个区域，结果如下：