第26关 综合实战应用1
你好，很开心又见到你。学到这里，你已经完成了入门课，数据处理、函数公式、透视表进阶、图表综合应用等5大板块。你已经比1个月前的自己掌握了更多实用的功能。优秀的你，请接受新的挑战：
接下来，我们即将进入有一定难度的实操——办公综合实战。
大家要清楚，实际工作往往是一个综合性问题，这就需要我们融会贯通，活学活Excel功能。我们这个板块的目的在于帮你跨板块，融会贯通知识点，从而灵活调用操作，提升办公效率。
今天，我们要帮一家上市公司的HR红姐做一份公司人力数据分析看板，然后给老板汇报。
这个看板的制作涵盖了数据整理汇总、透视表制作、图表制作的常见功能，等你做出这个看板，你就能真正掌握做一个看板汇报的全部流程和操作。具体涵盖以下知识点：
为了让你能主动地探索学习，我们在第26关-办公综合应用中，特意省去了部分具体操作步骤，希望你能依靠自己前1-25节所学，亲手完成这个综合应用案例。
好了，请先下载今天的练习表格：
1.案例说明
先别着急开始，我们先分析下案例：
红姐作为这家上市公司的HR，每年见证了太多职场人的职业变动。以她目前的水平，不必再参与具体工作比如招聘、发工资、后勤、审批，这些她完全可以委派给下属做。红姐要思考更重要的事情：目前公司的人员构成存在哪些问题，该如何跟领导反映呢？
关于汇报呈现，你还记得那句话么：“文不如表，表不如图”。红姐深知这个原理，决定用图来呈现公司一年来的人员变动。
为了给你一个明确的方向，先看看阿狸老师把公司人力数据分析看板做成什么样了：
拿到这样一张图，是不是觉得重点突出，内容清晰？但同时也摸不着头脑，到底怎么做出来？
别急，我们细细分析一下，其实也不难:
第一阶段，要根据看板，拆解出案例需要哪些数据;
第二阶段，根据所需要的数据，进行数据汇总;
第三阶段，制作对应图表，拼出看板。
2.案例拆解
我们仔细观察看板，可以发现需要以下7个维度的数据：
1、总人数（在职人数）
2、2019年入职人数
3、在职员工中，男女员工人数
4、各部门人数
5、不同学历人数
6、各年龄段人数
7、每个月入职和离职人数
拆解完案例，明确了制作思路，下面我们开始实操一步步做出看板。
3.数据汇总
打开刚才下载的练习文件，你是否看到了原始数据表呢？
根据刚才我们拆解的比对，我们发现是否在职字段、年龄字段在原始数据中并没有。我们需要对是否在职和年龄做一个简单处理：
1、在职人数-添加是否在职字段-根据离职时间判断
2、各年龄段人数-计算年龄
“补充字段”
首先，在I列添加一列【是否在职】。做出下图效果：
然后，请你使用if函数，判断员工是否在职，填在I列中。（提示：离职时间为空，则在职，否则离职）
做出来了么？别忘记，我们还需要计算年龄。请在J列添加一列【年龄】，你是否做出如下效果？
然后，请你用datedif函数，计算用户年龄。年龄是员工出生日期与制作看板的日期2019/12/20间隔的年数。（注意：日期参数2019/12/20需要加双引号“”）然后，做出如下效果：
补充完数据字段后，我们就对看板拆解出的7组数据依次进行整理：
这里我们要调用数据透视表功能，帮助我们快速分类汇总7组数据：
1.总人数
在原始数据中，创建完数据透视表后，以【是否在职】作为分类标签，对【姓名】进行计数，得出离职和在职的人数。操作如下图：
2.2019年入职人数
以【入职时间】作为分类标签，组合为年，对【姓名】进行计数，得出每年入职的人数。
3.在职员工中，男女员工占比
在第3组数据中，我们需要完成2步操作：
①插入【是否在职】的切片器，点击在职筛选。
②然后，以【性别】作为分类标签，对【姓名】进行计数，得出男女员工人数，设置值按总计的百分比显示，得出男女员工比例。(原始数据包含离职员工的记录，需要先筛选出在职人员的数据。)
具体操作如下：
然后，我们基于在职员工，对各部门人数、不同学历占比人数占比、不同年龄段人数占比进行汇总分析。
4.各部门人数
以【部门】作为分类标签，对【姓名】进行计数，得出各部门人数。
5.不同学历人数占比
以【文化程度】作为分类标签，对【姓名】进行计数，设置值按总计的百分比显示，得出不同学历人数占比。
6.不同年龄段人数占比
以【年龄】作为分类标签，对【姓名】进行计数，设置值按总计的百分比显示，得出占比。年龄组合起始：20，终止于60，步长10。（往下2步有效果图）
特别注意：以上三个表都是基于在职的员工统计的，所以是否在职切片器需要关联这三个透视表。最终做出如下的效果：
7.每月入职人数、每月离职人数
以【入职时间】作为分类标签，组合为年、月，对【姓名】进行计数，得出2019年每个月的入职人数。
以【离职时间】作为分类标签，组合为为年、月，对【姓名】进行计数，得出2019年每个月的离职人数。最终效果如图，你做出来了么：
完成了数据的汇总，下面就开始制作看板了：
4.看板制作
1.明确作图数据
在第2阶段，我们以数据透视表汇总好数据，在做图时，不少同学想着直接框选数据区域，来做出数据透视图。可阿狸老师不建议做数据透视图，为什么呢？有2个原因。
其一，数据透视图的部分字段无法删除，缩小比例还会模糊，在后期看板组合中会影响排版；
其二，数据透视图数据引用死板，不灵活。目前数据透视图引用的数据源是整个透视表，而我们实际需要透视表中的某些数据来做图，这就有很大的限制。
既然不能直接创建数据透视图，那怎么办呢？还记得我们24关利用透视表函数getpivotdata函数引用作图数据么，用这个函数来引用就能搞定！
为了让做出的图按一定规则排序，便于查看，这里需注意：
透视表4：各部门人数，需降序排序；
透视表5：不同学历人数占比，学历按从低到高排序
然后，我们依次对透视表4、5、6、7、8的数据源getpivotdata函数引用到下方的单元格中。操作效果如下：
最终数据引用的效果如下，你做出来了么？
接下来进入看板制作的第2步：
2.插入图表
应用我们图表综合应用篇中学到的知识，插入对应类型的图表，阿狸老师对4张图表选择如下，做出了如下效果，你是怎么选的呢？
图1：部门人员分析→条形图
图2：学历占比分析→条形图
图3：年龄分布→条形图
图4：全年入职离职对比→折线图
3.看板组合
接下来，我们就要将做好的图表，放置到看板对应区域，并调整大小了。你是否做出了下图效果呢？
这时，你是不是发现有三个数据看板是空着的？我们要从透视表中引用数据，并添加对应信息，操作如下（如果不会，请复习第24关的引用操作）：
然后合并单元格，调整字体大小，做出如下效果：
做到这里，我们就离成功不远了！
下面我们对看板进行美化，这里请你参考老师提供的配色，来对应填充看板背景色，设置三个数据看板单元格边框颜色，设置字体颜色为白色，将男、女的图片移动到对应位置，做出如下效果：
接下来，我们对4张图表进行美化：
先对图表区美化，应用我们之前所学操作：
①设置图表区边框颜色、填充为无
②字体颜色为白色
做出如下效果：
然后添加数据标签，删除多余信息，调整图标和图例布局，做出如下效果：
接着，调整条形图展示顺序为从上到下降序，效果如下：
同样的操作，设置其他两个柱形图，做出如下效果：
我们再调整一下图表的细节，调整间隙宽度为80%，操作如下：
同样更改其他两个条形图的柱子大小，做出如下的效果：
还记得商务美化么，我们做图表一定要知道自己要表达什么观点和结论。
所以，请你完成商务美化的3个操作：
①用对应颜色凸显男女比例；
②突出学历占比分析中，高中学历；
③ 全年入职离职对比，入职人数系列用亮色，离职人数系列用辅助色。
做出如下效果：
最后，插入文本框添加看板标题、制表信息。缩小F列和K列的宽度，就做出了如下效果，你做出来了么？
学到这里，恭喜你完成了公司人力数据分析看板的制作，也许过程中并不那么顺利，但就是在这个过程中，你完成了对和Excel技能的进阶！
不知不觉，你已经把数据汇总、看板的知识点已经实实在在地做出来了：
顺便告诉你个好消息：红姐也因为这份看板，获得了领导的赏识。
因为从这份看板，老板获得了几个信息，甚至是对公司发展至关重要的信息，并引发了老板的思考：
1.销售一部人数过多，销售二部是否需要平衡？
2.公司高中学历占比较高，是否有必要提升招聘门槛？
3.8月份离职人数最多，是什么原因导致的？
你可别小看这些思考，老板最重要的工作在于拿到准确的信息，然后做出有效的决策。红姐的这个看板，就是帮老板拿到了准确且直观的信息。
这里，老师也提醒大家：Excel是工具，既然是工具一定要结合实际工作使用才有意义。
换句话说，单纯的掌握快速操作和高大上的图表并没有意义。所作图表一定要跟实际工作关联度高，这样你做出的表格才真正有用，才能放大excel的价值！
5.下关预告
恭喜你完成公司人力数据分析看板，轻轻扫描下方二维码，就可以获得价值99元的通关礼包！
下关我们将进入办公综合实战2。
办公综合实战2综合了数据处理、函数公式，非常烧脑，但一旦完成，就证明你已经可以灵活应用函数了，成为了Excel达人，阿狸老师等你来挑战哦！
