欢迎光临草根哥
www.cgg6.com

合并任意多个Excel工作表的三个公式

如果要把不同的工作表合并在一个表里,有什么比较简便的方法呢?

今天教你一招,只要学会使用这三个Excel函数公式,就可以合并任意多个工作表。

 

【案例】在下图所示的工作簿里,有行数可能存在差别、但格式却相同的四个表格,现在如果让你把这4个表合并到一个“汇总”表中,你会怎么做?

 


【公式】

1、公式 – 名称管理器 – 新建名称 – 在新建名称中输入名称“sh”,然后“引用位置”框中输入公式:

=MID(GET.WORKBOOK(1),FIND(“]”,GET.WORKBOOK(1))+1,99)&T(now())

 

解析:

GET.WORKBOOK(1)表示的是宏表函数,当参数为1时,就可以获取工作簿中各个工作表名称。因为名称里带有工作簿的名称,所以要用FIND+MID截取只含工作表名称的字符串,而&T(now())则是为了能让公式完成自动更新。

 

 


2、在A列输入下面公式:

 

=INDEX(sh,INT((ROW(A1)-1)/6)+1)

 

解析:

这个公式是为了在A列自动填充工作表名称,并做到每隔N行更换填充下一个名称。在公式里的6是各表格的现在或将来更新后最大行数,需要尽量把它设置的更大一些,以免今后需要增加行汇总表时不能更新所需数据。sh是第1步添加的名称。

 


3、在B2输入公式并向右向下填充,取得各表的数据。

 

=INDIRECT($A2&”!”&ADDRESS(COUNTIF($A$1:$A2,$A2)+1,COLUMN(A1)))

 

解析:

这个公式是为了根据A列的表名称,用indirect函数取得该表的值。其中address函数是基于行和列数来生成单元格地址,如address(1,1)的结果是$A$1。

 

公式设置、复制完成,这时你就会看到各表的数据已经合并起来了!如下图所示:

 

 

若你删除表格,汇总表中就会自动删除该表的数据,

当你增加新工作表,该表数据又可以自动添加进来。

 

 

【注意】:在保存文件时会出现提示语“文件含4.0宏”,窗口中点“否”按钮后文件另存为 启用宏的工作簿类型。

 

赞(0)
版权声明:
文章名称:《合并任意多个Excel工作表的三个公式》
文章链接:http://www.cgg6.com/4298.html
声明:文章版权归本站自创作者所有,未经允许不得转载

评论 抢沙发

评论前必须登录!