13.2　Microsoft Query查询多个数据列表创建数据透视表

13.2.1　汇总同一工作簿中的数据列表

图13-13所示展示了同一个工作簿中的两个数据列表，分别位于“入库”和“出库”两个工作表中，记录了某公司某个期间产成品库按订单来统计的产成品出入库数据，该数据列表被保存在D盘根目录下的“产成品出入库明细表.xlsx”文件中。

在出、入库数据列表中，每个订单号只会出现一次，而同种规格的产品可能会对应多个订单号。

img281a

图13-13　出、入库数据列表

示例13.2　制作产成品收发存汇总表

要对图13-13所示的“入库”和“出库”2个数据列表使用Microsoft Query做数据查询并创建反映产品收发存汇总的数据透视表，请参照以下步骤。

步　骤1img01

在D盘根目录下新建一个Excel工作簿，将其命名为“制作产成品收发存汇总表.xlsx”，打开该工作簿。将Sheet1工作表改名为“汇总”，然后删除其余的工作表。

步　骤2img01

在【数据】选项卡中单击【自其他来源】按钮，在弹出的下拉菜单中选择【来自Microsoft Query】，在弹出的【选择数据源】对话框中单击【数据库】选项卡，在列表框中选中【Excel Files*】类型的数据源，并取消【使用“查询向导”创建／编辑查询】复选框的勾选，如图13-14所示。

img281b

图13-14　【选择数据源】对话框

步　骤3img01

单击【确定】按钮，【Microsoft Query】自动启动，并弹出【选择工作簿】对话框，选择要导入的目标文件所在路径，双击“产成品出入库明细表.xlsx”，激活【添加表】对话框，如图13-15所示。

img282a

图13-15　按路径选择数据源工作簿

步　骤4img01

在【添加表】的【表】列表框中选中“入库$”，单击【添加】按钮，然后选中“出库$”，再次单击【添加】按钮，向【Microsoft Query】添加数据列表，如图13-16所示。

img282b

图13-16　将数据表添加至【Microsoft Query】

步　骤5img01

单击【关闭】按钮关闭【添加表】对话框，在【查询来自Excel Files】窗口中，将“入库$”中的“订单号”字段拖至“出库$”中的“订单号”字段上，两表之间会出现一条连接线，如图13-17所示。

img283a

图13-17　两表之间的连接线

提示img02

因为两个表中只有“订单号”字段的数据是唯一的，所以本步骤中以“订单号”字段为主键在“入库”和“出库”两个数据列表中建立关联。

步　骤6img01

双击连接线，弹出【连接】对话框，选择【连接内容】中的第2个单选钮，如图13-18所示。

img283b

图13-18　选择“连接内容”

提示img02

此操作的目的是设置两个数据列表的关联类型，即返回“入库$”列表的所有记录以及“出库$”列表中与之关联的记录。

步　骤7img01

单击【添加】按钮，【查询中的连接】文本框中出现了用于连接的语句，单击【关闭】按钮关闭【连接】对话框，如图13-19所示。

img284a

图13-19　添加“连接”语句

步　骤8img01

在【查询来自Excel Files】窗口中的“入库$”列表中依次双击“产品名称”、“订单号”、“规格型号”、“颜色”和“数量”字段；在“出库$”列表中双击“数量”字段，随即出现数据集，如图13-20所示。

img284b

图13-20　向【查询来自Excel Files】查询对话框添加数据集

注意img01

在向【查询来自Excel Files】对话框添加数据集时，要添加数据最为齐全的表中的非数值字段，本例中添加的是“入库$”表中的“产品名称”、“订单号”、“规格型号”、“颜色”、“数量”等字段，“出库$”表中只添加了“数量”字段。

步　骤9img01

单击菜单【文件】→【将数据返回Microsoft Office Excel】，弹出【导入数据】对话框，如图13-21所示。

img285a

图13-21　【导入数据】对话框

步　骤10img01

单击【导入数据】对话框中的【属性】按钮，在弹出的【连接属性】对话框中单击【使用状况】选项卡，勾选【刷新控件】中的【打开文件时刷新数据】复选框，如图13-22所示。

img285b

图13-22　【连接属性】对话框

步　骤11img01

单击【确定】按钮返回【导入数据】对话框，单击【数据透视表】单选钮，【数据放置位置】选择【现有工作表】中的“$A$3”，单击【确定】按钮生成一张空白的数据透视表，如图13-23所示。

步　骤12img01

调整数据透视表字段，将“计数项：数量2”的汇总方式改为“求和”，如图13-24所不。

步　骤13img01

将“求和项：数量”字段标题更改为“入库”，“求和项：数量2”字段标题更改为“出库”，在数据透视表中插入“结存”计算字段，计算公式“结存=数量-数量2”，最终结果如图13-25所示。

img286a

图13-23　生成空白的数据透视表

img286b

图13-24　创建数据透视表

img286c

图13-25　利用“Microsoft Query SQL”创建的数据透视表

13.2.2　汇总不同工作簿中的数据列表

利用Microsoft Query数据查询并通过SQL语句的连接，也可以对多工作簿中不同的数据列表进行汇总并创建数据透视表。

图13-26所示展示了D盘根目录下“汇总不同工作簿内的数据表”文件夹内的5个工作簿，其中“滨海司.xlsx”、“丽江司.xlsx”、“美驰司.xlsx”、“山水司.xlsx”是某集团内部各分公司的费用发生额流水账，“集团内部各公司各月份费用汇总.xlsx”是用于汇总分公司费用发生额流水账的工作簿。

img287a

图13-26　D盘根目录下“汇总不同工作簿内的数据表”文件夹内的5个工作簿

图13-27所示展示了“滨海司”工作簿中的6张数据列表，分别位于“1月”、“2月”、“3月”、“4月”、“5月”和“6月”工作表中，数据列表中记录了该公司各月份费用发生额的数据。

“丽江司”、“美驰司”、“山水司”工作簿中也分别记录了各自1至6月份的费用数据，且数据结构与“滨海司”工作簿完全相同。

img287b

图13-27　“滨海司”工作簿中的6张数据列表

img288a

图13-27　“滨海司”工作簿中的6张数据列表（续）

示例13.3　集团内部各公司各月份费用汇总

“滨海司”、“丽江司”、“美驰司”和“山水司”工作簿中共有24个数据列表，如果希望对它们使用Microsoft Query做数据查询并生成汇总的数据透视表，以反映集团内部各公司各月份的费用发生额，请参照以下步骤。

步　骤1img01

打开“集团内部各公司各月份费用汇总.xlsx”，在【数据】选项卡中单击【自其他来源】按钮，在弹出的下拉菜单中选择【来自Microsoft Query】，在弹出的【选择数据源】对话框中单击【数据库】选项卡，在列表框中选中【Excel Files*】类型的数据源，并取消【使用“查询向导”创建／编辑查询】复选框的勾选，如图13-28所示。

img288b

图13-28　【选择数据源】对话框

步　骤2img01

单击【确定】按钮，【Microsoft Query】自动启动，并弹出【选择工作簿】对话框，选择要导入的目标文件的所在路径，双击“滨海司.xlsx”，激活【添加表】对话框，如图13-29所示。

img289a

图13-29　按路径选择数据源工作簿

步　骤3img01

在【添加表】对话框的【表】列表框中选中“1月$”，单击【添加】按钮向【Microsoft Query】添加数据列表，如图13-30所示。

img289b

图13-30　将数据表添加至【Microsoft Query】

步　骤4img01

单击【关闭】按钮关闭【添加表】对话框，单击工具栏中的imgSQL图标弹出【SQL】编辑框，在【SQL】编辑框内输入SQL语句，如图13-31所示。

SELECT * FROM 'D:\汇总不同工作簿内的数据表\山水司.xlsx'. '1月$' '1月$' UNION ALL

SELECT * FROM 'D:\汇总不同工作簿内的数据表\山水司.xlsx '. '2月$' '2月$' UNION ALL

SELECT * FROM 'D:\汇总不同工作簿内的数据表\山水司.xlsx '. '3月$' '3月$' UNION ALL

SELECT * FROM 'D:\汇总不同工作簿内的数据表\山水司.xlsx '. '4月$' '4月$' UNION ALL

SELECT * FROM 'D:\汇总不同工作簿内的数据表\山水司.xlsx '. '5月$' '5月$' UNION ALL

SELECT * FROM 'D:\汇总不同工作簿内的数据表\山水司.xlsx '. '6月$' '6月$' UNION ALL

SELECT * FROM 'D:\汇总不同工作簿内的数据表\滨海司.xlsx '. '1月$' '1月$' UNION ALL

SELECT * FROM 'D:\汇总不同工作簿内的数据表\滨海司.xlsx '. '2月$' '2月$' UNION ALL

SELECT * FROM 'D:\汇总不同工作簿内的数据表\滨海司.xlsx '. '3月$' '3月$' UNION ALL

SELECT * FROM 'D:\汇总不同工作簿内的数据表\滨海司.xlsx '. '4月$' '4月$' UNION ALL

SELECT * FROM 'D:\汇总不同工作簿内的数据表\滨海司.xlsx '. '5月$' '5月$' UNION ALL

SELECT * FROM 'D:\汇总不同工作簿内的数据表\滨海司.xlsx '. '6月$' '6月$' UNION ALL

SELECT * FROM 'D:\汇总不同工作簿内的数据表\美驰司.xlsx '. '1月$' '1月$' UNION ALL

SELECT * FROM 'D:\汇总不同工作簿内的数据表\美驰司.xlsx '. '2月$' '2月$' UNION ALL

SELECT * FROM 'D:\汇总不同工作簿内的数据表\美驰司.xlsx '. '3月$' '3月$' UNION ALL

SELECT * FROM 'D:\汇总不同工作簿内的数据表\美驰司.xlsx '. '4月$' '4月$' UNION ALL

SELECT * FROM 'D:\汇总不同工作簿内的数据表\美驰司.xlsx '. '5月$' '5月$' UNION ALL

SELECT * FROM 'D:\汇总不同工作簿内的数据表\美驰司.xlsx '. '6月$' '6月$' UNION ALL

SELECT * FROM 'D:\汇总不同工作簿内的数据表\丽江司.xlsx '. '1月$' '1月$' UNION ALL

SELECT * FROM 'D:\汇总不同工作簿内的数据表\丽江司.xlsx '. '2月$' '2月$' UNION ALL

SELECT * FROM 'D:\汇总不同工作簿内的数据表\丽江司.xlsx '. '3月$' '3月$' UNION ALL

SELECT * FROM 'D:\汇总不同工作簿内的数据表\丽江司.xlsx '. '4月$' '4月$' UNION ALL

SELECT * FROM 'D:\汇总不同工作簿内的数据表\丽江司.xlsx '. '5月$' '5月$' UNION ALL

SELECT * FROM 'D:\汇总不同工作簿内的数据表\丽江司.xlsx '. '6月$' '6月$'

img290

图13-31　输入SQL语句

步　骤5img01

单击【确定】按钮关闭【SQL】编辑框，弹出【Microsoft Query】提示框，单击【确定】按钮【Microsoft Query】提示框，随即出现数据集，如图13-32所示。

步　骤6img01

单击菜单【文件】→【将数据返回Microsoft Office Excel】，弹出【导入数据】对话框，如图13-33所示。

步　骤7img01

单击【属性】按钮，在弹出的【连接属性】对话框中单击【使用状况】选项卡，勾选【刷新控件】选项区中的【打开文件时刷新数据】复选框，如图13-34所示。

img291a

图13-32　向【查询来自Excel Files】查询对话框添加数据值

img333333

图13-33　导入数据”对话框

img292a

图13-34　【连接属性】对话框

步　骤8img01

单击【确定】按钮返回【导入数据】对话框，单击【数据透视表】单选钮，【数据的放置位置】选择【现有工作表】中的“$A$3”，单击【确定】按钮生成一张空白的数据透视表，如图13-35所示。

img292b

图13-35　生成空的数据透视表

步　骤9img01

将【数据透视表字段列表】中的“科目名称”字段拖动至【行标签】区域，“月”字段拖动至【列标签】区域，“求和项：借方”字段拖动至【Σ数值】区域，最终完成的数据透视表如图13-36所示。

img293a

图13-36　集团内部各公司各月份的费用发生额汇总表