​GROUPBY函数的几个典型应用

GROUPBY函数的几个典型应用

GROUPBY函数的作用是按指定字段进行聚合汇总，最终的效果类似于数据透视表。

先来看这个函数各个参数的作用：

GROUPBY(行标签,值字段,汇总的函数,[是否显示标题],[总计行的显示方式],[排序顺序],[筛选条件])

前面三个参数是必须的，后面几个参数可选。

接下来咱们用下面的数据举例，来看看这个函数的一些典型应用：

一：汇总各销售人员的销售总量

G2单元格输入以下公式即可：

=GROUPBY(B1:B201,D1:D201,SUM,3)

第一个参数B1:B201，表示要根据B列的销售人员进行汇总。

第二个参数D1:D201，是要汇总的数值区域。

第三个参数SUM，表示要汇总的方式是求和。

第四个参数使用3，表示显示字段标题。

二：汇总各销售人员的销售总量和销售总额

G2单元格输入以下公式即可：

=GROUPBY(B1:B201,D1:E201,SUM,3)

第三参数使用D1:E201，表示对D列和E列两个字段分别继续汇总。

三：汇总各销售人员不同产品规格的总量和总额

G2单元格输入以下公式即可：

=GROUPBY(B1:C201,D1:E201,SUM,3)

第一参数使用B1:C201，表示分别使用B列和C列两个字段作为汇总表中的行标签。

四：汇总各销售人员的销售总额和销售额平均值

G2单元格输入以下公式即可：

=GROUPBY(B1:B201,E1:E201,HSTACK(SUM,AVERAGE),,0)

第三参数聚合方式使用HSTACK函数将SUM和AVERAGE连接到一起，表示对第二参数分别执行求和以及平均值汇总。

根据需要，这里可以指定更多的聚合函数，比如以下写法，就表示分别执行求和、平均值和数值计数的汇总方式:

HSTACK(SUM,AVERAGE,COUNT)

五：汇总各销售人员的销售总量和销售额平均值

如果将第二参数设置成多列，第三参数使用HSTACK的方式，可对不同列分别执行不同的汇总方式。

例如以下公式，就是对D列的数量进行求和，同时对E列的销售额计算平均值。

=GROUPBY(B2:B201,D2:E201,HSTACK(SUM,AVERAGE))

六：生成带小计和总计的汇总表

如果第一参数选择两列或两列以上，还可以通过第五参数来设置显示总计和小计。

如下图所示，G2单元格公式为：

=GROUPBY(B1:C201,D1:E201,SUM,3,2)

第五参数使用2，表示同时显示总计和小计。

注意：如果第一参数仅选择了一列，这里设置成显示小计时，公式结果将返回错误值。

七：生成可排序的汇总表

第六参数用数字来指定对汇总表中的第几列进行升序或降序。

如下图所示，第六参数使用-3，表示对汇总表中的第三列进行排序，负数时为降序，正数时为升序。

八：按部门汇总人员姓名

除了数值计算，GROUPBY函数还可以对文本内容进行聚合。

如下图所示，使用以下公式，可按部门对人员姓名进行汇总。

=GROUPBY(A1:A20,B1:B20,ARRAYTOTEXT,,0)

第三参数ARRAYTOTEXT，表示将第二参数的数组转换成文本形式。

九、按条件筛选的汇总表

第七参数可以设置筛选条件，从而获得符合指定条件的汇总表。

如下图所示，使用以下公式，可对不同部门的男士姓名进行聚合。

=GROUPBY(A1:A20,B1:B20,ARRAYTOTEXT,3,0,,C1:C20="男")

十、孪生兄弟PIVOTBY函数

GROUPBY函数有一个孪生兄弟叫PIVOTBY，两个函数的功能大部分都是一致的，不同之处在于PIVOTBY函数可指定列标签。

如下图所示，使用以下公式可得到各部门不同性别的人数。

=PIVOTBY(A1:A20,C1:C20,C1:C20,COUNTA,3)

GROUPBY和PIVOTBY函数目前可以在Excel 365预览体验计划用户以及最新版的WPS表格中使用，快看看你的电脑能不能用？

好了，今天的内容就是这些，祝各位一天好心情~~

图文制作：祝洪忠