excel MsQuery教程.doc

ExcelMsQuery教程在Excel中有很多隐藏的好东西，Microsoft Query工具就是其中一个，它能让我们在似乎走投无路的时候，却又绝境逢生11.1Microsoft Query介绍熟悉Excel中Vlookup函数的人都知道，Excel可以把另外一个数据表中的某列数据提取到当前数据表，相当于我们查字典的功能（如果你还不是很熟悉Vlookup函数可以参见本书4.2节的内容）。但是Vlookup函数应用有很多局限，其中之一就是很难处理两个表格之间的“一对多”关系。有如下图所示的两个表格，位于A1:D6单元格中的是员工名单数据，位于G1:I4单元格中的是员工培训记录数据。现在的任务是：分析一下对于左面表格中的所有员工，哪些员工曾经参加过培训，以及分别参加了哪些培训？对于Excel用户来讲，解决这个问题时会很自然地想起Vlookup函数，在单元格E2中输入公式“=VLOOKUP(A2,$G$1:$I$4,2,0)”，设置好相对引用和绝对引用，然后把鼠标移动到E2单元格的右下角，当鼠标变成黑色小十字时，双击鼠标把公式自动填充到E3:E6，计算结果如图上图所示。现在我们发现一个问题，工号为E003的张三明明参加了两门培训课程，怎么只显示了一门？Excel的开发者们大概意识到了Excel在处理两表之间数据关联问题的缺陷，专门提供了一个叫做Microsoft Query的数据分析和处理工具。下面演示一下如何应用Microsoft Query工具完成上述任务。注意：如果在你的Excel中找不到Microsoft Query工具，很可能是你的Excel安装不完整，请完整安装Excel。要使用Microsoft Query工具，首先需要保证数据以规范的数据表格形式保存在Excel工作表中。这里所谓的“规范的数据表格”是指：（1）数据表格的左上角在A1单元格；（2）表格第一行为列标题；（3）从表格第二行开始为具体数据。其实以上要求一点也不过分，我们日常存储在Excel工作表中的数据大多都是这个样子。如图所示的这个Excel工作簿中包含了“员工名单”和“培训记录”两个工作表，正是以“规范的数据表格”的形式存在的。下面演示一下如何使用Excel中的Microsoft Query工具来完成上面这个使用Excel函数和公式不太容易完成的任务。首先，选择“数据获取外部数据自其他来源来自Microsoft Query”命令，弹出“选择数据源”对话框。在“数据源选择”对话框的“数据库”标签中，选择“Excel Files*”选项，注意在对话框的下部选择“使用查询向导创建/编辑查询”复选框，然后单击“确定”按钮，如图所示。此时弹出“选择工作簿”对话框。在“选择工作簿”对话框中，选择我们需要用到的Excel工作簿，这里用到的Excel文件位于“D:ExcelAccessPowerSkillMS_QUERY_培训记录.xlsx”。找到该文件并选中它，然后单击“确定”按钮，如图所示。然后弹出一个提示对话框，提示“数据源中没有包含可见的表格。”不要被这个对话框的内容所迷惑，它只是说明数据源中没有包含“可见”的表格，而Excel工作表在默认情况下是不可见的。在这一步我们只需单击“确定”按钮即可，如图所示，此时弹出“查询向导选择列”对话框。在“查询向导选择列”对话框中左边的“可用的表和列”中没有任何表和列的信息，不用着急，我们只需单击对话框下部的“选项”按钮，如图所示，接着打开“表选项”对话框。在“表选项”对话框中勾选“系统表”复选框，然后单击“确定”按钮，如图所示。此时在“查询向导选择列”对话框的左边“可用的表和列”中就会出现Excel工作簿中的两个工作表的名称。原来Excel把自己的工作表叫做“系统表”。因为我们要从“员工名单”出发，到另外一个表“培训记录”中查找每个员工的培训情况，所以这里先选择“员工名单”数据表格，然后单击对话框中右箭头按钮，把表格加到“查询结果中的列”列表框中。单击“下一步”按钮，出现“查询向导选择列”对话框，如图所示。在“查询向导筛选数据”对话框中，我们不对数据表中的数据做任何筛选，直接单击“下一步”按钮，如图11-9所示，弹出“查询向导排序顺序”对话框。在“查询向导排序顺序”对话框中，我们不对数据表中的数据做任何排序，直接单击“下一步”按钮，接着弹出“查询向导完成”对话框。在弹出“查询向导完成”对话框中，选择“在Microsoft Query中查看数据或编辑查询”单选框，然后单击“完成”按钮，如图所示。此时才真正地进入了期盼已久的Microsoft Query工具界面。在Microsoft Query工具的上半部分，是我们刚才选择的“员工名单”数据表的结构图，在Microsoft Query工具的下半部分是当前处理后的数据预览。因为我们没有对表格进行过任何筛选和排序操作，因此，Microsoft Query工具的下半部分显示的是我们所选的数据表的全部原始数据，如图所示。因为我们要从“员工名单”出发，从“培训记录”中查询每个员工的培训情况，因此我们需要在Microsoft Query工具上方增加另外一个数据表：“培训记录”。选择Microsoft Query工具菜单中的“表添加表”命令，弹出“添加表”对话框。在弹出的“添加表”对话框中，选择“培训记录”工作表，然后单击“添加”按钮，如图所示。添加第二个工作表后，Microsoft Query工具界面如图所示。用鼠标选中“培训记录”表格结构图中的“*”，将其拖曳到Microsoft Query工具下方的数据表格的最右边，松开鼠标，效果如图所示。让我们仔细观察一下Microsoft Query工具下方的数据，这些数据是由“员工名单”数据表中的每一行数据和“培训记录”数据表中的每一行数据首尾连接的所有可能组合组成的数据表。通过查看原始数据我们知道，“员工名单”数据表共有5行数据，“培训记录”数据表中共有3条数据，因此，在Microsoft Query工具下方的数据表中共有53=15条数据。这就是当我们把两个数据表放在Microsoft Query工具上方，不对两个表格施加任何关系操作（联接）时，Microsoft Query工具对两个表格的数据的处理结果，这个处理结果中只有部分数据是我们所需要的。现在我们要对这两个数据表施加关系操作。用鼠标选中左边“员工名单”数据表中的“员工工号”，将其拖曳到右表“培训记录”中的“员工工号”上面，然后松开鼠标。这时在两个表的“员工工号”字段之间出现了一条两端带有细小节点的联接线。注意该联接线的两端节点大小一样，这种节点表示对前面一个步骤所生成的53行数据表进行这样的筛选：检查53行数据表中的每一条记录，只有在左表“员工名单”中的“员工工号”和右表“培训记录”中的“员工工号”相等时才保留在运算结果中。通过对以上15条记录逐条分析，发现只有三条记录符合条件，Microsoft Query运算结果如图所示。到现在为止，Microsoft Query的运算结果给出了“谁参加了培训，参加的是什么培训”问题的答案。如果我们还想在Microsoft Query运算结果的数据表格中“同时”显示出哪些人没有参加培训，怎么办呢？可以通过改变两个表格之间的关联方式来解决这问题。双击两表之间的连线，此时会弹出“连接”对话框。在“连接”对话框中，有以下三个选项。选项1：仅“员工名单$”和“培训记录$”的部分记录，其中员工名单$.员工工号=培训记录$.员工工号。选项2：“员工名单$”的所有值和“培训记录$”的部分记录，其中，其中员工名单$.员工工号=培训记录$.员工工号。选项3：“培训记录$”的所有值和“员工名单$”的部分记录，其中，其中员工名单$.员工工号=培训记录$.员工工号。注意：Microsoft Query在称呼Excel文件中的工作表时，在工作表名称的末尾加了一个“$”符号，其实就是Excel文件中对应的工作表。在上一个问题中，我们的操作使用的是3个选项中的默认选项1，也就是仅“员工名单$”和“培训记录$”的部分记录，其中员工名单$.员工工号=培训记录$.员工工号。现在新的问题的要求是：我们除了要求在Microsoft Query运算结果中显示哪些人参加了培训以外，还要同时显示哪些人“没有”参加培训，因此选择选项2：“员工名单$”的所有值和“培训记录$”的部分记录，其中，员工名单$.员工工号=培训记录$.员工工号，选项设置如图所示。当我们选择“员工名单$的所有值和培训记录$的部分记录，其中员工名单$. 员工工号=培训记录$. 员工工号”选项时，Microsoft Query运算逻辑如下：Microsoft Query用左表中的每一行数据去扫描右表中的所有行数据，如果左表中的某行数据和右表中某行数据在所连接的列字段中的内容完全相等，则把左右两个数据表中的对应数据行首尾连接保留在Microsoft Query计算结果中，否则忽略。注意：Microsoft Query的分析计算并没有到此为止；如果Microsoft Query发现左表中的某行数据在扫描完右表中的所有数据行后，最终也没有找到联接字段相等的数据行（即：没有在右表中找到连接字段匹配的行），也要把左表中的该行数据原封不动地保留在Microsoft Query的计算结果中。比如在下图所示的第三行数据，工号为E004的员工在扫描比对右表“培训记录”中的所有行后，最终也没有发现在右表“培训记录”中的“员工工号”中有和E004相等的数据行，根据Microsoft Query连接选项2的计算要求，即使在右表中没有找到匹配的行，也要把左表中工号为E004的员工数据行保留在Microsoft Query运算结果中。在Microsoft Query工具的“连接”对话框中的选项2和选项3意思基本相同，只是把左右两表的顺序颠倒一下而已。好了，我们借助Excel中的Microsoft Query工具的帮助，完成了Excel公式和函数难以完成的任务，得到了所需要的分析结果，如上图所示，正确使用Microsoft Query工具的关键是正确理解Microsoft Query工具的工作原理，熟练掌握Microsoft Query工具可能需要一些动手试验，不断观察Microsoft Query工具的结果是否和自己预期的一致。正确地完成了Microsoft Query工具的查询操作后，接下来的任务是如何将分析结果返回到Excel界面。单击Microsoft Query工具的“将数据返回到Excel”按钮，如图所示（按钮上面有一个开门的小图标），此时会弹出一个“导入数据”的对话框。在“导入数据”对话框的“请选择数据在工作簿中的显示方式”选项下，选择“表”单选框，在“数据的放置位置”下，选择“现有工作表”单选框，然后单击下面的单元格位置选择按钮，在弹出的单元格位置选取框中选择“员工名单”工作表的F1单元格，单击“确定”按钮，如图所示。这时，Excel把我们用Microsoft Query分析的结果以Excel数据的形式导入到了Excel工作表中。从下图中可以看到工号为E003的员工参加了两项培训课程，如图所示。另