动态数据透视表的使用方法

主要内容

创建透视表的数据来源分类：

1、外部数据源；

2、内部数据源。

平时工作中，使用内部数据源方法的较多

两种数据源的优缺点:

外部数据源创建的透视表能随数据源的更新而更新；内部数据源创建的透视表一般是先择数据库源的固定

区域，透视表不能随着数据源的更新现更新。

但是，用内部数据源创建的数据透视表是不是就绝对不能随数据源的更新而更新？？？答案是否定的。

一、内部数据源实现动态更新详解：

1、借助Excel列表功能

1）方法：插入－表格(office2010)，数据－列表－创建列表(office2003)

2）特点：简单实用

3）不足：有限动态

EXECL列表在创建数据透视表不足之处是有限动态，只能对新增的记录动态，对新增字段不能动态。注：另外提醒，创建数据透视表时，如果选择区域里自动填充的是DATABASE，这时是不能实现动态效果

的

2、借助命名公式

1）方法：定义一个动态引用的名称

DATA=OFFSET($A$1,,,COUNTA($A:$A),COUNTA($1:$1))

新区域=OFFSET(起点单元格,,,新区域的行数,新区域的列数)

2）特点：能够创建真正动态透视表

3、Sql方法（其实质就是把Excel数据源当成外部数据）

方法：Select 字段名from [表名$] where 条件

1）如果有多个字段名，用逗号隔开，如果需要查询所有字段，则可以用通配符"*"代替；

2）字段名也可以通过计算得到。

二、应对多重区域数据源

1、使用多重合并计算区域功能创建数据透视表

1）单页字段

特点是：被合并的数据源区域的每个工作表均显示为页字段中的一项。

不足之处是：只能保留1个列字段，其余均为求和，无法详细统计。

2）自定义字段

特点：能够直接增加页字段名称，无需去透视表内修改，同事可以创建多个页字段。

2、基于动态的多重合并计算区域数据源创建数据透视表

1）每一个数据源表转换为列表

注：需要对创建完成的列表切换汇总行，否则向列表中添加新数据后，多重计算合并区域的数据透视表将

无法进行自动扩展，得到最新数据

2）每一个数据源表定义名称

DATA1=OFFSET(sheet1!$A$1,,,COUNTA(sheet1!$A:$A),COUNTA(sheet1!$1:$1)

DATA2=OFFSET(sheet2!$A$1,,,COUNTA(sheet2!$A:$A),COUNTA(sheet2!$1:$1)

3）Sql方法

Select * from [表1$] union all select * from [表2$]

3、评说多重合并区域透视表

1）多重合并区域数据透视表的不足

只能保留第一个完整的列字段信息，其他全部合并

2）把Excel数据当作外部数据源来处理即可实现多重合并的行列字段信息的保存

方法：数据－现有数据－（找到文件）打开－编辑查询

Select “1季度” , * from [1季度] union all Select “2季度”, * from [2季度]

4、用创建多重合并区域透视表几个实例应用

1）用二维表创建数据透视表

其中一维维某一个字段的分项目。比如：科目中的语文、数学、英语利用多重合并区域透视。可以讲行、列、值等分别显示，然后查看源数据后修改行字段即可。

2）在多行数据区域查找不重复值

选中数据区域，在其上面多选一行、右边多选一列，创建多重合并区域数据透视表。将行标签选为值，列

标签选为页1。在修改标签名称即可。

以下转自讲师的博客：

动态数据源的透视表

多重合并数据源的透视表

使用动态数据源创建数据透视表

Excel数据透视表的数据源来源分为两种：一excel工作簿和工作表当中的数据；二工作表之外的其他类型的外部数据，如：文本文件，access数据库文件，sql sever数据库文件。如果数据透视表是基于外部数据源创建的，当外部数据源发生变化时，只要更新数据透视表，新的数据也会被即时的反应到刷新后的数据透视表中。如果基于excel工作表里面的数据来创建数据透视表的话，因在选择数据透视表数据源时，选择的是一个固定的区域。如果数据源的数据列表发生变化（该变化仅指增加数据记录或数据字段），此时刷新数据透视表，新增的数据不会反应到数据透视表中来。本节所讲内容即针对如何不借助外部数据源的情况，而只是针对excel工作表当中的数据来创建动态的数据透视表。此处的动态是指数据源中增加记录或字段，刷新数据透视表后，数据源中新增的数据记录或字段也同步反应在数据透视表中。本课介绍

两种方法实现这个目标。下面对此进行详述。

方法一：借助Excel列表

特点：简单实用

步骤：

1创建列表：选中数据源当中的单元格，选择“数据”—》“列表”—》“创建列表”，在出现的对话框中选择数据列表的位置。勾选“列表有标题”。对于创建好的列表，我们可以做增减记录、筛选、排序、汇总计

算等操作。

2 创建数据透视表：单击“数据”—》“数据透视表和数据透视图”—》“下一步”—》“下一步”—》“完成”。

根据需要将各字段拖到数据透视表相应的数据区域中，进行相关的设置。

3 验证数据透视表的动态性：打开数据源所在的表，在数据记录的最下面一行（即有“*”的行）新增记录，此时回到数据透视表所在的表，点击刷新按钮，数据源中的新增记录即时的反应到数据透视表中来

了。

不足：有限动态，即只能对列表中新增的记录进行动态的反映，如果列表新增字段，而刷新后的数据透视表中并不能同步反映新增的字段。可以反应纵向增加的数据记录，而不能反应横向增加的数据字段。

注意：在创建列表是，excel会自动给创建的列表一个名称（database），我们在创建数据透视表时一定要看清数据源区域是database这个名称还是列表的实际单元格地址，如果是database这个名称，创建的数据透视表是无法使用以上所讲的动态刷新的特性的。

方法二：借助命名公式

特点：

步骤：

1、定义名称，动态引用当前工作比中的数据区域，方法：单击“插入”—》“名称”—》“定义”，在出

现的定义名称对话框中输入数据区域的名称，在引用位置处输入公式“=offset

（$a$1,,,counta($a:$a),counta($1:$1)”。

Offset参数讲解：

counta($a:$a) A列的非空单元格数量

counta($1:$1)行1的非空单元格数量

新区域=offset（起点单元格，，，新区域的行数，新区域的列数）

2、使用多重合并区域功能创建数据透视表

步骤：点击“数据”—》数据透视表和数据透视图—》多重合并计算数据区域（快捷键：alt—>D—>P）—》下一步—》创建单页字段—》下一步。（页字段：本例指数据区域来源）。依次添加数据区域，点击下一

步，点击完成。

可以看到新生成的数据透视表包含了四项，行、列、值、页1。根据需要将各个字段拖动到相应的区

域中。

缺点：数据列表变成一个行字段，其他字段都变成列字段，整个表变成了一个维度，不便于数据的

分析。

解决方法：

1、尽量将数据放在一个工作表里面保存。

2、把数据表格变成外部数据源来处理。

使用多重合并区域功能创建数据透视表的其他应用

1、处理二维表格（包括：对二维表格创建数据透视表及将二维表格转换为一维表格）

2、多行多列数据特殊统计的任务（在多行多列区域统计不重复值的区域及对其进行排序，操作时

注意将行和列往左往上多选一行空白的区域，排名时通过增加计算字段来实现）。

方法三：通过外部数据源创建数据透视表

新建工作表，点击数据—》导入外部数据—》导入数据—》选择数据源所在的工作簿。

在选择表格对话框中选择需要导入的excel工作表，在出现的导入数据对话框中点击编辑查询按钮，在随后出现的编辑OLE DB查询对话框中的命令文本输入对话框中输入以下文本：

Select “1季度”,* from [1季度$] union all

Select “2季度”,* from [2季度$] union all

Select “3季度”,* from [3季度$]

输入完成后点击确定。然后点击创建数据透视表。

在出现的数据透视表中将各字段拖入到数据透视表相应的数据区域中