第11章　Microsoft Query

第11章　Microsoft Query

在Excel中有很多隐藏的好东西，Microsoft Query工具就是其中一个，它能让我们在似乎走投无路的时候，却又绝境逢生……

11.1　Microsoft Query介绍

熟悉Excel中VLOOKUP函数的人都知道，Excel可以把另外一个数据表中的某列数据提取到当前数据表，相当于我们查字典的功能（如果你还不是很熟悉VLOOKUP函数可以参见本书4.2节的内容）。但是VLOOKUP函数应用有很多局限，其中之一就是很难处理两个表格之间的“一对多”关系。

下面有如图11-1所示的两个表格，位于A1:D6单元格中的是员工名单数据，位于G1:I4单元格中的是员工培训记录数据。现在的任务是：分析一下对于左面表格中的所有员工，哪些员工曾经参加过培训，以及分别参加了哪些培训？

图11-1

对于Excel用户来讲，解决这个问题时会很自然地想起VLOOKUP函数，在单元格E2中输入公式“=VLOOKUP(A2,$G$1:$I$4,2,0)”，设置好相对引用和绝对引用，然后把鼠标移动到E2单元格的右下角，当鼠标变成黑色小十字时，双击鼠标把公式自动填充到E3:E6，计算结果如图11-1所示。

现在我们发现一个问题，工号为E003的张三明明参加了两门培训课程，怎么只显示了一门？Excel的开发者们大概意识到了Excel在处理两表之间数据关联问题的缺陷，专门提供了一个叫做Microsoft Query的数据分析和处理工具。

下面演示一下如何应用Microsoft Query工具完成上述任务。

注意：如果在你的Excel中找不到Microsoft Query工具，很可能是你的Excel安装不完整，请完整安装Excel。

要使用Microsoft Query工具，首先需要保证数据以规范的数据表格形式保存在Excel工作表中。这里所谓的“规范的数据表格”是指：

（1）数据表格的左上角在A1单元格；

（2）表格第一行为列标题；

（3）从表格第二行开始为具体数据。

其实以上要求一点也不过分，我们日常存储在Excel工作表中的数据大多都是这个样子。如图11-2所示的这个Excel工作簿中包含了“员工名单”和“培训记录”两个工作表，正是以“规范的数据表格”的形式存在的。

图11-2

下面演示一下如何使用Excel中的Microsoft Query工具来完成上面这个使用Excel函数和公式不太容易完成的任务。

首先，选择“数据→获取外部数据→自其他来源→来自Microsoft Query”命令，弹出“选择数据源”对话框。在“数据源选择”对话框的“数据库”标签中，选择“Excel Files*”选项，注意在对话框的下部选择“使用‘查询向导’创建/编辑查询”复选框，然后单击“确定”按钮，如图11-3所示。此时弹出“选择工作簿”对话框。

图11-3

在“选择工作簿”对话框中，选择我们需要用到的Excel工作簿，这里用到的Excel文件位于“D:\ExcelAccessPowerSkill\MSQUERY培训记录.xlsx”。找到该文件并选中它，然后单击“确定”按钮，如图11-4所示。

图11-4

然后弹出一个提示对话框，提示“数据源中没有包含可见的表格。”不要被这个对话框的内容所迷惑，它只是说明数据源中没有包含“可见”的表格，而Excel工作表在默认情况下是不可见的。在这一步我们只需单击“确定”按钮即可，如图11-5所示，此时弹出“查询向导—选择列”对话框。

图11-5

在“查询向导—选择列”对话框中左边的“可用的表和列”中没有任何表和列的信息，不用着急，我们只需单击对话框下部的“选项”按钮，如图11-6所示，接着打开“表选项”对话框。

图11-6

在“表选项”对话框中勾选“系统表”复选框，然后单击“确定”按钮，如图11-7所示。此时在“查询向导—选择列”对话框的左边“可用的表和列”中就会出现Excel工作簿中的两个工作表的名称。原来Excel把自己的工作表叫做“系统表”。

图11-7

因为我们要从“员工名单”出发，到另外一个表“培训记录”中查找每个员工的培训情况，所以这里先选择“员工名单”数据表格，然后单击对话框中右箭头按钮，把表格加到“查询结果中的列”列表框中。单击“下一步”按钮，出现“查询向导—选择列”对话框，如图11-8所示。

图11-8

在“查询向导—筛选数据”对话框中，我们不对数据表中的数据做任何筛选，直接单击“下一步”按钮，如图11-9所示，弹出“查询向导—排序顺序”对话框。

图11-9

在“查询向导—排序顺序”对话框中，我们不对数据表中的数据做任何排序，直接单击“下一步”按钮，接着弹出“查询向导—完成”对话框。

在弹出“查询向导—完成”对话框中，选择“在Microsoft Query中查看数据或编辑查询”单选框，然后单击“完成”按钮，如图11-10所示。

图11-10

此时才真正地进入了期盼已久的Microsoft Query工具界面。在Microsoft Query工具的上半部分，是我们刚才选择的“员工名单”数据表的结构图，在Microsoft Query工具的下半部分是当前处理后的数据预览。因为我们没有对表格进行过任何筛选和排序操作，因此，Microsoft Query工具的下半部分显示的是我们所选的数据表的全部原始数据，如图11-11所示。

图11-11

因为我们要从“员工名单”出发，从“培训记录”中查询每个员工的培训情况，因此我们需要在Microsoft Query工具上方增加另外一个数据表：“培训记录”。

选择Microsoft Query工具菜单中的“表→添加表”命令，弹出“添加表”对话框。

在弹出的“添加表”对话框中，选择“培训记录”工作表，然后单击“添加”按钮，如图11-12所示。

图11-12

添加第二个工作表后，Microsoft Query工具界面如图11-13所示。

图11-13

用鼠标选中“培训记录”表格结构图中的“*”，将其拖曳到Microsoft Query工具下方的数据表格的最右边，松开鼠标，效果如图11-14所示。

图11-14

让我们仔细观察一下Microsoft Query工具下方的数据，这些数据是由“员工名单”数据表中的每一行数据和“培训记录”数据表中的每一行数据首尾连接的所有可能组合组成的数据表。

通过查看原始数据我们知道，“员工名单”数据表共有5行数据，“培训记录”数据表中共有3条数据，因此，在Microsoft Query工具下方的数据表中共有5×3=15条数据。这就是当我们把两个数据表放在Microsoft Query工具上方，不对两个表格施加任何关系操作（联接）时，Microsoft Query工具对两个表格的数据的处理结果，这个处理结果中只有部分数据是我们所需要的。

现在我们要对这两个数据表施加关系操作。

用鼠标选中左边“员工名单”数据表中的“员工工号”，将其拖曳到右表“培训记录”中的“员工工号”上面，然后松开鼠标。这时在两个表的“员工工号”字段之间出现了一条两端带有细小节点的联接线。

注意该联接线的两端节点大小一样，这种节点表示对前面一个步骤所生成的5×3行数据表进行这样的筛选：检查5×3行数据表中的每一条记录，只有在左表“员工名单”中的“员工工号”和右表“培训记录”中的“员工工号”相等时才保留在运算结果中。通过对以上15条记录逐条分析，发现只有三条记录符合条件，Microsoft Query运算结果如图11-15所示。

图11-15

到现在为止，Microsoft Query的运算结果给出了“谁参加了培训，参加的是什么培训”问题的答案。

如果我们还想在Microsoft Query运算结果的数据表格中“同时”显示出哪些人没有参加培训，怎么办呢？可以通过改变两个表格之间的关联方式来解决这问题。

双击两表之间的连线，此时会弹出“连接”对话框。在“连接”对话框中，有以下三个选项。

选项1：仅“员工名单$”和“培训记录$”的部分记录，其中员工名单$.员工工号=培训记录$.员工工号。

选项2：“员工名单$”的所有值和“培训记录$”的部分记录，其中，其中员工名单$.员工工号=培训记录$.员工工号。

选项3：“培训记录$”的所有值和“员工名单$”的部分记录，其中，其中员工名单$.员工工号=培训记录$.员工工号。

注意：Microsoft Query在称呼Excel文件中的工作表时，在工作表名称的末尾加了一个“$”符号，其实就是Excel文件中对应的工作表。

在上一个问题中，我们的操作使用的是3个选项中的默认选项1，也就是仅“员工名单$”和“培训记录$”的部分记录，其中员工名单$.员工工号=培训记录$.员工工号。

现在新的问题的要求是：我们除了要求在Microsoft Query运算结果中显示哪些人参加了培训以外，还要同时显示哪些人“没有”参加培训，因此选择选项2：“员工名单$”的所有值和“培训记录$”的部分记录，其中，员工名单$.员工工号=培训记录$.员工工号，选项设置如图11-16所示。

图11-16

当我们选择“‘员工名单$’的所有值和‘培训记录$’的部分记录，其中员工名单$. 员工工号=培训记录$. 员工工号”选项时，Microsoft Query运算逻辑如下：Microsoft Query用左表中的每一行数据去扫描右表中的所有行数据，如果左表中的某行数据和右表中某行数据在所连接的列字段中的内容完全相等，则把左右两个数据表中的对应数据行首尾连接保留在Microsoft Query计算结果中，否则忽略。

注意：Microsoft Query的分析计算并没有到此为止；如果Microsoft Query发现左表中的某行数据在扫描完右表中的所有数据行后，最终也没有找到联接字段相等的数据行（即：没有在右表中找到连接字段匹配的行），也要把左表中的该行数据原封不动地保留在Microsoft Query的计算结果中。比如在图11-17所示的第三行数据，工号为E004的员工在扫描比对右表“培训记录”中的所有行后，最终也没有发现在右表“培训记录”中的“员工工号”中有和E004相等的数据行，根据Microsoft Query连接选项2的计算要求，即使在右表中没有找到匹配的行，也要把左表中工号为E004的员工数据行保留在Microsoft Query运算结果中。

图11-17

在Microsoft Query工具的“连接”对话框中的选项2和选项3意思基本相同，只是把左右两表的顺序颠倒一下而已。

好了，我们借助Excel中的Microsoft Query工具的帮助，完成了Excel公式和函数难以完成的任务，得到了所需要的分析结果，如图11-17所示，正确使用Microsoft Query工具的关键是正确理解Microsoft Query工具的工作原理，熟练掌握Microsoft Query工具可能需要一些动手试验，不断观察Microsoft Query工具的结果是否和自己预期的一致。

正确地完成了Microsoft Query工具的查询操作后，接下来的任务是如何将分析结果返回到Excel界面。

单击Microsoft Query工具的“将数据返回到Excel”按钮，如图11-18所示（按钮上面有一个开门的小图标），此时会弹出一个“导入数据”的对话框。

图11-18

在“导入数据”对话框的“请选择数据在工作簿中的显示方式”选项下，选择“表”单选框，在“数据的放置位置”下，选择“现有工作表”单选框，然后单击下面的单元格位置选择按钮，在弹出的单元格位置选取框中选择“员工名单”工作表的F1单元格，单击“确定”按钮，如图11-19所示。

图11-19

这时，Excel把我们用Microsoft Query分析的结果以Excel数据的形式导入到了Excel工作表中。从图11-20中可以看到工号为E003的员工参加了两项培训课程，如图11-20所示。

图11-20

另外这个分析结果是和数据源动态链接的，当我们在“员工名单”工作表中增加新员工或在“培训记录”工作表中增加新的培训记录后，把鼠标放在分析结果数据表中，单击Excel功能区的“设计”标签下面的“外部表数据”功能组下的“刷新”按钮，就可以立即得到已经更新了得分析结果！而在整个过程中你根本感觉不到Microsoft Query工具的存在！

利用Microsoft Query工具，我们就可以在不编写VBA代码、不脱离Excel应用环境、不借助IT部门的帮助的情况下，开发出改善工作效率改善的工具！

在本书的第二部分　，将会介绍如何用Access处理多表之间的关系运算，到那时相信读者对Microsoft Query这个工具的理解会更深刻。

11.2　Microsoft Query应用案例

在前面我们介绍了Excel中的Microsoft Query工具的用法，下面我们再深入介绍一个完整的案例：如何利用Microsoft Query工具构建“员工培训管理系统”。

为了灵活有效地进行员工的培训管理，我们需要按照如下方式设计培训管理相关的数据表格。

（1）把具体的培训项目按照培训内容分组为较大的培训类别，建立一个培训类别对应具体培训项目的对照表，具体数据见图11-21中间部分的“培训类别-培训项目”工作表。

（2）为每个员工制订培训计划。这里我们按培训类别为每位员工指定培训计划，即每个员工应该参加哪些类别的培训，具体数据见图11-21左边的“工号-培训类别”。

（3）随着培训计划的执行，我们随时记录每个员工已经完成的具体培训项目。具体数据见图11-21右侧的“培训记录录入表”。

图11-21

我们之所以这样安排培训数据，是为了使培训管理变得更加方便和灵活。比如：当有员工需要更改他的培训计划时，我们只需更改针对该员工的培训类别即可，比如当“A0001”员工不再需要类别“A-入职培训”时，我们只需从工作表“工号-培训类别”中把对员工“A0001”的“A-入职培训”培训类别删除，这样相当于删除了员工“A0001”的“A-入职培训”培训类别所对应的所有4个具体的培训项目。

当我需要调整某个培训类别里面的具体培训项目时，只需在工作表“培训类别-培训项目”中对相关培训项目进行修改即可，所作的修改对于所有指定了该培训类别的员工都起作用。比如，加入我们需要在“A-入职培训”培训类别中增加一项培训项目“A05-保密制度”，那么该修改将对所有指定了“A-入职培训”培训类别的员工起作用。

我们精心设计的这三个工作表事实上就是一个小型的培训管理系统数据库，我们可以在这个小小的培训管理系统里为每一位员工制订培训计划、管理培训内容、追踪培训计划达成情况等。

下面重点讨论如何利用Microsoft Query工具追踪培训计划的达成情况。

管理学上有一个PDCA循环的说法，PDCA就是Plan（计划），Do（执行），Check（检查），Act（行动）四个英文单词的首字母。它是进行一项管理活动需要遵循的逻辑。这里的A（Act）是指对检查（Check）的结果进行处理，把成功的经验标准化并避免失败的教训重现。

我们的培训管理当然也需要PDCA，而Microsoft Query工具可以在PDCA循环的Check（检查）过程中助我们一臂之力。

在某一个特定的阶段，如每个季度末，我们需要检查每一个员工的培训计划的达成情况，大家可以试着用Excel的公式和函数，利用现有数据解决这个问题，你会发现解决方案将是非常复杂。有了Excel中的Microsoft Query工具的帮助，一切变得不再那么复杂，下面我们演示一下整个培训系统的制作过程。

首先在Excel数据文件中插入一个新的工作表，命名为“分析结果”，该工作表用来存放Microsoft Query培训追踪的分析结果。然后选择“数据→获取外部数据→其他来源→来自Microsoft Query”命令。

在弹出的“选择数据源”对话框中，选择“数据库”标签，在下面的列表框中，选择“Excel Files*”选项，表示我们要使用Excel中的数据。在对话框下部勾选“使用‘查询向导’创建/编辑查询”复选框。然后单击“确定”按钮，如图11-22所示。

图11-22

在弹出的“选择工作簿”对话框中，选中我们当前使用的Excel工作簿数据文件的存储地址，然后单击“确定”按钮，如图11-23所示。

图11-23

在“查询向导-选择列”对话框中，如果发现该对话框左边的“可用的表和列”列表框中没有任何内容，不要着急，单击对话框下部的“选项”按钮。

在弹出的“表选项”对话框中勾选“系统表”复选框。然后单击“确定”按钮，如图11-24所示。

图11-24

现在在“查询向导-选择列”对话框左边的“可用的表和列”中出现了Excel工作簿中的所有工作表名称，如图11-25所示。

图11-25

选择“工号-培训类别”数据表，然后单击对话框中的右箭头按钮，我们会在对话框右侧的“查询结果中的列”中看到“工号-培训类别”中的所有列标题，如图11-26所示。

图11-26

单击“下一步”按钮。弹出“查询向导-筛选数据”对话框，使用该对话框可以对选中的工作表中的数据进行筛选，我们忽略这一步，直接单击“下一步”按钮。

在弹出“查询向导-排序顺序”对话框中，可以对我们所选择的工作表中的数据进行排序操作，在这里我们忽略这一步，直接单击“下一步”按钮。

此时出现“查询向导-完成”对话框。由于我们是想从数据表“工号-培训类别”出发，结合数据表“培训类别-培训项目”查看每位员工具体安排了哪些培训计划，需要使用Microsoft Query做进一步分析。因此，在该对话框的“请确定下一步的动作”中，选择“在Microsoft Query中查看数据或编辑查询”单选框，然后单击“完成”按钮，如图11-27所示。

图11-27

现在我们进入了Microsoft Query工具的界面，在该工具界面的下方显示出我们所选的数据表格的所有数据，我们的目的是从数据表“工号－培训类别”出发，结合数据表“培训类别－培训项目”查看每位员工具体安排了哪些培训计划。因此，我们需要引入另一个数据表“培训类别－培训项目”，该表格是培训类别和该培训类别下所对应的培训项目的对照表。

选择Microsoft Query工具上方的“表”菜单，在下拉菜单中选择“添加表”命令，如图11-28所示，此时弹出“添加表”对话框。

图11-28

在“添加表”对话框中，选择“培训类别－培训项目”数据表。然后单击“添加”按钮，接着单击“关闭”按钮，如图11-29所示。

图11-29

此时在Microsoft Query界面的上半部分出现了两个表格的结构视图，用鼠标将右边表格的中的“*”将其拖曳到Microsoft Query界面的下半部分，这时右边表格的所有字段也出现在了Microsoft Query下方的数据预览窗口中，如图11-30所示。

图11-30

现在我们注意到，Microsoft Query两个表格结构图之间没有任何连线，这表示我们是对这两个表格进行这样的操作：即用左表的每一行数据行和右表的每一行数据首尾相连地在Microsoft Query工具的运算结果中形成一行行新的数据。

在这个例子中仔细观察一下Microsoft Query工具下方的数据表，它是由“员工工号－培训类别”数据表中的每一行数据和“培训类别－培训项目”数据表中的每一条数据首尾相连地构成的所有组合。通过查看原始数据我们知道，“员工工号－培训类别”数据表共有16行数据，“培训类别－培训项目”数据表中共有10条数据，因此，在Microsoft Query工具下方的数据表中共有16×10＝160条数据。

以上就是当我们把两个数据表放在Microsoft Query工具上方，不对两个表格施加任何关系操作时，即两个表之间没有任何连线时，Microsoft Query对两个表格数据的处理结果。

我们现在对左右两个表施加关系操作，用鼠标选中左表的“培训类别”列，将其拖曳到右边表格的“培训类别”列上，释放鼠标后，我们会发现在左右两表之间“培训类别”的列上出现了一条联接线。这条联接线的出现表示我们已经对两表之间成功地施加了关系操作。

仔细观察两表之间的联接线，该联接线两端的节点大小是一致的，该操作对应的结果是对Microsoft Query上一步两表之间无联接线时的操作结果进行筛选，对于两表之间无连接线时操作结果中的每一行数据，只有所连接的列（培训类别）中的内容完全相等时才保留在运算结果中，其余的列将忽略。对应的Microsoft Query运算的结果如图11-31所示，共有58行数据满足条件。

同过查看原始工作表“工号－培训类别”中的数据得知，我们为员工“A0001”设置了“A－入职培训”和“B－管理培训”两类培训，又从原始工作表“培训类别－培训项目”中得知，“A－入职培训”对应“A01－企业文化”、“A02－行为准则”、“A03－公司福利”、“A04－考勤制度”4个培训项目；“B－管理培训”对应“B01－组织行为”、“B02－运营管理”、“B03－公司财务”3个培训项目。因此员工“A0001”应该参加的培训一共是7个培训项目。通过观察图11-31中的Microsoft Query运算结果知道与上面的分析完全相符。

图11-31

现在的问题是：假如我们为某个员工设置了某种培训类别，但是如果因为某种原因，没有在“培训类别-培训项目”中对该类别的培训设置对应的培训项目，在这种情况下，我们也希望在运算结果中包括这些有缺陷的数据，以便及时发现和更正，在“培训类别-培训项目”数据表中补全数据。这时我们该怎么做呢？

双击两表之间的连线，此时会弹出“连接”对话框，如图11-32所示。在“连接”对话框中，有以下三个选项：

图11-32

（1）仅“‘工号-培训类别$’”和“‘培训类别-培训项目$’”的部分记录，其中“工号-培训类别$”.培训类别=“培训类别-培训项目$”.培训类别。

（2）“‘工号-培训类别$’”的所有值和“‘培训类别-培训项目$’”的部分记录，其中“工号-培训类别$”.培训类别=“培训类别-培训项目$”.培训类别。

（3）“‘培训类别-培训项目$’”的所有值和“‘工号-培训类别$’”的部分记录，其中“工号-培训类别$”.培训类别=“培训类别-培训项目$”.培训类别。

对话框中默认的选项是第1个选项，也就是我们在前面设置两表之间联接线时自动生成的选项设置。现在我们要把默认选项修改为第2个选项以达到在“运算结果中同时包括这些有缺陷的数据”。

选择第2个选项：“‘工号-培训类别$’”的所有值和“‘培训类别-培训项目$’”的部分记录，其中“工号-培训类别$”.培训类别=“培训类别-培训项目$”.培训类别。然后单击“添加”按钮，如图11-33所示。

图11-33

选择完该选项后，我们发现两表之间的联接线发生了变化，联接线的右端出现了一个箭头。表示我们的运算是“包括左表的所有值和右表的部分记录，其中左表的连接列的内容=右表连接列的内容”。

以上设置的运算结果如图11-34所示，由于我们的数据目前没有缺陷，所以Microsoft Query工具的运算结果没有什么改变。

图11-34

好了，我们借助Excel中的Microsoft Query工具的帮助，完成了Excel公式和函数难以完成的任务，得到了所需要的分析结果，下面的任务是如何将分析结果返回到Excel界面。

单击Microsoft Query工具中的“将数据返回到Excel”按钮，如图11-37所示，此时会弹出“导入数据”对话框。

图11-35

在“导入数据”对话框的“请选择数据在工作簿中的显示方式”选项下，选择“表”单选框，在“数据的放置位置”选项下，选择“现有工作表”单选框，然后单击下方的单元格地址选择按钮，选择“分析结果”工作表上的A1单元格，最后单击“确定”按钮，如图11-36所示。

图11-36

这时，Excel把我们用Microsoft Query分析的结果以Excel表的形式导入到了Excel工作表中。在Microsoft Query的运算结果中显示了每个员工对应的培训类别和该培训类别下对应的培训课程，如图11-37所示。

图11-37

其实更不错的是这个分析结果是和数据源动态链接的，当我们在“工号-培训类别”工作表中为员工设置新的培训类别或则在“培训类别-培训课程”工作表中修改培训类别所对应的培训课程后，把鼠标放在我们的分析结果数据中，单击Excel的“设计”功能区选项下面的“外部表数据”功能组下面的“刷新”命令后，我们可以立即得到更新了的分析结果，如图11-38所示。而在整个过程中你根本感觉不到Microsoft Query工具的存在！

图11-38

注意：我们的任务还没有最终完成！我们需要知道每一个员工的培训计划的达成情况，即对每一个员工，根据为其设置的培训计划，我们要知道他已经参加了哪些培训，还有哪些培训没有参加。

由于Microsoft Query不支持超过两个表的关联操作，所以这一步操作在Excel中完成。在Excel中操作之前，我们需要对Excel中的“员工培训记录录入表”做一下处理。在工作表“培训记录录入表”最左边增加一列，列表题为“工号&培训项目”，列的内容是B列“工号”和C列“培训项目”中的文字的首尾相连，A2单元格中的公式为：“=B2&C2”，然后将其向下填充，如图11-39所示。

图11-39

我们只需使用VLOOKUP函数在Microsoft Query分析的结果的基础上，以“员工工号”和“培训项目”首尾连接起来形成的字符串作为VLOOKUP函数的第一个参数，在“培训记录录入表”中查找该员工对应的培训项目是否已经在工作表“培训记录录入表”中存在，如果存在，则表明该员工已经参加过了该培训项目，否则表示该员工尚未完成该培训，如图11-40所示。