Excel财务小技巧之10「数据透视表2-从汇总到深度分析的核心技巧」

上一篇我们讲了数据透视表的基础操作，这一篇我们还是以“门店手机销售数据”为例，来具体讲下数据透视表的高阶用法。

一、值字段高阶应用：从“简单求和”到“财务多维度分析”

1. 值汇总依据

Excel 数据透视表默认的值汇总依据（值字段设置）不是固定的，而是由原始数据列的格式自动识别决定的。

核心规则可以总结为一句话：数字（数值）默认求和，文本默认计数。

默认的值汇总依据几乎可以满足70%以上的日常分析需要。

除此之外，本文分享几个其他常用的值汇总依据。

（1）非重复计数

【案例】统计各品牌手机销售的非重复客户数，以评估各品牌客户覆盖广度，适配客户分层分析。

【基础数据样式】包括：客户ID、手机品牌等核心信息

【操作步骤】

Step1：在插入数据透视表时，勾选“将此数据添加到数据模型”

Step2：将字段拖入到数据透视表后，“值汇总依据”选择“非重复计数”

【操作结果】

【核心要点】

一是在选择“非重复计数”时，一定要在创建数据透视表时就勾选“将此数据添加到数据模型”

二是数据透视表的值字段支持多次加入同一列，用于创建不同的值汇总依据（比如：此案例将“客户ID”分2次加入值字段，分别用于普通计数和非重复计数）

（2）平均值

【案例】计算不同手机品牌毛利额的平均值，衡量“平均一单赚多少钱”

【基础数据】包括：实际毛利、手机品牌等核心信息

【操作步骤&操作结果】

将品牌放在行字段，实际毛利放在值字段，并将值汇总依据改为平均值

【核心逻辑】平均值=合计（求和）/数量（计数）

——具体见上述check公式

（3）最大值/最小值

【案例】计算手机销售数据中标准单价的最大值和最小值，以分析价格带宽

【基础数据】包括：标准单价、手机品牌等核心信息

【操作步骤&操作结果】

2. 值显示方式

【设置方式】点击数据透视表的任一单元格，右击即可

值显示方式的种类有很多，本文主要介绍财务分析最常用的几类

【案例】以各品牌的手机销售为例

原数据透视表如下：

（1）列汇总的百分比/行汇总的百分比

【列汇总百分比】看每月不同品牌手机的销售占比。比如：1月销售中，苹果占多少、小米占多少

【行汇总百分比】看同一手机品牌，不同月份的销售占比。比如：

OPPO手机1-3月的销售分别贡献度有多少，哪个月卖的最多

（2）父行汇总的百分比

【设置条件】行字段需要为2个有构成关系的字段，比如：手机包含不同的手机品牌，同一手机品牌包含不同型号

【汇总结果】如：IQOO的1月销售占比为所有品牌的1.56%，而IQOO 13Pro占IQOO 1月销售总额的 53.41%

（3）差异百分比（环比）

【操作步骤】

基本字段：是环比的依据，比如2月与1月环比，则依据为销售月份

基本项为：比较的对象，一般选择上一个数据，即2月和上一个月（1月）相比

【结果】

【计算逻辑】

（本月销售额-上月销售额）/上月销售额

【注意事项】

如无计算时，显示为空；则环比会显示为#NULL!

（如下表的“摩托罗拉”）

二、计算字段：从“简单求和”到“财务多维度分析”

计算字段的逻辑是通过四则运算，直接在数据透视表中新增一些指标用于分析汇总，免去直接在Excel中不断插入列的问题

【案例】在手机销售数据对应的透视表中新增毛利率指标

【操作步骤】

Step1：点击数据透视表任一单元格，找到数据透视表分析临时选项卡，在“字段、项目和集”中，点击“计算字段”

Step2：维护需要计算的字段

填写名称、填写公式（可以从下方字段中选择；也可以直接打字）

之后点击添加，再点击确定即可

三、动态交互工具：切片器+透视图

1.切片器

切片器主要是用来做动态交互分析，我举个例子：

【具体操作】

Step1：先插入一个数据透视表，只把销售额放在值字段，合计金额3,970,190元

Step2：点击数据透视表任一单元格，再在数据透视表分析选项卡点击“插入切片器”

Step3：选择需要分析的字段，点击确定即可

会直接生成对应的切片器，拖动鼠标摆在合适的位置即可

Step4：动态交互分析

按住Ctrl，可以对同一切片器的选项进行多选：比如：品牌选择华为和苹果

之后可以继续选择档次、主推类型

会发现销售额的值变成了919,260元

对应：华为+苹果品牌 & 高端机 & 爆款+畅销款+滞销款 的合计销售额

Step5：持续选择不同的字段组合，得到不同的结果

【小Tips】如果想删除切片器，直接左击切片器，并按“delete”即可

2.透视图

透视图经常和切片器连用，实现动态交互的进一步可视化

【案例】我们将上个案例的数据透视表微调一下，加上月份

【操作步骤】

Step1：在数据透视表分析选项卡中，点击数据透视图

Step2：选择合适的图表，如：簇状柱形图等

Step3：数据透视图形成了雏形

此时我们继续点选切片器的选项，实现动态交互，会发现透视图会同步联动

Step4：透视图的样式调整

可以在设计选项卡中适当调整样式，如：去掉横向网格线等

四、表外取数：GetPivotData

GetPivotData是在数据透视表分析选项卡中的“选项”下拉菜单：

1.取消勾选GetPivotData

【案例】我希望在如下透视表的右侧加一列公式，使用：销售额-实际收款金额，以计算手续费金额

我在AU3单元格进行AS3-AT3的操作，公式会自动显示成GetPivotData开头的字样

之后，我按惯例下拉公式，发现金额没有变化，并不能符合我想计算每个销售员的收款对应的手续费金额

此时需要取消勾选GetPivotData

再进行AS3-AT3的操作，会发现结果显示没有问题

2.勾选GetPivotData

【案例】我希望在数据透视表外做一个汇总表,如下

以统计不同销售员的销售额

（1）静态场景下的比较

【方式1】用xlookup：查找区域来自数据透视表

用这种方法很容易得到具体的销售额数据，如下：

【方式2】用GetPivotData：

得到如上的数据，金额1,260,430元，与上述xlookup一致

但是，如果我们需要下拉，就必须修改GetPivotData公式，比较麻烦：

需要将最后一个参数从具体的数据“BJ00201”改为单元格相对引用“AX3”

此时，结果与xlookup的结果一模一样

（2）动态场景下的比较

【案例调整】我把原数据透视表的销售额和实际收款金额列调换顺序

GetPivotData的结果没有变化，依然正确

而xlookup则因为第2个参数始终为AX列，导致结果有误

这就是为什么，在数据透视表动态变化的场景下，GetPivotData更有优势的原因

五、Excel多表合并

Excel也可以实现多个表格的合并

【案例】1月-3月的销售额位于不同表，需要合并到一个表用于后续分析

【操作步骤】

Step1：先按Alt键，再依次按D和P键，调出数据透视表向导

Step2：点击创建单页字段

Step3：依次选定需要合并的区域，点击添加

都添加完毕后，点击下一步

Step4：确定数据透视表（即：合并后的数据）的放置位置，点击完成即可

【结果】可以看到1-3月销售数据在同一个数据透视表显示

【注意】基础数据格式规范

所有待合并的数据表，必须统一为 “单列维度 + 单列数值” 的明细格式。

✅ 正确：包含 “品牌列” 和 “销售额列”。

❌ 错误：写成 “品牌 + 1 月 + 2 月” 的交叉表形式（无法直接合并）。

表头一致（必须做到）：

每一张表的列标题名称必须完全一致（如都叫 “品牌”“销售额”），名称不一致会导致数据错位或识别错误。

内容匹配（宽松）：

不需要不同月份的品牌列表完全相同（如：2 月有摩托罗拉，3 月没有，不影响最终汇总）

六、数据透视表明细查看

1.数据透视表明细查看

财务分析场景下经常会涉及异动的下钻分析，比如：

销售员BJ00201的收款金额明显低于另外2位员工，希望能看下具体的明细

【具体操作】双击该员工实际收款金额对应的单元格，即：1,255,174.29

【结果显示】

2.如何禁止他人查看具体明细

我们在对外发送报表时，可能出于数据保密的要求，不希望他人查看具体明细，可以禁止他人查看具体明细

【操作步骤】点击对应的数据透视表，在数据透视表分析选项卡中，点击选项下拉菜单，再单击“选项”

在“数据”菜单中，取消勾选“启用显示明细数据”

【本期总结】

本期是数据透视表的进阶篇，具体包括：值汇总依据、值显示方式、计算字段、切片器、透视图、多表合并等

截止目前，用了2篇文章讲透了财务分析场景高频使用的数据透视表相关知识点，希望能对大家有所帮助

下期将聚焦财务实操高频的IF相关函数，包括IF、IFS等，教你用条件判断函数解决财务数据复杂计算等问题，进一步提升财务分析效率！