Excel Data Analysis 简明教程

Advanced Data Analysis - Data Model

Data Model 可用于 Excel 2013 及更高版本。在 Excel 中，您可以使用数据模型来整合当前工作簿中多个表的数据和/或来自导入的数据和/或通过数据连接连接到工作簿的数据源。

Data Model is available in Excel 2013 and later versions. In Excel, you can use the Data Model to integrate data from multiple tables in the current workbook and / or from the imported data and / or from the data sources connected to the workbook through data connections.

使用数据模型，您可以在数据表之间创建关系。数据模型在数据透视表、数据透视图、PowerPivot 和 Power View 报告中以透明的方式使用。

With a Data Model, you can create relationships among the tables. Data model is used transparently in PivotTable, PivotChart, PowerPivot and Power View reports.

Creating Data Model while Importing Data

当您从包含多个相关数据表的关系数据库（例如 Microsoft Access 数据库）中导入数据时，如果您一次导入多张表，则会自动创建数据模型。

When you import data from relational databases like Microsoft Access database that contain multiple related tables, Data Model is automatically created if you import more than one table at once.

creating data model

在从以下数据源导入数据时，您可以选择性地将数据表添加到数据模型中：

You can optionally add tables to the Data Model, when you import data from the following data sources −

  1. Relational databases, one table at a time

  2. Text Files

  3. Excel Workbooks

例如，当您从 Excel 工作簿导入数据时，您可以观察到 Add this data to the Data Model 选项，它具有启用的复选框。

For example, while you are importing data from an Excel workbook, you can observe the option Add this data to the Data Model, with an enabled check box.

observe option

如果您希望将要导入的数据添加到数据模型，请勾选此框。

If you want to add the data you are importing to the Data Model, check the box.

Creating Data Model from Excel Tables

可以使用 PowerPivot 命令使用 Excel 表创建数据模型。您将在后面的章节中详细学习 PowerPivot。

You can create the Data Model from Excel tables using PowerPivot commands. You will learn PowerPivot in detail in later chapters.

所有数据模型命令均在功能区的 PowerPivot 选项卡中提供。您可以使用这些命令将 Excel 表添加到数据模型。

All the Data Model commands are available under the PowerPivot tab on the Ribbon. You can add Excel tables to the Data Model with these commands.

考虑以下销售数据工作簿，其中您有一个产品目录工作表，其中包含产品、产品 ID 和价格。您有四张工作表用于四个地区 - 东部、北部、南部和西部。

Consider the following sales data workbook, wherein you have Product Catalog worksheet that contains Product, Product ID and Price. You have four worksheets for the sales in 4 regions – East, North, South and West.

这四张工作表中的每一张都包含了每个月份中每个产品的销售数量和总金额。您需要计算每个地区中每个产品的总金额和每个地区的总销售金额。

Each of these four worksheets contain No. of Units sold and Total Amount for each of the Products in each month. You need to calculate the total amount for each of the products in each region and total amount of sales in each region.

creating data model tables

以下步骤使您能够获得预期的结果−

The following steps enable you to arrive at the desired results −

  1. Start with creating the Data Model.

  2. Click in the Product Catalog worksheet.

  3. Click the POWERPIVOT tab on the Ribbon.

  4. Click Add to Data Model. The Create Table dialog box appears.

  5. Select the table range.

  6. Check the box My table has headers. Click OK.

desired results

一个新窗口 - PowerPivot for Excel - <你的 Excel 文件名> 出现。

A new window – PowerPivot for Excel - <your Excel file name> appears.

以下消息出现在空白窗口的中央−

The following message appears in the center of the blank window −

message appears

您添加到数据模型的产品积压表显示为 PowerPivot 窗口中的一个工作表。表中的每一行都是一个记录，您可以使用窗口底部的左右箭头按钮来回返回记录。

The table Product Backlog that you added to the Data Model appears as a sheet in PowerPivot window. Each row in the table is a record and you can go back and forth the records using the left and right arrow buttons at the bottom of the window.

product backlog

  1. Click the Linked Table tab in the PowerPivot window.

  2. Click Go to Excel Table.

excel table

Excel 数据窗口出现。

The Excel data window appears.

  1. Click the worksheet tab – East.

  2. Click the POWERPIVOT tab on the Ribbon.

  3. Click Add to Data Model.

另一个工作表显示在 PowerPivot 窗口中，显示东部表。

Another sheet appears in the PowerPivot window displaying the East table.

对北、南和西工作表重复进行操作。总之，你已向数据模型中添加了五个表。你的 PowerPivot 窗口如下所示：

Repeat for the worksheets – North, South and West. In all, you have added five tables to the Data Model. Your PowerPivot window looks as below −

excel data window

Creating Relationships between Tables

如果你想在各表之间进行计算，你必须首先定义它们之间的关系。

If you want to make calculations across the tables, you have to first define relationships among them.

  1. Click the Home tab on the Ribbon in the PowerPivot window. As you can observe, the tables are displayed in the Data View.

  2. Click Diagram View.

creating relationships between tables

这些表出现在“图表视图”中。正如你所看到的，某些表可能不在显示区域，而且表中的所有字段可能都不可见。

The tables appear in the Diagram View. As you observe, some of the tables may be out of display area and all the fields in the tables may not be visible.

  1. Resize each table to show all the fields in that table.

  2. Drag and arrange the tables so that all are displayed.

drag and arrange table

  1. In the East table, click on Product ID.

  2. Click the Design tab on the Ribbon.

  3. Click Create Relationship. The Create Relationship dialog box appears.

click create relationship

在“表”下面的方框中，显示“东”。在“列”下面的方框中，显示“产品 ID”。

In the box under Table, East is displayed. In the box under Column, Product ID is displayed.

  1. In the box under Related Lookup Table, select Product Catalog.

  2. Product ID appears in the box under Related Lookup Column.

  3. Click the Create button.

click create button

表示“东”表与产品积压之间的关系的线出现。

Line representing Relationship between the tables East and Product Backlog appears.

relationship between tables

  1. Repeat the same steps for the tables – North, South and West. Relationship Lines appear.

relationship lines

Summarizing the Data in the Tables in the Data Model

现在，你已准备好在几个步骤内总结每个区域中每个产品的销售数据。

Now, you are all set to summarize the sales data for each of the products in each region in just few steps.

  1. Click the Home tab.

  2. Click PivotTable.

  3. Select PivotTable from the drop-down list.

select pivottable

在 Excel 表窗口中，“创建数据透视表”对话框随即出现。选择“新建工作表”。

Create PivotTable dialog box appears in the Excel tables window. Select New Worksheet.

select new worksheet

在新建工作表中，出现一个空白数据透视表。如下所示，字段列表包含数据模型中的所有表以及所显示的所有字段。

In a new worksheet, an empty PivotTable appears. As you can observe, the Fields List contains all the tables in the Data Model with all the fields displayed.

pivottable

  1. Select Product ID from Table 1 (Product Catalog).

  2. Select Total Amount from the other four tables.

  3. For each of fields in ∑ Values, change the Custom Name in Value Field Settings to display the region names as column labels.

change custom name

总金额的总和将由你给予的标签代替。汇总了来自所有数据表的数值的 PivotTable 向你展示所需结果。

The sum of Total Amount will be replaced by the label you give. PivotTable with summarized values from all the data tables shows you the required results.

total amount

Adding Data to Data Model

你可以向数据模型添加一个新的数据表，或向数据模型中的现有表中添加新的数据行。

You can add a new data table to the Data Model or new rows of data to the existing tables in the Data Model.

通过以下步骤向数据模型添加一个新的数据表。

Add a new data table to the Data Model with the following steps.

  1. Click the DATA tab on the Ribbon.

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

  3. Click the Tables tab. The names of all the tables in the workbook will be displayed.

  4. Click the name of the table you want to add to the Data Model.

adding data to data model

单击打开按钮。将显示导入数据对话框。

Click on the Open button. The Import Data dialog box appears.

open button

正如你所了解的，在导入数据表时，它会自动添加到数据模型中。新添加的表格显示在 PowerPivot 窗口中。

As you are aware, while importing data table, it is automatically added to the Data Model. The newly added table appears in the PowerPivot window.

向数据模型的现有表格中添加新的数据行。

Add new rows of data to the existing tables in the Data Model.

刷新数据连接。来自数据源的新数据行会添加到数据模型中。

Refresh the Data Connection. New rows of data from the data source get added to the Data Model.