在 Excel 中创建数据模型

在 Excel 中创建数据模型

数据模型允许您集成来自多个表的数据,有效地在工作簿中构建Excel数据源。 在Excel中,数据模型以透明方式使用,提供在数据透视表和数据透视图中使用的表格数据。 数据模型可视化为字段列表中的表集合,在大多数情况下,您甚至永远不会知道它位于该集合中。

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

Power Pivot 在哪里?

  • Excel 2016 & Microsoft 365 专属 Excel - Power Pivot 包含在功能区中。

  • Excel 2013 - Power Pivot 是 Office Professional Plus 2013 Excel版本的一部分,但默认情况下未启用。 详细了解如何为 2013年 Excel Power Pivot 加载项。

  • Excel 2010 -下载 Power Pivot加载项,然后安装 Power Pivot 加载项

从 Power Query &获取 (转换) ?

  • Excel 2016 & Microsoft 365 专属 Excel - &" ("选项卡上) Power Query Excel集成了"获取转换"功能。

  • Excel 2013 - Power Query 是一个加载项,包含在 Excel,但需要激活。 转到"文件>">"加载项",然后在窗格底部的"管理"下拉列表中,选择"COM加载项">"转到"。 检查Microsoft Power Query 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 PivotTable 字段列表

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

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

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

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

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

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

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

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

将数据添加到表 Power Pivot 表

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

需要更多帮助吗?

可随时在 Excel 技术社区中咨询专家,在解答社区获得支持,或在 Excel User Voice 上建议新功能或功能改进。

另请参阅

获取&和 Power Pivot 学习指南

查询编辑器简介 (Power Query)

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

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

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

数据模型中表之间的关系

需要更多帮助?

扩展你的 Office 技能
了解培训
抢先获得新功能
加入 Office 预览体验计划

此信息是否有帮助?

谢谢你的反馈! 可能需要转接到 Office 支持专员。

×