Excel多表多文件汇总只会一招?教你几个快速易操作的好方法
- 相关推荐
本文将涉及:数据透视表——用于汇总明细数据的交互式动态表VBA——用于开发自动化脚本语言Power Query——Excel插件,用于简化数据处理操作的Power BI组件Power Pivot——用于在桌面分析大型数据,昵称超级数据透视表
前两天接到一份数据汇总工作,要求将日明细数据,按照ABCD四级关键字,分别汇总指标E、指标F的总和。
多张表按照关键字汇总
一共需要处理四张表,每张表保存在一个文件中。全部明细记录条数有33万行,经过一系列操作之后,最终将33万行,汇总为约1万行。(挺有成就感!)
处理过类似工作的同学都知道,常用条件汇总有四个方法:
方法1——使用函数SUMIF系列函数方法2——数据透视表的多区域汇总方法3——VBA方法4——Power Query原本设想测试四种方法用时,以证明最佳方法。然而……
在测试方法1时,由于需要合并的数据行数太多,非常占资源,多次提示高达94%,导致表哥的小本本黑屏了好几次 (一摸电池都烫手了……心痛)
心疼小本本
虽然没有测试出时间,但证明在处理几十万的数量级记录,使用SUM函数处理,不是个好办法。
依次说明后面三种方法的操作方式。
方法2——数据透视表汇总
通常使用数据透视表,用来透视汇总一张数据表。而实际工作中,可能会出现,需要透视的数据出现在不同的数据表,甚至不同的文件中。
下面这个圈圈,如果用Power Pivot记得勾上,不过这里用大材小用,故不推荐。
吸取了方法1的教训后,方法2的数据透视表,没有将四个文件合并到一张,否则过程也会很糟糕……
但是如果你习惯用数据透视表,建议每个文件分别透视后,将透视结果进行合并。最后再进行汇总。
表哥Tips:此时使用公式SUM或者再一次数据透视表均可。
这个方法,虽然中规中矩,至少可以开展工作,而且也是有一定效率。
方法3——VBA
曾经这个方法效率还是不错的,只要输入一些代码,或者上网搜索好心人的代码,拷到本地运行就可以啦。
但是需要注意的:
(1) 安全性。
大多数同学从网上抄下来的代码,一般都不会去怀疑真伪,虽然能暂时解决问题,但如果这一段并不完美,有可能造成潜在危险。使用前最好自己检查一下,再运行。
表哥Tips:之前安装过Excel工具箱,可以实现多文件合并。大多时候运行比较稳定,但曾经给同事造成Excel崩溃。公司统一卸载掉,没再用过了。
(2) 还是安全性。
网上有好心人分享,但也有心术不正之人,若不幸运行了他的代码,不晓得会被成功引流到哪里去了。
因为表哥胆子小,也不精通VBA,所以才如此谨慎地提醒着大家伙。
表哥Tips:EXCEL发展从一开始只有数据表,到可以自己VBA编写函数,再到Power BI。每一次迭代,都会赋予新的数据处理和分析方法,以提高办公效率。因此才有这种说法,有了VBA就不用公式,有了BI就不用VBA。
所以方法4就用BI的插件试一试。到底有多好。
方法4——Power Query
之前在兑换外币那篇用过他采集汇率。因为真的非常方便快捷,所以这一次多文件汇总非常推荐此法!
简单地说,一共三步就可以完成
第一步:导入文件一共有四个文件,可以一张一张导入,也可以提前将四个文件,放在一个文件夹中,从文件夹导入;
Power Query导入文件
导入时,会看到有窗口右下角的四个按钮,这里一定要选择"转换数据"。意思是想要对导入的数据进行编辑和整理;
导入时的四个按钮,一定选择"转换数据"
之后就会进入Power Query的界面,汇总工作,就是在这里面完成后,直接输出的。
第二步:进入界面后有三点需要留意(1) 查看第一列是否有如下标记,如果有,说明导入的文件中含有多个表。单击这个图标,告诉excel,你想要导入哪一张;
展开向下箭头,从多个文件表中,选择要
(2) 如果有空行,则使用”删除行”去掉那几行,免得干扰汇总数据;
在Power Query删除掉空行
(3) 如果表头是column,而真正的表头出现在第一行时,单击"将第一行用做标题",这样就替换为真正的表头
Power Query将首行作为标题
第三步:通过第一步导入数据,第二步整理数据,接下来就可以汇总数据啦根据文章开篇的要求,以四级关键字,进行汇总。于是用到"分组依据"中"高级"选项;
Power Query使用分组依据进行汇总
单击确定后,33万行的明细数据,约12秒左右即可自动汇总完成啦!
是不是比透视表,上网找代码省了很多时间?
重点是----------不----------费----------电----------脑----------
最后记得单击加载到excel表。就可以完活交差咯!
果你觉得方法还不错,请分享给更多的人看到。
这是对表哥的肯定与鼓励,谢谢↖(^ω^)↗