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圖表述/部落窩教育（未經同意，請勿轉載）