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

时间: 2023-01-26

摘要：我们提供了一系列教程，旨在让你熟悉、习惯地使用 Excel 及其内置的数据混合和分析功能，本教程是该系列中的第一个。这些教程从零开始构建 Excel 工作簿并进行优化，构建数据模型，然后使用 Power View 创建精彩的交互式报表。这些教程旨在展示 Excel、数据透视表、Power Pivot 和 Power View 中的 Microsoft 商业智能特性和功能。

注意: 本文介绍 Excel 2013 中的数据模型。但是，Excel 2013 中引入的相同数据建模和 Power Pivot 功能也适用于 Excel 2016。

在这些教程中，您将了解如何在 Excel 中导入和浏览数据、使用 Power Pivot 构建和优化数据模型，以及如何使用 Power View 创建交互式报表，此报表可以发布、保护和共享。

本系列包含以下几个教程：

将数据导入到 Excel 2013 并创建数据模型

使用 Excel、Power Pivot 和 DAX 扩展数据模型关系

创建基于地图的 Power View 报表

整合 Internet 数据并设置 Power View 报表默认值

PowerPivot 帮助

创建美观的 Power View 报表 – 第 2 部分

在本教程中，您将从一个空白的 Excel 工作簿开始。

本教程包括以下几部分内容：

从数据库导入数据

从电子表格导入数据

使用复制和粘贴导入数据

在导入的数据之间创建关系

检查点和测验

本教程最后提供了一个测验，以检验您的学习成效。

本教程系列使用描述奥运会奖牌、主办国家/地区和各种奥运会运动项目的数据。我们建议您依次学习每个教程。此外，教程使用启用 Power Pivot 的 Excel 2013。有关 Excel 2013 的详细信息，请单击此处。有关启用 Power Pivot 的指导信息，请单击此处。

从数据库导入数据

我们使用一个空白工作簿开始此教程。本节的目标是连接到外部数据源，并将数据导入 Excel 中供进一步分析。

首先，我们从 Internet 下载一些数据。这些数据描述奥运会奖牌情况，是一个 Microsoft Access 数据库。

单击以下链接可下载我们在本教程系列中使用的文件。将这四个文件中的每一个都下载到易于访问的位置，例如“下载”或“我的文档”，或者下载到自己创建的新文件夹：> OlympicMedals.accdb Access 数据库> OlympicSports.xlsx Excel 工作簿> Population.xlsx Excel 工作簿> DiscImage_table.xlsx Excel 工作簿

在 Excel 2013 中，打开一个空白工作簿。

单击“数据”>“获取外部数据”>“自 Access”。功能区会基于工作簿的宽度动态调整，因此功能区上的命令可能看起来与下面的屏幕稍有不同。第一个屏幕显示工作簿很宽时的功能区，第二个图像显示调整工作簿大小后工作簿界面仅占屏幕一部分时的情形。

选择下载的 OlympicMedals.accdb 文件，然后单击“打开”。将出现下面的“选择表格”窗口，其中显示在数据库中找到的表格。数据库中的表格类似于 Excel 中的工作表或表。选中“支持选择多个表”框，选择所有表格。然后单击“确定”。

将显示“导入数据”窗口。

注意: 请注意窗口底部的复选框，该复选框允许将此数据添加到数据模型，如以下屏幕所示。当你同时导入或处理两个或多个表时，将自动创建数据模型。数据模型会集成这些表，支持使用数据透视表、Power Pivot 和 Power View 进行大量分析。从数据库中导入表时，将使用这些表之间的现有数据库关系在 Excel 中创建数据模型。数据模型在 Excel 中是透明的，但你可以使用 Power Pivot 外接程序直接查看和修改它。本教程稍后将更详细地讨论数据模型。

选择“数据透视表”选项（这会将表格导入 Excel 中并准备数据透视表以便分析导入的表格），然后单击“确定”。

导入数据后，将使用所导入的表格创建数据透视表。

将数据导入到 Excel 且自动创建数据模型后，即可浏览数据。

使用数据透视表浏览数据

使用数据透视表浏览导入的数据非常容易。在数据透视表中，您可将表（与刚从 Access 数据库导入的表相似）中的字段（与 Excel 中的列相似）拖动到数据透视表中的其他区域，以调整数据的显示方式。数据透视表具有四个区域：“筛选”、“列”、“行”和“数值”。

可能需要做些尝试才能确定应将字段拖动到哪个区域。您可以根据需要拖动表中任意数量的字段，直到数据透视表按您需要的方式显示数据。您可以通过将字段拖动到数据透视表的不同区域进行随意浏览；当您排列数据透视表中的字段时，基础数据不会受到影响。

现在我们在数据透视表中浏览奥运会奖牌数据，从按比赛项目、奖牌类型和运动员所属国家/地区组织的奥运会奖牌获得者开始。

在“数据透视表字段”中，通过单击“奖牌”表旁边的箭头展开它。在展开的“奖牌”表中找到 NOC_CountryRegion 字段，将其拖动到“列”区域。 NOC 表示国家奥委会，是国家或地区的组织单位。

接下来，从“分项”表中将“分项”拖动到“行”区域。

我们对“分项”进行筛选，以仅显示五项运动：射箭、跳水、击剑、花样滑冰和速度滑冰。可以从“数据透视表字段”区域内或从数据透视表本身中的“行标签”筛选器执行此筛选。

单击数据透视表中的任意位置，确保选择了 Excel 数据透视表。在“数据透视表字段”列表中，“分项”表处于展开状态，将鼠标悬停在其“分项”字段上，该字段右侧会显示一个下拉箭头。单击该下拉箭头，单击“(全选)”删除所有选择，然后向下滚动并选择“射箭”、“跳水”、“击剑”、“花样滑冰”和“速度滑冰”。单击“确定”。

或者，在数据透视表的“行标签”部分中，单击数据透视表中“行标签”旁边的下拉列表，单击“(全选)”删除所有选择，然后向下滚动并选择射箭、跳水、击剑、花样滑冰和速度滑冰。单击“确定”。

在“数据透视表字段”中，从“奖牌”表中将“奖牌”拖动到“值”区域。由于“值”必须为数字，因而 Excel 会自动将“奖牌”更改为“奖牌数”。

从“奖牌”表中再次选择“奖牌”并将其拖到“筛选”区域。

我们对数据透视表进行筛选，以仅显示奖牌总数超过 90 枚的那些国家或地区。下面介绍如何操作。

在数据透视表中，单击“列标签”右侧的下拉列表。

选择“值筛选器”，然后选择“大于…”

在最后一个字段中（位于右侧）键入 90。单击“确定”。

数据透视表类似于下面的屏幕。

您现在已经轻而易举地构建了一个包含来自三个不同表中的字段的基本数据透视表。由于表之间已经预先存在关系，因而执行此任务非常简单。由于表关系已存在于源数据库中，并且您在一项操作中导入了所有表格，所以 Excel 可以在其数据模型中重新创建这些表关系。

但是，如果数据来自不同源或者是以后导入的，该怎么办？通常，您可以基于匹配列使用新数据创建关系。在下一步骤中，您将导入其他表，并了解如何创建新关系。

从电子表格导入数据

现在我们从另一个源中导入数据，这次是从现有工作簿中导入，然后指定现有数据和新数据之间的关系。关系让您能够分析 Excel 中的数据集合，利用导入的数据创建有趣的沉浸式可视化效果。

我们首先创建一个空白工作表，然后从 Excel 工作簿中导入数据。

插入新的 Excel 工作表，将其命名为运动。

通过浏览找到包含下载的示例数据文件的文件夹，打开 OlympicSports.xlsx。

在 Sheet1 中选择并复制数据。如果您选择了一个包含数据的单元格，如单元格 A1，您可以按 Ctrl + A 选择所有相邻数据。关闭 OlympicSports.xlsx 工作簿。

在“运动”工作表中，将光标放在单元格 A1 中并粘贴数据。

保持数据处于突出显示状态，按 Ctrl + T 将数据格式化为表。您还可以通过选择“开始 > 套用表格格式”将数据格式化为表。由于数据包含标题，因而可在显示的“创建表”窗口中选择“表包含标题”，如下图所示。将数据格式化为表有许多优点。您可以为表分配一个名称，使其易被识别。您还可以在表之间建立关系，从而支持在数据透视表、Power Pivot 和 Power View 中进行浏览和分析。

为该表命名。在“表格工具 > 设计 >属性”下，找到“表名称”字段并键入“运动”。工作簿类如以下屏幕所示。

保存工作簿。

使用复制和粘贴导入数据

现在我们已从 Excel 工作簿中导入了数据，下面我们从在网页中找到的表导入数据，或者从任何其他源中将数据复制和粘贴到 Excel 中。在以下步骤中，您从一个表中添加奥运会举办城市。

插入新的 Excel 工作表，将其命名为“举办地”。

选择并复制下表（包括表标题在内）。

城市

NOC_CountryRegion

Alpha-2 代码

版本

季节

墨尔本/斯德哥尔摩

AUS

1956

夏季

悉尼

AUS

2000

夏季

因斯布鲁克

AUT

1964

冬季

因斯布鲁克

AUT

1976

冬季

安特卫普

BEL

1920

夏季

安特卫普

BEL

1920

冬季

蒙特利尔

CAN

1976

夏季

普莱西德湖

CAN

1980

冬季

卡尔加里

CAN

1988

冬季

圣莫里茨

SUI

1928

冬季

圣莫里茨

SUI

1948

冬季

北京

CHN

2008

夏季

柏林

GER

1936

夏季

加米施-帕滕基兴

GER

1936

冬季

巴塞罗那

ESP

1992

夏季

赫尔辛基

FIN

1952

夏季

巴黎

FRA

1900

夏季

巴黎

FRA

1924

夏季

沙木尼

FRA

1924

冬季

格勒诺布尔

FRA

1968

冬季

阿尔贝维尔

FRA

1992

冬季

伦敦

GBR

英国

1908

夏季

伦敦

GBR

英国

1908

冬季

伦敦

GBR

英国

1948

夏季

慕尼黑

GER

1972

夏季

雅典

GRC

2004

夏季

柯提纳安培佐

ITA

1956

冬季

罗马

ITA

1960

夏季

都灵

ITA

2006

冬季

东京

JPN

1964

夏季

札幌

JPN

1972

冬季

长野

JPN

1998

冬季

首尔

KOR

1988

夏季

墨西哥城

MEX

1968

夏季

阿姆斯特丹

NED

1928

夏季

奥斯陆

NOR

1952

冬季

利勒哈默尔

NOR

1994

冬季

斯德哥尔摩

SWE

1912

夏季

圣路易斯

USA

1904

夏季

洛杉矶

USA

1932

夏季

普莱西德湖

USA

1932

冬季

斯阔谷

USA

1960

冬季

莫斯科

URS

1980

夏季

洛杉矶

USA

1984

夏季

亚特兰大

USA

1996

夏季

盐湖城

USA

2002

冬季

萨拉热窝

YUG

1984

冬季

在 Excel 中，将光标放在“举办地”工作表的单元格 A1 中并粘贴数据。

将数据格式化为表。如本教程中所述，您可按 Ctrl + T 将数据格式化为表，或从“开始 > 套用表格格式”执行此操作。由于数据包含标题，因而可在显示的“创建表”窗口中选择“表包含标题”。

为该表命名。在“表格工具 > 设计 >属性”下，找到“表名称”字段并键入“举办地”。

选择“版本”列，并从“开始”选项卡中将其格式设置为带 0 位小数的“数字”。

保存工作簿。工作簿如以下屏幕所示。

现在您的 Excel 工作簿中已经有了多个表，您可以创建它们之间的关系。通过创建表之间的关系，您可以组合来自两个表中的数据。

在导入的数据之间创建关系

您可以立即开始从导入的表在数据透视表中使用这些字段。如果 Excel 无法确定如何将字段合并到数据透视表中，就必须使用现有数据模型建立关系。在以下步骤中，您将了解如何在从不同数据源导入的数据之间创建关系。

在 Sheet1 中“数据透视表字段”的顶部，单击“全部”以查看可用表格的完整列表，如以下屏幕所示。

滚动列表以显示刚添加的新表。

展开运动，并选择运动，以将其添加到数据透视表。请注意，Excel 会提示您创建关系，如以下屏幕中所示。

出现此通知的原因是您使用了不属于基础数据模型的表中的字段。将表添加到数据模型的一种方法是创建与数据模型中已存在的某个表的关系。要创建关系，其中一个表中必须有一列包含唯一的、不重复的值。在示例数据中，从数据库导入的“分项”表包含一个具有称为 SportID 的运动代码的字段。这些相同的运动代码在导入的 Excel 数据中显示为字段。让我们来创建关系。

在突出显示的“数据透视表字段”区域中单击“创建… ”，打开“创建关系”对话框，如以下屏幕中所示。

在“表”中，从下拉列表中选择“分项”。

在“列(外来)”中，选择 SportID。

在“相关表”中，选择 Sports。

在“相关列(主要)”中，选择 SportID。

单击“确定”。

数据透视表将更改，以反映新关系。但因为“行”区域中字段的排列问题，数据透视表看上去还是不妥。分项是指定运动下的子类别，但由于我们在“行”区域中将“分项”排在了“运动”上面，所以组织结构不正确。以下屏幕显示了这种不正确的排序。

在“行”区域中，将“运动”移动到“分项”的上面。这样就好多了，数据透视表以您所需的方式显示数据，如以下屏幕中所示。

在后台，Excel 正在构建可以在整个工作簿中的任何数据透视表和数据透视图、Power Pivot 或任何 Power View 报表中使用的数据模型。表关系是数据模型的基础，也是确定导航和计算路径的基础。

在接下来的使用 Excel 2013、Power Pivot 和 DAX 扩展数据模型关系这一教程中，你将以这里学习到的内容为基础构建数据模型，并使用一个称为 Power Pivot 的强大可视化 Excel 外接程序逐步扩展数据模型。此外，你还将学习如何计算表中的列，以及如何使用该计算列，以便将不相关的表添加到数据模型。

检查点和测验

回顾您学习的内容

您现在有了一个包含数据透视表的 Excel 工作簿，通过该数据透视表可访问多个表中的数据，其中有几个表是您单独导入的。您学习了如何从数据库、从另一个 Excel 工作簿进行导入，并学习了如何通过复制数据并将其粘贴到 Excel 中来进行导入。

要使组合使用数据，您必须创建 Excel 用来关联行的表关系。您还学习了将一个表中的列关联到另一个表中的数据，这是创建关系和查找相关行的基础。

您已做好准备，可以学习此系列教程中的下一个教程了。下面是相应的链接：

使用 Excel 2013、Power Pivot 和 DAX 扩展数据模型关系

小测验

希望了解您记住了多少已学知识？这里为您提供了一个机会。以下测验重点强调您在本教程中学到的相关特性、功能或要求。您可以在页面底部找到答案。祝您好运！

问题 1：将导入的数据转换为表为什么重要？

A：不必将它们转换为表，因为所有导入的数据都将自动转换为表。

B：如果将导入的数据转换为表，就会将它们从数据模型中排除。仅当从数据模型中排除后，它们在数据透视表、Power Pivot 和 Power View 中才可用。

C：如果将导入的数据转换为表，就可以将它们包含在数据模型中，使其对数据透视表、Power Pivot 和 Power View 可用。