Excel数据透视表篇：4个方面出发，解决80%职场需求

文章为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秒电话量目标值，来求出完成率，方法如下图所示：

1. 选中透视表任意单元格区域，右击
2. 选择公式-计算字段
3. 输入字段名称，输入公式：公式中的字段在“字段列表”选择字段插入

3. 使用切片器

切片器功能同我们日常使用的数据报表（或产品在设计报表功能）时的筛选项是一样的，如下图所示，的数据报表中支持按日期筛选，2010版以上的excel版本的切片器功能也可以实现，方法如下。

选中数据透视表任意单元格，在【数据透视表工具】选项卡下的【选项】子选项卡下单击【插入切片器】的下拉按钮，在弹出的【插入切片器】对话框中勾选自己所需的内容即可。切片器对象的右上角，有两个按键，左边的是多选按钮，后面的按键是取消筛选的按钮。

4. 数据源刷新和更改

（1）数据源刷新

很多时候我们的数据源是不定期发生变化的，这就要求在数据透视表中也要体现出来，此时不需要重新创建一个新的数据透视表，刷新一下即可（原基础上修改，不增加行列的话）。

说明：大多数场景下使用的数据源均 非 外部数据源，本文仅介绍的为 数据源为本工作簿 的刷新方法。

1）手动刷新数据透视表：在数据透视表中的任意单元格区域鼠标右键，在弹出的快捷菜单中单击【刷新】命令即可；或，在【数据透视表】工具选项卡中，单击【刷新】/【全部刷新】按钮。

2）打开文件时刷新数据透视表：在数据透视表中的任意单元格区域鼠标右键，在弹出的快捷菜单中单击【数据透视表选项】命令，在【数据】选项卡小红，勾选“打开晚间时刷新数据”。

（2）数据源更改

如果增加了行或者列，只是刷新是不行的，还需要 更改数据源。

1）选中数据透视表中的任意单元格区域，在【数据透视表】工具选项卡中，单击【更改数据源】按钮，更改数据源区域。

2）将数据源表设置成“表格”，选中数据源，【插入选项卡】点击【表格】按钮，设置成表格。不管增加行还是列都不需要再去更改数据源，只需要刷新即可。（需要注意：只针对将数据源更改为“表格”之后建立的透视表有效）

二、布局与格式

首先介绍综合应用， 1图看懂布局与格式的作用，3步解决在工作中的需求场景中的布局与格式问题 ：

在涉及到多个行字段的时，Excel生成的透视表的默认格式（如下图1左）是不满足我们查看和分析的需要的，一般都期望调整成常规的表格格式（如下图1右）。只需 3步操作 ，操作说明如下（见下图2）：

第一步 ：选中透视表任意单元格，【设计】选项卡-【报表布局】-“表格形式”且“重复项目标签”；

第二步 ：选中透视表任意单元格，右击，取消勾选【分类汇总”…”】。

第三步 ：选中透视表任意单元格，右击，选择【数据透视表】-【显示】，取消“展开/折叠按钮”；

1. 布局

数据透视表共有三种布局形式，分别是 压缩形式、大纲形式、表格形式 ，各有不同的特点。如下图所示：选中数据透视表中的任意单元格区域，【设计】选项卡，点击【报表布局】更改布局形式。

1. 压缩形式 ：是Excel 默认的 透视表格式，主要的特点是：无论叠加多少个行字段，都只占一列，分项汇总显示在每项的上方。
2. 大纲形式： 主要特点是：有几个行字段就会占几列，即行字段会并排显示，分项汇总显示在每项的上方。如下图，有部门和小组两个行字段，大纲形式的布局会占两列，而压缩形式只占一列。
3. 表格形式 ：是 最常用 的一种形式。主要特点是：与大纲形式一样，有几个行字段就会占几列，行字段会并排显示，有几个行字段会占几列；与大纲形式不同的是，表格形式是有表格的（如下图所示）且分项汇总显示在每项的下方。

2. 格式

1. 标签项重复显示 ：如“一、布局”中所述，布局格式选择中，可进行标签是否重复的设置。
2. 显示/ 隐藏分类汇总 ：选中透视表任意单元格区域，【右击】，勾选/取消勾选【分类汇总】即可。
3. 合并行标签 ：选中透视表任意单元格区域，【右击】-【数据透视表选项】-【布局和格式选项卡】-【合并且居中排列带标签的单元格】（只对表格形式布局有效）
4. 插入空行间隔 ：【设计】选项卡- 【布局】-【空行】-【在每个项目后插入空行】
5. 取消字段前”+-“符 ：选中透视表任意单元格区域，【右击】-【数据透视表选项】-【展开/折叠按钮】

三、组合功能

数据透视表中的组合功能，一方面能按照给定的跨度对“日期、数值等可计算字段””进行组合，比如组合出按年、季度、月、日，甚至小时、分……的汇总；另一方面，也可通过手动选择的方式，将文本格式的数据按照自定义的方式进行组合，比如组合出一线城市、二线城市等等。

通过组合功能将这些不同数据类型的数据项按多种组合方式进行 分组 ，大大增强了数据表分类汇总的延伸性， 方便用户提取满足特定需求的数据子集 。

1. 按时间组合

在工作场景中，一般会获取时间范围(几个月)的天维度的明细数据为一个数据源，在通过透视表进行分析。比如：想看本季度各月各部门电话量的完成情况，对于这一需求，可对日期进行组合。

具体方法如下图所示：选中透视表日期列任意单元格，右击，选择【组合】，进行分组设置，可根据需求更改起始日期，从完成日期列表中选择分类维度“月/季度/年……”，即可生成我们需要的数据格式。

2. 按数值组合

如果是统计得分情况或年龄分段情况等数据列数值的分布情况，就需要用到透视表的数值分组，选中“分值”列的任意单元格，右键选择“创建组”，在组合中可设置起始和结尾以及步长。

3. 文本分组

如果是按地区统计或者个性化统计需求，可直接在透视表里面创建文本分组，在需要统计的列中，按住Ctrl键选择要组合的单元格，然后点击“鼠标右键”选择“创建组”即可，数据透视表就会按照我们所选定的内容进行组合，可以自行修改组的名称，例如改为华北大区。

四、技巧与建议

1. 透视表技巧

（1）表头格式：表头只能有一行；字段不能为空 （相同字段名会被自动添加序号，进行区别）。

（2）不能有合并单元格。 如下图，3步处理数据源：取消合并单元格、定位空值、自动填充。

（3）数值类数据不能为文本格式。 转换成常规数值的方法：使用“分列”功能进行处理，选中数据，【数据】菜单-选择“分列”（点击“下一步”-完成即可）。

（4） 需对透视表数据 再进行函数计算 的，可将透视表转为普通表格： 粘贴为值 。

2. 3点学习建议（同上篇）

（1）工作中尝试承接涉及数据分析相关需求， 有目标、具体场景的情况下学习速度会倍增。执行过程中，会遇到各种各样的问题，可通过快速百度查询、请教数据分析的同事等方式解决。

（2）报名1门exel线上课程 （价格¥150左右），历时1个月。推荐起点学院的“21天Excel零基础俗称训练营” https://vip.qidianla.com/course/detail/if5qd.html ；

推荐理由：性价比高、实用性强，表现在：内容好、有小节/章节作业检验+有答疑、有班主任老师管理(时间节点)+PK/奖励（上课期间还因为外出1周，错过了1个星期没有结业）。

（3）输出exel学习经验并建立自己的知识速查表。 学完不是自己的，只有消化吸收了才是自己的。对于技能学习只有 孰能生巧 一条路，学习完课程看似掌握了，但如果学完前期缺少实际场景的不断应用，很容易忘记。

所以学习完，建议输出exel学习经验，强化理解吸收；并形成自己的知识速查表，方便工作中使用快速查询及不断完善。

如上，有错误之处欢迎大家指正/交流。

#相关阅读#

Excel函数篇：十个函数解决80%职场需求

 

本文由 @ 团团 原创发布于人人都是产品经理，未经许可，禁止转载

题图来自 Unsplash，基于 CC0 协议