...

Group Every N Members

 

Example1

Part of the data in Excel file book1.xlsx is shown in the figure below:

..

The task is to divide every four rows into one group, calculate the group number and the sum of the second column in each group, and some results are shown in the following figure:

..

Write SPL script:

A

1

=T("E:/work/book1.xlsx")

2

=A1.groups((#-1)\4+1;sum(#2))

3

=T("E:/work/book2.xlsx",A2)

A1 Read the data of book1.xlsx

A2 Group the data by (#-1)\4+1, that is, the row number # minus 1 and is divided by 4, then add 1 to that quotient, so that every 4 rows are divided into groups. Count the sum of the second column in each group

A3 Store A2 to book2.xlsx

 

Example2

There is an Excel file book1.xlsx, and the data is as follows:

..

The task is to split the text string in A1 by commas, and format it into a table of 5 columns and N rows with table headers, first by row and then by column. The result is as follows:

..

Write SPL script:

A

1

=clipboard().split@c()

2

=A1.group((#-1)\5).concat@n("\t")

3

=clipboard(A2)

A1 Read the text string on the clipboard and split it into sequences with commas 

A2 According to the quotient of sequence number # minus 1 and then divided by 5, divide every 5 numbers into a group. Then use \n to concatenate each group, and use \t to concatenate the numbers in the group to form text strings

A3 Put the text strings in A2 into the clipboard 

Open the book1.xlsx file first, select cell A1, press Ctrl+c to copy, and then run the SPL script. Return to Excel after the run is over, click cell B2, and press Ctrl+v to paste the result.


 
 •  291 View  •