1.4 用一个例子说明报表自动化的实现过程

小勤：说了那么多，能不能先举一个例子来介绍一下Power Query和Power Pivot是怎么实现报表自动化的?

大海：好的。下面举一个例子。在这个过程里，有些步骤如果暂时不会操作也没关系，先有一个总体印象，后面深入学习之后再回头看一下就会觉得很简单。

小勤：嗯。

大海：比如下面有这些数据：2015-2017年，每年有一个Excel工作簿，每个工作簿里有一个订单表和一个订单明细表，如图1-4所示。

小勤：数据被分成多个文件，每个文件里有多个表的情况真是太常见了，这样每年一个工作簿的还算少的了，很多时候是每个月有一个工作簿，合并它们是一件麻烦的事。

大海：那你以前碰到这种数据合并的问题是怎么办的?

小勤：如果量少就手工复制一下，实在太多的话只能用VBA了，但VBA用得熟练的人毕竟少啊。从网上找的程序也不能适用于各种情况，比如有些适用于所有工作表都一样的合并，在这里就不适用了。如果找人开发，那就面临时间和成本的问题。

大海：下面看一看用Power Query是怎么解决的。

Step 01 为了避免跟原来的数据混在一起，我们在源数据的文件夹外面建了一个新的工作簿，用来专门进行数据处理，如图1-5所示。

Step 02 打开新创建的工作簿，切换到“数据”选项卡，单击“新建查询”按钮，在下拉菜单中选择“从文件”命令，继续在下一级菜单中选择“从文件夹”命令，如图1-6所示。

Step 03 弹出“文件夹”对话框，单击“浏览”按钮，在弹出的“浏览文件夹”对话框中选择待合并数据所在的文件夹，单击“确定”按钮关闭“浏览文件夹”对话框，继续单击“确定”按钮关闭“文件夹”选择对话框，如图1-7所示。

Step 04 此时，该文件夹内的所有文件都将被识别出来。由于每个文件里有不同的表，不能直接合并，因此，在弹出的对话框中单击“编辑”按钮，进入Power Query编辑界面，如图1-8所示。

Step 05 在Power Query（查询编辑器）里可以看到，3个Excel工作簿的信息都被读了进来，包括工作簿的名称、修改时间等。其中，工作簿内的数据在“Content”列里，如图1-9所示。

Step 06 用Power Query里的简单函数“Excel.Workbook”将这些工作簿的数据解析出来：在Power Query查询编辑器里，切换到“添加列”选项卡，单击“自定义列”按钮，在弹出的对话框中输入公式“=Excel.Workbook（[Content],true）”（提示，输入时一定要注意区分大小写），然后单击“确定”按钮，如图1-10所示。

Step 07 展开工作簿数据：单击上一步骤所添加“自定义”列右侧的数据展开按钮，取消勾选“使用原始列名作为前缀”复选框，单击“确定”按钮，如图1-11所示。

Step 08 筛选出需要合并的“订单”表：单击“Item”列右侧的数据筛选按钮，在弹出的对话框中勾选“订单”复选框，单击“确定”按钮，如图1-12所示。

Step 09 展开订单表数据：单击“Data”列右侧的数据展开按钮，在弹出的对话框中取消勾选“使用原始列名作为前缀”复选框，单击“确定”按钮，如图1-13所示。

Step 10 删除不需要的列：在上一步骤展开数据后，订单表中的所有数据都处于被选中的状态，此时，用鼠标右键单击任一列名位置，在弹出的菜单中选择“删除其他列”命令，如图1-14所示。

小勤：这样就把订单表的数据都合并好了?

大海：对啊。

小勤：而且在整个过程中还可以按需要选择工作簿、工作表等。

大海：对，到了这里，如果你只需要把合并的数据发给别人，就可以直接将数据返回Excel里了：切换到“开始”选项卡，单击“关闭并上载”按钮，如图1-15所示。

小勤：这也只是做了一个数据合并啊，怎么就让报表自动化了?

大海：报表自动化的关键是，以后要导入新数据的时候，你不需要再重复做一遍，而是一键刷新就自动得到最新的结果。比如，这里你将数据返回到Excel里了，以后源数据有了新的内容，只需要在Excel里用鼠标右击该结果表的任意位置，在弹出的菜单中选择“刷新”命令，就可以得到最新的数据，如图1-16所示。

小勤：这个真是太好用了。如果还要进行进一步的处理呢?

大海：在Power Query里还可以进行各种处理，比如按需要选择数据、合并、分组等，后面我们再一个功能一个功能地练习。现在我们继续看一下如何进一步实现数据分析类报表的自动化。

小勤：那把订单明细表也整合进来吧，这样才更像一个综合分析。

大海：好。其实订单明细表的整合跟前面订单表的整合方法类似。而且，选择文件夹、解析工作簿数据等步骤是完全一样的。

Step 11 在Excel中，切换到“数据”选项卡，单击“显示查询”按钮，在右侧的“工作簿查询”窗口中双击前面步骤创建的查询，如图1-17所示，进入Power Query界面。

Step 12 为方便后面与订单明细表进行区分，在Power Query右侧的“查询设置”窗口中，将查询名称修改为“订单表”，如图1-18所示。

Step 13 在Power Query左侧单击向右展开按钮，显示所有查询，用鼠标右键单击“订单表”，在弹出的菜单中选择“复制”命令（第2个），如图1-19所示。

Step 14 在“查询”列表中选中刚复制出来的“订单表（2）”，在右侧查询设置中将其名称修改为“订单明细”，如图1-20所示。

Step 15 因为订单明细表和订单表的整合过程从筛选步骤开始有差异，所以只要修改“筛选的行”以后的步骤即可。在“查询设置”的“应用的步骤”中，用鼠标右键单击“筛选的行”步骤，在弹出的菜单中选择“删除到末尾”命令，如图1-21所示。

Step 16 重新筛选要整合的数据：单击“Item”列右侧的筛选按钮，在弹出的对话框中勾选“订单明细”复选框，单击“确定”按钮，如图1-22所示。

Step 17 单击“Data”列右侧的数据展开按钮，在弹出的对话框中取消勾选“使用原始列名作为前缀”复选框，单击“确定”按钮，如图1-23所示。

Step 18 删除不需要的列：保持展开时订单明细数据列处于被选中状态，用鼠标右键单击任一列名位置，在弹出的菜单中选择“删除其他列”命令，如图1-24所示。

小勤：这样把订单明细表整合好了，真是方便啊！

大海：接下来我们看看怎么将两个表的数据结合起来做分析。

小勤：这才是关键啊。如果是在Excel里那么操作起来可麻烦了，一般得将订单表里的数据用VLOOKUP等函数读到订单明细表里，然后再按需要做筛选、数据透视等，而且，要从订单表里读这么多数据到订单明细表里，那电脑不知道得多“卡”……

大海：现在我们就用Power Query或Power Pivot来彻底解决这个问题。首先看看在Power Query里怎么将订单表里的数据读到订单明细表里。

Step 19 合并查询：在Power Query中，保持选中“订单明细表”查询，切换到“开始”选项卡，单击“合并查询”按钮，在弹出的对话框中选中“订单明细”中的“订单ID”列，选择“订单表”作为合并来源并选中其中的“订单ID”列，单击“确定”按钮，如图1-25所示。

Step 20 单击“订单表”列右侧的数据展开按钮，在弹出的对话框中取消勾选“使用原始列名作为前缀”复选框（若只需要得到订单表里的部分列，则可以在其中按需选择），单击“确定”按钮，如图1-26所示。

小勤：这个操作太方便了，而且速度好快，单击鼠标结果就出来了！

大海：用Power Query从一个表中读取数据到另一个表中，既简单又快捷。这个时候，我们的报表自动化又向前推进了一步，即可以从关联的表格中匹配汇总相关数据了。但是，这还属于数据整理的范畴，也就是说，如果某些部门或用户需要你给出这些数据整合的报表，那么你已经达到目的了。同样，导入新数据后，刷新一下就自动得到最新结果了。

小勤：怎样能进一步实现数据分析的自动化?

大海：根据不同的需要，有不同的方法进行数据分析。如果是比较简单的数据汇总、透视等，那么仍然可以直接基于Power Query已经合并的数据来进行，就像在Excel里将数据从订单表读取到订单明细表后，就可以做数据透视了。但是，如果结合Power Pivot来做，那么会更加简单，而且还不需要将一个表的数据读取到另一个表里。

Step 21 为避免混淆，我们在Power Query里把合并查询相关的步骤删掉（若保留也不影响后续操作，但会因为订单明细表里有订单表里的所有列而显得重复）：在“订单明细”的“查询设置”的“应用的步骤”中，用鼠标右键单击“合并的查询”步骤，在弹出的菜单中选择“删除到末尾”命令，如图1-27所示。

Step 22 调整数据格式（Power Query和Power Pivot对数据格式要求相对严格）：选择“订单明细表”里的“单价”“数量”“折扣”等列，切换到“转换”选项卡，单击“数据类型”按钮，在弹出的菜单中选择“小数”命令，如图1-28所示。

Step 23 将数据加载到数据模型中：切换到“开始”选项卡，单击“关闭并上载”按钮，在弹出的菜单中选择“关闭并上载至”命令，如图1-29所示。

在弹出的对话框中，选择“仅创建连接”单选框（不需要将表的数据直接返回Excel中），勾选“将此数据添加到数据模型”复选框，单击“加载”按钮，如图1-30所示。

Step 24 将“订单表”也添加到数据模型中：用鼠标右键单击“订单表”查询，在弹出的菜单中选择“加载到”命令，如图1-31所示。

在弹出的“加载到”对话框中，选中“仅创建连接”单选框（不需要将表的数据直接返回Excel中），勾选“将此数据添加到数据模型”复选框，单击“加载”按钮，如图1-32所示。

Step 25 在Excel中，切换到“Power Pivot”选项卡，单击“管理数据模型”按钮，如图1-33所示。

Step 26 在Power Pivot中，切换到“主页”选项卡，单击“关系图视图”按钮，将“订单明细”表中的“订单ID”字段（列）拖曳到“订单表”中的“订单ID”字段（列）上，如图1-34所示。

这样，两表之间就建立了关联关系，如图1-35所示，可以在后续的数据分析中直接调用相关表中的数据了。

小勤：就这样拉一根线就可以了?

大海：对。根本不需要从一个表里将数据读到另一个表里。接下来我们做一个简单的数据透视表就能看出效果了。

Step 27 在Power Pivot中，切换到“主页”选项卡，单击“数据透视表”按钮，如图1-36所示。

在弹出的对话框中选中“新工作表”单选框，单击“确定”按钮，如图1-37所示。

Step 28 构建数据透视表。比如将“订单表”的“货主地区”字段拖曳至“行”，将“订单明细”表中的“数量”拖曳至“值”，从两个表中导入的数据就直接结合在一起了，并生成了正确的分析结果，如图1-38所示。

小勤：这太厉害了！以后再也不用一遍又一遍地从一个表到另一个表来来回回地读数据了。

大海：这个也要看实际情况。有时候就是需要将不同表的数据整理到一起交给别人，那就得在Power Query里做合并。但是，如果要做的是分析结果，那就将数据加载到Power Pivot里并建立表间的关系，然后直接进行相关的分析。

小勤：对了，这样生成的数据透视表也可以在源数据有更新的情况下一键刷新吗?

大海：对，你可以用鼠标右键单击数据透视表的任意单元格，在弹出的菜单中选择“刷新”命令，如图1-39所示。

这时，如果在“显示查询”状态下可以看到数据透视表，则对数据透视表的刷新也会驱动Power Query中的查询去刷新以获取最新的数据，如图1-40所示。

小勤：终于大致理解为什么Power Query和Power Pivot可以让Excel普通用户也能轻松实现报表自动化了，直接通过鼠标操作和一些简单的函数就能实现原来必须用VBA才能实现的功能，甚至实现起来很困难的功能！

大海：这还只是一个开始。