报表自动化第一步：前序准备

1、把需要将至少2个工作簿放到一个文件夹中。该分享就将文件夹命名为【每日订单报表】，后续的每日报表都会放到该文件夹中进行现自动汇总。

2、在该文件夹外，新建一个 Excel 表，可以取名叫【数据汇总】。

注意事项：千万不要把汇总表放到【每日订单报表】文件夹内，不然会形成死循环。

报表自动化第二步：使用PQ获取数据

1、打开【数据汇总】 Excel 表，【数据】功能选卡中，Office365 版本找到【获取数据】-【来自文件】-【从文件夹】。Office2016 版本在【数据】-【新建查询】-【从文件】-【从文件夹】。

2、点击【从文件夹】后，通过【浏览按钮】选择文件存放的文件夹【每日订到报表】，选择好文件夹，虽然里面看不到文件，但是不要慌，点击打开就好。最终地址会被记录下来，点击确认。

3、在弹出来的数据加载框的右下角，选择【转换数据】对数据进行整合

报表自动化第三步：使用PQ解析合并数据

1、点击转换数据后，进入到 PowerQuery 的操作界面，和 Excel 差不多。

红色的是功能区，对整列数据进行转换、提取、判断等操作。

蓝色的是数据区，在这里可以看到数据情况，并且通过鼠标右键进行一些快捷操作。

紫色的是步骤区，可以用来看到每一步操作，可以修改或撤销做错的步骤。

2、现在蓝色区域展现的是文件夹中3张需要汇总的表单的信息，如表名、格式等。而表单中的实际数据是存在了第一列 Content 中。其他列可以右键删除。操作方法是点击选中 Content 列，点击鼠标右键，在弹出的退画框中点击删除其他列。

3、通过添加列增加解析列，会用到本练习中唯一的函数Excel.Workbook([content],false)

注意事项：

a、函数名称要注意首字母大写，括号为英文半角的符号；

b、只有365版本才有提升，其他版本需要手动输入。

细节分享：函数有两个参数，一个是【content】，鼠标在右框双击即可；第二个参数可以输入true或false，含义为是否要整合表头，具体区别如下：

4、解析完成数据已经在新添加的【自定义列】的 Table 里了，可以删除 content 列，然后点击自定义列右边的按钮，进行第一次展开该列数据。展开后的数据为每一张表里面，每一个 Sheet 的信息，其中【自定义.Data】中的 Table 存放了对应表单的数据。

5、由于我们每个工作簿里，只有一张 Sheet1 表单，并且数据也存在里面。于是这里不用进行筛选，直接保留【自定义.Data】列，把其他的去掉，然后展开该列。

注意事项：在点击确定展开时把【使用原始列名作为前缀】前面的勾去掉，不然每一列名字都会叫自定义.Data.xxxx。

这样一来所有数据就都汇总在一张表里啦，来看看初步成果，上海、北京、重庆都有了。

报表自动化第四步：使用PQ清理数据

1、虽然数据汇总了，但是想要用透视表来分析，还有3个需要调整的格式：

a. 将每一张表的标题【公司订单数据表】去掉；

b. 要将订单日期、区域、城市的信息形成单独的列；

c. 每张表都有表头，要把重复的表头去掉。

2、通过筛选功能就可以把标题去掉，点击表头列1右边的箭头，打开筛选窗口，就和Excel中一样，将【公司订单数据表】前面的勾去掉机构。

3、将订单日期、区域、城市数据转变成按列存储的方式，主要用到条件列功能。由于日期、区域、城市都在同一行，在PQ中用判断第一列是否包含【订单日期】就能取得对应的数据。

首先在PQ中判断是否包含某个文字，需要先将对应的列转换为文本格式。点击表头前的【ABC123】，将第一列转换为【文本格式ABC】。

4、通过判断将含有日期、区域、城市信息的行区分出来，这里用第一列是否含有【订单日期】作为区分，使用条件列进行判断，如果是订单日期，就返回第二列的订单日期数据，如果不是，就返回 null 。点击【添加列】-【条件列】，在弹出的对话框中进行输入：

a. 【新列名】可以修改名称 if 后面的;

b. 【列名】是指用哪一列参与判断，这里选择Column1（列1）;

c. 【运算符】是判断的方式，这里选择包含;

d. 【值】是指运算符包含所要包含的内容;

e. 【输出】是指【列名】所选的列满足判断条件，将返回的值。这里可以通过点击【ABC123】来选择【输出一个值（返回一个固定的值）】，还是【选择列（返回所选列在对应行上的值）】。这里输出选择【选择列】，后面选择Column2（列2），这样当第一列为订单日期时，就会返回订单日期所在这一行中，第2列的数据，也就是 2020/1/1 。如果其他日期的数据导入后，也能取得对应的日期;
f. 【ELSE】是指【列名】所选的列不满足判断条件，将返回的值。这里填写null，null在PQ中代表空值，什么都没有。

5、用与上一步同样的操作，判断第一列是否包含【订单日期】，然后返回第 5 列区域的内容和第 8 列城市的内容。

6、按住 Ctrl 键，点击新添加的三列数据将其都选中，通过【转换】-【填充】-【向下填充】补全数据，再筛选去掉原来含有日期、区域、城市的行。

7、在去掉重复表头，选中【将第一行用作标题】，将标题上移，保留第一个表头。通过筛选功能，去掉重复的表头。

8、更改新添加的列的列名，双击表头列名即可修改。点击【关闭并上载】，得到新的表单。

9、这么一来数据就清理好啦!

报表自动化第五步：统计与更新

1、选中生成的数据表，在【插入】-【数据透视表】中插入数据透视表，该分享演示一下各城市每日销售金额的统计。

2、最后我来说说，这个 PowerQuery 超强的一点了。每天的数据来了，只需要将新的数据拖入之前的【每日订到报表】文件夹，刷新绿色的数据表、并刷新透视表可以更新了。

通过以上的方法，今后工作就简化了许多，将新的文件放入文件夹，点击刷新即可搞定。

当然，如果你觉得上述操作过于复杂，也可以使用九数云BI，它支持0代码分析百万数据，拖拽式的报表生成方式对新手更加友好！

上一篇: 什么是数据应用？不知道的看过来！——九数云BI
下一篇: 简单3步让你做出高端大气的对比图表？打工人必备！——九数云BI