二维码 购物车
部落窝在线教育欢迎您!

别再为拆分、合并工作表闹心啦!最实用的7种方法,分分钟搞定它!(合并篇)

 

作者:E图表述来源:部落窝教育发布时间:2019-11-11 17:17:25点击:7594

分享到:
0
收藏    收藏人气:0人
版权说明: 原创作品,禁止转载。

编按:

哈喽,大家好!在上篇文章中我们给大家介绍了4种拆分工作表的方法,分别是函数、透视表、高级筛选、VBA,不知道小伙伴们学习得咋样了?今天我们将学习合并工作表的三种方法,赶紧来看看吧!(由于合并工作表的第一种方法函数法,涉及的函数的应用相对复杂,在函数方面比较薄弱的同学,可以先看第二、三种方法,再继续学习第一种~)

 

【前言】

 

在上篇文章中,对于总表拆分为分表的操作一共给大家分享了四种方法,建议同学们一定要勤加练习,才能熟能生巧。既然说了拆分,那么就没有道理不说“合并”。同样的,在日常工作中,合并各个分表到总表,也是经常会遇到的。

 


一、各个分表合并到总表——函数流



既然在“拆分”工作表的时候,我们使用了函数的方式,那么就来再感受一下“合并”工作表的函数方式吧!依然使用之前的分表作为我们合并工作表的数据源。

 

 

步骤1利用名称管理器把工作表名称建立为内存数组。按CTRL+F3,在弹出的“名称管理器”窗口中,点击“新建”,参照下图进行设置:

 

 

函数:=GET.WORKBOOK(1)&T(NOW())

 

利用宏表函数“GET.WORKBOOK(1)”,得到工作薄内所有工作表名称,并形成一个内存数组,把这个内存数组命名为“SHNAME”。T(NOW())是利用NOW函数的易失性,可以使宏表函数“GET.WORKBOOK(1)”自动更新。因为NOW函数返回的是时间格式的数值,T函数可以将数值转换为空,而时间日期是特殊的数值,所以T(NOW())的结构将返回空文本“”,这样返回值的内容就是工作表名称了(注意这里有坑,下面填坑)。

 

步骤2新建一个空白工作表,命名为“汇总”,在《汇总》工作表的A1单元格中输入“工作表”,在B1:K1区域复制粘贴分表的表头字段,在A2单元格输入函数:

 

=MID(INDEX(SHNAME,INT((ROW(A1)-1)/15)+1),FIND("]",INDEX(SHNAME,INT((ROW(A1)-1)/15)+1))+1,99)。如下图:

 

 

【函数解析】

 

这个嵌套函数的解析,要从上面提到的“坑”开始说起,GET.WORKBOOK1)提取后的返回值是:[工作薄名称]工作表名称,这样的格式。如果我们只需要提取工作表名称,就要使用文本处理函数MID来提取(当然也可以用RIGHT函数,大家可以自己试一下),通过FIND函数找到“]”的起始位置再加1,就是工作表名称的起始位置,用99作为MID函数的第三参数,来确定提取的字符串长度(如果提取长度超出实际长度,默认提取实际长度)。

 

这部分的内容,我们在制作excel中的目录的文章中,详细讲解过,具体可以点击链接《用GET.WORKBOOK函数实现excel批量生成带超链接目录且自动更新,进行学习。

 

那么这个函数最难理解的部分来了:INDEX(SHNAME,INT((ROW(A1)-1)/15)+1)

 

SHNAME是什么?是我们刚才在名称管理器中设置的自定义名称。在名称管理器中使用了提取工作表名称的宏表函数后,那么就形成了一个内存数组,数组的内容是{分表1;分表2;分表3;汇总}四个内容,再用INDEX函数分别提取某个位置的内容(即提取出工作表名称)。

 

因为每个分表中的明细数据都不可能是只有一条记录条,所以我们对于SHNAME中的工作表名称也不应该只提取一次,因此使用INT((ROW(A1)-1)/15)+1来确定我们引出分表名称的次数。

 

对于这个函数的理解,需要空间感和数学思维相结合:

 

1ROW(A1)=1, INT((ROW(A1)-1)/15)+1 = INT(0/15)+1 = 0+1 =1

2ROW(A2)=2, INT((ROW(A2)-1)/15)+1 = INT(1/15)+1 = 0+1 =1

3ROW(A3)=3, INT((ROW(A3)-1)/15)+1 = INT(2/15)+1 = 0+1 =1

15ROW(A15)=15, INT((ROW(A15)-1)/15)+1 = INT(14/15)+1 = 0+1 =1

16ROW(A16)=16, INT((ROW(A16)-1)/15)+1 = INT(15/15)+1 = 1+1 =2

17ROW(A17)=17, INT((ROW(A17)-1)/15)+1 = INT(16/15)+1 = 1+1 =2

30ROW(A30)=30, INT((ROW(A30)-1)/15)+1 = INT(29/15)+1 = 1+1 =2

31ROW(A31)=31, INT((ROW(A31)-1)/15)+1 = INT(30/15)+1 = 2+1 =3

 

大家可以看出来,当行号减1等于我们设定的值“15”的时候,这个等式的值就会累加1 。这个15就是我们设定的最大引用记录条的数值,算式就可以按这个数字,限定每个工作表名称的引用次数。如果我们的各分表明细中最多的记录条有6235行,那我们就设置这个值为INT((ROW(A1)-1)/6300)+1

 

步骤3删除错误值和名为“汇总”的数据。

 

 

步骤4B2单元格中,根据分表名称,提取分表中对应位置的数据,函数如下:

 

=INDIRECT(CONCATENATE($A2,"!",ADDRESS(COUNTIF($A$2:$A2,$A2)+1,COLUMN(A2))))

 

这三个函数其实都是常用函数,但是很多同学都不理解此时为什么用这三个函数来嵌套,我们来看看下图,估计会对同学们有所帮助。

 

 

B2单元格返回“《永达》表中的A2单元格”,如果直接引用的话,我们可以使用表达式“=永达!A2”。但是如果我们要动态的引用这个工作表的其他单元格地址,就需要使用上述函数过程:

 

第一阶段——得到地址名:

 

ADDRESS(COUNTIF($A$2:$A2,$A2)+1,COLUMN(A2)),利用工作表名称出现的次数,使用COUNTIF函数得到{1,2,3,…,15}的行号,再加1,就能得到对应目标工作表的引用行号,再使用COLUMN函数得到对应的列号。最后通过ADDRESS函数,返回行号列号确定的单元格地址;

 

第二阶段——确定此单元格地址,属于哪个工作表:

 

CONCATENATE($A2,"!",ADDRESS(COUNTIF($A$2:$A2,$A2)+1,COLUMN(A2)))CONCATENATE函数是一个连接文本的函数,A2单元格是工作表名称“永达”,所以此函数运行后,就得到了“永达!A2”的字符串;

 

第三阶段——使字符串形成引用地址:

 

INDIRECT函数引用连接,并返回引用地址值的函数。用INDIRECT函数引用刚才的“永达!A2”字符串,得到对应的单元格内容。

 

最后,将此函数向左填充、再向下填充,就可以得到我们各个分表的明细汇总了。

 

 

步骤5因为我们使用了宏表函数,所以保存时,我们要另存为.XLSM格式的文件。

 

【小结】

 

从上图中我们不难看出,如果对应的分表数据没有15行的时候,那么就会显示0;而如果对应的分表数据超出设定的15行,那么数据就会引出不全。

 

所以我们既要考虑最大行数的设定,做出汇总表后,还要筛选出为0的行进行删除,这样就会给我们后续的工作增加很多操作的步骤。

 


二、各个分表合并到总表——PQ



PQ是什么?PQPOWER QUERY的缩写(以下简称PQ),是EXCEL中一个查询模块,对于不是像作者这样需要写文章、写教程的同学来说,咱们不用知道它的各种解释,只要知道它的功能即可。就像工作表函数,我们知道IFSUM如何使用就行,没有必要知道这些内置函数存储在EXCEL的什么位置。

 

EXCEL2016PQ是自带的,如下图:

 

 

EXCEL2010版之前的版本是没有PQ的,EXCEL2010EXCEL2013版需要下载PQ插件。

 

步骤1启动PQ编辑器,如下图:

 

 

步骤2在“主页”选项卡中,点选“新建源”——“文件”——“EXCEL”,在弹出的“导入数据”窗口中,按路径找到需要合并分表的源文件,点击“导入”按钮,弹出“导航器”窗口;

 

 

步骤3在“导航器”窗口中,勾选“选择多项”,然后复选所有分表,再点击“确定”按钮将数据导入到PQ中,如下图;

 

 

 

步骤4在“主页”选项卡下的“组合”下拉菜单中,点击“追加查询”功能按键,弹出“追加”窗口。

 


 

将所有非当前的工作表,逐一全部追加到右面的列表框中,点击“确定”按钮,此时就将其他的工作表都追加到了当前的工作表中。

 

步骤5点击“主页”中左上角的“关闭并上载”按键,将新建查询导入此EXCEL工作薄中,如下图:

 

 

步骤6保留下汇总的工作表,删除其他的工作表,任务就完成了。

 

【小结】

 

没有复杂的函数,所有的操作只需要鼠标点击即可完成,是不是很方便,那么以后如果有再多的工作表合并的问题,都是分分钟搞定了吧。

 


三、各个分表合并到总表——VBA



“没有完美的方法,只有完美的操作体系”。EXCEL带给我们的好像就是这么一个完美的操作体系,永远都是“一题多解”,如果当你既不想费劲去写函数,还想一劳永逸的合并工作表,那就采用VBA的方式来处理。

 

有很多同学都是“谈VBA色变”,但是作者E图表述要说,当你学了VBA才会真正的掌握EXCEL这个软件。

 

ALT+F11组合键打开VBE界面,新建“模块1”,在代码区域输入下面的代码,操作一波看看吧。

 

 

Sub 合并工作表()

  Sheets("汇总").Range("A2:J65000").ClearContents

  For Each sh In Worksheets

    If sh.Name <> "汇总" Then

      a = Sheets("汇总").Range("A65000").End(3).Row + 1

      b = sh.Range("A65000").End(3).Row

      sh.Range("A2:J" & b).Copy Sheets("汇总").Cells(a, 1)

    End If

  Next

End Sub

 

代码中的Range是单元格区域对象的书写方式,括号中的J代表数据区域的末列,同学们如果操作自己的表格的时候,可以改成自己数据的末列列标,并把代码中的“汇总”改为自己汇总表的名称,即可。

 

【编后语】

 

同工作薄的“拆分工作表”和“合并工作表”的方法给大家列举了很多很多,在实际工作中,无论你掌握了哪种方法都可以让你有的放矢的去做,最怕的就是你没有一个方法傍身,那就真的无从下手了。

 

本文配套的练习课件请加入QQ群:747953401下载。

Excel高手,快速提升工作效率,部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你!

扫下方二维码关注公众号,可随时随地学习Excel

IMG_256

相关推荐:

拆分与合并(拆分篇)别再为拆分、合并工作表闹心啦!最实用的7种方法,分分钟搞定它!(拆分篇)

PQ合并数据的详细案例Excel一键生成报表教程:powerquery多表合并案例

INDIRECT函数大神专用求和公式!带你揭秘自动统计前几名数据合计的新套路!(下篇)