|
第２７章 使用外部数据库文件
用户在使用Excel进行工作的时候，不但可以使用工作表中的数据，还可以访问外部数据库文件。使用外部数据库文件有很多优点，其中最大的优点就是用户可以利用导入和查询，在Excel中使用熟悉的工具外部数据进行处理和分析。
多数情况下，用户并不需要导入整个外部数据文件，只需通过对外部数据库执行查询，就可以将外部数据库中的某一个子集截入到Excel工作表中。其中可以只包含用户所需要的某些字段或是某些数据。
２７．１ 了解外部数据库文件
由于Excel ２００３工作表的行不能超过65 536行，列不能超过256列，因此用户即使拥有最先进的计算机系统，也无法处理非常巨大的表格。而许多外部数据库文件在操作系统的允许下几乎可以无限大，其保存的记录数量也可以是Excel工作表的千万倍。外部数据库文件可以是文本文件、Microsoft Access数据库、Microsoft SQL数据库、Microsoft OLAP多维数据集、dBASE数据库等。虽然用户不能在Excel中处理巨大的数据，但是可以通过导入和查询外部数据库中的文件，获取所需要的信息。
２７．２ 利用文本文件获取数据
Excel提供了３种方法可以从文本文件获取数据。
１）利用菜单栏“文件”－“打开”命令，可以直接导入文本文件。
２）在菜单栏上依次单击“数据”——“导入外部数据”——“导入数据”，直接导入文本文件。
３）使用Microsoft Query。
使用第１种方法时，文本文件会被导入到单张的Excel工作表中。使用这种方式时，如果文本文件的数据发生变化，并不会在Excel中体现，除非重新进行导入。
使用第２种方法时，Excel会在当前工作表的指定位置上显示导入的数据，同时Excel会将文本文件作为外部数据源。一旦文本文件中的数据发生变化，用户只需单击鼠标右键，在弹出的快捷菜单中选择“刷新数据”即可获得最新的数据。
如果用户的文本文件数据量巨大，在Excel中不能导入全部数据，而只需选择某些满足特定需要的记录，可以使用Microsoft Query。利用
Microsoft Query，用户可以确定选择条件，将导入操作限制在实际需要的记录上。有关Microsoft Query的更多细节，请参阅２７－４节。
２７．２．１ 编辑文本导入
导入文本文件时，虽然不能满足用户只导入指定记录的需要，但它却能够向用户提供其他形式上的控制。例如，用户在导入文本文件时可以将不需要的列删除，还能够设置导入列的数据类型，主要为常规、文本、日期。
示例２７．１ 导入文本文件到Excel工作表中
如果要将“２７．２物料入库信息查询．ｔｘｔ”的文本文件导入到Excel中，可以参照以下步骤。
步骤１．创建一个新的空白工作表。
步骤２．单击菜单“文件”——“打开”，在出现的“打开”对话框中单击“文件类型”的下拉列表，选择“文本文件”，找到“物料入库信息查询.txt”的文本文件并双击它，出现“文本导入向导——3步骤之1”对话框。“文本导入向导——3步骤之1”对话框中的“导入起始行”为1，也就是说文本文件将从第1行连同标题导入，如果选择2，则从第2行导入，导入的数据中将不包含标题。“文件原始格式，如果用户在对话框下部的预览窗口中发现字符显示为乱码，可以在列表中手动选择一个匹配的字符集。
步骤３．单击“下一步”按钮，设置分列数据所包含的分隔符号，用户可以选择“分号”、“逗号”、空格及“其他”，“其他”可根据数据实际的分隔情况来输入，如“_"、"*"等，本例中则使用“Tab键”。
步骤４．单击“下一步”按钮，进入“文本导入向导——3步骤之3”对话框。在此步骤中，用户可以取消对某列的导入，同时可以设置每个导入列的列数据格式。单击第一列货位，在“列数据格式”中单击“不导入此列（跳过）”选项按钮；单击款号列，“列数据格式”中单击“文本”选项按钮；单击日期列，“列数据格式”中单击“日期”选项按钮。
步骤５．单击“确定”按钮完成导入。
注意“要将导入的文件另存为Excel类型的文件，否则将不能保存导入的文本文件。
２７．２．２ Excel中的分列功能
Excel中的分列功能在处理大量数据时十分方便、快捷。它能将一列带胡统一分隔符号的数据，快速地按照分隔符号分隔多列来达到用户的需要，也可以一次性地将不能计算的文本型数值改变为可以参与计算的常规型数值。
示例２７．２ 快速将数据分为多列
图所示的数据列表是从财务软件中导出的费用数据，其中的Ｇ列“借方”为文本型数值，不能统计金额合计，Ｆ列的“摘要”中“_”后面都是发生费用的各个部门。可以从Excel Home网站下载“28.2Excel分列功能.xls”文件。
步骤１．首先在Excel数据列表中选中整个G列后单击右键，在弹出的快捷菜单中选择“插入”，插入一个空白列，为下列的数据分列操作预留空间。
步骤２．先遣整个Ｆ列，单击菜单“数据”——“分列”，在弹出的“文本分列向导——３步骤１”对话框中，选择“分隔符号”选项。单击“下一步”按钮，出现“文本分列向导——３步骤之２”对话框。
步骤３．勾选“分隔符号”中“其他”复选框，在右边的输入框中输入“_”下划线，请注意“数据预览”中的变化。
步骤４．单击“下一步”按钮，出现“文本分列向导——３步骤之３”对话框。在“列数据格式”中，有常规、文本、日期３种数据格式，在“数据预览”中单击一列数据，在“列数据格式”选择要设置的数据类型，可以快速改变整列的数据类型。
步骤５．单击“完成”按钮，完成对数据列表中Ｆ列的分列，此时，部门作为单独的一列被分离出来了。
同样，对“借方”进行文本与数值的转换，在“文本分列向导——３步骤３”对话框中“列数据格式”选择“常规”即可。
２７．３ 导入外部数据
如果用户需要引用本地计算机或网络上的外部数据，例如引用局域网内共享文件中的数据，可以通过使用Excel“导入外部数据”的功能来达到目的。只要外部数据源的位置不改变，用户就可以在Excel数据列表中随时右击鼠标选择“刷新数据”，便可以获得引用的外部数据源中最新的数据。用户通过设置“外部数据区域属性”，还可以使得引用数据在每次打开工作表时自动刷新或是在工作表打开的状态下自动定时刷新。如果数据源的位置改变了，用户还需要重新设置名称和路径，才能保证外部数据的正常刷新。
例如，要引用文件“２７．３标准工时数据．ｍｄｂ”中的数据并保持与它的时时更新，请参照以下步骤进行。
步骤１．创建一个新的空白工作表。
步骤２．单击菜单“数据”——“导入外部数据’——鼠标指向“导入数据”并单击它。
步骤３．在弹出的“选择数据源”对话框中，单击“文件类型”下拉列表，选择文件类型为“Access 数据库”，在“查找范围”中找到“27.3标准工时数据.mdb”文件。
步骤４．单击“打开”按钮，出现“选择表格”对话框，选择“标准工时数据”。
步骤５．单击“确定”按钮，出现“导入数据”对话框，数据的放置位置选择“现有工作表”选项，并单击Ａ１单元格，导入的数据将在当前工作表的Ａ１单元格顺序排列；也可以根据用户需要选择“新建工作表”，Excel将新建一个工作表，然后从Ａ１单元格开始插入数据；还可以创建数据透视表，Excel可以显示数据透视表向导，用它可以指定数据透视表的布局（有关数据透视表的创建请参见第２８章）。
步骤６．在“导入数据”对话框中单击“属性”按钮，出现“外部数据区域属性”对话框，在“刷新控件”中勾选“打开工作簿时，自动刷新”复选框，这样，用户只要一打开这个导入外部数据的工作簿，就会启用自动刷新，来自动更新外部数据。
步骤７．单击“确定”按钮，返回“导入数据”对话框，再单击“确定”按钮，完成设置。工作表中将会出现“标准工时数据；正在获取数据”的提示行。稍候几秒钟后就会出现导入的外部数据。
当用户重新打开已经导入外部数据的工作簿时就会出现“查询刷新”对话框。
单击“启用自动刷新”工作表将自动对引用的外部数据进行更新，单击“禁用自动刷新”则不对引用的外部数据进行更新。
如果用户在使用外部数据时需要对引用的外部数据进行更新的话，可以用鼠标右击工作表数据区的任意单元格，在出现的快捷菜单中选择“刷新数据”。
用户还可以利用“外部数据”工具栏对已经导入的外部数据重新进行设置。鼠标右击任何可见的工具栏，在弹出的快捷菜单中选择“外部数据”。
２７．４ 利用Microsoft Query创建查询
Microsoft Query充当Excel数据列表和外部数据源之间的桥梁作用。使用Microsoft Query，可以连接到外部数据源，从外部数据源中选择数据，并将该数据导入到Excel数据列表中，还可以根据需要刷新数据，与外部数据源中的数据保持同步。
２７．４．１ Microsoft Query简介
使用Microsoft Query，用户可以选择数据源中所需的数据列，并将其导入Excel。在Microsoft Query中为特定数据库设置数据源以后，只要想创建查询，便可以从该数据源中选择并检索数据，而不必重新键入所有连接信息。创建查询并将数据返回到Excel数据列表后，Microsoft Query会为Excel工作簿提供查询和数据源信息，以便用户在需要刷新数据时重新连接到数据库。实际上Microsoft Query程序承担了一种连接外部数据库与Excel数据列表的纽带作用。
用户可以利用Query来访问任何安装了ODBC、OLE—DB或OLAP驱动程序的数据源。Excel为下列数据源提供了驱动程序：
Microsoft Office Access,dBASE,Excel,Oracle,Paradox,Microsoft SQL Server OLAP Services,文本文件数据库
２７．４．２ Microsoft Query的有关术语
Microsoft Query相关术语
２７．４．３ 查询向导和 Query的异同
“查询向导”是Query的一种接口，可以帮助用户方便地选择条件和筛选方案。用“查询向导”完成简单的查询是一种非常理想的方法，但它并不具备Query的所有功能。例如，如果用户的查询条件中不仅涉及简单的比较，还涉及了对数据的计算，或者用户建立的查询需要在运行时提示使用者输入一个或多个参数，就必须使用Query，因为“查询向导”无法做到。同时，Query的重命名列、筛选不包括在结果集中的字段、将结果集限抽为唯一项、完成汇总计算等功能也是“查询向导”无法完成的。
２７．４．４ 直接使用Microsoft Query
如果用户要想直接在Microsoft Query中创建查询，可以参照以下步骤进行。
例如，要对“２８．４Microsoft Query检索数据源．ｍｄｂ”创建Microsoft Query查询。
步骤１．创建一个空白工作表，单击菜单“数据”——“导入外部数据”——鼠标指向“新建数据库查询”并单击它，出现“选择数据源”对话框。
步骤２．由于是对Ａccess数据库文件创建Microsoft Query查询，所以在“数据库”选项卡中选择“Ms Access Database*”文件类型，同时取消“使用‘查询向导’创建/编辑查询”的勾选，单击“确定”按钮，弹出“选择数据库”对话框，在“目录”中选择“28.4Microsoft Quer
y检索数据源.mdb”的文件所在的位置。
注意：必须取消“使用‘查询向导’创建／编辑查询”的勾选，否则将进入“查询向导”模式，而不是Microsoft Query。
步骤３．单击“选择数据库”对话框内的“确定”按钮，会弹出Microsoft Query“添加表”对话框。
步骤４．先中“价格查询”，单击“添加”按钮，Microsoft Query中会出现来自“价格查询”的字段列表。完成表的选择后，单击“关闭”按钮，即可风Microsoft Query查询窗口。
步骤５．在“字段下拉列表”中分别单击“客户代码”、“款式号”、“商品类别”、“单价”字段，向“数据窗格”中添加数据。单击Microsoft Query工具栏“视图”——“条件”，在“条件”字段的下拉列表中选择“客户代码”，在“值”中输入“［］”，在弹出的“输入参数值”对话框中输入客户代码“Ｃ０００００５”（也可以输入其他的客户代码），单击“确定”按钮。设置第二个条件字段为“商品类别”，参数设置为“Ａ”。
步骤６．单击Microsoft Query工具栏“文件”——“将数据返回Microsoft Query Excel”，在弹出的“导入数据”对话框中设置“数据的放置位置”为“现有工作表”的A4单元格。
步骤７．单击“参数”按钮，在“查询参数”对话框中，“参数１”的“获取参数值的方式”选择“从下列单元格中获取数值”选项，激活编辑框并单击单元格Ｂ２，同时勾选“单元格值更改时自动刷新”复选框；“参数２”的设置同“参数１”，只是获取数值的单元格为Ｂ３。
步骤８．单击“确定”按钮，返回“导入数据”对话框，单击“确定”按钮，即可导入外部数据，并且可以根据单元格Ｂ２、Ｂ３的参数变换，单元格Ａ４以下的数据会被及时刷新。但是，如果单元格Ｂ２、Ｂ３没有输入查询参数，查询表中将只会显示一行标题，并没有查询数据。
步骤９．在单元格Ｂ２中输入参数“Ｃ０００００２”，Ｂ３中输入参数“Ｆ”立即可见查询的外部数据。
步骤１０．要查询表中的Ａ２单元格输入“客户代码”，Ａ３单元格中输入“商品类别”并存Ｂ２、Ｂ３单元格中运用数据有效性功能，将“客户代码”和“商品类别”作为下拉列表的可选项，可以大大提高用户的查询速度。
|
|
|
来自： 昵称380475 > 《Excel 大全》
