Excel 特殊分组汇总示例

在 Excel 中分析数据时,往往会用到数据透视表。通过透视表,可以对数据从不同维度、不同类别来汇总和分析。但是透视表功能很简单,分组依据也很固化。需要将部分数据合并、分区间或者允许重复的特殊条件来分组汇总时便没法实现。针对 Excel 透视表的分组难点,本文列出一些常用的分组示例,解析难点并提供 SPL 解决代码。SPL 是专业计算引擎 esProc 使用的语言,用于处理结构化数据的运算非常方便。

一、常规完全分组

这里有一份 2019 年的产品销售表,表中记录了每个销售对某产品的销售明细。现在想要按销售员工统计出每人的销售总量,并报告出销售量的前三名予以嘉奖。

Excel 文件中数据如下:

..

期望结果:

..

使用 Excel 的透视表来做这种常规分析,能用但不好用。之所以说能用,是因为使用透视表毕竟还是可以将所有数据汇总出来,然后通过排序得出名次,再复制出前三名的数据得到期望结果。但这么多的手工操作,当数据量大,或者待保留的名次比较多时,使用起来就不那么方便。

SPL 能遵循自然思路,分步计算:


A

B

1

=clipboard().import@t()

/从剪贴板导入带标题的销售数据

2

=A1.groups(Sale;sum(Amount):Total)

/根据销售汇总,统计出每个销售的销售总量 Total

3

=A2.top(-3;Total)

/只取销售总量前三名

4

=A3.derive(#:Rank)

/增加一个排名字段

5

=clipboard(A4.export@t())

/将汇总结果放回到剪贴板

代码执行完成后,只需在Excel 中粘贴便可得到期望结果。

本文中 SPL 示例,都通过剪贴板跟 Excel 进行数据交互。首先从 Excel 中将要分析的数据复制到剪贴板,然后在集算器设计器里编辑 SPL 脚本,最后执行代码,并在 Excel 中粘贴计算结果。

二、并入“其它”

有时候,并不希望统计每一明细数据。而是只关注其中一部分,其余的全部合并。比如上例中按产品分组统计时,只关注每种水果的销量,其他的蔬菜则全部合并统计。

期望结果:

..

 

这种依赖具体数据的合并要求,使用透视表没法做出。只能去写 VBA 宏,用程序针对特定数据分组,麻烦且有一定难度。

SPL 可以采用具体枚举值来进行划分:


A

B

1

=clipboard().import@t()

/从剪贴板导入销售记录

2

=A1.align@an(["Orange","Grape","Apple","Banana","Strawberry"],Product)

/根据水果列表,将销售记录分组,并且使用 n 选项,将其他归为一组

3

=A2.new(Product,~.sum(Amount):Total)

/计算每组总销量,产生新的序表

4

=A3(A3.len()).Product="Others"

/将最后一项的其他产品名称改为 Others

5

=clipboard(A3.export@t())

/将结果导出后放置到剪贴板

执行结束后,在Excel 中粘贴,便可得到期望结果。

三、补充对齐

透视表只能根据数据做常规分组,对于空缺的数据,不会产生相应分组。比如对销量表按日期分组时,由于部分月份没有销售记录,因此使用透视表分组后得到的结果为:

..

其中 3 月等没有数据的月份,也没有相应分组,结果看起来不整齐,现在想要这样的结果:

..

这种需求,透视表仍然没法做到。写 VBA 代码实现的难度跟上一节类似。

 

SPL 可以使用条件分组,事先预置出分组类别:


A

B

1

=clipboard().import@t()

/从剪贴板导入销售记录

2

=A1.run(Date=date(Date,"yyyy/MM/dd"))

/将串型日期字段转换为日期类型

3

=A2.align@a(12,month(Date))

/将日期按照固定的 12 个月来分组

4

=A3.new(#:Month,~.sum(Amount):Total)

/统计分组后的数据,产生新的序表

5

=clipboard(A4.export@t())

/将结果导出后放置到剪贴板

 

四、按区间分组

如下为学生的考试成绩表,现在需要根据分数所属区间,比如 60 分以下评为 C,90 分以下评为 B,90 以上评为 A,然后按照区间评级统计各分数段的人次。

考生的成绩表:

..

期望结果:

..

按照成绩划分区间段来分组时,定义好值的划分区间后,只需用 pseg 函数便可以将分数分好段,然后统计出各段人次即可。

SPL 代码如下:


A

B

1

=clipboard().import@t()

/从剪贴板导入分数表

2

=[0,60,90]

/定义分数分段区间

3

=["C","B","A"]

/定义对应区间名称

4

=A1.align@a(A2.len(),A2.pseg(Score))

/算出成绩所在段,按照段分组

5

=A4.new(A3(#):Level,~.count():Count)

/统计各段人次,产生新序表

6

=clipboard(A5.export@t())

/将导出结果放置到剪贴板

 

五、可重叠分组

如下为 2019 年部分国家的 GDP 产值。现在想按发达国家,发展中国家以及金砖五国分类,计算一下各类的平均 GDP。

2019 年部分国家 GDP 数据 (单位:亿美元):

..

期望结果:

..

可以看到,其中的发展中国家跟金砖五国,是有重复数据的。像这种重复划分的需求,可以使用 enum 枚举函数,通过对数值的枚举,可以很自由地定义分组条件。

SPL 代码:


A

B

1

=clipboard().import@t()

/从剪贴板导入国家 GDP 产值表

2

["America","Japan","Germany","Britain","France","Italy","Canada","Korea","Australia"].pos(?)>0

/枚举出发达国家

3

["China","India","Mexico","SouthAfrica"].pos(?)>0

/枚举发展中国家

4

["Brazil","Russia","India","China","South   Africa"].pos(?)>0

/枚举金砖五国

5

=[A2:A4]

/构造枚举条件序列

6

=["Developed","Developing","BRICS"]

/枚举条件的对应名称

7

=A1.enum@r(A5,Country)

/使用枚举函数对国家分组,注意如果允许重复数值的分组,要带上选项 r

8

=A7.new(A6(#):Countries,~.avg(GDP):Average)

/对分组后的数值计算平均 GDP,并产生新序表

9

=clipboard(A8.export@t())

/将结果导出并放置到剪贴板

 

 

SPL Cookbook》中还有更多敏捷计算示例。