使用数据模型可以集成多个表中的数据,从而有效地在 Excel 工作簿中生成关系数据源。 在 Excel 中,数据模型以透明方式使用,提供数据透视表和数据透视图中使用的表格数据。 数据模型可视化为字段列表中的表集合,大多数时候,你甚至永远不会知道它存在。

在开始使用数据模型之前,需要获取一些数据。 为此,我们将使用 Get & 转换 (Power Query) 体验,因此您可能需要后退一步,观看视频,或按照我们的学习指南获取&转换和 Power Pivot。

Power Pivot 在哪里?

转换 (Power Query) &在哪里?

  • Excel 2016 & Microsoft 365 专属 Excel - 获取&转换 (Power Query) 已与“数据”选项卡上的 Excel 集成。

  • Excel 2013 - Power Query是 Excel 附带的加载项,但需要激活。 转到“ 文件 > 选项 ”> 加载项,然后在窗格底部的 “管理 ”下拉列表中,选择 “COM 加载项 ”> Go。 检查 Microsoft Power Query for Excel,然后确定激活它。 将向功能区添加Power Query选项卡。

  • Excel 2010 - 下载并安装Power Query加载项。. 激活后,会将Power Query选项卡添加到功能区。

开始

首先,需要获取一些数据。

  1. 在Excel 2016和 Microsoft 365 专属 Excel 中,使用数据>获取&转换数据>从任意数量的外部数据源(例如文本文件、Excel 工作簿、网站、Microsoft Access、SQL Server 或其他包含多个相关表的关系数据库)入数据。

    在 Excel 2013 和 2010 中,转到Power Query>获取外部数据,然后选择数据源。

  2. Excel 将提示您选择一个表。 如果要从同一数据源获取多个表,请检查 “启用”选择多个表 选项。 选择多个表时,Excel 会自动为你创建数据模型。

    注意: 对于这些示例,我们使用 Excel 工作簿,其中包含有关课程和成绩的虚构学生详细信息。 可以下载 我们的学生数据模型示例工作簿,然后继续操作。 还可以 下载具有已完成数据模型的版本。.

    获取 (Power Query) 导航器&转换
  3. 选择一个或多个表,然后单击 “加载”。

    如果需要编辑源数据,可以选择 “编辑 ”选项。 有关更多详细信息,请参阅:查询编辑器 (Power Query) 简介

现在有一个数据模型,其中包含导入的所有表,它们将显示在数据透视表 字段列表中。

注意: 

  • 当您在 Excel 中同时导入两个或更多表格时,将隐式创建模型。

  • 当您使用 Power Pivot 加载项导入数据时,将显式创建模型。 在外接程序中,模型以类似于 Excel 的表格布局表示,其中每个选项卡都包含表格数据。 请参阅使用 Power Pivot 加载项获取数据,了解使用SQL Server数据库导入数据的基础知识。

  • 一个模型可以只包含一个表格。 要基于一个表创建模型,请选择该表,然后单击“添加到数据模型”(Power Pivot 中)。 如果要使用 Power Pivot 功能(如筛选的数据集、计算列、计算字段、KPI 和层次结构),可以执行此操作。

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

  • 有关如何减小数据模型大小的提示,请参阅 使用 Excel 和 Power Pivot 创建内存高效的数据模型

  • 有关进一步探索,请参阅 教程:将数据导入 Excel,并创建数据模型

提示: 如何判断工作簿是否具有数据模型? 转到 Power Pivot > 管理。 如果看到类似工作表的数据,则会存在一个模型。 请参阅: 了解工作簿数据模型中使用哪些数据源 以了解详细信息。

在表之间创建关系

下一步是在表之间创建关系,以便可以从其中任何一个表拉取数据。 每个表都需要有主键或唯一字段标识符,例如学生 ID 或类号。 最简单的方法是在 Power Pivot 的图示视图中拖放这些字段以连接它们。

  1. 转到 Power Pivot > 管理

  2. 在“ 开始 ”选项卡上,选择 “关系图视图”。

  3. 所有导入的表都将显示,可能需要一些时间来调整它们的大小,具体取决于每个表具有的字段数。

  4. 接下来,将主键字段从一个表拖到下一个表。 下面的示例是学生表的图示视图:

    Power Query数据模型关系图视图

    我们已创建以下链接:

    • tbl_Students |学生 ID > tbl_Grades |学生 ID

      换句话说,将“学生 ID”字段从“学生”表拖动到“成绩”表中的“学生 ID”字段。

    • tbl_Semesters |学期 ID > tbl_Grades |学期

    • tbl_Classes |类号> tbl_Grades |类号

    注意: 

    • 为了创建关系,字段名称不需要相同,但它们确实需要是相同的数据类型。

    • 关系图视图中的连接器一侧有“1”,另一侧有“*”。 这意味着表之间存在一对多关系,这决定了数据透视表中的数据使用方式。 请参阅: 数据模型中的表之间的关系 ,了解详细信息。

    • 连接器仅指示表之间存在关系。 它们实际上不会显示哪些字段相互链接。 若要查看链接,请转到 Power Pivot >管理>设计>关系>管理关系。 在 Excel 中,可以转到 “数据 > 关系”。

使用数据模型创建数据透视表或数据透视图

Excel 工作簿只能包含一个数据模型,但该模型可以包含多个可在整个工作簿中重复使用的表。 可以随时向现有数据模型添加更多表。

  1. Power Pivot中,转到 “管理”。

  2. 在“ 开始 ”选项卡上,选择 “数据透视表”。

  3. 选择要将数据透视表放置的位置:新工作表或当前位置。

  4. 单击 “确定”,Excel 将添加一个空的数据透视表,右侧显示“字段列表”窗格。

    Power Pivot 数据透视表字段列表

接下来, 创建数据透视表,或 创建数据透视图。 如果已在表之间创建关系,则可以在数据透视表中使用其任何字段。 我们已经在学生数据模型示例工作簿中创建了关系。

将现有的不相关数据添加到数据模型

假设已导入或复制了要在模型中使用的大量数据,但尚未将其添加到数据模型。 将新数据推送到模型比您想象的更为简单。

  1. 首先选择要添加到模型的数据中的任何单元格。 它可以是任意数据范围,但格式化为 Excel 表 的数据是最好的。

  2. 使用下面的一种方法添加数据:

  3. 单击“Power Pivot”>“添加到数据模型”。

  4. 单击“插入”>“数据透视表”,然后选中“创建数据透视表”对话框中的“将此数据添加到数据模型”。

现在将区域或表格作为链接表添加到了模型。 要了解有关在模型中使用链接表的详细信息,请参阅在 Power Pivot 中使用 Excel 链接表添加数据

将数据添加到 Power Pivot 表

在 Power Pivot 中,您不能通过直接键入新行来向表格添加行,而在 Excel 工作表中则可以。 但是,可以通过 复制和粘贴或更新源数据并 刷新 Power Pivot 模型来添加行。

需要更多帮助吗?

你始终可以在 Excel 技术社区中咨询专家或在 Answers 社区获取

另请参阅

获取&转换和 Power Pivot 学习指南

查询编辑器简介 (Power Query)

使用 Excel 和 Power Pivot 创建内存高效的数据模型

教程:将数据导入 Excel 中并创建数据模型

了解工作簿数据模型中使用哪些数据源

数据模型中表之间的关系

Need more help?

Expand your skills
Explore Training
Get new features first
Join Microsoft Office Insiders

Was this information helpful?

How satisfied are you with the translation quality?
What affected your experience?

Thank you for your feedback!

×