文章为B端产品经理根据入职1年来工作所需，结合Excel线上课程所学，总结沉淀的数据透视表文档。
作者尝试用两篇（函数篇+透视表篇）讲述初阶产品Excel80%职场需求，接上篇，本篇讲述数据透视表部分。
本文将从如下图所示 基本操作、布局与格式、组合功能、技巧四个部分进行讲述，只要用心掌握以下四个部分，基本解决80%的难题，工作效率会有质的提升。
基本操作包括【创建透视表基本操作】、【插入计算字段】、【使用切片器】、【数据源更新与更改】四部分。
1. 3步创建数据透视表
数据透视表是交互式的汇总和分析数据的工具，简单来说就是把明细表进行分类汇总的过程，可以使用户通过简单的拖拽操作，完成复杂的数据分类汇总，可以说是Excel中最实用、最常用的功能。所谓“透视”，即从数据背后找到联系，从而将看似杂乱的数据转化为有价值的信息。
结合函数理解数据透视表及其基本操作：
举个例子：A公司销售的KPI要求为“每天30秒以上电话数/人为25个”，现需统计12月2日各部门KPI完成情况。如下：左图为A公司12月2日销售外呼数据，右图为需要获取的A公司销售部各部门KPI完成情况（部门完成率=部门实际值总和/部门目标值总和）。
根据上篇内容，我们可以用SUMIF函数，快速计算出销售一部、销售二部、销售三部的30秒电话数和30秒电话数目标值，最后在完成率列输入公式=I4/J4计算出完成率，如下：
函数计算的方法，虽然也能较快的计算出我们需要的结果，但效率不高（这里毕竟只是计算2个值，如果我们计算的值较多时效率问题会更明显）。在此，我们介绍快速按需求获取汇总数据的方法-数据透视表：
第一步：选中目标数据：选中目标区域任意单元格，Ctrl+A。
第二步：插入数据透视表：【插入】选项卡-【数据透视表】，【创建数据透视表】弹窗：“选择要分析的数据”（默认即可）和“选择要放置数据透视表的位置（现有工作表）”。弹窗选项说明如下：
（1）【请选择要分析的数据】：如针对工作簿内数据分析，则点击“选择一个表或区域”（因为我们插入数据透视表前，已经选择区域，所以一般情况下，此处默认即可，也可以进行修改）；针对非工作簿内数据分析，则点击“使用外部数据源”。
（2）【选择要放置数据透视表的位置】：如数据字段数较多且分析较复杂的情况下，一般选择“新工作表”，会在新的“sheet”中生成透视表；数据字段数较少的情况下，可选择“现有工作表”，在当前“sheet”中所选区域生成透视表。
第三步：选择字段，生成透视表：从【字段名称】列表里，点击字段拖拽至“筛选器、列、行、值”当中，如下图所示：给到的案例比较简单，只需要【行】和【值】两部分即可获取需要的结果。透视表结构如下图，详细说明如下：
（1）行、列、值的应用：数据维度方在行，自变量放在列（因变量为值）。
如果我们想要看的是每一天，不同部门“30秒电话量总和”的差异，则日期是我们查看的数据维度（按照日期把数据拆分组，一个日期为一组数据，占到一行，呈现出来的就是有多少个日期就会有多少行数据）；部门是自变量；而“某天某部门的30秒电话量总和”是因变量。
如果我们想要看的是同一部门，不同日期“30秒电话量总和”的差异，则部门是我们查看的数据维度（按照部门把数据拆分组，一个部门为一组数据，占到一行，呈现出来的就是有多少个部门就会有多少行数据）；日期是自变量；而“某部门某日期的30秒电话量”是因变量。
（2）值：汇总方式和显示方式介绍如下
汇总方式：如上所说的因变量-某日期某部门30秒电话量总和，即对数据源表的数据进行求和，求和就是汇总方式。常用的主要是求和和计数；
数据显示方式：即将汇总出来的结果以某种方式展示，从而更清晰的看出数据之间的关系和逻辑。常用的主要是总计的百分比和父行汇总的百分比；
①总计的百分比：个体占总体的情况，每一项分类汇总的值占总计的百分比。如：“某日期某部门30秒电话量总和” 占“数据源中所有日期、所有部门30秒电话量总和”的百分比
②父级百分比：个体占局部的情况，局部百分比。某列*行字段的汇总结果/行字段*所有列（即父行）的汇总结果（如上左图：12月2日销售二部的30秒电话数之和/12月2日所有部门的30秒电话数之和）
需求的结果数据一般情况下都可使用“值”字段生成，因为“值”字段中的汇总方式包含了使用频率较高的通用的计算功能，但有一定的局限，而计算字段极大扩展了数据透视表的计算功能。
比如原始数据表中有一列数据为目标值，有一列数据为实际值，那么在数据透视表中可以通过计算字段输入公式=30秒电话量/30秒电话量目标值，来求出完成率，方法如下图所示：
- 选中透视表任意单元格区域，右击
- 输入字段名称，输入公式：公式中的字段在“字段列表”选择字段插入
切片器功能同我们日常使用的数据报表（或产品在设计报表功能）时的筛选项是一样的，如下图所示，的数据报表中支持按日期筛选，2010版以上的excel版本的切片器功能也可以实现，方法如下。
选中数据透视表任意单元格，在【数据透视表工具】选项卡下的【选项】子选项卡下单击【插入切片器】的下拉按钮，在弹出的【插入切片器】对话框中勾选自己所需的内容即可。切片器对象的右上角，有两个按键，左边的是多选按钮，后面的按键是取消筛选的按钮。
4. 数据源刷新和更改
很多时候我们的数据源是不定期发生变化的，这就要求在数据透视表中也要体现出来，此时不需要重新创建一个新的数据透视表，刷新一下即可（原基础上修改，不增加行列的话）。
说明：大多数场景下使用的数据源均非外部数据源，本文仅介绍的为数据源为本工作簿的刷新方法。
1）手动刷新数据透视表：在数据透视表中的任意单元格区域鼠标右键，在弹出的快捷菜单中单击【刷新】命令即可；或，在【数据透视表】工具选项卡中，单击【刷新】/【全部刷新】按钮。
2）打开文件时刷新数据透视表：在数据透视表中的任意单元格区域鼠标右键，在弹出的快捷菜单中单击【数据透视表选项】命令，在【数据】选项卡小红，勾选“打开晚间时刷新数据”。
如果增加了行或者列，只是刷新是不行的，还需要更改数据源。
1）选中数据透视表中的任意单元格区域，在【数据透视表】工具选项卡中，单击【更改数据源】按钮，更改数据源区域。
2）将数据源表设置成“表格”，选中数据源，【插入选项卡】点击【表格】按钮，设置成表格。不管增加行还是列都不需要再去更改数据源，只需要刷新即可。（需要注意：只针对将数据源更改为“表格”之后建立的透视表有效）
首先介绍综合应用，1图看懂布局与格式的作用，3步解决在工作中的需求场景中的布局与格式问题：
在涉及到多个行字段的时，Excel生成的透视表的默认格式（如下图1左）是不满足我们查看和分析的需要的，一般都期望调整成常规的表格格式（如下图1右）。只需3步操作，操作说明如下（见下图2）：
第一步：选中透视表任意单元格，【设计】选项卡-【报表布局】-“表格形式”且“重复项目标签”；
第二步：选中透视表任意单元格，右击，取消勾选【分类汇总”…”】。
第三步：选中透视表任意单元格，右击，选择【数据透视表】-【显示】，取消“展开/折叠按钮”；
数据透视表共有三种布局形式，分别是压缩形式、大纲形式、表格形式，各有不同的特点。如下图所示：选中数据透视表中的任意单元格区域，【设计】选项卡，点击【报表布局】更改布局形式。
压缩形式：是Excel默认的透视表格式，主要的特点是：无论叠加多少个行字段，都只占一列，分项汇总显示在每项的上方。大纲形式：主要特点是：有几个行字段就会占几列，即行字段会并排显示，分项汇总显示在每项的上方。如下图，有部门和小组两个行字段，大纲形式的布局会占两列，而压缩形式只占一列。表格形式：是最常用的一种形式。主要特点是：与大纲形式一样，有几个行字段就会占几列，行字段会并排显示，有几个行字段会占几列；与大纲形式不同的是，表格形式是有表格的（如下图所示）且分项汇总显示在每项的下方。
-
标签项重复显示：如“一、布局”中所述，布局格式选择中，可进行标签是否重复的设置。
-
显示/隐藏分类汇总：选中透视表任意单元格区域，【右击】，勾选/取消勾选【分类汇总】即可。
-
合并行标签：选中透视表任意单元格区域，【右击】-【数据透视表选项】-【布局和格式选项卡】-【合并且居中排列带标签的单元格】（只对表格形式布局有效）
-
插入空行间隔：【设计】选项卡- 【布局】-【空行】-【在每个项目后插入空行】
-
取消字段前”+-“符：选中透视表任意单元格区域，【右击】-【数据透视表选项】-【展开/折叠按钮】
数据透视表中的组合功能，一方面能按照给定的跨度对“日期、数值等可计算字段””进行组合，比如组合出按年、季度、月、日，甚至小时、分……的汇总；另一方面，也可通过手动选择的方式，将文本格式的数据按照自定义的方式进行组合，比如组合出一线城市、二线城市等等。
通过组合功能将这些不同数据类型的数据项按多种组合方式进行分组，大大增强了数据表分类汇总的延伸性，方便用户提取满足特定需求的数据子集。
在工作场景中，一般会获取时间范围(几个月)的天维度的明细数据为一个数据源，在通过透视表进行分析。比如：想看本季度各月各部门电话量的完成情况，对于这一需求，可对日期进行组合。
具体方法如下图所示：选中透视表日期列任意单元格，右击，选择【组合】，进行分组设置，可根据需求更改起始日期，从完成日期列表中选择分类维度“月/季度/年……”，即可生成我们需要的数据格式。
如果是统计得分情况或年龄分段情况等数据列数值的分布情况，就需要用到透视表的数值分组，选中“分值”列的任意单元格，右键选择“创建组”，在组合中可设置起始和结尾以及步长。
如果是按地区统计或者个性化统计需求，可直接在透视表里面创建文本分组，在需要统计的列中，按住Ctrl键选择要组合的单元格，然后点击“鼠标右键”选择“创建组”即可，数据透视表就会按照我们所选定的内容进行组合，可以自行修改组的名称，例如改为华北大区。
（1）表头格式：表头只能有一行；字段不能为空（相同字段名会被自动添加序号，进行区别）。
（2）不能有合并单元格。如下图，3步处理数据源：取消合并单元格、定位空值、自动填充。
（3）数值类数据不能为文本格式。转换成常规数值的方法：使用“分列”功能进行处理，选中数据，【数据】菜单-选择“分列”（点击“下一步”-完成即可）。
（4）需对透视表数据再进行函数计算的，可将透视表转为普通表格：粘贴为值。
2. 3点学习建议（同上篇）
（1）工作中尝试承接涉及数据分析相关需求，有目标、具体场景的情况下学习速度会倍增。执行过程中，会遇到各种各样的问题，可通过快速百度查询、请教数据分析的同事等方式解决。
推荐理由：性价比高、实用性强，表现在：内容好、有小节/章节作业检验+有答疑、有班主任老师管理(时间节点)+PK/奖励（上课期间还因为外出1周，错过了1个星期没有结业）。
（3）输出exel学习经验并建立自己的知识速查表。学完不是自己的，只有消化吸收了才是自己的。对于技能学习只有孰能生巧一条路，学习完课程看似掌握了，但如果学完前期缺少实际场景的不断应用，很容易忘记。
所以学习完，建议输出exel学习经验，强化理解吸收；并形成自己的知识速查表，方便工作中使用快速查询及不断完善。
如上，有错误之处欢迎大家指正/交流。
|
点击联系发帖人
