北京
乾学院 43 号会员
4 回帖  •  6,153 浏览  • 
     ...

excel 合并汇总

 
集算器(484) Excel(13) ETL(9) 基础(18) 合并(3) 汇总(3) 导出(8) 大文件(3) 分组汇总(7) 字段分组(3) 按序分组(2) 分段分组(2) 大数据分组(2) 去重(2) 英文版(12)

1544598466585

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 文件。

3.  分组汇总

下面继续以前面的销售数据 excel 文件为例。

A.  字段分组

根据某个字段或多个字段实现分组计算，脚本如下：

A

B

1

=file(”D:/sales_2013.xlsx”).xlsopen()

2

=A1.conj(A1.xlsimport@t(;~.stname))

3

=A2.groups('Customer ID';sum('Sale   Amount'):Total,avg('Sale Amount'):Average)

4

=A2.groups('Customer ID','Purchase Date';sum('Sale   Amount'):Total)

 

A3的效果：

Customer   ID

Total

Average

1234

2550

1275.0

2345

3214

1607.0

3456

2901

1450.5

4321

4132

2066.0

……

……

……

4567

1257

1257.0

 A4的效果：

Customer ID

Purchase Date

Total

1234

2013-01-01

1200

1234

2013-03-04

1350

2345

2013-01-11

1425

2345

2013-03-17

1789

……

……

……

9876

2013-02-02

1115

 脚本说明：

A1：打开指定的 excel 文件。

A2：读取并合并文件中所有 sheet 工作表的数据。

A3：在合并后的数据上按字段 'Customer ID' 分组求销售额、平均值

A4：在合并后的数据上按字段 'Customer ID', 'Purchase Date' 分组求销售额

B.  按序分组

集算器在进行分组聚合时还可以和相邻数据行对比，在原数据已经有序时可以不再排序，从而节省时间，并保持原有的次序。假设原数据已经按日期排序，我们想按月份分组统计时，代码如下。

集算器 SPL 脚本：

A

B

1

for directory@p(”d:/excel/*.xlsx“)

=file(A1).xlsopen()

2

=B1.conj(B1.xlsimport@t(;~.stname))

3

=@|B2

4

=B3.derive(year('Purchase Date'):Year,month('Purchase   Date'):Month)

5

=A4.groups (month('Purchase Date'):Month;sum('Sale   Amount'):Total,avg('Sale Amount'):Average)

6

=A4.groups@o (month('Purchase Date'):Month;sum('Sale   Amount'):Total,avg('Sale Amount'):Average)

 

A5分组效果：

Month

Total

Average

1

272414

15134.111111111111

2

168038

9335.444444444445

3

357693

19871.833333333332

A6分组效果：

Month

Total

Average

1

8992

1498.6666666666667

2

9375

1562.5

3

10139

1689.8333333333333

1

260221

43370.166666666664

2

103656

17276.0

3

101509

16918.166666666668

1

3201

533.5

2

55007

9167.833333333334

3

246045

41007.5

 

脚本说明：

A1至 B3：在前面的例子中已经介绍，将同一目录下所有相同结构的 excel 文件的工作表进行合并。

A4：在序表 B3 的基本上重新构造了一个序表 A4，将日期拆分，新增年、月字段。

A5：groups 跨年度按月分组汇总销售额、平均值。

A6：groups@o 按年月分组汇总销售额、平均值, 带参数 @o 实现分组归并处理.

 

其中，A4 为数据记录明细；A5 按月统计, 不区分年；A6 则按年月统计。这三个单元格中的数据展现出了不同层次的合并汇总结果。

C.  分段分组

将要统计的数据按条件分成几段，统计各组的情况。

集算器 SPL 脚本：

A

B

1

for   directory@p(”d:/excel/*.xlsx“)

=file(A1).xlsopen()

2

=B1.conj(B1.xlsimport@t(;~.stname))

3

=@|B2

4

=B3.groups(if   ('Sale Amount'<1000,"1::<1000",

if   ('Sale Amount'<1500,"2::1000~~1500",

if   ('Sale Amount'<2000,"3::1500~~2000",

if   ('Sale Amount'<2500,"4::2000~~2500",

"5::>=2500")))):Segment;

count(1):Number,sum('Sale   Amount'):Total)


分组效果：

Segment

Number

Total

1::<1000

22

8280

2::1000~~1500

9

11617

3::1500~~2000

6

10432

4::2000~~2500

4

8810

5::>=2500

13

759006


代码说明：

步骤A1到 B3 之间参考前面例子的说明。

A4：字段'Sale Amount'金额的范围分成 5 段，然后累计求出各段的数量及总数。


不过，这样的写法不够方便，如果我们想调整分段方案，就需要修改 groups 函数的参数，而这个参数表达式还是比较复杂的。这时，我们还可以利用集算器中另一个 pseg 函数，更方便地实现这个功能，脚本如下：

A

B

1

[0,1000,1500,2000,2500]

2

for directory@p(”d:/excel/*.xlsx“)

=file(A1).xlsopen()

3

=B1.conj(B1.xlsimport@t(;~.stname))

4

=@|B2

=B4.groups(A1.pseg(~.'Sale   Amount'):Segment;

count(1):Number,sum('Sale Amount'):Total)

 

当然，我们也可以根据需要，按不同字段不同要求进行分组，然后进行统计处理。例如，在统计班级考生成绩时，各科成绩可划分成优、良、中、差、及格的分数区段，一次为条件进行统计。groups 用法还有很多，可以参考函数手册中相应的章节。

D.  大数据分组

前面的例子中，要读取的 excel 文件都不能很大，也就是都能一次读进内存。手工处理大文件，也会有类似的要求，因为同时打开多个文件，意味着把这些文件都装入内存，很可能会超过机器的物理内存，而用 VBA 读取的情况也差不多。这时，我们就需要用流式的方法读取数据，不需一次读进内存，而是边读取边合并。

 

集算器 SPL 脚本：

A

B

1

=file(“d:/tdata.xlsx”).xlsopen@r()

2

for   A1.count()

=A1.xlsimport@ct(;A1(A2). stname)

3

=@|B2

4

= B3.conjx()

=A4.groups('Customer   ID';sum('Sale Amount'):SaleTotal)

>file(“d:/out.xlsx”).exportxls@bt(B4;"Customer&Sales")

  

筛选分组的效果：

Customer ID

SaleTotal

1234

107721792

2345

139041639

3456

137985543

4321

96170742

...

...

9876

37590417

 

代码说明：

A1：使用 @r 选项指明以流式打开 excel 文件。
A2：遍历 excel 中的 sheet 工作表。

B2：使用 @c 选项指明以游标方式导入数据。

B3：将游标B2汇集到B3序列中。

A4：将游标序列B3的成员合并到一起组成新的游标。

B4: 序列A4按‘Customer ID’分组累计‘Sale Amount’。

A5：将结果保存。

 

通过游标以流的方式循环从大文件中读取一段段数据，实现对数据的分组合并。

4.  去重处理

实际数据合并过程中，往往会出现数据重复的现象，重复数据肯定会影响到我们对数据的计算分析。下面介绍使用集算器 SPL 脚本去除重复数据的几种主要解决方法。

A.  主键去重

sales_2013中的数据，设其主键为’Invoice Number’，则根据主键去掉重复记录。

A

B

1

=file(“d:/sales_2013.xlsx”).xlsopen()

2

=A1.conj(A1.xlsimport@t('Customer   Name', 'Invoice Number', 'Sale Amount';~. stname))

3

=A2.group@1('Invoice   Number')

4

>file(“d:/out.xlsx”).   xlsexport@t(A3;"result")

 

合并去重后的数据：

Customer Name

Invoice Number

Sale Amount

John Smith

100-0002

1200

Mary Harrison

100-0003

1425

Lucy Gomez

100-0004

1390

Rupert Jones

100-0005

1257

Jenny Walters

100-0006

1725

……

……

……

Susan Wallace

100-0019

2280

 

代码说明：

A1：打开指定的 excel 文件。

A2：导入 sheet 工作表中指定列的数据。

A3：将序表 A2 按主键' Invoice Number '分组去重处理, 其中参数 @1 表示取每一个分组的第一条记录组成排列后返回（注意是数字 1，不是字母 l）。

A4：将结果保存。

各个 sheet> 中的数据是唯一的，但合并的数据不一定是唯一的，因此采用主键方式去掉重复数据。

B.  某字段去重

根据数据表sales_2013中的某字段去重处理, 查看不同姓名的雇员记录.

A

B

1

=file(“d:/sales_2013.xlsx”).xlsopen()

2

=A1.conj(A1.xlsimport@t('Customer   ID', 'Customer Name';~. stname))

3

=A2.id('Customer   Name')

4

=A2.group@1('  Customer Name')

5

>file(“d:/out.xlsx”).   xlsexport@t(A4;"result")

代码说明：

A1：打开指定的 excel 文件。

A2：导入 sheet 工作表中指定列的数据。

A3: 从序表 A2 中获取不重复姓名的记录

A4：从序表 A2中获取不重复姓名的记录列表。

A5：将序表 A4 另存，首行记录为标题。

 

A3数据去重结果：

Member

Anushka Vaz

Daniel Farber

Harriet Cooper

……

Tony Song

A4数据去重结果：

Customer ID

Customer Name

5432

Anushka Vaz

9876

Daniel Farber

4321

Harriet Cooper

……

……

8765

Tony Song 

C.  联合多字段去重

有的记录虽然有主键，但判断是否为重复的记录，需要用其它几个字段来确定，此时用多个字段联合来确定是否有重复记录.

A

B

1

=file(“d:/sales_2013.xlsx”).importxls@t()

2

=file(“d:/sales_2014.xlsx”).importxls@t()

3

=[A1,A2].merge('Customer   ID', 'Purchase Date')

4

=A3.group@1('Customer   ID', 'Purchase Date')

5

>file(“d:/out.xlsx”).   xlsexport@t(A4;"result")

 

代码说明：

A1：导入指定 excel 文件的数据。

A2：同上。

A3：按字段 'Customer ID', 'Purchase Date' 合并序表 A1，A2，返回序表 A3

A4：序表 A3 按 'Customer ID', 'Purchase Date' 分组去重。

A5：将结果保存。
当然，也可以根据需要，参考更多的字段进行分组合并，去掉重复记录。

D.  记录级去重

   解决要合并的每个文件中的记录本身是不重复的，但合并后可能存在重复记录。

A

B

1

=file(“d:/sales_2013.xlsx”).importxls@t()

=A1.group@1('Invoice   Number')

2

=file(“d:/sales_2014.xlsx”).importxls@t()

=A2.group@1('Invoice   Number')

3

=[B1,B2].merge@u()

=A3.count()

代码说明：

A1：导入 excel 文件的数据。

B1: 根据字段'Invoice Number'去掉序表 A1中的重复数据

A2、B2：同上。

A3：合并序表 B1，B2 的数据，并去掉重复数据记录返回序表 A3。选项 @u 表示序表成员按顺序合并到一起组成新的序表, 去掉重复的记录。

B3: 查看合并后的数据记录数。
merge@u适合对多序表合并处理, 其中序表内部有序且无重复数据。

 

本文主要介绍了集算器处理同构 excel 多文件合并、分组汇总数据及数据去重几种情况，在实际工作中，还会遇到异构的情况，只要把需要合并的字段读成集算器的集合对象，后续处理和同构的逻辑是一样的。学会了用这种专业数据处理工具，不仅能合并 Excel 文件, 合并其他文本数据方法也是一致的，再也不用担心合并数据中的多文件、大文件和结构差异问题了。

5.  附件：

salesrar

相关文章:

Java 嵌入 SPL 轻松实现 Excel 文件合并

例子程序:
多文件内容排序后合并 
如何将两列数据转为一列数据  
对分组后的各组前几名做合并   
跨行计算库存

集算器(484) Excel(13) ETL(9) 基础(18) 合并(3) 汇总(3) 导出(8) 大文件(3) 分组汇总(7) 字段分组(3) 按序分组(2) 分段分组(2) 大数据分组(2) 去重(2) 英文版(12)