首页 > Excel专区 > Excel教程 >

在 Excel 中创建数据模型

Excel教程 2022-02-03 17:56:42

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

在开始使用数据模型之前，需要获取一些数据。 为此，我们将使用 “获取 & 转换（Power Query）” 体验，因此你可能需要返回并观看视频，或者关注我们的学习指南，了解 & 转换和 Power Pivot。

先决条件

Power Pivot 在哪里？

  • Excel 2016 & 适用于 Office 365 的 excel -Power Pivot 包含在功能区中。

  • Excel 2013 -Power Pivot 是 excel 2013 的 Office 专业增强版的一部分，但默认情况下不启用。 了解有关启动 Excel 2013 的 Power Pivot 加载项的详细信息。

  • Excel 2010 -下载 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 中并创建数据模型

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

数据模型中表之间的关系


标签： 关系创建字段数据数据模型excel教程

上一篇：Power Pivot 中的数据分析表达式 (DAX) 下一篇：更改图表中的轴标签

相关文章

office教程网 Copyright © 2016-2020 https://www.office9.cn. Some Rights Reserved.