早下班系列 - 合并 Excel

日常工作中,经常需要把多个 Excel 文件的数据合并到一起,便于做各种统计分析。

1     纵向合并 - 列名列数相同

 

最简单常见的是把几个列名列数列顺序完全一样的文件,纵向合并到一起。

 

如:

              Fruits.xlsx                             Meats.xlsx

合并前: undefinedundefined

合并后:undefined

 

实现代码:


A

1

=file("Fruits.xlsx").xlsimport@t()

2

=file("Meats.xlsx").xlsimport@t()

3

=A1|A2

4

=file("Foods.xlsx").xlsexport@t(A3)

 

2     横向合并 - 行名行数相同

一些行数一样,行名一样的 Excel 文件,常常需要横向合并到一起,如:

 

合并前:

       Fruits.xlsx                           FruitStock.xlsx

undefined

合并后:

undefined

实现代码:


A

1

=file("Fruits.xlsx").xlsimport@t()

2

=file("FruitStock.xlsx").xlsimport@t()

3

=A1.new(Name,UnitPrice,A2(#).Stock,A2(#).MaximumStock)

4

=file("FruitsPriceStock.xlsx").xlsexport@t(A3)

3     纵向合并 - 列名列数不同 - 保留所有列

合并前:

       FruitsPriceStock.xlsx                                                        MeatsPriceStock.xlsx

 undefined

 

合并后:

undefined

实现代码:


A

B

1

=file("FruitsPriceStock.xlsx").xlsimport@t()


2

=file("MeatsPriceStock.xlsx").xlsimport@t()


3

=create(${(A1.fname()&A2.fname()).concat@c()})

/需要保留所有列,所以用列名的并集

4

=A3.insert@f(0:A1)



=A3.insert@f(0:A2)



=file("FoodsPriceStock.xlsx").xlsexport@t(A3)


 

4     纵向合并 - 列名列数不同 - 只保留重复列

合并前:

       FruitsPriceStock.xlsx                                                        MeatsPriceStock.xlsx

 

 

合并后:

undefined

实现代码:


A

B

1

=file("FruitsPriceStock.xlsx").xlsimport@t()


2

=file("MeatsPriceStock.xlsx").xlsimport@t()


3

=create(${(A1.fname()^A2.fname()).concat@c()})

/只保留重复列,所以用列名的交集

4

=A3.insert@f(0:A1)



=A3.insert@f(0:A2)



=file("FoodsPriceStock.xlsx").xlsexport@t(A3)


 

5     纵向合并 - 列名列数不同 - 只保留第一个文件的列

 

合并前:

       FruitsPriceStock.xlsx                                                        MeatsPriceStock.xlsx

 

 

合并后:

undefined

实现代码:


A

B

1

=file("FruitsPriceStock.xlsx").xlsimport@t()


2

=file("MeatsPriceStock.xlsx").xlsimport@t()


3

=A1.insert@f(0:A2)

/@f的意思是把 A2 中同名字段的数据插入 A1 中

4

=file("FoodsPriceStock.xlsx").xlsexport@t(A3)


 

6     横向合并 - 行名行数不同 - 保留所有行

 

合并前:

Meats.xlsx                          MeatStock.xlsx

undefinedundefined

 

合并后:

实现代码:


A

B

1

=file("Meats.xlsx").xlsimport@t()


2

=file("MeatStock.xlsx").xlsimport@t()


3

=join@f(A1:Price,Name;A2:Stock,Name)

/@f是全连接

4

=A3.new([Price.Name,Stock.Name].ifn():Name,Stock.Stock,Stock.MinimumStock,Price.UnitPrice)

/蓝色代码意思是选出不为空的 Name 值

5

=file("MeatsPriceStock.xlsx").xlsexport@t(A4)


 

 

7     横向合并 - 行名行数不同 - 只保留重复行

合并前:

Meats.xlsx                          MeatStock.xlsx

合并后:

undefined

实现代码:


A

B

1

=file("Meats.xlsx").xlsimport@t()


2

=file("MeatStock.xlsx").xlsimport@t()


3

=join(A1:Price,Name;A2:Stock,Name)

/内连接

4

=A3.new(Stock.Name,Stock.Stock,Stock.MinimumStock,Price.UnitPrice)


5

=file("MeatsPriceStock.xlsx").xlsexport@t(A4)


 

8     横向合并 - 行名行数及次序不同 - 只保留第一个文件的行并对齐

合并前:

Meats.xlsx                          MeatStock.xlsx

合并后:

undefined

实现代码:


A

B

1

=file("Meats.xlsx").xlsimport@t()


2

=file("MeatStock.xlsx").xlsimport@t()


3

=join@1(A1:Price,Name;A2:Stock,Name)

/@1是左连接,注意这里是数字 1,不是字母 l

4

=A3.new([Price.Name,Stock.Name].ifn():Name,Stock.Stock,Stock.MinimumStock,Price.UnitPrice)

/ifn()在这里是选出不为空的 Name

5

=file("MeatsPriceStock.xlsx").xlsexport@t(A4)


 

 

9     纵向合并 - 文件名转成列值 - 文件个数不定

合并前:

 

Apple.xlsx                  Bread.xlsx                  Pork.xlsx

undefined undefined undefined

合并后:

undefined

 

SPL 实现代码:


A

B

1

=directory@p("tmp/*.xlsx")

/列出目录下的所有文件,此种方式可处理不定数量的文件

2

=A1.conj((fn=filename@n(~),T(~).derive(fn:Commodity)))


3

=file("Amount.xlsx").xlsexport@t(A2)


 

10          横向合并 - 文件名转成列名

合并前:

 

Apple.xlsx                   Bread.xlsx                  Pork.xlsx

  

合并后:

undefined

 

SPL 实现代码:


A

B

1

=directory@p("tmp/*.xlsx")

/列出目录下的所有文件名

2

=A1.(filename@n(~))

/获得不带扩展名的文件名

3

=A1.(T(~))

/把文件读成序表

4

=A3(1).new(Name,Amount:${A2(1)},A3(2)(#).Amount:${A2(2)},A3(3)(#).Amount:${A2(3)})

/产生新序表的同时,把原序表中的 Amount 字段转成各自的文件名

5

=file("Amount.xlsx").xlsexport@t(A4)


 

 

11          横向合并 - 一对多 - 复制数据

合并前:

 

Types.xlsx

undefined

Foods.xlsx

undefined

 

合并后:

undefined

 

SPL 实现代码:


A

B

1

=T("Types.xlsx")


2

=T("Foods.xlsx")


3

=join@f(A1:Type,Type;A2:Food,Type)

/@f为全连接

4

=A3.new(Food.Type,Food.Name,Food.UnitPrice,Type.Description)


5

=T("FoodsDescription.xlsx",A4)


12          横向合并 - 一对多 - 后续行置空

合并前:

 

Types.xlsx

Foods.xlsx

 

合并后:

undefined

 

SPL 实现代码:


A

B

1

=T("Types.xlsx")


2

=T("Foods.xlsx")


3

=A1.align(A2:Type,Type)

/align表示 A1 向 A2 对齐,对齐条件是 A2 的 Type 字段和 A1 的 Type 字段,如果 A2 有重复数据,只对齐第一行

4

=A2.new(Type,Name,UnitPrice,A3(#).Description)


5

=T("FoodsDescription.xlsx",A4)


 

13          纵向合并去重 - 整行重复

 

纵向合并时如遇到整行数据重复,合并时只保留相同记录中的一条,如:

 

合并前

undefinedundefined

 

从上图可以看出,Cindy 和 Lily 为整行重复数据,合并后的结果如下:

undefined

实现代码:


A

B

1

=file("Customer1.xlsx").xlsimport@t().sort(Name,Times)

/因为 merge 为归并,所以源数据需排序

2

=file("Customer2.xlsx").xlsimport@t().sort(Name,Times)


3

=[A1,A2].merge@u(Name,Times)

/merge@u表示并集,以 Name 和 Times 作为判断相等的标准,所以如果以整行作为判断相等的标准,则必须写上所有字段名

4

=file("CustomerTimes.xlsx").xlsexport@t(A3)


14          纵向合并去重 - 行头重复 - 保留初次出现的数据

纵向合并多个 Excel 文件时,可能只以行头或者其中某一 / 几个关键列作为判断数据是否重复的标准,如下例所示,仅用 Name 作为判断是否重复的标准:

 

合并前

 

从上图可以看出,Cindy 和 Lily 为 Name 字段重复的数据,合并后的结果如下:

实现代码:


A

B

1

=file("Customer1.xlsx").xlsimport@t().sort(Name,Times)

/因为 merge 为归并,所以源数据需排序

2

=file("Customer2.xlsx").xlsimport@t().sort(Name,Times)


3

=[A1,A2].merge@u(Name)

/merge@u表示并集,以 Name 作为判断相等的标准

4

=file("CustomerTimes.xlsx").xlsexport@t(A3)


 

15          纵向合并去重 - 行头重复 - 保留不为空的数据

 

Customer3.xlsx                Customer4.xlsx

undefined  undefined

从上图看出,Cindy 和 Lily 重复了,合并时去掉 Quantity 值为空的记录,合并后的结果如下:

undefined

实现代码:


A

1

=file("Customer3.xlsx").xlsimport@t().select(Quantity!=null)

2

=file("Customer4.xlsx").xlsimport@t().select(Quantity!=null)

3

=A1|A2

4

=file("CustomerQuantity.xlsx").xlsexport@t(A3)

 

16          纵向合并去重 - 行头重复 - 删除所有重复数据

 

CustomerTotal.xlsx                                                 Customer.xlsx

undefined      undefined

Name 字段作为关键列,关键列相同认为是重复数据,需要从 CustomerTotal.xlsx 中把在 Customer.xlsx 里重复出现的数据都删了, 去重后的结果如下所示:

undefined

实现代码如下:


A

B

1

=file("CustomerTotal.xlsx").xlsimport@t().sort(Name)

/因为 merge 为归并,所以源数据需排序

2

=file("Customer.xlsx").xlsimport@t().sort(Name)


3

=[A1,A2].merge@d(Name)

/@d表示从第一个序表中删除后续序表中出现的数据

4

=file("CustomerTotalNew.xlsx").xlsexport@t(A3)


 

17          横向合并去重 - 列名重复 - 保留后出现的列数据

 

合并前:

              CustomerFruits.xlsx                                          CustomerMeats.xlsx

undefinedundefined

可以发现,Bread 重复了,合并后希望保留第二个文件中的 Bread 字段,去掉第一个文件中的 Bread,结果如下所示:

undefined

 

实现代码:


A

1

=file("CustomerFruits.xlsx").xlsimport@t()

2

=file("CustomerMeats.xlsx").xlsimport@t()

3

=A1.new(Name,Apple,Strawberry,Peach,A2(#).Mutton,A2(#).Pork,A2(#).Bread,A2(#).Duck)

4

=file("CustomerFoods.xlsx").xlsexport@t(A3)

 

18          横纵两个方向同时合并 - 保留先出现的数据

 

合并前:

              CustomerFruits1.xlsx                                      CustomerMeats1.xlsx

undefined   undefined

 

按照先 CustomerFruits1.xlsx 后 CustomerMeats1.xlsx,重复项保留先出现在 CustomerFruits1.xlsx 中的数据,合并后:

undefined

 

SPL 的实现代码:


A

B

1

=file("CustomerFruits1.xlsx").xlsimport@t()


2

=file("CustomerMeats1.xlsx").xlsimport@t()


3

=A1.pivot@r(Name;col,val)

/将原交叉结构的数据转置成列表

4

=A2.pivot@r(Name;col,val)


5

=(A3|A4).group@1(Name,col)

/分组后取第一条出现的记录

6

=A5.pivot(Name;col,val)

/再转置回交叉结构

7

=file("CustomerFoods1.xlsx").xlsexport@t(A6)


19          汇总文件 - 相同行列

业务上有时候需要在合并多个 Excel 的同时汇总数据,比如:

 

Apple.xlsx                  Bread.xlsx                 Pork.xlsx

  

现需要将 Amount 汇总起来,形成一个总金额字段,存到新文件中,结果如下:

undefined

 

SPL 实现代码:


A

1

=file("Apple.xlsx").xlsimport@t()

2

=file("Bread.xlsx").xlsimport@t()

3

=file("Pork.xlsx").xlsimport@t()

4

=A1.new(Name,Amount+A2(#).Amount+A3(#).Amount:TotalAmount)

5

=file("TotalAmount.xlsx").xlsexport@t(A4)

 

20          汇总文件 - 横纵两个方向同时合并 - 汇总重复项

 

合并前:

              CustomerFruits1.xlsx                                       CustomerMeats1.xlsx

  

 

重复项汇总,合并后:

undefined

 

SPL 的实现代码:


A

B

1

=file("CustomerFruits1.xlsx").xlsimport@t()


2

=file("CustomerMeats1.xlsx").xlsimport@t()


3

=A1.pivot@r(Name;col,val)

/将原交叉结构的数据转置成列表

4

=A2.pivot@r(Name;col,val)


5

=(A3|A4).groups(Name,col;sum(val):val)

/分组汇总

6

=A5.pivot(Name;col,val)

/再转置回交叉结构

7

=file("CustomerFoods2.xlsx").xlsexport@t(A6)


 

21          汇总文件 - 按单元格位置对位汇总 - 文件个数不定

 

总公司有收到各分公司发来的资产负债表,其中某分公司的表格如下图所示 (共有 37 行,图中只列出 14 行):

undefined

现在需要用各分公司的表格汇总出总公司的资产负债表。

编写 SPL 脚本:


A

B

C

1

=directory@p("zc*.xlsx")

/列出目录下文件名匹配格式的所有文件,此种方式可处理不定数量的文件

2

=A1.(file(~).xlsopen())



3

=to(4,37)

[B,C,E,F]

=A3.(B3.(~/A3.~)).conj()

4

for C3

>v=null


5


for A2

>v+=number(B5.xlscell(A4,1))

6


>A2(1).xlscell(A4,1;string(v))


7

=file("total.xlsx").xlswrite(A2(1))

A1   列出文件夹中要汇总的所有以 zc 开头的资产负债表文件名,选项 @p 表示列出文件全路径

A2   打开 A1 中列出的文件为 Excel 对象

A3   指定要汇总的数字单元格的行号范围 4-37

B3   指定要汇总的数字单元格的列号 B,C,E,F

C3   用 A3 行号和 B3 列号拼出所有要汇总的数字单元格的名称

A4   循环 C3 中所有要汇总的单元格

B4   定义汇总值变量 v

B5   循环所有分公司资产负债表

C5   从当前分公司资产负债表中读出当前汇总单元格的值,转成数值后累加到 v

B6   将完成累加后的 v 保存到第 1 个分公司的资产负债表中

A7   将第 1 个分公司的资产负债表保存到总公司资产负债表 total.xlsx

 

 

22          汇总文件 - 追加汇总

 

有每日商品进货发货统计表如下图:

undefined

另有商品每日进销存汇总表如下:

undefined

现在需要把一日的进货发货数据追加到汇总表,并计算新的库存:前日库存 + 进货 - 发货,汇总后的结果如下所示:

undefined

编写 SPL 脚本:


A

1

=T("20200803.xlsx").derive(Inventory)

2

=T("total.xlsx")

3

=A1.run(Inventory=A2.select@z1(Goods==A1.Goods).Inventory+Purchase-Delivery)

4

=file("total.xlsx").xlsexport@a(A3)

A1   读出需追加汇总的当日数据并新增一列 Inventory

A2   读出汇总表数据

A3   循环 A1 中每一行,令 Inventory 的值为汇总表中最后一条当前商品的 Inventory 加上当前的 Purchase 再减去当前的 Delivery。选项 @z1 表示从后向前选择第 1 条满足条件的记录

A4   将 A3 中的结果追加保存到文件 total.xlsx,选项 @a 表示追加数据

 

23          汇总文件 - 累计汇总

 

现有本月一些商品的日销售额统计表,每日一个文件,需要在这些文件的当月累计销售额字段中增加累计值

 

合并前:

       20220101.xlsx

undefined

20220102.xlsx

undefined

20220103.xlsx

undefined

其它日期文件略过…

 

合并后:

       20220101.xlsx

undefined

20220102.xlsx

undefined

20220103.xlsx

undefined

其它日期文件略过…

 

SPL 的实现代码:


A

B

1

2022-01-01

2022-01-31

2

=periods(A1,B1).(string(~,"yyyyMMdd")+".xlsx")

3

=A2.(T(~))


4

>A3(1).run(MonthlyCumulativeSales=DailySales)

5

for A3.to(2,)

=A5.run(MonthlyCumulativeSales=DailySales+A3(#A5).select@1(Name==A5.Name).   MonthlyCumulativeSales)

6

=A3.run(T(A2(#),~))


 

24          汇总文件 - 插入汇总 sheet 页

 

商场有全年 12 个月的重点客户购买量汇总表,格式如下所示:

 

Jan.xlsx:

undefined

 

Feb.xlsx:

undefined

 

其它月份的数据这里略过……

 

现在需要把这些 Excel 文件汇总到一个文件的不同 sheet 中,且以文件名作为 sheet 名,并在首页插入汇总页面,取名为 Total。

 

汇总后的 Excel 如下所示:

undefined

 

SPL 实现代码:


A

B

B

1

[Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec]


2

=A1.(T(~+".xlsx"))



3

=A2.conj().groups(CustomerName;sum(Apple):Apple,

sum(Banana):Banana,sum(Peach):Peach,sum(Strawberry):Strawberry)

/汇总数据

4

=T("Total.xlsx",A3;"Total")


/将 T3 导出到 Excel 的第一个 sheet 页,命名为 Total

5

for A2

=file("Total.xlsx").xlsexport@at(A5;A1(#A5))

/将原始数据追加到 Excel 后续 Sheet 页并以文件名命名,@a 表示追加