...

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


 
 •  387 View  •