再见Ctrl + C!合并100个Excel表格,只需30秒!

编按:哈喽,大家好!在上篇文章中我们给大家介绍了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.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)

相关推荐

  • 如何用一个简单函数,创建各个分表返回总表的超链接?

    每天一篇Excel技术图文 微信公众号:Excel星球 NO.755-照亮分表回家的路 作者:看见星光  微博:EXCELers / 知识星球:Excel HI,大家好,我是星光.之前给大家分享过一期 ...

  • Excel用vlookup函数跨表调取数据的操作方法

    导读: Excel是日常办公中常用到的一款办公软件,有很多小伙伴不会在Excel中用vlookup函数跨表调取数据,下面小编就来分享一下在Excel中用vlookup函数跨表调取数据的方法.感兴趣的朋 ...

  • 你一定还不会跨工作表求和,快来这里看看吧!

    送人玫瑰,手有余香,请将文章分享给更多朋友 动手操作是熟练掌握EXCEL的最快捷途径! 有这样一个问题: 某品牌的店面在不同区域的销售情况如下表.这些店面分属于两个区域.每个门店表格的格式完全相同. ...

  • 再见Ctrl + V!合并100个单元格的内容,用它只需30秒【Excel教程】

    全套Excel视频教程,扫码观看 编按: 哈喽,大家好!用excel连接字符串,是我们在日常工作中,比较常用的技巧.相信大家使用最多的连接方式就是"&".但其实,在exce ...

  • 再见Ctrl+C!单列数据转换成多列数据,用它只需30秒!

    送你200篇独家Excel精华教程 全套Excel视频,扫码观看! 每天一点小技能 职场打怪不得怂 编按:别再Ctrl+C了!简单的工作做一万遍也会让人心力交瘁!在办公中,我们常常会遇到把单列数据变成 ...

  • 合并同类项,Wps表格只须一秒完成!

    表格中相同的部门,要求批量合并成一个单元格,如果只是几行,手工合并也不麻烦,但如果有几十几百行呢? 如果在Excel表格中,需要好多步骤: 分类汇总 定位空值并合并 用格式刷刷 取消分类汇总 删除辅助 ...

  • 再见Ctrl+C!按指定次数生成上千行数据,这个方法只需30秒!

    ★ 编按 ★ 哈喽各位小伙伴,Power Query(后文简称PQ)作为数据清洗整理的利器,能够轻松解决很多复杂函数才能搞定的问题.在日常的工作中,我们会遇到这样一种问题:要求以某一条数据为起始点,按 ...

  • 只需30秒, 解决Excel的图片批处理

    日常工作在,在报表里经常会插入一些图片,但是格式经常大小不一,如果不调整好,显得很不规范,如果一张张调整,又很麻烦,今天就给大家分享一个批量调整图片的方法. 如图所示,这张小鲜肉表里的照片不是很规范, ...

  • Excel数据呈现,只需30秒!

    技能在手就是自己最大的竞争力!我是每天陪伴大家的小雅. 今天继续分享透视表第二篇,来看看用透视表筛选出每个产品对应的最大和最小订单金额. 需求: 统计每种产品订单金额的最大值和最小值,进行分析. 解读 ...

  • 别再粘粘粘,合并两个Excel表格很简单!

    有两个表格:本年计划表 和 本年实际表,要求把这两个表格合并到一起,生成实际计划对比表. 多数人是先分多次插入空列,然后一列一列的粘贴,最后逐列设置公式.其实有更简单的方法. 操作步骤: 1.在本年实 ...

  • 别再粘粘粘了!100个工作薄合并,只需1秒!

    每天一点小技能 职场打怪不得怂 编按:Excel工作中,工作薄共享是一个很常用的办公技巧.当每个公司员工的填好自己的数据以后,财务或人事需要把无数张不同数据的工作薄合并在一起.最笨的方法是把一张张表格 ...

  • Excel批量合并单元格,点点鼠标,30秒完成

    [温馨提示]亲爱的朋友,阅读之前请您点击[关注],您的支持将是我最大的动力! 我们都知道,在Excel中合并单元格是个非常闹心的事,合并单元格后进行数据筛选.求和之类操作计算时,往往会出现错误.但合并 ...