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