編按：

哈嘍，大家好！在上篇文章中我們給大家介紹了4種拆分工作表的方法，分別是函數(shù)、透視表、高級(jí)篩選、VBA，不知道小伙伴們學(xué)習(xí)得咋樣了？今天我們將學(xué)習(xí)合并工作表的三種方法，趕緊來(lái)看看吧！（由于合并工作表的第一種方法函數(shù)法，涉及的函數(shù)的應(yīng)用相對(duì)復(fù)雜，在函數(shù)方面比較薄弱的同學(xué)，可以先看第二、三種方法，再繼續(xù)學(xué)習(xí)第一種~）

【前言】

在上篇文章中，對(duì)于總表拆分為分表的操作一共給大家分享了四種方法，建議同學(xué)們一定要勤加練習(xí)，才能熟能生巧。既然說(shuō)了拆分，那么就沒(méi)有道理不說(shuō)“合并”。同樣的，在日常工作中，合并各個(gè)分表到總表，也是經(jīng)常會(huì)遇到的。

一、各個(gè)分表合并到總表——函數(shù)流

既然在“拆分”工作表的時(shí)候，我們使用了函數(shù)的方式，那么就來(lái)再感受一下“合并”工作表的函數(shù)方式吧！依然使用之前的分表作為我們合并工作表的數(shù)據(jù)源。

步驟1：利用名稱管理器把工作表名稱建立為內(nèi)存數(shù)組。按CTRL+F3，在彈出的“名稱管理器”窗口中，點(diǎn)擊“新建”，參照下圖進(jìn)行設(shè)置：

函數(shù)：=GET.WORKBOOK(1)&T(NOW())

利用宏表函數(shù)“GET.WORKBOOK(1)”，得到工作薄內(nèi)所有工作表名稱，并形成一個(gè)內(nèi)存數(shù)組，把這個(gè)內(nèi)存數(shù)組命名為“SHNAME”。T(NOW())是利用NOW函數(shù)的易失性，可以使宏表函數(shù)“GET.WORKBOOK(1)”自動(dòng)更新。因?yàn)?span>NOW函數(shù)返回的是時(shí)間格式的數(shù)值，T函數(shù)可以將數(shù)值轉(zhuǎn)換為空，而時(shí)間日期是特殊的數(shù)值，所以T(NOW())的結(jié)構(gòu)將返回空文本“”，這樣返回值的內(nèi)容就是工作表名稱了（注意這里有坑，下面填坑）。

步驟2：新建一個(gè)空白工作表，命名為“匯總”，在《匯總》工作表的A1單元格中輸入“工作表”，在B1:K1區(qū)域復(fù)制粘貼分表的表頭字段，在A2單元格輸入函數(shù)：

=MID(INDEX(SHNAME,INT((ROW(A1)-1)/15)+1),FIND("]",INDEX(SHNAME,INT((ROW(A1)-1)/15)+1))+1,99)。如下圖：

【函數(shù)解析】

這個(gè)嵌套函數(shù)的解析，要從上面提到的“坑”開(kāi)始說(shuō)起，GET.WORKBOOK（1）提取后的返回值是：[工作薄名稱]工作表名稱，這樣的格式。如果我們只需要提取工作表名稱，就要使用文本處理函數(shù)MID來(lái)提?。ó?dāng)然也可以用RIGHT函數(shù)，大家可以自己試一下），通過(guò)FIND函數(shù)找到“]”的起始位置再加1，就是工作表名稱的起始位置，用99作為MID函數(shù)的第三參數(shù)，來(lái)確定提取的字符串長(zhǎng)度（如果提取長(zhǎng)度超出實(shí)際長(zhǎng)度，默認(rèn)提取實(shí)際長(zhǎng)度）。

這部分的內(nèi)容，我們?cè)谥谱?span>excel中的目錄的文章中，詳細(xì)講解過(guò)，具體可以點(diǎn)擊鏈接《用GET.WORKBOOK函數(shù)實(shí)現(xiàn)excel批量生成帶超鏈接目錄且自動(dòng)更新》，進(jìn)行學(xué)習(xí)。

那么這個(gè)函數(shù)最難理解的部分來(lái)了：INDEX(SHNAME,INT((ROW(A1)-1)/15)+1)

SHNAME是什么？是我們剛才在名稱管理器中設(shè)置的自定義名稱。在名稱管理器中使用了提取工作表名稱的宏表函數(shù)后，那么就形成了一個(gè)內(nèi)存數(shù)組，數(shù)組的內(nèi)容是{分表1；分表2；分表3；匯總}四個(gè)內(nèi)容，再用INDEX函數(shù)分別提取某個(gè)位置的內(nèi)容（即提取出工作表名稱）。

因?yàn)槊總€(gè)分表中的明細(xì)數(shù)據(jù)都不可能是只有一條記錄條，所以我們對(duì)于SHNAME中的工作表名稱也不應(yīng)該只提取一次，因此使用INT((ROW(A1)-1)/15)+1來(lái)確定我們引出分表名稱的次數(shù)。

對(duì)于這個(gè)函數(shù)的理解，需要空間感和數(shù)學(xué)思維相結(jié)合：

1：ROW(A1)=1, INT((ROW(A1)-1)/15)+1 = INT(0/15)+1 = 0+1 =1

2：ROW(A2)=2, INT((ROW(A2)-1)/15)+1 = INT(1/15)+1 = 0+1 =1

3：ROW(A3)=3, INT((ROW(A3)-1)/15)+1 = INT(2/15)+1 = 0+1 =1

…

15：ROW(A15)=15, INT((ROW(A15)-1)/15)+1 = INT(14/15)+1 = 0+1 =1

16：ROW(A16)=16, INT((ROW(A16)-1)/15)+1 = INT(15/15)+1 = 1+1 =2

17：ROW(A17)=17, INT((ROW(A17)-1)/15)+1 = INT(16/15)+1 = 1+1 =2

…

30：ROW(A30)=30, INT((ROW(A30)-1)/15)+1 = INT(29/15)+1 = 1+1 =2

31：ROW(A31)=31, INT((ROW(A31)-1)/15)+1 = INT(30/15)+1 = 2+1 =3

…

大家可以看出來(lái)，當(dāng)行號(hào)減1等于我們?cè)O(shè)定的值“15”的時(shí)候，這個(gè)等式的值就會(huì)累加1 。這個(gè)15就是我們?cè)O(shè)定的最大引用記錄條的數(shù)值，算式就可以按這個(gè)數(shù)字，限定每個(gè)工作表名稱的引用次數(shù)。如果我們的各分表明細(xì)中最多的記錄條有6235行，那我們就設(shè)置這個(gè)值為INT((ROW(A1)-1)/6300)+1。

步驟3：刪除錯(cuò)誤值和名為“匯總”的數(shù)據(jù)。

步驟4：在B2單元格中，根據(jù)分表名稱，提取分表中對(duì)應(yīng)位置的數(shù)據(jù)，函數(shù)如下：

=INDIRECT(CONCATENATE($A2,"!",ADDRESS(COUNTIF($A$2:$A2,$A2)+1,COLUMN(A2))))

這三個(gè)函數(shù)其實(shí)都是常用函數(shù)，但是很多同學(xué)都不理解此時(shí)為什么用這三個(gè)函數(shù)來(lái)嵌套，我們來(lái)看看下圖，估計(jì)會(huì)對(duì)同學(xué)們有所幫助。

B2單元格返回“《永達(dá)》表中的A2單元格”，如果直接引用的話，我們可以使用表達(dá)式“=永達(dá)!A2”。但是如果我們要?jiǎng)討B(tài)的引用這個(gè)工作表的其他單元格地址，就需要使用上述函數(shù)過(guò)程：

第一階段——得到地址名：

ADDRESS(COUNTIF($A$2:$A2,$A2)+1,COLUMN(A2))，利用工作表名稱出現(xiàn)的次數(shù)，使用COUNTIF函數(shù)得到{1,2,3,…,15}的行號(hào)，再加1，就能得到對(duì)應(yīng)目標(biāo)工作表的引用行號(hào)，再使用COLUMN函數(shù)得到對(duì)應(yīng)的列號(hào)。最后通過(guò)ADDRESS函數(shù)，返回行號(hào)列號(hào)確定的單元格地址；

第二階段——確定此單元格地址，屬于哪個(gè)工作表：

CONCATENATE($A2,"!",ADDRESS(COUNTIF($A$2:$A2,$A2)+1,COLUMN(A2)))，CONCATENATE函數(shù)是一個(gè)連接文本的函數(shù)，A2單元格是工作表名稱“永達(dá)”，所以此函數(shù)運(yùn)行后，就得到了“永達(dá)!A2”的字符串；

第三階段——使字符串形成引用地址：

INDIRECT函數(shù)引用連接，并返回引用地址值的函數(shù)。用INDIRECT函數(shù)引用剛才的“永達(dá)!A2”字符串，得到對(duì)應(yīng)的單元格內(nèi)容。

最后，將此函數(shù)向左填充、再向下填充，就可以得到我們各個(gè)分表的明細(xì)匯總了。

步驟5：因?yàn)槲覀兪褂昧撕瓯砗瘮?shù)，所以保存時(shí)，我們要另存為.XLSM格式的文件。

【小結(jié)】

從上圖中我們不難看出，如果對(duì)應(yīng)的分表數(shù)據(jù)沒(méi)有15行的時(shí)候，那么就會(huì)顯示0；而如果對(duì)應(yīng)的分表數(shù)據(jù)超出設(shè)定的15行，那么數(shù)據(jù)就會(huì)引出不全。

所以我們既要考慮最大行數(shù)的設(shè)定，做出匯總表后，還要篩選出為0的行進(jìn)行刪除，這樣就會(huì)給我們后續(xù)的工作增加很多操作的步驟。

二、各個(gè)分表合并到總表——PQ流

PQ是什么？PQ是POWER QUERY的縮寫(xiě)（以下簡(jiǎn)稱PQ），是EXCEL中一個(gè)查詢模塊，對(duì)于不是像作者這樣需要寫(xiě)文章、寫(xiě)教程的同學(xué)來(lái)說(shuō)，咱們不用知道它的各種解釋，只要知道它的功能即可。就像工作表函數(shù)，我們知道IF、SUM如何使用就行，沒(méi)有必要知道這些內(nèi)置函數(shù)存儲(chǔ)在EXCEL的什么位置。

在EXCEL2016中PQ是自帶的，如下圖：

EXCEL2010版之前的版本是沒(méi)有PQ的，EXCEL2010和EXCEL2013版需要下載PQ插件。

步驟1：啟動(dòng)PQ編輯器，如下圖：

步驟2：在“主頁(yè)”選項(xiàng)卡中，點(diǎn)選“新建源”——“文件”——“EXCEL”，在彈出的“導(dǎo)入數(shù)據(jù)”窗口中，按路徑找到需要合并分表的源文件，點(diǎn)擊“導(dǎo)入”按鈕，彈出“導(dǎo)航器”窗口；

步驟3：在“導(dǎo)航器”窗口中，勾選“選擇多項(xiàng)”，然后復(fù)選所有分表，再點(diǎn)擊“確定”按鈕將數(shù)據(jù)導(dǎo)入到PQ中，如下圖；

步驟4：在“主頁(yè)”選項(xiàng)卡下的“組合”下拉菜單中，點(diǎn)擊“追加查詢”功能按鍵，彈出“追加”窗口。

將所有非當(dāng)前的工作表，逐一全部追加到右面的列表框中，點(diǎn)擊“確定”按鈕，此時(shí)就將其他的工作表都追加到了當(dāng)前的工作表中。

步驟5：點(diǎn)擊“主頁(yè)”中左上角的“關(guān)閉并上載”按鍵，將新建查詢導(dǎo)入此EXCEL工作薄中，如下圖：

步驟6：保留下匯總的工作表，刪除其他的工作表，任務(wù)就完成了。

【小結(jié)】

沒(méi)有復(fù)雜的函數(shù)，所有的操作只需要鼠標(biāo)點(diǎn)擊即可完成，是不是很方便，那么以后如果有再多的工作表合并的問(wèn)題，都是分分鐘搞定了吧。

三、各個(gè)分表合并到總表——VBA流

“沒(méi)有完美的方法，只有完美的操作體系”。EXCEL帶給我們的好像就是這么一個(gè)完美的操作體系，永遠(yuǎn)都是“一題多解”，如果當(dāng)你既不想費(fèi)勁去寫(xiě)函數(shù)，還想一勞永逸的合并工作表，那就采用VBA的方式來(lái)處理。

有很多同學(xué)都是“談VBA色變”，但是作者E圖表述要說(shuō)，當(dāng)你學(xué)了VBA才會(huì)真正的掌握EXCEL這個(gè)軟件。

按ALT+F11組合鍵打開(kāi)VBE界面，新建“模塊1”，在代碼區(qū)域輸入下面的代碼，操作一波看看吧。

Sub 合并工作表()

  Sheets("匯總").Range("A2:J65000").ClearContents

  For Each sh In Worksheets

    If sh.Name <> "匯總" Then

      a = Sheets("匯總").Range("A65000").End(3).Row + 1

      b = sh.Range("A65000").End(3).Row

      sh.Range("A2:J" & b).Copy Sheets("匯總").Cells(a, 1)

    End If

  Next

End Sub

代碼中的Range是單元格區(qū)域?qū)ο蟮臅?shū)寫(xiě)方式，括號(hào)中的J代表數(shù)據(jù)區(qū)域的末列，同學(xué)們?nèi)绻僮髯约旱谋砀竦臅r(shí)候，可以改成自己數(shù)據(jù)的末列列標(biāo)，并把代碼中的“匯總”改為自己匯總表的名稱，即可。

【編后語(yǔ)】

同工作薄的“拆分工作表”和“合并工作表”的方法給大家列舉了很多很多，在實(shí)際工作中，無(wú)論你掌握了哪種方法都可以讓你有的放矢的去做，最怕的就是你沒(méi)有一個(gè)方法傍身，那就真的無(wú)從下手了。

本文配套的練習(xí)課件請(qǐng)加入QQ群：747953401下載。

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

掃下方二維碼關(guān)注公眾號(hào)，可隨時(shí)隨地學(xué)習(xí)Excel：

相關(guān)推薦：

拆分與合并（拆分篇）《別再為拆分、合并工作表鬧心啦！最實(shí)用的7種方法，分分鐘搞定它?。ú鸱制?/span>》

PQ合并數(shù)據(jù)的詳細(xì)案例《Excel一鍵生成報(bào)表教程：powerquery多表合并案例》

INDIRECT函數(shù)《大神專用求和公式！帶你揭秘自動(dòng)統(tǒng)計(jì)前幾名數(shù)據(jù)合計(jì)的新套路?。ㄏ缕?/span>》