这份数据能把人逼疯,实际上处理起来非常简单

真是什么奇怪的事情都有!
我介绍过一个真实的报表的需求,就是制作一个汇总明细数据在一起的报表:

刚开始把我难住了。经过了一番研究,实现了这个报表:

本来我以为够奇怪的了,结果又有一个需求,不过这次反过来了。
一份这样的源数据:

需要分析每个公司或者每个产品的销售情况。
看上去很简单对不对?只要用数据透视表很容易就可以搞定。
但是,注意看黄色框里的数字,这些不同的公司,不同的产品,不同的数量是在一个单元格里,用换行符来制作出好像是不同行的样子。
这可怎么办?
实际上还是有一些办法的。其中使用Excel的传统方法,就需要很多步骤,而且基本上是完全手动的处理了,这个过程完全会把人逼疯。
我们还是使用Power Query来完成比较好😀。
先来看渠道这一列如何处理。

01
拆分渠道列
选中表格区域的任意单元格,点击“数据”选项卡中的“从表格”:

数据导入到Power Query编辑器中:

删除产品和销售额两列:

选中渠道列,在转换选项卡中,点击拆分列,点击按分隔符:

在对话框中,按照如下图的方式进行设置:

点击确定,得到结果:

在添加列选项卡中,点击索引列:

添加成功索引列:

在右侧查询设置面板中,将名称修改为“渠道”:

在主页选项卡中,点击关闭并上载至:

在对话框中,选择“仅创建连接”:

点击加载,完成这一步。

02
拆分产品和销售额列
重复上一节的步骤,拆分产品和销售额两列,并分别将查询命名为产品和数量:

完成后,我们拥有3个查询:


03
得到最终结果
在“数据”选项卡中,点击新建查询,合并查询,点击合并:

在弹出的对话框中,将上面第一个表选择为渠道,下面选择为产品,将连接种类选择为左外部,并分别点击上表和下表中的索引列进行关联:

点击确定,来到Power Query编辑器:

在“主页”选项卡中,点击“合并查询”:

在弹出的对话框中,保持上面第一个表不变,将下面的表格选择为销售额,连接种类选择为左外部,分别点击两个表的索引列进行关联:

点击确定,得到表格:

点击产品列标题右侧的展开按钮,选择产品列,然后点击确定:

同样展开销售额:

删除索引列:

修改后产品.1列名为产品:

同样将销售额.1修改为销售额。
在主页选项卡中,点击关闭并上载:

得到结果:

大功告成!✌
加入E学会,学习更多Excel数据处理方法