# 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)

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