第14章　Excel与Access传递数据

要想借助Access分析和处理Excel中的数据，我们首先要了解的就是如何在Excel和Access之间互相传递数据，即如何把Excel中的数据“放到”Access中去处理，然后再把Access中处理好的结果“交还”给Excel做进一步的数据分析和展示。以下是Excel和Access之间互相传递数据的几种方法。

14.1　从Excel到Access

14.1.1　粘贴法

要把Excel中的数据交给Access，我们最先可能想到的方法就是使用“复制和粘贴”方法。“粘贴法”的具体操作步骤为：首先打开Access，选择“程序→Microsoft Office→Microsoft Office Access”命令，启动Access程序，在Access界面左侧单击“文件→新建”命令，然后单击Access界面中部的“可用模板→空数据库”命令，在Access界面右下角“文件名”处取一个有意义的文件名（这里我们使用Access自动生成的文件名称），最后单击“创建”按钮，创建一个空白的Access数据库文件。

默认情况下，新建的空数据会从新建数据表格界面开始。在这里，我们要自己创建表格，因此，单击表格创建界面右上角的关闭（×）图标，退出创建数据表格界面。

打开Excel数据文件，把Excel界面和Access界面并排放置在计算机屏幕上，选中Excel中的数据并复制，然后将鼠标置于Access窗口左侧的空白处，即“所有Access对象”下部，单击鼠标右键，在弹出的快捷菜单中选择“粘贴”命令，如图14-1所示。

图14-1

此时弹出一个Access对话框，询问“是否让数据的第一行包含列标题？”因为我们的Excel数据是含有列标题的，因此单击“是”按钮，如图14-2所示。

图14-2

此时Access弹出下一个消息框，上面提示“已成功导入所有对象。”这代表粘贴成功，单击“确定”按钮，如图14-3所示。

图14-3

这时，你会发现在Access窗口左侧的“所有Access对象”下方的列表中出现了一个新的项目，这就是你刚才粘贴过来的Excel数据。默认情况下，表格的名称就是Excel数据所在的工作表的名称。双击该名称，会在Access窗口右侧显示所粘贴的数据详细内容，如图14-4所示。

图14-4

用这种方法可以粘贴多个Excel数据表格到Access中，每个粘贴的Excel数据表格都会在Access界面左侧的对象列表中形成一个Access表格名称。

14.1.2　导入法

Access和Excel是一对亲兄弟，它们之间可以做到无缝对接。在Access中，为了方便与Excel之间交换数据，Access为Excel设计了专用的外部数据导入工具，所以在不打开Excel文件的情况下，可以“导入”或者“链接”Excel中的数据。

Access“导入”Excel中数据的方法如下。

在Access界面中单击“外部数据→导入并链接→Excel”命令。

这时弹出“获取外部数据—Excel电子表格”对话框，在“获取外部数据—Excel电子表格”对话框中的“文件名”处单击“浏览”按钮，在打开的对话框中选择所需的Excel数据文件。在“指定数据在当前数据库中的存储方式和存储位置”下方，选择“将源数据导入当前数据库的新表中”单选框，然后单击“确定”按钮，如图14-5所示。

图14-5

此时弹出“导入数据表向导”对话框，在“导入数据表向导”对话框的上部显示有“电子表格含有一个以上工作表或区域。请选择合适的工作表或区域：”。

Access导入外部数据向导非常聪明，它能够探测出外部Excel文件含有多个工作表或者命名单元格区域。当外部Excel文件含有多个工作表时，或者Excel文件中含有多个Excel命名单元格区域时，会在这里显示外部Excel文件的所有工作表或者命名单元格区域信息。

默认情况下，Access会“显示工作表”，此时在右侧列表框中显示外部Excel文件中所有的工作表名称。如果你曾经在Excel数据文件中给某些单元格区域定义了名称，那么当你选择了“显示命名区域”后，在右侧的列表框中会显示出所有已命名单元格区域名称。这时，我们可以导入Excel文件中某个特定命名单元格区域中的数据。

在这里，我们选择“显示工作表”单选框，然后在右侧的列表框中选择“员工名单”工作表，此时，我们可以在对话框下方预览选中的Excel工作表中的数据，如图14-6所示。

图14-6

接着单击“下一步”按钮，弹出“导入数据表向导”下一个对话框。在这个对话框中，我们要根据Excel中数据的实际情况，告诉Access即将导入的数据表格中是否包含列标题。因为我们的数据包含列标题，因此勾选“第一行包含列标题”复选框，如图14-7所示。

图14-7

继续单击“下一步”按钮，在“导入数据表向导”这一步中，我们需要告诉Access：即将导入的Excel数据每一列的数据类型各是什么，即外部Excel数据中的每一列里的内容是文本、数字、日期还是其他类型的数据。只有让Access明确这一点，它才能知道将来如何对每列数据进行计算和处理：数字要按数字的方式运算，日期要按日期的方式进行运算，文本不能参与数字运算，只能按文本的方式处理。

当然这一步我们也可以让Access自行判断Excel表格中每一列的数据类型，但可能会出现判断失误，最有可能出现的误判是：把数字和时间误认为是文本，造成的结果是，数字和时间不能参与数字和时间相关的运算，给以后的数据处理造成麻烦。

设置每列数据类型的方法是：在对话框下部的数据预览窗口中选择相应的列，然后在对话框上部的“数据类型”下拉列表中选择相应的数据类型即可，如图14-8所示。这里重点是保证数字和日期列设置了正确的数据类型。

图14-8

继续单击“下一步”按钮，在这一步中，Access让我们为导入的Excel数据设置或者添加“主键”。所谓的“主键”，就是用来区分数据表中每一行数据的关键字。比如，对于正在导入的“员工名单”表格，其中的“员工工号”就是该数据表的“主键”。主键具有如下特性：给定任意一个主键，在整个数据表中只能找出唯一的一行记录与之对应。比如，对于“员工名单”表格，员工工号就是主键，因为给定任意一个员工工号，“员工名单”表格中只有唯一的员工与之相对应。“姓名”列不能作为“员工名单”表的主键，因为在一个公司中，员工可能存在重名现象，即给定一个员工姓名，不一定只有一个员工与该姓名对应。

对于“主键”，我们还可以这样理解：如果我们按照“姓名”给员工发工资，可能会出现混乱，而按照“员工工号”（主键）给员工发工资则不会出现混乱。这也是为什么每个公司在员工入职时都要分配一个工号的原因。

在这里，由于表格中存在着可以作为“主键”的字段，所以，这里选择“我自己选择主键”单选框，然后在右面的下拉列表中选择“员工工号”作为主键，如图14-9所示。接着单击“下一步”按钮。

图14-9

此时导入Excel中的数据到Access的工作基本完成，现在需要给导入的数据表取一个名称，Access默认的导入表的名称是Excel工作表的名称。我们接受默认的名称，然后单击“完成”按钮，如图14-10所示。

图14-10

Access获取外部数据最后一个对话框是询问是否保存刚才的外部数据导入步骤。保存外部数据导入步骤的好处是在下一次导入同样结构的外部数据时，不需要再手动执行前面的各种导入外部数据的各种设置操作。我们暂时不保存外部数据的导入步骤，单击“关闭”按钮关闭Access的“获取外部数据”对话框。

至此，我们完成了Access导入Excel一个工作表中数据的全部操作，此时，在Access界面左侧的Access对象列表中，我们看到了刚刚导入的Excel数据表的名称，如图14-11所示。

图14-11

双击刚刚导入的数据表名称，在Access右侧可以查看详细数据，如图14-12所示。

图14-12

重复上述步骤，继续导入Excel文件中其他工作表中的数据，直至全部数据导入完成。

14.1.3　我们的方法：链接法

用前面所讲解的“粘贴法”或“导入法”把Excel中的数据导入到Access的方法有一个缺陷，即导入到Access中的数据与Excel中保存的原始数据失去了联系，导入的数据是静态的，外部Excel中数据的变化不能及时反映到Access中。对于一次性的数据分析和处理，这种操作我们还可以接受，但是，如果需要对不断变化的数据源进行数据处理和分析，显然，每次数据源发生变化时，我们都需要使用“粘贴法”或“导入法”导入数据，这实在有些麻烦。

下面介绍使用“链接法”把Excel中的数据动态关联到Access，使用“链接法”动态关联到Access中的数据能够与Excel中的数据建立动态联系，当Excel中的数据发生变化时，Access中的数据也跟着发生变化！使用“链接法”将Excel中的数据链接到Access的方法如下。

在Access界面中，单击“外部数据→导入并链接→Excel”命令，如图14-13所示。调出“获取外部数据—Excel电子表格”对话框。

图14-13

在弹出的“获取外部数据—Excel电子表格”对话框的“指定数据源”处，选择Excel数据源文件所在的位置。在对话框的下部的“指定数据在当前数据库中的存储方式和存储位置”处，选择“通过创建链接表来链接到数据源”单选框，然后单击“确定”按钮，如图14-14所示。

图14-14

“通过创建链接表来链接到数据源”复选框与“将源数据导入当前数据库的新表中”复选框的不同之处在于，当外部Excel数据发生变化时，以“链接”方式导入到Access中的数据能够及时地反映外部数据变化的情况，而第一个选项只是一次性地导入了静态的Excel中的数据，失去了和外部数据联系的动态链接。

在弹出的“链接数据表向导”对话框中，选择“显示工作表”单选框，这时，对话框右侧会显示外部Excel文件中所有工作表的名称，这里选择“员工名单”工作表。在下方的表格预览窗口中查看工作表中的数据，如果没有问题，单击“下一步”按钮，如图14-15所示。

图14-15

在这一步中要告诉Access，我们导入的Excel外部数据第一行含有列标题，勾选“第一行包含列标题”复选框，然后单击“下一步”按钮，如图14-16所示。

图14-16

此时出现如图14-17所示的对话框，表示外部数据链接完成。

图14-17

我们现在观察Access界面左侧的Access对象列表，这里出现一个带有Excel图标的“员工名单”对象，如图14-18所示。这里的Excel图标表示该表格是链接到Access中的“Excel”表格，原始数据保存在Excel中，在Access中只是建立了和Excel中原始数据表格的一个动态链接。

图14-18

双击带有Excel图标的“员工名单”对象，打开链接的Excel数据表，查看数据无误后，关闭“员工名单”链接表对象，我们可以试着改变外部Excel文件“员工名单”工作表中的数据，再重新打开Access中的链接表格，你会发现链接表格中的数据已经同步更新了，如图14-19所示。

图14-19

注意：我们必须在Access中关闭了链接表对象后才能对所链接的外部Excel数据进行修改，当Access中的链接表处于打开状态时，我们是无法修改所链接的外部Excel数据的。

重复上述过程，链接Excel文件中其他工作表中的数据到Access，直至全部数据链接至Access完成。

14.2　从Access到Excel

14.2.1　粘贴法

当数据导入到Access中，借助Access的强大数据处理能力完成它所擅长的任务后，经常需要把Access中的处理结果再次导出到我们最为熟悉的Excel中做进一步分析和可视化展示。数据从Access导出到Excel中的最先想到的方法自然是“粘贴法”。“粘贴法”具体操作方法如下。

如果Access中已经有了一些数据，现在我们在Access界面左侧选中需要导出到Excel中的Access对象，如“员工名单”表格对象，双击将其打开，然后单击表格第一个单元格左上方的位置（见图14-20），这可以选中整个表格，接着单击鼠标右键，在弹出的鼠标右键菜单中选择“复制”命令，如14-20所示。

图14-20

打开或新建一个Excel文件，在空白的工作表中，单击鼠标右键，在弹出的快捷菜单中选择“粘贴”命令，此时，Access中的数据就粘贴到Excel文件中了，如图14-21所示。

图14-21

14.2.2　Access导出法

在Access界面左侧的Access对象列表中，选中表格名称，单击鼠标右键，在弹出的快捷菜单中选择“导出”命令，在弹出的子菜单中选择“Excel”选项。

此时弹出“导出—Excel电子表格”对话框。如果不想使用默认文件名，可在“文件名文本框中输入自定义的文件名。然后单击对话框下方的“确定”按钮，如图14-22所示。

图14-22

到此将表格导出到Excel中就完成了。在这里暂不保存导出步骤。最后打开导出的Excel文件，查看其中的数据。

14.2.3　我们的方法：Excel导入法

前面讲到的方法是在Access环境中，把Access中的数据“推”到Excel；其实，将Access中的数据转移到Excel还有另外一种方法，就是在Excel环境下，把Access中的数据“拉”到Excel中来。用“拉”的方法取得Access中的数据的好处是，这种方法能够在Excel中建立并保持与Access中数据的联系，在Excel中，通过使用Excel外部数据区域的“刷新”功能，随时得到Access中最新的数据分析和处理结果。具体操作步骤如下。

首先，打开一个现存的或者新建的Excel文件，选择“获取外部数据→自Access”命令。

在打开的“选取数据源”对话框中，选取数据所在的Access文件名称，在这里，我们选择一个叫“罗斯文”的Access文件，当然也可以选择我们在前面创建的Access文件，如图14-23所示。

图14-23

此时打开“选择表格”对话框，该对话框显示出了Access中存在的所有表格和查询的名称（关于查询，本书后面会有详细的介绍，我们暂且把Access表格和查询统称为“Access对象”）。选择其中的任何一个Access对象名称，这里选择“员工”工作表，然后单击“确定”按钮，如图14-24所示。

图14-24

此时弹出“导入数据”对话框。在对话框下方的“请选择该数据在工作簿中的显示方式”中，选择“表”单选框；在“数据的放置位置”处，选择即将导入的Access对象在当前工作簿中的存放位置。选择“A1”单元格，然后单击“确定”按钮，如图14-25所示。

图14-25

此时，我们选中的Access中的表格数据以Excel“表”的形式成功导入到Excel中，如图14-26所示。

图14-26

正如本书前面所提到的，用这种方法导入的Access表格不是一个普通的Excel表格，这是一个与Access中的数据建立了动态联系的表格。如果选中表格中的任意一个单元格，单击鼠标右键，在弹出的快捷菜单中选择“刷新”命令，可以再次导入此时Access中最新的数据。