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

　　编按：哈喽，大家好！在平时的工作中，我们经常会遇到将工作表拆分，或者合并的问题。大多数人还只会用复制粘贴的方式来解决，虽然操作简单，但是当遇到数据量较大的情况，无疑会拖垮我们的工作效率。其实工作表的拆分和合并没有大家想象中的那么难，本系列将分为上下两篇教程，分别讲解工作表拆分与合并的方法，本篇是上篇，将给大家带来4种工作表拆分的方法，赶紧来看看吧！
　　【前言】
　　＂小张，把采购明细表，按供应商拆分成一个一个的文件，发出去对账。＂
　　＂小张，把每个库管的采购明细，汇总成一个总明细表，发给我。＂
　　＂小张，这个月部门的工资条做了吗？做完给每个人都发一下。＂
　　＂小张，把这个表拆开，……，小张，把那些表合并，小张，小张，……，小张呢？原来你做这类活儿都是靠＂复制、粘贴’啊？！去＂部落窝’里找找方法吧！＂
　　【正文】
　　＂总-分式套表＂，好像是每个行业每个统计岗都会遇到的表格形式，十分的普遍，我们在日常工作中，也经常需要把各种表格，拆分又合并，合并再拆分，这就是＂分久必合，合久必分＂的千古铁律。可是如果我们只会筛选后复制、粘贴，确实效率低下，那么这篇文章就给大家带来几种拆分与合并的操作方法，希望在工作中能够帮到同学们。
　　一、总表拆分为工作表——函数流
　　【数据源】
　　下图是比较常见数据，我们现在的需求：按照总表中的供应商数据做出各个分表，把数据分别做到每个对应供应商的工作表中。
　　步骤1：当我们使用函数拆分工作表时，需要先＂手工＂创建各个分表。我们先确定表中的供应商名称分别是：＂永达＂，＂安达＂，＂洋子＂三家公司，然后手动添加一个名为《永达》的分表：
　　步骤2：制作供应商分表的＂表头＂。分表的表头可以和总表的一样，也可以不一样，具体问题具体分析，但是一定要注意，在分表中出现的字段一定是可以从总表中引用的，或者是可以通过数据计算的。
　　我们制作的分表表头如下：
　　步骤3：在总表中制作辅助列，作为分表引用的＂关键词＂。
　　（＂关键词＂的作用是用于唯一地标识表中的某一条记录或某一个字段属性，具有唯一性的原则。我们在《函数技巧千千万，如何制表才关键！（上篇）》的文章说明过，不太熟悉的小伙伴可以点击链接复习一下~）
　　A列函数：=I2&amp;COUNTIF($I$2:I2,I2)
　　通过COUNTIF函数和区域＂混合引用＂的方法，得到每个供应商，在总表中出现的顺序号，再与供应商名连接，形成一个新的引用关键字。此类引用方法在之前的教程中介绍过，小伙伴们可以点击教程《同样是countifs函数，为什么同事却使得比你好？原因在这里！》学习，此处不做赘述了。
　　步骤4：在分表中制作引用数据的函数。当仁不让，我们一定会使用到常用函数VLOOKUP。
　　《永达》分表中A6单元格函数：
　　=IFERROR(VLOOKUP($B$2&amp;ROW(A1),总表!$A$1:$K$50000,MATCH(A$5,总表!$A$1:$K$1,0),0),＂＂)
　　这是一个典型的IFERROR+VLOOKUP+MATCH函数的嵌套使用：
　　$B$2&amp;ROW(A1)是供应商名称&amp;行号，这样就和我们刚才在总表中做的辅助列字段相呼应，可以作为VLOOKUP函数的引用标准。
　　用MATCH函数得到表头字段在总表中的序列号，可以确定VLOOKUP函数引用的第几列的数据。
　　最后再用IFERROR函数规避#N/A值。
　　同学们可以看到这里用了很多的＂区域引用技巧＂，这是函数应用基础的一部分，就不在这里多说了，不会的话赶紧在部落窝补补课。
　　输入函数后，右拉填充，再下拉填充，一个分表的自动化拆分就做好了。
　　＂小常识＂：
　　这里介绍一个右拉、再下拉填充公式的快捷方式，在A6单元格输入公式后，接着在名称框中输入A6:J10000，按回车键选中需要填充的区域，再按CTRL+D组合键向下填充，再按CTRL+R组合键向右填充，完工。
　　步骤5：调整单元格各个字段的格式，比如＂日期＂列的数据需设置成日期格式，如下：
　　步骤6：以此表为模板，制作其他供应商的分表。我们可以新建一个空白工作表，再全选、复制《永达》工作表的内容，然后粘贴到新建的空白表中，再更改工作表名称和B2单元格的引用条件，如下：
　　是不是很简单呢？而且如果总表中有了新的修改或新增内容，分表中也会自动调整。但是这种函数的拆分方式，在数据量较多的情况，就会显得有一些卡顿，那么我们就继续来看看下面的拆分方法吧。
　　二、总表拆分为工作表——数透流
　　步骤1：选中数据区域A1:J25，在工具栏中选择插入——数据透视表。
　　按下图，设置数据透视表的放置位置。
　　步骤2：设置数据透视表的字段。我们还是按＂供应商＂拆分工作表，如下设置字段的布局。
　　得到下面的数据透视表：
　　步骤3：选中数据透视表中某一个单元格，在工具栏顶端出现的＂数据透视表工具＂选项卡中，点击其中的＂分析＂菜单，接着选择下图所示的＂显示报表筛选页＂功能。
　　弹出下面的窗口，点击＂确定＂。
　　现在你的分表是不是已经被拆分出来了？但它依然还是数据透视表的结构。
　　找到这个数据透视表的最末行，双击＂总计＂框，就可以得到一个分表的列表。
　　三、总表拆分为工作表——高筛流
　　高级筛选功能估计很多同学都是听说过，但没怎么用过，那么就借今天＂拆分工作表＂的主题，带着大家一起再学习一次吧。
　　步骤1：新建空白表，制作筛选条件区域。
　　步骤2：在工具栏的＂数据＂选项卡中，排序和筛选工作组中，点击＂高级＂，弹出下面的窗口。
　　步骤3：按照下图的设置内容，分别设置高级筛选的＂列表区域＂、＂条件区域＂、＂复制到＂的区域。
　　注意：当我们点选＂将筛选结果复制到其他位置＂的选项后，＂复制到＂的输入框才可以输入内容，然后点击＂确定＂按钮，得到如下图所示的筛选数据。
　　重复上面的操作过程，可以制作其他供应商的分表。另外，高级筛选可以多条件的提取数据，是比较方便的一个工具。
　　关于高级筛选详细的使用方法，小伙伴们可以参考之前的教程《常用筛选的表哥表姐，知道增强版的筛选吗？不知的，请进！》，此处不做赘述了。
　　【小结】
　　通过＂函数流＂、＂数透流＂、＂高筛流＂三种方式的操作，作者E图表述还是认为函数的方式更加符合我们＂一劳永逸＂的建模思路，而数据透视表和高级筛选只能说应急的时候操作比较快，但不方便形成模板，如果有了数据删改，需要我们再次的操作。
　　可对于函数操作来说，如果数据量较大的时候，又会造成卡顿的情况，那么下面我们再来看一种更优的拆分工作表的方式——VBA流。
　　四、总表拆分为工作表——VBA流
　　不废话，先来一个效果图：
　　是不是很方便？而且每次修改、删除、增加总表记录的时候，再次点击按钮就可以自动更新数据！下面我们就一起来看看操作方法吧~
　　步骤1：按ALT+F11组合键，打开VBE界面；
　　步骤2：在左边工程窗口处，单击鼠标右键，在弹出的菜单中选择＂插入＂——＂模块＂；
　　步骤3：双击新生成的模块，在右侧代码区，输入如下代码：
　　Sub拆分表()
　　Application.ScreenUpdating = False
　　Application.DisplayAlerts = False
　　On Error Resume Next
　　Dim arr, brr, d
　　’＂总表＂是作者测试数据的工作表名称，如果你的总表工作表名称是其他的，如：XXX，把代码中所有的＂总表＂替换（CTRL+H）成XXX即可。
　　a = Sheets(＂总表＂).[B65000].End(3).Row
　　’A2:J &amp; a是作者测试数据中的区域，大家可以改成自己的列表范围
　　arr = Sheets(＂总表＂).Range(＂A2:J＂ &amp; a)
　　Set d = CreateObject(＂scripting.dictionary＂)
　　For i = 1 To UBound(arr)
　　’为什么是arr(i,8)呢？因为我们是按照数据范围中的第8列内容也就是＂供应商＂列拆分总表。大家可以按照自己的需要改成某列号即可，下面的arr(i,8)都是这样的修改方式。
　　d(arr(i, 8)) = d(arr(i, 8)) + ＂＂
　　Next i
　　x = Sheets.Count
　　For j = x To 1 Step -1
　　If Sheets(j).Name &lt;&gt; ＂总表＂ Then
　　Sheets(j).Delete
　　End If
　　Next j
　　x = Sheets.Count
　　For Each dic In d
　　ReDim brr(1 To UBound(arr), 1 To UBound(arr, 2))
　　Sheets.Add after:=Sheets(x)
　　x = x + 1
　　Sheets(x).Name = dic
　　For i = 1 To UBound(arr)
　　If arr(i, 8) = dic Then
　　k = k + 1
　　For j = 1 To UBound(arr, 2)
　　brr(k, j) = arr(i, j)
　　Next j
　　End If
　　Next i
　　Sheets(＂总表＂).Range(＂1:1＂).Copy Sheets(x).Range(＂1:1＂)
　　’Range(＂A2＂)，是作者被粘贴区域的首个单元格，如果大家需要从其他部分粘贴，就把这里改一下。
　　Sheets(x).Range(＂A2＂).Resize(UBound(brr), UBound(brr, 2)) = brr
　　Erase brr
　　k = 0
　　Next
　　End Sub
　　步骤4：运行代码，测试代码是否运行正常。
　　步骤5：如果测试代码无误，将.XLSX文件另存为.XLSM文件（启用宏的EXCEL工作薄）。作者E图表述的很多学生在初学VBA的时候，经常会忘记另存为.XLSM文件，虽然也能保存，但是保存的是工作表区域的数据，VBE界面的代码是没有被保存的，辛苦付之东流。
　　【编后语】
　　虽然没有解释代码的含义，但却给出了代码的修改方式。这样一来，会VBA的同学可以看懂；而不会VBA的同学，可以根据不同的场景，修改代码。对于＂拆分＂工作表的操作，作者总结了4种方式，选择有把握的方式来处理工作上的问题，是解决问题的最快途径！＂分久必合、合久必分＂，说过了拆分，那么我们继续关注下篇的＂合并＂吧！（未完待续）
　　＊＊＊＊部落窝教育－excel表格拆分技巧＊＊＊
　　原创：E图表述/部落窝教育（未经同意，请勿转载）
网站目录投稿：寄蕾