数据透视表的发明历史

题外话：excel2007也有透视表向导，但需要从自定义中添加

如下图，选择【所有命令】，在列表中找到【数据透视表和数据透视图向导】。

导致今天“数据透视表”的想法源自拥有具革命意义的电子表程序Lotus Improv的Lotu公司过道。1986年该公司高技术小组的Pito Salas即有了Improv这一设想。他认识到电子表常常具有各种数据样式，于是得出这样的结论：如果有人能建立识别这些样式的工具，那么他就可以建立起高级数据模型。Lotus公司正是遵循这样的观点开始研发下一代电子表软件的。

在1987年，Lotus公司便向几家公司演示了它的新程序。1988年，Steve Jobs见到了这个程序后就立即希望它能被开发成一个能在其即将发行的NeXT计算机系统上运行的版本。这个程序后来被命名为Lotus Improv，并最终在1991年被移植到了NeXT平台。1993年，Improv 的Windows版本也问世了。

Improv背后的核心观念是：数据、数据视图和公式都应被封装成各自独立的实体，即可以将它们看成不同的动物个体。正是在一个电子表软件中，一组数据首次被冠以一个名字且被包含于更大的分类中。这种命名和分类的功能为Improv（灵活）重置数据清除了障碍。通过Improv，用户可以定义和存储一组类别然后用鼠标只是简单的拖动类别的名称来改变视图。同时，用户也能创建类别总计和分组合计。

Microsoft公司最终在Excel 5的数据透视表功能中吸纳了上述概念。几年后，伴随Excel 97的发行，Microsoft公司是向用户提供了更为高级的数据透视表向导和关键性的功能改进，如增加了“计算字段”功能。Excel 97同时也向开发者开放了透视表缓冲区，缓冲区决定了透视表的生成和控制方式。在Excel 2000中，Microsoft公司引如了数据透视图，从而使用户能够以图表的方式更为直观地展示数据。从Excel 2000开始，数据透视表的改变都是修饰性的（没有实质性的功能改变），这主要是为了回应来自各地的透视表fans的不满意见。

 

Pito Salas—— 数据透视表概念的发明者

数据透视是由Pito Salas首先提出，经过多年发展的一种用于对企业流水信息进行高效处理(排序，分类，汇总)，并且以表格或者图形的方式呈现的功能集合的总称

1、 什么是数据透视表？

 

Ø         数据透视表是用来从Excel数据列表、关系数据库文件或OLAP多维数据集中的特殊字段中总结信息的分析工具。

Ø         它是一种对大量数据快速汇总和建立交叉列表的交互式报表，并可以随时选择其中页、行和列中的不同元素，以达到快速查看源数据的不同统计结果，同时还可以随意显示和打印出你所感兴趣区域的明细数据。

 

2、 数据透视表的作用？

 

Ø         数据透视表有机的综合了数据排序、筛选、分类汇总等数据分析的优点，可方便地调整分类汇总的方式，灵活地以多种不同方式展示数据的特征。一张“数据透视表”仅靠鼠标移动字段位置，动态的改变版面布置，可以按照不同方式分析数据，即可变换出各种类型的报表

Ø         数据透视表也是解决函数公式速度瓶颈的手段之一。

Ø         而且数据透视表和图表和用组成动态图表功能也相当的强大。

因此，该工具是最常用、功能最全的Excel数据分析工具之一。

 

3、 何时使用数据透视表呢？什么样的数据（最简单的EXCEL数据列表）可以作为数据透视表的数据源？

Ø         在您想要使大量复杂的数据更易于理解和更有意义时，使用数据透视表。当您有大量数据需要汇总并想要对每个图表比较多个因素时，数据透视表特别有用。因为数据透视表使您能够将字段拖至布局的不同位置，您可以改变数据的视图来查看更多的详细信息或计算不同的总和。数据透视表是交互式的，因此，您可以更改数据的视图以查看更多明细数据或计算不同的汇总额，如计数或平均值。

Ø         一般而言，数据透视表的数据源都需要是一维的数据列表（你想想自己是否知道什么是一维数据）。

大家看下面的例子：这是一个二维数据列表

                                             

 

如果你想使用数据透视表对其进行分析，那么需要进行一点点的加工，使之变成一维数据列表。做这个加工我们可以利用宏代码，但是使用数据透视表可以更简单一点，下面我们来进行操作……

得到一张一维数据列表：

 

       那么怎样制作数据透视表呢？我们接着往下学……

4、 怎样创建数据透视表？（——请大家举手，谁使用过或是无意中打开过“数据透视表”，请不要害羞的试一试）

步骤：（我们采用Office Online上提供的例子给大家开始介绍怎样做做简单的数据透视表）

²        确定需要了解的信息，确定你想要的信息或问题（这是你操纵数据的前提）

比如EXCEL已经打开，你正在看一张长长的销售图表，如下：

 

数据透视表教程全集\数据透视表一次学会\创建数据透视表1.xls

在本示例中，共有 799 行销售记录，按国家/地区、销售人员、订单金额排列。

大家都处理过大量的EXCEL数据，可能比我更清楚的见过那些信息很多的表，怎样容易的看出表中的信息呢？比如，看到上面的销售图表，你可能会想知道：每一位销售人员的销售额是多少呀？各国家地区的销售额是多少呀？

——请大家畅所欲言，谈谈自己解决这个问题的方法。

——排序、分类汇总

好，大家都做的不错！现在我们来谈谈用数据透视表怎样处理：）

²        使用向导 创建报表视图

 

单击数据中的任意位置，然后在“数据”菜单上，单击“数据透视表和数据透视图”，启动向导。在默认情况下，向导将：

J        使用EXCEL列表或数据库中的数据；

J        准备“布局区域”以创建数据透视表；

J        使用列表中的所有数据；

J        将报表布局区域放置在新工作表中。

 

新产生的工作表中包含：数据透视表字段列表、报表的布局区域，数据透视表工具栏。如果“数据透视表列表”没有出现在屏幕上，那么你可以单击“数据透视表工具栏”中的显示字段列表按钮“”。如果“数据透视表工具栏”没有出现，在“视图”菜单上，指向“工具栏”，然后单击“数据透视表”。

²        利用向导布局

J        创建报表视图并布局：比如：源数据中的每一列都将成为具有相同名称的字段，要创建数据透视表视图的操作就是将字段列表中的字段拖放到布局区域。假如需要计算出每位销售人员的销量，可以通过选择字段名称和字段名称前面的按钮拖动字段；如果将字段拖放到了错误的区域，只需把它拖放到正确的区域即可。而数据透视表的框架可以通过拖放并安放字段来控制。

 

（注意：拖放字段名称后，它仍然保留在原来的列表上，但是字体更改为黑色。以此，你可以判断字段是否已经拖放在数据透视表中；同时你需要注意，并不是所有的字段都需要拖放到数据透视表视图中，而是依据你的需要拖放）

 

OK，一张简单的数据透视表完成了，你是否满意呢？：）

J        数据方向转化：如果你不满意，你说：横轴与纵轴的字段恰好相反了，怎么办呢？很好办，这就需要你转动报表，将单元格A4拖动放至汇总上方，你的透视表发生了什么变化呢？对了，就是下面的样子：

 

              好，这就是将数据从一个方向移动到另外的一个方向。

J        好，想想你还有什么要求？你可能会说，我想把各国的数据放在不同的页面上，没问题，对于数据透视表来说是小菜一碟！这叫做“创建页视图”，满容易理解的名称对吗？

仍然以上面的资料为例，你想将上面不同国家/地区的数量显示在不同页面上，将“国家/地区”字段拖放至“将页字段拖放至此处”即可。所以说，往“页字段”处添加字段时，是不会改变原数据透视表的结构的，它就像一个筛选工具一样，你选择一个备选项，透视表中的数据就仅显示满足此字段的数据。

 

5、 使用数据透视表添加字段（非拖拽）

带着同学们一起将前面的例子重新做一遍。

注意：如果你不想通过拖放的方式布局，那么可以采用另外的一种方式，请大家看“数据透视表字段列表”的右下端的下拉菜单，你可以选中要拖放的字段，然后选择“行政区”，选好后单击“添加”即可。

6、 旋转数据透视表

²        重命名字段：单击字段，可以在“编辑框”内直接更改，也可以将光标移至需要更改的字段，然后按F2键，光标显示在字段的最后面便可以Delete字段，输入新字段。比如：将“订单金额总和”。

 

²        更改排序次序：在“数据透视表”工具栏上，单击“数据透视表”，然后单击“排序并显示前10项”命令。在“自动排序选项”下，单击“降序”；在“使用字段”列表中，单击向下的箭头，选择“订单总额”并确定。现在透视表以降序次序显示销售人员的销售额，从销售额最高的潘金开始，以销售最低的林丹结束。

²        设置数字的格式：单击数据拖放区域中包含数值的任意单元格；单击“数据透视表”上的“字段设置”按钮  ，但后单击“数字”；在“单元格格式”对话框中，单击“分类”列表中的“货币”；在“小数位数”列表中，单击向下箭头是列表中的值为0，这将去掉小数点；单击“确定”即可。

²        刷新数据：如果根据需要你变更了原数据，那么需要在数据透视表中刷新

7、 重命名字段标题，更改排序次序，设置数字格式，刷新数据，更改数据汇总方式，删除整张数据透视表等简单的操作。

好，以上全部就是数据透视表的基础内容，至此你就可以说知道了什么是数据透视表，但这些可以说仅仅是“皮毛”，下面我们来深入一些

PART-2 深入学习

8、 高级使用

²        在同一拖放区使用两个字段

比如要按照季度比较销售人员的销售额，我们先按照上面学过的内容制作一张汇总销售人员订单金额的数据透视表，然后将“数据透视表字段列表”中的“订单日期”字段拖放到已生成数据透视表的行字段“销售人员”左边的区域，形成如下图的样子：

 

接下来，我们将每日信息汇总到季度，将鼠标放在行字段“定购日期”的那一列的任意单元格上，然后单击右键，在快捷菜单上指向“组及显示明细数据”，单击“组合”，此时出现一个叫“分组”的框，你可以在其中选择自己想要的频率，比如：季度，年度等。此时，我们看到的数据透视表可能显得很繁琐，没关系，你可以通过“组合”中的“隐藏明细数据”来调整透视表至你想得到的效果，当然你也可以使用更简单的办法，双击被汇总的字段即可。如下：

 

²        交换位置以获得不同的视图

²        格式化报告,有很多的备用报告格式供备选，注意：在使用外部公式新增行字段时，必须将报告格式化后新增才能增加到行字段的正确位置。注意，我们可以隐藏行字段，但是你可以注意看一下“数据透视表字段列表”会发现，被隐藏的字段由被拖拽使用的粗体字变成了未被使用的细体，所以相当于从数据透视表中取消了此字段，要想恢复，必须重新拖拽进入数据透视表。   

²        在数据透视表外创建公式

在报告外部键入等号然后选择报告内部的单个单元格时，会自动输入 GETPIVOTDATA 函数。GETPIVOTDATA 将从数据透视表中检索数据。该函数的最大好处是，当报告布局更改时它能继续检索正确的数据。如果在布局更改时某块数据的单元格位置也更改了，函数仍将找到并返回正确的值，不管它在哪个单元格。

PART-3 高级运用

9、使用动态数据源

²        好，前面我们生成所有的数据透视表都是依据不变的数据源形成的报告，而我们在现实工作中，可能要对不停变动的数据进行处理报告，比如每月对累计数据进行报告，那这样我们需要所有的工作都重新来过一遍吗？答案是：否！

那么，这就需要使用到“动态数据源”。

首先，在你首次使用的数据源中做一项重要的工作，插入-“定义”。为什么要做这项工作呢？请大家看完后给我答案J

在你的数据源工作表中单击工具栏上的“插入”-“名称”-“定义”，在出现的“定义名称”对话框中给你的数据源取一个你喜欢的名字，然后在“引用名称”中添加一个重要的公式，完成这个公式，你的数据源相当于完成了99%呦！具体的公式是：=OFFSET(销售信息！$A$1,0,0,COUNTA(销售信息！$A:$A),COUNTA(销售信息!$1:$1))

怎么样？大家的答案是什么呢？OK,使用OFFSET函数的目的为了获取一个不断变动的数据源，这样我们在每次需要提供报告的时候直接使用第一次做好的数据透视表更新数据即可。是不是很简单呢？