Excel Power Pivot 简明教程

Excel Power PivotTable - Creation

Power 透视表基于 Power 透视表数据库，该数据库被称为数据模型。你已经学习了数据模型的强大功能。Power 透视表的力量在于其在 Power 透视表中汇总数据模型数据的的能力。正如你所知道的，数据模型可以处理数百万行的数据，而且这些数据来自不同的输入。这让 Power 透视表可以在几分钟内汇总来自任何地方的数据。

Power PivotTable is based on the Power Pivot database, which is called the Data Model. You have already learnt the powerful features of the Data Model. The power of Power Pivot is in its ability to summarize data from the Data Model in the Power PivotTable. As you are aware, the Data Model can handle huge data spanning millions of rows and coming from diverse inputs. This enables Power PivotTable to summarize the data from anywhere in a matter of few minutes.

Power 透视表在布局上类似于透视表，具有以下不同之处−

Power PivotTable resembles PivotTable in its layout, with the following differences −

  1. PivotTable is based on Excel tables, whereas Power PivotTable is based on data tables that are part of Data Model.

  2. PivotTable is based on a single Excel table or data range, whereas Power PivotTable can be based on multiple data tables, provided they are added to Data Model.

  3. PivotTable is created from Excel window, whereas Power PivotTable is created from PowerPivot window.

Creating a Power PivotTable

假设你在数据模型中有两个数据表 − Salesperson 和 Sales。要从这两个数据表创建 Power 透视表，请执行以下操作 −

Suppose you have two data tables − Salesperson and Sales in the Data Model. To create a PowerPivot Table from these two data tables, proceed as follows −

  1. Click the Home tab on the Ribbon in PowerPivot window.

  2. Click PivotTable on the Ribbon.

  3. Select PivotTable from the dropdown list.

creating power pivottable

创建透视表对话框出现。正如你所观察到的，这是一个简单的对话框，没有任何关于数据的查询。这是因为 Power 透视表总是基于数据模型，即定义了它们之间关系的数据表。

Create PivotTable dialog box appears. As you can observe, this is a simple dialog box, without any queries on data. This is because, Power PivotTable is always based on Data Model, i.e. the data tables with the relationships defined among them.

选择新建工作表并单击确定。

Select New Worksheet and click OK.

new

在 Excel 窗口中创建一个新工作表并会出现一个空透视表。

A new worksheet is created in Excel window and an empty PivotTable appears.

new worksheet created

正如你所观察到的，Power 透视表的布局类似于透视表。 PIVOTTABLE TOOLS 出现于功能区，其中 ANALYZE 和 DESIGN 选项卡与透视表相同。

As you can observe, the layout of the Power PivotTable is similar to that of PivotTable. The PIVOTTABLE TOOLS appear on the Ribbon, with ANALYZE and DESIGN tabs, identical to PivotTable.

透视表字段列表出现在工作表的右侧。在这里，你会发现与透视表的一些不同之处。

The PivotTable Fields List appears on the right side of the worksheet. Here, you will find some differences from PivotTable.

Power PivotTable Fields

透视表字段列表有两个选项卡 − 标题下方和字段列表上方出现的活动状态和全部状态。 ALL 选项卡被高亮显示。

The PivotTable Fields list has two tabs − ACTIVE and ALL that appear below the title and above the fields list. The ALL tab is highlighted.

pivottable field

请注意， ALL 标签显示“数据模型”中的所有数据表，而 ACTIVE 标签显示所有为所选 Power PivotTable 选择的数据表。由于 Power PivotTable 为空，这意味着尚未选择任何数据表；因此，默认情况下会选择 ALL 标签，并显示数据模型中当前存在的两个表。此时，如果您单击 ACTIVE 标签，则“字段”列表将为空。

Note that the ALL tab displays all the data tables in the Data Model and ACTIVE tab displays all the data tables that are chosen for the Power PivotTable at hand. As the Power PivotTable is empty, it means that no data table is selected yet; hence by default, ALL tab is selected and the two tables that are currently in the Data Model are displayed. At this point, if you click the ACTIVE tab, the Fields list would be empty.

  1. Click on the table names in the PivotTable Fields list under ALL. The corresponding fields with check boxes will appear.

  2. Each table name will have the symbol on the left side.

  3. If you place the cursor on this symbol, the Data Source and the Model Table Name of that data table will be displayed.

active tab

  1. Drag Salesperson from Salesperson table to the ROWS area.

  2. Click the ACTIVE tab.

click active tab

正如您所观察到的，“销售人员”字段如期显示在 PivotTable 中，“销售人员”表显示在 ACTIVE 标签下。

As you can observe, the field Salesperson appears in the PivotTable and the table Salesperson appears under the ACTIVE tab as expected.

  1. Click the ALL tab.

  2. Click on Month and Order Amount in the Sales table.

all tab

再次单击 ACTIVE 标签。两张表 - “销售额”和“销售人员”显示在 ACTIVE 标签下。

Again, click the ACTIVE tab. Both the tables − Sales and Salesperson appear under the ACTIVE tab.

under active tab

  1. Drag Month to COLUMNS area.

  2. Drag Region to FILTERS area.

column area

  1. Click the arrow next to ALL in the Region filter box.

  2. Click Select Multiple Items.

  3. Select North and South and click OK.

multiple items

以升序对列标签进行排序。

Sort the column labels in the ascending order.

column labels

Power PivotTable 可以动态修改，以浏览和报告数据。

Power PivotTable can be modified dynamically explore and report data.