轻松办公系列进阶课堂-OfficeExcel数据透视表和透视图（二十三）

EasyOffice系列高级课堂OfficeExcel数据透视表和数据透视图

（23）

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

(2)

优化数据透视表的内存

只能针对连接到开放数据库连接(ODBC)数据源的数据透视表优化内存。您必须使用数据透视表和数据透视图向导来完成以下步骤。

按AltDP启动数据透视表和数据透视图向导。

要将数据透视表和数据透视图向导添加到快速工具栏：

单击工具栏旁边的箭头，然后单击更多命令。

在“选择命令”下，选择“所有命令”。

从列表中选择“数据透视表和数据透视图向导”，单击“添加”，然后单击“确定”。

在向导的“步骤1”页面上，选择“外部数据源”，然后单击“下一步”。

在向导的“步骤2”页面上，单击“获取数据”。

连接到数据源。

有关连接到ODBC数据源的更多信息，请参见下文：

使用MicrosoftQuery检索外部数据

在向导的“步骤3”页面上，单击“设置”。

在“数据透视表和数据透视图向导选项”对话框中选中“优化内存”复选框。

使用MicrosoftQuery检索外部数据

可以使用MicrosoftQuery检索来自外部源的数据。使用MicrosoftQueryData搜索公司数据库和文件，以避免在Excel中重新输入要分析的数据。此外，只要原始源数据库更新了新数据，您就可以自动刷新Excel报告和摘要。

1了解有关MicrosoftQuery的更多信息

MicrosoftQuery允许您连接到外部数据源、从这些外部数据源中选择数据、将数据导入到电子表格中，并根据需要更新数据，以将电子表格中的数据与外部源中的数据同步。

可用的数据库类型

您可以从多种类型的数据库检索信息，包括MicrosoftOfficeAccess、MicrosoftSQLServer和MicrosoftSQLServerOLAPServices。您还可以在Excel工作簿和文本文件中搜索信息。

MicrosoftOffice提供了一组存储的“源数据”，可用于从以下数据源连接到数据库。数据源包含数据库服务器的名称和位置、数据库控制器的名称和信息。)驱动程序检索信息：

MicrosoftSQLServerAnalysisServices（OLAPProvider（OLAPProvider：提供对特定类型OLAP数据库的访问的一套软件。该软件包括数据源驱动程序以及连接数据库所需的其他客户端软件。））

微软办公访问

数据库

微软FoxPro

微软办公软件

甲骨文

悖论

文本文件数据库

还可以使用第三方ODBC驱动程序（OpenDatabaseConnectivity(ODBC)驱动程序：用于连接特定数据库的程序文件。各个数据库程序（例如Access或dBASE）或数据库管理系统（例如SQLServer）需要不同的驱动程序）或数据源驱动程序（数据源驱动程序：用于连接到特定数据库的程序文件。每个数据库程序或管理系统都需要不同的驱动程序。）从未在从数据源检索数据（包括其他OLAP数据库类型）。有关安装此处未列出的ODBC驱动程序或数据源驱动程序的信息，请参阅数据库文档或联系数据库供应商。

从数据库中选择数据

通过创建查询从数据库中检索信息，查询是您对存储在外部数据库中的信息提出的问题。例如，如果数据存储在Access数据库中，您可能想了解特定产品在不同地区的销售数据。您可以通过仅选择要分析的产品和区域的数据来搜索部分数据。

使用MicrosoftQuery，您可以选择所需的数据列并仅将这些数据导入Excel中。

使用操作更新工作表

如果您的Excel工作簿包含外部数据，是的，只要数据库发生变化，就可以更新（刷新：从外部数据源更新数据。每次数据更新后，你会看到数据库中数据的最新版本，包括对数据所做的更改。）更新您的分析，而无需重新创建摘要报告和图表。例如，您可以创建每月销售摘要，并在每月出现新销售数据时更新它。

MicrosoftQuery如何使用数据源

从特定数据库定义数据源后，只要您想要创建查询以从该数据库选择和检索数据，就可以使用该数据源，而无需重新键入所有联系信息。MicrosoftQuery使用此数据源连接到外部数据库并显示可用数据。创建查询并将数据返回到Excel后，MicrosoftQuery在Excel工作簿中提供查询和数据源信息，以便您在需要更新数据时可以重新连接到数据库。

使用MicrosoftQuery导入数据

要使用MicrosoftQuery将外部数据导入Excel，请按照以下基本步骤操作，每个步骤在以下部分中进行描述：

连接到数据源。

使用查询向导设置查询。

在Excel中处理数据。

2连接数据源

什么是数据源？

数据源是存储的数据集，允许Excel和MicrosoftQuery连接到外部数据库。使用MicrosoftQuery定义数据源时，您可以为数据源命名，然后输入数据库或服务器的名称和位置、数据库类型以及您的登录名和密码信息。此信息还包括OBDC驱动程序或数据源驱动程序的名称，它是连接到特定类型数据库的程序。

使用MicrosoftQuery指定数据源：

在“数据”选项卡上的“获取外部数据”组中，选择“从其他源”，然后选择“从MicrosoftQuery”。

执行以下操作之一：

要将数据源设置为数据库、文本文件或Excel工作簿，请单击“数据库”选项卡。

要定义OLAP多维数据集（多维数据集定义：多维数据集向导存储在oqy文件中的信息，定义如何根据从关系数据库检索的数据在内存中构建OLAP多维数据集。）数据源，请单击“OLAP多维数据集”选项卡。仅当使用Excel中的MicrosoftQuery时，此选项卡才可用。

双击或单击“确定”，然后单击“确定”。

将出现“创建新数据源”对话框。

在步骤1中，输入名称以标识数据源。

在步骤2中，单击要用作数据源的数据库类型的控制器。

如果随MicrosoftQuery安装的ODBC驱动程序不支持要使用的外部数据库，则必须获取并安装第三方供应商（例如数据库制造商）提供的与MicrosoftOffice兼容的ODBC驱动程序。向数据库供应商询问安装说明。

注意：OLAP数据库不需要ODBC驱动程序。安装MicrosoftQuery时，将为使用MicrosoftSQLServerAnalysisServices创建的数据库安装驱动程序。要连接其他OLAP数据库，必须安装数据源驱动和客户端软件。

单击“连接”并输入连接到数据源所需的信息。对于数据库、Excel工作簿和文本文件，您提供的信息取决于您选择的数据源。可能会要求您提供登录名、密码、您正在使用的数据库版本、数据库位置或与该类型数据库相关的其他信息。

使用由大小写字母、数字和符号组合组成的强密码。弱密码不会结合这些元素。例如，Y6dh!et5是强密码，House27是弱密码。密码长度必须大于或等于8个字符。最好使用包含至少14个字符的密码。有关详细信息，请参阅使用强密码保护个人信息。

记住您的密码很重要。如果您忘记了密码，Microsoft无法重置它。您应该记下密码并将其存放在安全的地方，并尽可能远离受密码保护的信息。

输入所需信息后，单击“确定”或“完成”返回“创建新数据源”对话框。

如果您的数据库包含表（表：关于特定主题的数据集合，存储为记录（行）和字段（列）。）并且您希望特定表自动出现在查询向导中，请单击“单击”框步。4然后单击所需的表。

如果您不想在使用数据源时输入登录信息用户名和密码，选中将我的用户名和密码保存在数据源定义中复选框。保存的密码未加密。如果此复选框不可用，请询问数据库管理员以确定此选项是否可用。

安全性：连接到数据源时避免保存登录信息。这些信息可以以纯文本形式存储，并且可以被恶意用户访问，从而损害数据源的安全。

完成这些步骤后，数据源的名称将显示在“选择数据源”对话框中。

3使用查询向导定义查询

对大多数查询使用查询向导

通过向导查询功能，可以轻松地从数据库中的不同表和字段中选择和收集数据。您可以使用查询向导来选择要包含的表和字段。当向导检测到一个表中的主字段和另一个表中的同名字段时，它会自动创建内部联接（指定两个表中的行根据相同字段的值联接的查询）。

您还可以使用向导对结果集进行排序并执行简单的过滤器。在向导的最后一步中，您可以将数据返回到Excel或在MicrosoftQuery中进一步细化查询。创建查询后，您可以在Excel或MicrosoftQuery中运行它。

通过执行以下操作启动调查向导：

在“数据”选项卡上的“获取外部数据”组中，选择“从其他源”，然后选择“从MicrosoftQuery”。

在“选择数据源”对话框中，确保选中“使用查询向导创建/编辑查询”复选框。

双击或单击要使用的数据源，然后单击“确定”。

直接在MicrosoftQuery中处理其他类型的查询

如果您还想创建查询向导不允许的复杂查询，则可以直接在MicrosoftQuery中执行此操作。使用MicrosoftQuery，您可以查看和更改在查询向导中启动的查询，也可以在不使用向导的情况下创建新查询。要创建执行以下操作的查询，请直接在MicrosoftQuery中执行：

从字段中选择特定信息

在大型数据库中，可能需要从字段中选择某些数据并省略不必要的数据。例如，如果您需要有关两种产品的信息，并且该信息位于包含有关多个产品的信息的字段中，则可以使用条件仅选择这两种产品的信息。

每次完成调查时根据不同的标准搜索信息

如果您想使用相同的外部数据为多个区域创建相同的Excel报表或汇总（例如为每个区域创建单独的销售报表），您可以创建参数查询（参数查询：查询类型，当当您运行参数查询时，系统会要求您提供用于在结果集中选择记录的值（条件），因此可以使用相同的查询来检索不同的结果集）。当您运行参数查询时，系统会提示您输入用作记录选择查询条件的值。例如，参数查询可能会要求您输入特定区域，您可以重复使用该查询来生成每个区域的销售报告。

以不同方式组合数据

由查询向导创建的内联接是创建查询时最常用的联接类型。然而，有时需要使用不同类型的连接。例如，如果您有一个产品销售数据表和一个客户数据表，则内部联接（由查询向导创建的类型）会阻止检索尚未购买的客户的客户记录。在MicrosoftQuery的帮助下，可以将这些表组合起来，这样除了搜索已购买的客户的销售信息外，还可以搜索所有客户记录。

按如下方式启动MicrosoftQuery：

在“数据”选项卡上的“获取外部数据”组中，选择“从其他源”，然后选择“从MicrosoftQuery”。

在“选择数据源”对话框中，确保清除“使用查询向导创建/编辑查询”复选框。

双击或单击要使用的数据源，然后单击“确定”。

重复使用和共享调查

在查询向导和MicrosoftQuery中，您可以将查询另存为可编辑、重用和共享的dqy文件。Excel可以直接打开dqy文件，因此您或其他用户可以使用同一查询创建其他外部数据范围。

从Excel打开保存的查询：

在“数据”选项卡上的“获取外部数据”组中，选择“从其他源”，然后选择“从MicrosoftQuery”。将出现“选择数据源”对话框。

在“选择数据源”对话框中，单击“查询”选项卡。

双击已保存的查询将其打开。这次民意调查是显示在MicrosoftQuery中。

如果要打开已保存的查询并且MicrosoftQuery已打开，请单击MicrosoftQuery中的“文件”菜单，然后单击“打开”。

如果双击dqy文件，Excel将打开，运行查询，并将结果插入到新的电子表格中。

如果要共享基于外部数据的Excel摘要或报告，您可以为其他用户提供包含外部数据区域的工作簿，也可以创建模板（模板：作为其他类似工作簿的基础而创建的工作簿）可以提供工作簿和电子表格创建模板。工作簿的默认模板称为Bookxlt和电子表格的默认模板为Sheetxlt。模板允许您在不保存外部数据的情况下保存摘要或报告。用户打开时会检索外部数据。报告模板。

4在Excel中处理数据

使用查询向导或MicrosoftQuery创建查询后，您可以将数据返回到Excel电子表格。之后，该数据就成为外部数据区（externaldataarea：从Excel外部（例如数据库或文本文件）导入到电子表格中的数据区域。在Excel中，可以格式化或使用外部数据中的数据。执行计算如下可以与任何其他数据一起格式化和更新。

格式化检索到的信息

在Excel中，您可以使用各种工具（例如图表或自动小计）来呈现和汇总MicrosoftQuery检索到的数据。您可以格式化数据，以便在更新外部数据时保留格式。您可以使用自己的列标签代替字段名称，行号将自动添加。

Excel可以自动设置您在范围末尾键入的新数据的格式，以匹配前面的行。Excel还可以自动复制前一行中重复的公式并将其扩展到其他行。

注意：要将范围扩展到新行，格式和公式必须位于前五行中的至少三行上。

您可以随时启用此选项（或再次禁用它）：

单击“Office按钮”的图像。

，单击“Excel选项”，然后单击“高级”类别。

在“编辑”部分中，选中“高级数据区域格式和公式”复选框以禁用此选项。要禁用数据区域设置的自动重新格式化，请取消选中此框。

更新外部数据

更新外部数据时，运行查询以检索与您指定的条件匹配的任何新数据或更改的数据。您可以更新MicrosoftQuery和Excel中的查询。Excel提供了许多用于刷新查询的选项，包括每次打开工作簿时刷新数据以及定期自动刷新数据。您可以在更新数据的同时继续在Excel中工作，还可以在更新数据的同时检查状态。

单元格区域、Excel表、数据透视表、数据透视图、文本文件和Web查询都可以连接到外部数据源（数据源：用于连接到数据库的一组存储的“源”数据。数据源包括名称以及数据库服务器的位置、数据库控制器的名称以及登录所需的数据库信息）。您可以刷新数据以更新此外部数据源中的数据。每次更新数据时，您都会看到数据源中数据的最新版本，包括对数据所做的任何更改。

但是，出于数据安全原因，您的计算机可能已删除与外部数据的连接。如果要在打开工作簿时更新数据，则必须使用信任中心栏启用数据连接或将工作簿放置在受信任的位置。

下一节：《EasyOffice系列高级课堂-OfficeExcel数据透视表和数据透视图（24）使用数据透视表和数据透视图向导（3）》

更多精彩内容将在以后的章节中与朋友们分享。添加好友和收藏夹。喜欢并持续关注。