excel多工作表汇总到一个工作表(多工作簿多表汇总)

excel多工作表汇总到一个工作表(多工作簿多表汇总)(1)

封面

亲爱的小伙伴们,跟我学Excel系列福利来了,从初级一直到高级学习EXCEL系列文章,结合财务实际应用讲解,配合动图细节演示,通俗易懂,是一套比较系统的不可多见学习EXCEL的好文章。持续更新中!

本系列文章包括基础篇(包括技巧、函数)、进阶篇(主要是数据透视表)、高级篇(主要是Power Query)。

希望大家喜欢,欢迎提出宝贵意见和建议!

大家好,我们继续学习Power Query。

四、EXCEL高级篇-Power Query10

10、PQ案例06多工作簿多表汇总

(1)、案例06基础表及需求

我们今天利用PQ做一个多工作簿多表的汇总,这是PQ的一个经典应用案例。

基础表是一个“大广地产2018工资表”的文件夹,这个文件夹只包含我们需要汇总的各个门店的工资表,文件夹包含三个门店的工资表,分别为“1.大广地产-桥华店工资表”、“2.大广地产-爱民店工资表”、“3.大广地产-鼓楼店工资表”,每个工资表包含2018年1月-5月的工资。样表如下,其余所有的工资样式一样,截图一,截图二:

excel多工作表汇总到一个工作表(多工作簿多表汇总)(2)

截图一

excel多工作表汇总到一个工作表(多工作簿多表汇总)(3)

截图二

需求就是,利用PQ汇总工资表。

(2)、PQ操作过程

第一步、在“大广地产2018工资表”文件夹外建立一个单独的汇总表,比如叫“大广地产2018工资表汇总”,如图所示,截图三:

excel多工作表汇总到一个工作表(多工作簿多表汇总)(4)

截图三

第二步、选取文件夹

打开“大广地产2018工资表汇总”工作表,“数据”->“获取数据”->“来自文件”->“从文件夹”,在弹出的对话框中选择文件夹,案例的这个文件夹名称为“大广地产2018工资表”,按路径找到这个文件夹并选择,点击“打开”,动图一。

excel多工作表汇总到一个工作表(多工作簿多表汇总)(5)

动图一

第三步、选取汇总方式

继续上一步,点击对话框右下侧的“组合”旁边的小三角,选择“合并并转换数据”,弹出的对话框,“确定”一般是灰色不可选的,我需要点击“参数1[5]”,点击后,“确定”按钮就可以点击了,动图二。

excel多工作表汇总到一个工作表(多工作簿多表汇总)(6)

动图三

第四步、展开Data

继续上一步,将最后三列删除,展开“Data”,弹出的对话框我们选择所有列,将“使用原始列名作为前缀”的勾选去掉,动图三。

excel多工作表汇总到一个工作表(多工作簿多表汇总)(7)

动图三

第五步、整理查询

“主页”下的“将第一行用作标题”,连续操作两次,将“姓名”“序号”“基本工资”等等这一行作为标题,然后修改第一列标题为“门店”,将“1”修改为“月份”,动图四。

excel多工作表汇总到一个工作表(多工作簿多表汇总)(8)

动图四

第六步、继续整理查询

从“姓名”列筛选,将“null”和“姓名”勾选去除,动图五。

excel多工作表汇总到一个工作表(多工作簿多表汇总)(9)

动图五

第七步、继续整理查询

将“门店”列拆分提取门店,“主页”->“拆分列”->“按分隔符”,系统默认分隔符为“.”,确认,再选定“门店.2”重复上面的步骤,系统默认分隔符为“-”,确定,再选定“门店.2.2”,点击“主页”->“拆分列”->“按字符数”,输入3,选择“一次,尽可能靠右”,确定,然后将“门店.2.2.1”保留,其余拆分后的列都删除,然后将“门店.2.2.1”修改为“门店”,动图六。

excel多工作表汇总到一个工作表(多工作簿多表汇总)(10)

动图六

第七步、上载查询

“关闭并上载至”->“关闭并上载至”->“表”,上载后系统会自动增加一个工作表“大广地产2018工资表”,然后我们就可以利用数据透视表透视出我们想要的结果,动图七。

excel多工作表汇总到一个工作表(多工作簿多表汇总)(11)

动图七

后续有新的工资表,比如6月的工资表完成后,我们只需要将所有门店的工资表复制到这个文件夹下,然后,打开汇总表,刷新“大广地产2018工资表”就得到了一个新的所有数据的工资总表。

因为PQ是一个非常庞大而且复杂的功能,PQ基础操作部分已经将主要实用功能都涉及了,本系列Excel基础操作暂时就结束了。

后续PQ高级操作还有很多,我们以后根据情况会推出新的更新系列。

谢谢大家!

附言:演示数据已发至公共邮箱,再次提醒用OFFICE2016及以上版本才可以看演示数据和操作PQ。公共邮箱:excel147@163.com,公共邮箱密码:Excel258。

,

免责声明:本文仅代表文章作者的个人观点,与本站无关。其原创性、真实性以及文中陈述文字和内容未经本站证实,对本文以及其中全部或者部分内容文字的真实性、完整性和原创性本站不作任何保证或承诺,请读者仅作参考,并自行核实相关内容。