Excel Postgrouping Calculations
Excel supports grouping & aggregation on data, as well as offering pivot tables to perform multidimensional analysis. But Excel methods and operations are too simple to handle complicated tasks or manage them conveniently. Such tasks include getting ranks on each subset, and filtering and sorting by aggregates. In this article, we’ll give examples of postgrouping calculations, analyze it and provide SPL code solution. SPL (Structured Process Language) is the language used by esProc, the specialized data computing engine. It’s convenient to use for handling structured computations.
I Intragrouping sorting
Below is the student scores table. We want to get rankings for each subject. To do this we need to first group records by subject and then perform sorting on each group.
Below is the Excel data:
The expected result:
A SPL script interacts with Excel through clipboard. We edit an SPL script in esProc designer, paste the tobeanalyzed Excel data to the clipboard, execute the script, and then paste result to Excel.
SPL provides stepwise coding in an easy way:
A 
B 

1 
=clipboard().import@t() 
/ Import student scores table with column headers 
2 
=A1.group(Subject) 
/ Group records by Subject 
3 
=A2.(~.sort(Score:1)) 
/ Sort each group by score in ascending order 
4 
=A3.(~.derive(#:Rank)) 
/ Add a Rank field for each group of records 
5 
=A4.conj() 
/ Concatenate groups of sorted records 
6 
=A5.new(Subject,Rank,Score,Name) 
/ Create a new table sequence with fields of different orders 
7 
=clipboard(A6.export@t()) 
/ Paste the sorting result to clipboard 
Then we just paste the result to Excel to get the desired table.
To get records containing only the top 3 scores only for each subject, we just use top function to get the eligible records from each group.
The expected result:
SPL uses top function to get records of top 3 scores:
A 
B 

1 
=clipboard().import@t() 
/ Import student scores table with column headers 
2 
=A1.group(Subject) 
/ Group records by Subject 
3 
=A2.(~.top(3;Score)) 
/ Get the top 3 scores by Score from each group 
4 
=A3.(~.derive(#:Rank)) 
/ Add a Rank field for each group of records 
5 
=A4.conj() 
/ Concatenate groups of sorted records 
6 
=A5.new(Subject,Rank,Score,Name) 
/ Create a new table sequence with fields of different orders 
7 
=clipboard(A6.export@t()) 
/ Paste the result to clipboard 
Then we just paste the result to Excel to get the desired table.
II Sorting by subaggregate
Another scenario is to sort groups according to the subaggregates, such as ranking students by their total scores based on the student scores table. The expected result is as follows:
To do this, SPL uses groups function to group records while calculating total for each group:
A 
B 

1 
=clipboard().import@t() 
/ Import student scores table with column headers 
2 
=A1.groups(Name;sum(Score):Total) 
/ Group records by name and calculate each student’s total score 
3 
=A2.sort(Total:1) 
/ Sort total scores in descending order 
4 
=A3.derive(#:Rank) 
/ Add a Rank field 
5 
=clipboard(A4.export@t()) 
/ Export the sorting result and paste it to clipboard 
III Intragroup filtering
This time we want to find students whose performances are not satisfactory. Since exams of subjects are different in difficulty levels, it’s unsuitable to get them by Fail. Instead, we need to find those whose scores are below the average for each subject.
The expected result:
So we group records by subject, calculate the average of each subject, and find students whose scores are below the average.
SPL script:
A 
B 

1 
=clipboard().import@t() 
/ Import student scores table with column headers 
2 
=A1.group(Subject) 
/ Group records by Subject 
3 
=A2.((AVG=~.avg(Score),~.select(Score<AVG))) 
/ Calculate average of each group, and find students whose scores are below the average 
4 
=A3.conj() 
/ Concatenate records of eligible students 
5 
=clipboard(A4.export@t()) 
/ Export the result and paste it to clipboard 
IV Filtering by subaggregate
In another scenario, we want to group records by name, calculate total of each group, and find students whose total scores are below the average total.
The expected result:
To perform an operation, filtering here, over the subtotals, we use groups function to group records, calculate the subtotals, get the average total and find the eligible students.
SPL script:
A 
B 

1 
=clipboard().import@t() 
/ Import student scores table with column headers 
2 
=A1.groups(Name;sum(Score):Total) 
/ Group records by name and calculate total score for each student 
3 
=A2.select(Total<A2.avg(Total)) 
/ Calculate average total score and find students whose scores are below the average 
4 
=clipboard(A3.export@t()) 
/ Export the result and paste it to clipboard 
V Intragroup percentage calculation
The following Excel sheet stores GDP values of some countries in 2019. The task is to group records by continent and calculate the percentage of each country’s GDP in its continent.
Below is the 2019 GDP table (Unit: $billion):
The expected result:
We can group records by Area, sum the total GDP of each continent, and then calculate the percentage of each country’s GDP in every group.
SPL script:
A 
B 

1 
=clipboard().import@t() 
/ Import 2019 GDP table with column headers from clipboard 
2 
=A1.group(Area) 
/ Group records by Area 
3 
=A2.((SUM=~.sum(GDP),~.derive(string(GDP/SUM,"0.00%"):Percentage))) 
/ Sum the total GDP of each group and calculate percentage of each country in it 
4 
=A3.conj() 
/ Concatenate result records of all groups 
5 
=clipboard(A4.export@t()) 
/ Export the result and paste it to clipboard 
VI Calculating percentage of each group
Let’s look at how to calculate the percentage of each continent’s GDP in the global total.
The expected result:
Here we also use groups function to group records, calculate GDP of each continent, and then get the percentage of each continent’s GDP in the global total.
SPL script:
A 
B 

1 
=clipboard().import@t() 
/ Import 2019 GDP table with column headers from clipboard 
2 
=A1.groups(Area;sum(GDP):Total) 
/ Group records by Area and calculate total GDP of each continent 
3 
=A2.derive(string(Total/A2.sum(Total),"0.00%"):Percentage) 
/ Calculate percentage of each continent’s GDP in the global total 
4 
=A3.run(Total=string(Total,"0.00")) 
/ Adjust the display accuracy of Total value 
5 
=clipboard(A4.export@t()) 
/ Export the result and paste it to clipboard 
Read Excel Grouping & Aggregation Examples to learn more analyses and solutions about Excel grouping & aggregation.
Find more agile computing examples in SPL Cookbook.
Chinese version