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