在同类数据的首行拼上汇总信息
举例
有 Excel 文件 Book1.xlsx,数据如下所示:
A |
B |
C |
D |
E |
result |
ASDF |
ISO9001 |
zxcv |
TYUI |
USA |
|
ASDF |
ISO9001 |
zxcv |
TYUI |
USA |
|
ASDF |
ISO9001 |
zxcv |
TYUI |
USA |
|
ASDF |
ISO9001 |
zxcv |
TYUI |
USA |
|
ASDF |
ISO9001 |
zxcv |
TYUI |
USA |
|
ASDF |
ISO9002 |
zxcv |
TYUI |
USA |
|
ASDF |
ISO9002 |
zxcv |
TYUI |
USA |
|
ASDF |
ISO9002 |
zxcv |
TYUI |
USA |
|
ASDF |
ISO9003 |
zxcv |
QWER |
USA |
|
ASDF |
ISO9003 |
zxcv |
QWER |
USA |
B列有序,按 B 列分组,每组的第 1 个 result 填上该组的计数值,结果如下:
A |
B |
C |
D |
E |
result |
ASDF |
ISO9001 |
zxcv |
TYUI |
USA |
5 |
ASDF |
ISO9001 |
zxcv |
TYUI |
USA |
|
ASDF |
ISO9001 |
zxcv |
TYUI |
USA |
|
ASDF |
ISO9001 |
zxcv |
TYUI |
USA |
|
ASDF |
ISO9001 |
zxcv |
TYUI |
USA |
|
ASDF |
ISO9002 |
zxcv |
TYUI |
USA |
3 |
ASDF |
ISO9002 |
zxcv |
TYUI |
USA |
|
ASDF |
ISO9002 |
zxcv |
TYUI |
USA |
|
ASDF |
ISO9003 |
zxcv |
QWER |
USA |
2 |
ASDF |
ISO9003 |
zxcv |
QWER |
USA |
编写 SPL 脚本:
A |
|
1 |
=file("Book1.xlsx").xlsimport@t() |
2 |
=A1.group@o(B).run(~(1).result=~.count()) |
3 |
=file("result.xlsx").xlsexport@t(A1) |
A1 读取 Excel 数据
A2 按 B 列分组,将每组的计数值赋予组内的一个 result
A3 将结果 A1 导出至 result.xlsx
英文已更新