从图中的数据来看,这不是一个标准的数据明细,因此,无论是怎样的汇总结果要求,目测都是很难直接做数据透视的。 如果是Excel2016(2010或2013需要装官方插件),可以考虑使用新功能Power Query进行数据的处理,转换为标准规范的格式。 比如多月数据汇总、多重行列表头数据转规范一维表等,可以参考以下方法进行规范整理——只有当数据得到规范后,后续的数据透视才能顺利进行,并极大提高数据汇总和统计分析效率。 PQ批量汇总Excel文件就是这么简单原创 大海 Excel到PowerBI小勤:前面看了关于《批量导入文本文件,再也不需要VBA》的文章,发现批量汇总文本文件好简单,但我现在更多的是一大堆格式一样的Excel文件,要汇总起来,怎么办? 大海:如果是以前,真得写VBA,但现在Excel里有了Power Query,这个事情就变得好简单了。 第一步:【数据】-【从文件】-【从文件夹】 第二步:【浏览】选择数据所在的文件夹,【确定】 第三步:数据都在“Content”列中,但是被识别为二进制的内容。右键-【删除其他列】(当然,你也可以不删,或只删掉其他部分列) 第四步:【添加列】-【添加自定义列】-【输入公式:Excel.Workbook([Content])】-【确定】,注意大小写哦,括号里面的内容双击一下右边小窗口里的Content字段就可以了,和Excel里选单元格类似。 第五步:展开数据列表 这将列出你所有工作簿里的所有表,如果某些工作簿里有多张表的话,都会显示在这里: 第六步:继续展开表数据 数据都出来啦! 小勤:是啊!真牛!不过,好像比文本那个还乱呢。 大海:是的,因为多了好多相关的列,但整理数据是Power Query的强项!这个例子也只要3步就搞定了。 第一步:删掉一些没用的列:选择要保留的列,【开始】-【删除列】-【删除其他列】 第二步:借用一下你第一张表里的标题【将第一行用作标题】 第三步:选择删掉其他表里重复的标题行和汇总行(其实跟Excel里的筛选是一摸一样的) 最后,【关闭并上载】数据。 小勤:嗯。我知道了,其实跟文本文件导入的差别就是要将二进制的内容用Excel.Workbook函数解析出来,然后在解析出来的内容里展开数据。对吗? 大海:真聪明。这样理解就行了。
PQ-综合实战:Excel多重行列表头数据转规范一维表原创 大海 Excel到PowerBI小勤:大海,上次看了你《逆透视,二维表转一维表用多重数据透视?弱爆了!》的文章,觉很好用。但是,现在问题又来了,公司有个表居然是多重表头的,感觉搞不定啊。你看: 大海:嗯,这种情况处理起来稍为复杂一点点,不过也不难,都是一些基础操作的灵活运用。 Step-1:获取数据 Step-2:向下填充完善[区域]列 Step-3:用冒号(按自己喜欢选择)合并[区域]和[事业部]列 Step-4:对表格进行转置 Step-5:向下填充[分期]列——关于填充的文章可参考《PQ-数据转换:那个迷倒无数表妹的数据填充技能》 Step-6:合并[分期]和[月份]列 Step-7:将第一行提升为标题 Step-8:逆透视 Step-9:按分隔符(冒号)拆分[区域:事业部]列 Step-10:用同样的方式拆分[分期:月份]列 Step-11:更改列名称 Step-12:数据上载 小勤:嗯,真好。原来只要用填充再加上合并就变成规范的二维表了。 大海:对的,这就是其中的核心思路,因为最终肯定得靠逆透视,所以就先想办法变成通过填充、合并、转置等办法变成规范的二维表。 小勤:知道了。我先动手练一练。
|