Excel Power Pivot 简明教程

Excel Power Pivot - Exploring Data

在上一章中，您学习了如何从一组普通数据表中创建 Power Pivot 表。在本章中，您将学习当数据表包含数千行时，如何使用 Power Pivot 表来探索数据。

In the previous chapter, you have learnt how to create a Power PivotTable from a normal set of data tables. In this chapter, you will learn how you can explore data with Power PivotTable, when the data tables contain thousands of rows.

为了更好地理解，我们将从一个访问数据库导入数据，该数据库是一个关系数据库。

For a better understanding, we will import the data from an access database, which you know is a relational database.

Loading Data from Access Database

要从 Access 数据库加载数据，请按照给定的步骤操作 -

To load data from the Access database, follow the given steps −

  1. Open a new blank workbook in Excel.

  2. Click Manage in the Data Model group.

  3. Click the POWERPIVOT tab on the Ribbon.

ribbon

“透视表”窗口会出现。

The Power Pivot window appears.

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

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

  3. Select From Access from the dropdown list.

power pivot

表导入向导出现。

The Table Import Wizard appears.

  1. Provide Friendly connection name.

  2. Browse to the Access database file, Events.accdb, the Events database file.

  3. Click on the Next > button.

friendly connection

Table Import 向导会显示用于选择如何导入数据的选项。

The Table Import wizard displays options for choosing how to import data.

单击 Select from a list of tables and views to choose the data to import ，然后单击 Next 。

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

import table

Table Import 向导会显示您已选取的 Access 数据库中的所有表格。选中所有的方框以选中所有的表格，然后单击“完成”。

The Table Import Wizard displays all the tables in the Access database that you have selected. Check all the boxes to select all the tables and click Finish.

table import wizard

Table Import 向导会显示 - Importing ，并呈现导入的状态。这可能需要几分钟的时间，您也可以单击 Stop Import 按钮来停止导入。

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

一旦数据导入完成，“导入表格向导”就会显示 - Success ，并显示导入的结果。单击 Close 。

Once the data import is complete, Table Import Wizard displays – Success and shows the results of the import. Click Close.

stop import

透视表在数据视图的不同标签中显示所有的导入表格。

Power Pivot displays all the imported tables in different tabs in Data View.

tabs in data view

单击图表视图。

Click on the Diagram View.

click diagram view

您可以观察到表格之间存在关系 - Disciplines and Medals 。这是因为当您从关系数据库（例如 Access）导入数据时，数据库中存在的相关性也导入到了透视表中的数据模型中。

You can observe that a relationship exists between the tables – Disciplines and Medals. This is because, when you import data from a relational database such as Access, the relationships that exist in the database also are imported to the Data Model in Power Pivot.

Creating a PivotTable from the Data Model

使用先前导入的表格创建一个数据透视表，如下：

Create a PivotTable with the tables that you have imported in the previous section as follows −

  1. Click PivotTable on the Ribbon.

  2. Select PivotTable from the drop down list.

  3. Select New Worksheet in the Create PivotTable dialog box that appears and click OK.

select new worksheet

在 Excel 窗口的新工作表中创建了一个空数据透视表。

An empty PivotTable is created in a new worksheet in the Excel window.

empty pivottable

在 Power 透视数据模型中列入的所导入表格将显示在数据透视表字段列表中。

All the imported tables that are a part of Power Pivot Data Model appear in the PivotTable Fields list.

  1. Drag the NOC_CountryRegion field in the Medals table to the COLUMNS area.

  2. Drag Discipline from the Disciplines table to the ROWS area.

  3. Filter Discipline to display only five sports: Archery, Diving, Fencing, Figure Skating, and Speed Skating. This can be done either in PivotTable Fields area, or from the Row Labels filter in the PivotTable itself.

  4. Drag Medal from the Medals table to the VALUES area.

  5. Select Medal from the Medals table again and drag it into the FILTERS area.

数据透视表将填充选定区域的添加字段和布局。

The PivotTable is populated with the added fields and in the chosen layout from the areas.

noc countryregion

Exploring Data with PivotTable

你可能只想显示奖牌数 > 80 的值。要执行此操作，请按照以下步骤操作：

You might want to display only those values with Medal Count > 80. To perform this, follow the given steps −

  1. Click the arrow to the right of Column Labels.

  2. Select Value Filters from the dropdown list.

  3. Select Greater Than…. from the second dropdown list.

  4. Click OK.

value filters

将会出现 Value Filter 对话框。在最靠右的框中输入 80，然后单击“确定”。

The Value Filter dialog box appears. Type 80 in the right-most box and click OK.

value filters dialog box

数据透视表只会显示奖牌总数高于 80 的地区。

The PivotTable displays only those regions with total number of medals more than 80.

region

你只需按照几个步骤，就可以用不同的表格生成你想要的特定报表。由于 Access 数据库中的表之间存在预先的关系，因此才能够实现这一点。由于你同时从数据库中导入了所有表格，因此 Power 透视会在其数据模型中重新创建关系。

You could arrive at the specific report that you wanted from the different tables in just few steps. This became possible because of the pre-existing relationships among the tables in the Access database. As you imported all the tables from the database together at the same time, Power Pivot recreated the relationships in its Data Model.

Summarizing Data from Different Sources in Power Pivot

如果你从不同来源获取数据表，或者没有同时从数据库中导入表格，或者如果你在工作簿中创建了新的 Excel 表格并将它们添加到数据模型中，则必须创建希望用于在数据透视表中进行分析和汇总的表格之间的关系。

If you get the data tables from different sources or if you do not import the tables from a database at the same time, or if you create new Excel tables in your workbook and add them to the Data Model, you have to create the relationships among the tables that you want to use for your analysis and summarization in the PivotTable.

  1. Create a new worksheet in the workbook.

  2. Create an Excel table – Sports.

summarizing data

将“体育”表格添加到数据模型中。

Add Sports table to Data Model.

add sports

使用字段 SportID 在表格 Disciplines and Sports 之间创建关系。

Create a relationship between the tables Disciplines and Sports with the field SportID.

disciplines and sports

将字段 Sport 添加到数据透视表中。

Add the field Sport to the PivotTable.

sport

整理字段 - Discipline and Sport 在行区域。

Shuffle the fields - Discipline and Sport in the ROWS area.

rows area

Extending Data Exploration

您还可以进一步探索 Events 表中的数据。

You can get the table Events also into further data exploration.

创建 Events 和 Medals 表格之间的关系，字段为 DisciplineEvent 。

Create a relationship between the tables- Events and Medals with the field DisciplineEvent.

extending data exploration

将表格 Hosts 添加到工作簿和数据模型中。

Add a table Hosts to the workbook and Data Model.

hosts

Extending the Data Model using Calculated Columns

为了将主机表格连接到任何其他表格，它应该具有一个字段，该字段的值唯一标识主机表格中的每一行。由于主机表格中不存在这样的字段，因此您可以在主机表格中创建计算列，以便其中包含唯一值。

To connect Hosts table to any of the other tables, it should have a field with values that uniquely identify each row in the Hosts table. As no such field exists in the Host table, you can create a calculated column in the Hosts table so that it contains unique values.

  1. Go to the Hosts table in Data View of the PowerPivot window.

  2. Click the Design tab on the Ribbon.

  3. Click Add.

最右边的名为添加列的列被高亮显示。

The right-most column with the header Add Column is highlighted.

highlighted

  1. Type the following DAX formula in the formula bar = CONCATENATE ([Edition], [Season])

  2. Press Enter.

使用上述 DAX 公式生成的值创建了一个新列，标题为 CalculatedColumn1 ，并且该列已填满。

A new column is created with the header CalculatedColumn1 and the column is filled by the values resulting from the above DAX formula.

dax formula

右键单击新列，然后从下拉列表中选择重命名列。

Right-click on the new column and select Rename Column from the dropdown list.

rename column

在新列的标题中键入 EditionID 。

Type EditionID in the header of the new column.

editionid

如您所见，列 EditionID 在 Hosts 表格中有唯一值。

As you can see, the column EditionID has unique values in the Hosts table.

Creating a Relationship Using Calculated Columns

如果您必须在 Hosts 表格和 Medals 表格之间创建关系，则列 EditionID 还应存在于 Medals 表格中。按照以下步骤在 Medals 表格中创建计算列-

If you have to create a relationship between the Hosts table and the Medals table, the column EditionID should exist in the Medals table also. Create a calculated column in Medals table as follows −

  1. Click on the Medals table in the Data View of Power Pivot.

  2. Click the Design tab on the Ribbon.

  3. Click Add.

在公式栏中键入 DAX 公式 = YEAR ([EDITION]) ，然后按 Enter。

Type the DAX formula in the formula bar = YEAR ([EDITION]) and press Enter.

将创建的新列重命名为 Year，然后单击 Add 。

Rename the new column that is created as Year and click Add.

created as year

  1. Type the following DAX formula in the formula bar = CONCATENATE ([Year], [Season])

  2. Rename the new column that is created as EditionID.

concatenate

正如您所观察到的，Medals 表格中的 EditionID 列与 Hosts 表格中的 EditionID 列具有相同的值。因此，您可以使用 EditionID 字段在表格之间（Medals 和 Sports）创建关系。

As you can observe, the EditionID column in the Medals table has identical values as the EditionID column in the Hosts table. Therefore, you can create a relationship between the tables – Medals and Sports with the EditionID field.

  1. Switch to the diagram view in PowerPivot window.

  2. Create a relationship between the tables- Medals and Hosts with the field that is obtained from the calculated column i.e. EditionID.

calculated column

现在可以将主办方表中的字段添加到 Power 透视表中。

Now you can add fields from Hosts table to Power PivotTable.