数据透视表的用法
数据透视表的用法 . .......................................................................................................................... 1
1：什么叫数据数据透视表？ . ........................................................................................................ 2
2：制作数据透视表 . ........................................................................................................................ 4
3：源表的整理 . ................................................................................................................................ 8
4：透视表的结构 . .......................................................................................................................... 11
5：值的计算方式 . .......................................................................................................................... 13
6：报表布局 . .................................................................................................................................. 15
7：添加和删除汇总项 . .................................................................................................................. 17
8：穿上漂亮的外衣 . ...................................................................................................................... 19
9：修改引用数据源 . ...................................................................................................................... 21
10：数据透视表的刷新 . ................................................................................................................ 22
11：数据透视表的移动和删除 . .................................................................................................... 23
12：筛选数值项 . ............................................................................................................................ 24
13：固定数字和表格格式 . ............................................................................................................ 26
14：选取......................................................................................................................................... 27
1：什么叫数据数据透视表？
在学习excel 数据透视表之前，我们有必要了解它的概念。什么是数据透视表呢？
理论上讲，数据透视表是excel 提供的一种交互式的强大数据分析和汇总工具。兰色把它总结为：数据透视表是可以把一个明细表进行分类汇总，而且可以随意改变汇总模式的一种工具。
明细表? 分类汇总？随意改变模式？工具？神马东东？听起来还是有点抽象，还是看图比较真观。
销售明细表：
根据地区汇总
根据地区和销售员汇总
根据地区、销售员、商品名称汇总
根据月份、地区、销售员、商品名称汇总
好吧！因为篇幅关系只展示这几种汇总方式。也许你认为我函数水平高超，我会说我一个函数和公式也没用；也许你会说你是不是用了传说中的宏，对不起，我没写一句代码。以上。。。全是excel 数据透视表实现的，而且展示的也只是数据透视表最最最。。基础的汇总功能！！
2：制作数据透视表
做数据透视表之前，我们需要了解以下两个问题：
1 什么样的表格需要做数据透视表
数据透视表的用途如果细数会有很多，但最基本、最常用的是“分类汇总”，所以当我们需要对一个流水账式的明细表进行分类汇总时，就需要使用数据透视表。数据透视表的源数据即是“明细表”。如第1讲中的销售明细表。 2 什么样的格式才能做出数据透视表
在制作数据透视表，需要注意以下几点：
1 空字段名包括空列，无法制作数据透视表
2 相同的字段名，会自动添加序号，以示区别
3 字段所在行有合并单元格，等同于空字段，也无法创建数据透视表 4 如果有空行，会当成空值处理
如果数据源表不规范，需要处理一下才能制作数据透视表，数据表的整理我们将在第3讲介绍。
下面我们就要亲手制作数据透视表了。
原材料：销售明细表(点微信右下角阅读原文 下载示例表格)
制作步骤：
第1步 选取销售明细表，excel2003版 ： 数据菜单 - 数据透视表(Excel2010版：插入选项卡 - 数据透视表 )
excel2003版
excel2010版
第2步 执行“数据透视表”命令后，会进入数据透视表创建向导。（excel2003和excel2010稍有差异，但大体选项是一样的）在向导的第一步一般是默认选项，不需要设置，直接点确定即可(excel2003版在第一步点“完成”按钮) 。
第3步 进行前两步后，会自动新建一个工作表，且在工作表中会有一个数据透视表空白区域，就象盖楼房地基已打好，需要我们安排具体的房间位置。
添加数据透视表项目
A 把地区拖动到行标签
B 把销售量和销售金额拖动到数值区域。
注意：excel2003版和excel2010添加方式不同，是选取字段，直接拖到数据透视表中的相对位置。如下图所示。
拖动完成后，一个数据透视表的雏形已展现在我们的面前，如下图所示。
3：源表的整理
当你制作成数据透视表之前，你需要看看你的明细表是否存在下面的问题： 1 存在空列或没有行标题。
影响：明细表如果有标题为空，将无法制作数据透视表。 整理: 把标题补充完整。
2 存在相同的标题。
影响：数据透视表会自动添加序号以区分
整理：尽量不要存在相同的列标题。
3 存在合并单元格
影响：合并单元格除第1个格外，其他均作为空值处理。 整理：取消合并单元格，填充完整。
4 存在非法日期。
影响：生成的数据透视表中，无法按日期格式进行年、月、日格式的筛选和组合。
整理：转换成excel 认可的日期格式。方法：选取列 - 分列 - 第3步选日期
5 存在文本型数字
影响：文本型数字将无法在数据透视表中正确求和。
整理：转换成数值型数字。方法:选取列 - 分列 - 完成
综上所述，一个无空标题行、无重复行标题、无合并单元格、无非法日期和数字格式的数据表，才是一个标准的数据透视表的数据源。
4：透视表的结构
操作数据透视表，我们需要先了解一下数据透视表的结构。
1 字段列表。明细表的所有第一行列标题都会显示在“字段列表”中。相当于数据透视表的原材料基地。
2 字段设置区域。把要显示的字段拖放在相应区域内。相当于数据透视表的加工厂。加工的方式把相应的字段拖入。
3 数据透视表的显示区域。组合成的数据透视表会在这儿显示。
A 报表筛选区域（excel2003版称为页字段）。如上图所示，可以按“月”筛选数据透视表要显示的内容。
B 行标签（2003版称为行字段）。该区域的字段会上下排列显示，如上图中“销售地区”和“销售员”字段。
C 列标签（2003版称为列字段）。该区域的字段会左右排列显示。如下图中的“地区”。
D 数值。要统计的数据一定要放在该栏内才会进行求和等统计。如上图中的“销量”。
字段拖放在不同的区域，就会以不同的显示方式显示汇总的结果。而且同一个区域内的顺序不同，在数据透视表内汇总的先后层次也会不同。同学们可以拖动转换一下各个区域的字段内容，同时观察数据透视表的变化。 5：值的计算方式
上讲学习了数据透视表的基本结构，其中一个重要的组成部分是“数值”区域。我们需要把要求和的数值拖放到“数值”框内进行求和，如下图所示。可有时结果却显示“计数”。
原因：如果把含文本格式内容的列拖放到值区域，统计方式默认“计数”，显示为“计数项：字段名”。本例中，一是误把销售员放在了值区域，二是在销售量列可能含有文本型内容。
处理方法：把销售员从值框中拖出去（删除），然后双击“数量”列标题，在弹出的“值字段设置”窗口中，把“计数”修改为“求和”，如下图所示。
结果如下图
从上图也可以看到，数据透视表值的统计方式，不仅有求和、计数，还可以进行最大值、最小值、标准偏差等计算。
本讲最后介绍一个小技巧，上图中，如果你想把“求和项：数量”改成“数量”会提示无法更改，解决方法是可以在字段名后添加一个空格。即“数量 ”。
6：报表布局
生成数据透视表后，如果你是excel2003以上版本，你会看到多列的内容会显在一列中，如下图所示。
如果你想让列分开，就需要调整数据透视表的报表布局。选取数据透视表 - 在顶部找到“设计”选项卡 - 再打开报表布局下拉菜单，会看到5种不同的布局方式：
1 压缩式布局。如本文开始所示。
2 大纲式视图。特点标题分列显示。汇总显示在每项的上方
3 以表格显示形式。特点：标题分列显示。汇总显示在每项的下方
4 重复所有项目标签。可以填充相同的内容。大家注意下图和上图的区别。
5 不重复所有项目标答。取消重复。是4的逆操作。
7：添加和删除汇总项
默认情况下，在数据透视表中添加行或列都会自动添加汇总项，如何调整隐藏或显示汇总行/列呢？
1 总计行
总计行分为行总计和列总计，如下图所示。
控制它隐藏和显示的命令在 数据透视表右键 - 选项 - 如下图所示。（2003版在数据透视表上右键 - 表格选项 - 行和列总计选中）
2 分类汇总行
分类汇总主要是指行或列标签的分类汇总，如下图所示。
想隐藏汇总，在数据透视表相应的列上右键，取消“分汇汇总..”勾选，如果要恢复操作相同。
8：穿上漂亮的外衣
新创建的数据透视表，就是一个无任何美化效果的表格。今天学习快速给数据透视表穿上美丽的外衣。
原始表格。
操作步骤：
1 打开 选取数据透视表 - 顶端数据透视表选项 - 设计 - 如下图所示。
2 点设计 - 其他 - 打开更多数据透视表模块，点击后即套用该模板。
设置后的效果如下图所示。
9：修改引用数据源
如果明细表中行或列数发生了变化，数据透视表一般是不会自动调整数据源的，而需要手工去调整数据源的数据范围。方法是：
excel2010版选取数据透视表 - 最顶端数据透视表选项 - 更改数据源 - 重新选取范围。
excel2003版在数据透视表的右键菜单中，有“数据透视表向导”，打开数据透视表向导后再点“上一步”就可以重新选取数据源了。
开始兰色说“一般”不会自动更改数据源，难道有“二般”的情况？？
还真有，方法就是把明细表设置为“数据表”（2003版称为列表），设置步骤是：选取明细表区域，excel2010版插入 - 表格。（excel2003右键菜单中“创建列表”）
设置完成后，再次增加新数据或新列，数据透视表就会自动更新数据源了。
10：数据透视表的刷新
当明细表更新数据后，以该明细表制的数据透视表也需要刷新才可以显示新的汇总数据。
在数据透视表上的左键菜单上，点“刷新”即可完成数据刷新，如下图所示。
如果excel 文件中有很多数据透视表，可以添加“全部刷新”按钮。方法是
文件 - Excel选项 - 快捷访问工具栏 - 左侧栏中找到“全部刷新” - 点击“添加”按钮即可添加到“快捷访问工具栏”如下图所示
点击“全部刷新”命令可以刷新本工作薄所有数据透视表，如下图所示。
11：数据透视表的移动和删除 一 移动和复制
选取包含数据透视表的区域，把光标放在选取区域边缘后，直接拖动到指定的区域放下即可。如果在拖动时按ctrl 键。移动变为复制数据透视表。
二 删除
选取包括所有数据透视表元素的区域，包括透视表筛选区域和
行列及数值标签区域，按del 键即可删除。 12：筛选数值项
数据透视表中如果需要对值进行筛选，我们无法在值上面找到筛选按钮，如下图所示。
要筛选值，我们借助行标筛选的筛选值功能。比如筛选数量大于30的行。 步骤：
打开最右一个行标签的按钮，筛选值 - 大于 - 输入30，点确定后即可筛选出我们需要的结果。
设置筛选条件
筛选后的结果如下图所示。
另：选取“求和项：单价”右边单元格 - 数据 - 筛选，即可使用excel 的自带筛选功能。如下图所示。
13：固定数字和表格格式
在数据透视表中，好不容易设置的数字格式，以及列宽，在刷新后全部恢复了原状，设置的格式全部消失。怎么才能固定格式呢？ 解决方法：
在数据透视表上右键 - 数据透视表选项 - 去掉“更新时自动调整列宽”，勾选“更新时保留数字格式”
可能大家发现，即使进行了上面的设置，更新时 设置的数字格式还是会变。比如下图中，设置了广州的数字是“千位分隔符”样式，但地区换成“苏州”时，还会有的数字非千位分隔符样式。
解决上述问题的方法：把地区放在行标签，然后统一设置数字的格式为千位分隔符样式。然后再筛选所有地区，数字格式均为千位分隔符样式。 14：选取
数据透视表有值、行标签等元素构成，怎么能快速选取它们呢？ 1 全选数据透视表
光标放在数据透视表上，最上面数据透视表工具 - 布局 - 选择 -
整个数据透视表。（excel2003版在右键菜单中就有选择的子菜单）
2 选取值或行列标签部门。
在第1步中我们发现除了全选外，其他的都是灰色。这是因为当你选取的范围内包括值等元素时，这些才能用，所以要选取这些内容，先全选，然后再选值或行列标签。 3 选取某行、某列。
当你把光标放在数据透视表的某行或某列前面时，你会发现光标变成了黑箭头。这时你只需要单击就可以选取所有和本行列有关的行或列了。
数据透视表的用法
数据透视表的用法 . .......................................................................................................................... 1
1：什么叫数据数据透视表？ . ........................................................................................................ 2
2：制作数据透视表 . ........................................................................................................................ 4
3：源表的整理 . ................................................................................................................................ 8
4：透视表的结构 . .......................................................................................................................... 11
5：值的计算方式 . .......................................................................................................................... 13
6：报表布局 . .................................................................................................................................. 15
7：添加和删除汇总项 . .................................................................................................................. 17
8：穿上漂亮的外衣 . ...................................................................................................................... 19
9：修改引用数据源 . ...................................................................................................................... 21
10：数据透视表的刷新 . ................................................................................................................ 22
11：数据透视表的移动和删除 . .................................................................................................... 23
12：筛选数值项 . ............................................................................................................................ 24
13：固定数字和表格格式 . ............................................................................................................ 26
14：选取......................................................................................................................................... 27
1：什么叫数据数据透视表？
在学习excel 数据透视表之前，我们有必要了解它的概念。什么是数据透视表呢？
理论上讲，数据透视表是excel 提供的一种交互式的强大数据分析和汇总工具。兰色把它总结为：数据透视表是可以把一个明细表进行分类汇总，而且可以随意改变汇总模式的一种工具。
明细表? 分类汇总？随意改变模式？工具？神马东东？听起来还是有点抽象，还是看图比较真观。
销售明细表：
根据地区汇总
根据地区和销售员汇总
根据地区、销售员、商品名称汇总
根据月份、地区、销售员、商品名称汇总
好吧！因为篇幅关系只展示这几种汇总方式。也许你认为我函数水平高超，我会说我一个函数和公式也没用；也许你会说你是不是用了传说中的宏，对不起，我没写一句代码。以上。。。全是excel 数据透视表实现的，而且展示的也只是数据透视表最最最。。基础的汇总功能！！
2：制作数据透视表
做数据透视表之前，我们需要了解以下两个问题：
1 什么样的表格需要做数据透视表
数据透视表的用途如果细数会有很多，但最基本、最常用的是“分类汇总”，所以当我们需要对一个流水账式的明细表进行分类汇总时，就需要使用数据透视表。数据透视表的源数据即是“明细表”。如第1讲中的销售明细表。 2 什么样的格式才能做出数据透视表
在制作数据透视表，需要注意以下几点：
1 空字段名包括空列，无法制作数据透视表
2 相同的字段名，会自动添加序号，以示区别
3 字段所在行有合并单元格，等同于空字段，也无法创建数据透视表 4 如果有空行，会当成空值处理
如果数据源表不规范，需要处理一下才能制作数据透视表，数据表的整理我们将在第3讲介绍。
下面我们就要亲手制作数据透视表了。
原材料：销售明细表(点微信右下角阅读原文 下载示例表格)
制作步骤：
第1步 选取销售明细表，excel2003版 ： 数据菜单 - 数据透视表(Excel2010版：插入选项卡 - 数据透视表 )
excel2003版
excel2010版
第2步 执行“数据透视表”命令后，会进入数据透视表创建向导。（excel2003和excel2010稍有差异，但大体选项是一样的）在向导的第一步一般是默认选项，不需要设置，直接点确定即可(excel2003版在第一步点“完成”按钮) 。
第3步 进行前两步后，会自动新建一个工作表，且在工作表中会有一个数据透视表空白区域，就象盖楼房地基已打好，需要我们安排具体的房间位置。
添加数据透视表项目
A 把地区拖动到行标签
B 把销售量和销售金额拖动到数值区域。
注意：excel2003版和excel2010添加方式不同，是选取字段，直接拖到数据透视表中的相对位置。如下图所示。
拖动完成后，一个数据透视表的雏形已展现在我们的面前，如下图所示。
3：源表的整理
当你制作成数据透视表之前，你需要看看你的明细表是否存在下面的问题： 1 存在空列或没有行标题。
影响：明细表如果有标题为空，将无法制作数据透视表。 整理: 把标题补充完整。
2 存在相同的标题。
影响：数据透视表会自动添加序号以区分
整理：尽量不要存在相同的列标题。
3 存在合并单元格
影响：合并单元格除第1个格外，其他均作为空值处理。 整理：取消合并单元格，填充完整。
4 存在非法日期。
影响：生成的数据透视表中，无法按日期格式进行年、月、日格式的筛选和组合。
整理：转换成excel 认可的日期格式。方法：选取列 - 分列 - 第3步选日期
5 存在文本型数字
影响：文本型数字将无法在数据透视表中正确求和。
整理：转换成数值型数字。方法:选取列 - 分列 - 完成
综上所述，一个无空标题行、无重复行标题、无合并单元格、无非法日期和数字格式的数据表，才是一个标准的数据透视表的数据源。
4：透视表的结构
操作数据透视表，我们需要先了解一下数据透视表的结构。
1 字段列表。明细表的所有第一行列标题都会显示在“字段列表”中。相当于数据透视表的原材料基地。
2 字段设置区域。把要显示的字段拖放在相应区域内。相当于数据透视表的加工厂。加工的方式把相应的字段拖入。
3 数据透视表的显示区域。组合成的数据透视表会在这儿显示。
A 报表筛选区域（excel2003版称为页字段）。如上图所示，可以按“月”筛选数据透视表要显示的内容。
B 行标签（2003版称为行字段）。该区域的字段会上下排列显示，如上图中“销售地区”和“销售员”字段。
C 列标签（2003版称为列字段）。该区域的字段会左右排列显示。如下图中的“地区”。
D 数值。要统计的数据一定要放在该栏内才会进行求和等统计。如上图中的“销量”。
字段拖放在不同的区域，就会以不同的显示方式显示汇总的结果。而且同一个区域内的顺序不同，在数据透视表内汇总的先后层次也会不同。同学们可以拖动转换一下各个区域的字段内容，同时观察数据透视表的变化。 5：值的计算方式
上讲学习了数据透视表的基本结构，其中一个重要的组成部分是“数值”区域。我们需要把要求和的数值拖放到“数值”框内进行求和，如下图所示。可有时结果却显示“计数”。
原因：如果把含文本格式内容的列拖放到值区域，统计方式默认“计数”，显示为“计数项：字段名”。本例中，一是误把销售员放在了值区域，二是在销售量列可能含有文本型内容。
处理方法：把销售员从值框中拖出去（删除），然后双击“数量”列标题，在弹出的“值字段设置”窗口中，把“计数”修改为“求和”，如下图所示。
结果如下图
从上图也可以看到，数据透视表值的统计方式，不仅有求和、计数，还可以进行最大值、最小值、标准偏差等计算。
本讲最后介绍一个小技巧，上图中，如果你想把“求和项：数量”改成“数量”会提示无法更改，解决方法是可以在字段名后添加一个空格。即“数量 ”。
6：报表布局
生成数据透视表后，如果你是excel2003以上版本，你会看到多列的内容会显在一列中，如下图所示。
如果你想让列分开，就需要调整数据透视表的报表布局。选取数据透视表 - 在顶部找到“设计”选项卡 - 再打开报表布局下拉菜单，会看到5种不同的布局方式：
1 压缩式布局。如本文开始所示。
2 大纲式视图。特点标题分列显示。汇总显示在每项的上方
3 以表格显示形式。特点：标题分列显示。汇总显示在每项的下方
4 重复所有项目标签。可以填充相同的内容。大家注意下图和上图的区别。
5 不重复所有项目标答。取消重复。是4的逆操作。
7：添加和删除汇总项
默认情况下，在数据透视表中添加行或列都会自动添加汇总项，如何调整隐藏或显示汇总行/列呢？
1 总计行
总计行分为行总计和列总计，如下图所示。
控制它隐藏和显示的命令在 数据透视表右键 - 选项 - 如下图所示。（2003版在数据透视表上右键 - 表格选项 - 行和列总计选中）
2 分类汇总行
分类汇总主要是指行或列标签的分类汇总，如下图所示。
想隐藏汇总，在数据透视表相应的列上右键，取消“分汇汇总..”勾选，如果要恢复操作相同。
8：穿上漂亮的外衣
新创建的数据透视表，就是一个无任何美化效果的表格。今天学习快速给数据透视表穿上美丽的外衣。
原始表格。
操作步骤：
1 打开 选取数据透视表 - 顶端数据透视表选项 - 设计 - 如下图所示。
2 点设计 - 其他 - 打开更多数据透视表模块，点击后即套用该模板。
设置后的效果如下图所示。
9：修改引用数据源
如果明细表中行或列数发生了变化，数据透视表一般是不会自动调整数据源的，而需要手工去调整数据源的数据范围。方法是：
excel2010版选取数据透视表 - 最顶端数据透视表选项 - 更改数据源 - 重新选取范围。
excel2003版在数据透视表的右键菜单中，有“数据透视表向导”，打开数据透视表向导后再点“上一步”就可以重新选取数据源了。
开始兰色说“一般”不会自动更改数据源，难道有“二般”的情况？？
还真有，方法就是把明细表设置为“数据表”（2003版称为列表），设置步骤是：选取明细表区域，excel2010版插入 - 表格。（excel2003右键菜单中“创建列表”）
设置完成后，再次增加新数据或新列，数据透视表就会自动更新数据源了。
10：数据透视表的刷新
当明细表更新数据后，以该明细表制的数据透视表也需要刷新才可以显示新的汇总数据。
在数据透视表上的左键菜单上，点“刷新”即可完成数据刷新，如下图所示。
如果excel 文件中有很多数据透视表，可以添加“全部刷新”按钮。方法是
文件 - Excel选项 - 快捷访问工具栏 - 左侧栏中找到“全部刷新” - 点击“添加”按钮即可添加到“快捷访问工具栏”如下图所示
点击“全部刷新”命令可以刷新本工作薄所有数据透视表，如下图所示。
11：数据透视表的移动和删除 一 移动和复制
选取包含数据透视表的区域，把光标放在选取区域边缘后，直接拖动到指定的区域放下即可。如果在拖动时按ctrl 键。移动变为复制数据透视表。
二 删除
选取包括所有数据透视表元素的区域，包括透视表筛选区域和
行列及数值标签区域，按del 键即可删除。 12：筛选数值项
数据透视表中如果需要对值进行筛选，我们无法在值上面找到筛选按钮，如下图所示。
要筛选值，我们借助行标筛选的筛选值功能。比如筛选数量大于30的行。 步骤：
打开最右一个行标签的按钮，筛选值 - 大于 - 输入30，点确定后即可筛选出我们需要的结果。
设置筛选条件
筛选后的结果如下图所示。
另：选取“求和项：单价”右边单元格 - 数据 - 筛选，即可使用excel 的自带筛选功能。如下图所示。
13：固定数字和表格格式
在数据透视表中，好不容易设置的数字格式，以及列宽，在刷新后全部恢复了原状，设置的格式全部消失。怎么才能固定格式呢？ 解决方法：
在数据透视表上右键 - 数据透视表选项 - 去掉“更新时自动调整列宽”，勾选“更新时保留数字格式”
可能大家发现，即使进行了上面的设置，更新时 设置的数字格式还是会变。比如下图中，设置了广州的数字是“千位分隔符”样式，但地区换成“苏州”时，还会有的数字非千位分隔符样式。
解决上述问题的方法：把地区放在行标签，然后统一设置数字的格式为千位分隔符样式。然后再筛选所有地区，数字格式均为千位分隔符样式。 14：选取
数据透视表有值、行标签等元素构成，怎么能快速选取它们呢？ 1 全选数据透视表
光标放在数据透视表上，最上面数据透视表工具 - 布局 - 选择 -
整个数据透视表。（excel2003版在右键菜单中就有选择的子菜单）
2 选取值或行列标签部门。
在第1步中我们发现除了全选外，其他的都是灰色。这是因为当你选取的范围内包括值等元素时，这些才能用，所以要选取这些内容，先全选，然后再选值或行列标签。 3 选取某行、某列。
当你把光标放在数据透视表的某行或某列前面时，你会发现光标变成了黑箭头。这时你只需要单击就可以选取所有和本行列有关的行或列了。