Excel Data Analysis 简明教程

Importing Data into Excel

你可能需要使用来自不同来源的数据进行分析。在 Excel 中,你可以从不同的数据源导入数据。部分数据源如下:

  1. Microsoft Access Database

  2. Web Page

  3. Text File

  4. SQL Server Table

  5. SQL Server Analysis Cube

  6. XML File

你可以从数据库同时导入任意数量的表。

Importing Data from Microsoft Access Database

我们将学习如何从 MS Access 数据库导入数据。按照以下步骤进行操作:

Step 1 − 在 Excel 中打开一个新的空白工作簿。

Step 2 − 点击功能区上的“数据”选项卡。

Step 3 − 在“获取外部数据”组中点击 From Access 。将出现 Select Data Source 对话框。

from access

Step 4 − 选择要导入的 Access 数据库文件。Access 数据库文件的扩展名为 .accdb。

select access database

将出现“选择表”对话框,显示 Access 数据库中找到的表。你可以一次性导入数据库中的所有表,也可以仅根据数据分析需求导入选定的表。

select table dialog box

Step 5 − 选中“启用多表选择”框并选择所有表。

enable selection

Step 6 − 点击“确定”。将出现 Import Data 对话框。

import data dialog box

正如你观察到的,你有以下选项来查看你导入到工作簿中的数据:

  1. Table

  2. PivotTable Report

  3. PivotChart

  4. Power View Report

你还有一个选项 - only create connection 。此外,默认选中“数据透视表报告”。

Excel 也允许你将数据放在工作簿中 −

  1. Existing worksheet

  2. New worksheet

你将会发现另一个已选择但已禁用的复选框 – Add this data to the Data Model 。每当你将数据表导入到工作簿时,它们都会自动添加到工作簿中的数据模型中。你将在后续章节中了解到更多关于数据模型的信息。

你可以尝试其中的任何一个选项来查看你正在导入的数据,并检查数据如何在工作簿中显示 −

  1. 如果你选择 Table ,现有工作表选项会禁用, New worksheet 选项会选中,并且 Excel 会创建和你从数据库中导入的表格数量一样多的工作表。Excel 表格会显示在这些工作表中。

  2. 如果你选择 PivotTable Report ,Excel 会将表格导入到工作簿中,并且创建空数据透视表以分析导入表格中的数据。你可以选择在现有工作表或新工作表中创建数据透视表。导入数据表所用的 Excel 表格不会出现在工作簿中。但是,你可以在数据透视表字段列表中找到所有数据表,以及每个表中的字段。

  3. 如果你选择 PivotChart ,Excel 会将表格导入到工作簿中,并且创建空数据透视图表以显示导入表格中的数据。你可以选择在现有工作表或新工作表中创建数据透视图表。导入数据表所用的 Excel 表格不会出现在工作簿中。但是,你可以在数据透视图表字段列表中找到所有数据表,以及每个表中的字段。

  4. 如果你选择 Power View Report ,Excel 会将表格导入到工作簿中,并且在新工作表中创建一个 Power View 报表。你将在后续章节中了解如何使用 Power View 报表分析数据。导入数据表所用的 Excel 表格不会出现在工作簿中。但是,你可以在 Power View 报表字段列表中找到所有数据表,以及每个表中的字段。

  5. 如果你选择 Only Create Connection 选项,会在数据库和工作簿之间建立数据连接。工作簿中不会显示任何表格或报表。但是,默认情况下会将导入的表格添加到工作簿中的数据模型中。你需要根据导入数据以进行数据分析的意图选择其中任何一种选项。正如你上面观察到的,无论你选择哪种选项,数据都会导入并添加到工作簿中的数据模型中。

Importing Data from a Web Page

有时,你可能必须使用网站上刷新的数据。你可以将网站上的表格中的数据导入到 Excel 中。

Step 1 − 在 Excel 中打开一个新的空白工作簿。

Step 2 − 点击功能区上的“数据”选项卡。

Step 3 − 单击 Get External Data 组中的 From WebNew Web Query 对话框将出现。

new web query

Step 4 − 在地址旁边的框中输入你想要从中导入数据网站的网址,然后单击转到。

enter url

Step 5 − 网站上的数据将会出现。可以导入的表格数据旁边会有黄色箭头图标。

data appears

Step 6 − 单击黄色图标选择你想要导入的数据。这会将黄色图标变成带有对勾的绿色方框,如下面的屏幕截图所示。

selected area

Step 7 − 选择想要导入的内容后单击导入按钮。

click import button

Import Data 对话框将出现。

import data dialog box appears

Step 8 − 指定你想要放置数据的位置,然后单击确定。

Step 9 − 整理数据以作进一步分析和/或展示。

arrange data

Copy-pasting data from web

从网页获取数据的另一种方法是复制并粘贴所需数据。

Step 1 − 插入新工作表。

Step 2 − 复制网页中的数据并将其粘贴到工作表中。

Step 3 − 使用粘贴的数据创建一个表格。

copypasting data

Importing Data from a Text File

如果您在 .txt.csv.prn 文件中有数据,则可以将数据从这些文件中导入,并将它们视为文本文件。按照以下步骤操作 −

Step 1 − 在 Excel 中打开一个新工作表。

Step 2 − 点击功能区上的“数据”选项卡。

Step 3 − 单击获取外部数据组中的 From TextImport Text File 对话框随即出现。

importing data from text file

您可以看到接受 .prn, .txt and .csv 扩展名文本文件。

Step 4 − 选择文件。所选文件名出现在文件名框中。打开按钮将更改为导入按钮。

select file

Step 5 − 单击导入按钮。 Text Import Wizard – Step 1 of 3 对话框随即出现。

Step 6 − 单击选项 Delimited 以下选择文件类型,然后单击下一步。

delimited

Text Import Wizard – Step 2 of 3 对话框随即出现。

Step 7 − 在分隔符下选择 Other

Step 8 − 在其他旁边的框中,键入 |(您正在导入文本文件中的分隔符)。

Step 9 − 单击下一步。

text import wizard

Text Import Wizard – Step 3 of 3 对话框随即出现。

Step 10 − 在此对话框中,您可以为每列设置列数据格式。

set column data format

Step 11 − 完成列的数据格式化设置之后,单击完成。 Import Data 对话框随即出现。

complete data formatting

您将观察到以下内容 −

  1. 表格已选择用于显示,并且显示为灰色。在此情况下,表格是您拥有的唯一视图选项。

  2. 您可以将数据放在现有工作表或新建工作表中。

  3. 可以选择或不选择复选框“将此数据添加到数据模型”。

  4. 在做出选择后单击“确定”。

数据显示在你指定的电子表格中。已将数据从文本文件导入到 Excel 工作簿中。

Importing Data from another Workbook

你可能必须使用其他 Excel 工作簿中的数据进行数据分析,但其他工作簿是由其他人来维护的。

若要获取来自其他工作簿的最新数据,请与该工作簿建立数据连接。

Step 1 - 单击功能区上“连接”组中的 DATA > Connections

出现 Workbook Connections 对话框。

workbook connections

Step 2 - 单击工作簿连接对话框中的“添加”按钮。出现 Existing Connections 对话框。

existing connections

Step 3 - 单击 Browse for More… 按钮。出现 Select Data Source 对话框。

browse more

Step 4 - 单击 New Source button 。出现 Data Connection Wizard 对话框。

data connection wizard

Step 5 - 从数据源列表中选择 Other/Advanced ,然后单击“下一步”。出现数据链接属性对话框。

data link properties

Step 6 - 设置数据链接属性如下:

  1. Click the Connection tab.

  2. 单击“使用数据源名称”。

  3. 单击向下箭头并从下拉列表中选择 Excel Files

  4. Click OK.

出现 Select Workbook 对话框。

select workbook

Step 7 - 浏览到位于要导入工作簿的位置。单击“确定”。

出现具有“选择数据库和表”的 Data Connection Wizard 对话框。

Note - 在此情况下,Excel 会将每个要导入的工作表都当作一个表。表名称将是工作表名称。因此,为了使表名称具有实际意义,请根据需要对工作表进行命名/重命名。

data connection wizard dialog box

Step 8 - 单击“下一步”。出现具有“保存数据连接文件并完成”的 Data Connection Wizard 对话框。

save and finish

Step 9 - 单击“完成”按钮。 Select Table 对话框随即出现。

select table dialog box appears

正如你所见,名称是作为 TABLE 类型导入的工作表名称。单击“确定”。

会建立与你所选工作簿的数据连接。

Importing Data from Other Sources

Excel 为你提供了选择其他各种数据源的选项。你可以按几个步骤从中导入数据。

Step 1 − 在 Excel 中打开一个新的空白工作簿。

Step 2 − 点击功能区上的“数据”选项卡。

Step 3 - 单击“获取外部数据”组中的 From Other Sources

importing data from other sources

带有各种数据源的下拉列表随即出现。

dropdown

你可以将任何一种这些数据源中的数据导入到 Excel 中。

Importing Data using an Existing Connection

在上一部分中,你已建立了与工作簿的数据连接。

现在,你可以使用现有连接来导入数据。

Step 1 - 单击功能区中的“数据”选项卡。

Step 2 - 单击“获取外部数据”组中的 Existing Connections 。“现有连接”对话框随即出现。

Step 3 - 选择你希望从中导入数据且单击“打开”的连接。

importing data using existing connection

Renaming the Data Connections

如果你工作簿中的数据连接具有便于理解和查找的含义丰富的名称,这将非常有用。

Step 1 - 转到功能区上的 DATA > ConnectionsWorkbook Connections 对话框随即出现。

Step 2 - 选择希望重命名的连接,并单击“属性”。

renaming data connections

Connection Properties 对话框随即出现。现有的名称显示在“连接名称”框中 -

connection properties

Step 3 - 编辑“连接名称”,并单击“确定”。数据连接将具有你所给出的新名称。

Refreshing an External Data Connection

如你在上述部分中所见,当你将你的 Excel 工作簿连接到外部数据源时,你会希望保留工作簿中的数据,并且反映随时间对外部数据源所做的更改。

你可以通过刷新你已建立到这些数据源的数据连接来执行此操作。每当你刷新数据连接时,你将看到来自该数据源的最新数据更改,包括任何新内容、修改内容或已删除的内容。

你可以只刷新工作簿中选定的数据或一次性刷新所有数据连接。

Step 1 - 单击功能区中的“数据”选项卡。

Step 2 − 单击连接组中的 Refresh All

refreshing external data connection

如你所见,下拉列表中有两个命令——刷新和全部刷新。

  1. 如果你单击 Refresh ,工作簿中选定的数据就得到更新。

  2. 如果你单击 Refresh All ,工作簿的所有数据连接都得到更新。

Updating all the Data Connections in the Workbook

你的工作簿可能有多个数据连接。你需要不时地更新它们,以便你的工作簿可以访问最新的数据。

Step 1 − 单击包含指向导入数据文件链接的表中的任意单元格。

Step 2 − 单击功能区的“数据”选项卡。

Step 3 − 单击连接组中的“全部刷新”。

data connections

Step 4 − 从下拉列表中选择“全部刷新”。工作簿中的所有数据连接都将得到更新。

select refresh all

Automatically Refresh Data when a Workbook is opened

每当你打开工作簿时,你可能希望访问工作簿的数据连接中的最新数据。

Step 1 − 单击包含指向导入数据文件链接的表中的任意单元格。

Step 2 − 单击“数据”选项卡。

Step 3 − 单击连接组中的“连接”。

此时将显示“工作簿连接”对话框。

automatically refresh data

Step 4 − 单击“属性”按钮。此时将显示“连接属性”对话框。

click properties button

Step 5 − 单击“使用”选项卡。

click usage tab

Step 6 − 选中选项 - 打开文件时刷新数据。

你还有另一个选项,即 Remove data from the external data range before saving the workbook 。你可以使用此选项保存工作簿,内容包括查询定义但不包括外部数据。

Step 7 − 单击“确定”。每当你打开工作簿时,最新数据将加载到你的工作簿中。

Automatically Refresh Data at regular Intervals

你可能在使用工作簿并且将其打开很长时间。在这种情况下,你可能需要定期刷新数据而无需你进行任何干预。

Step 1 − 单击包含指向导入数据文件链接的表中的任意单元格。

Step 2 − 单击功能区的“数据”选项卡。

Step 3 − 单击连接组中的“连接”。

此时将显示“工作簿连接”对话框。

automatically refresh data at regular intervals

Step 4 −单击“属性”按钮。

click the properties button

将出现“连接属性”对话框。设置如下属性−

  1. Click the Usage tab.

  2. 选中 Refresh every 选项。

  3. 在每次刷新操作之间输入分钟数60,然后单击“确定”。

refresh every

你的数据将每60分钟(即每小时)自动刷新一次。

Enabling Background Refresh

对于非常大的数据集,请考虑运行后台刷新。这可以将 Excel 的控制权还给你,而不是让你等待几分钟或更长的时间来完成刷新。当你在后台运行查询时,可以使用此选项。但是,在此期间,你无法为为数据模型检索数据的任何连接类型运行查询。

  1. 单击包含与导入数据文件链接的表格中的任何单元格。

  2. Click the Data tab.

  3. 单击“连接”组中的“连接”。将出现“工作簿连接”对话框。

click connections

单击“属性”按钮。

properties

将出现“连接属性”对话框。单击“使用情况”选项卡。将显示“刷新控制”选项。

refresh control options
  1. Click Enable background refresh.

  2. 单击“确定”。已为你的工作簿启用后台刷新。