ITPub博客

首页 > 应用开发 > IT综合 > Excel 分组后计算

Excel 分组后计算

IT综合 作者:rqgxy 时间:2020-11-09 14:16:53 0 删除 编辑

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》中还有更多敏捷计算示例。


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31555597/viewspace-2732998/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2018-09-20

  • 博文量
    405
  • 访问量
    194272