Problem description & analysis:
Here below is a data table:
Task: We want to put every 4 rows in order in a group, and calculate the serial number of the group and the sum of the values in the second column of each group.
Solution:
Use SPL XLL and enter the following one-line code:
=spl("=E@b(?1).groups((#-1)\4+1:ID;sum(#2):Total)",A1:B12)
The results are as follows:
To put every 4 rows in a group, we can group by the result of (#-1)\4+1, that is, subtract 1 from row number # to get the difference, divide the difference by 4 to get the quotient, and add 1 to the quotient. After grouping, we can count the sum of the values in the second column of each group.
Download esProc Desktop for FREE and revolutionize your Excel workflow using SPL XLL!! 🚀✨⬇️
✨SPL download address: esProc Desktop FREE Download
✨Plugin Installation Method: SPL XLL Installation and Configuration
✨References to other rich Excel operation cases: Desktop and Excel Data Processing Cases
✨YouTube FREE courses: SPL Programming