在Excel中用Microsoft Query导入外部数据

在Ｅｘｃｅｌ中用Ｍｉｃｒｏｓｏｆｔ Ｑｕｅｒｙ导入外部数据

[摘 要] Excel拥有强大的数据管理､分析能力,借助Microsoft Query能够访问多种类型的外部数据源,可从外部数据源选择数据,将该数据导入到工作表中,以及根据需要刷新数据｡本文借助于罗斯文数据库进行分析,讲述Excel与外部Access数据源连接的操作方法及技巧｡

[关键词] Excel;Query;外部数据;数据源

Query是Excel中的一个可选功能,如果Office安装过程中选择了典型安装,Microsoft Query就不会安装到计算机中｡当用户第一次在Excel中使用【数据】│【导入外部数据】│【新建数据库查询】菜单项时,系统将提示安装Query,插入Office安装盘,单击【确定】按钮即可｡Excel访问外部数据源的操作方法因数据源类型的不同而有差异,但操作方法和步骤基本相同｡

一､外部数据､Query及Excel三者关系

Query是Excel表和数据源之间的桥梁,Query可访问的数据库文件包括MicrosoftAccess文件､Microsoft SQL Server文件､Microsoft FoxPro文件､Oracle文件､文本数据库文件和网页文件等｡如果Query所要访问的数据源不在本地计算机中,则需要向外部数据库管理员申请密码､用户权限或其他有关连接数据库的信息｡Query 使用数据源连接到外部数据库并显示可用的数据,通过创建查询将数据返回到 Excel｡数据源是存储起来的一组信息,使用Query设置数据源时,可为数据源指定名称,然后提供数据库或服务器的名称和位置､数据库的类型,以及用户的登录和密码信息,该信息还包括ODBC驱动程序或数据源驱动程序的名称｡查询读取数据库前,要确切掌握指定数据库中所使用的各表的文件名､字段名的含义等,才能正确获取所需要数据｡外部数据､Query及Excel三者的关系如图1所示｡

二､使用Query查询向导导入外部数据

使用查询向导可以很轻松地从外部数据库的不同表和字段中选择数据并将

数据放在一起｡在创建新数据源对话框中指定数据源的名称､驱动程序并进行连接,选中“使用查询向导创建/编辑查询”复选框,确定后在陆续出现的多个查询向导对话框中依次指定所需要的列､筛选条件､排序方式及结果的保存方式等,就可将数据导入到Excel中｡

【例1】使用查询向导,查找在罗斯文数据库中购买苹果汁数量大于等于30件的货主以及购买的数量和单价｡

说明:罗斯文数据库是Office中的一个示例数据库,其默认位置为C:\\Program File\\Microsoft Office\\Office11

Samples\\Northwind.mdb｡

1. 创建名为“罗斯文公司数据1”的新数据源

打开Excel新建工作簿Book1,然后选择【数据】│【导入外部数据】│【新建数据库查询】菜单项,随即会弹出【选择数据源】对话框,切换到【数据库】选项卡中,然后在其下方的列表框中选择【】选项,并且选中【使用“查询向导”创建/编辑查询】复选框,单击【确定】按钮,在【创建新数据源】对话框中,输入数据源的名称【罗斯文公司数据1】､选择驱动程序【Microsoft Access Driver(*.mdb)】,单击【连接】按钮,弹出【ODBC Microsoft Access安装】对话框,单击【选择】按钮,弹出【选择数据库】对话框,找出存放数据库的文件即“Northwind.mdb”,并将其选中｡单击【确定】按钮返回【ODBC Microsoft Access安装】对话框｡单击【确定】按钮,回到【创建新数据源】对话框,单击【确定】按钮,回到【选择数据源】对话框,至此,在【数据库】选项卡的列表框中会显示出添加的数据源【罗斯文公司数据1】(如图2所示)｡

2. 查询向导操作

查询向导—选择列:在图2中,单击【确定】按钮,弹出【查询向导—选择列】对话框,单击【>】按钮分别将【订单】表中的“货主名称”,【产品】表中的“产品名称”,【订单明细】表中的“数量”和“单价”导入到【查询结果中的列】的列表框中｡

查询向导—筛选数据:单击【下一步】按钮,弹出【查询向导—筛选数据】对话框,这里可根据需要对数据进行筛选｡如先设置【产品名称 等于 苹果汁】,再设置【数量 大于或等于 30】｡

查询向导—排序顺序:单击【下一步】按钮,弹出【查询向导—排序顺序】对话框,这里按【货主名称】的升序排列｡

查询向导—完成:单击【下一步】按钮,弹出【查询向导—完成】对话框,然后在【请确定下一步的动作】组合框中选中【将数据返回Microsoft Excel】单选按钮｡单击【完成】按钮,弹出【导入数据】对话框,然后在【数据的放置位置】组合框中选择【现有工作表】单选按钮,并在其下方的文本框中选择放置的

位置【=$A$1】,单击【确定】按钮,即可将符合条件的数据导入Excel(如图3所示)｡

三､直接使用Query导入外部数据

Query图形化界面更直观,直接使用Query可以创建查询向导不允许的复杂查询｡

【例2】不使用Query查询向导,直接通过Query在罗斯文数据库中查找购买苹果汁数量大于等于30件的货主以及购买的数量和单价,并且要求在Query中添加列字段“金额”｡

1.创建名为“罗斯文公司数据2”的新数据源

和向导查询步骤相同建立一个名为【罗斯文公司数据2】的数据源,注意撤选【使用“查询向导”创建/编辑查询】复选框(如图4所示)｡

2. 直接在Query下的操作

添加表:在图4中,选中【罗斯文公司数据2】,单击【确定】按钮,弹出【添加表】对话框,分别对【订单】表､【产品】表､【订单明细】表进行【添加】操作,添加完毕后单击【关闭】按钮｡

设置查找字段:分别双击【订单】表中的“货主名称”､【产品】表中的“产品名称”､【订单明细】表中的“数量”和“单价”｡也可直接将上述字段用鼠标拖动到Query的下方区域中｡

设置查询条件:选择【视图】│【条件】菜单项,或者单击工具栏上的【显示/隐藏条件】按钮,设置如图5所示条件｡

设置排序顺序:单击【货主名称】列,单击工具栏上的【升序】按钮,即按【货主名称】的升序排列｡

添加列字段【金额】:选择【记录】│【添加列】菜单项,弹出【添加列】对话框,进行如图6所示的设置,设置完成后,单击【添加】按钮,然后单击【关闭】按钮,即可完成【金额】列的添加｡

返回数据:单击按钮【将数据返回到Excel】,弹出【导入数据】对话框,然后在【数据的放置位置】组合框中选择【现有工作表】单选按钮,并在其下方的文本框中选择放置的位置【=$A$1】,单击【确定】按钮,完成导入过程｡

四､保存和调用查询

如果经常用到这个查询,则每次进行这些操作步骤比较麻烦,我们可将该查询过程保存起来｡查询的保存:如果使用Query查询向导导入数据,则在【查询向导—完成】对话框中单击【保存查询(S)…】按钮;直接使用Query导入数据,则可在Query界面中单击工具栏上的【保存】按钮或者单击【文件】│【保存】菜单项｡当第一次保存查询定义时,将打开【另存为】对话框,将查询保存为“.dqy”的查询文件｡

以后调用这个查询,只需要在Excel中执行【数据】│【导入外部数据】,找到保存的“.dqy”查询文件就可以了｡如果Query已经打开,单击Query的【文件】│【打开】菜单项,找到保存的“.dqy”文件,运行查询,然后将结果返回到Excel工作表中｡

五､在 Excel 中处理数据

使用Query或 Query查询向导创建查询,将数据返回到 Excel 工作表之后,数据会变为外部数据区域,可以对其进行格式设置和刷新｡Excel 提供了许多用于刷新查询的选项,包括每次打开工作簿时自动刷新数据和设置刷新频率刷新数据｡查询结果返回到Excel工作表中之后,就可以借助Excel的数据处理功能､图表处理功能等,对查询结果进行必要的格式化处理､数据统计或图表处理,以方便用户的使用｡

目前财务软件多使用Access或Server的数据库,在会计实务工作中对于不太熟悉Access或Server的财务人员来说,通过Query或Query查询向导,可以将数据返回到Excel的工作表进行分析管理｡

主要参考文献

[1] 华联科技. Excel电子表格[M]. 北京:机械工业出版社,2007.

[2] 杨文林. 会计电算化中级培训教材[M]. 北京:经济科学出版社,2004.

[3] Hlaudit. 还说Excel中的神秘武器——简述在Excel中打开Access数据[DB/OL].

http://bbs.iaudit.cn/ShowPost.asp?menu=Previous&ForumID=21&ThreadID=26886,2007-08-09.