1. 問題背景
在日常工作中我們經(jīng)常遇到具有相同表頭的 Excel 文件，需要將它們合并到同一個工作表中再進行分析。當文件比較多時，手工合并表格通常是件很麻煩的事情，而如果數(shù)據(jù)量很大，用 Excel 自帶的 VBA 來處理也會經(jīng)?？ㄋ?。今天我就來分享一個專業(yè)的外部數(shù)據(jù)工具——集算器，掌握了集算器處理 Excel 多表合并的方法，就不用再編寫復雜且低效的 VBA 代碼了，簡單的幾行 SPL(Structured Process Language，結(jié)構(gòu)化過程處理語言)代碼就能輕松搞定 Excel 文件合并，即使文件再多、再大也不用擔心。
2. 基本合并
A. 同一個 excel 中的多表合并
下面的例子是一個包含了銷售數(shù)據(jù)的 excel 文件，其中包含了按月劃分的 3 個結(jié)構(gòu)相同的 sheet 工作表，數(shù)據(jù)如下：
集算器SPL腳本：
腳本說明：
A1：打開指定的 excel 文件，創(chuàng)建一個由多個 sheet 工作表組成的序列。
A2：利用 conj 函數(shù)遍歷 A1 序列中所有的成員工作表，導入每個工作表中指定列'Customer Name','Sale Amount'，并將數(shù)據(jù)并合并。其中 xlsimport 函數(shù)導入指定列，最后一列用分號; 隔開。 參數(shù)~.stname表示指定當前工作表，由于在 conj 函數(shù)的循環(huán)中，所以就可以逐個導入所有工作表。同時，xlsimport 使用選項@t指明將工作表的第一行記錄作為字段名。
A3：將序表 A2 作為一個新的工作表“merge_sheets”保存到原來的 excel 文件中，同樣用選項 @t 指明首行記錄為標題。
這段腳本只有三句話，短小精干之余，邏輯清晰，也比較容易理解。下面我們再看看如何合并多個文件中的多個工作表。
B. 不同 excel 中的多表合并
下面是要合并的多個 excel 文件，它們都具有和上面例子相同的表結(jié)構(gòu)，每個文件記錄了當年的數(shù)據(jù)?：
腳本說明：
A1：通過 for 循環(huán)，遍歷指定目錄下的 excel 文件，在 B1 到 B3 之間進行循環(huán)內(nèi)處理.
B1：打開目錄下的一個 excel 文件，生成序列。
B2：導入當前文件中的每個 sheet 工作表中指定列'Customer Name','Sale Amount'，'Purchase Date'的數(shù)據(jù)，然后合并這些數(shù)據(jù)，與前面例子中的 A2 類似。
B3：將序表 B2 的數(shù)據(jù)與 @表示的本網(wǎng)格的值進行合并。
A4：將序表 B3 保存到result.xlsx文件中的 merge_data 工作表中。
上面程序用兩個循環(huán)就實現(xiàn)了多個 excel 文件數(shù)據(jù)合并，外循環(huán) for 遍歷了目錄下所有的 excel 文件，內(nèi)循環(huán)B1.conj則合并每個excel文件中的多個sheet工作表的數(shù)據(jù)。
C. 合并出大文件
前面第一個例子中的 A2、第二個例子中的 B3 都是在內(nèi)存中裝載了合并后的 Excel 的所有數(shù)據(jù)，然后一次性寫出。如果文件太多太大，那么對內(nèi)存的占用也會很大，甚至超出內(nèi)存允許的范圍。為此，我們可以采用流式追加的方式生成大文件。
腳本說明：
A1：打開指定輸出的文件。
A2: 遍歷目錄下需要合并的 excel 文件。
B2：打開一個需要合并的 excel 文件。
B3：如果輸出文件不存在，讀取 sheet 工作表的所有數(shù)據(jù)，包括標題行；如果輸出文件已經(jīng)有了，就通過 @t 選項指明第一行是標題，從第二行開始讀取數(shù)據(jù)。
B4：將 B3 讀取的數(shù)據(jù)以流式追加到 A1 指定的輸出文件的 merger 工作表中。
通過流式逐個讀取文件數(shù)據(jù)后追加寫入，這個方式適合將大量小的 excel 文件合并成一個大的 excel 文件。
3. 分組匯總
下面繼續(xù)以前面的銷售數(shù)據(jù) excel 文件為例。
A. 字段分組
根據(jù)某個字段或多個字段實現(xiàn)分組計算，腳本如下：
腳本說明：
A1：打開指定的 excel 文件。
A2：讀取并合并文件中所有 sheet 工作表的數(shù)據(jù)。
A3：在合并后的數(shù)據(jù)上按字段 'Customer ID' 分組求銷售額、平均值
A4：在合并后的數(shù)據(jù)上按字段 'Customer ID', 'Purchase Date' 分組求銷售額
B. 按序分組
集算器在進行分組聚合時還可以和相鄰數(shù)據(jù)行對比，在原數(shù)據(jù)已經(jīng)有序時可以不再排序，從而節(jié)省時間，并保持原有的次序。假設(shè)原數(shù)據(jù)已經(jīng)按日期排序，我們想按月份分組統(tǒng)計時，代碼如下。
集算器 SPL 腳本：
腳本說明：
A1至 B3：在前面的例子中已經(jīng)介紹，將同一目錄下所有相同結(jié)構(gòu)的 excel 文件的工作表進行合并。
A4：在序表 B3 的基本上重新構(gòu)造了一個序表 A4，將日期拆分，新增年、月字段。
A5：groups 跨年度按月分組匯總銷售額、平均值。
A6：groups@o 按年月分組匯總銷售額、平均值, 帶參數(shù) @o 實現(xiàn)分組歸并處理.
其中，A4 為數(shù)據(jù)記錄明細；A5 按月統(tǒng)計, 不區(qū)分年；A6 則按年月統(tǒng)計。這三個單元格中的數(shù)據(jù)展現(xiàn)出了不同層次的合并匯總結(jié)果。
C. 分段分組
將要統(tǒng)計的數(shù)據(jù)按條件分成幾段，統(tǒng)計各組的情況。
集算器 SPL 腳本：
代碼說明：
步驟A1到 B3 之間參考前面例子的說明。
A4：字段'Sale Amount'金額的范圍分成 5 段，然后累計求出各段的數(shù)量及總數(shù)。
不過，這樣的寫法不夠方便，如果我們想調(diào)整分段方案，就需要修改 groups 函數(shù)的參數(shù)，而這個參數(shù)表達式還是比較復雜的。這時，我們還可以利用集算器中另一個 pseg 函數(shù)，更方便地實現(xiàn)這個功能，腳本如下：
當然，我們也可以根據(jù)需要，按不同字段不同要求進行分組，然后進行統(tǒng)計處理。例如，在統(tǒng)計班級考生成績時，各科成績可劃分成優(yōu)、良、中、差、及格的分數(shù)區(qū)段，一次為條件進行統(tǒng)計。groups 用法還有很多，可以參考函數(shù)手冊中相應(yīng)的章節(jié)。
D. 大數(shù)據(jù)分組
前面的例子中，要讀取的 excel 文件都不能很大，也就是都能一次讀進內(nèi)存。手工處理大文件，也會有類似的要求，因為同時打開多個文件，意味著把這些文件都裝入內(nèi)存，很可能會超過機器的物理內(nèi)存，而用 VBA 讀取的情況也差不多。這時，我們就需要用流式的方法讀取數(shù)據(jù)，不需一次讀進內(nèi)存，而是邊讀取邊合并。
集算器 SPL 腳本：
代碼說明：
A1：使用 @r 選項指明以流式打開 excel 文件。
A2：遍歷 excel 中的 sheet 工作表。
B2：使用 @c 選項指明以游標方式導入數(shù)據(jù)。
B3：將游標B2匯集到B3序列中。
A4：將游標序列B3的成員合并到一起組成新的游標。
B4: 序列A4按‘Customer ID’分組累計‘Sale Amount’。
A5：將結(jié)果保存。
通過游標以流的方式循環(huán)從大文件中讀取一段段數(shù)據(jù)，實現(xiàn)對數(shù)據(jù)的分組合并。
4. 去重處理
實際數(shù)據(jù)合并過程中，往往會出現(xiàn)數(shù)據(jù)重復的現(xiàn)象，重復數(shù)據(jù)肯定會影響到我們對數(shù)據(jù)的計算分析。下面介紹使用集算器 SPL 腳本去除重復數(shù)據(jù)的幾種主要解決方法。
A. 主鍵去重
sales_2013中的數(shù)據(jù)，設(shè)其主鍵為’Invoice Number’，則根據(jù)主鍵去掉重復記錄。
代碼說明：
A1：打開指定的 excel 文件。
A2：導入 sheet 工作表中指定列的數(shù)據(jù)。
A3：將序表 A2 按主鍵' Invoice Number '分組去重處理, 其中參數(shù) @1 表示取每一個分組的第一條記錄組成排列后返回（注意是數(shù)字 1，不是字母 l）。
A4：將結(jié)果保存。
各個 sheet> 中的數(shù)據(jù)是唯一的，但合并的數(shù)據(jù)不一定是唯一的，因此采用主鍵方式去掉重復數(shù)據(jù)。
B. 某字段去重
根據(jù)數(shù)據(jù)表sales_2013中的某字段去重處理, 查看不同姓名的雇員記錄.
代碼說明：
A1：打開指定的 excel 文件。
A2：導入 sheet 工作表中指定列的數(shù)據(jù)。
A3: 從序表 A2 中獲取不重復姓名的記錄
A4：從序表 A2中獲取不重復姓名的記錄列表。
A5：將序表 A4 另存，首行記錄為標題。
A3數(shù)據(jù)去重結(jié)果：
C. 聯(lián)合多字段去重
有的記錄雖然有主鍵，但判斷是否為重復的記錄，需要用其它幾個字段來確定，此時用多個字段聯(lián)合來確定是否有重復記錄.
代碼說明：
A1：導入指定 excel 文件的數(shù)據(jù)。
A2：同上。
A3：按字段 'Customer ID', 'Purchase Date' 合并序表 A1，A2，返回序表 A3
A4：序表 A3 按 'Customer ID', 'Purchase Date' 分組去重。
A5：將結(jié)果保存。
當然，也可以根據(jù)需要，參考更多的字段進行分組合并，去掉重復記錄。
D. 記錄級去重
解決要合并的每個文件中的記錄本身是不重復的，但合并后可能存在重復記錄。
代碼說明：
A1：導入 excel 文件的數(shù)據(jù)。
B1: 根據(jù)字段'Invoice Number'去掉序表 A1中的重復數(shù)據(jù)
A2、B2：同上。
A3：合并序表 B1，B2 的數(shù)據(jù)，并去掉重復數(shù)據(jù)記錄返回序表 A3。選項 @u 表示序表成員按順序合并到一起組成新的序表, 去掉重復的記錄。
B3: 查看合并后的數(shù)據(jù)記錄數(shù)。
merge@u適合對多序表合并處理, 其中序表內(nèi)部有序且無重復數(shù)據(jù)。
本文主要介紹了集算器處理同構(gòu) excel 多文件合并、分組匯總數(shù)據(jù)及數(shù)據(jù)去重幾種情況，在實際工作中，還會遇到異構(gòu)的情況，只要把需要合并的字段讀成集算器的集合對象，后續(xù)處理和同構(gòu)的邏輯是一樣的。學會了用這種專業(yè)數(shù)據(jù)處理工具，不僅能合并 Excel 文件, 合并其他文本數(shù)據(jù)方法也是一致的，再也不用擔心合并數(shù)據(jù)中的多文件、大文件和結(jié)構(gòu)差異問題了。
5. 附件：
salesrar下載地址：http://img.raqsoft.com.cn/file/2018/09/d8df41ec3114468eb310ef52c4516e1f_sales.rar?
