Excel Power Pivot 简明教程

Excel Power Pivot - Data Model

数据模型是 Excel 2013 中引入的一种新方法，用于集成来自多个表的数据，有效地在 Excel 工作簿中构建关系数据源。在 Excel 中，数据模型以透明的方式使用，可提供用于数据透视表和数据透视图形的数据。在 Excel 中，您可以通过包含表名称和相应字段的数据透视表/数据透视图形字段列表访问表及其相应的值。

A Data Model is a new approach introduced in Excel 2013 for integrating data from multiple tables, effectively building a relational data source inside an Excel workbook. Within Excel, Data Model is used transparently, providing tabular data used in PivotTables and PivotCharts. In Excel, you can access the tables and their corresponding values through the PivotTable / PivotChart Field lists that contain the table names and corresponding fields.

数据模型在 Excel 中的主要用途是 Power Pivot 使用它。数据模型可以看作是 Power Pivot 数据库，并且 Power Pivot 的所有强大功能都是通过数据模型进行管理的。Power Pivot 的所有数据操作本质上都是显式的，并且可以在数据模型中视化。

The main use of Data Model in Excel is its usage by Power Pivot. Data Model can be considered as the Power Pivot database, and all the power features of Power Pivot are managed with the Data Model. All data operations with Power Pivot are explicit in nature and can be visualized in the Data Model.

在本章中，您将详细了解数据模型。

In this chapter, you will understand the Data Model in detail.

Excel and Data Model

Excel 工作簿中只有一个数据模型。当您使用 Excel 时，数据模型使用是隐式的。您不能直接访问数据模型。您只能在数据透视表或数据透视图形的“字段”列表中查看“数据模型”中的多个表并使用它们。创建数据模型和添加数据也是在 Excel 中隐式完成的，此时将外部数据获取到 Excel 中。

There will be only one Data Model in an Excel workbook. When you work with Excel, Data Model usage is implicit. You cannot directly access the Data Model. You can only see the multiple tables in the Data Model in the Fields list of PivotTable or PivotChart and use them. Creating the Data Model and adding data is also done implicitly in Excel, while you are getting external data into Excel.

如果你要查看数据模型，你可以按以下步骤进行…​

If you want to look at the Data Model, you can do so as follows −

  1. Click the POWERPIVOT tab on the Ribbon.

  2. Click Manage.

如果工作簿中存在数据模型，它将显示为表格，每个表格都有一个选项卡。

Data Model, if exists in the workbook, will be displayed as tables, each one with a tab.

Note − 如果你将 Excel 表添加到数据模型，你不会将 Excel 表转化为数据表。Excel 表的副本将作为数据表添加到数据模型中，并在两者之间创建一个链接。因此，如果在 Excel 表中进行了更改，数据表也会更新。但是，从存储的角度来看，有两个表。

Note − If you add an Excel table to Data Model, you will not transform the Excel table into a data table. A copy of the Excel table is added as a data table in the Data Model and a link is created between the two. Hence, if changes are done in the Excel table, the data table also is updated. However, from the storage point of view, there are two tables.

Power Pivot and Data Model

数据模型本质上是 Power Pivot 的数据库。即使你从 Excel 创建数据模型，它也只构建 Power Pivot 数据库。创建数据模型和/或添加数据是在 Power Pivot 中明确完成的。

Data Model is inherently the database for Power Pivot. Even when you create the Data Model from Excel, it builds the Power Pivot database only. Creating the Data Model and/or adding data is done explicitly in Power Pivot.

事实上，你可以从 Power Pivot 窗口管理数据模型。你可以向数据模型添加数据，从不同的数据源导入数据，查看数据模型，在表之间创建关系，创建计算字段和计算列，等等。

In fact, you can manage the Data Model from Power Pivot window. You can add data to Data Model, import data from different data sources, view the Data Model, create relationships between the tables, create calculated fields and calculated columns, etc.

Creating a Data Model

你既可以从 Excel 添加表到数据模型，也可以直接将数据导入 Power Pivot，从而创建 Power Pivot 数据模型表。你可以在 Power Pivot 窗口中单击“管理”来查看数据模型。

You can either add tables to the Data Model from Excel or you can directly import data into Power Pivot, thus creating the Power Pivot Data Model tables. You can view the Data Model by clicking Manage in the Power Pivot window.

你将在“通过 Excel 加载数据”一章中了解如何从 Excel 向数据模型添加表。你将在“将数据加载到 Power Pivot”一章中了解如何将数据加载到数据模型中。

You will understand how to add tables from Excel to the Data Model in the chapter – Loading Data through Excel. You will understand how to load data into Data Model in the chapter – Loading Data into Power Pivot.

Tables in Data Model

数据模型中的表可以定义为一组跨不同表持有关系的表。这些关系使人们能够出于分析和报告目的将来自不同表的相关数据进行组合。

Tables in Data Model can be defined as a set of tables holding relationships across them. The relationships enable combining related data from different tables for analysis and reporting purposes.

数据模型中的表称为数据表。

The tables in the Data Model are called Data Tables.

数据模型中的表被认为是一组记录（记录是一行），由字段（字段是一列）组成。你不能编辑数据表中的各个项目。但是，你可以向数据表中追加行或添加计算列。

A table in the Data Model is considered as a set of records (a record is a row) made up of fields (a field is a column). You cannot edit individual items in a data table. However, you can append rows or add calculated columns to the data table.

Excel Tables and Data Tables

Excel 表仅仅是单独表的集合。一个工作表上可以有多个表。每个表可以单独访问，但不可能同时访问来自多个 Excel 表的数据。这就是当您创建数据透视表时，它仅基于一个表的原因。如果您需要同时使用来自两个 Excel 表的数据，您需要首先将它们合并到一个 Excel 表中。

Excel tables are just a collection of separate tables. There can be multiple tables on a worksheet. Each table can be accessed separately, but it is not possible to access data from more than one Excel table at the same time. This is the reason that when you create a PivotTable, it is based on only one table. If you need to use the data from two Excel tables collectively, you need to first merge them into a single Excel table.

另一方面，数据表与其他有关系的数据表共存，有助于组合多表中的数据。将数据导入 Power Pivot 中时，将创建数据表。在创建数据透视表时，您还可以将 Excel 表添加到数据模型，以获取外部数据或从多表中获取数据。

A data table on the other hand coexists with other data tables with relationships, facilitating the combination of data from multiple tables. Data tables get created when you import data into Power Pivot. You can also add Excel tables to the Data Model while you are creating a Pivot Table getting external data or from multiple tables.

数据模型中的数据表可以通过两种方式查看 −

The data tables in the Data Model can be viewed in two ways −

  1. Data View.

  2. Diagram View.

Data View of Data Model

在数据模型的数据视图中，每个数据表都存在于一个单独的选项卡中。数据表行是记录，列表示字段。选项卡包含表名称，列标题是该表中的字段。您可以在数据视图中使用数据分析表达式 (DAX) 语言进行计算。

In the data view of the Data Model, each data table exists on a separate tab. The data table rows are the records and columns represent the fields. The tabs contain the table names and the column headers are the fields in that table. You can do calculations in the data view using the Data Analysis Expressions (DAX) language.

data view

Diagram View of Data Model

在数据模型的图表视图中，所有数据表都由包含表名称的框表示，并包含表中的字段。您只需拖动即可安排图表视图中的表。您可以调整数据表的大小，以便显示表中的所有字段。

In the diagram view of the Data Model, all the data tables are represented by boxes with the table names and contain the fields in the table. You can arrange the tables in the diagram view by just dragging them. You can adjust the size of a data table so that all the fields in the table are displayed.

diagram view

Relationships in Data Model

您可以在图表视图中查看关系。如果两表之间已定义关系，则会显示连接源表和目标表的箭头。如果您想知道关系中使用了哪些字段，只需双击箭头。箭头和两表中的两个字段将被突出显示。

You can view the relationships in the diagram view. If two tables have a relationship defined between them, an arrow connecting the source table to the target table appears. If you want to know which fields are used in the relationship, just double click the arrow. The arrow and the two fields in the two tables are highlighted.

如果您导入具有主键和外键关系的相关表，表关系会自动创建。Excel 可以使用导入的表关系信息作为数据模型中表关系的基础。

Table relationships will be created automatically if you import related tables that have primary and foreign key relationships. Excel can use the imported relationship information as the basis for table relationships in the Data Model.

您还可以在两种视图中的任何一种中显式创建关系 −

You can also explicitly create relationships in either of the two views −

  1. Data View − Using Create Relationship dialog box.

  2. Diagram View − By clicking and dragging to connect the two tables.

Create Relationship Dialog Box

Create Relationship Dialog Box

在一个关系中，涉及四个实体 −

In a relationship, four entities are involved −

  1. Table − The data table from which the relationship starts.

  2. Column − The field in the Table that is also present in the related table.

  3. Related Table − The data table where the relationship ends.

  4. Related Column − The field in the related table that is same as the field represented by Column in Table. Note that the values of Related Column should be unique.

在图表视图中，可以通过单击表中的字段并将鼠标拖动到相关表来创建关系。

In the diagram view, you can create the relationship by clicking on the field in the table and dragging to the related table.

您将在本章中了解有关关系的更多信息 - 使用 Power Pivot 管理数据表和关系。

You will learn more about relationships in the chapter - Managing Data Tables and Relationships with Power Pivot.