12.4.1　汇总数据列表的所有记录和与之关联的另一个数据列表的部分记录

12.4　导入数据关联列表创建数据透视表

运用导入外部数据结合“编辑OLE DB”查询中的SQL语句技术，可以轻而易举地汇总关联数据列表的所有记录。

图12-45所示展示了某公司2011年员工领取物品记录数据列表和该公司的部门员工资料数据列表。此数据列表保存在D盘根目录下的“2011年物品领取记录.xlsx”文件中。

图12-45　部门-员工数据列表和物品领取数据列表

示例12.8　汇总每个部门下所有员工领取物品记录

如果希望统计不同部门不同员工的物品领取情况，请参照以下步骤。

步　骤1

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

图12-46　选取数据源

步　骤2

打开D盘根目录下的目标文件“2011年物品领取记录.xlsx”，弹出【选择表格】对话框，如图12-47所示。

图12-47　选择表格

步　骤3

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

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

步　骤4

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

SELECT A.部门，A.员工，B.日期，B.领取物品，B.单位，B.数量FROM [部门一员工$]A LEFT JOIN [物品领取$]B ON A.员工=B.员工
也可以使用以下SQL语句：
SELECT A.日期，A.领取物品，A.单位，A.数量，B.部门，B.员工FROM [物品领取$]A RIGHT JOIN [部门一员工$]B ON A.员工=B.员工
单击【确定】按钮返回【导入数据】对话框，再次单击【确定】按钮创建一张空白的数据透视表，如图12-49所示。

图12-49　输入SQL语句，创建空白数据透视表

提示

此语句的含义是：返回“部门-员工”工作表中“部门”和“员工”字段的所有记录，和“物品领取”工作表中“员工”字段与“部门-员工”工作表中“员工”字段相同的“员工”对应的“日期”、“物品”、“单位”和“数量”的领取记录。

注意

第一条语句使用的是LEFT JOIN ON（左连接），意思是返回第一个表指定字段的所有记录和第二个表符合与之关联条件的指定字段的部分记录；第二条语句使用的是RIGHT JOIN ON（右连接），意思刚好与LEFT JOIN ON相反，意思是返回第二个表指定字段的所有记录和第一个表符合与之关联条件的指定字段的部分记录。

步　骤5

将“部门”、“员工”、“领取物品”和“单位”字段移动至【行标签】区域内，将“日、期”移动至【报表筛选】区域，并在数据透视表中对“日期”字段按步长【月】进行组合，最后将“数量”字段移动至【Σ数值】区域内，修改“数量”字段的汇总方式为“求和”，最后对数据透视表进行美化，完成后的数据透视表如图12-50所示。

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

12.4.2　汇总关联数据列表中符合关联条件的指定字段部分记录

图12-51所示展示了某级“一班”班级的学生信息数据列表和某次班级考试前20名学生数据列表，此数据列表存放在D盘根目录下的“班级成绩表.xlsx”文件中。

图12-51　班级信息和前20名成绩数据列表

示例12.9　汇总班级进入年级前20名学生成绩

如果希望统计“一班”数据列表中，成绩进入“前20名”的学生情况，请参照以下步骤。

步　骤1

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

图12-52　选取数据源

步　骤2

在D盘根目录下打开目标文件“班级成绩表.xlsx，弹出【选择表格】对话框，如图12-53所示。

步　骤3

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

图12-53　选择表格

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

步　骤4

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

SELECT A.学生,A.性别,B.* FROM [一班$]A INNER JOIN [前20名$]B ON A.学生ID=B.考生ID单击【确定】按钮返回【导入数据】对话框，然后再单击【导入数据】对话框的【确定】按钮创建一个空白的数据透视表，如图12-55所示。

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

提示

此语句的含义是：返回“一班”数据列表和“前20名”数据列表中，具有相同“学生ID”的部分记录。

步　骤5

单击【数据透视表字段列表】对话框，将“考生ID”、“学生”和“性别”字段移动、至【行标签】区域内，将“语文”、“数学”、“英语”和“总分”字段移动至【Σ数值】区域内，最后对数据透视表进行美化，完成后的数据透视表如图12-56所示。

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

12.4.3　汇总多张关联数据列表

图12-57所示展示了某公司2011年订单明细数据列表，此数据列表保存在D盘根目录下的“2011年订单明细.xlsx”文件中。

图12-57　某公司2011年订单数据列表

示例12.10　编制客户未完成订单汇总表

如果用户希望查看还没有完成的客户订单表，请参照以下步骤。

步　骤1

双击打开“2011年订单明细.xlsx”文件，单击“汇总”工作表标签，在【数据】选项卡中单击【现有连接】按钮，弹出【现有连接】对话框，单击【浏览更多】按钮，打开【选取数据源】对话框，如图12-58所示。

图12-58　选取数据源

步　骤2

在D盘根目录下打开目标文件“2011年订单明细.xlsx”，弹出【选择表格】对话框，如图12-59所示。

图12-59　选择表格

步　骤3

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

步　骤4

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

SELECT [客户$].客户，[订单$].订单ID,[订单$].日期，[订单$].商品，[订单$].单位，[订单$].单价，[订单$].数量，[订单$].金额FROM（[订单$] LEFT JOIN [状态$] ON [订单$]．订单ID=[状态$]．订单ID）LEFT JOIN [客户$] ON [订单$]．客户ID=[客户$]．客户ID WHERE [状态$]．发货日期IS NULL

单击【确定】按钮返回【导入数据】对话框，然后单击【导入数据】对话框的【确定】按钮创建一个新的数据透视表，如图12-61所示。