EXCEL数据透视表在数据分析中的

数字会告诉你事实的真相
22
添加自定义计算项
计算项：就是在某个字段下，对已有的项目进行计算，得 到新的项目 方法 先定位要插入计算项的字段 07版：“选项”——“公式”——“计算项” 10版：“选项”——“域、项目和集”——“计算项” 案例11
数字会告诉你事实的真相
23
查看明细数据
双击某个汇总数据单元格（即值单元格），就可 以查看该数值的明细数据 当行标签和列表前内有多个字段时，双击某个外 层字段，就会折叠或展开该字段的下一级字段明 细
数字会告诉你事实的真相
16
刷新数据透视表
• 数据源变化后，可以刷新数据 透视表，更新数据 • 方法:单击右键，执行“刷新” 命令 • 辅助功能:刷新透视表还可以 自动调整列宽
数字会告诉你事实的真相
17
复制/移动数据透视表
复制透视表的正确方法是：
1、选择整个透视表 2、复制 3、粘贴 移动透视表 命令按钮
• 方法
值字段设置对话框 案例03 设置字段显示方式
数字会告诉你事实的真相
20
自动/手动组合项目
组合：就是把字段下的某些项目进行组合，使之成为一个 新的项目 三种数据的组合方式 文本：手动组合 数字：自动组合 日期：自动组合 方法： 快捷菜单中组合（创建组）命令
数字会告诉你事实的真相
21
字段下的项目名称也可以修改,比如把“当月汇 总”修改为“当月合计
方法:直接在单元格里修改
数字会告诉你事实的真相 8
显示/隐藏分类汇总
在默认情况下,每个分类字段都有分类汇总,也就是我们常 说的“小计”。 当不需要这个分类汇总时,可以隐藏起来不显 方法：
右键快捷菜单的分类汇总”****“命令
数字会告诉你事实的真相
14
设置数字格式
• 透视表的数字格式可以根据需要进行任意设置 • 方法：
选择区域，实用普通的单元格格式命令 在“值字段设置”对话框中进行，或者右键快捷命令
Байду номын сангаас
数字会告诉你事实的真相
15
显示/隐藏空数据项
• 默认情況下,分类字段中没有数据的项目会被隐藏,这样就 会对表格结构的完整性造成破坏。 • 可以一直显示没有数据的空数据项。方法:在“字段设置” 对话框中进行
• 报表布局有三种
1、压缩形式：所有字段压缩一列显示 2、大纲形式：所有字段分列显示，分类汇总在顶部 3、表格形式：所有字段分列显示，分类汇总在底部
数字会告诉你事实的真相
7
修改字段和项目名称
数值字段默认的名称一般为“求和项:销售额”、 “计计数项:销售额”,很难看,需要修改名称
方法：
在单元格直接修改 双击字段名称单元格,在对话框里修改
数字会告诉你事实的真相
18
设置字段分类汇总方式
字段可以有很多汇总方式，从而进行不同的分类汇总，分 析不同的指标
求和 计数 最大值 最小值
方法
快捷菜单命令 值字段设置对话框
数字会告诉你事实的真相
19
设置字段显示方式
• 字段可以设置为各种显示方式，从而得到不同的分析结果
普通 差异 差异百分比 百分比 按某一字段汇总 占同行汇总的百分比 占同列汇总的百分比
行总计
列 总 计
不需要时，可以隐藏起来，具体方法：
在“数据透视表选项”里设置 在命令按钮里设置
数字会告诉你事实的真相 10
合并居中字段标签
• 当行字段和列字段中有两个以上的字段时，外层的字段项 目可以合并居中显示。 • 方法：在“数据透视表选项”对话框中进行
数字会告诉你事实的真相
11
处理数据透视表中的错误值
数据量大,布局出现停止响应怎么办?
解决方法:推退布局更新,待全部布局完毕后,再统一更新
数字会告诉你事实的真相 5
设置透视表的样式
• 创建的透视表是默认的样式和格式,可以根 据需要选择一个喜欢的样式
・方法:在“设计计”选项卡中进行
・不需要默认的样式时,可以清除
数字会告诉你事实的真相
6
设置报表的布局
右键快捷菜单中的“数据汇总依据”命令 或者在值字段对话框中进行
数字会告诉你事实的真相
13
对数据进行手动/自动排序
• 列字段和行字段会按照一般的排序规则进行了 自动升序非序,不一定满足实际需要
• 可以手工进行重新排序,方法:拖动字段项目单 元格边框
• 也可以进行自定义排序
先定义一个自定义序列 再进行自定义排序
1.选择 公式 --定义名称。 2.在弹出的对话框中输入名称， 3.在引用位置后面输入
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),5)
然后点确定，这样一个动态的数据源就建好了。
数字会告诉你事实的真相
27
数字会告诉你事实的真相
24
注意数据的外泄
双击数值单元格，可以查明细数据，由于这个功 能的存在，当涉及敏感数据时，最好不要把透视 表发给外人。
解决方法
把透视表转换为普通的数值单元格(选择性粘贴)
数字会告诉你事实的真相
25
以多个二维表格数据创建
当多个工作表数据是二维表格（即数据区域的第 一列和第一行是文本，其它的都是数字）时，可 以使用多重合并计算数据区域透视表进行合并。
数字会告诉你事实的真相
4
透视表布局的几种方法
基本布局方法：
1、在右侧的字段列表和四个小窗格中拖放字段,或者勾选字段 2、注意:勾选字段时 ・如果字段是文本型,自动出现在“行标签”区域 ・如果字段是数值型,自动出现在“数值”区域
经典布局方法
1、即03版的布局方法,直接在字段列表和透视表之间拖放字段 2、恢复经典显示模式的方法: 右键→“数据透视表选项”→勾选经典布局模式”
号、列标和页字段。每一次改变版面布置时，数据透视表 会立即按照新的布置重新计算数据。
数字会告诉你事实的真相
2
数据透视表的格式
了解数据透视表结构 了解几个术语 透视表布局的几种方法 设置透视表的样式 设置报表布局 修改字段和项目名称 显示/隐藏分类汇总 显示隐藏表格总计
EXCEL数据透视表在数据 分析中的高级运用
数字会告诉你事实的真相
1
何为数据透视表
数据透视表(Pivot Table)是一种交互式的表，可以进行 某些计算，如求和与计数等。所进行的计算与数据跟数据 透视表中的排列有关。 之所以称为数据透视表，是因为可以动态地改变它们的版
面布置，以便按照不同方式分析数据，也可以重新安排行
数字会告诉你事实的真相
9
显示/隐藏表格总计
• 每个透视表都有两个总计：行总计和列总计
行总计：透视表右侧一列或几列的总计 列总计：透视表底部一行的总计
求和项 :销售额 商品 2 商品 1 家用 办公用 城市 北京 3,362,141 1,293,403 贵阳 2,115,557 707,995 杭州 3,230,185 725,622 合肥 1,873,484 512,398 昆明 1,590,260 1,125,904 武汉 3,120,159 800,830 郑州 2,895,592 1,259,110 总计 39,874,196 12,078,964 总计 4,655,544 2,823,552 3,955,807 2,385,882 2,716,164 3,920,989 4,154,702 51,953,160
• 如果在数据源中有错误值单元格，或者透视表中 使用计算公式，那么数据透视表中也会出现错误 值。此时需要将这些错误值进行适当处理（比如 不显示） • 方法：在“数据透视表”对话框中进行。
数字会告诉你事实的真相
12
改变字段的汇总方式
• 在默认情况下，数值型字段的计算方式是“求和”，文本 型字段的计算方式是“计数” • 可以根据实际情况和需要，更改字段默认的计算方式。 • 方法：
添加自定义计算字段
我们还可以在数据透视表中添加计算字段，以便得到新的 分类汇总和分析指标 计算字段：在透视表中创建公式，对现有的字段进行计算， 得到的一个新的字段（在数据源中没有） 方法： 07版：“选项”——“公式”——“计算字段” 10版：“选项”——“域、项目和集”——“计算字段” 案例
具体方法 快捷键 ALT+D+P (P要按2下) 案例
数字会告诉你事实的真相
26
创建动态数据源的数据透视表
普通命令(插入透视表)的方法创建的数据透视表,其数据源 是一个固定的单元格区域,如果数据源发生变化(主要是增 加),新增加的数据不会自动添加到透视表.
解决数据变化的方法:使用动态名称(OFFSET)
合并居中字段标签 处理数据透视表中的错误值 改变字段的汇总方式 对数据进行手动/自动排序 设置数字格式 显示/隐藏空数据项 刷新数据透视表 复制移动数据透视表
数字会告诉你事实的真相
3
了解数据透视表的结构
• 透视表分4个区域 行标签（行字段）：用于分类字段下的项目，垂直排列 列标签（列字段）：用于分类字段下的项目，水平排列 数值（数据项）：用于汇总计算字段下的数据 报表筛选（页字段）：用于筛选整个数据透视表