在 Excel 中创建数据模型

在 Excel 中创建数据模型

注意: 我们希望能够尽快以你的语言为你提供最新的帮助内容。本页面是自动翻译的,可能包含语法错误或不准确之处。我们的目的是使此内容能对你有所帮助。可以在本页面底部告诉我们此信息是否对你有帮助吗?请在此处查看本文的英文版本以便参考。

数据模型允许您集成多个表中的数据, 从而在 Excel 工作簿中有效地生成关系数据源。在 Excel 中, 数据模型透明地使用, 用于提供数据透视表和数据透视图中使用的表格数据。数据模型作为字段列表中的表的集合进行可视化处理, 大多数情况下, 您甚至不会知道它在那里。

在开始使用数据模型之前, 你需要获取一些数据。为此, 我们将使用 "获取和转换" (Power Query) 体验, 这样你可能需要执行一步操作并观看视频, 或者关注我们的学习指南, 了解 "获取和转换" 以及 "Power Pivot"。

Power Pivot 在哪里?

"获取和转换" (Power Query) 在哪里?

  • excel 2016 & excel for Office 365 -"获取和转换" (Power Query) 已与 "数据" 选项卡上的 Excel 集成。

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

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

入门

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

  1. 在 excel 2016 和 excel for Office 365 中, 使用 "数据" > "获取和转换数据" > "获取数据" 以从任意数量的外部数据源导入数据, 例如文本文件、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 技术社区中咨询专家,在解答社区获得支持,或在 Excel User Voice 上建议新功能或功能改进。

另请参阅

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

查询编辑器简介 (Power Query)

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

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

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

数据模型中表之间的关系

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

此信息是否有帮助?

谢谢您的反馈!

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

×