小勤:能用数据透视实现从数据明细到这样的数据汇总吗?
大海:对不起,不能啊。一定要这样吗?说(shui)服一下领导用标准的数据透视呗。
小勤:说不服不了啊!领导说这样最直观。那Power Pivot行吗?
大海:Power Pivot也不能直接实现,不过可以考虑Power Query和Power Pivot结合来实现,或者考虑用Power Pivot生成数据透视表后转为OLAP公式实现。
小勤:这么复杂?
大海:先说一下这种非标准的数据汇总特殊的地方。
- 区域合计数与区域在同一维度:这相当于在区域这个维度上增加了一项同级别的内容,而不仅仅是数据透视表中的合计项显示问题了,这种数据追加的问题,可以考虑用Power Query的追加合并功能来实现。
- 跨维度合并数据:这相当于在一个细的维度上去统计另一个维度上的内容,但好在只是计算问题,所以可以通过Power Pivot的DAX函数来实现,当然,也可以通过Power Query的合并查询(横向扩展)功能来实现。
小勤:那总体看起来就可以直接用Power Query来实现了?
大海:是的。但Power Query出来的结果是不能合并单元格的。呵呵。
小勤:没有合并单元格也能接受。
大海:那就先用Power Query实现一下,后面我再跟你讲怎么Power Query跟Power Pivot结合起来做完整的。
Step-01:以仅创建链接的方式获取数据
Step-02:分组生成各区域及细类汇总
Step-03:引用分区汇总表,并按需要修改名字
Step-04:在引用的查询中再次分组,生成三区合并的销量数据
Step-05:添加自定义列,使三区合计的表与分区域的表结构一致
Step-06:将原分区的表和三区合并的表进行追加查询,合并成新的查询
Step-07:用新的查询再次合并“三区合并”查询,获得最右侧的三区合计列
Step-08:展开合并的数据
Step-09:按需要重命名列
Step-10:排序
结果出来了,最后上载数据:
小勤:感觉还好啊,也不算复杂。简单来说就是先分组得到各个区域的,然后另外在建个查询分组得到三区合计的,然后用各个区域的纵向追加三区合计的,再横向和三区合计的合并起来……
大海:嗯。整个过程主要就是这个思路。
小勤:知道了。以后这种报表非标准的报表要自动生成也不怕了。反正就是通过Power Query各种拼接。呵呵。
大海:对的。Power Query可以用来做这种数据的拼接,但是一般还是建议最好用标准化的数据透视表,那样统计的效率会高很多。
小勤:对了,你说再结合Power Pivot可以连那些区域合并单元格也实现了?
大海:嗯。可以的。以后再跟你讲吧。
小勤:好的。好期待啊。