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

部落窩教育BLW 2019-11-15

展開全文

編按：哈嘍，大家好！在上篇文章中我們給大家介紹了4種拆分工作表的方法，分別是函數(shù)、透視表、高級篩選、VBA，不知道小伙伴們學習得咋樣了？今天我們將學習合并工作表的三種方法，趕緊來看看吧?。ㄓ捎诤喜⒐ぷ鞅淼牡谝环N方法函數(shù)法，涉及的函數(shù)的應用相對復雜，在函數(shù)方面比較薄弱的同學，可以先看第二、三種方法，再繼續(xù)學習第一種~）

＊＊＊＊＊＊＊＊＊

【前言】

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

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

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

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

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

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

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

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

【函數(shù)解析】

這個嵌套函數(shù)的解析，要從上面提到的“坑”開始說起，GET.WORKBOOK（1）提取后的返回值是：[工作薄名稱]工作表名稱，這樣的格式。如果我們只需要提取工作表名稱，就要使用文本處理函數(shù)MID來提?。ó斎灰部梢杂肦IGHT函數(shù)，大家可以自己試一下），通過FIND函數(shù)找到“]”的起始位置再加1，就是工作表名稱的起始位置，用99作為MID函數(shù)的第三參數(shù)，來確定提取的字符串長度（如果提取長度超出實際長度，默認提取實際長度）。

這部分的內容，我們在制作excel中的目錄的文章中，詳細講解過，具體可以點擊鏈接《用GET.WORKBOOK函數(shù)實現(xiàn)excel批量生成帶超鏈接目錄且自動更新》，進行學習。

那么這個函數(shù)最難理解的部分來了：

INDEX(SHNAME,INT((ROW(A1)-1)/15)+1)

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

因為每個分表中的明細數(shù)據(jù)都不可能是只有一條記錄條，所以我們對于SHNAME中的工作表名稱也不應該只提取一次，因此使用INT((ROW(A1)-1)/15)+1來確定我們引出分表名稱的次數(shù)。

對于這個函數(shù)的理解，需要空間感和數(shù)學思維相結合：

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

…

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

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

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

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

這三個函數(shù)其實都是常用函數(shù)，但是很多同學都不理解此時為什么用這三個函數(shù)來嵌套，我們來看看下圖，估計會對同學們有所幫助。

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

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

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

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

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

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

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

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

步驟5：因為我們使用了宏表函數(shù)，所以保存時，我們要另存為.XLSM格式的文件。

【小結】

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

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

二、各個分表合并到總表——PQ流

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

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