B端产品经理讲述初阶产品Excel80%职场需求，接上篇

文章为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默认的透视表格式，主要的特征是：无论叠加多少个行字段，都只占一列，分项汇总显示在每项的上面。大纲形式：主要特征是：有几个行字段就会占几列，即行字段会并排显示，分项汇总显示在每项的上面。如下图，有部门和小组两个行字段，大纲形式的布局会占两列，而压缩方式只占一列。表格方式：是很常见的一种形式。主要特征是：与大纲形式一样，有几个行字段就会占几列，行字段会并排显示，有几个行字段会占几列；与大纲形式不同的是，表格方式是有表格的（如下图图示）且分项汇总显示在每项的下方。

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

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

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

1. 按时间组合

在工作场景中，一般会获得时间范围(几个月)的天维度的明细数据为一个数据源，在借助透视表进行预测。比如：想看本月份各月各部门电话量的完成状况，对于这一意愿，可对日期进行组合。

具体步骤如下图图示：选中透视表日期列任意单元格，右击，选择【组合】，进行分组设置，可依照需求变更起始日期，从完成日期列表中选取分类维度“月/季度/年……”教案格式 空表，即可生成我们必须的数据格式。

2. 按数值组合

如果是统计得分情况或年龄分段情况等数据列数值的分布状况，就必须用到透视表的数值分组，选中“分值”列的任意单元格，右键选取“创建组”，在组合中能设定起始跟结尾或者步长。

3. 文本分组

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

四、技巧与建议1. 透视表技巧

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

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

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

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