Summarize Data by Column Direction
Example
There is an Excel file Book1.xlsx, and the data is as follows:
class |
name |
Math |
English |
PE |
one |
Jack |
89 |
78 |
83 |
Tom |
90 |
60 |
99 |
|
Jerry |
76 |
88 |
62 |
|
Kate |
66 |
90 |
85 |
|
two |
Jim |
87 |
60 |
76 |
Alice |
100 |
99 |
97 |
|
Rebecca |
75 |
76 |
88 |
|
three |
Cindy |
63 |
80 |
72 |
Kitty |
82 |
50 |
74 |
|
Lucy |
40 |
100 |
63 |
Based on a certain test score table of three classes, sum up the number of students in each class and the total scores of all subjects (including mathematics, English, and PE):
class |
num |
total |
one |
4 |
966 |
two |
3 |
758 |
three |
3 |
624 |
Write SPL script:
Method 1: Structured data
A |
|
1 |
=file("Book1.xlsx").xlsimport@t() |
2 |
=A1.groups@i(class;count(1):num,sum(~.array().to(3,).sum()):total) |
3 |
=file("result.xls").xlsexport@t(A2) |
A1 Read the Excel data
A2 Group the data by class, and calculate the number of students in each class as “num” and the total score of all subjects (from the 3rd to 5th columns) as “total”, where ${to(3,5).("#"/~).concat("+")} is used to concatenate a string: #3+#4+#5
A3 Export the result A2 to result.xlsx
Method 2: Two-dimensional array
A |
|
1 |
=file("Book1.xlsx").xlsimport@w(;,2:) |
2 |
=A1.groups@i(~(1):calss;count(1):num,sum(~.to(3,).sum()):total) |
3 |
=file("result.xls").xlsexport@t(A2) |
A1 Read the Excel data, and read the data as a two-dimensional array starting from the second row
A2 Group the data by class (the first column), calculate the number of students in each class as “num” and the total scores of all subjects (from the third column) as “total”
A3 Export the result A2 to result.xlsx
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc_Desktop/
SPL Learning Material 👉 https://c.scudata.com
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProcDesktop
Linkedin Group 👉 https://www.linkedin.com/groups/14419406/
