第17关 透视表的计算

第16关我们一起重温了数据透视表的基础知识，认识了字段列表，学会了对信息进行分类汇总和图表的布局美化。今天我们来学习对透视表进行计算：

1.值的汇总方式

数据透视表作为一款综合型的数据分析工具，它能够让多维度的数据汇总分析，接下来我们就通过实操案例来学习透视表中值的汇总方式：

打开【第17关-透视表的计算】，这是一个公司第一季度的销售情况详表。接下来我们就基于这些数据，以【销售员】为分析主体，进行数据汇总和计算。

首先打开“值的汇总方式”表格，插入数据透视表：

然后将“销售人员”字段拖至“行”区域，更改“行标签”为“姓名”：

“求和”

创建好数据透视表以后，我们先计算出每个销售人员的第1季度的总销售金额。这里需要进行【求和】计算：

点击已经创建好的数据透视表，将“销售金额”字段拖至“值”区域：

拖动完成后，透视表就自动生成了求和项，每个销售员第一季度销售的金额就计算好了：

“计数”

算出金额后，我们再来统计一下每个销售人员在第1季度的完成多少订单。要算出数量，就需要用到透视表的【计数】功能：

操作很简单，将“订单序号”字段拖至“值”区域，对应的计数项就生成了，我们只需要把名称为“订单量”就可以了：

“平均值”

透视表除了可以【求和】与【计数】还可以直接算出【平均值】！我们结合案例来计算销售人员每单的销售金额平均值：

首先再次将“销售金额”字段拖至“值”区域：

然后点击右侧的下拉图标，选择【值字段设置】，在计算类型中选择【平均值】，自定义名称为【平均销售金额】。具体操作如下：

点击【确定】后，每个销售人员的平均销售金额就出来了。不过显示出来的数值小数点后保留了好几位，这里我们取整数，操作如下：

求出了平均值以后，我们再来找出最大值和最小值。

“最大值”

接下来，我们找出每个销售人员成交的最大产品数量。

将“产品数量”拖至“值”区域，“计算类型”选择“最大值”，自定义名称为“订单最大数量”，具体操作参照下图：

“最小值”

类似地，找出每个销售人员成交的最小产品数量。

将“产品数量”拖至“值”区域，“计算类型”选择“最小值”，自定义名称为“订单最小数量”，具体操作参照下图：

通过上面实操练习，我们学会了透视表中最常用的五种值的汇总方式。

恭喜你已经掌握了最常见的五种值汇总方式。

2.值显示方式

学会了数据透视表的值汇总依据，接下来我们打开【值显示方式】工作表，学习用不同的值的显示方式呈现的透视表有什么区别。

阿狸已经根据【销售人员】【产品】和【销售金额】三个维度作出了初始的透视表：

但是现在的透视表都是一堆的数字，我们很难直观的看出每个数值之间的关系，也很难通过这些数据进行互相参照与对比。这个时候，我们就可以用百分比进行数值呈现。

“总计的百分比”

当我们想知道每个销售人员对应卖出的某一类产品占到总金额的百分之几的时候，参照案例已经给出的透视表：

假设所有人的销售金额总和【F10】为100%，我们想知道李华卖的电子阅读器【B5】占了总金额的百分之几，我们就可以用【总计的百分比】进行呈现。

具体操作步骤如下：点击【总计的百分比】透视表中任意单元格，单击右键，选择【值显示方式】中的【总计的百分比】：

这里Excel就将B5:F10区域中的每一个单元格都进行了除以F10再乘以100%的操作。

每个单元格对应的金额占总金额的百分比就都显示出来了。

“列汇总的百分比”

除了算出每个单元格对应总金额的占比，我们还可以算出每列的所有值为列的汇总的百分比，也就是算出案例中每个销售人员在所有销售人员中的销售占比。

点击【列汇总的百分比】透视表中任意单元格，单击右键，选择【值显示方式】中【列汇总的百分比】。具体操作如下：

按列汇总百分比设置好以后，我们就可以直观地看出每个销售人员最擅长卖什么产品，谁的销售金额最高啦：

“行汇总的百分比”

对应的，我们还可以按行来呈现汇总的百分比。单击右键，选择【值显示方式】中【行汇总的百分比】：

我们就可以直观的看出每类产品的销售占比了。

“百分比”

除了把具体的值拿来和总金额进行百分比计算外，我们还可以将任意的值作为基本项，拿其他值与之比较求出百分比。

比如为了看出第一季度的三个月的销售情况，我们可以把一月作为基本项，然后算出用二、三月对应的销售额是一月销售额的百分之多少：

具体操作如下：

点击【确定】后，我们就可以直观地看出二月的销售总额是一月的98.72%，三月的销售总额是一月的88.56%。也就能知道第一季度销售金额最高的月份为一月：

“父行汇总的百分比”

在实际工作中，很少能够用一张表就展示完所有的信息。当透视表需要表达多个维度的信息时，我们就可以用【父行汇总的百分比】进行展示。

观察一下【5.父行汇总的百分比】透视表。该透视表不仅显示了每个销售员第一季度销售金额占总金额的数字，同时还把每个月对应的销售金额汇总出来了。

这里我们就可以选择“值显示方式”中“父行汇总的百分比”展示出每个销售人员第一季度每月的销售占比，以及总销售金额在整个销售部门的占比：

“父级汇总的百分比”

除了进行父行汇总的百分比，我们还可以用【父级汇总的百分比】算出每个销售人员每月的销售金额在其总销售金额中的占比。

操作步骤很简单。只需要选择【值显示方式】中【父级汇总的百分比】，基本字段选择【销售人员】，就可以看到每个销售人员每月的销售金额在其总销售金额中的占比了：

恭喜你已经掌握了值显示的方式，在具体使用的时候结合需求，再多的信息也能直观地展出出来了！

3.计算字段

透视表不仅可以进行值的汇总方式也可以用百分比展示数据，还可以进行字段的计算。

“创建计算字段”

打开【计算字段】工作表，点击第一个透视表，我们先来试一试根据已有信息算出产品单价：

公式：产品单价 = 销售金额 / 产品数量

具体操作如下：

点击确定后，再把小数点位数设置为1位，单价就显示出来了。

找到了用透视表进行计算的【插入计算字段】页面，学会了除法计算。我们点击第二个透视表，做一个乘法题。

公司规定：销售提成=销售金额*1%。请算出每个销售人员的销售提成。

同样的，我们只需要点击第二个透视表，找到“分析”下的【字段、项目和集】选择【计算字段】。设置字段名称为”提成”，公式“=销售金额*1%”。结果如下：

每个销售人员的销售提成就算出来了。

“修改计算字段”

以上所有的操作都是在创建计算字段，我们也可以对已经创建好的字段进行修改。这里我们来尝试将上一题中的提成比例修改到2%

同样是在找到“分析”下的【字段、项目和集】选择【计算字段】。我们只需要把刚才写好的公式改为“=销售金额*2%”，然后选择【修改】就可以了。操作可参考下图：

“删除计算字段”

当然了，能修改也就能删除。我们点击第一个数据透视表，来尝试删除创建好了的“单价”字段。

具体步骤如下：

计算字段的创建、修改和删除你学会了吗？

“课程总结”

这一节课我们集中学习了透视表的计算：

4.下关预告

下一关我们将系统梳理动态数据源，让数据实现自动更新！

下载表格

第17关-17-50套excel经典图表模板