12.2　导入多张数据列表创建数据透视表

运用导入外部数据结合“编辑OLE DB”查询中的SQL语句技术，可以轻而易举地对不同工作表，甚至不同工作簿中结构相同的多张数据列表进行合并汇总并创建动态的数据透视表，而不会出现多重合并计算数据区域创建数据透视表只会选择第一行作为行字段的限制。

12.2.1　汇总同一工作簿下多张数据列表记录

图12-15所示展示了某公司“一仓”、“二仓”和“三仓”3张数据列表，这些数据列表存放在D盘根目录下的“仓库入库表.xlsx”文件中。

img251a

图12-15　仓库入库数据列表

示例12.3　仓库入库表

如果希望对图12-15所示的3张仓库数据列表进行汇总分析，请参照以下步骤。

步　骤1img01

打开D盘根目录下的“仓库入库表.xlsx”文件，单击“汇总”工作表标签，在【数据】选项卡中单击【现有连接】按钮，弹出【现有连接】对话框，单击【浏览更多】按钮，打开【选取数据源】对话框，如图12-16所示。

img251b

图12-16　选取数据源

步　骤2img01

打开D盘根目录下的目标文件“仓库入库表.xlsx”，弹出【选择表格】对话框，如图12-17所示。

步　骤3img01

保持【选择表格】对话框的默认选择，单击【确定】按钮，在弹出的【导入数据】对话框中选择【数据透视表】单选钮，【数据的放置位置】选择【现有工作表】单选钮，然后单击“汇总”工作表中的A3单元格，再单击【属性】按钮打开【连接属性】对话框，单击【定义】选项卡，如图12-18所示。

img252a

图12-17　选择表格

img252b

图12-18　打开【连接属性】

步　骤4img01

清空【命名文本】文本框中的内容，输入以下SQL语句：

SELECT“一仓库”AS 仓库名称， FROM [一仓$] UNION ALL

SELECT“二仓库”AS 仓库名称， FROM [二仓$] UNION ALL

SELECT“三仓库”AS仓库名称，* FROM [三仓$]

单击【确定】按钮返回【导入数据】对话框，再次单击【确定】按钮创建一张空白数据透视表，如图12-19所示。

img252c

图12-19　创建空白的数据透视表

此语句的含义是：SQL语句第一部分“SELECT”—仓库”AS仓库名称，*FROM[一仓$]”表示返回一仓库数据列表的所有数据记录，“"一仓库"”作为插入的常量来标记不同的记录，然后对这个插入常量构成的字段利用AS别名标识符进行重命名字段名称，最后通过UNIONALL将每个班级的所有记录整合在一起，相当于将“一仓”、“二仓”和“三仓”3张工作表粘贴到一起。

步　骤5img01

在【数据透视表字段列表】中，将“日期”字段移动至【列标签】区域内，在数据透视表中按【步长】为【月】对“日期”字段进行分组组合，将“物料编码”和“单位字段移动至【行标签】区域内，将“仓库名称”字段移动至【报表筛选】区域内，将“数量”字段移动至【Σ数值】区域内，然后对数据透视表进行美化，完成后的数据透视表如图12-20所示。

img253a

图12-20　汇总后的数据透视表

12.2.2　汇总不同工作簿下多张数据列表记录

图12-21所示展示了2011年某集团“华北”、“东北”和“京津”3个区域的销售数据列表，这些数据列表保存D盘根目录下的“2011年区域销售”文件夹中。

img253b

图12-21　各区域销售数据列表

示例12.4　编制各区域销售统计动态数据列表

步　骤1img01

打开D盘根目录下“2011年区域销售”文件夹中的“汇总”文件，在【数据】选项卡中单击【现有连接】按钮，弹出【现有连接】对话框，单击【浏览更多】按钮，打开【选取数据源】对话框，如图12-22所示。

img254a

图12-22　选取数据源

步　骤2img01

打开D盘根目录下的目标文件“汇总.xlsx”，弹出【选择表格】对话框，如图12-23所示。

img254b

图12-23　选择表格

步　骤3img01

保持【选择表格】对话框的默认选择，单击【确定】按钮，在弹出的【导入数据】对话框中选择【数据透视表】单选钮，【数据的放置位置】选择【现有工作表】单选钮，然后单击“汇总”工作表中的A3单元格，再单击【属性】按钮打开【连接属性】对话框，单击【定义】选项卡，如图12-24所示。

img255a

图12-24　打开【连接属性】

步　骤4img01

清空【命名文本】文本框中的内容，输入以下SQL语句：

SELECT“东北”AS区域, FROM [D:\2011年区域销售\东北地区.xlsx].[东北$] UNION ALL

SELECT“华东”AS区域, FROM [D:\2011年区域销售\华东地区.xlsx].[华东$] UNION ALL

SELECT“京津”AS区域，* FROM [D:\2011年区域销售\京津地区.xlsx].[京津$]

单击【确定】按钮返回【导入数据】对话框，再次单击【确定】按钮创建一张空白数据透视表，如图12-25所示。

img255b

图12-25　数据SQL语句，创建数据透视表

步　骤5img01

在【数据透视表字段列表】中，将“日期”字段移动至【列标签】区域内，在数据透视表中按【步长】为【月】对“日期”字段进行分组组合，将【区域】字段移动至【报表筛选】区域内，将【客户】字段移动至【行标签】区域内，将【金额】字段移动至【Σ数值】区域内，最后对数据透视表进行美化，完成后的数据透视表如图12-26所示。

img256a

图12-26　完成后的数据透视表