王佩丰数据透视表（六到十一讲）

动态引用数据源区域。

结合新建名称和offset函数，设置一个动态数据区域。

插入数据透视表时引用这个名称即可。

Gerpivotdata函数可以动态地引用数据透视表中的数据，默认是开启的。

举个例子，在透视表外引用单元格B5，会显示

即动态引用常熟区域的彩盒金额，此后如果常熟和彩盒的顺序改变了，该单元格也会动态调整，引用的数据仍然是常熟的彩盒。

这个动态引用功能可以在数据透视表工具栏的分析——选项处的生成Gerpivotdata处进行设置。

关闭此功能后再引用数据透视表中的数据就不会动态调整了，会引用固定位置的数据。

上面的Getpivotdata函数的第二个参数表示A3单元格所在的数据透视表，第一参数表示值字段，后面的参数表示字段和标签。

练习：通过结合数据有效性和Getpivotdata做出下面的结果。

先通过数据有效性建立序列，再在第四列中写上公式即可。

参数一后面&""将数值转为文本格式。

创建单页字段组合。

有时需要总结好几个工作表中的数据。

比如要对三个月的数据进行汇总。

顺序按下Alt加d加p，可以打开数据透视表和数据透视图向导。

默认设置数据源的第一列是透视表的行字段，其他列都是列字段，通过切换页字段可以显示不同工作表的数据。

不过这样做出来的透视表自由度小。

自定义页字段

向导中将创建单页字段改成自定义页字段。

多文件合并

现在有两个文件，南京和苏州的数据，每个文件有三个工作表。

方法和前面差不多，向导处选自定义页字段

得到的透视表中有城市和月份两个页字段。

在上面的创建单页字段和自定义页字段中，可以结合新建名称，这样创建的透视表是动态的。

比如将一个文件中的三个工作表中的数据都用offset新建名称

在相同中选定区域时直接使用。

若源数据发生增删，刷新透视表即可。

将源数据从左图调整成右图

通过左图的数据来创建单页字段，建好透视表后双击透视表的右下角即可。

根据外部文件创建透视表

打开属性，有编写sql语句的地方。通过sql语句可以获取指定数据。

select 日期,产品类别,金额 from [数据$]

表示从数据文件中获取指定的三列。

如果数据文件中有三个表

select * from [苏州$] union all select * from[无锡$] union all select * from [昆山$]

表示获取三张表中的所有数据。

可是表中没有城市这一列，而又需要使用到城市列，可以通过sql语句构建一个。

select "苏州" as 城市,* from [苏州$] union all select "无锡" as 城市,* from [无锡$] union all select "昆山" as 城市,* from [昆山$]

通过这个语句建立的透视表就有了城市字段。

将城市字段设置为列字段。

多个工作表的数据结构不相同，通过sql语句选择指定数据。

两个工作表差异很大时

左图是表一实际情况，右图是表二计划目标，做出下面这个效果，即将实际和计划进行比较。

将表二的数据移植到表一，实际效果会是如图，新增了类型

sql语句应该写成

select "实际完成" as 类型,* from [销售数据$] union all select "计划完成" as 类型,null,null,null,null,null,null,所属区域,null,null,金额,null from [销售计划$]

得到透视表后，将区域设置为列字段，类型设置为行字段，金额设置为值字段，通过计算项求出计划和实际的差额，最后再调整一下即可得到上面的效果。

上面那个例子只是给出了区域的计划销售额，这里还有个例子，给出了销售部门，产品类别和销售区域的计划金额。

要求做出下面这种效果

结合两个数据表

写出sql语句。

创建好透视表之后，将指定的行列值字段放入即可，再通过计算项得出实际和计划的差额，再通过条件格式调整透视表格式，插入指定切片器即可。

跨文件创建透视表

有三个数据文件，南京、苏州和销售计划，城市文件分为三个工作表

左图是城市的数据结构，右图是计划的数据结构。

结合起来如图。

所以sql语句需要为城市文件新建三个列，为计划文件新建一个列。

因为最终是根据sql语句中提供的文件地址来引用数据，所以源数据可以随意引用一个不相关数据。

得到透视表后设定相关字段即可。

关系型表合并

左图和右图就是关系型表，通过产品类型和品名关联，总金额通过左图的数量乘右图的单价得出。

通过sql语句联结两个表

select [数据$].*,[产品信息$].产品单价 from [数据$] left join [产品信息$] on [数据$].产品类别 = [产品信息$].品名

获得数据透视表后通过计算字段，将单价和数量相乘，得出销量。

sql语句可以使用缩写

现在有三个关联表，分别是数据，产品信息，促销信息，通过产品类型和品名关联。

sql语句如下：

select [数据$].*,[产品信息$].产品单价,[促销信息$].折扣 from [数据$],[产品信息$],[促销信息$] where [数据$].产品类别 = [产品信息$].品名, [数据$].产品类别=[促销信息$].品名

此语句将数据工作表的所有数据和另外两个工作表的产品单价和折扣列结合在一起。

使用Microsoft query工具

在工具栏点击数据——自其他来源——来自Microsoft query，选择excel file，打开指定的文件

记得在选项处勾上系统表。

添加表到query中

选择数据，将数据导入文件做成透视表。

数据文件中有三个城市的数据，做出以下透视图。

sql语句：

select "昆山" as 城市,* from [昆山$] where (数量<20) union all select "苏州" as 城市,* from [苏州$] where (数量<20) union all select "无锡" as 城市,* from [无锡$] where (数量<20)

通过where语句筛选出数据中数量小于20的数据。

将指定字段放到透视表中即可。

使用Microsoft query工具结合两个关系型表，创建透视表。

将关联列连接起来，不连的话会得到一个笛卡尔积。

选中数据中的全部和产品信息中的单价，创建透视表，新建一个字段计算销量 = 数量*单价

query中还可以设置条件对数据进行过滤。

PowerPivot工具的使用，xlsx格式下才能使用。

打开一个数据文件，添加到数据模型

将数据加载到PowerPivot中

、

可以创建透视表。

如果源数据中有日期的话会自动创建年和季度和月

从excel文件中导入数据到Powerpivot

当数据文件中有多个关系型表时。

导入含有关系型表的数据。

在关系图中连接数据表间的关系

新建透视表，设置好行列标签

通过新建度量值计算出总金额

另一种方法计算金额

重新调整表间关系

在数据工作表中添加三个新列，将产品信息的产品单价和促销信息的折扣列关联过来，最后计算出金额