分组并将某列合并成串
例题描述和简单分析
有Excel 文件 Book1.xlsx，数据如下所示：
Session |
FirstName |
LastName |
Address |
|
1 |
Bob |
Smith |
bob@1.com |
Main Street |
2 |
Bob |
Smith |
bob@1.com |
Main Street |
4 |
Bob |
Smith |
bob@1.com |
Main Street |
1 |
Frank |
Jones |
Frank@2.com |
2nd Street |
2 |
Frank |
Jones |
Frank@2.com |
2nd Street |
3 |
Frank |
Jones |
Frank@2.com |
2nd Street |
4 |
Frank |
Jones |
Frank@2.com |
2nd Street |
1 |
Henry |
Andrews |
henry@3.com |
3rd Street |
2 |
Henry |
Andrews |
henry@3.com |
3rd Street |
数据按每个人分组后，将Session 列用逗号分隔拼成串，结果如下所示：
FirstName |
LastName |
Address |
Session |
|
Frank@2.com |
Frank |
Jones |
2nd Street |
1,2,3,4 |
bob@1.com |
Bob |
Smith |
Main Street |
1,2,4 |
henry@3.com |
Henry |
Andrews |
3rd Street |
1,2 |
解法及简要说明
在集算器中编写脚本p1.dfx，如下所示：
A |
|
1 |
=file("Book1.xlsx").xlsimport@t() |
2 |
=A1.group(Email;FirstName,LastName,Address,~.(Session).concat@c():Session) |
3 |
=file("result.xlsx").xlsexport@t(A2) |
简要说明：
A1 Excel 读成序表
A2 按 Email 分组，保留除 Session 外的列，Session 列变为用逗号分隔拼成的串
A3 A2 的结果导出至 result.xlsx
https://stackoverflow.com/questions/63623624/merge-excel-rows-based-on-single-field
英文版