excel MsQuery教程.docx

《excel MsQuery教程.docx》由会员分享，可在线阅读，更多相关《excel MsQuery教程.docx（12页珍藏版）》请在优知文库上搜索。

1、ExcelMsQuery教程在EXCel中有许多隐藏的好东西.MiCroSOftQUery工具就是其中一个，它能让我们在好像走投无路的时候.却又绝境逢生11.1MiCrOSoftQUery介绍熟识Excel中Vlookup函数的人都知道,Excel可以把另外一个数据表中的某列数据提取到当前数据表，相当于我们查字典的功能(假如你还不是很熟识V100kUP函数可以参见本书4.2节的内容)。但是Vlookup函数应用有许多局限,其中之一就是很难处理两个表格之间的“一对多,关系。有如下图所示的两个表格,位于A1:D6单元格中的是员工名单数据，位于Gl:14单元格中的是员工培训记录数据。现在的任务是：分

2、析一下对于左面表格中的全部员工,哪些员工曾经参与过培训.以及分别参与了哪些培训？22.用:TlOOIOPaZ林*M2lo)ABCDEFGHI1曼II号姓名懦BJS助硼州曲虹工号培皿期2E003张男3/3/1980曾琰MGTOOI:003品城顶110/10/20103EOOd李四另4/4/1980MAo枝木TECHJO11/11/20104E005三男5/5/1980fN/A:007林TKHM15E006隰女6/6/1980*M/A6E007便过文7/7/19801Utecwoi7f对于EXCel用户来讲，解决这个问题时会很自然地想起Vlookup函数，在单元格E2中输入公式-=V1.OOKUP

3、(A2.SGS1.SS4,2,0),设置好相对引用和肯定引用，然后把副标移动到E2单元格的右下角，当鼠标变成黑色小十字时,双击鼠标把公式自动填充到E3:E6,计算结果如图上图所示。现在我们发觉一个问题,工号为E003的张三明明参与了两门培训课程,怎么只显示了一门？Excel的开发青们也许意识到了Excel在处理两表之间数据关联问题的缺陷，特地供应了一个叫做MiCroSOftQuery的数据分析和处理工具。下面演示一下如何应用MierOSoftQUery工具完成上述任务。留意：假如在你的Excel中找不到MicrosoftQuery工具,很可能是你的Excel安装不完整.请完整安装Excel.要

4、运用MicrosoftQuery工具，首先须要保证数据以规范的数据表格形式保存在Excel工作表中。这里所谓的“规范的数据表格.是指：(1)数据表格的左上角在Al单元格；(2)表格第一行为列标题；(3)从表格其次行起先为详细数据。其实以上要求一点也不过分，我们日常存储在EXCel工作表中的数据大多都是这个样子。如图所示的这个EXCel工作簿中包含了“员工名单”和“培训记录”两个工作表，正是以“规范的数据表格“的形式存在的。在“选择工作簿对话框中.选择我们须要用到的Excel工作簿，这里用到的Excel文件位于“D:ExcelAccessP。WerSkiIlMS_QUERY_培训记录.XlsxZ

5、找到该文件并选中它，然后单击“确定“按钮，如图所示。然后弹出一个提示对话框，提示“数据源中没有包含可见的表格J不要被这个对话框的内容所迷惑，它只是说明数据源中没有包含可见的表格,而EXCel工作表在默认状况下是不行见的.在这一步我们只需单击“确定”按钮即可,如图所示，此时弹出“查询向导一选择列”对话框。因为我们要从员工名单”动身，到另外一个表培训记录”中查找每个员工的培训状况，所以这里先选择”员工名单”数据表格,然后单击对话框中右箭头按钮，把表格加到“查询结果中的列.列表框中。单击“下一步”按钮，出现“查询向导一选择列对话框，如图所示。在查询向导一筛选数据.对话框中，我们不对数据表中的数据做任

6、何筛选，干脆单击“下一步”此时才真正地进入了期盼已久的MicrosoftQuery工具界面。在MicrosoftQuery工具的上半部分,是我们刚才选择的员工名单数据表的结构图，在MicrosoftQuery工具的下半部分是当前处理后的数据预览。因为我们没有对表格进行过任何筛选和排序操作，因此，MicrosoftQuery工具的下半部分显示的是我们所选的数据表的全部原始数据，如图所示。炉GmE加2，萩，hs他B:1A!JIl号2EOo31011:WqI-aa,?.0if.*wft.因为我们要从二员工名单”动身从培训记录.中查询每个员工的培训状况因此我们须要在MicrosoftQuery工具上方

7、增加另外一个数据表：培训记录，选择MicrosoftQuery工具菜单中的“表T添加表“吩附，弹出“添加表”对话框。在弹出的“添加表”对话框中，选择.培训记录”工作表,然后单击添加按钮，如图所示。寸方当前者*m.I-添加其次个工作表后,IEWMicrosoftQuery工具界面如图所示。曷六学*阴卷为？IS3炉T套评,抬点“金WWcoprat9M三8-一KCWj27一M一MZr4T一aA:二IM*.3YlffCrA9一*-1CCK一M59S，III三I1A，彳IlU131415U17JU1$Xyy皿愠WMVIMVVlWCu*B.aj：.-m3rStor5RaBTX5蟆,笺0M%c*/m三H*汴

8、,K2三3阶：KMC肝即（r:0Wm懒M*7tt飘:Km息二二q同闾ImgC4,W1CVI1三更三!0iini/ax；.尚魁七；CIl削秘通过查看原始数据我们知道，员工名单”数据表共有5行数据，培训记录.数据表中共有3条数据，因此，在MicrosoftQuery工具下方的数据表中共有5x3=15条数据。这就是当我们把两个数据表放在MiCroSOftQUery工具上方，不对两个表格施加任何关系操作（联接）时，MicrosoftQuery工具对两个表格的数据的处理结果，这个处理结果中只有部分数据是我们所须要的。现在我们要对这两个数据表施加关系操作。用鼠标选中左边“员工名单数据表中的“员工工号二将其

9、拖曳到右表“培训记录”中的“员工工号”上面，然后松开鼠标。这时在两个表的“员工工号”字段之间出现了一条两端带有细小节点的联接线。留意该联接线的两端节点大小一样.这种节点表示对前面一个步骤所生成的53行数据表进行这样的筛选：检查53行数据表中的每一条记录,只有在左表员工名单”中的员工工号”和右表“培训记录”中的“员工工号”相等时才保留在运算结果中.通过对以上15条记录逐条分析.发觉只有三条记录符合条件.MicrosoftQuery运算结果如图所示。到现在为止，MicrosoftQuery的运算结果给出了一谁参与了培训，参与的是什么培训”问题的答案。假如我们还想在MiCroSOftQUery运算结

10、果的数据表格中“同时”显示出哪些人没有参与培训，怎么办呢？可以通过变更两个表格之间的关联方式来解决这问题。双击两表之间的连线,此时会弹出“连接”对话框。在1连接”对话框中,有以下三个选项。选项1仅“员工名单$和“培训记录$的部分记录，其中员工名单$.员工工号=培训记录$.员工工号。选项2：“员工名单$”的全部值和培训记录$的部分记录，其中，其中员工名单*员工工号=培训记录$.员工工号。选项3：”培训记录S”的全部值和“员工名单$的部分记录，其中，其中员工名单$.员工工号=培训记录$.员工工号。留意：MlCrOsOftQUery在称呼EXCel文件中的工作表时，在工作表名称的末尾加了一个飞”符号

11、，其实就是EXCel文件中对应的工作表。在上一个问题中，我们的操作运用的是3个选项中的默认选项1,也就是仅“员工名单$“和.培训记录$”的部分记录，其中员工名单$.员工工号=培训记录$.员工工号。现在新的问题的要求是：我们除了要求在MicrosoftQUery运算结果中显示哪些人参与了培训以外，还要同时显示哪些人“没有参与培训，因此选择选项2:员工名单$的全部值和“培训记录$,的部分记录，其中，员工名单$.员工工号=培训记录$.员工工号,选项设置如图所示。当我们选择“员工名单$的全部值和培训记录$的部分记录，其中员工名单$员工工号=培训记录$.员工工号”选项时，MierosoftQuery运算

12、逻辑如下：MiCrOSOftQUery用左表中的每一行数据去扫描右表中的全部行数据，假如左表中的某行数据和右表中某行数据在所连接的列字段中的内容完全相等,则把左右两个数据表中的对应数据行首尾连接保留在MiCroSOflQUery计算结果中，否则忽视。留意：MieroSoflQUery的分析计算并没有到此为止；假如MiCrOSOftQUery发觉左表中的某行数据在扫描完右表中的全部数据行后，最终也没有找到联接字段相等的数据行（即：没有在右表中找到连接字段匹配的行），也要把左表中的该行数据原封不动地保留在MaOSoflQUery的计算结果中。比如在下图所示的第三行数据，工号为EOO4的员工在扫描比对右表“培训记录”中的全部行后，银终也没有发觉在右表“培训记录.中的员工工号中有和E004相等的数据行，依据Mier。SOftQUery连接选项2的计算要求，即使在右表中没有找到匹配的行，也要把左表中工号为E004的员工数据行保留在MiCrOSoftQUery运算结果中。IiczorttQMry工Acci三的员，二三t电；-：扇&1BS1）三Q:琳G分Ij83惭/郎回函引碓画画册!员H:S三SS*AAi-M五颜SBK黑片-w三)g-n1111t3to9TWXlHrnwV7inWIttOSV1000&19rlwranft-11TBo靛网3维多中山皇式赫的在MicrosoftQuery工具