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

Excel2016数据透视表应用大全

图14-1所示为一张某公司2018年销售合同数据列表，该数据列表保存在D盘根目录下的“销售合同数据库.xlsx”文件中。

图14-1某公司2018年销售合同数据库

示例：销售合同汇总分析

如果希望对图14-1所示的数据列表进行汇总分析，编制按“用户名称”反映合同执行及回款情况的动态分析表，具体操作步骤如下。

步骤1在D盘根目录下新建一个Excel工作簿，将其命名为“销售合同汇总分析.xlsx”，打开该工作簿，将Sheet1工作表改名为“汇总”。

步骤2在【数据】选项卡中单击【自其他来源】按钮，在弹出的下拉菜单中选择【来自MicrosoftQuery】命令，在弹出的【选择数据源】对话框中选择【数据库】选项卡，在列表框中选中【ExcelFiles*】类型的数据源，并取消选中【使用｜查询向导｜创建/编辑查询】复选框，如图14-2所示。

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

注意：必须取消选中【使用|查询向导|创建/编辑查询】复选框，否则将进入“查询向导”模式，而不是直接进入“MicrosoftQuery”。

步骤3单击【确定】按钮，【MicrosoftQuery】自动启动，并弹出【选择工作簿】对话框，选择要导入的目标文件所在路径，双击“销售合同数据库.xlsx”，激活【添加表】对话框，如图14-3所示。

图14-3按路径选择数据源工作簿

步骤4在【添加表】对话框中的【表】列表框中选中“2018年合同库$”，单击【添加】按钮向【MicrosoftQuery】添加数据列表，如图14-4所示。

图14-4将数据表添加至MicrosoftQuery

注意：如果【添加表】对话框中的【表】列表框为空，说明需要调整设置：

单击【添加表】对话框中的【选项】按钮，在【表选项】对话框中选中【系统表】复选框，最后单击【确定】按钮，待查询的数据列表即会出现在【添加表】列表框中，如图14-5所示。

图14-5向【添加表】列表框内添加数据列表

步骤5单击【关闭】按钮关闭【添加表】对话框，在“2018年合同库$”下拉列表框中分别双击“产品规格”“合同金额”“数量”“累计到款”“欠款”“销售额”“销售数量”和“用户名称”等字段，向数据窗格中添加数据，如图14-6所示。

图14-6向数据窗格中添加数据

步骤6单击工具栏中的按钮，将数据返回到Excel，此时Excel窗口中将弹出【导入数据】对话框，如图14-7所示。

图14-7将数据返回到Excel

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

图14-8【连接属性】对话框

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

图14-9生成空的数据透视表

步骤9将【数据透视表字段】窗格中的“用户名称”和“产品规格”字段拖曳至【行】区域内，“合同金额”“累计到款”“欠款”“数量”“销售额”和“销售数量”字段拖曳至【∑值】区域内，如图14-10所示。

图14-10创建数据透视表

步骤10在“计数项:累计到款”字段标题单元格（如C3）上右击，在弹出的快捷菜单中选择【值字段设置】命令，在弹出的【值字段设置】对话框中选择【值汇总方式】选项卡，【值字段汇总方式】列表框中选择【求和】计算类型，单击【确定】按钮，如图14-11所示。

图14-11更改数据透视表内的字段设置

步骤11将【列】区域内的字段标题的“求和项：”字样以一个空格代替，调整数据透视表相关布局，如图14-12所示。

图14-12销售合同汇总分析

END