精彩课程推荐

excel拆分合并技巧:将工作表合并成总表的方法

[复制链接]
查看: 349|回复: 1
发表于 2020-1-24 16:58:18 | 显示全部楼层 |阅读模式

马上注册,结交更多好友,享用更多功能,让你轻松玩转社区。

您需要 登录 才可以下载或查看,没有帐号?立即注册

x
【前言】
在上篇文章中,对于总表拆分为分表的操作一共给大家分享了四种方法,建议同学们一定要勤加练习,才能熟能生巧。既然说了拆分,那么就没有道理不说“合并”。同样的,在日常工作中,合并各个分表到总表,也是经常会遇到的。
一、各个分表合并到总表——函数流
既然在“拆分”工作表的时候,我们使用了函数的方式,那么就来再感受一下“合并”工作表的函数方式吧!依然使用之前的分表作为我们合并工作表的数据源。
image.jpg

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

函数:=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)。如下图:
image.jpg

【函数解析】
这个嵌套函数的解析,要从上面提到的“坑”开始说起,GET.WORKBOOK(1)提取后的返回值是:[工作薄名称]工作表名称,这样的格式。如果我们只需要提取工作表名称,就要使用文本处理函数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来确定我们引出分表名称的次数。
对于这个函数的理解,需要空间感和数学思维相结合:
1:ROW(A1)=1, INT((ROW(A1)-1)/15)+1=INT(0/15)+1=0+1=1
2:ROW(A2)=2, INT((ROW(A2)-1)/15)+1=INT(1/15)+1=0+1=1
3:ROW(A3)=3, INT((ROW(A3)-1)/15)+1=INT(2/15)+1=0+1=1
15:ROW(A15)=15, INT((ROW(A15)-1)/15)+1=INT(14/15)+1=0+1=1
16:ROW(A16)=16, INT((ROW(A16)-1)/15)+1=INT(15/15)+1=1+1=2
17:ROW(A17)=17, INT((ROW(A17)-1)/15)+1=INT(16/15)+1=1+1=2
30:ROW(A30)=30, INT((ROW(A30)-1)/15)+1=INT(29/15)+1=1+1=2
31:ROW(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:删除错误值和名为“汇总”的数据。
image.jpg

步骤4:在B2单元格中,根据分表名称,提取分表中对应位置的数据,函数如下:
=INDIRECT(CONCATENATE($A2,"!",ADDRESS(COUNTIF($A$2A2,$A2)+1,COLUMN(A2))))
这三个函数其实都是常用函数,但是很多同学都不理解此时为什么用这三个函数来嵌套,我们来看看下图,估计会对同学们有所帮助。
image.jpg

B2单元格返回“《永达》表中的A2单元格”,如果直接引用的话,我们可以使用表达式“=永达!A2”。但是如果我们要动态的引用这个工作表的其他单元格地址,就需要使用上述函数过程:
第一阶段——得到地址名:
ADDRESS(COUNTIF($A$2A2,$A2)+1,COLUMN(A2)),利用工作表名称出现的次数,使用COUNTIF函数得到{1,2,3,…,15}的行号,再加1,就能得到对应目标工作表的引用行号,再使用COLUMN函数得到对应的列号。最后通过ADDRESS函数,返回行号列号确定的单元格地址。
第二阶段——确定此单元格地址,属于哪个工作表:
CONCATENATE($A2,"!",ADDRESS(COUNTIF($A$2A2,$A2)+1,COLUMN(A2))),CONCATENATE函数是一个连接文本的函数,A2单元格是工作表名称“永达”,所以此函数运行后,就得到了“永达!A2”的字符串。
第三阶段——使字符串形成引用地址:
INDIRECT函数引用连接,并返回引用地址值的函数。用INDIRECT函数引用刚才的“永达!A2”字符串,得到对应的单元格内容。
最后,将此函数向左填充、再向下填充,就可以得到我们各个分表的明细汇总了。
image.jpg

步骤5:因为我们使用了宏表函数,所以保存时,我们要另存为.XLSM格式的文件。
【小结】
从上图中我们不难看出,如果对应的分表数据没有15行的时候,那么就会显示0;而如果对应的分表数据超出设定的15行,那么数据就会引出不全。
所以我们既要考虑最大行数的设定,做出汇总表后,还要筛选出为0的行进行删除,这样就会给我们后续的工作增加很多操作的步骤。
二、各个分表合并到总表——PQ流
PQ是什么?PQ是POWER QUERY的缩写(以下简称PQ),是EXCEL中一个查询模块,对于不是像作者这样需要写文章、写教程的同学来说,咱们不用知道它的各种解释,只要知道它的功能即可。就像工作表函数,我们知道IF、SUM如何使用就行,没有必要知道这些内置函数存储在EXCEL的什么位置。
在EXCEL2016中PQ是自带的,如下图:
image.jpg

EXCEL2010版之前的版本是没有PQ的,EXCEL2010和EXCEL2013版需要下载PQ插件。
步骤1:启动PQ编辑器,如下图:
image.jpg

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

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

image.jpg

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

image.jpg

将所有非当前的工作表,逐一全部追加到右面的列表框中,点击“确定”按钮,此时就将其他的工作表都追加到了当前的工作表中。
步骤5:点击“主页”中左上角的“关闭并上载”按键,将新建查询导入此EXCEL工作薄中,如下图:
image.jpg

步骤6:保留下汇总的工作表,删除其他的工作表,任务就完成了。
【小结】
没有复杂的函数,所有的操作只需要鼠标点击即可完成,是不是很方便,那么以后如果有再多的工作表合并的问题,都是分分钟搞定了吧。
三、各个分表合并到总表——VBA流
“没有完美的方法,只有完美的操作体系”。EXCEL带给我们的好像就是这么一个完美的操作体系,永远都是“一题多解”,如果当你既不想费劲去写函数,还想一劳永逸的合并工作表,那就采用VBA的方式来处理。
有很多同学都是“谈VBA色变”,但是作者E图表述要说,当你学了VBA才会真正的掌握EXCEL这个软件。
按ALT+F11组合键打开VBE界面,新建“模块1”,在代码区域输入下面的代码,操作一波看看吧。
image.jpg

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代表数据区域的末列,同学们如果操作自己的表格的时候,可以改成自己数据的末列列标,并把代码中的“汇总”改为自己汇总表的名称,即可。
?
【编后语】
同工作薄的“拆分工作表”和“合并工作表”的方法给大家列举了很多很多,在实际工作中,无论你掌握了哪种方法都可以让你有的放矢的去做,最怕的就是你没有一个方法傍身,那就真的无从下手了。






上一篇:Excel达人之路,良好的工作习惯是基础
下一篇:备考注册会计师有哪些好的学习方法?

2836

主题

3109

帖子

16万

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
163910

突出贡献优秀版主荣誉管理论坛元老管理员

推荐:原价298现活动价只需¥98 升级VIP会员就可以免费学习本站全部资源【点击立即开通】
发表于 2020-1-26 00:32:25 | 显示全部楼层
谢谢楼主的分享 谢谢

0

主题

58

帖子

111

积分

二星会员

Rank: 2

积分
111
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

商务合作

客服热线:400-808-2188
点击这里给我发消息

关注微信

Copyright;  ©2013-2020  天天会计网  网站地图  友情链接   ( 粤ICP备16059295号-1 )