我的书签
添加书签
移除书签13.1 Microsoft Query查询单个数据列表创建数据透视表
图13-1所示展示了一张某公司2011年销售合同数据列表，该数据列表保存在D盘根目录下的“销售合同数据库.xlsx”文件中。
图13-1 某公司2011年销售合同数据库
示例13.1 销售合同汇总分析
如果希望对图13-1所示的数据列表进行汇总分析，编制按“用户名称”反映合同执行及回款情况的动态分析表，请参照以下步骤。
步 骤1
在D盘根目录下新建一个Excel工作簿，将其命名为“销售合同汇总分析.xlsx”，打开该工作簿。将Sheet1工作表改名为“汇总”，然后删除其余的工作表。
步 骤2
在【数据】选项卡中单击【自其他来源】按钮，在弹出的下拉菜单中选择【来自Microsoft Query】，在弹出的【选择数据源】对话框中单击【数据库】选项卡，在列表框中选中【Excel Files*】类型的数据源，并取消【使用“查询向导”创建/编辑查询】复选框的勾选，如图13-2所示。
图13-2 【选择数据源】对话框
注意
必须取消【使用“查询向导”创建/编辑查询】复选框的勾选，否则将进入“查询向导”模式，而不是直接进入“Microsoft Query”。
步 骤3
单击【确定】按钮，【Microsoft Query】自动启动，并弹出【选择工作簿】对话框，选择要导入的目标文件所在路径，双击“销售合同数据库.xlsx”，激活【添加表】对话框，如图13-3所示。
图13-3 按路径选择数据源工作簿
提示
如果用户的电脑中分别安装了不同版本的Office，在进行步骤3的操作过程中可能会出现错误提示，解决方法请参阅13.4节。
在【添加表】对话框中的【表】列表框中选中“2011年合同库$”，单击【添加】按钮向【Microsoft Query】添加数据列表，如图13-4所示。
图13-4 将数据表添加至Microsoft Query
注意
如果【添加表】对话框中的【表】列表框为空，说明需要调整设置。
单击【添加表】对话框中的【选项】按钮，勾选【表选项】对话框中【系统表】复选框，最后单击【确定】按钮，待查询的数据列表即会出现在【添加表】列表框中，如图13-5所示。
图13-5 向【添加表】列表框内添加数据列表
步 骤5
单击【关闭】按钮关闭【添加表】对话框，在“2011年合同库$”下拉列表框中分别双击“产品规格”、“合同金额”、“数量、“累计到款”、“欠款”、“销售额”、“销售数量”和“用户名称”等字段，向数据窗格中添加数据，如图13-6所示。
步 骤6
单击工具栏中的按钮，将数据返回到Excel，此时Excel窗口中将弹出【导入数据】对话框，如图13-7所示。
步 骤7
单击【导入数据】对话框中的【属性】按钮，在弹出的【连接属性】对话框中单击【使用状况】选项卡，勾选【刷新控件】选项区中的【打开文件时刷新数据】复选框，如图13-8所示。
图13-6 向数据窗格中添加数据
图13-7 将数据返回到Excel
图13-8 【连接属性】对话框
步 骤8
单击【确定】按钮返回【导入数据】对话框，单击【数据透视表】单选钮，【数据的放置位置】选择【现有工作表】中的“$A$3”，单击【确定】按钮生成一张空的数据透视表，如图13-9所示。
步 骤9
将【数据透视表字段列表】中的“用户名称”和“产品规格”字段拖动至【行标签】区域内，“合同金额”、“累计到款”、“欠款”、“数量”、“销售额”和“销售数量”字段拖动至【Σ数值】区域内，如图13-10所示。
图13-9 生成空的数据透视表
图13-10 创建数据透视表
步 骤10
在“计数项：累计到款”字段标题单元格（如C3）上单击鼠标右键，在弹出的快捷菜单中选择【值字段设置】命令，在弹出的【值字段设置】对话框中单击【汇总方式】选项卡，【值字段汇总方式】列表框中的计算类型选择【求和】，单击【确定】按钮，如图13-11所示。
步 骤11
将【列标签】区域内的字段标题的“求和项：”字样以一个空格代替，调整数据透视表相关布局后，如图13-12所示。
图13-11 更改数据透视表内的字段设置
图13-12 售合同汇总分析
