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

?

【前言】?

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

“小張，把每個(gè)庫(kù)管的采購(gòu)明細(xì)，匯總成一個(gè)總明細(xì)表，發(fā)給我?！?/p>

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

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

?

【正文】?

“總-分式套表”，好像是每個(gè)行業(yè)每個(gè)統(tǒng)計(jì)崗都會(huì)遇到的表格形式，十分的普遍，我們?cè)谌粘９ぷ髦?，也?jīng)常需要把各種表格，拆分又合并，合并再拆分，這就是“分久必合，合久必分”的千古鐵律?？墒侨绻覀冎粫?huì)篩選后復(fù)制、粘貼，確實(shí)效率低下，那么這篇文章就給大家?guī)?lái)幾種拆分與合并的操作方法，希望在工作中能夠幫到同學(xué)們。?

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

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

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

?

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

?

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

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

?

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

（“關(guān)鍵詞”的作用是用于唯一地標(biāo)識(shí)表中的某一條記錄或某一個(gè)字段屬性，具有唯一性的原則。我們?cè)凇?/span>函數(shù)技巧千千萬(wàn)，如何制表才關(guān)鍵?。ㄉ掀?/span>》的文章說(shuō)明過(guò)，不太熟悉的小伙伴可以點(diǎn)擊鏈接復(fù)習(xí)一下~）?

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

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

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

?

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

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

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

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

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

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

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

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

?

“小常識(shí)”：

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

?

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

?

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

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

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

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

?

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

?

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

?

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

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

?

彈出下面的窗口，點(diǎn)擊“確定”。?

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

?

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

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

高級(jí)篩選功能估計(jì)很多同學(xué)都是聽(tīng)說(shuō)過(guò)，但沒(méi)怎么用過(guò)，那么就借今天“拆分工作表”的主題，帶著大家一起再學(xué)習(xí)一次吧。?

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

?

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

?

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

?

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

?

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

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

?

【小結(jié)】

通過(guò)“函數(shù)流”、“數(shù)透流”、“高篩流”三種方式的操作，作者E圖表述還是認(rèn)為函數(shù)的方式更加符合我們“一勞永逸”的建模思路，而數(shù)據(jù)透視表和高級(jí)篩選只能說(shuō)應(yīng)急的時(shí)候操作比較快，但不方便形成模板，如果有了數(shù)據(jù)刪改，需要我們?cè)俅蔚牟僮鳌?/p>

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

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

不廢話，先來(lái)一個(gè)效果圖：?

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

步驟1：按ALT+F11組合鍵，打開(kāi)VBE界面；

?

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

?

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

Sub?拆分表()

? Application.ScreenUpdating = False

? Application.DisplayAlerts = False

? On Error Resume Next

?

? Dim arr, brr, d

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

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

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

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

? Set d = CreateObject("scripting.dictionary")

?

? For i = 1 To UBound(arr)

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

??? Sheets(x).Range("A2").Resize(UBound(brr), UBound(brr, 2)) = brr

??? Erase brr

??? k = 0

? Next

End Sub

?

步驟4：運(yùn)行代碼，測(cè)試代碼是否運(yùn)行正常。?

?

步驟5：如果測(cè)試代碼無(wú)誤，將.XLSX文件另存為.XLSM文件（啟用宏的EXCEL工作?。?。作者E圖表述的很多學(xué)生在初學(xué)VBA的時(shí)候，經(jīng)常會(huì)忘記另存為.XLSM文件，雖然也能保存，但是保存的是工作表區(qū)域的數(shù)據(jù)，VBE界面的代碼是沒(méi)有被保存的，辛苦付之東流。?

?

【編后語(yǔ)】?

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

＊＊＊＊部落窩教育－excel表格拆分技巧＊＊＊

原創(chuàng)：E圖表述/部落窩教育（未經(jīng)同意，請(qǐng)勿轉(zhuǎn)載）

更多教程：部落窩教育（www.itblw.com）

微信公眾號(hào)：exceljiaocheng