1.2 Microsoft Query

Mr.林：小白，接下来我们来学习另外一个工具，它就是Microsoft Query。

Microsoft Query是用于将数据从外部数据源检索到其他Microsoft Office应用（特别是Microsoft Excel）中的一种程序。通过使用Microsoft Query可以从企业的数据库和文件中检索数据，而不必在Excel中重新输入需要分析的数据。也可以在每次更新数据库时，自动通过源数据库中的数据来更新Excel报表和汇总数据。

Microsoft Query可以从多种类型的数据源中检索数据，常见数据源类型包括：

★ Microsoft Office Access

★ Microsoft SQL Server

★ Microsoft FoxPro

★ Oracle

★ Microsoft Office Excel

★ 文本文件

在Microsoft Query中，可以使用“查询向导”创建简单的查询。我们知道在Microsoft Query或Access数据库中，查询是一种查找记录的方法，而这些记录回答了用户对数据库中存储的数据提出的特定问题。

小白：那么Microsoft Query与Access数据库有何区别呢？

Mr.林：Microsoft Query与Access数据库既有相似之处，也有不同之处。

两者的数据处理与分析功能类似，都可以采用SQL语句进行查询处理，连Microsoft Query的查询操作界面与Access数据库的都很类似，如图1-59所示，这样方便我们学习掌握Microsoft Query工具。

图1-59 Microsoft Query查询操作界面

Microsoft Query与Access数据库的不同之处主要体现在前者在性能方面稍逊于后者，以及Microsoft Query数据处理的灵活性及速度都不及Access数据库。

小白：什么时候该使用Microsoft Query工具？什么时候使用Access数据库呢？

Mr.林：以下几种情况可采用Microsoft Query进行处理分析。

★ 计算机没有安装Access或其他数据库软件。

★ 数据处理量相对较小，数据量在Excel可容纳的范围内。

★ 无须进行数据处理，或者数据处理操作较为简单。

★ 初学者学习SQL语句。

需要补充说明一点，如果数据量超过Excel容纳范围（如Excel 2007-2010版本最大记录数为1048576），不过只需得到统计分析结果，无须获取处理后的明细数据，也是可以使用Micro-soft Query工具进行数据处理与分析的。在计算机配置允许的情况下，Microsoft Query工具也可以处理千万级的数据，只是性能会变差，耗时较长。

小白：好的，具体如何使用Microsoft Query工具进行数据处理与分析呢？

Mr.林：使用Microsoft Query工具进行数据处理与分析，需要以下四步，如图1-60所示。

图1-60 Microsoft Query数据处理与分析步骤

1.2.1 数据导入

建立数据源连接数据

小白：如何建立数据源以连接数据呢？

Mr.林：仍以“订购明细”、“用户明细”文本数据为例，我们需要了解不同年龄段用户的产品购买偏好，要用到“订购明细”表的“用户ID”、“产品”字段，以及“用户明细”表的“年龄”字段，首先就是建立数据源以连接数据。

STEP 01 启动Excel 2010，在【数据】选项卡上的【获取外部数据】组中，单击【自其他来源】，然后单击【来自Microsoft Query】。

STEP 02 在弹出的【选择数据源】对话框中，在【数据库】选项卡下，双击【<新数据源>】，如图1-61所示。

图1-61 Microsoft Query【选择数据源】对话框

STEP 03 弹出【创建新数据源】对话框，如图1-62所示。

图1-62 Microsoft Query【创建新数据源】对话框

★ 在第1项输入框中输入自定义的数据源名称，以标识和区分数据源，本例输入“数据”。

★ 在第2项下拉框中，单击选择作为数据源的数据库类型的驱动程序，本例选择“Microsoft Text Driver (*.txt; *.csv)”项。

★ 在第3项设置数据源所在文件夹路径，本例选择“D：\数据”。

★ 在第4项中可选择其中一个文本文件作为数据源选定默认表，本例选择“订购明细.txt”，单击【确定】按钮。

STEP 04 在【创建新数据源】对话框中设置完相应参数后，单击【确定】按钮，返回【选择数据源】对话框，这时在【数据库】选项卡下就会出现一个名为“数据”的数据源，如图1-61所示，这时数据源已经设置好。

小白：Excel数据源如何建立连接？

Mr.林：Excel数据源建立连接与文本数据源建立连接的操作步骤类似，只是选择的驱动程序不一样，可以只设置Excel 2003版以下的数据格式，也可以设置Excel 2007—2010版的数据格式。你可以事后进行练习，我在此就不再重复阐述。

小白：再请教个问题，图1-61所示的【选择数据源】对话框中，可以直接选择现有的“Ex-cel Files*”、“Text Files*”等现有数据源么？

Mr.林：可以的，步骤也类似，但是每次打开同一个Excel、文本文件，都需要重新操作一次，如果建立好数据源，以后就可以直接调用，所以需要根据自己的需求选择建立一次性数据源还是永久数据源。

小白：好的，明白了。

使用“查询向导”选择数据

Mr.林：建立好数据源，接下来就使用“查询向导”选择我们所需要的数据。

STEP 01 返回【选择数据源】对话框，确认对话框中下方的【使用“查询向导”创建/编辑查询】项被勾选上，双击【数据库】选项卡下刚建好的名为“数据”的数据源，如图1-61所示。

STEP 02 在弹出的【查询向导—选择列】对话框中，将所需的“订购明细”表的“用户ID”、“产品”、“数量”字段，以及“用户明细”表的“年龄”字段，分别从左边【可用的表和列】中选择并移至右边的【查询结果中的列】，单击【下一步】按钮，如图1-63所示。

图1-63 Microsoft Query【查询向导 — 选择列】对话框

STEP 03 在弹出的警告提示框中（如图1-64所示），单击【确定】按钮，将出现如图1-65所示的Microsoft Query查询操作界面。

图1-64 Microsoft Query警告提示框

图1-65 Microsoft Query查询操作界面1

Mr.林：小白，你来看一下，从图1-65所示的Microsoft Query查询操作界面可以发现什么？

小白认真地看了一会后兴奋地跳起来：Mr.林，我发现了：

①“订购明细”与“用户明细”两个表之间没有建立关系。

②“用户ID”与“年龄”没有对应，出现同一个用户有不同年龄信息。

③ Microsoft Query查询操作界面最下方有提示“将字段从某一表拖曳到另一表的相关字段”。

Mr.林：没错，基本上就是这些，这一切都源于没有对“订购明细”与“用户明细”两个表建立关系，在第三步操作中，如图1-64所示，就已经提示需要拖动字段进行人工连接。所以接下来就要建立两表关系。

STEP 04 将“用户明细”表中的“用户ID”字段拖曳到“订购明细”表的“用户ID”字段，得到如图1-66所示的结果。

图1-66 Microsoft Query查询操作界面2

Mr.林：Microsoft Query工具中，用拖动字段的方式建立各表之间的连接关系默认是内连接（INNER JOIN），即选择两个表中关键字段相匹配的记录。

在Microsoft Query工具中，单击菜单中的【表】，选择【连接】项，即可得到如图1-67所示的连接关系对话框，连接内容中的1、2、3项分别对应内连接（INNER JOIN）、左连接（LEFT JOIN）、右连接（RIGHT JOIN）三种关系，这与Access数据库中的连接关系是一致的。

图1-67 Microsoft Query连接关系对话框

到此，我们就完成了数据导入的工作，后续还要进行相应的数据处理工作。

1.2.2 数据处理

Mr.林：之前我们已经学习了在Access数据库中如何用SQL语句进行数据处理，所以我们在Microsoft Query工具中也采用SQL语句进行数据处理。小白，你有没发现菜单栏中有个SQL按钮？

小白：发现了。

Mr.林：用鼠标单击它，见证奇迹的时刻又来了——刚才文本数据导入的操作同样被转化为SQL语句，并在SQL语句输入对话框中显示，如图1-68所示。

图1-68 Microsoft Query SQL语句输入对话框

小白：是呀！这个功能真好用。

Mr.林：现在我们只需修改SQL语句，采用PARTITION分组函数，增加年龄分组相应的语句，修改后的语句如下：

SELECT订购明细.用户ID,订购明细.产品,订购明细.数量,PARTITION(用户明细.年龄,1,100,5) AS
年龄分组
FROM订购明细.txt订购明细, 用户明细.txt用户明细
WHERE用户明细.用户ID = 订购明细.用户ID;

将编写好的SQL语句直接复制至SQL语句输入对话框中，单击【确定】按钮，Microsoft Query工具将运行SQL语句，结果如图1-69所示。

图1-69 Microsoft Query数据处理结果示例

Mr.林：这样就完成了年龄分组的数据处理工作。

数据合并、数据计算、数据分组、数据去重等其他的SQL语句数据处理方式，均与Access数据库中的基本一致，就不再重复阐述。

1.2.3 数据分析

SQL语句分析法

Mr.林：接下来要在刚才数据处理的基础上进行数据分析。

小白还没等Mr.林继续往下说就抢道：Mr.林，数据分析的操作让我来试试，在原有的SQL语句后加上按“年龄分组”和“产品”进行GROUP BY分组，并对“订购明细”表的“数量”字段进行求和计算，修改后的语句如下：

      SELECT PARTITION(用户明细.年龄,1,100,5) AS年龄分组, 订购明细.产品, SUM(订购明细.数量)
      AS产品数
      FROM订购明细.txt订购明细, 用户明细.txt用户明细
      WHERE用户明细.用户ID = 订购明细.用户ID
      GROUP BY PARTITION(用户明细.年龄,1,100,5),订购明细.产品;

将编写好的SQL语句直接复制至SQL语句输入对话框中，单击【确定】按钮，Microsoft Query工具将运行SQL语句，结果如图1-70所示。

图1-70 Microsoft Query数据分析结果示例

Mr.林满意地点了点头：小白，说得好，看来你已经掌握SQL语句了，并且能灵活运用，很不错。

小白笑眯眯地说：还要感谢Mr.林教导有方。

Mr.林：补充一点：我将数据处理与数据分析分为两个单独步骤，为的是让你对数据处理与数据分析过程能有更清晰的了解。我们知道数据处理的目的就是为了进行数据分析，如果对SQL语句熟悉的话，就不必分开编写，只要直接用最后数据分析的那条SQL语句就可以完成数据处理与分析工作。

小白：好的。

数据透视表法

Mr.林：刚才学了SQL语句分析法，我们再来学习另外一种方法，就是数据透视表法，这个工具已经教过，在这里就不细说了。这里要学习的是如何把处理好的数据作为数据透视表的数据源。

我在刚才年龄分组数据处理的基础上进行介绍。如图1-69所示，这是处理好的数据，现要将其导入Excel数据透视表中，作为分析所需的数据源，具体操作步骤如下。

STEP 01 单击菜单栏中的【文件】→【将数据返回Microsoft Excel】项，或者单击菜单栏中的【将数据返回Microsoft Excel】按钮（如图1-69所示），或者直接关闭Microsoft Query查询操作界面，以上三种操作方式都将弹出如图1-71所示的Excel【导入数据】对话框。

图1-71 Excel【导入数据】对话框

STEP 02 在弹出的【导入数据】对话框的【请选择该数据在工作簿中的显示方式】中选择【数据透视表】项，数据的放置位置采用默认方式，单击【确定】按钮，Excel会执行导入数据操作，并在Excel下方的状态栏中显示数据导入进度。

STEP 03 数据导入完毕后，则会创建一个空白的数据透视表框架，同时在其右侧显示一个【数据透视表字段列表】窗格。将【数据透视表字段列表】窗格中相应的字段拖动至【行标签】、【列标签】、【汇总数值】处，得到如图1-72所示的结果，与图1-70所示的结果是一样的，只是数据透视表结果可以更加灵活、方便地以交叉表方式显示。

图1-72 Excel数据透视表分析结果示例

小白：果然如此，这一招真实用。