Excel Power Pivot 简明教程

Excel Power Pivot - Managing Data Model

Power Pivot 的主要用途是管理数据表及其之间的关系，以便于分析来自多张表的数据。在创建数据透视表或直接从 PowerPivot 功能区时，可以将 Excel 表添加到数据模型中。

The major use of Power Pivot is its ability to manage the data tables and the relationships among them, to facilitate analysis of the data from several tables. You can add an excel table to the Data Model while you are creating a PivotTable or directly from the PowerPivot Ribbon.

仅当它们之间存在关系时，您才能分析来自多个表的数据。使用 Power Pivot，您可以从“数据视图”或“图表视图”创建关系。此外，如果您已选择将表添加到 Power Pivot，您还需要添加关系。

You can analyze data from across multiple tables only when relationships exist among them. With Power Pivot, you can create relationships from the Data View or Diagram View. Moreover, if you had chosen to add a table to the Power Pivot, you need to add a relationship as well.

Adding Excel Tables to Data Model with PivotTable

当您在 Excel 中创建数据透视表时，它只基于单个表/范围。如果您想向数据透视表添加更多表，可以使用数据模型来实现。

When you create a PivotTable in Excel, it is based only on a single table / range. In case you want to add more tables to the PivotTable, you can do so with the Data Model.

假设您的工作簿中有两个工作表：

Suppose you have two worksheets in your workbook −

  1. One containing the data of salespersons and the regions they represent, in a table- Salesperson.

  2. Another containing the data of sales, region and month wise, in a table – Sales.

adding excel tables

您可以按业务员总结销售，如下所示。

You can summarize the sales – salesperson-wise as given below.

  1. Click the table – Sales.

  2. Click the INSERT tab on the Ribbon.

  3. Select PivotTable in the Tables group.

将创建一个包含销售表字段（地区、月份和订单金额）的空数据透视表。正如你所观测到的，在数据透视表字段列表下面有一个 MORE TABLES 命令。

An empty PivotTable with the fields from the Sales table – Region, Month and Order Amount will be created. As you can observe, there is a MORE TABLES command below the PivotTable Fields list.

  1. Click on MORE TABLES.

出现 Create a New PivotTable 消息框。显示的消息是 - 为在分析中使用多张报表，需要使用数据模型创建一个新的数据透视表。单击 是

The Create a New PivotTable message box appears. The message displayed is- To use multiple tables in your analysis, a new PivotTable needs to be created using the Data Model. Click Yes

create new pivot

将创建一个如下所示的新数据透视表 -

A New PivotTable will be created as shown below −

new pivottable

在数据透视表字段下方，你可以看到有两个选项卡 - ACTIVE 和 ALL 。

Under PivotTable Fields, you can observe that there are two tabs – ACTIVE and ALL.

  1. Click the ALL tab.

  2. Two tables- Sales and Salesperson, with the corresponding fields appear in the PivotTable Fields list.

  3. Click the field Salesperson in the Salesperson table and drag it to ROWS area.

  4. Click the field Month in the Sales table and drag it to ROWS area.

  5. Click the field Order Amount in the Sales table and drag it to ∑ VALUES area.

pivottable fields

数据透视表已创建。在数据透视表字段中出现一条消息 - Relationships between tables may be needed 。

The PivotTable is created. A message appears in the PivotTable Fields – Relationships between tables may be needed.

单击消息旁边的 创建 按钮。出现 Create Relationship 对话框。

Click the CREATE button next to the message. The Create Relationship dialog box appears.

create relationship

  1. Under Table, select Sales.

  2. Under Column (Foreign) box, select Region.

  3. Under Related Table, select Salesperson.

  4. Under Related Column (Primary) box, select Region.

  5. Click OK.

dialog box

你从两张工作表中的两张表的 PivotTable 已准备就绪。

Your PivotTable from the two tables on two worksheets is ready.

two tables

此外，正如 Excel 在将第二张表添加到数据透视表时所述，数据透视表已使用数据模型创建。要进行验证，请执行以下操作：

Further, as Excel stated while adding the second table to the PivotTable, the PivotTable got created with Data Model. To verify, do the following −

  1. Click the POWERPIVOT tab on the Ribbon.

  2. Click Manage in the Data Model group. The Data View of the Power Pivot appears.

create manage

你可以看到，你在创建数据透视表时所使用的两张 Excel 表已转换为数据模型中的数据表。

You can observe that the two Excel tables that you used in creating the PivotTable are converted to data tables in the Data Model.

Adding Excel Tables from a Different Workbook to Data Model

假设两张表 - 销售人员和销售位于两个不同的工作簿中。

Suppose the two tables – Salesperson and Sales are in two different workbooks.

salesperson

你可以将其他工作簿中的 Excel 表格添加到数据模型中 ，如下所示 −

You can add the Excel table from a different workbook to the Data Model as follows −

  1. Click the Sales table.

  2. Click the INSERT tab.

  3. Click PivotTable in the Tables group. The Create PivotTable dialog box appears.

insert table

  1. In the Table/Range box, type Sales.

  2. Click on New Worksheet.

  3. Check the box Add this data to the Data Model.

  4. Click OK.

你将获得一个新工作表上的空枢纽分析表，其中仅包含与“销售额”表相对应的字段。

You will get an empty PivotTable on a new worksheet with only the fields corresponding to the Sales table.

你已将销售额表数据添加到数据模型中。接下来，你还必须按照以下步骤将销售人员表数据获取到数据模型中 −

You have added the Sales table data to the Data Model. Next, you have to get the Salesperson table data also into Data Model as follows −

  1. Click on the worksheet containing Sales table.

  2. Click the DATA tab on the Ribbon.

  3. Click Existing Connections in the Get External Data group. The Existing Connections dialog box appears.

  4. Click on the Tables tab.

在 This Workbook Data Model, 1 table 下显示（这是先前添加的销售额表）。你还将找到两个在其中显示表格的工作簿。

Under This Workbook Data Model, 1 table is displayed (This is the Sales table that you added earlier). You also find the two workbooks displaying the tables in them.

  1. Click Salesperson under Salesperson.xlsx.

  2. Click Open. The Import Data dialog box appears.

  3. Click on PivotTable Report.

  4. Click on New worksheet.

import data

你可以看到 — Add this data to the Data Model 框已选中且处于非活动状态。单击“确定”。

You can see that the box – Add this data to the Data Model is checked and inactive. Click OK.

new worksheet

将创建枢纽分析表。

The PivotTable will be created.

pivottable created

正如你所观察到的，两个表格都在数据模型中。你可能需要在两个表之间创建关系，如前一部分所示。

As you can observe the two tables are in the Data Model. You might have to create a relationship between the two tables as in the previous section.

Adding Excel Tables to Data Model from the PowerPivot Ribbon

将 Excel 表格添加到数据模型的另一种方法是执行 so from the PowerPivot Ribbon 。

Another way of adding Excel tables to Data Model is doing so from the PowerPivot Ribbon.

假设您的工作簿中有两个工作表：

Suppose you have two worksheets in your workbook −

  1. One containing the data of salespersons and the regions they represent, in a table – Salesperson.

  2. Another containing the data of sales, region and month wise, in a table – Sales.

sales

可以在进行任何分析之前，首先将这些 Excel 表格添加到数据模型中。

You can add these Excel tables to the Data Model first, before doing any analysis.

  1. Click on the Excel table - Sales.

  2. Click the POWERPIVOT tab on the Ribbon.

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

powerpivots

Power Pivot 窗口会出现，其中添加了数据表“销售人员”。在 Power Pivot 窗口的 Ribbon 中会出现一个选项卡 — 链接的表格。

Power Pivot window appears, with the data table Salesperson added to it. Further a tab – Linked Table appears on the Ribbon in the Power Pivot window.

  1. Click on the Linked Table tab on the Ribbon.

  2. Click on Excel Table: Salesperson.

linked table

你会发现工作簿中显示了两个表的名字，其中“销售人员”已经被选中。这意味着数据表“销售人员”已经链接到 Excel 表“销售人员”。

You can find that the names of the two tables present in your workbook are displayed and the name Salesperson is ticked. This means the data table Salesperson is linked to the Excel table Salesperson.

单击 Go to Excel Table 。

Click Go to Excel Table.

go to excel table

出现包含“销售人员”表格的工作表的 Excel 窗口。

Excel window with worksheet containing Salesperson table appears.

  1. Click the Sales worksheet tab.

  2. Click the Sales table.

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

sales table

Excel 表格“销售”也会被添加到数据模型中。

The Excel table Sales is also added to the Data Model.

excel table sales

如果你希望基于这两个表格进行分析，正如你所了解的那样，你需要创建两个数据表格之间的关系。在 Power Pivot 中，可以通过两种方式进行操作 −

If you want to do analysis based on these two tables, as you are aware, you need to create a relationship between the two data tables. In Power Pivot, you can do this in two ways −

  1. From Data View

  2. From Diagram View

Creating Relationships from Data View

正如你所了解的那样，在数据视图中，你可以查看作为行和记录、作为列的字段中的数据表。

As you know that in Data View, you can view the data tables with records as rows and fields as columns.

  1. Click on the Design tab in the Power Pivot window.

  2. Click on Create Relationship in the Relationships group. The Create Relationship dialog box appears.

creating relationships

  1. Click on Sales in the Table box. This is the table from where the relationship starts. As you are aware, Column should be the field that is present in the related table Salesperson that contains unique values.

  2. Click on Region in the Column box.

  3. Click on Salesperson in the Related Linked Table box.

相关链接列会自动填入“区域”。

The Related Linked Column gets automatically populated with Region.

linked column

单击“创建”按钮。关系就会创建。

Click the Create button. The relationship is created.

Creating Relationships from Diagram View

从图表视图中创建关系会相对更容易。按照给定的步骤进行操作。

Creating Relationships from Diagram View is relatively easier. Follow the given steps.

  1. Click the Home tab in the Power Pivot window.

  2. Click Diagram View in the View group.

realtionships from diagram view

数据模型的图表视图出现在 Power Pivot 窗口中。

The Diagram View of the Data Model appears in the Power Pivot window.

power pivot window

  1. Click on Region in Sales table. Region in Sales table is highlighted.

  2. Drag to Region in Salesperson table. Region in Salesperson table is also highlighted. A line appears in the direction you dragged.

  3. A line appears from the table Sales to the table Salesperson indicating the relationship.

salesperson relationship

正如您所见，一条线从销售表显示到销售人员表，表示关系和方向。

As you can see, a line appears from the Sales table to the Salesperson table, indicating the relationship and the direction.

direction

如果您想知道是关系的一部分哪个字段，请单击关系线。关系线和两个表中的字段都会高亮显示。

If you want to know the field that is a part of a relationship, click on the relationship line. The line and the field in both the tables are highlighted.

relationship line

Managing Relationships

您可以编辑或删除数据模型中的现有关系。

You can edit or delete an existing relationship in Data Model.

  1. Click the Design tab in the Power Pivot window.

  2. Click Manage Relationships in the Relationships group. The Manage Relationships dialog box appears.

manage relationships

将显示数据模型中存在的所有关系。

All the relationships that exist in the Data Model are displayed.

To edit a relationship

  1. Click on a Relationship.

  2. Click the Edit button. The Edit Relationship dialog box appears.

insert

  1. Make the required changes in the relationship.

  2. Click OK. The changes get reflected in the relationship.

To delete a relationship

  1. Click on a Relationship.

  2. Click on the Delete button. A warning message appears showing how the tables that are affected by deleting the relationship would affect the reports.

  3. Click OK if you are sure you want to delete. The selected relationship is deleted.

Refreshing Power Pivot Data

假设您修改了 Excel 表中的数据。您可以在 Excel 表中添加/更改/删除数据。

Suppose you modify the data in the Excel table. You can add / change / delete the data in the Excel table.

若要刷新 PowerPivot 数据，请执行以下操作：

To refresh the PowerPivot data, do the following −

  1. Click the Linked Table tab in the Power Pivot window.

  2. Click Update All.

数据表将更新为 Excel 表中所做的修改。

The data table is updated with the modifications made in the Excel table.

正如您所观察到的，您不能直接修改数据表中的数据。因此，当您将数据添加到数据模型时，最好将数据保存在链接到数据表中的 Excel 表中。这样可以在您更新 Excel 表中的数据时更新数据表中的数据。

As you can observe, you cannot modify data in the data tables directly. Hence, it is better to maintain your data in Excel tables that are linked to the data tables when you add them to the Data Model. This facilitates updating the data in data tables as and when you update the data in Excel tables.