Excel Power Pivot 简明教程

Excel Power Pivot - Loading Data

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

In this chapter, we will learn to load data into Power Pivot.

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

You can load data into Power Pivot in two ways −

  1. Load data into Excel and add it to the Data Model

  2. Load data into PowerPivot directly, populating the Data Model, which is the PowerPivot database.

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

If you want the data for Power Pivot, do it the second way, without Excel even knowing about it. This is because you will be loading the data only once, in highly compressed format. To understand the magnitude of difference, suppose you load data into Excel by first adding it to the Data Model, the file size is say 10 MB.

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

If you load data into PowerPivot, and hence into Data Model skipping the extra step of Excel, your file size could be as less as 1 MB only.

Data Sources Supported by Power Pivot

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

You can either import data into the Power Pivot Data Model from various data sources or establish connections and/or use the existing connections. Power Pivot supports the following data sources −

  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. Object Linking and Embedding Database/Open Database Connectivity

  14. (OLEDB/ODBC) sources

  15. Microsoft Excel File

  16. Text File

Loading Data Directly into PowerPivot

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

To load data directly into Power Pivot, perform the following −

  1. Open a new workbook.

  2. Click on the POWERPIVOT tab on the ribbon.

  3. Click on Manage in the Data Model group.

load data

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

The PowerPivot window opens. Now you have two windows − the Excel workbook window and the PowerPivot for Excel window that is connected to your workbook.

  1. Click the Home tab in the PowerPivot window.

  2. Click From Database in the Get External Data group.

  3. Select From Access.

from access

表导入向导出现。

The Table Import Wizard appears.

  1. Browse to the Access database file.

  2. Provide Friendly connection name.

  3. If the database is password protected, fill in those details also.

wizard appears

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

Click the Next → button. The Table Import Wizard displays the options for choosing how to import data.

next

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

Click Select from a list of tables and views to choose the data to import.

data to import

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

Click the Next → button. The Table Import Wizard displays the tables and views in the Access database that you have selected.

选中 Medals 复选框。

Check the box Medals.

box medals

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

As you can observe, you can select the tables by checking the boxes, preview and filter the tables before adding to Pivot Table and/or select the related tables.

单击 Preview & Filter 按钮。

Click the Preview & Filter button.

preview

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

As you can see, you can select specific columns by checking the boxes in the column labels, filter the columns by clicking the dropdown arrow in the column label to select the values to be included.

  1. Click OK.

  2. Click the Select Related Tables button.

  3. Power Pivot checks what other tables are related to the selected Medals table, if a relation exists.

select tables

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

You can see that Power Pivot found that the table Disciplines are related to the table Medals and selected it. Click Finish.

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

Table Import Wizard displays − Importing and shows the status of the import. This will take a few minutes and you can stop the import by clicking the Stop Import button.

table import

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

Once the data is imported, the Table Import Wizard displays – Success and shows the results of the import as shown in the screenshot below. Click Close.

success

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

Power Pivot displays the two imported tables in two tabs.

imported table

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

You can scroll through the records (rows of the table) using the Record arrows below the tabs.

Table Import Wizard

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

In the previous section, you have learnt how to import data from Access through the Table Import Wizard.

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

Note that the Table Import Wizard options change as per the data source that is selected to connect to. You might want to know what data sources you can choose from.

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

Click From Other Sources in the Power Pivot window.

from other sources

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

The Table Import Wizard – Connect to a Data Source appears. You can either create a connection to a data source or you can use one that already exists.

connect to datasource

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

You can scroll through the list of connections in the Import Table Wizard to know the compatible data connections to Power Pivot.

  1. Scroll down to the Text Files.

  2. Select Excel File.

excel file

  1. Click the Next → button. The Table Import Wizard displays – Connect to a Microsoft Excel File.

  2. Browse to the Excel file in the Excel File Path box.

  3. Check the box – Use first row as column headers.

excel file path box

  1. Click the Next → button. The Table Import Wizard displays − Select Tables and Views.

  2. Check the box Product Catalog$. Click the Finish button.

product catalog

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

You will see the following Success message. Click Close.

success message

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

You have imported one table, and you have also, created a connection to the Excel file that contains several other tables.

Opening Existing Connections

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

Once you have established a connection to a data source, you can open it later.

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

Click Existing Connections in the PowerPivot window.

existing connections

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

The Existing Connections dialog box appears. Select Excel Sales Data from the list.

excel sales data

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

Click the Open button. The Table Import Wizard appears displaying the tables and views.

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

Select the tables that you want to import and click Finish.

click finish

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

The selected five tables will be imported. Click Close.

click close

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

You can see that the five tables are added to the Power Pivot, each in a new tab.

new tab

Creating Linked Tables

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

Linked tables are a live link between the table in Excel and the table in the Data Model. Updates to the table in Excel automatically update the data in the data table in the model.

你可以按照以下几个步骤将 Excel 表格链接到 Power Pivot 中−

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

  1. Create an Excel table with the data.

  2. Click the POWERPIVOT tab on the Ribbon.

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

add data model

Excel 表格链接到 PowerPivot 中的对应数据表。

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

excel tables

你可以看到具有链接表格选项卡的表格工具已添加到 Power Pivot 窗口。如果你单击 Go to Excel Table ，你将切换到 Excel 工作表。如果你单击 Manage ，你将切换回 Power Pivot 窗口中的链接表格。

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.

请注意，你只能在你与 Power Pivot 在工作簿中存在时链接 Excel 表格。如果你在一个单独的工作簿中有 Excel 表格，那么你必须按下一节中的解释加载它们。

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

如果你想要从 Excel 工作簿加载数据，请记住以下内容−

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 表格之间的差异 −

The following are the differences between linked Excel tables and imported Excel tables −

  1. Linked tables need to be in the same Excel workbook in which the Power Pivot database is stored. If the data already exists in other Excel workbooks, there is no point in using this feature.

  2. The Excel import feature allows you to load data from different Excel workbooks.

  3. Loading data from an Excel workbook does not create a link between the two files. Power Pivot creates only a copy of the data, while importing.

  4. When the original Excel file is updated, data in the Power Pivot will not be refreshed. You need to either set the update mode to automatic or update the data manually, in the Linked Table tab of the Power Pivot window.

Loading from Text Files

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

One of the popular data representation styles is with the format known as comma separated values (csv). Each data row /record is represented by a text line, wherein the columns /fields are separated by commas. Many databases provide the option of saving to a csv format file.

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

If you want to load a csv file into Power Pivot, you have to use the Text File option. Suppose you have the following text file with csv format −

result

  1. Click the PowerPivot tab.

  2. Click the Home tab in the PowerPivot window.

  3. Click From Other Sources in the Get External Data group. The Table Import Wizard appears.

  4. Scroll down to Text Files.

text file

  1. Click Text File.

  2. Click the Next → button. Table Import Wizard appears with the display − Connect to Flat File.

  3. Browse to the text file in the File Path box. The csv files usually have the first line representing column headers.

  4. Check the box Use first row as column headers, if the first line has headers.

  5. In the Column Separator box, default is Comma (,), but in case your text file has any other operator such as Tab, Semicolon, Space, Colon or Vertical Bar, then choose that operator.

path box

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

As you can observe, there is a preview of your data table. Click Finish.

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

Power Pivot creates the data table in the Data Model.

preview data table

Loading from the Clipboard

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

Suppose, you have data in an application that is not recognized by Power Pivot as a data source. To load this data into Power Pivot, you have two options −

  1. Copy the data to an Excel file and use the Excel file as data source for Power Pivot.

  2. Copy the data, so that it will be on the clipboard, and paste it into Power Pivot.

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

You have already learnt the first option in an earlier section. And this is preferable to the second option, as you will find at the end of this section. However, you should know how to copy data from clipboard into Power Pivot.

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

Suppose you have data in a word document as follows −

clipboard

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

Word is not a data source for Power Pivot. Therefore, perform the following −

  1. Select the table in the Word document.

  2. Copy and Paste it in the PowerPivot window.

word document

Paste Preview 对话框将出现。

The Paste Preview dialog box appears.

  1. Give the name as Word-Employee table.

  2. Check the box Use first row as column headers and click OK.

word employee table

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

The data copied into the clipboard will be pasted into a new data table in Power Pivot, with the tab − Word-Employee table.

tab

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

Suppose, you want to replace this table with new content.

  1. Copy the table from Word.

  2. Click Paste Replace.

paste replace

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

The Paste Preview dialog box appears. Verify the contents that you are using for replace.

paste preview

单击确定。

Click OK.

click ok

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

As you can observe, the contents of the data table in Power Pivot are replaced by the contents in the clipboard.

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

Suppose you want to add two new rows of data to a data table. In the table in the Word document, you have the two news rows.

new rows

  1. Select the two new rows.

  2. Click Copy.

  3. Click Paste Append in the Power Pivot window. The Paste Preview dialog box appears.

  4. Verify the contents that you are using to append.

paste append

单击确定继续。

Click OK to proceed.

proceed

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

As you can observe, the contents of the data table in Power Pivot are appended with the contents in the clipboard.

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

In the beginning of this section, we have said that copying data to an excel file and using linked table is better than copying from clipboard.

这是由于以下原因：

This is because of the following reasons −

  1. If you use linked table, you know the source of the data. On the other hand, you will not know the source of the data later or if it is used by a different person.

  2. You have tracking information in the Word file, such as when the data is replaced and when the data is appended. However, there is no way of copying that information to Power Pivot. If you copy the data first to an excel file, you can preserve that information for later use.

  3. While copying from clipboard, if you want to add some comments, you cannot do so. If you copy to Excel file first, you can insert comments in your Excel table that will be linked to the Power Pivot.

  4. There is no way to refresh the data copied from clipboard. If the data is from a linked table, you can always ensure that the data is updated.

Refreshing Data in Power Pivot

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

You can refresh the data imported from the external data sources at any point of time.

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

If you want to refresh only one data table in the Power Pivot, do the following −

  1. Click the tab of the data table.

  2. Click Refresh.

  3. Select Refresh from the dropdown list.

refresh

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

If you want to refresh all the data tables in the Power Pivot, do the following −

  1. Click the Refresh button.

  2. Select Refresh All from the dropdown list.