Excel Data Analysis 简明教程

Advanced Data Analysis - Data Model

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

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

Creating Data Model while Importing Data

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

creating data model

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

  1. 每次一张表的关系数据库

  2. Text Files

  3. Excel Workbooks

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

observe option

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

Creating Data Model from Excel Tables

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

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

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

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

creating data model tables

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

  1. 从创建数据模型开始。

  2. 单击产品目录工作表。

  3. 单击功能区上的 POWERPIVOT 选项卡。

  4. 单击添加到数据模型。创建表对话框随即出现。

  5. Select the table range.

  6. 勾选我的表有标题框。单击确定。

desired results

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

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

message appears

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

product backlog

  1. 单击 PowerPivot 窗口中的链接工作表选项卡。

  2. 单击转到 Excel 表。

excel table

Excel 数据窗口出现。

  1. 单击工作表选项卡 - 东部。

  2. 单击功能区上的 POWERPIVOT 选项卡。

  3. 单击添加到数据模型。

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

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

excel data window

Creating Relationships between Tables

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

  1. 在 PowerPivot 窗口的 Ribbon 上单击“开始”选项卡。正如你所看到的，这些表显示在“数据视图”中。

  2. Click Diagram View.

creating relationships between tables

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

  1. 调整每个表大小，以显示该表中的所有字段。

  2. 拖动并排列这些表，以便所有表都显示出来。

drag and arrange table

  1. 在“东”表中，单击“产品 ID”。

  2. 在功能区上点击“设计”选项卡。

  3. 单击“创建关系”。“创建关系”对话框随即出现。

click create relationship

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

  1. 在“相关查找表”下面的方框中，选择“产品目录”。

  2. 产品 ID 出现 在“相关查找列”下面的方框中。

  3. Click the Create button.

click create button

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

relationship between tables

  1. 对北、南和西表重复相同的步骤。关系线会出现。

relationship lines

Summarizing the Data in the Tables in the Data Model

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

  1. Click the Home tab.

  2. Click PivotTable.

  3. 从下拉列表中选择“数据透视表”。

select pivottable

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

select new worksheet

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

pivottable

  1. 从表 1（产品目录）中选择“产品 ID”。

  2. 从其他四个表中选择“总金额”。

  3. 对于 ∑ Values 中的每个字段，将 Value Field Settings 中的自定义名称更改为将区域名称显示为列标签。

change custom name

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

total amount

Adding Data to Data Model

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

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

  1. 单击功能区上的“数据”选项卡。

  2. 单击获取外部数据组中的现有连接。将显示现有连接对话框。

  3. 单击表格选项卡。将显示工作簿的所有表格的名称。

  4. 单击你想要添加到数据模型的表格的名称。

adding data to data model

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

open button

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

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

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