学会这些“套路”，excel 合并汇总都不是事(1)

SLN

1.  问题背景 在日常工作中我们经常遇到具有相同表头的 Excel 文件，需要将它们合并到同一个工作表中再进行分析。当文件比较多时，手工合并表格通常是件很麻烦的事情，而如果数据量很大，用 Excel 自带的 VBA 来处理也会经常卡死。今天我就来分享一个专业的外部数据工具——集算器，掌握了集算器处理 Excel 多表合并的方法，就不用再编写复杂且低效的 VBA 代码了，简单的几行 SPL(Structured Process Language，结构化过程处理语言)代码就能轻松搞定 Excel 文件合并，即使文件再多、再大也不用担心。
2.  基本合并 A.  同一个 excel 中的多表合并下面的例子是一个包含了销售数据的 excel 文件，其中包含了按月划分的 3 个结构相同的 sheet 工作表，数据如下： january_2013:

Customer ID	Customer Name	Invoice Number	Sale Amount	Purchase Date
1234	John Smith	100-0002	$1,200.00	2013/1/1
2345	Mary Harrison	100-0003	$1,425.00	2013/1/6
3456	Lucy Gomez	100-0004	$1,390.00	2013/1/11
4567	Rupert Jones	100-0005	$1,257.00	2013/1/18
5678	Jenny Walters	100-0006	$1,725.00	2013/1/24
6789	Samantha Donaldson	100-0007	$1,995.00	2013/1/31

february_2013:

Customer ID	Customer Name	Invoice Number	Sale Amount	Purchase Date
9876	Daniel Farber	100-0008	$1,115.00	2013/2/2
8765	Laney Stone	100-0009	$1,367.00	2013/2/8
7654	Roger Lipney	100-0010	$2,135.00	2013/2/15
6543	Thomas Haines	100-0011	$1,346.00	2013/2/17
5432	Anushka Vaz	100-0012	$1,560.00	2013/2/21
4321	Harriet Cooper	100-0013	$1,852.00	2013/2/25

march_2013:

Customer ID	Customer Name	Invoice Number	Sale Amount	Purchase Date
6634	Poop Smith	100-0014	$1,350.00	2013/3/4
8765	Tony Song	100-0015	$1,167.00	2013/3/8
2345	Mary Harrison	100-0016	$1,789.00	2013/3/17
6543	Rachel Paz	100-0017	$2,042.00	2013/3/22
3456	Lucy Gomez	100-0018	$1,511.00	2013/3/28
4321	Susan Wallace	100-0019	$2,280.00	2013/3/30

在合并3个sheet的同时，我们还可以同时从每个sheet中筛选出字段Customer Name, Sale Amount。最后的效果如下：

Customer Name	Sale Amount
John Smith	1200
Mary Harrison	1425
Lucy Gomez	1390
Rupert Jones	1257
Jenny Walters	1725
.......	........
Susan Wallace	2280

  
集算器SPL脚本：

	A
1	=file(”D:/sales_2013.xlsx”).xlsopen()
2	=A1.conj(A1.xlsimport@t('Customer   Name','Sale Amount';~.stname))
3	>file(“D:/result_2013.xlsx”).   xlsexport@t(A2;"merge_sheets")

脚本说明：
A1：打开指定的 excel 文件，创建一个由多个 sheet 工作表组成的序列。
A2：利用 conj 函数遍历 A1 序列中所有的成员工作表，导入每个工作表中指定列'Customer Name','Sale Amount'，并将数据并合并。其中 xlsimport 函数导入指定列，最后一列用分号; 隔开。 参数~.stname表示指定当前工作表，由于在 conj 函数的循环中，所以就可以逐个导入所有工作表。同时，xlsimport 使用选项@t指明将工作表的第一行记录作为字段名。
A3：将序表 A2 作为一个新的工作表“merge_sheets”保存到原来的 excel 文件中，同样用选项 @t 指明首行记录为标题。
  
这段脚本只有三句话，短小精干之余，逻辑清晰，也比较容易理解。下面我们再看看如何合并多个文件中的多个工作表。

B.  不同 excel 中的多表合并 下面是要合并的多个 excel 文件，它们都具有和上面例子相同的表结构，每个文件记录了当年的数据：

集算器 SPL 脚本：

	A	B
1	for   directory@p(”d:/excel/*.xlsx“)	=file(A1).xlsopen()
2		=B1.conj(B1.xlsimport@t('Customer   Name','Sale Amount','Purchase Date';~.stname))
3		=@|B2
4	>   file(“d:/result.xlsx”). xlsexport@t(B3;"merge_data")

合并的效果如下：

Customer Name	Sale Amount	Purchase Date
John Smith	1200	2013-01-01
Mary Harrison	1425	2013-01-06
Lucy Gomez	1390	2013-01-11
Rupert Jones	1257	2013-01-18
......	......	......
Thomas Haines	1346	2013-02-17

  脚本说明：
A1：通过 for 循环，遍历指定目录下的 excel 文件，在 B1 到 B3 之间进行循环内处理.
B1：打开目录下的一个 excel 文件，生成序列。
B2：导入当前文件中的每个 sheet 工作表中指定列'Customer Name','Sale Amount'，'Purchase Date'的数据，然后合并这些数据，与前面例子中的 A2 类似。
B3：将序表 B2 的数据与 @表示的本网格的值进行合并。
A4：将序表 B3 保存到result.xlsx文件中的 merge_data 工作表中。

上面程序用两个循环就实现了多个 excel 文件数据合并，外循环 for 遍历了目录下所有的 excel 文件，内循环B1.conj则合并每个excel文件中的多个sheet工作表的数据。
C.  合并出大文件 前面第一个例子中的 A2、第二个例子中的 B3 都是在内存中装载了合并后的 Excel 的所有数据，然后一次性写出。如果文件太多太大，那么对内存的占用也会很大，甚至超出内存允许的范围。为此，我们可以采用流式追加的方式生成大文件。
  
集算器 SPL 脚本：

	A	B
1	=file("D:/out.xlsx")
2	for   directory@p(”d:/excel/*.xlsx“)	=file(A2).xlsopen()
3		=if(A1.exists(),B2.xlsimport@t(),B2.xlsimport())
4		>A1.xlsexport@s(B3;"merger")

合并后的效果如下：

Customer ID	Customer Name	Invoice Number	Sale Amount	Purchase Date
1234	John Smith	100-0002	1200	2013-01-01
2345	Mary Harrison	100-0003	1425	2013-01-06
3456	Lucy Gomez	100-0004	1390	2013-01-11
4567	Rupert Jones	100-0005	1257	2013-01-18
......	......	......	......	......
6789	Thomas Haines	100-0002	1346	2013-02-17

  
脚本说明：
A1：打开指定输出的文件。
A2: 遍历目录下需要合并的 excel 文件。
B2：打开一个需要合并的 excel 文件。
B3：如果输出文件不存在，读取 sheet 工作表的所有数据，包括标题行；如果输出文件已经有了，就通过 @t 选项指明第一行是标题，从第二行开始读取数据。
B4：将 B3 读取的数据以流式追加到 A1 指定的输出文件的 merger 工作表中。

       通过流式逐个读取文件数据后追加写入，这个方式适合将大量小的 excel 文件合并成一个大的 excel 文件。