如何在 Excel 中创建数据模型：完整指南

数据模型使我们能够从多个来源加载、集成并分析数据，从而以更动态、更具洞察力的方式审视数据。 

在本教程的各个部分中，我们将探索在 Excel 中创建数据模型的多种方法，包括使用现有表、导入新表，以及利用 Power Query、Power Pivot 和数据透视图。   

开始吧！

如何在 Excel 中创建数据模型

在 Excel 中创建数据模型的一般步骤如下：

1. 将数据格式化为表。
2. 在各表之间创建关系。
3. 使用 Power Query 加载并转换数据。
4. 使用 Power Pivot 进行高级数据建模。
5. 利用数据透视表和数据透视图来可视化数据。

何时在 Excel 中使用数据模型

在多种常见情境下，在 Excel 中创建数据模型尤为有用，包括数据清洗、数据集成与转换，以及复杂数据分析。以下是一些主要益处。

• 便于数据清洗：数据模型有助于清洗与标准化不同数据集，保持一致性与统一性。这在处理来自不同来源、需要融合与预处理的数据时至关重要。 
• 实现数据集成与转换：借助数据模型，可轻松集成与转换来自其他任何来源的数据，向数据使用者提供便于处理与分析的统一视图。
• 增强数据可视化：数据模型支持开发高级数据可视化，例如数据透视图和 Power View 报表。 
• 提升数据安全与管理：通过数据模型，可以实现对数据不同部分的受控访问。您可以更轻松地在模型内管理权限与数据，降低未授权访问和数据泄露的风险。
• 简化数据报告：借助数据模型，可以将数据报告实现全面自动化，从而简化报告与决策流程。当有新数据加入时，报告与仪表板也可动态调整，为所有相关方提供最新信息。
• 处理大型数据集：数据模型可在 Excel 中处理大规模数据集，突破传统工作表的限制。
• 支持复杂数据分析：在多张数据表之间创建关系的功能，使您能够开展常规 Excel 函数难以完成的高级数据分析。

如何通过导入数据创建数据模型

本节将演示如何通过导入示例数据集在 Excel 中创建数据模型。我们将使用两个示例数据集：Order 和 Product。我创建了一个名为 Excel-Data-Model 的公共 GitHub 仓库，您可以下载数据集并跟随本教程操作。

第一步是将数据导入 Excel。先打开一个新的 Excel 工作簿，点击“数据（Data）”选项卡。在“获取数据（Get Data）”下，选择“获取数据（Get Data）” > “自文件（From File）” > “自 Excel 工作簿（From Excel Workbook）”。

将数据导入 Excel。作者供图

选择 Order.xlsx 工作簿，并点击“导入（Import）”。这将打开“导航器（Navigator）”窗口：选择 Sheet1 表。

Excel 的导航器窗口。作者供图

单击“加载到（Load To）”选项，打开“导入数据（Import Data）”窗口。在对话框中，选择 Only Create Connection 并勾选 Add this data to the Data Model。点击“确定（OK）”。

 

导入数据窗口。作者供图

重复上述步骤加载 Product 工作表。完成这两个任务后，您的 Excel 工作簿将如下所示：

 

“查询与连接”窗格。作者供图

恭喜！您已将两个工作表导入到 Excel 文件中。下一步是建立导入表之间的关系。为此，转到“数据工具（Data Tools）”选项卡并单击“关系（Relationships）”。

数据工具选项卡。作者供图 

将会弹出一个新窗口。

管理关系窗口。作者供图

点击“新建（New）”以创建关系。会打开“创建关系（Create Relationship）”弹窗。按下图所示定义关系。 

创建关系窗口。作者供图

点击“确定（OK）”。关系已建立，您可以在“连接（Connections）”选项卡中确认。

“查询与连接”窗格。作者供图

下一步是创建数据透视表。转到“插入（Insert）”选项卡并单击“数据透视表（PivotTable）”。然后在“数据透视表”对话框中，选择“使用此工作簿的数据模型（Use this workbook’s Data Model）”。

数据透视表对话框。作者供图

选择数据透视表的位置。点击“确定（OK）”。数据透视表将打开，您可以在“数据透视表字段（PivotTable Fields）”下看到各字段。

数据透视表字段。作者供图

构建数据透视表，执行以下步骤。

在“数据透视表字段”窗格中，将字段拖到“行（Rows）”、“列（Columns）”、“值（Values）”和“筛选器（Filters）”区域：

• 行（Rows）：ProductName
• 值（Values）：Quantity

生成的数据透视表如下所示：

数据透视表字段窗格。作者供图

您可以根据需要自定义并设置数据透视表格式。

如何使用 Power Query 创建数据模型 

本节我们将使用名为 Sample_Data 的 Excel 文件中的数据。该文件也可通过上一节提供的链接获取。工作表 Data 中有两个表，包含如下记录。

待连接的 Excel 表。作者供图

我们将在此现有 Excel 表中构建数据模型。首先，在 Customer ID and Name 表中选择任意单元格，然后按 Ctrl + T。

选择单元格。作者供图

这将打开如下所示的“创建表（Create Table）”弹窗。

创建表弹窗。作者供图

 点击“确定（OK）”，表即创建完成。 

Excel 中的数据透视表。作者供图

现在给该表命名。进入“表设计（Table Design）”上下文窗口，在“表名称（Table Name）”下，将表重命名为 CustomerPQ。您也可以使用其他名称。 

表设计上下文窗口。作者供图

现在该表已命名为 CustomerPQ。

表设计上下文窗口。作者供图

对第二个表重复该过程。创建表后，将其名称设为 PurchasePQ。

为数据模型准备好的两个表。作者供图

现在，活动表为 PurchasePQ 表。前往“数据（Data）”，选择“自表/区域（From Table/Range）”。将出现以下页面。

打开 Power Query 编辑器。作者供图

要创建连接并将 Purchase PQ 表添加到数据模型，点击“关闭并加载（Close & Load）”的下拉菜单，选择“关闭并加载到…（Close & Load To…）”  

关闭 Power Query 编辑器。作者供图

这将打开一个“导入数据（Import Data）”窗口，需要按以下方式填写：

导入数据窗口。作者供图 

完成上述步骤后，您将看到“查询与连接（Queries & Connections）”框。

“查询与连接”框。作者供图

现在，将 CustomerPQ 表添加到连接中。为此，点击该表任意单元格，前往“数据（Data）”并选择“自表/区域（From Table/Range）”。将出现以下页面。

已为 Excel 数据模型准备好的表。作者供图

重复为 PurchasePQ 表执行的过程，并按下图填写“导入数据（Import Data）”窗口：

导入数据窗口。作者供图 

 点击“确定（OK）”。此时“查询与连接（Queries & Connections）”选项卡将显示两个表。

查询与连接。作者供图

现在可以建立关系了。在“数据（Data）”选项卡内，转到“数据工具（Data Tools）”部分，单击“管理数据模型（Manage Data Model）”。

数据工具。作者供图

这将打开 Power Pivot for Excel。

Power Pivot for Excel。作者供图

现在要在两个表之间创建关系，点击“图表视图（Diagram View）”。

视图窗格。作者供图

 将打开如下所示的界面。 

图表视图。作者供图

您可以看到有两个表，但尚未连接。要创建关系，请转到“设计（Design）”选项卡并点击“创建关系（Create Relationship）”。

设计选项卡。作者供图

将弹出新窗口。按下图所示填写详细信息：

创建关系窗口。作者供图

点击“确定（OK）”。您会注意到关系已建立。

窗口显示关系已建立。作者供图 

通过保存按钮或 Ctrl + S 保存 Power Pivot，并关闭该选项卡。随后显示如下输出。

“启用内容”弹窗。作者供图

点击“启用内容（Enable Content）”以创建连接。现在连接已建立，我们可以使用数据透视表了。为此，进入“插入（Insert）”并点击“数据透视表（PivotTable）”选项，选择“来自数据模型（From Data Model）”。

数据透视表窗格。作者供图

您会看到如下弹窗：

“来自数据模型”的数据透视表选项卡。作者供图

点击“新工作表（New Worksheet）”并单击“确定（OK）”。会创建一个名为 Sheet1 的新工作表。

数据透视表工作表。作者供图 

在“数据透视表字段（PivotTable Fields）”中，您可以找到这些表。

“数据透视表字段”下的标签。作者供图

在 CustomerPQ 表中选择 Customer Name。接着在 PurchasePQ 下选择 Purchase。“数据透视表字段”将如下所示：

在“数据透视表字段”中选择项目。作者供图 

这将在工作表中生成数据透视表，效果如下：

数据透视表结果。作者供图

搞定！通过这种方式，您可以使用 Power Query 创建数据模型，并使用 Power Pivot 创建汇总报表。

如何直接在 Power Pivot 中创建数据模型

本节您将使用 Power Pivot 创建数据模型，并准备汇总报表。 

首先，转到我们上文所述数据中的 Power Pivot 工作表，为两组记录创建表。

待使用的记录。作者供图 

我们已在前文演示如何创建表，请按相同步骤创建这两个表。

唯一的不同是，我们将为这些表设置新名称，以便与上一节区分： 

• 将第一个表命名为 CustomerPP。
• 将第二个表命名为 PurchasePP。

来自记录的 数据表。作者供图

两张表已创建，下一步是将其添加到数据模型。为此，前往Power Pivot，选择“添加到数据模型（Add to Data Model）”。

在 PowerPivot 下添加到数据模型。作者供图

这将打开一个 Power Pivot for Excel 弹窗，如下所示。

Power Pivot for Excel 弹窗。作者供图 

 

保存并关闭该窗口。对 CustomerPP 表重复相同过程，将其添加到数据模型。

Power Pivot for Excel 弹窗。作者供图

将这些表添加到数据模型后，您可以使用 Power Pivot 建立它们之间的关系。为此，点击“图表视图（Diagram View）”。

图表视图。作者供图

接着，进入“设计（Design）”选项卡并点击“创建关系（Create Relationship）”。

“创建关系”选项卡。作者供图

将弹出新窗口。按下图所示填写详细信息：

在两张表之间创建关系。作者供图

 点击“确定（OK）”。您会注意到关系已建立。

 

关系已建立。作者供图

从这里开始，创建数据透视表就很容易了，方法与上一节相同。最终输出应如下图所示。

数据透视表汇总。作者供图

按照上述步骤，您可以使用 Power Pivot 创建数据模型并进行汇总分析。

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

创建好数据透视表后，插入数据透视图就很简单了。选中数据透视表（Sheet 1），前往“插入（Insert）”选项卡。

数据透视表汇总。作者供图

点击“数据透视图（PivotChart）”，选择图表类型并点击“确定（OK）”。

插入图表选项。作者供图

这将创建如下数据透视图。

数据透视图可视化。作者供图

您可以通过以下方式自定义数据透视图：

• 调整图表元素，如标题、图例与轴标签；以及
• 设置格式以提升图表的外观与可读性。

很好，您已使用数据透视表构建了首个可视化。

常见的 Excel 数据模型问题与解答

以下是与 Excel 数据模型常见的一些问题与处理方法：

如果数据模型问题导致 Microsoft Excel 无法打开工作簿，我该怎么办？

该问题可能由于数据模型损坏或兼容性问题引起。可按以下步骤解决：

1. 以安全模式打开：启动 Excel 时按住 Ctrl 键以安全模式打开，有时可绕过问题。
2. 检查更新：前往“文件（File） > 帐户（Account） > 更新选项（Update Options）”确保 Excel 为最新版本。
3. 修复 Office：使用 Office 修复工具。进入“控制面板（Control Panel） > 程序和功能（Programs and Features）”，选择您的 Office 安装并点击“更改（Change） > 修复（Repair）”。
4. 禁用加载项：前往“文件（File） > 选项（Options） > 加载项（Add-ins）”并禁用所有加载项。重启 Excel 后重试打开工作簿。
5. 提取数据：如果仍无法打开工作簿，尝试使用数据恢复工具或联系 Microsoft 支持以获取高级协助。

如何排查数据模型中的错误？

要排查数据模型中的错误：

1. 检查数据类型：确保用于建立关系的列的数据类型一致。
2. 验证关系：确认所有关系均正确定义。
3. 审查计算：检查计算列与度量值是否存在错误。
4. 刷新数据：确保所有数据连接均已更新。
5. 查看错误信息：在 Power Pivot 窗口中查看任何错误消息，以定位具体问题。

遵循这些步骤，您即可在 Excel 中高效管理与排查数据模型问题，确保数据分析顺畅高效。Microsoft 支持也是排障的有用资源。 

结语

感谢您完成本教程！在掌握 Excel 数据分析的旅程中，您已学习到一个重要概念。创建数据模型将显著提升您及您所在组织的数据分析能力，也将提高您管理不同数据表与记录的效率。 

如需进一步学习，建议探索以下资源：   

• Introduction to Excel：一份帮助您入门 Excel 的综合指南，涵盖基本功能与特性。
• Data Analysis in Excel：学习 Excel 中可用的多种数据分析技术与工具，提升您的分析技能。
• Excel Fundamentals：涵盖 Excel 基础概念与操作的课程，助您夯实基础。
• Data Visualization in Excel：探索如何使用 Excel 内置工具创建有影响力且交互性强的可视化。
• Introduction to Power Query in Excel：了解如何在 Excel 中使用 Power Query 高效导入、清洗与转换数据。