办公技巧数据透视表下载(轻松办公系列进阶课堂-OfficeExcel数据透视表和透视图（二十四）)

轻松办公系列进阶课堂-OfficeExcel数据透视表和数据透视图

（二十四）

使用数据透视表和数据透视图向导

（3）

检索数据透视表数据时每次检索一个报表筛选项

如果数据透视表连接到一个包含大量数据的外部开放式数据库连接 (ODBC) 数据源，那么检索这些数据可能会导致计算机内存不足或查询（查询：在 Query 或 Access 中，查询是一种查找记录的方法，而这些记录回答了用户对数据库中存储的数据提出的特定问题。）运行缓慢。为了防止产生上述问题，请对数据透视表应用报表筛选器，以便根据所选的项（源数据：用于创建数据透视表或数据透视图的数据清单或表。源数据可以来自 Excel 数据清单或区域、外部数据库或多维数据集，或者另一张数据透视表。）仅检索当前这组值所需要的项（项：数据透视表和数据透视图中字段的子分类。例如，“月份”字段可能有“一月”、“二月”等项。）。

一、检索报表筛选数据概述

使用数据透视表和数据透视图向导中的“数据透视表字段高级选项”对话框，可以配置报表筛选器，以便从数据透视表的数据源检索所选数据。默认情况下，当您创建或刷新（刷新：更新数据透视表或数据透视图中的内容以反映基本源数据的变化。如果报表基于外部数据，则刷新将运行基本查询以检索新的或更改过的数据。）报表时，Microsoft Office Excel 会检索该数据透视表的所有源数据，其中包括筛选出的数据（在报表中不显示）。如果将报表筛选器配置为仅检索在筛选器中指定的项的数据，您就能够根据需要检索数量更少的数据。每次在筛选器中显示不同项时，都仅检索该新项的数据的子集。您可以配置任意数目的报表筛选器，然后根据需要加以应用。

对性能的影响

当配置报表筛选器以便为一个或多个（但不是所有）项检索数据时，用于汇总完整数据集的“(全部)”项不可用（灰显），“显示报表筛选页”命令（在“选项”选项卡上的“数据透视表”组中，单击“选项”旁的箭头时会显示该命令）也不可用。每次选择不同项时，检索的数据会较少，但是还需要等待数据检索。如果您的计算机与外部数据源断开连接，则在重新连接到数据源之后才能选择其他项。不过，使用此选项与使用其他选项相比可以处理更大量的数据。

如果您检索所有项的全部数据，那么初始数据检索操作将花费时间较长，但是以后在报表筛选器中选择新项时，将不会出现延迟，这是因为您需要的所有数据都已加载到计算机的内存中。选择“(全部)”项，您还可以打开完整数据集的汇总，并重新组织报表以便在其他区域中使用报表筛选器。如果要检索的数据总量在计算机可用内存限制范围内，则利用“(全部)”选项会实现更多功能。

移动报表筛选器

如果您将一个已配置为检索所有项的所有数据的报表筛选器移动到另一个位置（例如，将它作为行标签），Excel 将尝试检索所有项的数据，则可能会耗尽计算机的资源。不过，您可以阻止用户执行此操作。

当报表筛选器不可用时为项检索数据

在以下情况下无法使用此功能：

基于 OLAP （OLAP：为查询和报表（而不是处理事务）而进行了优化的数据库技术。OLAP 数据是按分级结构组织的，它存储在多维数据集而不是表中。） 数据库源数据的报表会根据需要自动查询数据，所以请不要使用此功能。数据透视表正与一个或多个其他数据透视表共享数据缓存。您要查询的外部数据库的 ODBC 驱动程序 （开放式数据库连接 (ODBC) 驱动程序：用来连接到特定数据库的程序文件。每个数据库程序（如 Access 或 dBASE）或数据库管理系统（如 SQL Server）需要不同的驱动程序。）不支持参数查询 （参数查询：一种查询类型，当运行参数查询时，将提示输入用于为结果集选择记录的值（条件），这样同一个查询就可用于检索不同的结果集。）。Microsoft Office 附带的 ODBC 驱动程序均支持参数查询。您可以与第三方驱动程序供应商联系，以确定他们提供的驱动程序是否支持参数查询。

二、为每一项或所有项检索报表筛选数据

只能对连接到 ODBC 数据源的数据透视表使用此功能。您必须使用数据透视表和数据透视图向导来完成下列步骤。

为了启动数据透视表和数据透视图向导，请按 ALT D P。

为了将数据透视表和数据透视图向导添加到“快速访问”工具栏，请执行下列操作：

单击该工具栏旁的箭头，然后单击“其他命令”。在“从下列位置选择命令”下，选择“所有命令”。在该列表中，选择“数据透视表和数据透视图向导”，单击“添加”，然后单击“确定”。在该向导的“步骤 1”页上，选择“外部数据源”，然后单击“下一步”。在该向导的“步骤 2”页上，单击“获取数据”。连接到数据源。在该向导的“步骤 3”页上，单击“布局”。在“布局”对话框中，将一个或多个字段拖动到“报表筛选”区域。对于每个报表筛选字段，执行下列操作：双击该字段。

将显示“数据透视表字段高级选项”对话框。

要在每次从报表筛选器中选择一个新项时仅从数据源中检索最新数据，请单击“每选择一个报表筛选字段项都检索外部数据源(节省内存)”。

为了防止报表筛选字段被移动到“行标签”、“列标签”或“值”区域，请选中“禁用对该字段进行透视(建议)”复选框。

要回到默认操作，即每次从报表筛选器中选择新项时都从数据源检索所有数据，请单击“同时检索所有报表筛选字段项涉及的外部数据(减少透视次数)”。

取消数据透视表之间数据缓存共享

默认情况下，基于同一数据源（工作表中的一个单元格区域或数据连接）的数据透视表共享一个数据缓存，不过您可以使用多种方法取消此数据缓存的共享。

一、了解有关数据透视表数据缓存的详细信息

数据透视表的数据缓存是计算机内存上的一个区域，Microsoft Office Excel 使用该区域存储报表数据。为了提高性能并减小工作簿大小，Excel 会在基于同一单元格区域或数据连接的两个或多个数据透视表之间自动共享数据透视表数据缓存。如果两个或多个数据透视表的单元格区域或数据连接不同，那么这些报表之间不能共享数据缓存。

不过，有时候您可能并不希望基于同一数据源的两个或多个数据透视表共享数据缓存。例如：

您不希望所有数据透视表中都显示计算字段和计算项。您不希望在所有数据透视表中都按同一方式分组字段。您不希望同时刷新所有数据透视表。使用共享数据缓存时不支持您想使用的功能，如应用报表筛选器时为选择的一项或多项检索数据。

注释：不能共享连接到联机分析处理 (OLAP) （OLAP：为查询和报表（而不是处理事务）而进行了优化的数据库技术。OLAP 数据是按分级结构组织的，它存储在多维数据集而不是表中。） 数据源的数据透视表的数据缓存，这是因为该数据缓存与非 OLAP 数据源的数据缓存的使用方式不同。

二、取消数据透视表之间数据缓存共享

有多种方法可以取消数据缓存共享，包括：

使用数据透视表和数据透视图向导创建一个新数据透视表，使该新数据透视表与另一报表基于同一单元格区域但不共享数据缓存。通过临时重新定义数据区域以强制 Excel 取消数据缓存共享，可以取消基于该单元格区域的数据透视表之间的数据缓存共享。通过为工作簿中的每个数据透视表创建唯一的数据连接，可以取消基于同一数据连接的两个或多个数据透视表之间的数据缓存共享。创建一个新数据透视表，使其与另一报表基于同一单元格区域但不共享数据缓存确保有一个现有数据透视表基于您要用于新数据透视表的区域。在工作表中数据透视表外，单击任意空白单元格。要启动数据透视表和数据透视图向导，请按 Alt D P。

为了将数据透视表和数据透视图向导添加到“快速访问”工具栏，请执行下列操作：

单击该工具栏旁的箭头，然后单击“其他命令”。在“从下列位置选择命令”下，选择“所有命令”。在该列表中，选择“数据透视表和数据透视图向导”，单击“添加”，然后单击“确定”。在该向导的“步骤 1”页，单击“Microsoft Office Excel 数据列表或数据库”，然后单击“下一步”。在该向导的“步骤 2”页，选择您希望新数据透视表要基于的数据区域，然后单击“下一步”。当数据透视表和数据透视图向导显示一条消息询问您是否希望共享数据缓存时，单击“否”。在该向导的“步骤 3”页，为新数据透视表选择一个位置，然后单击“完成”。取消基于单元格区域的数据透视表之间的数据缓存共享确保至少有两个数据透视表是基于相同的单元格区域，并且这些报表共享同一数据缓存。在要取消其数据缓存共享的数据透视表中，单击一个单元格。为了启动数据透视表和数据透视图向导，请按 Alt D P。

为了将数据透视表和数据透视图向导添加到“快速访问”工具栏，请执行下列操作：

单击该工具栏旁的箭头，然后单击“其他命令”。在“从下列位置选择命令”下，选择“所有命令”。在该列表中，选择“数据透视表和数据透视图向导”，单击“添加”，然后单击“确定”。在该向导的“步骤 3”页，单击“上一步”返回到“步骤 2”页。在该向导的“步骤 2”页，请确保选中您希望数据透视表要基于的相同数据区域，但至少要少选一行内容。

例如，如果区域为 $A$1:$E$286，请将区域更改为 $A$1:$E$285。

单击“下一步”。在该向导的“步骤 3”页，请确保选中“现有工作表”，并且位置是相同的，然后单击“完成”。

这时，该数据透视表具有一个不同的数据缓存，而且是基于不同的数据区域。

确保在要取消其数据缓存共享的数据透视表中，选中一个单元格。为了再次启动数据透视表和数据透视图向导，请按 Alt D P。在该向导的“步骤 3”页，单击“上一步”返回到“步骤 2”页。在该向导的“步骤 2”页，将数据区域更改回初始区域。

例如，如果当前区域为 $A$1:$E$285，请将区域更改回 $A$1:$E$286。

单击“下一步”。在该向导的“步骤 3”页，请确保选中“现有工作表”，并且位置是相同的，然后单击“完成”。

这时，新数据透视表与另一报表是基于相同的数据区域，但是具有不同的数据缓存。

取消基于相同数据连接的两个或多个数据透视表的数据缓存共享请确保至少有两个数据透视表是基于相同的数据连接，并且这些报表共享相同的数据缓存。

确认数据透视表之间使用的是相同的数据连接。

在“数据”选项卡上的“连接”组中，单击“连接”。在“工作簿连接”对话框中，选择数据透视表连接。在“此工作簿中使用连接的位置”下，单击显示文本“单击此处以查看所选连接的使用位置”的链接。

将显示使用此数据连接的数据透视表。

确保您的计算机或网络上有此数据连接的连接文件。

在工作簿中为数据连接创建连接文件

在“数据”选项卡上的“连接”组中，单击“连接”。在“工作簿连接”对话框中，选择数据透视表连接。单击“属性”。在“连接属性”对话框中，单击“定义”选项卡，然后单击“导出连接文件”。在“文件保存”对话框中，将当前连接信息保存为一个 .odc 文件。单击“确定”，然后单击“关闭”。在要取消其数据缓存共享的数据透视表中，单击任意单元格。在“选项”选项卡上的“数据”组中，单击“更改数据源”，然后再单击“更改数据源”。

将显示“更改数据透视表数据源”对话框。

要使用其他数据连接，请选择“使用外部数据源”，然后单击“选择连接”。

将显示“现有连接”对话框。

在“选择连接”列表中，从“网络的连接文件”或“此计算机的连接文件”类别中，选择一个数据连接，然后单击“打开”。

注释：您可能需要单击“浏览”查找该连接文件。

单击“确定”。

这时，数据透视表具有一个不同的数据缓存。

下一节：《轻松办公系列进阶课堂-OfficeExcel数据透视表和数据透视图（二十五）假设分析（1）》

更多精彩内容将在以后的章节分享给朋友们，请添加好友并收藏，请点赞并欢迎关注后期更新！