第7章　导入外部数据

如果我们要输入数据已经在其他地方存在，当然不需要再把这些数据手动地往Excel中输入一遍，甚至连复制粘贴都不需要！

如果你是一名生产计划人员，每周的工作是从企业的业务系统中导入订单数据到Excel中，然后再结合物料清单数据进行各种分析，得出原材料需求报表、设备需求计划报表、工时需求报表等……

或者你是一名财务人员，每天的任务就是从财务系统中把原始财务数据导入到Excel进行程式化分析，得出各种财务分析报告……

又或者你是一名培训管理人员，需要追踪几百或上千人的培训计划的完成情况，每周从培训系统里下载最新的培训记录到Excel中，结合每位培训人员的培训计划追踪计划的完成情况……

如果你属于以上这几种类似情况，那么这里有一个好消息告诉你，你的工作可以通过Excel自动完成，而用Excel自动导入外部数据就是实现这个计划的第一步。

Excel为我们提供了非常方便的自动导入外部数据的功能，让我们有机会摆脱枯燥的复制和粘贴操作。如果是实时更新的外部数据，还可以通过在Excel中设置数据的刷新频率，来不断获得最新数据。

下面分别介绍导入来自不同数据源的外部数据的方法。

7.1　导入Excel数据

很多时候，分析报告所使用的数据来自于其他人准备的Excel文件，难道我们每次都需要打开另外一个Excel文件，然后把其中的数据粘贴到自己的Excel文件中再进行分析吗？

不需要！我们可以通过建立自己的Excel文件与其他人的Excel文件之间的连接，这样，在需要最新数据的时候，只要单击一下Excel中的“数据→连接→全部刷新”命令就可以了。

导入外部Excel数据的方法如下。

选择“数据→获取外部数据→现有连接”命令，打开“现有连接”对话框。因为要导入的数据属于Excel自己家族的数据，所以使用的是Excel的“现有连接”功能。

在“现有连接”对话框中，单击左下角的“浏览更多”按钮，如图7-1所示，打开“选取数据源”对话框。

图7-1

在“选取数据源”对话框中，找到外部Excel数据文件所在的位置，单击右下角的“打开”按钮。

此时在工作表界面上出现“选择表格”对话框，外部数据文件中的所有工作表名称显示在该对话框下部的列表框里。可以根据实际情况决定是否勾选该对话框下部的“数据首行包含列标题”复选框，如图7-2所示。

图7-2

在弹出的“导入数据”对话框中，设置“请选择该数据在工作簿中的显示方式”为“表”，“数据的放置位置”为“$A$1”单元格。然后单击“确定”按钮导入外部数据，如图7-3所示。

图7-3

可以看到，外部数据成功导入。现在我们就可以基于导入的外部数据做各种分析了。当外部数据源改变时，只需要把鼠标置于数据区域内的任意一个单元格，选择“表格工具→外部表数据→刷新”命令，就可以重新导入外部最新的数据了，如图7-4所示。

图7-4

7.2　导入网页的数据

有很多数据是以网页的形式发布的，比如某些财经数据、人口统计数据等。难道我们每次分析的时候都需要复制和粘贴这些数据到Excel中吗？如果你真是在这样做，那么你很可能“OUT”了。

其实很多以网页形式发布的数据可以自动导入到Excel中的。

虽然我不是“彩迷”，也不太相信彩票号码会有什么特别的规律，但是分析一下彩票的历史数据还是比较有趣的。下面就演示一下如何利用Excel动态导入某彩票网站上发布的福彩号码历史数据。

（1）选择“数据→获取外部数据→自网站”命令，调出“新建WEB查询”对话框。

（2）在“新建WEB查询”对话框的地址栏中，输入以下地址（或者其他你所感兴趣的网址）http://bf.smswriter.com/list/ssq/sc.asp，然后单击“转到”按钮打开网页，如图7-5所示。

图7-5

（3）在“新建Web查询”对话框的下部，观察已经打开了的网页时会发现：在网页不同的位置都有一个黄色小箭头，黄色小箭头所指示的数据可以导入到Excel中，如图7-6所示。

图7-6

单击彩票号码区域的小箭头，表示我们要导入这部分数据到Excel，然后单击对话框右下角的“导入”按钮。

（4）在“导入数据”对话框中选择导入数据的放置位置，这里选择当前工作表的A1单元格。然后单击“确定”按钮，如图7-7所示。

图7-7

现在，网页中的数据已经成功导入到了Excel工作表中，数据导入后的效果如图7-8所示。

图7-8

当有新的一期彩票数据时，在保证活动的单元格处在数据区域的情况下，只需选择“数据→连接→全部刷新”命令即可快速刷新数据。

7.3　导入数据库中的数据

一般公内部都面有各种业务数据库，如果我们能直接把业务数据库比如SQL Server或者Oracle中的数据自动提取到Excel中来，岂不是很方便？

直接连接公司的业务数据库可能需要IT部门的协助，具体细节可以向自己所在公司的IT部门咨询。在这里我们演示一下用Excel连接到数据库的基本逻辑，以便我们在和IT部门沟通时有共同语言。

在Excel连接数据库之前，必须在Excel和数据库之间建立一个桥梁，这个桥梁称为ODBC，这里不介绍ODBC的含义，只要知道它是一个Excel与数据库连接的桥梁就好了。

如何建立这个Excel与数据库连接的桥梁呢？请按照如下步骤操作。

注意：这里值得提醒的是，为了演示的需要，这里连接的是Access数据库，但是Excel与其他类型数据库连接原理基本相同（其实Excel与Access的连接还有更直接的方法：选择“数据→获取外部数据→自Access”命令，在本书第二部分　有详细的介绍）。

（1）打开Windows操作系统的控制面板（以Windows XP为例），选择“性能和维护”选项。

（2）在“性能和维护”类别中，选择“管理工具”选项。

（3）在“管理工具”中选择“数据源（ODBC）”选项，如图7-9所示。

图7-9

（4）在打开的“ODBC数据源管理器”对话框中，选择“系统DSN”页标签，如图7-10所示。

图7-10

（5）根据所要连接的数据库类型，选择对应的数据库驱动程序（Driver）。因为我们要连接的是Access数据库，因此在这里选择“Microsoft Access Driver(.mdb,accdb)”，如图7-11所示，然后单击“完成”按钮。

图7-11

（6）在弹出的“ODBC Microsoft Access安装”对话框中，给这个正在建设的Excel和数据库之间的桥梁取一个好记的名字，如“myODBC”。然后单击“选择”按钮选择数据库的位置，如图7-12所示。

图7-12

（7）在弹出的“选择数据库”对话框中，选择数据库所在的位置，然后单击“确定”按钮，如图7-13所示。

图7-13

（8）此时回到“ODBC Microsoft Access安装”对话框，再次单击“确定”按钮，如图7-14所示。

图7-14

（9）回到“ODBC数据源管理器”对话框，我们看到Excel连接数据库的桥梁已经建设完毕，名字为“myODBC”，如图7-15所示。

图7-15

下面我们在Excel界面中，利用刚刚建设的桥梁（myODBC）连接数据库。

注意：虽然在“获取外部数据”功能组里面有直接连接Access的图标，但是为了演示Excel连接各类数据库的一般逻辑，我们故意没有使用它。

（1）在Excel 2010中，选择“数据→获取外部数据→自其他来源”命令。在弹出的下拉菜单中，选择“来自Microsoft Query”选项，如图7-16所示。

图7-16

（2）此时弹出“选择数据源”对话框，在该对话框的“数据库”标签下，我们发现：前面建设的Excel与数据库的桥梁（myODBC）已经在那里了。选择“myODBC*”选项，然后单击“确定”按钮，如图7-17所示。

图7-17

（3）此时弹出“查询向导-选择列”对话框，在这个对话的左边的列表框中，显示出了数据库中的所有数据表，选择感兴趣的表，然后单击“”按钮把选中的表加入到右边的列表框中，如图7-18所示。此时右边的表格显示出了该表中的所有列标题。接着单击“下一步”按钮。

图7-18

（4）此时弹出“查询向导-筛选数据”对话框，在这里我们可以对数据进行筛选。这里跳过这一步，直接单击“下一步”按钮，如图7-19所示。

图7-19

（5）接着弹出“查询向导-排序顺序”对话框，在这里我们可以对数据进行排序操作。这里跳过这一步，直接单击“下一步”按钮，如图7-20所示。

图7-20

（6）此时弹出“查询向导-完成”对话框，这里有两个选项，选择第一个选项：“将数据返回Microsoft Excel”。然后单击“完成”按钮，如图7-21所示。

图7-21

（7）弹出“导入数据”对话框，在“请选择该数据在工作簿中的显示方式”下面选择“表”；在“数据的放置位置”中选择“现有工作表”的“$A$1”单元格，如图7-22所示。然后单击“确定”按钮。

图7-22

（8）此时在选择的现有工作表上出现了我们导入的数据库表格，可以根据需要手动或自动刷新Excel数据表，从数据库中提取最新的数据，如图7-23所示。

图7-23

至此我们已经成功地用Excel连接了数据库中的数据。Excel连接数据库的操作的关键是“建立Excel与数据库之间的桥梁”，这需要一些数据库方面的信息，可能需要IT部门协助。

7.4　导入文本数据

我们知道Excel能够直接与数据库相连，动态导入数据库中的数据。然而，现实情况往往不是那么理想，有时IT部门会以某种理由说服你，你不可以用Excel与公司的业务数据库“直接”相连。这时你可以要求IT部门把需要的数据以文本的形式每天定时输出到公共磁盘上的指定位置，然后再用Excel导入功能获得这些文本数据。

其实这些文本数据不一定是来自数据库，很多公司的业务系统中都具有用户自助导出文本格式数据的能力。如果外部数据是以文本格式存在，Excel中的导入文本数据的功将给我们提供很大的便利。

如图7-24所示的数据是从某公司的考勤系统中输出的文本数据，如果我们需要在Excel中对这些数据进行进一步分析，该如把它何导入到Excel中呢？

图7-24

（1）选择“数据→获取外部数据→自文本”命令，弹出“导入文本文件”对话框。

（2）在弹出的“导入文本文件”对话框中，选择文本数据文件所在的位置。然后单击对话框右下角的“导入”按钮。

（3）此时弹出“文本导入向导-第1步，共3步”对话框，首先要确定将要导入的文本文件类型，通过观察文本文件得知，文本数据的列与列之间是用符号（逗号）分隔的。因此在“请选择最合适的文件类型”下方选择“分隔符号”单选框。然后单击“下一步”按钮，如图7-25所示。

图7-25

（4）在“文本导入向导-第2步，共3步”对话框中，通过查看原始数据的分隔符号知道，文本数据的分隔符号类型为“逗号”，因此勾选“逗号”复选框，此时在对话框下部的“数据预览”中已经看到了分割后的效果，如图7-26所示，单击“下一步”按钮。

图7-26

（5）在“文本导入向导-第3步，共3步”对话框中，可以手动为每一列数据设置数据类型以修正Excel外部数据导入向导可能出现的判断错误；除此之外，我们还可以决定是否导入某列数据。这里保持默认的“常规”选项。直接单击“下一步”按钮，如图7-27所示。

图7-27

（6）此时弹出“导入数据”对话框，选择即将导入的数据放置在工作表中的位置。选择现有工作表中的A1单元格，然后单击“确定”按钮，如图7-28所示。

图7-28

至此，外部的文本数据已经成功地导入了Excel中，并且建立了Excel与外部文本数据的动态连接，如图7-29所示。

图7-29

如果外部的文本文件数据发生了更新，可以将光标放置在刚刚导入的数据区内的任意一个单元格，选择“数据→连接→全部刷新”命令刷新数据。但是，对于导入文本文件，当单击了“全部刷新”按钮后，默认情况下Excel会提示我们重新选择文件，选择文件后，单击“导入”按钮完成数据的刷新。

现在的问题是：如果每次刷新导入的文本文件数据都需要重新选择文件地址，显然在外部文件名称不变而只有文件中的数据发生变化的情况下，这一步显得很多余。可以通过如下设置来避免这个多余的步骤。

（1）将光标置于数据区域中的任意一个单元格，选择“数据→连接→属性”命令，调出“外部数据区域属性”对话框。

（2）在“外部数据区域属性”对话框中，取消勾选“刷新时提示文件名”复选框，然后单击“确定”按钮，关闭对话框，如图7-30所示。

图7-30

此时再次选择“数据→连接→全部刷新”按钮刷新数据，这时将不再提示选择文件的对话框。

7.5　设置外部数据刷新方式

当导入外部数据完成后，可以通过“外部数据区域属性”对话框对外部导入的数据区域做一些有用的设置。

将光标置于外部数据区域中的任意一个单元格，选择“数据→连接→属性”命令，调出“外部数据区域属性”对话框。

在前面介绍导入外部数据时，我们都是通过手动刷新来重新获取外部的最新数据，其实重新获取外部数据还可以使用“自动刷新”的方法。在这里，可以把外部数据区域设置为“打开文件时刷新数据”或者自定义数据的“刷新频率”，如图7-31所示。

图7-31

微言微语

●　对于老板来说，用一个Excel不太熟练的下属去准备一份重要的分析报告，就如同让他制造一个质量不过关的“定时炸弹”，说不定这个炸弹会在你毫无准备的时候轰然爆炸，炸得你措手不及！因此，让有丰富实战经验的人对您的下属做一次彻底的Excel应用技术培训，绝对不是可有可无的投资。