【原】excel拆分合并技巧：将总表拆分成工作表的方法

编按：哈喽，大家好！在平时的工作中，我们经常会遇到将工作表拆分，或者合并的问题。大多数人还只会用复制粘贴的方式来解决，虽然操作简单，但是当遇到数据量较大的情况，无疑会拖垮我们的工作效率。其实工作表的拆分和合并没有大家想象中的那么难，本系列将分为上下两篇教程，分别讲解工作表拆分与合并的方法，本篇是上篇，将给大家带来4种工作表拆分的方法，赶紧来看看吧！

【前言】 

“小张，把采购明细表，按供应商拆分成一个一个的文件，发出去对账。”

“小张，把每个库管的采购明细，汇总成一个总明细表，发给我。”

“小张，这个月部门的工资条做了吗？做完给每个人都发一下。”

“小张，把这个表拆开，……，小张，把那些表合并，小张，小张，……，小张呢？原来你做这类活儿都是靠'复制、粘贴’啊？！去'部落窝’里找找方法吧！”

【正文】 

“总-分式套表”，好像是每个行业每个统计岗都会遇到的表格形式，十分的普遍，我们在日常工作中，也经常需要把各种表格，拆分又合并，合并再拆分，这就是“分久必合，合久必分”的千古铁律。可是如果我们只会筛选后复制、粘贴，确实效率低下，那么这篇文章就给大家带来几种拆分与合并的操作方法，希望在工作中能够帮到同学们。 

一、总表拆分为工作表——函数流

【数据源】 

下图是比较常见数据，我们现在的需求：按照总表中的供应商数据做出各个分表，把数据分别做到每个对应供应商的工作表中。 

 

步骤1：当我们使用函数拆分工作表时，需要先“手工”创建各个分表。我们先确定表中的供应商名称分别是：“永达”，“安达”，“洋子”三家公司，然后手动添加一个名为《永达》的分表： 

 

步骤2：制作供应商分表的“表头”。分表的表头可以和总表的一样，也可以不一样，具体问题具体分析，但是一定要注意，在分表中出现的字段一定是可以从总表中引用的，或者是可以通过数据计算的。 

我们制作的分表表头如下： 

 

步骤3：在总表中制作辅助列，作为分表引用的“关键词”。 

（“关键词”的作用是用于唯一地标识表中的某一条记录或某一个字段属性，具有唯一性的原则。我们在《函数技巧千千万，如何制表才关键！（上篇）》的文章说明过，不太熟悉的小伙伴可以点击链接复习一下~） 

A列函数：=I2&COUNTIF($I$2:I2,I2) 

通过COUNTIF函数和区域“混合引用”的方法，得到每个供应商，在总表中出现的顺序号，再与供应商名连接，形成一个新的引用关键字。此类引用方法在之前的教程中介绍过，小伙伴们可以点击教程《同样是countifs函数，为什么同事却使得比你好？原因在这里！》学习，此处不做赘述了。 

步骤4：在分表中制作引用数据的函数。当仁不让，我们一定会使用到常用函数VLOOKUP。 

 

《永达》分表中A6单元格函数：

=IFERROR(VLOOKUP($B$2&ROW(A1),总表!$A$1:$K$50000,MATCH(A$5,总表!$A$1:$K$1,0),0),"") 

这是一个典型的IFERROR+VLOOKUP+MATCH函数的嵌套使用： 

$B$2&ROW(A1)是供应商名称&行号，这样就和我们刚才在总表中做的辅助列字段相呼应，可以作为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 & a 是作者测试数据中的区域，大家可以改成自己的列表范围

  arr = Sheets("总表").Range("A2:J" & 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 <> "总表" 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图表述/部落窝教育（未经同意，请勿转载）