2019年还剩下最后几天,又到了一年一度的总结时刻,表哥表姐们又要忙着整理数据,出报表,做PPT,做总结的最重要的环节是数据分析,为了使数据有说服力,需要汇总数据,要本年度的数据,也要历史数据,有比较才更有说服力。
数据整理就是做总结的第一步,我们之前讲过很多关于Power Query文件合并的案例,但大多数都是直接给出的正确的操作程序,并没有提示大家可能遇到的问题,今天我们就从问题出发,来总结一合并文件可能遇到的坑。
我们都知道Power Query合并文件也好,VB合并文件也好,最理想的文件标准:
- 没有合并单元格;
- 单行表头;
- 各列的顺序一致;
但是这三点在实际应用中,却是经常会遇到的,我们先从单文件多表合并说起:
我们会遇到的第一个问题:多层表头
多层表头非常普遍,几乎随处可见,给大家举个例子:
![bcf276c81bac0a196781340006448e90.png](https://i-blog.csdnimg.cn/blog_migrate/d90ac0201668814e08c51e0fb6d9611b.jpeg)
多层表头的优点是便于阅读,但是不是存储数据的标准格式,好在我们使用多层表头的表格,通常为了便于查看,数据通常是统计数据,数据量不大,处理起来也比较方便。我们其实就是要重新构造一个一行的表头,为了不漏掉关键词,我们可以直接将两行合并成一行。
简单说一下步骤:
第一步转置表格:
![f7e824574c6878a599fe2a596b9855e4.png](https://i-blog.csdnimg.cn/blog_migrate/072fa4922a6567de265954b7577373b0.jpeg)
第二步填充第一列空值(第一行):
![e3d6350c6f79e68c27f95eaf08140745.png](https://i-blog.csdnimg.cn/blog_migrate/11ded60ac9418bbb29cc7d743e3bb5cf.jpeg)
第三步合并前两列:
![d47c87e0ed7f674cb7ea9bf3e2b7cdf3.png](https://i-blog.csdnimg.cn/blog_migrate/1124d580e219a5156e846152adf970fd.jpeg)
第四步转置表:
![7cec9871a0f0d92f299070a3c2d30724.png](https://i-blog.csdnimg.cn/blog_migrate/86b363c8c7e5ae151005fac44505a5ce.jpeg)
最后提升标题行:
![48c9298e49cfe7255c794aae69187975.png](https://i-blog.csdnimg.cn/blog_migrate/7fb3b03a65874ec0a8355367306eb2a0.jpeg)
为了方便我们按照这个程序处理多个表格,我们要根据上面的处理步骤,创建一个自定义函数。
![ec4af2640afdca64dcfcdb6809b905a1.png](https://i-blog.csdnimg.cn/blog_migrate/0725c469dfc1cef7eaa3b39cf1f68d9f.jpeg)
然后我们在单文件合并数据时引用这个函数来处理表格,就可以了:
![25e3c8c53e6db9857a0bb3c80d22267e.png](https://i-blog.csdnimg.cn/blog_migrate/ab1e7e6c8a71395879d93ae99914c714.jpeg)
然后展开表格就得到合并的数据了。
上面这个表头处理,其实也包含了另一个问题,就是合并单元格,Power Query中合并单元格并不是不能处理,就是会增加数据清洗的难度,对与确定数据关系的合并单元格,可以使用填充功能,来填充打开合并形成的null。
可能遇到的第二个问题就是列的顺序不一致
我们故意颠倒了一下产品和单价的位置:
![6482bb4543ed2dad960dd3a427fcf7fe.png](https://i-blog.csdnimg.cn/blog_migrate/ce8fc13b5e5fd99e723eb823f3999d6c.jpeg)
如果我们带着表头合并,就会得到下面的结果:
![16c8c326ebcdb0d1138662cef011c437.png](https://i-blog.csdnimg.cn/blog_migrate/67ebccdc3861b4a08e6125030127a364.jpeg)
两个表格的产品单价是错位的。
如果我们用上一个问题的方法处理表头,只要表头名称完全一致,Power Query会很智能的自动纠正这个错位的问题:
![9d3f84fd99263655838774463e56e5cc.png](https://i-blog.csdnimg.cn/blog_migrate/c65b17e64f8607440497f324987b8325.jpeg)
虽然在表格预览中看起来仍然是错位的排列,但是当我们展开表格,顺序就一致了。
![b1d1afee23b88af96b7036b2b09819b4.png](https://i-blog.csdnimg.cn/blog_migrate/01d50a2568ed9005c124bf16b7a817c8.jpeg)
所以对每个表格的预处理是必要的,直接带表头合并,需要完全确定各个表格的数据排列顺序一致,才使用的办法。
可能遇到的第三个问题工作表名称不一致的问题
这个问题是多文件多表合并时会遇到的问题,我们使用Power Query从文件夹合并文件时,就可能遇到这样的问题:
![c9a35e60519460239b7c296c0932dc06.png](https://i-blog.csdnimg.cn/blog_migrate/f19e016d8c0d4b148c24e4d1e62337c6.jpeg)
当出现上面这样的情况,我们在做从文件夹合并多个文件时,就会出错:
![14dcde09b32f983e15fe3f88a318b46d.png](https://i-blog.csdnimg.cn/blog_migrate/d603f7077fc4247fdf858560d95e14c2.jpeg)
多数表格的sheet名称是“表1”,只有本溪公司的sheet名称是“1月”,就是因为sheet名称不一致,导致不能取得本溪公司的数据。
出现这个问题也不要紧,如果只有一个文件不一致,就直接去到这个文件中把sheet名称改成一致的,如果各个文件名称都不一致,但是sheet表的位置都是第一个表,那么我们就要到示例文件中去修改一下:
![2a3d9cad4e8e5e0fd272184994021a5e.png](https://i-blog.csdnimg.cn/blog_migrate/32e1875ac7df32dea996592bc7fd667f.jpeg)
然后我们在去查看一下:
![68eb711d3e561ac64eac877cde9c667e.png](https://i-blog.csdnimg.cn/blog_migrate/6dfca47fbe19f4a49ad25246b7970b87.jpeg)
已经能够正确获得数据。
值得注意的是,不可见字符,我就遇到过sheet名称中含有空格,因为表的位置不是固定的,只能通过sheet名称合并文件,结果就因为一个空格,导致合并失败。在Power Query多文件多表合并中,和VBA一样,可以通过sheet的名称来合并,也可以通过sheet的位置来合并。
就说这么多吧,对Power Query越熟悉,处理意外情况的方法就会越多,不管遇到什么问题,只要认真研究总能找到解决方法。