1. 問題背景
在日常工作中我們經(jīng)常遇到具有相同表頭的 Excel 文件浩螺，需要將它們合并到同一個(gè)工作表中再進(jìn)行分析。當(dāng)文件比較多時(shí)仍侥，手工合并表格通常是件很麻煩的事情要出，而如果數(shù)據(jù)量很大，用 Excel 自帶的 VBA 來處理也會(huì)經(jīng)撑┰ǎ卡死患蹂。今天我就來分享一個(gè)專業(yè)的外部數(shù)據(jù)工具——集算器，掌握了集算器處理 Excel 多表合并的方法砸紊，就不用再編寫復(fù)雜且低效的 VBA 代碼了况脆，簡(jiǎn)單的幾行 SPL(Structured Process Language，結(jié)構(gòu)化過程處理語言)代碼就能輕松搞定 Excel 文件合并批糟，即使文件再多格了、再大也不用擔(dān)心。
2. 基本合并
A. 同一個(gè) excel 中的多表合并
下面的例子是一個(gè)包含了銷售數(shù)據(jù)的 excel 文件徽鼎，其中包含了按月劃分的 3 個(gè)結(jié)構(gòu)相同的 sheet 工作表盛末，數(shù)據(jù)如下：
集算器SPL腳本：
腳本說明：
A1：打開指定的 excel 文件，創(chuàng)建一個(gè)由多個(gè) sheet 工作表組成的序列否淤。
A2：利用 conj 函數(shù)遍歷 A1 序列中所有的成員工作表悄但，導(dǎo)入每個(gè)工作表中指定列'Customer Name','Sale Amount'，并將數(shù)據(jù)并合并石抡。其中 xlsimport 函數(shù)導(dǎo)入指定列檐嚣，最后一列用分號(hào); 隔開。 參數(shù)~.stname表示指定當(dāng)前工作表啰扛，由于在 conj 函數(shù)的循環(huán)中嚎京，所以就可以逐個(gè)導(dǎo)入所有工作表嗡贺。同時(shí)，xlsimport 使用選項(xiàng)@t指明將工作表的第一行記錄作為字段名鞍帝。
A3：將序表 A2 作為一個(gè)新的工作表“merge_sheets”保存到原來的 excel 文件中诫睬，同樣用選項(xiàng) @t 指明首行記錄為標(biāo)題。
這段腳本只有三句話帕涌，短小精干之余摄凡，邏輯清晰，也比較容易理解蚓曼。下面我們?cè)倏纯慈绾魏喜⒍鄠€(gè)文件中的多個(gè)工作表亲澡。
B. 不同 excel 中的多表合并
下面是要合并的多個(gè) excel 文件，它們都具有和上面例子相同的表結(jié)構(gòu)纫版，每個(gè)文件記錄了當(dāng)年的數(shù)據(jù)?：
腳本說明：
A1：通過 for 循環(huán)床绪，遍歷指定目錄下的 excel 文件，在 B1 到 B3 之間進(jìn)行循環(huán)內(nèi)處理.
B1：打開目錄下的一個(gè) excel 文件捎琐，生成序列会涎。
B2：導(dǎo)入當(dāng)前文件中的每個(gè) sheet 工作表中指定列'Customer Name','Sale Amount'，'Purchase Date'的數(shù)據(jù)瑞凑，然后合并這些數(shù)據(jù)末秃，與前面例子中的 A2 類似。
B3：將序表 B2 的數(shù)據(jù)與 @表示的本網(wǎng)格的值進(jìn)行合并籽御。
A4：將序表 B3 保存到result.xlsx文件中的 merge_data 工作表中练慕。
上面程序用兩個(gè)循環(huán)就實(shí)現(xiàn)了多個(gè) excel 文件數(shù)據(jù)合并，外循環(huán) for 遍歷了目錄下所有的 excel 文件技掏，內(nèi)循環(huán)B1.conj則合并每個(gè)excel文件中的多個(gè)sheet工作表的數(shù)據(jù)铃将。
C. 合并出大文件
前面第一個(gè)例子中的 A2、第二個(gè)例子中的 B3 都是在內(nèi)存中裝載了合并后的 Excel 的所有數(shù)據(jù)哑梳，然后一次性寫出劲阎。如果文件太多太大，那么對(duì)內(nèi)存的占用也會(huì)很大鸠真，甚至超出內(nèi)存允許的范圍悯仙。為此，我們可以采用流式追加的方式生成大文件吠卷。
腳本說明：
A1：打開指定輸出的文件锡垄。
A2: 遍歷目錄下需要合并的 excel 文件。
B2：打開一個(gè)需要合并的 excel 文件祭隔。
B3：如果輸出文件不存在货岭，讀取 sheet 工作表的所有數(shù)據(jù)，包括標(biāo)題行；如果輸出文件已經(jīng)有了千贯，就通過 @t 選項(xiàng)指明第一行是標(biāo)題屯仗，從第二行開始讀取數(shù)據(jù)。
B4：將 B3 讀取的數(shù)據(jù)以流式追加到 A1 指定的輸出文件的 merger 工作表中丈牢。
通過流式逐個(gè)讀取文件數(shù)據(jù)后追加寫入祭钉，這個(gè)方式適合將大量小的 excel 文件合并成一個(gè)大的 excel 文件瞄沙。
3. 分組匯總
下面繼續(xù)以前面的銷售數(shù)據(jù) excel 文件為例己沛。
A. 字段分組
根據(jù)某個(gè)字段或多個(gè)字段實(shí)現(xiàn)分組計(jì)算，腳本如下：
腳本說明：
A1：打開指定的 excel 文件距境。
A2：讀取并合并文件中所有 sheet 工作表的數(shù)據(jù)申尼。
A3：在合并后的數(shù)據(jù)上按字段 'Customer ID' 分組求銷售額、平均值
A4：在合并后的數(shù)據(jù)上按字段 'Customer ID', 'Purchase Date' 分組求銷售額
B. 按序分組
集算器在進(jìn)行分組聚合時(shí)還可以和相鄰數(shù)據(jù)行對(duì)比垫桂，在原數(shù)據(jù)已經(jīng)有序時(shí)可以不再排序师幕，從而節(jié)省時(shí)間，并保持原有的次序诬滩。假設(shè)原數(shù)據(jù)已經(jīng)按日期排序霹粥，我們想按月份分組統(tǒng)計(jì)時(shí)，代碼如下疼鸟。
集算器 SPL 腳本：
腳本說明：
A1至 B3：在前面的例子中已經(jīng)介紹后控，將同一目錄下所有相同結(jié)構(gòu)的 excel 文件的工作表進(jìn)行合并。
A4：在序表 B3 的基本上重新構(gòu)造了一個(gè)序表 A4空镜，將日期拆分浩淘，新增年、月字段吴攒。
A5：groups 跨年度按月分組匯總銷售額张抄、平均值。
A6：groups@o 按年月分組匯總銷售額洼怔、平均值, 帶參數(shù) @o 實(shí)現(xiàn)分組歸并處理.
其中署惯，A4 為數(shù)據(jù)記錄明細(xì)；A5 按月統(tǒng)計(jì), 不區(qū)分年镣隶；A6 則按年月統(tǒng)計(jì)极谊。這三個(gè)單元格中的數(shù)據(jù)展現(xiàn)出了不同層次的合并匯總結(jié)果。
C. 分段分組
將要統(tǒng)計(jì)的數(shù)據(jù)按條件分成幾段矾缓，統(tǒng)計(jì)各組的情況怀酷。
集算器 SPL 腳本：
代碼說明：
步驟A1到 B3 之間參考前面例子的說明。
A4：字段'Sale Amount'金額的范圍分成 5 段嗜闻，然后累計(jì)求出各段的數(shù)量及總數(shù)蜕依。
不過，這樣的寫法不夠方便，如果我們想調(diào)整分段方案样眠，就需要修改 groups 函數(shù)的參數(shù)友瘤，而這個(gè)參數(shù)表達(dá)式還是比較復(fù)雜的。這時(shí)檐束，我們還可以利用集算器中另一個(gè) pseg 函數(shù)辫秧，更方便地實(shí)現(xiàn)這個(gè)功能，腳本如下：
當(dāng)然被丧，我們也可以根據(jù)需要盟戏，按不同字段不同要求進(jìn)行分組，然后進(jìn)行統(tǒng)計(jì)處理甥桂。例如柿究，在統(tǒng)計(jì)班級(jí)考生成績(jī)時(shí)，各科成績(jī)可劃分成優(yōu)黄选、良蝇摸、中、差办陷、及格的分?jǐn)?shù)區(qū)段貌夕，一次為條件進(jìn)行統(tǒng)計(jì)。groups 用法還有很多民镜，可以參考函數(shù)手冊(cè)中相應(yīng)的章節(jié)啡专。
D. 大數(shù)據(jù)分組
前面的例子中，要讀取的 excel 文件都不能很大殃恒，也就是都能一次讀進(jìn)內(nèi)存植旧。手工處理大文件，也會(huì)有類似的要求离唐，因?yàn)橥瑫r(shí)打開多個(gè)文件病附，意味著把這些文件都裝入內(nèi)存，很可能會(huì)超過機(jī)器的物理內(nèi)存亥鬓，而用 VBA 讀取的情況也差不多完沪。這時(shí)，我們就需要用流式的方法讀取數(shù)據(jù)嵌戈，不需一次讀進(jìn)內(nèi)存覆积，而是邊讀取邊合并。
集算器 SPL 腳本：
代碼說明：
A1：使用 @r 選項(xiàng)指明以流式打開 excel 文件熟呛。
A2：遍歷 excel 中的 sheet 工作表宽档。
B2：使用 @c 選項(xiàng)指明以游標(biāo)方式導(dǎo)入數(shù)據(jù)。
B3：將游標(biāo)B2匯集到B3序列中庵朝。
A4：將游標(biāo)序列B3的成員合并到一起組成新的游標(biāo)吗冤。
B4: 序列A4按‘Customer ID’分組累計(jì)‘Sale Amount’又厉。
A5：將結(jié)果保存。
通過游標(biāo)以流的方式循環(huán)從大文件中讀取一段段數(shù)據(jù)椎瘟，實(shí)現(xiàn)對(duì)數(shù)據(jù)的分組合并覆致。
4. 去重處理
實(shí)際數(shù)據(jù)合并過程中，往往會(huì)出現(xiàn)數(shù)據(jù)重復(fù)的現(xiàn)象肺蔚，重復(fù)數(shù)據(jù)肯定會(huì)影響到我們對(duì)數(shù)據(jù)的計(jì)算分析煌妈。下面介紹使用集算器 SPL 腳本去除重復(fù)數(shù)據(jù)的幾種主要解決方法。
A. 主鍵去重
sales_2013中的數(shù)據(jù)宣羊，設(shè)其主鍵為’Invoice Number’璧诵，則根據(jù)主鍵去掉重復(fù)記錄。
代碼說明：
A1：打開指定的 excel 文件段只。
A2：導(dǎo)入 sheet 工作表中指定列的數(shù)據(jù)腮猖。
A3：將序表 A2 按主鍵' Invoice Number '分組去重處理, 其中參數(shù) @1 表示取每一個(gè)分組的第一條記錄組成排列后返回（注意是數(shù)字 1鉴扫，不是字母 l）赞枕。
A4：將結(jié)果保存。
各個(gè) sheet> 中的數(shù)據(jù)是唯一的坪创，但合并的數(shù)據(jù)不一定是唯一的炕婶，因此采用主鍵方式去掉重復(fù)數(shù)據(jù)。
B. 某字段去重
根據(jù)數(shù)據(jù)表sales_2013中的某字段去重處理, 查看不同姓名的雇員記錄.
代碼說明：
A1：打開指定的 excel 文件莱预。
A2：導(dǎo)入 sheet 工作表中指定列的數(shù)據(jù)柠掂。
A3: 從序表 A2 中獲取不重復(fù)姓名的記錄
A4：從序表 A2中獲取不重復(fù)姓名的記錄列表。
A5：將序表 A4 另存依沮，首行記錄為標(biāo)題涯贞。
A3數(shù)據(jù)去重結(jié)果：
C. 聯(lián)合多字段去重
有的記錄雖然有主鍵，但判斷是否為重復(fù)的記錄危喉，需要用其它幾個(gè)字段來確定宋渔，此時(shí)用多個(gè)字段聯(lián)合來確定是否有重復(fù)記錄.
代碼說明：
A1：導(dǎo)入指定 excel 文件的數(shù)據(jù)。
A2：同上辜限。
A3：按字段 'Customer ID', 'Purchase Date' 合并序表 A1皇拣，A2，返回序表 A3
A4：序表 A3 按 'Customer ID', 'Purchase Date' 分組去重薄嫡。
A5：將結(jié)果保存氧急。
當(dāng)然，也可以根據(jù)需要毫深，參考更多的字段進(jìn)行分組合并吩坝，去掉重復(fù)記錄。
D. 記錄級(jí)去重
解決要合并的每個(gè)文件中的記錄本身是不重復(fù)的哑蔫，但合并后可能存在重復(fù)記錄钉寝。
代碼說明：
A1：導(dǎo)入 excel 文件的數(shù)據(jù)手素。
B1: 根據(jù)字段'Invoice Number'去掉序表 A1中的重復(fù)數(shù)據(jù)
A2、B2：同上瘩蚪。
A3：合并序表 B1泉懦，B2 的數(shù)據(jù)，并去掉重復(fù)數(shù)據(jù)記錄返回序表 A3疹瘦。選項(xiàng) @u 表示序表成員按順序合并到一起組成新的序表, 去掉重復(fù)的記錄崩哩。
B3: 查看合并后的數(shù)據(jù)記錄數(shù)。
merge@u適合對(duì)多序表合并處理, 其中序表內(nèi)部有序且無重復(fù)數(shù)據(jù)言沐。
本文主要介紹了集算器處理同構(gòu) excel 多文件合并邓嘹、分組匯總數(shù)據(jù)及數(shù)據(jù)去重幾種情況，在實(shí)際工作中险胰，還會(huì)遇到異構(gòu)的情況汹押，只要把需要合并的字段讀成集算器的集合對(duì)象，后續(xù)處理和同構(gòu)的邏輯是一樣的起便。學(xué)會(huì)了用這種專業(yè)數(shù)據(jù)處理工具棚贾，不僅能合并 Excel 文件, 合并其他文本數(shù)據(jù)方法也是一致的，再也不用擔(dān)心合并數(shù)據(jù)中的多文件榆综、大文件和結(jié)構(gòu)差異問題了妙痹。
5. 附件：
salesrar下載地址：http://img.raqsoft.com.cn/file/2018/09/d8df41ec3114468eb310ef52c4516e1f_sales.rar?
