数据透视-适应变化的数据源

涉及内容：

数据透视源数据刷新，更改；

使用插入表格或 offset 实现源数据区域的自动更新；

正确调用 Excel 常见的数据源：包括 access，sql，excel 以及 text，excel；作用：

1、  多种更新形式，保证数据透视的结果是最新的结果；

2、  源数据增加行列时，不需要更改源数据区域，实现表格的自动更新；

3、  摆脱 Excel 表格行项目最上限的限制，通过引用外部数据实现更多数据的透视。

一、 数据透视源数据更新:数据透视表完成后，如果原始数据有修改，如列标题变化、某个数值变化等，

由于计算量可能很大，数据透视表是不能实时动态变化的，需要通过一定的设置和操作来完成更新；

下图为某公司电话呼出相关记录，透视数据为每个分机每天的电话呼出时长，此时如果地址列名称更

改为“呼入地址”可通过如下办法进行更新；

①   手动全部刷新（Ctrl+alt+f5）：工作簿中所有透视或相关数据全部刷新，一般情况下点击两次，保

证全部刷新成功；

②   右键单击透视区域-刷新：只刷新该透视表源数据以及同一源数据的其他透视表；

③   文件打开时自动刷新：右键单击透视表区域-数据透视表选项

数据页签下勾选“打开文件时刷新数据”，每次打开文件时，文件将自动完成刷新；

④   Vba 语言更新：通过建立宏及运行宏来实现刷新，首先建立一个对象，为该对象指定宏，宏名称：

“刷新测试 2”，点击新建

在弹出的对话框内输入“ThisWorkbook.RefreshAll”

或  Dim pc As PivotCache

    For Each pc In ThisWorkbook.PivotCaches         pc Refresh

        Next  pc

当鼠标放在刷新按钮上呈现小手状态时点击即可完成刷新操作；

二、 数据透视源数据区域变化：透视表数据源区域大小或位置发生变化时可通过更改数据区域实现透

视表内容更新；在选项选项卡下找到更改数据源功能键

 早弹出的对话框中通过选择数据源按钮实现数据区域更改；

 三、 原始数据增加行列

①   手动增加-不在最后添加：当我们对原始数据增加或删除行时，不建议在行列末尾后增加，建议在

最后一行一列前增加，这样增加的数据可通过刷新更新到透视表，如果一定要在行列末尾后增加，

增加后可更改数据源或选用以下两种方式设置数据源，注：添加列标题，否则数据透视时会报错

②   用 Offset()配合名称实现动态区域

首选选中原始数据区域-公式选项卡-定义名称-输入名称“电话记录源数据”-引用位置输入：

=OFFSET(明细!$A$1,0,0,COUNTA(明细!$A:$A),COUNTA(明细!$1:$1)) -确定

插入数据透视-公式选项卡-用于公式-选择“电话记录数据源”

 原来的数据引用区域

新透视表的数据区不在是一个固定的引用范围，而是一个带名称的表区域，该区域范围通过 offset 函数可自动增加或减少，从而实现原始数据增减后，通过刷新操作即可完成相关行列更新

③   插入表对象实现动态区域：通常我们说的表格是表格的普通区域，我们可以通过插入表格或套用

表格样式将普通区域变成对象区域，该对象区域自动拥有一个名称并且当在最后一行或列增加数

据时，表区域自动扩展

注：要紧挨着最后一行或一列增加数据，增加的列对象区域会对齐进行自动命名

选中源数据区域-插入选项卡-表-创建表对话框数据来源默认为选中区域-确定

注意看上图红框区域：增加了设计选项卡，说明该区域由普通区域转换为对象区域，由于对象区域的

特性，通过引用对象区域创建的数据透视表在源数据增加行列后刷新即可

四、 数据透视常见外部数据源：excel2013 行项目一百多万条，数据透视的行项目没有任何限制，如果

要透视一个 300 万的数据，可通过引用外部数据来实现对该源数据的数据透视分析，常见的外部元数

据有以下几种。

①   来自 Access

新建数据透视表-使用外部数据源-选择链接-浏览更多

下图显示的是 excel 能够进行外部链接的所有数据格式，通过左侧的导航栏找到我们要访问的文件即

可完成外部数据的引用，后续操作与正常透视表操作一致；

②   将 SQL Server 数据导入 excel

数据选项卡-自其它来源-来自 SQL Server-输入服务器名称-用户名/密码等相关信息

寻找数据库中需要进行透视的表格-点击完成-可以选择导入成为表格、透视表、透视图等格式

 确定后即可对该表进行数据透视操作

③   来自 Text（控制面板，管理工具，新建 ODBC 数据源）：不能直接通过引用外部数据来创建跟文本文件的链接,如果想链接 text 文件，可通过如下步骤进行链接控制面板-管理工具-数据源（ODBC）

 点击添加-选择 txt、csv 格式-点击完成

 取消勾选“使用当前目录”-点击选择目录-找到相应的文件-确定

 点击确定-

完成建立一个通过 odbc 到一个文本数据源，数据源建立后，我们就可以通过数据透视-引用外部数据-

新建源

 选择 ODBC DNS-下一步，找到我们刚才新建的 testcsv，点击下一步

 选中要导入的数据-点击下一步-点击完成

 数据透视表即可引用到该文本数据-点击确定后

右下角会显示读取进度，完成后我们即可对该文本中的数据进行数据透视分析

④   来自 Excel：当我们将某个普通区域变成对象区域后，我们在创建 excel 数据透视表-选择外部数据的时候会多一个选项卡叫：表格（07 以后版本有，所有打开的所有工作簿中的对象区域都会显示

在这）-选择相应表格-点击打开-即可进行数据透视操作；效果与选中表区域插入数据透视效果一样，同样 excel 在数据透视表

中对所有对象区域已经做了提示；

⑤   通过 microsoft query 可建立各种数据连接，同时使用 sql 语句进行查询后，使用数据透视分析查询后的数据，此处不做具体介绍