如何对两个 Excel 表实现各种类型的 JOIN

甘心为你

某Excel中的sheet分为3类，其中sheet A是基础表，部分数据如下：

A	B	C
1	interval1	interval2	interval3
2	1 hour	1 day	1 week
3	2 hours	2 days	2 weeks
4	3 hours	3 days	3 weeks
5	4 hours	4 days	4 weeks

Sheet B1\B2…Bn是关联表，它们的格式都一样，且与A有相同的列interval1、interval2、interval3。其中一个B的部分数据如下：

A	B	C	D	E	F	G
1	interval1	interval2	interval3	Type	value1	value2	value3
2	2 hours	1 day	7 week	circle	37	108.1	4.1
3	3 hours	3 days	7 weeks	Line	39	117.5	4.2
4	4 hours	4 days	7 weeks	Line	35	127	4.3

Sheet C用来描述A与B1\B2..Bn的Join类型，共3种，其中cross Join表示笛卡尔积；leftJoinBig表示左关联，关联列是interval1；leftJoinSmall也是左关联，关联列是interval1、interval2。部分数据如下：

A	B
1	table	joinType
2	B1	crossJoin
3	B2	leftJoinBig
4	B3	leftJoinSmall

计算目标：按照sheet C中的join类型将sheet A和B1\B2..Bn关联起来，从A中取interva1列，从B取其他列，最后形成n个Excel文件。
以上面的sheet B为例（实际上每个B应当不同），如果joinType==crossJoin，则关联结果应该是：

A	B	C	D	E	F	G
1	interval1	interval2	interval3	Type	value1	value2	value3
2	1 hour	1 day	7 week	Circle	37	108.1	4.1
3	1 hour	3 days	7 weeks	Line	39	117.5	4.2
4	1 hour	4 days	7 weeks	Line	35	127	4.3
5	2 hours	1 day	7 week	circle	37	108.1	4.1
6	2 hours	3 days	7 weeks	Line	39	117.5	4.2
7	2 hours	4 days	7 weeks	Line	35	127	4.3
8	3 hours	1 day	7 week	circle	37	108.1	4.1
9	3 hours	3 days	7 weeks	Line	39	117.5	4.2
10	3 hours	4 days	7 weeks	Line	35	127	4.3
11	4 hours	1 day	7 week	circle	37	108.1	4.1
12	4 hours	3 days	7 weeks	Line	39	117.5	4.2
13	4 hours	4 days	7 weeks	Line	35	127	4.3

如果joinType==leftJoinBig，则关联结果应该是：

A	B	C	D	E	F	G
1	interval1	interval2	interval3	Type	value1	value2	value3
2	1 hour
3	2 hours	1 day	7 week	circle	37	108.1	4.1
4	3 hours	3 days	7 weeks	line	39	117.5	4.2
5	4 hours	4 days	7 weeks	line	35	127	4.3

如果joinType==leftJoinSmall，则关联结果应该是：

A	B	C	D	E	F	G
1	interval1	interval2	interval3	type	value1	value2	value3
2	1 hour
3	2 hours
4	3 hours	3 days	7 weeks	line	39	117.5	4.2
5	4 hours	4 days	7 weeks	line	35	127	4.3

这个计算需要循环遍历sheet C，因此只能用脚本而不是公式来实现。Join属于结构化计算，VBA缺乏直接可用的函数，代码会非常繁琐。
实现步骤：
1. 运行集算器（可以到润乾官网下载，用职场版，首次运行时会提示加载授权，下载个免费的就够了）
2. 编写脚本并执行

A	B
1	=file("data.xlsx").xlsopen()
2	=A1.xlsimport@t(;"C")
3	=tableA=A1.xlsimport@t(;"A")
4	for A2	=tableB=A1.xlsimport@t(;A4.table)
5	=case(A4.joinType, "crossJoin",xjoin(tableA:A;tableB:B), "leftJoinBig",xjoin@1 (tableA:A;tableB:B,A.interval1==interval1), "leftJoinSmall",xjoin@1(tableA:A;tableB:B,A.interval1==interval1  && A.interval2==interval2))
6	=B5.new(A.interval1,B.interval2,B.interval3,B.type,B.value1,B.value2,B.value3)
7	=file(A4.table+A4.joinType+".xlsx").xlsexport@t(B6)

脚本函数 case 可对 Join 类型做分支判断，xjoin 算出笛卡尔积，@1 表示左关联。