Excel 分组后计算
Excel 可以对数据进行分组汇总，也可以用透视表对数据进行多层次分析，但提供的汇总及相关运算都很简单，稍复杂一些的运算没法实现或者很难实现了。比如我们希望计算分组内的排名，按分组汇总值再过滤与排序等。本文将列出这类分组后计算示例，解答并给出 SPL 解决代码。SPL 是专业计算引擎 esProc 使用的语言，用于处理结构化数据的运算非常方便。
一、组内排序
这里以学生的考试成绩表为例，现在需要对每门课程进行单科排名。显然这时需要先按课程分组，然后再在组内进行排序。
Excel 文件中数据如下：
期望结果：
本文中 SPL 示例，都通过剪贴板跟 Excel 进行数据交互。在集算器设计器里编辑好 SPL 脚本后，需要先从 Excel 中将要分析的数据复制到剪贴板，然后才执行代码，执行完成后，再到 Excel 中粘贴结果。
使用 SPL 能分步计算，步骤也很简单：
A |
B |
|
1 |
=clipboard().import@t() |
/从剪贴板导入带标题的考试成绩表 |
2 |
=A1.group(Subject) |
/先按科目将成绩分组 |
3 |
=A2.(~.sort(Score:-1)) |
/然后对每一组的数据按成绩升序排列 |
4 |
=A3.(~.derive(#:Rank)) |
/为每组数据增加一个名次字段 |
5 |
=A4.conj() |
/合并排好序后的各组成绩 |
6 |
=A5.new(Subject,Rank,Score,Name) |
/用新序表调整下字段顺序 |
7 |
=clipboard(A6.export@t()) |
/将排名结果放回到剪贴板 |
代码执行完成后，只需在Excel 中粘贴便可得到期望结果。
如果仅希望筛选出每门课程的前三名，则只需使用 top 函数在上述分组后的数据中，取出前三名即可。
期望结果：
SPL 采用 top 函数取出前三名：
A |
B |
|
1 |
=clipboard().import@t() |
/从剪贴板导入带标题的考试成绩表 |
2 |
=A1.group(Subject) |
/先按科目将成绩分组 |
3 |
=A2.(~.top(-3;Score)) |
/直接针对每组数据，根据 Score 过滤出前三名 |
4 |
=A3.(~.derive(#:Rank)) |
/为每组数据增加一个名次字段 |
5 |
=A4.conj() |
/合并排好序后的各组成绩 |
6 |
=A5.new(Subject,Rank,Score,Name) |
/用新序表调整下字段顺序 |
7 |
=clipboard(A6.export@t()) |
/将考生名单放回到剪贴板 |
执行结束后，在Excel 中粘贴，便可得到期望结果。
二、组后排序
上一节是分组后，对分组内的数据进行各自的排序。现在看一下分组后，如何对整个组的汇总数据来排序。仍以上述学生的考试成绩表为例，但现在想对同学的总分进行一个排名，期望结果为：
根据分组后的总分来排序时，需要使用 groups 函数，分组的同时计算出总分：
A |
B |
|
1 |
=clipboard().import@t() |
/从剪贴板导入带标题的考试成绩表 |
2 |
=A1.groups(Name;sum(Score):Total) |
/按姓名分组，同时计算出每个同学的总分 Total |
3 |
=A2.sort(Total:-1) |
/对总分按降序排序 |
4 |
=A3.derive(#:Rank) |
/增加一个名次字段 |
5 |
=clipboard(A4.export@t()) |
/排序后的结果导出后放置到剪贴板 |
三、组内过滤
现在需要将成绩落后的学生找出来，给他们补补课。由于各科考试题目的难易不同，所以不适合都按不及格的条件选出。而是根据每门课程的平均分，将平均分以下的同学找出来。
期望结果：
此时需要分组后，先算出每一门课程的平均分，然后再将在平均分以下的同学选出来。
SPL 代码如下：
A |
B |
|
1 |
=clipboard().import@t() |
/从剪贴板导入带标题的考试成绩表 |
2 |
=A1.group(Subject) |
/先按科目将成绩分组 |
3 |
=A2.((AVG=~.avg(Score),~.select(Score<AVG))) |
/对每组数据，先算出平均分，再选出在平均分以下的同学 |
4 |
=A3.conj() |
/合并每门课程下的需要补课的同学名单 |
5 |
=clipboard(A4.export@t()) |
/将学生名单放回到剪贴板 |
四、组后过滤
上一节是找出每门课程中低于平均分的同学。现在按照姓名分组，计算出每个同学的总分后，再将总分平均分以下的同学找出来。
期望结果：
由于同样是对分组后的总分来过滤，仍然采用 groups 函数，分组并计算出总分，再按总分求平均以及过滤。
SPL 代码如下：
A |
B |
|
1 |
=clipboard().import@t() |
/从剪贴板导入带标题的考试成绩表 |
2 |
=A1.groups(Name;sum(Score):Total) |
/按姓名分组，并计算总分 |
3 |
=A2.select(Total<A2.avg(Total)) |
/根据总分求出平均分，然后选出总分低于平均分的同学 |
4 |
=clipboard(A3.export@t()) |
/将结果导出并放置到剪贴板 |
五、组内占比
如下为 2019 年部分国家的 GDP 产值。现在想按地区，也即各大洲分组，然后计算出各国占所在洲的 GDP 百分比。
2019 年部分国家 GDP 数据 (单位：亿美元)：
期望结果：
计算组内的占比，同样是按地区分组后，先算出每个洲的 GDP 总量，再依次计算组内的各成员国的百分比占比。
SPL 代码：
A |
B |
|
1 |
=clipboard().import@t() |
/从剪贴板导入带标题的考试成绩表 |
2 |
=A1.group(Area) |
/按地区将各国分组 |
3 |
=A2.((SUM=~.sum(GDP),~.derive(string(GDP/SUM,"0.00%"):Percentage))) |
/对每组数据，先算出 GDP 总量，再计算各自的百分比 |
4 |
=A3.conj() |
/合并所有组内数据 |
5 |
=clipboard(A4.export@t()) |
/将结果导出后放回到剪贴板 |
六、组后占比
这一节再来看一下如何计算分组后的各大洲的全球占比。
期望结果：
分组后的计算都类似，仍然采用 groups 函数，分组并同时计算出各洲的 GDP 总量，然后再求一遍各洲的 GDP 总量占全球 GDP 的百分比值。
SPL 代码如下：
A |
B |
|
1 |
=clipboard().import@t() |
/从剪贴板导入带标题的考试成绩表 |
2 |
=A1.groups(Area;sum(GDP):Total) |
/按地区分组，并计算各大洲的 GDP 总值 |
3 |
=A2.derive(string(Total/A2.sum(Total),"0.00%"):Percentage) |
/再计算各洲的总量占全球 GDP 的百分比 |
4 |
=A3.run(Total=string(Total,"0.00")) |
/调整下 Total 的显示精度 |
5 |
=clipboard(A4.export@t()) |
/将结果导出并放置到剪贴板 |
更多 Excel 的分组分析，请参考《Excel 特殊分组汇总示例》。
另外《SPL Cookbook》中还有更多敏捷计算示例。
英文版