教程:将数据导入 Excel 中并创建数据模型
发布时间:2022-02-28 05:13:08
摘要: 我们提供了一系列教程,旨在让你熟悉、习惯地使用 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 工作表,将其命名为“举办地”。
选择并复制下表(包括表标题在内)。
墨尔本/斯德哥尔摩
AUS
AS
1956
夏季
悉尼
AUS
AS
2000
夏季
因斯布鲁克
AUT
AT
1964
冬季
因斯布鲁克
AUT
AT
1976
冬季
安特卫普
BEL
BE
1920
夏季
安特卫普
BEL
BE
1920
冬季
蒙特利尔
CAN
CA
1976
夏季
普莱西德湖
CAN
CA
1980
冬季
卡尔加里
CAN
CA
1988
冬季
圣 莫里茨
SUI
SZ
1928
冬季
圣 莫里茨
SUI
SZ
1948
冬季
北京
CHN
CH
2008
夏季
柏林
GER
GM
1936
夏季
加米施-帕滕基兴
GER
GM
1936
冬季
巴塞罗那
ESP
SP
1992
夏季
赫尔辛基
FIN
FI
1952
夏季
巴黎
FRA
FR
1900
夏季
巴黎
FRA
FR
1924
夏季
沙木尼
FRA
FR
1924
冬季
格勒诺布尔
FRA
FR
1968
冬季
阿尔贝维尔
FRA
FR
1992
冬季
伦敦
GBR
英国
1908
夏季
伦敦
GBR
英国
1908
冬季
伦敦
GBR
英国
1948
夏季
慕尼黑
GER
DE
1972
夏季
雅典
GRC
GR
2004
夏季
柯提纳安培佐
ITA
IT
1956
冬季
罗马
ITA
IT
1960
夏季
都灵
ITA
IT
2006
冬季
东京
JPN
JA
1964
夏季
札幌
JPN
JA
1972
冬季
长野
JPN
JA
1998
冬季
首尔
KOR
KS
1988
夏季
墨西哥城
MEX
MX
1968
夏季
阿姆斯特丹
NED
NL
1928
夏季
奥斯陆
NOR
NO
1952
冬季
利勒哈默尔
NOR
NO
1994
冬季
斯德哥尔摩
SWE
SW
1912
夏季
圣路易斯
USA
US
1904
夏季
洛杉矶
USA
US
1932
夏季
普莱西德湖
USA
US
1932
冬季
斯阔谷
USA
US
1960
冬季
莫斯科
URS
RU
1980
夏季
洛杉矶
USA
US
1984
夏季
亚特兰大
USA
US
1996
夏季
盐湖城
USA
US
2002
冬季
萨拉热窝
YUG
YU
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 可用。
D:无法将导入的数据转换为表。
问题 2: 以下哪些数据源可以导入 Excel 中,并包含在数据模型中?
A:Access 数据库以及许多其他数据库。
B:现有 Excel 文件。
C:复制和粘贴到 Excel 中且格式化为表的任何内容,包括网站上的数据表、文档或其他任何可以粘贴到 Excel 中的内容。
D:以上全部
问题 3: 在数据透视表中,在四个“数据透视表字段”区域中重排字段时会发生什么情况?
A:什么也不会发生 – 将字段放置到数据透视表字段区域中后,无法重排字段。
B:数据透视表格式更改以反映布局,但基础数据并不会受到影响。
C:数据透视表格式更改以反映布局,并且所有基础数据永久改变。
D:基础数据更改,从而得到新数据集。
问题 4: 在表之间创建关系时,必需的条件是什么?
A:两个表都不能有任何列包含唯一的、非重复的值。
B:一个表不能是 Excel 工作簿的一部分。
C:列一定不能转换为表。
D:以上都不正确。
测验答案
正确答案:C
正确答案:D
正确答案:B
正确答案:D
注意: 本系列教程中的数据和图像基于以下内容:
奥运会数据集由 Guardian News & Media Ltd. 提供
国旗图像由 CIA Factbook (cia.gov) 提供
人口数据由世界银行 (worldbank.org) 提供
奥运会比赛图标由 Thadius 856 和 Parutakupiu 提供
城市 | NOC_CountryRegion | Alpha-2 代码 | 版本 | 季节 |
---|
猜你喜欢
- 在Excel中怎么从一个文本的第一个字符开始截取指定数目的字符?很多朋友都不是很清楚,所以今天小编就为大家详细介绍一下,不会的朋友可以参考本
- 选择分散 - 右键 - 添加趋势线 - 选择公式类型(线性,指数,多项式等) - 选择“显示公式”和“显示R平方值” - 出现公式R2和R2
- 很多小伙伴经常会使用Word来编辑一些文档,而有时候在打开一些文档之时会出现损坏的情况,无法正常的去开启,那么我们面对这样的情况时应该要如何
- Word字体怎么格式化?1、调整字体设置。在功能区上,您会看到字体和大小的下拉菜单。要更改文本,您首先必须选择要使用的文本。您可以选择单个字
- 如何在Word 2013中格式化文本字体,大小和颜色?Word 2013文档中的文本使用某种样式的字体显示,称为字体或字体。Word带有数十
- word打字后面的字会消失,覆盖后面的文字这种情况,出现这种情况一般是因为insert键或Word设置问题。可以看一下word最下方是插入还
- Win10强制更新怎么解决呢?微软在一定的时间都会推出安全补丁,微软强制安装所有更新,但并不是所有更新都必须安装,个别更新还可能导致系统异常
- 下图左侧A2:C6单元格区域为数据源,右侧为根据数据源制作的简易 * 图,其中系列"计划"用折线图显示,系列"实
- 我们在Word文档中处理文档内容时,都会进行基础的排版设置,比如进行文档的分段,设置首行缩进两字符,设置对齐方式,添加项目符号,编号,调节行
- Excel是常用的办公软件,我们使用它做表格的时候,可能需要制作表头,那么怎么用Excel绘制三线表头呢,下面给大家带来具体绘制步骤大家都知
- 系统备份是一个非常好用非常实用的功能,每当我进行升级系统或是对电脑有一些重大的改动的时候就需要对系统进行备份,这样就保证了系统数据的安全。有
- EXCEL更改垂直轴的数据该怎么改呢,下面就为大家介绍一下方法,希望对家有所帮助操作步骤如下:双击纵坐标值。在出来的对话框里的刻度里的最小值
- 每逢新春佳节,家家户户都要在屋门上、墙壁上、门楣上贴上大大小小的“福”字。春节贴“福”字,是我国民间由来已久的风俗。据《梦梁录》记载:“岁旦
- 貌似别人的东西都是好的,偶然发现别人制作的幻灯片,很有感触,里面的某些效果还是不错的,值得学习。一直吸引我的效果“是一直闪烁的文字”,这个效
- 每年2月和3月都是求职招聘最繁忙的季节,作为一个初入职场的新人,在实际工作中难免会遇到很多问题。除了人际关系、沟通方式、工作态度等方面,职场
- win10系统是一个非常优秀的计算机系统,在使用win10系统的过程中一般不会有任何影响使用的问题!但各种各样的原因总会出现!最近,许多朋友
- Excel有许多的功能,那么如何在excel中插入公式?下面小编就为大家介绍在excel中写公式方法,不懂的朋友可以参考本文,希望能对大家有
- type-c数据线能有什么缺点呢?从以前出门要带一大堆数据线,到如今几乎一条type-c走天下,真的就剩苹果这临门一脚。要说type-c数据
- Excel怎么做Xbar-R控制图?SPC控制图是对过程质量加以测定、记录从而进行控制管理的一种用科学方法设计的图,是质量控制的行之有效的手
- 我们平常办公时,经常需要将撰写的Word文档发给同事或领导批阅和修改。正常的反馈意见和修改都是用批注和修订功能,因此文件中具有所有此类标记,