...

Convert every N columns into multiple rows

 

Example

An Excel file book1.xlsx starts from the second column, and every two columns is a pair (that is, the even and odd columns except the first column, such as the second and third columns). There are a total of 4 pairs of such columns. Details as follows:

Now you need to group according to the first column and all the even-numbered columns, and summarize the odd-numbered columns in the group. The result should be as follows:

Write SPL script:

A

1

=file("E:/work/book1.xlsx").xlsimport@w().to(2,)

2

=A1.news(~.len()\2;A1.~(1):Micro,A1.~(#*2):Group,A1.~(#*2+1):Series)

3

=A2.groups(Micro,Group;sum(Series):Series)

4

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

A1 Read in the book1.xlsx file, the option @w means read as the sequence of a sequence. After reading out, take out the second row to the last row.

A2 Expand each row of A1, and the number of rows expanded is the quotient of the number of members in this row divided by 2. In the expanded row, the first column of Micro is the first member of A1, the second column of Group is the #*2 member of A1, and the third column of Series is the #*2+1 member of A1, where # represents this The row number of the row expansion.

A3 Group A2 according to Micro and Group, and calculate the sum of Series as the Series column

A4 Save A3 to the file book2.xlsx

 
 •  311 View  •