excel数据处理:快速提取多工作簿数据

  本文分享excel多个工做簿查询数据提与汇总办法,利用到Power Query插件去完成Excel差别工做簿数据汇总.

小王所正在的公司正在天下各天皆有分部,每到年终小王皆很头痛。各个天域的贩卖数据需供汇总,虽然工做簿模板分歧,可是天下那终多都会,工做簿也要一一翻开复制粘掀数据。工做簿容量有的年夜有的小,一个个翻开要破费年夜批的工妇。那有无甚么好办法能够没有消挨完工做簿间接提与数据呢?明天给各人引睹了两种办法去完成。

如图,正在桌里那个文件夹及第例阐明了五个都会的12个月的贩卖数据。

  此中每一个工做簿正在“贩卖额”工做表下存储的是该都会1-12月的数据,现正在要没有挨完工做簿批量提与各个都会12月份的开计值,也便是“贩卖额”工做表C14单位格的值。

  1、设置援用公式法提与

1.正在该文件夹下,新建一个记事本,输进代码dir *.xlsx /b >1.txt ,保留范例挑选“一切文件”,另存为bat文件。

  2.单击新建好的bat文件,该文件夹便会天死1.txt文件,翻开文件便可以看到当前文件夹下的一切xlsx文件的文件名。经由过程那类圆法我们便获与到了该文件夹一切的工做簿称号。

  3.新建一个工做簿用去存储提与到的数据。以下图所示,把获与到的工做簿称号输进A列,现正在要把各个工做簿C14的值放进对应的B列。正在B1单位格列输进

=C:\Users\Administrator\Desktop\贩卖\[&A1&]贩卖额!C14 ,正在单位格隐现为C:\Users\Administrator\Desktop\贩卖\[北京.xlsx]贩卖额!C14 ,也便是文件夹下“北京”工做簿的“贩卖额”工做表的C14单位格,然后下推减补。

  4.选中B列复制然后粘掀为值

  5.按住Ctrl+H,翻开“查找战交换”窗心,把 C 交换成 =C ,面击“局部交换”。

  如许单位格的值便酿成各工做簿的开计值。

  那类办法正在真践操做中很便利,上里获与文件夹工做簿称号的办法也很适用。可是范围性便是提与的值必需正在一切表格的统一单位格内。那有无甚么办法能够没有按单位格间接提掏出月份为开计那一止的贩卖额呢?之前给各人的引睹的Power Query便可以够完成。

2、Power Query提与

面击数据选项卡下,新建查询—从文件—从文件夹。

  2.阅读窗心找到文件夹的途径,面击肯定。

  文件夹窗心面击编纂。

  3.正在Power Query编纂器显现的便是该文件夹下的一切内容,那个之前给各人引睹过,把【content】那列binary格局转换成table格局提与data便可以够提与文件夹各个表格的数据。我们那里只列出步调,详细引睹能够面击那里检察:插进链接

  面击增减列选项卡下的自界说列。

  正在自界说列窗心的列公式下输进 =Excel.Workbook([Content]) 面击肯定。

  4.把除【Name】战【自界说】两列之中的其他列删除。按住Ctrl选中两列,左键挑选删除其他列。

  5.面击【自界说】列左边的睁开按钮,睁开Data那列,没有勾选“利用本初列名做为前缀”。

  然后再面击睁开的【Data】列左边的睁开按钮,睁开一切列。没有勾选“利用本初列名做为前缀”

  睁开成果以下:

  6.那现正在要做的便是把【Column2】那列的开计挑选进来便可以够了。面击左边的挑选按钮,勾选“开计”,面击肯定。

  睁开成果以下:

  7.接下去要做的便是把那个上载到表格。面击开初选项卡下的启闭并上载。

  表格以下:

  利用Power Query便比力智能,那类办法没有限制单位格天位,按照前提批量提与跨工做簿的单位格值。更减适用快速。

古晨给各人引睹的年夜部门皆是Power Query最根底的图形化操做,操纵简朴的图形化操做便可以完成那么多功用,减班的小同陪们借等甚么,赶松珍躲吧!

****部降窝教育-excel多工做簿查询数据提与汇总****

本创:夏雪/部降窝教育(已经赞成,请勿转载)

更多教程:部降窝教育()

微信公家号:exceljiaocheng

相关教育文章

Leave a Comment

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据