情形:我公司下属有20多家分子公司,2019年初要统计整个集团的纳税情况,我制作了一个excel模板(如下图所示)上传到群共享里面,让各分子公司下载填列。大家填好后,我收集到了20多个excel工作簿。
问题:我有什么简便快捷的方法,能生成一张汇总工作表,并且,下属企业如果填报有误,想更正时我的汇总工作簿能够及时更新了?
最笨的办法,也是大部分人经常操作的方法,就是一张一张表复制粘贴到一张新表中,高级点的可能会建立个公式链接。但是如果我公司下属如果有100多家分子公司,那估计要全神贯注的复制粘贴到崩溃。
今天小熊给大家分享一个excel数据查询功能,来完美实现快速解决以上问题,并且一劳永逸。
这个功能就叫PowerQuery。
步骤一:将收集好的excel待汇总数据工作簿放到一个统一文件夹待汇总。
我这里是举例子,没有把我公司下属企业数据放齐,放了5家的。在建立一个新的excel工作簿 汇总结果。
步骤二:打开汇总结果工作簿,点击数据——新建查询——从文件——从文件夹。
从弹出的对话框里面选择待汇总文件所在路径,点击确定。
接着从弹出的对话框里面继续点击转换数据,进入到数据查询界面。
步骤三:选中前两列,将其他记录工作簿信息的数据列全部删除,只保留前两列。通过点击删除列——删除其他列实现。
删除后结果如下:
步骤四:添加列选项,在添加列选项卡中,点击自定义列,弹出以下对话框。输入公式=Excel.Workbook([Content]),此处公式要区分大小写。点击确定。
添加列完成后,多了内容为table的自定义列,要合并的工作簿数据就隐藏在此列里面。
点击自定义右边的枢纽按钮,出现下图,只勾选扩展data,点击确定。
又出现了隐藏的表格table,继续点击自定义右方枢纽按钮,从弹出的如下图对话框里面直接点确定。
文件夹里面的所有工作簿数据就合并在一起了。
步骤五:数据加工。
1、有几个合并工作簿,就会有几个列标签,点击将第一行用做标题按钮,点击后,第一行标题行会出现删选枢纽。
2、选中第一列,将其删除。
3、点击第一行标题行里面的税总的筛选按钮,从弹出的对话框里面需要统计的税信息:个人所得税、企业所得税 、其他税收、增值税。点击确定。
步骤六:此时需要的信息明细以及查找完毕,点击开始——关闭并上载。最终实现了工作簿的合并。
利用数据透视表,实现分类合并求和。点击数据透视表,从弹出的对话框里面默认直接点确定。
将税种拖到列标签,缴纳金额拖到值标签后,如下图所示。整个集团分税总的合计缴纳数就统计完成。
以上我的待汇总文件夹里面,只有5家下属公式的数据,如果其他15家的数据收集起后,我继续放到待汇总文件夹里面,待汇总文件夹里面就有我们集团所有分子公式的数据了,此时,我在在汇总结果sheet1表格里面点击刷新,例外15家的数据的也跑到汇总结果里面来了,我在打开数据透视表sheet3,继续点刷新,所有公司的分税总合计数也会自动更新。当然,如果更改,待汇总文件任意公司的数据,在汇总结果工作表里面点击刷新,会自动更新。是不是很方便。如果觉得有用。请分享,请点在看,请关注!