轻松筹筹款标题怎么写:使用数据透视表分析数据1

２８．１关于数据透视表
灵气透视表是用来从Ｅxcel数据列表、关系数据库文件或OLAP多维数据集中的特殊字段中总结信息的分析工具。它是一种交互式报表，可以快速分类汇总和比较大量的数据，并可以随时选择其中页、行和列中的不同元素，以快速查看源数据的不同统计结果，同时还可以随意显示和打印出你所感兴趣区域的明细数据。
灵气透视表有机地综合了数据排序、筛选、分类汇总等数据分析的优点，可方便地调整分类汇总的方式，灵活地以多种不同方式展示数据的特征。一张“数据透视表”仅靠鼠标移到字段位置，即可变换出各种类型的报表。同时，数据透视表也是解决函数公式速度瓶颈的手段之一。因此，该工具是最常用、功能最全的Excel数据分析工具之一。
２８．１．１数据透视表的用途
数据透视表是一种大量数据快速汇总和建立交叉列表的交互式动态表格，能帮助用户分析、组织数据。例如，计算平均数、标准差，建立列联表、计算百分比、建立新的数据了集等。建好数据透视表后，可以对数据透视表重新安排，以便从不同的角度查看数据。数据透视表的名字来源于它具有“透视”表格的能力，从大量看似无关的数据中寻找背后的联系，从而将纷繁的数据转化为有价值的信息，以供研究和决策所用。
总之，合理运用数据透视表进行计算与分析，能使许多复杂的问题简单化并极大地提高工作效率。
２８．１．２　一个简单的例子
图所示的数据列表显示了一家贸易公司的销售数据清单。清单中包括年份、季度、销售地区、品名、数量、单价、金额；时间跨度为８个季度（２００５～２００６年）。只需几步简单操作，就可以将这张“平庸”的数据列表变成能够提供有用信息的数据透视表。
销售地区 销售人员 品名 数量 单价￥ 销售金额￥ 销售年份 销售季度
北京 苏珊 按摩椅 13 800 10,400 2005 2
北京 苏珊 显示器 98 1,500 147,000 2005 3
北京 苏珊 显示器 49 1,500 73,500 2005 4
北京 苏珊 显示器 76 1,500 114,000 2005 1
北京 苏珊 显示器 33 1,500 49,500 2005 2
北京 苏珊 液晶电视 53 5,000 265,000 2005 3
北京 苏珊 液晶电视 47 5,000 235,000 2005 4
北京 苏珊 液晶电视 1 5,000 5,000 2005 1
北京 白露 液晶电视 43 5,000 215,000 2005 2
北京 白露 液晶电视 34 5,000 170,000 2005 3
北京 白露 微波炉 27 500 13,500 2005 4
北京 白露 微波炉 69 500 34,500 2005 1
北京 白露 微波炉 24 500 12,000 2006 1
北京 白露 按摩椅 28 800 22,400 2006 2
北京 白露 按摩椅 45 800 36,000 2006 3
北京 赵琦 按摩椅 20 800 16,000 2006 4
北京 赵琦 按摩椅 68 800 54,400 2006 1
北京 赵琦 显示器 77 1,500 115,500 2006 2
数据列表，用来创建一个数据透视表
求和项:销售金额￥ 　 　 品名
销售人员 销售年份 销售季度 按摩椅 跑步机 微波炉 显示器 液晶电视 总计
白露 2005 1 　 　 34,500 　 　 34,500
2 　 215,000 215,000
3 　 170,000 170,000
4 　 13,500 13,500
2005 汇总 　 　 　 48,000 　 385,000 433,000
2006 1 　 　 12,000 　 　 12,000
2 22,400 22,400
3 36,000 36,000
2006 汇总 　 58,400 　 12,000 　 　 70,400
白露 汇总 　 　 58,400 　 60,000 　 385,000 503,400
毕春艳 2006 1 　 　 11,000 36,000 460,000 507,000
2 　 19,500 114,000 120,000 253,500
3 　 38,000 73,500 45,000 156,500
4 67,200 79,500 225,000 371,700
2006 汇总 　 67,200 　 68,500 303,000 850,000 1,288,700
毕春艳 汇总 　 　 67,200 　 68,500 303,000 850,000 1,288,700
高伟 2006 1 　 187,000 9,500 43,500 340,000 580,000
2 2,400 74,800 9,500 86,700
3 74,400 100,500 174,900
4 　 162,800 106,500 90,000 359,300
2006 汇总 　 76,800 424,600 19,000 250,500 430,000 1,200,900
高伟 汇总 　 　 76,800 424,600 19,000 250,500 430,000 1,200,900
何庆 2006 1 　 94,600 　 　 　 94,600
2 　 123,200 123,200
3 　 66,000 66,000
2006 汇总 　 　 217,800 　 66,000 　 283,800
何庆 汇总 　 　 　 217,800 　 66,000 　 283,800
李兵 2006 1 　 228,800 　 　 　 228,800
2 　 66,000 66,000
3 　 132,000 132,000
4 　 15,400 2,500 17,900
2006 汇总 　 　 442,200 2,500 　 　 444,700
李兵 汇总 　 　 　 442,200 2,500 　 　 444,700
根据数据列表创建的数据透视表
此数据透视表显示了不同销售人员在不同年份所销售的各种产品的销售额汇总，数据透视表最后一行是所有销售人员所销售的各种产品的销售额总计。
从图中可以很容易找出原始数据清单所记录的大多数信息，没有显示的数据信息仅为销售数量和销售地区。只要再将数据透视表做进一步调整，就可以将这些信息也显示出来。将销售年份、销售季度、销售地区移到数据区以外，使销售数量与销售金额并排显示，只需简单地从销售年份、季度、地区字段标题左侧的下拉列表框中选择相应的年份、季度、销售地区即可查看不同时期和不同销售地区的数据。如图所示。
品名 (全部)
数量 (全部)
销售年份 (全部)
销售地区 (全部)
数据
销售人员 销售季度  数量  销售金额￥ 求和项:销售金额￥
白露 1 93 46500 46500
2 71 237400 237400
3 79 206000 206000
4 27 13500 13500
白露 汇总 270 503400 503400
毕春艳 1 138 507000 507000
2 139 253500 253500
3 134 156500 156500
4 182 371700 371700
毕春艳 汇总 593 1288700 1288700
高伟 1 201 580000 580000
2 56 86700 86700
3 160 174900 174900
4 163 359300 359300
高伟 汇总 580 1200900 1200900
何庆 1 43 94600 94600
2 56 123200 123200
3 44 66000 66000
何庆 汇总 143 283800 283800
李兵 1 104 228800 228800
2 30 66000 66000
3 60 132000 132000
4 12 17900 17900
李兵 汇总 206 444700 444700
林茂 1 161 354200 354200
2 37 23000 23000
3 113 169500 169500
4 56 71900 71900
林茂 汇总 367 618600 618600
苏珊 1 77 119000 119000
2 46 59900 59900
3 151 412000 412000
4 96 308500 308500
苏珊 汇总 370 899400 899400
杨光 1 27 135000 135000
2 52 78000 78000
3 129 334500 334500
4 105 157500 157500
杨光 汇总 313 705000 705000
赵琦 1 122 324400 324400
2 117 175500 175500
3 106 237500 237500
4 72 94000 94000
赵琦 汇总 417 831400 831400
总计 3259 6775900 6775900
２８．１．３　数据透视表的数据组织
用户可以从４种类型的数据源中来创建数据透视表。
１）Excel数据列表
２）外部数据源
３）进行多重合并计算的独立的Excel数据列表
４）其他的数据透视表
注意：如果以Excel数据列表作为数据源，则标题行不能有空白单元格或合并的单元格，否则不能生成数据透视表，付出现错误提示。
数据透视表中的术语：
数据源　　从中创建数据透视表的数据列表或多维数据集。
轴　　　　数据透视表中的一维，如行、列、页。
列字段　　信息的种类，造价于数据列表中列。
行字段　　在数据透视表中具有行方向的字段。
颁页符　　数据透视表中进行分页的字段。
字段标题　描述字段内容的标志。可以通过拖动字段标题对数据透视表进行透视。
项目　　　组成字段的成员。如图中，２００５和２００６就是组成年份字段的项。
组　　　　一组项目的集合，可以自动或手动地为项目组合。
透视　　　通过改变一个或多个字段的位置来重新安排数据透视表。
汇总函数　Excel计算表格中数据的值的函数。文本和数值的默认汇总函数为计数和求和。
分类汇总　数据透视表中对一行或一列单元格的分类汇总。
刷新　　　重新计算数据透视表，反映目前数据源的状态。
２８．１．４　数据透视表的工具栏
数据透视表的所有功能和特性都可以通过数据透视表工具栏来实现。
显示数据透视表的工具栏有２种方法。
１）选择“视图”——“工具栏”——选择数据透视表菜单项。
２）鼠标右击数据透视表——在弹出的菜单中选择“显示数据透视表工具栏”。
２８．２　创建数据透视表
使用数据透视表和数据透视图向导以创建数据透视表，启用此向导的方法是单击Excel菜单栏中的“数据”——“数据透视表和数据透视图”。在该向导的指导下，用户只要按部就班地一步一步进行操作，就可以轻松地完成数据透视表的创建。
它的操作步骤共分为３步。
步骤１．选择数据源类型。
步骤２．选择数据源区域。
步骤３．指定数据透视表位置。
２８．２．１步骤１指定数据源的类型
选定数据透视表和数据透视图向导所示的销售数据清单中任意一个数据单元格，单击菜单“数据”——“数据透视表和数据透视图”，出现“数据透视表和数据透视图向导——３步骤之１”对话框。
该步骤帮助用户确定数据源类型和报表类型。单击不同选项的选项按钮，对话框左侧的图像将会产生相应变化。此处保留对默认选项的选择，即数据源类型为Excel数据列表，报表类型为数据透视表。
２８．２．２步骤２指定数据源的位置
指定了数据源类型后，单击“下一步”按钮，向导将显示第２个对话框，“数据透视表和数据透视图向导——３步骤之２”，要求指定数据源的位置。
该步骤用于选定数据源区域。由于数据列表都是位于某个连续的单元格区域，所以，一般情况下Excel会自动识别数据源所在的单元格区域，并填入到“选定区域”框。
如果Excel识别的数据源区域不正确，则需要用户重新选定区域，单击“选定区域”的折叠按钮选定整个数据源。
如果数据源是当前未打开的数据列表，可以单击“浏览”按钮打开另一个工作表，并选择范围。
２８．２．３步骤３指定数据透视表的显示位置
在向导的最后一个对话框中，需要指定数据透视表的显示位置。
如果要将数据透视表显示到新的工作表上，可以选择“新建工作表”选项按钮，Excel将为数据透视表插入一个新的工作表。否则，可以选择“现有工作表”选项按钮，并且在文本框中指定开始单元格位置。
单击“完成”按钮之前，可以单击“选项”按钮对数据透视表格式和数据提前进行设置。但是建议用户在完成数据透视表的创建以后再使用“数据透视表选项”对话框设置这些选项，后者更加灵活方便。］
有两种方法可以设置数据透视表的布局，这也是创建数据透视表过程中最关键的下一步。
方法１．在“数据透视表和数据透视图向导——３步骤之３”对话框中单击“布局”按钮，在“布局”对话框中设置数据透视表的结构。
方法２．单击“完成”按钮，创建一个空的数据透视表，然后使用“数据透视表字段列表”工具栏来布局数据透视表。
１．使用对话框布局数据透视表
当用户在“数据透视表和数据透视图向导——３步骤之３”对话框内单击“布局”按钮，会出现“数据透视表和数据透视图向导——布局”对话框。
销售数据清单中各列标题作为按钮出现在对话框的右半部分。用鼠标拖支这些按钮，将其按自己的设计要求放置在左边图中相应的位置就可以构造出数据透视表。
从结构上看，数据透视表分为４个部分。
１）页：此标志区域中按钮将作为数据透视表的分页符。
２）行：此标志区域中按钮将作为数据透视表的行字段。
３）列：此标志区域中按钮将作为数据透视表的显示汇总的数据。
４）数据：此标志区域中按钮将作为数据透视表的显示汇总的数据。
将“销售人员”、“销售年份”、“销售季度”字段按钮拖动到“行”区域；将“品名”字段按钮拖动到“列”区域；将“销售金额”字段按钮拖动到“数据”区域。
单击“确定”按钮关闭“布局”对话框，最后单击“数据透视表和数据透视图向导——３步骤之３”对话框的“完成”按钮，即可创建出数据透视表。］
２．使用“数据透视表字段列表”工具栏布局数据透视表
用户可以使用“数据透视表字段列表”工具栏在工作表中直接来布局数据透视表。
如果用户在“数据透视表和数据透视图向导——３步骤之３”对话框中没有利用“布局”命令来做相关设置，而是直接单击“完成”按钮，则Excel将显示一张空的数据透视表。单击空表区域内的任意位置，将会出现放置不同字段类型的提示，并且显示“数据透视表字段列表”。
在“数据透视表字段列表”内将“销售人员”，“销售年份”，“销售季度”依次拖入“将行字段拖至此处”的区域。
如果字段列表对话框没有出现，可以先选中数据透视表，然后再单击数据透视表工具栏“显示字段列表”按钮，也可以在数据透视表内单击鼠标右键，选择显示字段列表，激活字段列表对话框。
在“数据透视表字段列表”内将“品名”拖入“将列字段拖至此处”的区域；将“销售金额￥”拖入“请将数据项拖至此处”的区域。
此外，还可以使用“数据透视表字段列表”工具栏的“添加到”按钮把字段添加进报表。单击“数据透视表字段列表”中的“数量”，在下拉框中将字段位置从“行区域”改为“数据区域”，然后单击“添加到”按钮。
２８．２．４创建动态的数据透视表
用户在完成数据透视表后，如果数据源增加了新的行或列，即使刷新数据透视表，新增的数据仍无法出现在数据透视表中。为了避免这种情况的发生，可以为数据源定义名称或使用数据列表功能来获得动态的数据源，从而来创建动态的数据透视表。
１．定义名称法创建动态的数据透视表
使用定义名称的方法来创建动态的数据透视表，首先要使用一个动态的公式定义数据透视表的数据源。当一个新的记录添加到表格中时，数据源会自动扩展。然后将定义的名称范围用于数据透视表，从而创建动态的数据透视表。
示例２８．１定义名称创建动态数据透视表
在图所示的销售明细表中定义名称data=OFFSET（销售明细表!$A$1,0,0,COUNTA（销售明细表!$A:$A),COUNTA(销售明细表!$1:$!))。
销售地区 销售人员 品名 数量 单价￥ 销售金额￥ 销售年份 销售季度
北京 苏珊 按摩椅 13 800 10,400 2005 2
北京 苏珊 显示器 98 1,500 147,000 2005 3
北京 苏珊 显示器 49 1,500 73,500 2005 4
北京 苏珊 显示器 76 1,500 114,000 2005 1
北京 苏珊 显示器 33 1,500 49,500 2005 2
北京 苏珊 液晶电视 53 5,000 265,000 2005 3
北京 苏珊 液晶电视 47 5,000 235,000 2005 4
北京 苏珊 液晶电视 1 5,000 5,000 2005 1
北京 白露 液晶电视 43 5,000 215,000 2005 2
北京 白露 液晶电视 34 5,000 170,000 2005 3
北京 白露 微波炉 27 500 13,500 2005 4
北京 白露 微波炉 69 500 34,500 2005 1
北京 白露 微波炉 24 500 12,000 2006 1
北京 白露 按摩椅 28 800 22,400 2006 2
北京 白露 按摩椅 45 800 36,000 2006 3
北京 赵琦 按摩椅 20 800 16,000 2006 4
北京 赵琦 按摩椅 68 800 54,400 2006 1
北京 赵琦 显示器 77 1,500 115,500 2006 2
北京 赵琦 液晶电视 41 5,000 205,000 2006 3
北京 赵琦 显示器 52 1,500 78,000 2006 4
北京 赵琦 液晶电视 54 5,000 270,000 2006 1
北京 赵琦 显示器 40 1,500 60,000 2006 2
定义动态数据源
有关定义名称的更多内容可以参阅第１１章。
将定义的名称范围用于数据透视表。
步骤１．单击销售明细表中的任意一个有效数据单元格，单击菜单“数据”——“数据透视表和数据透视图”。
步骤２．在“数据透视表和数据透视图向导——３步骤之１”对话框中选择“Microsoft office Excel数据列表或数据库”并单击“下一步”
步骤３．在“数据透视表和数据透视图向导——３步骤之２”对话框的选定区域框内，输入范围名称data，单击“完成”按钮。
步骤４．在工作表中，将“数据透视表字段列表”对话框内的字段按钮拖至数据透视表中行、列和数据区域。
如此即完成了动态数据透视表的创建。用户可以向作为数据源的销售明细表中添加一些新记录，如新增一条记录，“销售地区”为“深圳”、“销售人员”为“张林波”。在数据透视表中单击鼠标右键，在弹出的快捷菜单中单击“刷新数据”命令，即可见到新增的数据。
注意：由于在数据源“销售明细表”中添加的新记录只有销售地区和销售人员的数据，而没有相应的增加销售年份、销售季度、品名以及数量、金额数据，因此数据透视表中销售年份等字段会显示“（空白）”。
２．使用数据列表功能创建动态的数据透视表
列表功能Excel 2003中新增功能，利用列表对数据源的自动扩展可以创建动态的数据透视表。
示例２８．２　使用数据列表功能创建动态数据透视表
在图（上图）所示的销售明细表中操作如下。
步骤１．在“销售明细表”中单击任意一个有效数据单元格，单击菜单“数据”——“列表”——鼠标指向“创建列表”并单击它。
步骤２．单击“确定”按钮即可创建一张列表。
步骤３．在创建的列表内单击任意一个有效数据单元格，在菜单栏上依次单击“数据”——“数据透视表和数据透视图”，在“数据透视表和数据透视图向导——３步骤之１”对话框中选择“Microsoft Office Excel数据列表或数据库”，单击“完成”。
这样，利用列表创建的动态数据透视表完成，用户可以向销售明细表中添加一些新记录，如：新增记录中的“销售地区”为“深圳”、“销售人员”为“张林波”。在数据透视表中单击右键，在弹出的快捷菜单中单击“刷新数据”命令，即可见到新增的数据。
２８．３改变数据透视表的布局
对于已经完成的数据透视表，用户在任何时候都只需拖支字段按钮就可以重新安排透视布局，满足新的要求。例如，用户想生成按销售年份统计销售金额的报表，以图所示的数据透视表为例，将“销售季度”字段按钮拖至“销售人员”的左侧，“销售年份”字段按钮拖至“销售季度”的左侧。
现在，“销售人员”出现在“销售年份”及“销售季度”的组合中，并且按照“销售年份”、“销售季度”进行分类汇总。
２８．３．１数据透视表页面区域的使用
当字段显示在列区域行区域上时，滚动数据透视表就可以看到字段中的所有项。然而，当字段位于页面区域中时，则一次只能显示一项。要查看字段的其他项，可以在该字段的下拉列表框中依次进行选择。
１．显示页面区域中字段的汇总
数据透视表中每个字段的下拉列表框中，首先项都是“全部”。选择该选项可以显示发球该字段所有项目的信息。例如，将图所示数据透视表页面区域中的“销售地区”、“品名”、“销售年份”字段都选择“全部”，则可以得到每个销售人员在所有年份和销售地区销售所有品名的汇总数据。
２．数据透视表的分页显示功能
虽然数据透视表可以包含多个页面区域，但通常情况下只显示其中的一个页面数据。利用数据透视表的分面显示功能，用户就可以创建一系列链接在一起的数据透视表，每张表显示页字段的一项。
示例２８．３分页显示数据透视表
要在图所示的数据透视表中创建分页显示，可以参照以下步骤。
步骤１．单击数据透视表工具栏中的“数据透视表”。
步骤２．在弹出的快捷菜单中单击“分页显示”命令。
步骤３．在“分页显示”对话框中单击“销售地区”。
步骤４．最后单击“确定”按钮，可将“销售地区”字段中的每个销售地区分页显示在不同的工作表中，并且按照“销售地区”字段的各项对工作表命名。
２８．３．２整理数据透视表字段
数据透视表完成后，用户可以通过对数据透视表字段的整理来满足自己对数据透视表格式上的需求。
１．整理复合字段
示例２８．４设置数据透视表的数据字段并排显示
如果数据透视表的数据区域中垂直显示“销售金字￥”、“数量”两个字段，为了便于读取和比较数据，用户可以重新安排数据透视表的字段。
单击“数据”按钮，按着鼠标左键并拖拉“数据”按钮到有“汇总”一词的单元格，松开鼠标左键。
此时两个数据字段成水平位置排列。
２．重命名字段
当用户向数据区域添加字段后，它们都将被重命名，例如“销售金额￥”变成了“求和项：销售金额￥”或“计数项”销售金额￥”，这样就会加大字段所在列的列宽，影响表格的美观。
下面介绍两种字段重命名的方法：
１）单击数据透视表中的标题单元格“求和项：销售金额￥”，输入新标题“金额合计”，按下ＥＮＴＥＲ键，这种方法是最简便易行的
２）如果用户要保持原有字段的名称不变，可以采用替换的方法。选中数据透视表的标题单元格，在菜单栏上依次单击“编辑”——替换，在弹出的“查找和替换对话框中的“查找内容文本框中输入“求和项”，在“替换为”文本框中输入一个空格，单击“全部替换”，完成标题重命名。
注意：数据透视表中每个字段的名称必须唯一，Excel不接受任意两个字段具有相同的名称。
２８．３．３整理数据透视表数据
１．显示所有数据项
在数据透视表中，每个字段右侧都有一个小箭头，单击它可打开一个数据项下拉列表，选择“（全部显示）”复选框将显示全部数据项。
２．隐藏数据项
如果用户不需要显示所有数据项的数据，可以将不需要的数据项隐藏。单击数据项下拉列表中“（全部显示）”复选框，至少选中其中一个数据项的复选框，并单击“确定”按钮。
３．隐藏页字段数据
页字段下拉列表中不是复选框，用户不能同时选中多个数据项，只能选择“全部”项或可视数据项中的一项。下面介绍两种方法可以隐藏或显示多个数据项。
示例２８．５隐藏数据透视表的页字段数据
方法１．利用“数据透视表字段”对话框中的“隐藏数据项”。
步骤１．双击页字段按钮“品名”调出“数据透视表字段”对话框。
步骤２．在“隐藏数据项”列表中单击你想要隐藏的数据项，如“按摩椅”和“微波炉”。
步骤３．单击“确定”按钮完成。
方法２　利用页字段的下拉列表框。
步骤１．将页字段按钮“品名”拖到行区域。
步骤２．单击“品名”字段的下拉按钮，在出现的下拉列表框中取消勾选“全部显示”，再分别取消“按摩椅”和“微波炉”的勾选。
步骤３．单击“确定”按钮，将页字段按钮“品名”拖回原处，页字段“品名”的选项变为“多项”。
４．清除数据透视表原有数据项
数据透视表的数据源改变后，会导致字段下拉列表中有些无用的数据项存在。例如有些销售人员已经离开公司，但他们的名字仍然会在数据透视表的数据项中存在。即使用户对数据透视表进行刷新，这些名字仍然会与新名字同时显示。
示例２８．６彻底清除数据透视表中不应存在的数据项
在下面的数据项下拉列表中，“李兵”已经被“刘坤”代替，但他的名字仍然存在。
清除原有的数据项的操作方法如下：
步骤１．将数据透视字段“销售人员”字段拖到数据透视表以外的区域。
步骤２．在数据透视表内单击右键，刷新数据。
步骤３．最后将“销售人员”字段拖回到原来的行区域位置。
２８．４数据透视表的刷新
源数据发生变化后，数据透视表本身并不会自动刷新。要对数据透视表进行刷新，只需在数据透视表内右键，单击“刷新数据”即可。
２８．４．１在打开文件时刷新
用户如果希望Excel在每次打开数据透视表所在的工作表时都进行数据刷新，则可以这样设置：
步骤１．右键单击数据透视表，在弹出的快捷菜单中单击“表格选项”。
步骤２．在“数据透视表选项”对话框中勾选“数据选项”的“打开时刷新”复选框。
步骤３．单击“确定”按钮。
２８．４．２刷新链接在一起的数据透视表
当数据透视表用作其他数据透视表的数据源时，对其中任何一张数据透视表进行刷新，都会引起所有链接在一起的数据透视表进行刷新。
２８．４．３刷新引用外部数据的数据透视表
如果创建的数据透视表是基于对外部数据的查询，Excel可以在用户工作时在后台中执行数据查询。
步骤１．右键单击数据透视表，在弹出的快捷菜单中单击“表格选项”。
步骤２．在“数据透视表选项”对话框中勾选“外部数据选项”中的“后台查询”复选框。
步骤３．单击“确定”按钮。
注意：“外部数据选项”只对由外部数据创建的数据透视表可用，否则“外部数据选项”下的复选框均为灰色。
２８．４．４定时刷新
如果要让数据透视表自动地定时刷新，可以这样设置：
步骤１．右键单击数据透视表，在弹出的快捷菜单中单击“表格选项”。
步骤２．在“数据透视表选项”对话框中勾选“数据源选项”中的“刷新频率”复选框，并选择以分钟为单位指定刷新的时间间隔。
步骤３．单击“确定”按钮。
这一选项也只对由外部数据创建的数据透视表适用。
２８．５设置数据透视表的格式
用户可用设置单元格格式的方法来修改数据透视表中单元格的外观。例如，改变字体、字号、设置数字格式、填充颜色等。如果要避免刷新数据后发生格式丢失，可按如下步骤加以解决。
步骤１．右键单击数据透视表。
步骤２．在弹出的快捷菜单中单击“表格选项”。
步骤３．在“灵气透视表选项”对话框的“格式选项”中勾选“保留格式”复选框。
步骤４．最后单击“确定”按钮。
２８．５．１数据透视表自动套用格式化
Excel为数据透视表提供了超过20种的自动套用格式选项。要应用这些格式，可在数据透视表中选择任意单元格，然后单击数据透视表工具栏上的“设置报告格式”按钮，在“自动套用格式”对话框中选择一种报表格式。此外，也可以单击Excel菜单“格式”——“自动套用格式”来设置。
要取消现有数据透视表中的自动套用格式，右键单击数据透视表，在弹出的快捷菜单中单击“表格选项”，在“数据透视表选项”对话框中取消勾选“自动套用格式”复选框，单击“确定”按钮。
２８．５．２改变数据透视表中所有单元格的数字格式
如果用户要改变数据透视表中所有单元格的数字格式，只需选中这些单元格再设置单元格格式，可以参见以下步骤。
步骤１．鼠标单击数据透视表。
步骤２．按下CTRL+A组合键，选中整个数据透视表，单击鼠标右键。
步骤３．在弹出的快捷菜单中单击“设置单元格格式”。
步骤４．在弹出“单元格格式”对话框中选择“数字”选项卡进行相关的数字格式设置。
２８．６在数据透视表中排序
２８．６．１使用手动排序
用户可以使用Excel中的“排序”功能对数据透视表的字段项进行排序，该字段项所有的实例都将会进行排序，并在改变数据透视表布局时保持既定的排列顺序。
示例２８．７　在数据透视表中进行手动排序
品名 (全部)
数量 (全部)
销售年份 (全部)
销售地区 (全部)
数据
销售人员 销售季度  数量  销售金额￥ 求和项:销售金额￥
白露 1 93 46500 46500
2 71 237400 237400
3 79 206000 206000
4 27 13500 13500
白露 汇总 270 503400 503400
毕春艳 1 138 507000 507000
2 139 253500 253500
3 134 156500 156500
4 182 371700 371700
毕春艳 汇总 593 1288700 1288700
高伟 1 201 580000 580000
2 56 86700 86700
3 160 174900 174900
4 163 359300 359300
高伟 汇总 580 1200900 1200900
要对图所示的数据透视表中按“销售季度”项进行降序排序，可以通过以下步骤来实现。］
步骤１．单击单元格Ｂ６（销售季度）。
步骤２．在菜单栏上依次单击“数据”——“排序”。
步骤３．在“排序”对话框中选择“降序”。
步骤４．单击“确定”按钮结束。
Excel会重新排列“销售季度”的显示顺序，在数据透视表中任何出现“销售季度”的地方均按“4”、3、2、1 的顺序排列。
２８．６．２　使用自动排序
用户还可以对希望排序的字段使用自动排序。自动排序特性与标准的排序功能一样，都会对该字段项所有的实例进行排序，只是增加了附加选项，可以按照数据的值对字段项进项排序。
示例２８．８在数据透视表中进行自动排序
数据透视表中按“销售人员”字段进行降序的自动排序，可按以下步骤进行。
步骤１．双击“销售人员”字段按钮，出现“数据透视表字段”对话框。
步骤２．单击“高级”按钮，出现“数据透视表字段高级选项”对话框。
步骤３．自动排序选项选择“降序”单选按钮，“使用字段”下拉列表框中选择“销售人员”，单击“确定”按钮。
２８．６．３使用非标准顺序排序
用户如果既不希望按降序又不希望按升序排序，可以使用自定义的排序方法进行排序。
２８．７数据透视表的项目组合
数据透视表能够自动地在外部字段的标题下对内部字段的项进行组合，并可按照用户的要求来创建各个内部字段的分类汇总。在图中“销售人员”发球外部字段，而“销售季度”则发球内部字段。
项目组合是数据透视表的一个非常有用的特性，Excel提供了以下几种对项进行组合的选择。
２８．７．１组合数据透视表的指定项
求和项:订单金额 　 销售人员
销售途径2 销售途径 毕春艳 高伟 何庆 林茂 苏珊 杨光 张林波 总计
国际业务 国际业务 1597.2 3058.82 10779.6 18298.72 8794.74 8623.68 14445.3 65598.06
国内业务 国内市场 7840.91 1423 23681.12 30401.68 4235.4 16609.97 6597.34 90789.42
送货上门 86232.2 47129.65 173558.39 143187.25 51456.1 124045.36 92711.21 718320.16
网络销售 19989.48 16664.78 61067.99 26466.4 7753.39 26794.78 9038.42 167775.24
邮购业务 1303.2 516 7157.21 7409.63 288 6426.3 240.4 23340.74
总计 　 116962.99 68792.25 276244.31 225763.68 72527.63 182500.09 123032.67 1065823.62
组合前的数据透视表
图所示的示例文件“２８．７指定项组合．xls”可以从Excel Home网站下载。
如果用户在图所示的数据透视表中，希望将“国内市场”、“送货上门”、“网络销售”、“邮购业务”的销售数据组合在一起，合称为“国内业务”，可参考如下步骤创建此项组合。
步骤１．在数据透视表中，鼠标单击“国内市场”，按住键盘上的CTRL键，用鼠标依次单击“送货上门”、“网络销售”、“邮购业务”标题。
步骤２．单击鼠标右键，在弹出的快捷菜单中单击“组及显示明细数据”，鼠标指向“组合”并单击它；Excel创建了新的字段标题，并自动命名为“销售途径2”，并且将选中的项组合到新的“数据组1”的项中。
步骤３．选中标为“数据组１”的单元格，输入新的名称“国内业务”。
２８．７．２数字项组合
要将字段中的数字项组合，可以在分组对话框中进行设置。例如，“销售季度”字段组合，在数据透视表中单击该字段的任意项，单击鼠标右键，在弹出的快捷菜单中单击“组及显示明细数据”鼠标指向“组合”并单击它，出现“组合”对话框。
如果用户希望将每２个季度创建为一组，可以在“起始于”文本框中输入“１”，在“终止于”文本框中输入“４”，在“步长”文本框中输入“２”，单击“确定”按钮。
２８．７．３按日期或时间项组合
求和项:订单金额 　 销售人员
年 订单日期 毕春艳 高伟 何庆 林茂 苏珊 杨光 张林波 总计
2003年 7月 　 996.62 7,327.52 8,323.85 1,863.40 1,614.88 584.00 20,710.27
8月 479.40 642.20 3,452.08 4,948.87 2,738.22 3,411.20 7,196.00 22,867.97
9月 1,206.60 1,420.00 2,317.20 4,193.12 3,866.40 3,260.00 16,263.32
10月 3,624.48 1,393.20 10,706.09 13,519.93 288.00 6,426.30 4,126.50 40,084.50
11月 9,087.48 3,901.08 3,304.78 6,898.66 1,586.40 7,834.90 568.80 33,182.10
12月 834.20 9,314.10 3,328.00 13,936.60 3,850.54 7,708.08 3,425.40 42,396.92
2004年 1月 11,025.34 716.72 6,365.44 15,955.81 2,122.92 12,234.35 9,664.39 58,084.97
2月 192.00 9,532.81 14,487.58 2,704.24 407.70 4,118.14 31,442.47
3月 3,891.00 2,520.40 15,324.58 10,645.14 756.00 5,243.77 6,209.70 44,590.59
4月 8,618.96 2,164.30 7,007.70 7,718.50 1,419.00 2,346.50 29,274.96
5月 5,053.66 4,500.27 13,359.05 9,977.74 3,061.90 9,355.96 4,417.90 49,726.48
6月 2,765.30 2,147.40 22,156.88 5,404.17 3,464.81 5,150.60 1,922.32 43,011.48
7月 3,569.58 7,365.40 1,900.76 4,795.69 493.00 8,666.67 671.34 27,462.44
8月 8,004.99 1,434.00 1,739.75 17,134.24 3,569.94 16,286.69 7,920.85 56,090.46
9月 8,689.36 4,176.40 12,533.50 9,301.19 1,418.71 7,441.55 1,057.80 44,618.51
10月 5,898.50 7,581.32 12,494.91 10,953.15 5,409.50 8,990.15 9,415.00 60,742.53
11月 1,890.50 484.25 13,965.81 5,177.05 4,016.35 7,513.90 5,452.75 38,500.61
12月 228.00 507.00 16,938.17 13,816.10 6,090.50 13,140.02 3,757.33 54,477.12
日期按原始项目排列的数据透视表
如图所示的数据透视表显示了按订单日期统计的销售人员的订单金额。在实际工作中，因为日期仍然按照原始项目按天排列，难以体现出分段时间的统计信息。在此种情况下，用户可以对日期项进行分组使表格变得更有意义，具体步骤如下。
步骤１．在数据透视表“订单日期”字段内单击鼠标右键，在弹出的快捷菜单中单击“组及显示明细数据”，鼠标指向“组合”并单击它。
步骤２．在出现的“分组”对话框中，保持起始和终止日期的默认设置，再单击“步长”列表框中的“年”，按CTRL键同时再选中列表框中的“月”。
步骤３．单击“分组”对话框中的“确定”按钮。
２８．７．４取消项目组合
如果不需要某个组合，可以选中这个组合单击鼠标右键，在弹出的快捷菜单中单击“组及显示明细数据”，鼠标指向“取消组合”并单击它，即可删除组合，将字段恢复到组合前的状态。
２８．７．５组合数据时遇到的问题
当用户试图对一个日期或字段进行分组时，可能会得到一个错误信息警告，内容为“选定区域不能分组”。
进行分组字段的数据源中如果包含有空白的单元格或文本型的日期、数字，在组合时会提示用户选定的区域不能分组。解决这个问题可以参见以下步骤。
步骤１．在空白单元格填充日期或数字（如有必要填充一个假设的日期或数字）。
步骤２．如果在日期或数字字段存在文本，移除它。
步骤３．如果数字被Excel视为了文本，将它改变为常规类型。
２８．８在数据透视表中执行计算
在默认设置下，Excel对数据透视表数据区域的数字字段应用求和函数，对非数字字段应用计数函数。
Excel提供了多种汇总方式供用户选择，包括“求和”、“计数”、“平均值”、“最大值”、“最小值”、“乘积”等。要选择汇总方式，可在数据透视表的数据区域中单击右键，在弹出的快捷菜单中单击“字段设置”。Excel会显示出“数据透视表字段”对话框，选择要采用的汇总方式，最后单击“确定”按钮。
２８．８．１以同一字段应用多种汇总方式
用户可以对一个数据字段同时应用多种汇总方式。要为已经位于数据透视表数据区域的字段添加第二种或更多的汇总方式，只需在“数据透视表列表”内将该字段一次拖进数据透视表的数据区中，利用“字段设置”对话框选择想用的汇总方式即可。
２８．８．２自定义计算
如果“数据透视表字段”对话框内的汇总方式仍然不能满足需求，Excel还允许用户自定义计算。利用此功能，可以计算数据透视表数据区域中每项占同行或同列数据总和的百分比，或显示每个数值占总和的百分比。
应用自定义计算的步骤为：
步骤１．在数据透视表的数据区域中单击鼠标右键，在弹出的快捷菜单中单击“字段设置”，在“数据透视表字段”对话框内单击“选项”按钮，出现扩展对话框“数据显示方式”。
步骤２．在“数据显示方式”下拉列表中选择想要执行的计算，再从“基本字段”和“基本项”列表中选择适当的选项。例如，在数据透视表中要显示每名销售人员与销售人员“毕春艳”的“订单金额”的差异，在“数据显示方式”中选择“差异”；基本字段选择”销售人员”；“基本项”中选择“毕春艳”。
最后单击“确定”按钮完成。
自定义计算功能描述
选项
功能描述
普通 数据区域字段按普通方式而不是指定的函数汇总，此为默认的计算
差异 数据区域字段与指定的基本字段和基本项的差值
百分比
数据区域显示为基本字段和基本项的百分比
差异百分比 数据区域字段显示为基本字段项的差异百分比
按某一字段汇总 数据区域字段显示为基本字段项的汇总
占同行数据总和的百分比 数据区域字段显示为每个数据项占该行所有项总和的百分比
占同列数据总和的百分比 数据区域字段显示为每个数据项占该列所有项总和的百分比
占总和的百分比 数据区域了段显示为每个数据项占该字段所有项总和的百分比
指数 使用公式：（（单元格的值）＊（总体汇总之和））／（（行汇总）＊（列汇总））
２８．８．３　在数据透视表中使用计算字段和计算项
除了以上介绍的多种汇总方式以外，Excel还允许用户向数据透视表中添加计算字段和计算项。计算字段是通过对表中现有的字段执行计算后得到的新字段；计算项则是在已有的字段中插入新的项，是通过对该字段现有的其他项执行计算后得到的。一旦创建了自定义的字段或项，Excel就允许在表格中使用它们，它们就像是在数据源中真实存在的一样。
１．创建计算字段
图中展示了一张已经创建成功的数据透视表，下面下面通过添加计算字段，来写成销售人员提成的计算。
数据
销售人员 求和项:订单金额 求和项:销售人员提成
毕春艳 116,962.99 1,754.44
高伟 68,792.25 1,031.88
何庆 276,244.31 4,143.66
林茂 225,763.68 3,386.46
苏珊 72,527.63 1,087.91
杨光 182,500.09 2,737.50
张林波 123,032.67 1,845.49
总计 1,065,823.62 15,987.35
需要创建计算字段的数据透视表
步骤１．在数据透视表内单击任意单元格，单击数据透视表工具栏的“数据透视表”，在弹出的快捷菜单中单击“公式”，鼠标指向“计算字段”并单击它，激活“插入计算字段”对话框。
步骤２．在“插入计算字段”对话框的“名称”框内输入“销售人员提成”，将光标定位到“公式”框中，清除原有的数据“＝０”
双击“字段”中的“订单金额”，然后在“公式”框的“＝订单金额”后输入“＊０．０１５”（销售人员的提成按１．５％）计算）。
步骤３．单击“添加”按钮，最后单击“确定”按钮关闭对话框。此时，数据透视表的数据区域中已经创建了一个新的字段“销售人员提成”。
２．添加计算项
如果要对上图数据透视表创建“国际业务——国内市场”的计算项，操作步骤如下。
步骤１．单击数据透视表中“销售途径”字段标题或分别单击“销售途径”字段标题下的“国际业务”和“国内市场”，单击数据透视表工具栏的“数据透视表”，在弹出的快捷菜单中单击“公式”，鼠标指向“计算项”并单击它，激活“在销售途径中插入计算字段”对话框。
步骤２．在“在‘销售途径’中插入计算字段”对话框的“名称”框内输入“国际业务—国内市场”，将光标定位到“公式”框中，清除原有的数据“＝０”，双击“项”中的“国际业务”，在“公式”框的“＝国际业务”后输入“—”，再双击“项”中的“国内市场”。
步骤３．单击“添加”按钮，最后单击“确定”按钮关闭对话框。此时，数据透视表行字段区域中已经插入一个新的项目“国际业务——国内市场”。
２８．９获取数据透视表数据
数据透视表创建完成后，用户要想获取数据透视表中数据，可以使用GETPIVOTDATA函数。只需在数据透视表之外的单元格中输入一个等号并单击数据透视表中的一个单元格就可以看到一个替代单元格引用产生的透视数据公式。
GETPIVOTDATA函数有2个参数。
第１个参数是对数据透视表本身的引用，它可以是数据透视表中的任意单元格、分配给数据透视表的数据区域名或存储在表格左上角内的单元格中的标志。
第２个参数则告知Excel所需数据的类型，它包含在双引号，指定各字段名，这些字段名在表中的交叉点就是要获取的数据。第2个参数中的各字段名之间要用空格键隔开。
注意：GETPIVOTDATA函数获取的数据在数据透视表中必须是可见的，否则将返回错误值“#REF！”。
例如，对于图所示的数据透视表，要获取销售人员“毕春艳”在２００６年第三季度的销售数据，可以输入：
GETPIVOTDATA（数据透视表！A1，“毕春艳 2006 3“）
要获取销售人员“高伟”液晶电视的销售数据，可以输入：
GETPIVOTDATA（数据透视表！A1，“高伟 液晶电视”）
要获取销售人员“何庆”在2006年第二季度跑步机的销售数据，可以输入：
GETPIVOTDATA（数据透视表！A1，“何庆 2006 2 跑步机”）
２８．１０创建复合范围的数据透视表
用户可以使用来自同一工作簿的不同工作表或不同工作簿中数据，来创建数据透视表，前提是它们的结构完全相同。在创建好的数据透视表中，每个源数据区域均显示为页轴的一项。通过页轴上的下拉列表，用户可以一览无余地查看各个源数据区域以及对各数据区域合并计算后的汇总表格。
２８．１０．１创建单页字段
要从工作表生成多重合并计算数据区域的数据透视表，可按照如下步骤进行：
步骤１．单击“数据”菜单，选择“数据透视表和数据透视图”命令。
步骤２．“数据透视表和数据透视图向导——３步骤之１”对话框显示出来后，选择“多重合并计算数据区域”选项，接着单击“下一步”按钮。
步骤３．“数据透视表和数据透视图向导——３步骤之２ａ”对话框显示出来后，选择“多重单页字段”选项（此项为默认选项）。
步骤４．选择参加数据透视表合并计算的第一个数据区域，接着单击“添加”按钮。本例中，第一个参加合并计算的数据区域为‘１月份’！$A$1:$E$26。其他区域分别是'2月份'!$A$1:$E$17、‘3月份’！$A$1:$E$23。
注意“在指定数据区域进行合并计算时，要包括行和列的标题，但是不要包括汇总的行和列，数据透视表创建后会自动计算汇总的行和列。
步骤５．对每个希望合并计算的区域重复步骤４。当指定了所有的数据区域后，单击“下一步按钮选择Excel显示数据透视表的位置，再单击”完成“按钮。
步骤６．在默认的情况下，Excel对数据源区域中的数值进行和汇总。如果希望使用不同的汇总方式，双击”求和项：值”标题，选择要用的汇总方式，最后单击“确定”按钮完成。
图２８－５９显示了完成后的数据透视表。现在的页轴项为“全部”，这一页显示了所有月份费用明细的汇总。选择页轴上的其他项，可单独显示各个月份的数据。
２８．１０．２创建自定义页字段
在上例步骤３中，“数据透视表和数据透视图向导——３步骤之２ａ”对话框显示出来后，接受的是默认选项“建单页字段”，完成后的数据透视表页轴会出现Excel默认的“全部”、“项1”、“项2”、“项3”，如果用户需要将页轴中的各项改变为与待合并的各个工作表同名，则可以选择“自定义页字段”。添加各月份的数据区域后，在“字段”的文本框中分别输入“1月份”、“2月份”、“3月份”即可。
２８．１０．３创建复合范围的数据透视表的限制
在创建多重合并计算数据区域的数据透视表时，Excel会以各个待合并的子表的第一列作为合并的基准，如果子表的非数据列有多个，Excel也只会选择第一列。这一点与Excel的合并计算功能比较类似。
２８．１１创建数据透视图
用户可以通过“数据透视表和数据透视图向导”，在所需创建的报表类型中选择“数据透视图（及数据透视表）”来创建数据透视图，
Excel将会在创建一张新的数据透视表的同时，创建一幅数据透视图。此外，也可以先创建数据透视表，接着在数据透视表中选择任意单元格，然后单击“数据透视表”工具栏中的“图表向导”按钮，Excel将在当前数据透视表的基础上创建一幅数据透视图。
不论采用哪种方法，数据透视图和数据透视表都链接在一起，其中之一的改变就会立即影响另一个。