Excel 2016的PowerQuery多工作表合并技巧

很多公司，特别是大型公司部门都非常多，为了记账的方便，财务一般会为每个部门建立一个工作表记录来往支出（图1）。不过这样记录虽然清晰，但是后续查询起来并不方便。比如要查看某部门特定月份的支出，常规的方法要来回在两个工作表中查询。现在借助Excel 2016的PowerQuery功能，我们可以轻松将多个工作表合成在一起。

图1 某公司部门费用支出表

新建一个工作簿文件，接着单击“数据-新建查询-从Excel文件”，然后选择上述包含多个工作表的Excel文件。导入文件后会自动读取上述工作簿的列表，每个工作表都会自动显示出来（图2）。

图2 导入数据源

小提示：

如果需要合成的工作表分别保存在多个工作簿文件中（合并工作表必须具备相同的数据结构），那么可以Kutools for Excel插件实现，在Excel中启动Kutools for Excel，点击“企业/汇总”，在汇总工作表向导里，选择“复制多个工作簿中的资料到一个工作表中”选项。接着点击添加按钮插入需要合并的档指定需要合并的工作表即可将多个工作簿合成在一个工作簿中。最后再以合并的工作簿作为数据源执行上述的操作即可。

在上述窗口选中任意一个工作表，点击“编辑”进入PowerQuery编辑窗口，因为这里我们只需对“源”数据进行编辑，因此可以在右侧窗格“应用的步骤”下将除“源”之外的数据全删除（仅保留源），右击数据选择“删除”即可（图3）。

图3 保留源数据

完成上述操作后，由于我们也是只需对包含金额的“data”数据进行分析，按住Ctrl键选中DATA列的数据，将其它列的数据也全部删除。完成列数据的删除后，点击Data列旁的“筛选”按钮，筛选选项选择“扩展”，勾选所有的Coloume点击“确定”（图4）。

图4 筛选数据

可以看到通过上述的操作后，原来工作簿的所有费用数据全部整合在一张工作表中了。选中第一行，点击“开始-将第一行作为标题”。把第一行的表头内容作为标题，不过由于合成的时候，将每个子表的表头全部集成过来，因此需要将其他行多余的表头数据数据隐藏，隐藏操作借助筛选工具完成，按提示点击“日期”右侧的筛选按钮，在打开的下拉窗口去除“日期”的勾选，这样旧只保留第一行表头内容的显示了（图5）。

图5 筛选隐藏日期

完成上述的操作后点击“确定”，在返回的编辑窗口点击“文件-关闭并上载”，将整合好的数据全部上载到新建工作簿中（图6）。

小提示：

数据保存并上载到Excel工作簿后，可以在右侧窗格随时点击刷新按钮，在打开的窗户口点击“编辑”，重新返回PowerQuery窗口进行编辑。

图6 上载并保存

在保存上载数据的窗口，点击“插入-数据透视表”，在打开的窗口，选择一个列表区域，全选合成好的数据，选择存放位置选择“现有工作表”，在现有工作表区域插入一张透视工作表（图7）。

图7 插入透视工作表

在数据透视表的字段设定中，依次将日期拖到“行”，金额拖到“值”，部门拖到“筛选器”，费用拖到“列”完成透视表的布局，这样在一张数据透视表中就可以很方便看到各个部门费用的排列和统计（图8）。

图8 数据透视表字段设置

继续点击“插入-日程表”，在弹出的窗口勾选“日期”，点击“插入-切片器”，在弹出的窗勾选“部门”，完成数据透视表的设置（图9）。

图9 插入切片器

OK，完成上述的操作后。以后如果我们需要查看各个部门的费用数据，那么只要点击插入的日期或者部门切片器，就可以很方便根据自己需要进行筛选查看了。比如需要查看销售部第一季度的费用明细，那么只要将日期查看设置为“季度”，接着选中第1季度，在切片器中点击“销售部”，该部门1季度的费用汇总立刻就可以显示出来。当然我们可以过根据自己实际需要查看任意数据（图10）。

图10 展示效果