2.1 传统Excel数据透视表的工作原理

本书的案例素材是一份假想的小型书店的销售数据库，尽管是虚拟数据，但这份数据是根据企业实际情况合理构造出来的，为了方便学习，做了专门的设计，比真实生产数据更适合Power Pivot学习使用。

我们之所以使用虚拟数据而不使用真实数据，第一个原因是使数据尽量涵盖学习Power Pivot的所有方面；第二个原因是避免真实数据中存在的干扰数据，使读者将有限的学习精力集中于最重要的内容上。

假设在这个小型书店中，店员每销售一本书，计算机都会在图书销售系统数据库中生成一条销售记录。虽然原始数据存储于销售系统数据库中，但由于大部分业务系统都有数据导出功能，因此，为了使读者快速熟悉数据，我们将图书销售系统数据库中的数据导出到了Excel文件中，如下图所示。

在上图中的图书销售数据中，第一行是表的列标题（又称为字段名称），从第二行开始，表中的每行（又称为每条记录）都代表一次销售事件。表中的每列内容都是由列标题表示的图书销售数据的不同属性。该数据表中每列的具体内容如下：

A.所售图书所属的图书大类（T0大类K）。

B.所售图书所属的图书子类（T1子类K）。

C.所售图书的图书名称，这里用书号表示（T2书号K）。

D.图书销售事件的发生时间（T3销售日期）。

E.销售事件的销售款到账日期（T3到账日期）。由于Power Pivot的学习需要，我们假设书店不一定能在销售事件发生当日立即收到销售款。

F.所售图书的原始单价（T4原始单价）。

G.所售图书的实际售价（T3销售单价）。

H.销售事件的图书销售册数（T3销售册数）。

I.所售图书的作者（T4作者）。

J.所售图书的封面颜色（T4封面颜色）。这一列内容纯粹是为了学习Power Pivot设计的，真实的图书销售系统一般不会专门记录这个属性。

下面，我们基于该数据源制作一个传统Excel数据透视表，具体方法如下：

选中数据源，选择“插入”→“表”→“数据透视表”命令，在新生成的Excel工作表中插入数据透视表，并且按照下图，在Excel数据透视表界面右侧的“数据透视表字段”视图中，分别在行区域、列区域、值区域、筛选区域用鼠标拖入相应字段。

Excel数据透视表是可以直接与外部数据源动态连接的，因此在当前案例中，为了方便学习，我们将图书销售数据从外部的图书销售系统数据库中导入本地Excel中，得到如下图所示的传统Excel数据透视表。该数据透视表的分析目的是查看每个图书子类（数据透视表的行标题）中不同原始单价的图书（数据透视表的列标题）的销售总册数。

上图是比较常见的数据透视表布局，事实上，我们还可以进行更炫酷的数据透视表布局设置。例如，将鼠标光标放置于数据透视表中，选择“分析”→“筛选”→“插入切片器”命令，在数据透视表中插入切片器控件；对于日期类型的字段，选择“分析”→“筛选”→“插入日程表”命令，在数据透视表中插入日程表控件。经过以上操作，新的数据透视表布局如下图所示。

为了更深刻地理解数据透视表的工作原理，我们需要介绍两个概念，分别是数据透视表值区域和数据透视表筛选环境，这两个概念可以分别简称为透视表值区域和透视表筛选环境。

数据透视表值区域是指数据透视表的“数据汇总区”。要计算数据透视表值区域中每个单元格中的数值，首先确定每个单元格所对应的数据透视表筛选环境，然后以这个数据透视表筛选环境为筛选条件对数据源进行筛选，从而得到若干个数据源子集，最后根据数据透视表值区域中指定的计算方式（如求和、计算平均值、计数等）对这些数据源子集进行汇总计算。

数据透视表筛选环境是指在数据透视表中，所有对数据透视表的数据源起筛选作用的各种数据透视表周边设置，包括对数据透视表的行标题、列标题的设置，对筛选字段的设置，以及对数据透视表切片器控件和日程表控件的设置，等等。数据透视表筛选环境针对的是数据透视表值区域中的每个单元格，即数据透视表值区域中的每个单元格都对应着不同的数据透视表筛选环境，请参照下图。

在上图中的数据透视表中，我们除了对行标题、列标题和筛选字段进行了常规设置，还通过日程表控件将“T3销售日期”字段的日期限制为2015年，并且通过切片器控件将“T4封面颜色”字段的图书封面颜色限制为“蓝”。新的数据透视表筛选环境下的数据透视表布局如下图所示。

下面以Excel数据透视表值区域中数值为6的单元格（Excel数据透视表D6位置的单元格）为例进行研究。

这个数值6是怎么得来的呢？根据前面提到的关于数据透视表的重要事实可以知道：这个值是先由该单元格所对应的数据透视表筛选环境对数据源进行筛选，得到一个数据源子集，然后根据数据透视表值区域定义的汇总方式对这个数据源子集进行汇总得到的。

换一个简洁点儿的表述就是，这个数值6是由其单元格所对应的数据透视表筛选环境（筛选条件）对数据源进行筛选后汇总得到的。在上图中，数值6所在的单元格所对应的数据透视表筛选环境如下：

行标题“T1子类K”为“21课内”。

列标题“T4原始单价”为“70”。

日程表“T3销售日期”为“2015”年。

切片器“T4封面颜色”为“蓝”。

筛选字段“T0大类K”为“全部”，即无筛选限制。

在本案例中，数据透视表对值区域中的“T3销售册数”字段进行的汇总操作是“求和”，从而得到数值6。这里，我们需要清楚的是，数据透视表对其值区域中的每个单元格都进行相同逻辑的操作，只是不同单元格因为位置不同，所以对应的数据透视表筛选环境也不相同。

数据透视表值区域中的每个单元格都对应着一个数据源子集，下面用数据透视表中的“显示明细数据”来验证这个观点。

在数据透视表的默认设置下，双击数据透视表值区域中任意一个单元格，会立即在Excel中生成一个新的工作表。该工作表中的内容是从数据源中提取出来的，对应所双击单元格的数据源子集的明细数据。也就是说，当双击数据透视表值区域中任意一个单元格时，“显示明细数据”功能会对数据源应用该单元格所对应的数据透视表筛选环境中定义的筛选条件，并且用生成一个新工作表的方式展示所双击单元格对应的数据源子集。

双击D6单元格，得到该单元格对应的数据源子集明细数据如下图所示。

由上图可知，这个数据源子集完全符合D6单元格对应的数据透视表筛选环境。D6单元格所对应的数据透视表筛选环境有如下几点。

● 行标题“T1子类K”为“21课内”。

● 列标题“T4原始单价”为“70”。

● 筛选字段“T0大类K”为全部，即无筛选限制。

● 日程表“T3销售日期”为“2015年”。

● 切片器“T4封面颜色”为“蓝”。

我们虽然只研究了数据透视表值区域中的一个单元格，但是要知道，数据透视表值区域中的每个单元格都对应着一个应用该单元格所对应的数据透视表筛选环境的数据源子集。

在传统Excel数据透视表中，我们几乎不能对数据透视表值区域所对应的数据透视表筛选环境进行任何修改，从而满足更加灵活的数据分析需求，这也是传统Excel数据透视表在数据分析能力上的最大短板。

在Power Pivot中，我们可以使用DAX表达式对Power Pivot超级数据透视表值区域中单元格所对应的数据透视表筛选环境进行修改，从而满足复杂的数据分析需求，这是Power Pivot之所以Power（强大）的最主要原因。