 之前小薪推送了“资深表姐”Coco的群发工资条教程《3分钟学会邮箱群发工资条，让你瞬间秒杀90%的“资深”HR》后，很多HR宝宝表示想继续跟随Coco老师学习“数据透视表”。 因此，小薪再次邀请@Coco为各位HR宝宝量身打造了这篇数据透视表的详细教程，此处为Coco撒花。  各个事业部报上各个岗位人员需求，如何快速按照岗位汇总，以更有针对性选择招聘渠道，制作招聘计划？ 各个部门提交了各自的绩效考核表，如何快速汇总全公司各个分项的得分情况，以助力培训工作？ 对于员工考勤表，如何快速将原表中分行记录的员工的请假、迟到等情况合并计算显示为一条，来为工资表提供考勤数据？ 当月工资表，如何快速汇总计算每个部门的工资数据，来为财务部门提供工资成本数据？ …… 天天跟Excel表打交道，大家有想过我们用Excel记录数据的意义在哪里吗？是单纯的记录数据，还是用Excel来展示一张手工汇总的表格？  对于一个资深的HR，数据绝不仅仅是记录就可以了，更重要的是要通过记录的最原始的数据，汇总分析得到原始数据背后的数据，为工作中的各项决策提供数据支持，这才是我们记录数据的意义。 而对原始数据进行汇总分析，也绝不是依靠我们手工解决的，Excel给我们提供了一个强有力的工具——数据透视表。 数据透视表是自动生成分类汇总表的工具，可以根据源数据表的数据内容及分类，按任意角度、任意多层级、不同的汇总方式，得到不同的汇总结果，帮助我们看透数据背后的意义。 1 初识【数据透视表】 话不多说，先上表。 我这里有一个文件夹，里面存放了2016年每个月从某系统导出的社保缴费明细，每个月一张表，这张表里有月份、员工姓名、该员工社保缴纳的区域以及各项社保缴费的明细，这张表长这样：  像这样的表，这个文件夹里一共有12个，现在问题来了：老板跟我说，想要看一下16年各个地区公司承担的费用分别是多少。 现在你该怎么做呢？ 每个表打开算一下，每个区域公司各承担多少钱，然后把12个数据手动加起来，再做成一个漂亮的表给老板看。 一个上午已经过去了。  老板问题又来了，“那个表不错，我还想看看各个地区每个月公司分别承担了多少钱呢。” 然后你还得跑回去再次一张一张表打开重新分月计算，再手动汇总，终于再加班2小时后你做出了一张这样的表：  一边战战兢兢发着邮件，一边默默祈祷老板不要再提其他要求了……可是他是老板啊！！！不断给你抛出问题，注定你今天要跟这12张表死磕到底了。 怪我咯，没有早点教会你【数据透视表】，10分钟搞定老板所有分类汇总的问题。 第一步：准备【源数据表】 老板要的几个表其实全部来自于12张表，如果我们不想重复开关表格找我们需要的数据，那至少要保证你所需要的数据在一张表里，对不对？这样即使是进行手工汇总，也会相对容易得多。 就好比现在有好几个盒子，每个盒子里面装了不同颜色的球，现在老板一会说想要所有红色的球一会想要所有绿色的球，如果我们不想每个盒子都找一次，那就把所有盒子的球装在一个大盒子里面，老板想要什么直接从大盒子里找就行了。 所以，我们先要把12张表组合放在一张表里。 由于12张表的格式都一样，这里我们直接选择复制粘贴，好在只有12张表，工程量不会特别大，如果表格过多，则需要利用vba编程来实现这个过程了。最终我们完成了一张这样的表:  注意，这张表中，不要有合并单元格，不要有空白单元格。 这也是我们所有作为源数据表的表格的基本要求，不然后面的运算结果可能出错。 源数据表最好是在日常工作中就准备出来的。比如每个月我导出表来的时候，就复制到这张源数据表中，而不是在要用到的时候才批量突击处理，这也是良好的制表习惯。 第二步：插入【数据透视表】 先选中源数据表中所有数据（随便单击一个有数据的单元格，ctrl+A全选，选中的就是所有有数据的单元格了），然后点击表格上方菜单栏中的【插入】--【数据透视表】，像这样：  之后，页面会弹出一个设置数据透视表的对话框。 上半部分显示了我们选择的数据范围，不用做改动。 下半部分让我们选择存放数据透视表的位置。有两个选项，一个【新工作表】，一个【现有工作表】。这里我们选择默认的【新工作表】，当然我们也可以选择【现有工作表】。 但是作为一个有良好制表习惯的hr，是绝对不会允许源数据表里还有其他数据的。 选好之后，点击确定：  然后Excel在我们源数据表的左边生成了一个新的插页，我们先来看看这个新生成的sheet长啥样：  左边部分是透视表的展示区域，右边部分是透视表的操作区域。 在这张表里面，右边的操作区域就好比工厂里的车间，而这个操作区域的上半部分显示的各个字段，均来自于我们源数据表的各列的列名。 这各个字段就好比零件，我们想要在左边区域展示什么样的产品，就看我们把右上区域的各个零件在右下以什么样的方式进行组合。  第三步：【数据透视表】的具体操作 我们来看老板的第一个问题：想要看一下16年各个地区公司承担的费用分别是多少。 针对于这个问题，我们先提取一下关键字——【各个地区】、【公司承担的费用】。这两个关键字对应的我们源数据表中的字段是什么呢——【社保缴纳城市】、【单位合计】。 还有一点别忘了，老板要的是16年全年的，不是某一个月份的。 首先，我们选择上方的【社保缴纳城市】。 鼠标按住直接拖拽至下方4个白框中的【行标签】中，然后再选择上方的【单位合计】，拖拽至下方的【数值】。 拖拽完成后，大家可以看到左边的透视表展示区域已经有了数据，像这样： 上图透视表中第二列显示的【求和项：单位合计】，就是自动对12个月的【单位合计】按区域进行了求和。 这里数据透视表展示区域的表已经是个“半成品”了，要给老板看我们当然还要“包装”一下。 第四步：完善【数据透视表】 选中我们数据透视表，复制，在一张新表中，选择粘贴为数值。 这里不能直接粘贴。 直接粘贴就把数据透视表给粘过来了，而数据透视表是一个动态的表，随时可能因为老板“手欠”导致数据丢失或者改变。
粘贴过来是这样的： 这时候，我们把上表中，该调行距、调字体、改字段名称、加标题的，都按照自己平时的习惯改一下就好了，最后出成品： 以上就是数据透视表的操作步骤。 操作其实很简单，重要的是有没有那个意识和逻辑去排列组合你已有的“零件”，来加工成你所需要的“半成品”。这个意识和逻辑需要在后期的探索和练习中来获得。 第五步：再调【数据透视表】 下面我们来复习一下，来看老板的第二个问题：还想看看各个地区每个月公司分别承担了多少钱。 源数据表已经有了，老板要的数据都是来自源数据表，第一步、第二步我们都可以跳过，我们只是需要将各个零件再重新组合一次，形成一个新的半成品。 回到我们刚才的数据透视表，再次提取老板问题中的关键字——【各个地区】、【每个月】、【公司承担】。 对应的我们源数据表中的字段分别是——【社保缴纳区域】、【月份】、【单位合计】。 知道我们要哪些零件了，现在开始组装。 首先，我们将【月份】、【社保缴纳城市】分别拖入至【行标签】（注意行标签里面这两个字段的上下位置，大家可以试试如果【月份】在上面，透视表会是什么样子），然后将【单位合计】拖拽至【数值】。 这样就得到了左边的数据透视表了，就像这样： 大家可能会说了，这个透视表瘦长瘦长的，好难看啊，会不会被老板嫌弃啊。 不怕，我们让透视表里的【社保缴纳城市】作为行标签，而把【月份】作为列标签，【单位合计】不用变，还待在数值里面，透视表就从“站着”变为“躺着”了： 这样是不是好看了些，然后就开始美化吧。 2 进阶功能演示 【报表筛选】功能 细心的hr宝宝可能发现了，透视表操作区域一共有4个白框，行标签、列标签、数值都用过了，【报表筛选】是干嘛的呢？ 如果老板说我只要杭州区域的数据，或者我只要16年前3个月的数据，这时候【报表筛选】就派上用场了。 具体操作： 当我们把字段【月份】拖到【报表筛选】框里的时候，你会发现透视表上方多出来了一行。 这就是带有筛选功能的下拉列表了，点开小箭头看看： 然后我们就可以选择相应的月份，点击确定后，透视表展示区域出来的就是我们想要的数据了。 改变数值的【值字段设置】 1. 求最大最小值 还有些同学可能发现了，在透视表中，显示【单位合计】的那一列，前面有个“求和项”，意思是“以下数据是根据源数据表的相关数据求和得来的”。 我们都知道“求和”是Excel最基本的运算规则，除此之外，Excel还能求平均值、最大值、最小值等等，这在数据透视表中依然可以实现。 选中【求和项：单位合计】单元格右击，选择【值字段设置】，就像这样： 此时，会弹出一个对话框，我们选择需要的计算方式就可以了： 2、计数 针对于平均值、最大值、最小值、乘积等等，怎么运算，出来的结果是什么样的应该都好理解。 那么【计数】是什么意思呢？与求和对比来看，求和是把几个数值直接相加求和，而【计数】就是数一共有多少个数值。 一般来说，我们在分类汇总的时候，会对金额进行求和，而对姓名进行计数，以求一个特定范围内有多少人。 比如本例中，我们同时把姓名字段拉入【数值】框里，姓名就会以计数的形式呈现，就像这样： 这张表就清晰展示了各个区域每个月在缴人数分别是多少人，单位承担的汇缴金额是多少。 当然，我们也可以通过把【单位合计】的值字段设置从【求和项】改为【计数】，也能得到每个月有多少人在缴，就像这样： 注意： 源数据表中的数据，单元格格式为【数值】的，我们在数据透视表里汇总的时候默认的运算方式就是【求和】，如果格式为【文本】或者其他的，默认就是计数。 如果大家发现源数据表里都是数字啊，为什么还是默认计数了？那就好好检查一下源数据表，有没有空单元格，数字单元格中有没有混入实为“文本”的数字。 以上就是今天的数据透视表的教程了，数据透视表就是分类汇总的神器，以不变应万变，老板要看什么我们都能分分钟把报表甩他脸上，大胆去探索吧！ -END- 留言 互动 ”看完这期干货教程 你还有什么想学习的技巧 欢迎在下方留言 |
|
|
