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

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

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

步骤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.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:删除错误值和名为“汇总”的数据。

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

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

在EXCEL2016中PQ是自带的,如下图:

EXCEL2010版之前的版本是没有PQ的,EXCEL2010和EXCEL2013版需要下载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代表数据区域的末列,同学们如果操作自己的表格的时候,可以改成自己数据的末列列标,并把代码中的“汇总”改为自己汇总表的名称,即可。

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

qingguo1979

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值