比对两个行式工作表的差异

例题描述和简单分析

文件data.xlsx2个行式sheet,两者格式相同,A列都是逻辑主键,Sheet2的数据比Sheet1新。具体如下:

Sheet1


A

B

1

A1

AV1

2

A2

AV2

3

A3

AV3

4

A4

AV4

5

A5

AV5

Sheet2


A

B

1

A1

AV100

2

A2

AV2

3

A3

AV3

4

A4

AV4

5

A6

AV6

现在要找到sheet2sheet1的差异,并分别写入新的sheet。其中sheet added存储所有新增的数据,即:


A

B

1

A6

AV6

sheet removed存储所有删掉的数据,即:


A

B

1

A5

AV5

sheet modified存储所有修改(主键不变,其他列变化)的记录,即:


A

B

1

A1

AV100

上述算法涉及记录的归并和集合运算

解法及简要说明

编写下面的集算器脚本,并执行:


A

B

C

1

=file("050/data.xlsx")



2

=A1.xlsopen()



3

=A2.xlsimport(;"Sheet1")

=A2.xlsimport(;"Sheet2")


4

=[B3,A3].merge@od(_1)

=[A3,B3].merge@od(_1)

=[[B3,A3].merge@od(), A4].merge@od()

5

=A2.xlsexport(A4;"added")

=A2.xlsexport(B4;"removed")

=A2.xlsexport(C4;"modified")

6

=A1.xlswrite(A2)



A1-B3A5-C6:在Excel文件中读取/写入sheet

A4:通过比对第1列,对B3A3进行差集运算,从而算出新增的数据。函数merge实现归并运算,@o表示对内存数据进行不排序归并,@d表示归并中计算差集。

B4:算出删除的数据。

C4:先通过比对整行,算出新增或修改的数据。再将该中间结果与A4比对整行,算出修改的数据。由于第二步比对的是同一个序表中的数据,因此可以直接用集合差集进行计算,即:=[B3,A3].merge@od() \ A4

问答搜集

https://stackoverflow.com/questions/63841318/comparing-column-a-to-b-and-b-to-a-and-copy-entire-row-of-missing-and-added-to-n