編按：

哈嘍，大家好！在平時的工作中，我們經(jīng)常會遇到將工作表拆分，或者合并的問題。大多數(shù)人還只會用復(fù)制粘貼的方式來解決，雖然操作簡單，但是當遇到數(shù)據(jù)量較大的情況，無疑會拖垮我們的工作效率。其實工作表的拆分和合并沒有大家想象中的那么難，本系列將分為上下兩篇教程，分別講解工作表拆分與合并的方法，本篇是上篇，將給大家?guī)?種工作表拆分的方法，趕緊來看看吧！

【前言】

“小張，把采購明細表，按供應(yīng)商拆分成一個一個的文件，發(fā)出去對賬?！?/span>

“小張，把每個庫管的采購明細，匯總成一個總明細表，發(fā)給我?！?/span>

“小張，這個月部門的工資條做了嗎？做完給每個人都發(fā)一下。”

“小張，把這個表拆開，……，小張，把那些表合并，小張，小張，……，小張呢？原來你做這類活兒都是靠‘復(fù)制、粘貼’??？！去‘部落窩’里找找方法吧！”

【正文】

“總-分式套表”，好像是每個行業(yè)每個統(tǒng)計崗都會遇到的表格形式，十分的普遍，我們在日常工作中，也經(jīng)常需要把各種表格，拆分又合并，合并再拆分，這就是“分久必合，合久必分”的千古鐵律。可是如果我們只會篩選后復(fù)制、粘貼，確實效率低下，那么這篇文章就給大家?guī)韼追N拆分與合并的操作方法，希望在工作中能夠幫到同學們。

一、總表拆分為工作表——函數(shù)流

【數(shù)據(jù)源】

下圖是比較常見數(shù)據(jù)，我們現(xiàn)在的需求：按照總表中的供應(yīng)商數(shù)據(jù)做出各個分表，把數(shù)據(jù)分別做到每個對應(yīng)供應(yīng)商的工作表中。

步驟1：當我們使用函數(shù)拆分工作表時，需要先“手工”創(chuàng)建各個分表。我們先確定表中的供應(yīng)商名稱分別是：“永達”，“安達”，“洋子”三家公司，然后手動添加一個名為《永達》的分表：

步驟2：制作供應(yīng)商分表的“表頭”。分表的表頭可以和總表的一樣，也可以不一樣，具體問題具體分析，但是一定要注意，在分表中出現(xiàn)的字段一定是可以從總表中引用的，或者是可以通過數(shù)據(jù)計算的。

我們制作的分表表頭如下：

步驟3：在總表中制作輔助列，作為分表引用的“關(guān)鍵詞”。

（“關(guān)鍵詞”的作用是用于唯一地標識表中的某一條記錄或某一個字段屬性，具有唯一性的原則。我們在《函數(shù)技巧千千萬，如何制表才關(guān)鍵！（上篇）》的文章說明過，不太熟悉的小伙伴可以點擊鏈接復(fù)習一下~）

A列函數(shù)：=I2&COUNTIF($I$2:I2,I2)

通過COUNTIF函數(shù)和區(qū)域“混合引用”的方法，得到每個供應(yīng)商，在總表中出現(xiàn)的順序號，再與供應(yīng)商名連接，形成一個新的引用關(guān)鍵字。此類引用方法在之前的教程中介紹過，小伙伴們可以點擊教程《同樣是countifs函數(shù)，為什么同事卻使得比你好？原因在這里！》學習，此處不做贅述了。

步驟4：在分表中制作引用數(shù)據(jù)的函數(shù)。當仁不讓，我們一定會使用到常用函數(shù)VLOOKUP。

《永達》分表中A6單元格函數(shù)：

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

這是一個典型的IFERROR+VLOOKUP+MATCH函數(shù)的嵌套使用：

$B$2&ROW(A1)是供應(yīng)商名稱&行號，這樣就和我們剛才在總表中做的輔助列字段相呼應(yīng)，可以作為VLOOKUP函數(shù)的引用標準。

用MATCH函數(shù)得到表頭字段在總表中的序列號，可以確定VLOOKUP函數(shù)引用的第幾列的數(shù)據(jù)。

最后再用IFERROR函數(shù)規(guī)避#N/A值。

同學們可以看到這里用了很多的“區(qū)域引用技巧”，這是函數(shù)應(yīng)用基礎(chǔ)的一部分，就不在這里多說了，不會的話趕緊在部落窩補補課。

輸入函數(shù)后，右拉填充，再下拉填充，一個分表的自動化拆分就做好了。

“小常識”：

這里介紹一個右拉、再下拉填充公式的快捷方式，在A6單元格輸入公式后，接著在名稱框中輸入A6:J10000，按回車鍵選中需要填充的區(qū)域，再按CTRL+D組合鍵向下填充，再按CTRL+R組合鍵向右填充，完工。

步驟5：調(diào)整單元格各個字段的格式，比如“日期”列的數(shù)據(jù)需設(shè)置成日期格式，如下：

步驟6：以此表為模板，制作其他供應(yīng)商的分表。我們可以新建一個空白工作表，再全選、復(fù)制《永達》工作表的內(nèi)容，然后粘貼到新建的空白表中，再更改工作表名稱和B2單元格的引用條件，如下：

是不是很簡單呢？而且如果總表中有了新的修改或新增內(nèi)容，分表中也會自動調(diào)整。但是這種函數(shù)的拆分方式，在數(shù)據(jù)量較多的情況，就會顯得有一些卡頓，那么我們就繼續(xù)來看看下面的拆分方法吧。

二、總表拆分為工作表——數(shù)透流

步驟1：選中數(shù)據(jù)區(qū)域A1:J25，在工具欄中選擇插入——數(shù)據(jù)透視表。

按下圖，設(shè)置數(shù)據(jù)透視表的放置位置。

步驟2：設(shè)置數(shù)據(jù)透視表的字段。我們還是按“供應(yīng)商”拆分工作表，如下設(shè)置字段的布局。

得到下面的數(shù)據(jù)透視表：

步驟3：選中數(shù)據(jù)透視表中某一個單元格，在工具欄頂端出現(xiàn)的“數(shù)據(jù)透視表工具”選項卡中，點擊其中的“分析”菜單，接著選擇下圖所示的“顯示報表篩選頁”功能。

彈出下面的窗口，點擊“確定”。

現(xiàn)在你的分表是不是已經(jīng)被拆分出來了？但它依然還是數(shù)據(jù)透視表的結(jié)構(gòu)。

找到這個數(shù)據(jù)透視表的最末行，雙擊“總計”框，就可以得到一個分表的列表。

三、總表拆分為工作表——高篩流

高級篩選功能估計很多同學都是聽說過，但沒怎么用過，那么就借今天“拆分工作表”的主題，帶著大家一起再學習一次吧。

步驟1：新建空白表，制作篩選條件區(qū)域。

步驟2：在工具欄的“數(shù)據(jù)”選項卡中，排序和篩選工作組中，點擊“高級”，彈出下面的窗口。

步驟3：按照下圖的設(shè)置內(nèi)容，分別設(shè)置高級篩選的“列表區(qū)域”、“條件區(qū)域”、“復(fù)制到”的區(qū)域。

注意：當我們點選“將篩選結(jié)果復(fù)制到其他位置”的選項后，“復(fù)制到”的輸入框才可以輸入內(nèi)容，然后點擊“確定”按鈕，得到如下圖所示的篩選數(shù)據(jù)。

重復(fù)上面的操作過程，可以制作其他供應(yīng)商的分表。另外，高級篩選可以多條件的提取數(shù)據(jù)，是比較方便的一個工具。

關(guān)于高級篩選詳細的使用方法，小伙伴們可以參考之前的教程《常用篩選的表哥表姐，知道增強版的篩選嗎？不知的，請進！》，此處不做贅述了。

【小結(jié)】

通過“函數(shù)流”、“數(shù)透流”、“高篩流”三種方式的操作，作者E圖表述還是認為函數(shù)的方式更加符合我們“一勞永逸”的建模思路，而數(shù)據(jù)透視表和高級篩選只能說應(yīng)急的時候操作比較快，但不方便形成模板，如果有了數(shù)據(jù)刪改，需要我們再次的操作。

可對于函數(shù)操作來說，如果數(shù)據(jù)量較大的時候，又會造成卡頓的情況，那么下面我們再來看一種更優(yōu)的拆分工作表的方式——VBA流。

四、總表拆分為工作表——VBA流

不廢話，先來一個效果圖：

是不是很方便？而且每次修改、刪除、增加總表記錄的時候，再次點擊按鈕就可以自動更新數(shù)據(jù)！下面我們就一起來看看操作方法吧~

步驟1：按ALT+F11組合鍵，打開VBE界面；

步驟2：在左邊工程窗口處，單擊鼠標右鍵，在彈出的菜單中選擇“插入”——“模塊”；

步驟3：雙擊新生成的模塊，在右側(cè)代碼區(qū)，輸入如下代碼：

Sub 拆分表()

  Application.ScreenUpdating = False

  Application.DisplayAlerts = False

  On Error Resume Next

  Dim arr, brr, d

’“總表”是作者測試數(shù)據(jù)的工作表名稱，如果你的總表工作表名稱是其他的，如：XXX，把代碼中所有的“總表”替換（CTRL+H）成XXX即可。

  a = Sheets("總表").[B65000].End(3).Row

’A2:J & a 是作者測試數(shù)據(jù)中的區(qū)域，大家可以改成自己的列表范圍

  arr = Sheets("總表").Range("A2:J" & a)

  Set d = CreateObject("scripting.dictionary")

  For i = 1 To UBound(arr)

’為什么是arr(i,8)呢？因為我們是按照數(shù)據(jù)范圍中的第8列內(nèi)容也就是“供應(yīng)商”列拆分總表。大家可以按照自己的需要改成某列號即可，下面的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")，是作者被粘貼區(qū)域的首個單元格，如果大家需要從其他部分粘貼，就把這里改一下。

    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的時候，經(jīng)常會忘記另存為.XLSM文件，雖然也能保存，但是保存的是工作表區(qū)域的數(shù)據(jù)，VBE界面的代碼是沒有被保存的，辛苦付之東流。

【編后語】

雖然沒有解釋代碼的含義，但卻給出了代碼的修改方式。這樣一來，會VBA的同學可以看懂；而不會VBA的同學，可以根據(jù)不同的場景，修改代碼。對于“拆分”工作表的操作，作者總結(jié)了4種方式，選擇有把握的方式來處理工作上的問題，是解決問題的最快途徑！“分久必合、合久必分”，說過了拆分，那么我們繼續(xù)關(guān)注下篇的“合并”吧?。ㄎ赐甏m(xù)）

本文配套的練習課件請加入QQ群：747953401下載。

做Excel高手，快速提升工作效率，部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你！

掃下方二維碼關(guān)注公眾號，可隨時隨地學習Excel：

相關(guān)推薦：

拆分工作表的方法《效率提高99.99%的工作表極速拆分法！》

Match函數(shù)的應(yīng)用《MATCH：函數(shù)哲學家，找巨人做伴。新出道必學！》

高級篩選《常用篩選的表哥表姐，知道增強版的篩選嗎？不知的，請進！》