2.3　以多个多维表格数据创建数据透视表

在利用重合并计算数据区域透视表来汇总分析多个工作表数据时，一个基本的要求就是每个工作表数据区域只能有一列文本，并且这列文本必须放在数据区域的第一列，从第二列开始都必须是数字了，得到的数据透视表中也仅仅有“行”“列”“值”和“页”4个字段。但是，实际工作中，要合并计算的每个工作表一般都会有多列文本，此时就无法使用多重合并计算数据区域透视表了。

要解决这样的多个工作表汇总分析问题，有很多方法可以使用，但其中最简单的方法是利用导入数据+SQL语句的方法。下面我们详细介绍这种方法。

2.3.1　利用现有连接+SQL数据查询汇总多个多维工作表

利用现有连接+SQL数据查询的方法，实际上就是将各个工作表当成数据库的表进行连接查询，得到一个查询数据集，在这个数据集中保存有各个工作表的所有数据，然后再以这个查询记录集的数据为基础制作普通的数据透视表，从而得到需要的报表。

因此，为了能够利用现有连接+SQL数据查询创建多列文本情况下的多个工作表数据的数据透视表，每个工作表的第一行最好是数据区域的列标题。如果每个工作表的第一行不是数据区域的列标题，那么就需要先对数据区域定义名称，或者在SQL语句中进行特殊处理。

案例2-4

图2-37是各个地区的销售数据流水表，分别保存在各个工作表中，而且每个工作表数据会随时增加或修改，现在要把这几个地区的销售数据进行汇总分析。

由于各个地区工作表数据的第二列数据是文本，就不能使用多重合并计算数据区域透视表了。下面是使用现有连接+SQL数据查询的方法来进行汇总分析的具体步骤。

01　在任何一个工作表中，单击“数据”选项卡中的“现有连接”命令，如图2-38所示，打开“现在连接”对话框，如图2-39所示。

02　单击对话框左下角的“浏览更多”按钮，打开“选取数据源”对话框，然后从保存本文件的文件夹里选择该文件，如图2-40所示。

03　单击“打开”按钮，打开“选择表格”对话框，从表格列表中任选一表格，并要特别注意选择下边的“数据首行包含列标题”复选框，如图2-41所示。

04　单击“确定”按钮，打开“导入数据”对话框，选择“数据透视表”和“新工作表”选项按钮，如图2-42所示。

05　单击“属性”按钮，打开“连接属性”对话框，切换到“定义”选项卡，然后在“命令文本”框中输入下面的SQL语句，如图2-43所示。

注意：

在查询语句的关键字select、union、all、as和from等的前后必须有至少一个空格，这些关键字的字母大小写无关紧要。

使用星号（*）表示工作表的所有字段，不过这要求各个工作表数据列的左右顺序必须完全一样，而且列数也必须相同。

不同工作表的SQL查询语句是基本相同的，唯一区别是各自生成地区语句的部分及查询表名，如“'北京' as城市”，“from [华北$]”。工作表名后要写符号“$”，并且用方括号括起来。而各个工作表的查询语句之间用“union all”连接起来。这样，上述语句的执行结果就生成了一个查询数据集，它是6个工作表所有数据的集合，并且还有一个新字段“地区”，以区别开各个工作表的数据。

06　单击“确定”按钮，返回到“导入数据”对话框，确认选择了“数据透视表”和“新工作表”选项按钮（参阅图2-42）。然后单击“完成”按钮，就得到如图2-44所示的还没有布局的空数据透视表框架。

07　对数据透视表进行布局，即可得到各种分析报表，图2-45就是一个简单的汇总分析报表。图2-46则是对日期按月进行组合后的汇总分析报表。

这种方法汇总得到的报表是动态的，也就是说，当各个工作表数据发生变化（数据改变、数据增加或减少）时，只需刷新数据透视表即可更新为最新的分析报告。

2.3.2　小知识：关于SQL语句

在众多的SQL语句中，SELECT语句是使用最频繁的。SELECT语句主要被用来对数据库进行查询并返回符合用户查询标准的结果数据。

SELECT语句有5个主要的子句，而FROM是唯一必需的子句。每一个子句有大量的选择项和参数等。

SELECT语句的语法格式如下。

SELECT语句的各项组成说明如下。

1.字段列表

字段列表指定多个字段名称，各个字段之间用逗号“，”分隔。也可以用星号“*”代替所有字段。当包含有多个表的字段时，可用“数据表名.字段名”来表示，即在字段名前表明该字段所在的数据表。

例如，“select日期，产品，销售量，销售额”就是选择数据表里的日期、产品、销售量和销售额这4个字段。

我们还可以在字段列表中自定义字段，比如前面介绍的SQL语句“select '华北' as地区，* from [华北$]”中，除了查询工作表“华北”中的字段日期、产品、销售量和销售额外，还自定义了一个工作表里没有的字段“地区”，并将“华北”作为该字段的数据。由于“华北”是一个文本，因此需要用单引号括起来。将某个数据保存在自定义字段的方法是利用AS属性词，即“'华北' as地区”。

2.FROM子句

FROM子句是一个必需子句，指定要查询的数据表，各个数据表之间用逗号“，”分隔。

但要注意，如果是查询工作簿的数据表，那么必须用方括号将工作表名括起来，并且在工作表名后要有符号（$）。

例如，“select * from [华北$]”就是查询工作表“华北”里的所有字段。

如果要查询的是Access数据库、SQL Server数据库等关系型数据库的数据表，那么在FROM后面直接写上数据表名即可。

3.WHERE子句

WHERE子句是一个可选子句，指定查询的条件，可以使用SQL运算符组成各种条件运算表达式。

例如，“WHERE部门='销售部'”就表示要查询的部门是“销售部”的数据。

如果条件值是数值，则直接写上数值，如“WHERE年龄>50”；如果条件值是字符串，则必须用单引号“'”括起来，如“WHERE部门='销售部'”。如果条件值是日期，则必须用井号“#”或单引号“'”括起来，如“WHERE日期=#2016-4-22#”。

4.GROUP BY子句

GROUP BY子句是一个可选子句，指定分组项目，使具有同样内容的记录（例如日期相同、部门相同、性别相同等）归类在一起。

例如，“GROUP BY性别”就表示将查询的数据按性别分组。

5.HAVING子句

HAVING子句是一个可选子句，功能与WHERE子句类似，只是必须与GROUP BY子句一起使用。

例如，要想只显示平均工资大于5000元的记录并按部门进行分组，则可以使用子句“GROUP BY部门HAVING AVG（工资总额）> 5000”。

6.ORDER BY子句

ORDER BY子句是一个可选子句，指定查询结果以何种方式排序。排序方式有两种：升序（ASC）和降序（DESC）。如果省略ASC和DESC，则表示按升序（ASC）排序。

例如，“ORDER BY姓名ASC”就表示查询的结果按姓氏拼音升序排序。而“ORDER BY工资总额，年龄DESC”则表示查询结果按“工资总额”从小到大升序排列，而“年龄”则按从大到小降序排序。

2.3.3　利用现有连接+SQL数据查询创建数据透视表应注意的问题

利用现有连接+SQL数据查询来创建数据透视表，是要注意一些问题的。下面就一些常常容易忽略的问题进行介绍。

1.各个工作表的字段有空白记录的情况

当各个工作表的字段有空白单元格时，可能会导致不能将各个工作表数据进行正确汇总计算。此时，应当对每个工作表的数据区域进行整理：即将所有空白单元格填充为数字0，然后再制作数据透视表。

2.各个工作表的第一行最好是数据区域的列标题

由于SQL语句是将各个工作表当成数据库的表进行查询的，因此最好使各个工作表的第一行就是数据区域的列标题，这样可以简化操作步骤，减少工作量。

3.各个工作表的数据列数不一样，数据列顺序也不一样

此时，就不能在SQL语句中使用星号（*）来代替所有字段，而必须写出完整的共有字段列表，比如几个工作表中，都有日期、产品、销售量、销售额这4列，就可以写如下的SQL语句：