Excel Power Pivot 简明教程

Excel Power Pivot - Loading Data

在本章中，我们将学习如何将数据加载到 Power Pivot。

你可以使用两种方式将数据加载到 Power Pivot 中——

  1. 将数据加载到 Excel 中，并将其添加到数据模型

  2. 直接将数据加载到 PowerPivot 中，填充数据模型（即 PowerPivot 数据库）

如果你需要 Power Pivot 数据，无需通过 Excel 便可以使用第二种方法。这是因为，你只需要将数据加载一次，以高度压缩的格式。为了理解差异的程度，假设你通过先将数据添加到数据模型中并将数据加载到 Excel 中，文件大小可能是 10 MB。

如果你直接将数据加载到 PowerPivot 中，便可以跳过 Excel 的额外步骤，进入数据模型，此时你的文件大小可能只有不到 1 MB。

Data Sources Supported by Power Pivot

你可以将数据从各种数据源导入到 Power Pivot 数据模型，也可以建立连接和/或使用现有连接。Power Pivot 支持以下数据源——

  1. SQL Server relational database

  2. Microsoft Access database

  3. SQL Server Analysis Services

  4. SQL Server Reporting Services（SQL 2008 R2）

  5. ATOM data feeds

  6. Text files

  7. Microsoft SQL Azure

  8. Oracle

  9. Teradata

  10. Sybase

  11. Informix

  12. IBM DB2

  13. 对象链接和嵌入数据库/开放数据库连接

  14. (OLEDB/ODBC) sources

  15. Microsoft Excel File

  16. Text File

Loading Data Directly into PowerPivot

要直接将数据加载到 Power Pivot，请执行以下操作——

  1. Open a new workbook.

  2. 单击功能区上的 POWERPIVOT 选项卡。

  3. 点击数据模型组中的管理。

load data

将会打开 PowerPivot 窗口。现在你有两个窗口——Excel 工作簿窗口和连接到你的工作簿的 PowerPivot for Excel 窗口。

  1. 单击 PowerPivot 窗口中的 Home 选项卡。

  2. 单击获取外部数据组中的 From Database 。

  3. Select From Access.

from access

表导入向导出现。

  1. 浏览到 Access 数据库文件。

  2. Provide Friendly connection name.

  3. 如果数据库受密码保护，也填写这些详细信息。

wizard appears

单击 Next → 按钮。表导入向导显示用于选择如何导入数据的选项。

next

单击从表和视图的列表中选择以选择要导入的数据。

data to import

单击 Next → 按钮。表导入向导显示您选择的 Access 数据库中的表和视图。

选中 Medals 复选框。

box medals

您可能会注意到，您可以通过选中复选框来选择表、在添加到数据透视表之前预览和筛选表，或者选择相关表。

单击 Preview & Filter 按钮。

preview

您会注意到，您可以通过选中列标签中的复选框来选择特定的列、通过单击列标签中的下拉箭头来选择包含的值来筛选列。

  1. Click OK.

  2. 单击 Select Related Tables 按钮。

  3. 如果存在关系，Power Pivot 就会检查哪些其他表与选定的 Medals 表相关。

select tables

您会发现，Power Pivot 发现 Disciplines 表与 Medals 表相关并将其选中。单击完成。

表导入向导显示 − Importing 并显示导入的状态。这将需要几分钟的时间，您可通过单击 Stop Import 按钮来停止导入。

table import

导入数据后，表导入向导显示 – Success 并显示导入的结果，如下面的截图所示。单击关闭。

success

Power Pivot 在两个选项卡中显示两个导入的表。

imported table

您可以使用选项卡下方的 Record 箭头滚动记录（表格的行）。

Table Import Wizard

在上一个部分中，您已经了解了如何通过表导入向导从 Access 导入数据。

请注意，根据所选用于连接的数据源，表导入向导选项会发生更改。您可能想知道可以选择哪些数据源。

单击 Power Pivot 窗口中的 From Other Sources 。

from other sources

“表导入向导 − Connect to a Data Source ”出现。你可以创建一个到数据源的连接或使用已存在的连接。

connect to datasource

可以在导入表向导中滚动浏览连接列表以了解与 Power Pivot 兼容的数据连接。

  1. 向下滚动到“文本文件”。

  2. Select Excel File.

excel file

  1. 单击 Next → 按钮。表导入向导显示“连接至 Microsoft Excel 文件”。

  2. 浏览到 Excel 文件路径框中的 Excel 文件。

  3. 选中 Use first row as column headers 框。

excel file path box

  1. 单击 Next → 按钮。表导入向导显示 Select Tables and Views 。

  2. 选中 Product Catalog$ 框。单击 Finish 按钮。

product catalog

你将看到以下 Success 消息。单击“关闭”。

success message

你已导入一张表，并且还创建了连接至包含其他表的 Excel 文件的连接。

Opening Existing Connections

建立与数据源的连接后，你可以在稍后打开它。

单击 Power Pivot 窗口中的“现有连接”。

existing connections

“现有连接”对话框出现。从列表中选择“Excel 销售数据”。

excel sales data

单击“打开”按钮。表导入向导出现，显示表和视图。

选择要导入的表，然后单击 Finish 。

click finish

选定的五张表将被导入。单击 Close 。

click close

你可以看到这五张表已添加到 Power Pivot 中，每张都在一个新选项卡中。

new tab

Creating Linked Tables

链接表是 Excel 中表与数据模型中表之间的实时链接。对 Excel 中表的更新将自动更新模型中数据表中的数据。

You can link the Excel table into Power Pivot in a few steps as follows −

  1. Create an Excel table with the data.

  2. 单击功能区上的 POWERPIVOT 选项卡。

  3. Click Add to Data Model in the Tables group.

add data model

The Excel table is linked to the corresponding Data Table in PowerPivot.

excel tables

You can see that the Table Tools with the tab - Linked Table is added to the Power Pivot window. If you click Go to Excel Table, you will switch to the Excel worksheet. If you click Manage, you will switch back to the linked table in the Power Pivot window.

You can update the linked table either automatically or manually.

Note that you can link an Excel table only if it is present in the workbook with the Power Pivot. If you have Excel tables in a separate workbook, then you have to load them as explained in the next section.

Loading from Excel Files

If you want to load the data from Excel workbooks, keep the following in mind −

  1. Power Pivot considers the other Excel workbook as a database and only worksheets are imported.

  2. Power Pivot loads each worksheet as a table.

  3. Power Pivot cannot recognize single tables. Hence, Power Pivot cannot recognize if there are multiple tables on a worksheet.

  4. Power Pivot cannot recognize any additional information other than the table on a worksheet.

Hence, keep each table in a separate worksheet.

Once your data in the workbook is ready, you can import the data as follows −

  1. Click From Other Sources in the Get External Data group in the Power Pivot window.

  2. Proceed as given in the section − Table Import Wizard.

以下是链接的 Excel 表格和导入的 Excel 表格之间的差异 −

  1. 链接的表格必须存储在与 Power Pivot 数据库相同的 Excel 工作簿中。如果数据已经存在于其他 Excel 工作簿中，则使用此功能没有任何意义。

  2. Excel 导入功能允许您从不同的 Excel 工作簿中加载数据。

  3. 从 Excel 工作簿加载数据并不会在两个文件之间创建链接。导入时，Power Pivot 只会创建数据副本。

  4. 当最初的 Excel 文件更新时，Power Pivot 中的数据不会刷新。您需要在 Power Pivot 窗口的“链接表格”选项卡中，将更新模式设置为“自动”或者手动更新数据。

Loading from Text Files

一种流行的数据表示形式是逗号分隔值（csv）格式。每行数据/记录都由文本行表示，其中列/字段由逗号分隔。许多数据库都提供保存为 csv 格式文件的选择。

如果您想将 csv 文件加载到 Power Pivot 中，则必须使用“文本文件”选项。假设您有以下带 csv 格式的文本文件：

result

  1. Click the PowerPivot tab.

  2. 单击 PowerPivot 窗口中的“主页”选项卡。

  3. 单击“获取外部数据”组中的@ {s0}。表导入向导就会显示。

  4. 向下滚动到“文本文件”。

text file

  1. Click Text File.

  2. 单击 Next → 按钮。表导入向导随即显示，屏幕上会显示“连接到平面文件”。

  3. 在“文件路径”框中，找到该文本文件。csv 文件通常的首行代表列标题。

  4. 如果首行有标题，则勾选“将首行用作列标题”框。

  5. 在“列分隔符”框中，默认是逗号 (,)，但是如果您的文本文件有其他操作符，例如制表符、分号、空格、冒号或竖线，可以选择该操作符。

path box

您可以观察到，这里是对数据表的预览。单击“完成”。

Power Pivot 在数据模型中创建数据表。

preview data table

Loading from the Clipboard

假设您有应用程序中的数据，而 Power Pivot 无法将其识别为数据源。要将这些数据加载到 Power Pivot 中，您有两个选择：

  1. 将数据复制到 Excel 文件中，并使用 Excel 文件作为 Power Pivot 的数据源。

  2. 复制数据，以便在剪贴板上，然后将其粘贴到 Power Pivot 中。

您已经在较早的部分学习了第一个选项。而且这比第二个选项更好，您将在本部分的末尾找到它。但是，您应该知道如何将数据从剪贴板复制到 Power Pivot 中。

假设您在 word 文档中按以下方式拥有数据：

clipboard

Word 不是 Power Pivot 的数据源。所以，执行以下操作：

  1. 在 Word 文档中选择表格。

  2. 将其复制并粘贴到 PowerPivot 窗口中。

word document

Paste Preview 对话框将出现。

  1. 将名称指定为 Word-Employee table 。

  2. 选中 Use first row as column headers 框并单击确定。

word employee table

复制到剪贴板的数据将粘贴到 Power Pivot 中的新数据表中，标签为 − Word-Employee 表。

tab

假设您想要用新内容替换此表。

  1. 从 Word 中复制表格。

  2. Click Paste Replace.

paste replace

“粘贴预览”对话框将出现。验证您用来替换的内容。

paste preview

单击确定。

click ok

正如您所观察到的，Power Pivot 中数据表的内容将被剪贴板中的内容替换。

假设您想要向数据表添加两行新数据。在 Word 文档的表格中，您有两个新行。

new rows

  1. 选择这两个新行。

  2. Click Copy.

  3. 单击 Power Pivot 窗口中的 Paste Append 。“粘贴预览”对话框将出现。

  4. 验证您用来追加的内容。

paste append

单击确定继续。

proceed

正如您所观察到的，Power Pivot 中数据表的内容将追加剪贴板中的内容。

在本节的开头，我们已经说过将数据复制到 excel 文件并使用链接表比从剪贴板中复制更好。

这是由于以下原因：

  1. · 如果您使用链接表，您将知道数据的来源。另一方面，您不会知道数据的来源时间或是否被其他人使用。

  2. 在 Word 文件中，您有跟踪信息，例如，数据替换的时间以及数据追加的时间。但是，无法将该信息复制到 Power Pivot。如果首先将数据复制到 Excel 文件，则可以保留该信息以供以后使用。

  3. 从剪贴板复制时，如果您想添加一些批注，则无法这样做。如果您首先复制到 Excel 文件，可以在 Excel 表中插入将链接到 Power Pivot 的批注。

  4. 无法刷新从剪贴板复制的数据。如果数据来自链接表，您始终可以确保数据已更新。

Refreshing Data in Power Pivot

您可以随时刷新从外部数据源导入的数据。

如果您只想刷新 Power Pivot 中的一个数据表，请执行以下操作：

  1. 单击数据表的选项卡。

  2. Click Refresh.

  3. 从下拉列表中选择“刷新”。

refresh

如果您想刷新 Power Pivot 中的所有数据表，请执行以下操作：

  1. Click the Refresh button.

  2. 从下拉列表中选择“全部刷新”。