Grouping by Empty or NonEmpty Rows
1. Empty row
There is an Excel file book1.xlsx, and the data is as follows:
The data in column A consists of multiple segments, and each segment is composed of continuous N rows + 1 empty row. The task is to calculate the sum of each segment at the empty rows in column B, and other rows in column B remain empty. The results are as follows:
Write SPL script:
A 

1 
=clipboard().split("\n") 
2 
=A1.group@i(~[1]=="") 
3 
=A2.([""]*(~.len()1)~.sum()).conj() 
4 
=clipboard(A3.concat("\n")) 
A1 Read the data from the clipboard and use the carriage return character to split it into a sequence
A2 When the previous member is an empty string, create a new group
A3 Loop through each group, calculate the sum of the numbers in each group and fill in (number of members1) empty stings in front
A4 Concatenate the sequence in A3 to a string with \n and put it into the clipboard
Open book1.xlsx, select all valid data in column A, copy with Ctrl+c, and then run the SPL script. After running, return to Excel, click cell B1, and paste the result with Ctrl+v.
2. Nonempty row
The Excel file book1.xlsx has monthly water consumption data for each year. Only the “Year” before the first “Month” of each year is filled in. Part of the data is as follows:
The task is to calculate the total water consumption each year, and some of the results are as follows:
Write SPL script:
A 

1 
=T("e:/water/book1.xlsx") 
2 
=A1.group@i(Year!=null).new(Year,~.sum(Water):Total_Water) 
3 
=T("e:/water/book2.xlsx",A2) 
A1 Read the data of book1.xlsx
A2 Divide the records into a new group when the Year field is not empty. The @i option means generating a new group when the condition is met. Then create a new data set, take out the Year field, and the total water consumption in each year is the Total_Water field
A3 Store the results in A2 to book2.xlsx