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

　　编按：哈喽，大家好！在上篇文章中我们给大家介绍了4种拆分工作表的方法，分别是函数、透视表、高级筛选、VBA，不知道小伙伴们学习得咋样了？今天我们将学习合并工作表的三种方法，赶紧来看看吧！（由于合并工作表的第一种方法函数法，涉及的函数的应用相对复杂，在函数方面比较薄弱的同学，可以先看第二、三种方法，再继续学习第一种）
　　【前言】
　　在上篇文章中，对于总表拆分为分表的操作一共给大家分享了四种方法，建议同学们一定要勤加练习，才能熟能生巧。既然说了拆分，那么就没有道理不说合并。同样的，在日常工作中，合并各个分表到总表，也是经常会遇到的。
　　一、各个分表合并到总表函数流
　　既然在拆分工作表的时候，我们使用了函数的方式，那么就来再感受一下合并工作表的函数方式吧！依然使用之前的分表作为我们合并工作表的数据源。
　　步骤1：利用名称管理器把工作表名称建立为内存数组。按CTRLF3，在弹出的名称管理器窗口中，点击新建，参照下图进行设置：
　　函数：GET。WORKBOOK（1）amp；amp；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）1INT（015）1011
　　2：ROW（A2）2，INT（（ROW（A2）1）15）1INT（115）1011
　　3：ROW（A3）3，INT（（ROW（A3）1）15）1INT（215）1011
　　15：ROW（A15）15，INT（（ROW（A15）1）15）1INT（1415）1011
　　16：ROW（A16）16，INT（（ROW（A16）1）15）1INT（1515）1112
　　17：ROW（A17）17，INT（（ROW（A17）1）15）1INT（1615）1112
　　30：ROW（A30）30，INT（（ROW（A30）1）15）1INT（2915）1112
　　31：ROW（A31）31，INT（（ROW（A31）1）15）1INT（3015）1213
　　大家可以看出来，当行号减1等于我们设定的值15的时候，这个等式的值就会累加1。这个15就是我们设定的最大引用记录条的数值，算式就可以按这个数字，限定每个工作表名称的引用次数。如果我们的各分表明细中最多的记录条有6235行，那我们就设置这个值为INT（（ROW（A1）1）6300）1。
　　步骤3：删除错误值和名为汇总的数据。
　　步骤4：在B2单元格中，根据分表名称，提取分表中对应位置的数据，函数如下：
　　INDIRECT（CONCATENATE（A2，！，ADDRESS（COUNTIF（A2：A2，A2）1，COLUMN（A2））））
　　这三个函数其实都是常用函数，但是很多同学都不理解此时为什么用这三个函数来嵌套，我们来看看下图，估计会对同学们有所帮助。
　　B2单元格返回《永达》表中的A2单元格，如果直接引用的话，我们可以使用表达式永达！A2。但是如果我们要动态的引用这个工作表的其他单元格地址，就需要使用上述函数过程：
　　第一阶段得到地址名：
　　ADDRESS（COUNTIF（A2：A2，A2）1，COLUMN（A2）），利用工作表名称出现的次数，使用COUNTIF函数得到｛1，2，3，，15｝的行号，再加1，就能得到对应目标工作表的引用行号，再使用COLUMN函数得到对应的列号。最后通过ADDRESS函数，返回行号列号确定的单元格地址。
　　第二阶段确定此单元格地址，属于哪个工作表：
　　CONCATENATE（A2，！，ADDRESS（COUNTIF（A2：A2，A2）1，COLUMN（A2））），CONCATENATE函数是一个连接文本的函数，A2单元格是工作表名称永达，所以此函数运行后，就得到了永达！A2的字符串。
　　第三阶段使字符串形成引用地址：
　　INDIRECT函数引用连接，并返回引用地址值的函数。用INDIRECT函数引用刚才的永达！A2字符串，得到对应的单元格内容。
　　最后，将此函数向左填充、再向下填充，就可以得到我们各个分表的明细汇总了。
　　步骤5：因为我们使用了宏表函数，所以保存时，我们要另存为。XLSM格式的文件。
　　【小结】
　　从上图中我们不难看出，如果对应的分表数据没有15行的时候，那么就会显示0；而如果对应的分表数据超出设定的15行，那么数据就会引出不全。
　　所以我们既要考虑最大行数的设定，做出汇总表后，还要筛选出为0的行进行删除，这样就会给我们后续的工作增加很多操作的步骤。
　　二、各个分表合并到总表PQ流
　　PQ是什么？PQ是POWERQUERY的缩写（以下简称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这个软件。
　　按ALTF11组合键打开VBE界面，新建模块1，在代码区域输入下面的代码，操作一波看看吧。
　　Sub合并工作表（）
　　Sheets（汇总）。Range（A2：J65000）。ClearContents
　　ForEachshInWorksheets
　　Ifsh。Nameamp；lt；amp；gt；汇总Then
　　aSheets（汇总）。Range（A65000）。End（3）。Row1
　　bsh。Range（A65000）。End（3）。Row
　　sh。Range（A2：Jamp；amp；b）。CopySheets（汇总）。Cells（a，1）
　　EndIf
　　Next
　　EndSub
　　代码中的Range是单元格区域对象的书写方式，括号中的J代表数据区域的末列，同学们如果操作自己的表格的时候，可以改成自己数据的末列列标，并把代码中的汇总改为自己汇总表的名称，即可。
　　【编后语】
　　同工作薄的拆分工作表和合并工作表的方法给大家列举了很多很多，在实际工作中，无论你掌握了哪种方法都可以让你有的放矢的去做，最怕的就是你没有一个方法傍身，那就真的无从下手了。
　　部落窝教育excel表格合并技巧
　　原创：E图表述部落窝教育（未经同意，请勿转载）