专栏名称: Excel之家ExcelHome
excel技巧原创教程每日推送,excel表格职场模板干货仓库,图文/视频/动画等多种教学方式分享excel操作技巧教程/excel函数公式教程/excel数据透视表教程/excel图表教程/Word教程,助您轻松提高办公效率!
目录
相关文章推荐
Excel之家ExcelHome  ·  6个Excel序号技巧,新手必会 ·  昨天  
Excel之家ExcelHome  ·  Excel算年龄,DATEDIF会不会? ·  2 天前  
完美Excel  ·  使用deepseek自动处理Excel工作表 ·  3 天前  
Excel之家ExcelHome  ·  几个常用Excel公式,简单又高效 ·  6 天前  
Excel之家ExcelHome  ·  因为精通Excel,我年纪轻轻就月入三千 ·  5 天前  
51好读  ›  专栏  ›  Excel之家ExcelHome

手搓透视表,用GROUPBY函数就够了

Excel之家ExcelHome  · 公众号  · Excel  · 2024-11-16 06:45

正文

GROUPBY函数的作用是按指定字段进行聚合汇总,最终的效果类似于数据透视表。目前可以在最新版的WPS表格或者Excel 365中使用。

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

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函数还可以对文本内容进行聚合。

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






请到「今天看啥」查看全文