Excel Data Analysis 简明教程

Importing Data into Excel

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

You might have to use data from various sources for analysis. In Excel, you can import data from different data sources. Some of the data sources are as follows −

  1. Microsoft Access Database

  2. Web Page

  3. Text File

  4. SQL Server Table

  5. SQL Server Analysis Cube

  6. XML File

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

You can import any number of tables simultaneously from a database.

Importing Data from Microsoft Access Database

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

We will learn how to import data from MS Access database. Follow the steps given below −

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

Step 1 − Open a new blank workbook in Excel.

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

Step 2 − Click the DATA tab on the Ribbon.

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

Step 3 − Click From Access in the Get External Data group. The Select Data Source dialog box appears.

from access

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

Step 4 − Select the Access database file that you want to import. Access database files will have the extension .accdb.

select access database

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

The Select Table dialog box appears displaying the tables found in the Access database. You can either import all the tables in the database at once or import only the selected tables based on your data analysis needs.

select table dialog box

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

Step 5 − Select the Enable selection of multiple tables box and select all the tables.

enable selection

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

Step 6 − Click OK. The Import Data dialog box appears.

import data dialog box

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

As you observe, you have the following options to view the data you are importing in your workbook −

  1. Table

  2. PivotTable Report

  3. PivotChart

  4. Power View Report

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

You also have an option - only create connection. Further, PivotTable Report is selected by default.

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

Excel also gives you the options to put the data in your workbook −

  1. Existing worksheet

  2. New worksheet

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

You will find another check box that is selected and disabled – Add this data to the Data Model. Whenever you import data tables into your workbook, they are automatically added to the Data Model in your workbook. You will learn more about the Data Model in later chapters.

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

You can try each one of the options to view the data you are importing, and check how the data appears in your workbook −

  1. If you select Table, Existing worksheet option gets disabled, New worksheet option gets selected and Excel creates as many worksheets as the number of tables you are importing from the database. The Excel tables appear in these worksheets.

  2. If you select PivotTable Report, Excel imports the tables into the workbook and creates an empty PivotTable for analyzing the data in the imported tables. You have an option to create the PivotTable in an existing worksheet or a new worksheet. Excel tables for the imported data tables will not appear in the workbook. However, you will find all the data tables in the PivotTable fields list, along with the fields in each table.

  3. If you select PivotChart, Excel imports the tables into the workbook and creates an empty PivotChart for displaying the data in the imported tables. You have an option to create the PivotChart in an existing worksheet or a new worksheet. Excel tables for the imported data tables will not appear in the workbook. However, you will find all the data tables in the PivotChart fields list, along with the fields in each table.

  4. If you select Power View Report, Excel imports the tables into the workbook and creates a Power View Report in a new worksheet. You will learn how to use Power View Reports for analyzing data in later chapters. Excel tables for the imported data tables will not appear in the workbook. However, you will find all the data tables in the Power View Report fields list, along with the fields in each table.

  5. If you select the option - Only Create Connection, a data connection will be established between the database and your workbook. No tables or reports appear in the workbook. However, the imported tables are added to the Data Model in your workbook by default. You need to choose any of these options, based on your intent of importing data for data analysis. As you observed above, irrespective of the option you have chosen, the data is imported and added to the Data Model in your workbook.

Importing Data from a Web Page

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

Sometimes, you might have to use the data that is refreshed on a web site. You can import data from a table on a website into Excel.

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

Step 1 − Open a new blank workbook in Excel.

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

Step 2 − Click the DATA tab on the Ribbon.

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

Step 3 − Click From Web in the Get External Data group. The New Web Query dialog box appears.

new web query

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

Step 4 − Enter the URL of the web site from where you want to import data, in the box next to Address and click Go.

enter url

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

Step 5 − The data on the website appears. There will be yellow arrow icons next to the table data that can be imported.

data appears

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

Step 6 − Click the yellow icons to select the data you want to import. This turns the yellow icons to green boxes with a checkmark as shown in the following screen shot.

selected area

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

Step 7 − Click the Import button after you have selected what you want.

click import button

Import Data 对话框将出现。

The Import Data dialog box appears.

import data dialog box appears

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

Step 8 − Specify where you want to put the data and click Ok.

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

Step 9 − Arrange the data for further analysis and/or presentation.

arrange data

Copy-pasting data from web

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

Another way of getting data from a web page is by copying and pasting the required data.

Step 1 − 插入新工作表。

Step 1 − Insert a new worksheet.

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

Step 2 − Copy the data from the web page and paste it on the worksheet.

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

Step 3 − Create a table with the pasted data.

copypasting data

Importing Data from a Text File

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

If you have data in .txt or .csv or .prn files, you can import data from those files treating them as text files. Follow the steps given below −

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

Step 1 − Open a new worksheet in Excel.

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

Step 2 − Click the DATA tab on the Ribbon.

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

Step 3 − Click From Text in the Get External Data group. The Import Text File dialog box appears.

importing data from text file

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

You can see that .prn, .txt and .csv extension text files are accepted.

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

Step 4 − Select the file. The selected file name appears in the File name box. The Open button changes to Import button.

select file

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

Step 5 − Click the Import button. Text Import Wizard – Step 1 of 3 dialog box appears.

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

Step 6 − Click the option Delimited to choose the file type and click Next.

delimited

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

The Text Import Wizard – Step 2 of 3 dialog box appears.

Step 7 − 在分隔符下选择 Other 。

Step 7 − Under Delimiters, select Other.

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

Step 8 − In the box next to Other, type | (That is the delimiter in the text file you are importing).

Step 9 − 单击下一步。

Step 9 − Click Next.

text import wizard

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

The Text Import Wizard – Step 3 of 3 dialog box appears.

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

Step 10 − In this dialog box, you can set column data format for each of the columns.

set column data format

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

Step 11 − After you complete the data formatting of columns, click Finish. The Import Data dialog box appears.

complete data formatting

您将观察到以下内容 −

You will observe the following −

  1. Table is selected for view and is grayed. Table is the only view option you have in this case.

  2. You can put the data either in an existing worksheet or a New worksheet.

  3. You can select or not select the check box Add this data to the Data Model.

  4. Click OK after you have made the choices.

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

Data appears on the worksheet you specified. You have imported data from Text file into Excel workbook.

Importing Data from another Workbook

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

You might have to use data from another Excel workbook for your data analysis, but someone else might maintain the other workbook.

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

To get up to date data from another workbook, establish a data connection with that workbook.

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

Step 1 − Click DATA > Connections in the Connections group on the Ribbon.

出现 Workbook Connections 对话框。

The Workbook Connections dialog box appears.

workbook connections

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

Step 2 − Click the Add button in the Workbook Connections dialog box. The Existing Connections dialog box appears.

existing connections

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

Step 3 − Click Browse for More… button. The Select Data Source dialog box appears.

browse more

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

Step 4 − Click the New Source button. The Data Connection Wizard dialog box appears.

data connection wizard

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

Step 5 − Select Other/Advanced in the data source list and click Next. The Data Link Properties dialog box appears.

data link properties

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

Step 6 − Set the data link properties as follows −

  1. Click the Connection tab.

  2. Click Use data source name.

  3. Click the down-arrow and select Excel Files from the drop-down list.

  4. Click OK.

出现 Select Workbook 对话框。

The Select Workbook dialog box appears.

select workbook

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

Step 7 − Browse to the location where you have the workbook to be imported is located. Click OK.

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

The Data Connection Wizard dialog box appears with Select Database and Table.

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

Note − In this case, Excel treats each worksheet that is getting imported as a table. The table name will be the worksheet name. So, to have meaningful table names, name / rename the worksheets as appropriate.

data connection wizard dialog box

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

Step 8 − Click Next. The Data Connection Wizard dialog box appears with Save Data Connection File and Finish.

save and finish

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

Step 9 − Click the Finish button. The Select Table dialog box appears.

select table dialog box appears

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

As you observe, Name is the worksheet name that is imported as type TABLE. Click OK.

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

The Data connection with the workbook you have chosen will be established.

Importing Data from Other Sources

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

Excel provides you options to choose various other data sources. You can import data from these in few steps.

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

Step 1 − Open a new blank workbook in Excel.

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

Step 2 − Click the DATA tab on the Ribbon.

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

Step 3 − Click From Other Sources in the Get External Data group.

importing data from other sources

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

Dropdown with various data sources appears.

dropdown

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

You can import data from any of these data sources into Excel.

Importing Data using an Existing Connection

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

In an earlier section, you have established a data connection with a workbook.

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

Now, you can import data using that existing connection.

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

Step 1 − Click the DATA tab on the Ribbon.

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

Step 2 − Click Existing Connections in the Get External Data group. The Existing Connections dialog box appears.

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

Step 3 − Select the connection from where you want to import data and click Open.

importing data using existing connection

Renaming the Data Connections

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

It will be useful if the data connections you have in your workbook have meaningful names for the ease of understanding and locating.

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

Step 1 − Go to DATA > Connections on the Ribbon. The Workbook Connections dialog box appears.

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

Step 2 − Select the connection that you want to rename and click Properties.

renaming data connections

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

The Connection Properties dialog box appears. The present name appears in the Connection name box −

connection properties

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

Step 3 − Edit the Connection name and click OK. The data connection will have the new name that you have given.

Refreshing an External Data Connection

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

When you connect your Excel workbook to an external data source, as you have seen in the above sections, you would like to keep the data in your workbook up to date reflecting the changes made to the external data source time to time.

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

You can do this by refreshing the data connections you have made to those data sources. Whenever you refresh the data connection, you see the most recent data changes from that data source, including anything that is new or that is modified or that has been deleted.

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

You can either refresh only the selected data or all the data connections in the workbook at once.

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

Step 1 − Click the DATA tab on the Ribbon.

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

Step 2 − Click Refresh All in the Connections group.

refreshing external data connection

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

As you observe, there are two commands in the dropdown list – Refresh and Refresh All.

  1. If you click Refresh, the selected data in your workbook is updated.

  2. If you click Refresh All, all the data connections to your workbook are updated.

Updating all the Data Connections in the Workbook

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

You might have several data connections to your workbook. You need to update them from time to time so that your workbook will have access to the most recent data.

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

Step 1 − Click any cell in the table that contains the link to the imported data file.

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

Step 2 − Click the Data tab on the Ribbon.

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

Step 3 − Click Refresh All in the Connections group.

data connections

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

Step 4 − Select Refresh All from the dropdown list. All the data connections in the workbook will be updated.

select refresh all

Automatically Refresh Data when a Workbook is opened

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

You might want to have access to the recent data from the data connections to your workbook whenever your workbook is opened.

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

Step 1 − Click any cell in the table that contains the link to the imported data file.

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

Step 2 − Click the Data tab.

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

Step 3 − Click Connections in the Connections group.

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

The Workbook Connections dialog box appears.

automatically refresh data

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

Step 4 − Click the Properties button. The Connection Properties dialog box appears.

click properties button

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

Step 5 − Click the Usage tab.

click usage tab

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

Step 6 − Check the option - Refresh data when opening the file.

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

You have another option also - Remove data from the external data range before saving the workbook. You can use this option to save the workbook with the query definition but without the external data.

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

Step 7 − Click OK. Whenever you open your workbook, the up to date data will be loaded into your workbook.

Automatically Refresh Data at regular Intervals

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

You might be using your workbook keeping it open for longer durations. In such a case, you might want to have the data refreshed periodically without any intervention from you.

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

Step 1 − Click any cell in the table that contains the link to the imported data file.

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

Step 2 − Click the Data tab on the Ribbon.

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

Step 3 − Click Connections in the Connections group.

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

The Workbook Connections dialog box appears.

automatically refresh data at regular intervals

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

Step 4 − Click the Properties button.

click the properties button

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

The Connection Properties dialog box appears. Set the properties as follows −

  1. Click the Usage tab.

  2. Check the option Refresh every.

  3. Enter 60 as the number of minutes between each refresh operation and click Ok.

refresh every

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

Your Data will be automatically refreshed every 60 min. (i.e. every one hour).

Enabling Background Refresh

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

For very large data sets, consider running a background refresh. This returns control of Excel to you instead of making you wait several minutes or more for the refresh to finish. You can use this option when you are running a query in the background. However, during this time, you cannot run a query for any connection type that retrieves data for the Data Model.

  1. Click in any cell in the table that contains the link to the imported data file.

  2. Click the Data tab.

  3. Click Connections in the Connections group. The Workbook Connections dialog box appears.

click connections

单击“属性”按钮。

Click the Properties button.

properties

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

The Connection Properties dialog box appears. Click the Usage tab. The Refresh Control options appear.

refresh control options

  1. Click Enable background refresh.

  2. Click OK. The Background refresh is enabled for your workbook.