学会这些“套路”,excel 合并汇总都不是事

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

阅读更多
换一批

没有更多推荐了,返回首页