实例总结Excel中AGGREGATE函数的八个用法

wufei123 2024-05-24 阅读:33 评论:0
本篇文章给大家带来了关于excel的相关知识,其中主要介绍了关于aggregate函数的相关内容,该函数用法与subtotal函数类似,但在功能上比subtotal函数更加强大,下面一起来看一下,希望对大家有帮助。 相关学习推荐:exce...

本篇文章给大家带来了关于excel的相关知识,其中主要介绍了关于aggregate函数的相关内容,该函数用法与subtotal函数类似,但在功能上比subtotal函数更加强大,下面一起来看一下,希望对大家有帮助。

实例总结Excel中AGGREGATE函数的八个用法

相关学习推荐:excel教程

AGGREGATE函数用法与SUBTOTAL函数类似,但在功能上比SUBTOTAL函数更加强大,不仅可以实现诸如SUM、AVERAGE、COUNT、LARGE、MAX等19个函数的功能,而且还可以忽略隐藏行、错误值、空值等,并且支持常量数组。

该函数的第一参数是1到19之间的数字,用于指定要使用的汇总方式:

11.jpg

第二参数是介于0到7之间的数字,指定在计算区域内要忽略哪些类型的值:

12.jpg

接下来咱们就说说这个函数的一些典型用法:

1、多个不连续区域忽略错误值直接求和

这个函数的强大之处就是在于2参可以指定参数来忽略错误值直接统计

如下图,蓝色区域中包含有不同的错误值,现在要对这几个不连续的区域求和。

公式为:

=AGGREGATE(9,6,A3:A7,C3:C4,D6:F7)

13.jpg

2、筛选状态下忽略错误值

如下图,在筛选后的数据区域中包含有错误值,如何对可见单元格进行统计呢?

公式为:

=AGGREGATE(9,7,B6:B18)

14.jpg

第一参数使用9,表示求和,第二参数使用7,表示忽略隐藏行和错误值。

3、一个公式解决多种统计效果

如下图,A3:B14单元格区域中是筛选后的的数据,要分别统计在可见区域和所有数据的最大、最小、平均、总和、计数和中位数。

只要一个公式就够了:

=AGGREGATE({4;5;1;9;3;12},{5,0},B4:B14)

15.jpg

注意是区域数组公式,先选取c17:d22区域,然后在编辑栏写上公式,最后按ctrl+shift+enter三键录入。

4、向上求和你们都会,哪怕是筛选下的,向下呢?

=AGGREGATE(9,3,A4:A$18)*2-AGGREGATE(9,7,A4:A$18)

16.jpg

除了向下求和的方向外还有隐藏和错误值,这是subtotal+sum(if)都无法实现的统计效果

(录入方法是选取区域定位空值后编辑栏写完公式ctrl+enter批量填充)

5、这条开始才是重点-条件极值统计

这个函数提早五年就实现了2016才有的maxifs和minifs函数的统计效果,而且不需要三键。

如下图,要计算1车间对应的最小值,公式为:

=AGGREGATE(15,6,B4:B15/(A4:A15="1车间"),1)

17.jpg

公式中的第一参数使用15,表示使用SMALL函数,第二参数使用6,表示忽略错误值。要统计的区域是B4:B15/(A4:A15=”1车间”)

A4:A15=”1车间”部分,先对比A列的车间是不是等于指定的条件。如果A4:A15单元格区域中等于”1车间”,就返回逻辑值TRUE,否则返回逻辑值FALSE。然后再用B4:B15除以这组内存数组,结果为:

{70;69;87;77;55;46;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;19;47}

最后,AGGREGATE函数忽略里面的错误值,得到第一个最小值。

如果要计算1车间对应的第三个最小值,只需要将最后的1,变成3就好了。

如果要计算1车间对应的最大值,咱们可以修改一下第一参数,使用14,就是第k个最大值了。

6、 一对多查询

如果想要一对多查询,很多人想到的是INDEX+SAMLL+IF函数的三键客组合。其实,用aggregate函数替代也是能实现的。

如下图,要提取出二车间的所有工号,可以使用以下公式:

=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW($3:$12)/(A$3:A$12=D$3),ROW(A1))),"")

18.jpg

这个公式的思路和第五个公式基本相同。

7、统计同一单元格中的最大值

如下图,B列多人的考核情况被写到同一个单元格内,要统计其中的最大值。公式为:

=AGGREGATE(14,6,--MID(B4,ROW($1:$50),COLUMN(A:AZ)),1)

19.jpg

公式中的MID(B4,ROW($1:$50),COLUMN(A:AZ))部分,使用MID函数,依次从第1~50个字符处开始,各提取长度为1~50的字符串,得到一个巨长的内存数组。再使用两个负号,把内存数组中的文本变成错误值,数值仍然是其本身的值。

最后使用AGGREGATE函数,忽略内存数组中的错误值,计算出其中的第一个最小值。

8、同时统计指定条件的最大最小值

如下图所示,要同时统计1车间对应的最大和最小值。

先同时选中F4:G4单元格,编辑栏输入以下公式,按Ctrl+Shift+回车。

=AGGREGATE({16,15},6,B4:B15/(A4:A15=E4),1)

20.jpg

AGGREGATE第一参数使用常量数组{16,15},表示分别使用最大值和最小值的计算规则。

最终的结果也是一个内存数组,所以要同时选中两个单元格输入。

这个函数的特性在于第一参数为14~19时,可以使用第四参数,此时的第四参数是支持数组的,因此就能玩出各种应用,来替代不能直接忽略错误值的SMALL、LARGE等函数。

相关学习推荐:excel教程

以上就是实例总结Excel中AGGREGATE函数的八个用法的详细内容,更多请关注知识资源分享宝库其它相关文章!

版权声明

本站内容来源于互联网搬运,
仅限用于小范围内传播学习,请在下载后24小时内删除,
如果有侵权内容、不妥之处,请第一时间联系我们删除。敬请谅解!
E-mail:dpw1001@163.com

分享:

扫一扫在手机阅读、分享本文

发表评论
热门文章
  • BioWare埃德蒙顿工作室面临关闭危机,龙腾世纪制作总监辞职引关注(龙腾.总监.辞职.危机.面临.....)

    BioWare埃德蒙顿工作室面临关闭危机,龙腾世纪制作总监辞职引关注(龙腾.总监.辞职.危机.面临.....)
    知名变性人制作总监corrine busche离职bioware,引发业界震荡!外媒“smash jt”独家报道称,《龙腾世纪:影幢守护者》制作总监corrine busche已离开bioware,此举不仅引发了关于个人职业发展方向的讨论,更因其可能预示着bioware埃德蒙顿工作室即将关闭而备受关注。本文将深入分析busche离职的原因及其对bioware及游戏行业的影响。 Busche的告别信:挑战与感激并存 据“Smash JT”获得的内部邮件显示,Busche离职原...
  • 闪耀暖暖靡城永恒怎么样-闪耀暖暖靡城永恒套装介绍(闪耀.暖暖.套装.介绍.....)

    闪耀暖暖靡城永恒怎么样-闪耀暖暖靡城永恒套装介绍(闪耀.暖暖.套装.介绍.....)
    闪耀暖暖钻石竞技场第十七赛季“华梦泡影”即将开启!全新闪耀性感套装【靡城永恒】震撼来袭!想知道如何获得这套精美套装吗?快来看看吧! 【靡城永恒】套装设计理念抢先看: 设计灵感源于夜色中的孤星,象征着淡然、漠视一切的灰色瞳眸。设计师希望通过这套服装,展现出在虚幻与真实交织的夜幕下,一种独特的魅力。 服装细节考究,从面料的光泽、鞋跟声响到裙摆的弧度,都力求完美还原设计初衷。 【靡城永恒】套装设计亮点: 闪耀的绸缎与金丝交织,轻盈的羽毛增添华贵感。 这套服装仿佛是从无尽的黑...
  • python怎么调用其他文件函数

    python怎么调用其他文件函数
    在 python 中调用其他文件中的函数,有两种方式:1. 使用 import 语句导入模块,然后调用 [模块名].[函数名]();2. 使用 from ... import 语句从模块导入特定函数,然后调用 [函数名]()。 如何在 Python 中调用其他文件中的函数 在 Python 中,您可以通过以下两种方式调用其他文件中的函数: 1. 使用 import 语句 优点:简单且易于使用。 缺点:会将整个模块导入到当前作用域中,可能会导致命名空间混乱。 步骤:...
  • 斗魔骑士哪个角色强势-斗魔骑士角色推荐与实力解析(骑士.角色.强势.解析.实力.....)

    斗魔骑士哪个角色强势-斗魔骑士角色推荐与实力解析(骑士.角色.强势.解析.实力.....)
    斗魔骑士角色选择及战斗策略指南 斗魔骑士游戏中,众多角色各具特色,选择适合自己的角色才能在战斗中占据优势。本文将为您详细解读如何选择强力角色,并提供团队协作及角色培养策略。 如何选择强力角色? 斗魔骑士的角色大致分为近战和远程两种类型。近战角色通常拥有高攻击力和防御力,适合冲锋陷阵;远程角色则擅长后方输出,并依靠灵活走位躲避攻击。 选择角色时,需根据个人游戏风格和喜好决定。喜欢正面硬刚的玩家可以选择战士型角色,其高生命值和防御力能承受更多伤害;偏好策略性玩法的玩家则可以选择法...
  • 奇迹暖暖诸星梦眠怎么样-奇迹暖暖诸星梦眠套装介绍(星梦.暖暖.奇迹.套装.介绍.....)

    奇迹暖暖诸星梦眠怎么样-奇迹暖暖诸星梦眠套装介绍(星梦.暖暖.奇迹.套装.介绍.....)
    奇迹暖暖全新活动“失序之圜”即将开启,参与活动即可获得精美套装——诸星梦眠!想知道这套套装的细节吗?一起来看看吧! 奇迹暖暖诸星梦眠套装详解 “失序之圜”活动主打套装——诸星梦眠,高清海报震撼公开!少女在无垠梦境中,接受星辰的邀请,馥郁芬芳,预示着命运之花即将绽放。 诸星梦眠套装包含:全新妆容“隽永之梦”、星光面饰“熠烁星光”、动态特姿连衣裙“诸星梦眠”、动态特姿发型“金色绮想”、精美特效皇冠“繁星加冕”,以及动态摆件“芳馨酣眠”、“沉云余音”、“流星低语”、“葳蕤诗篇”。...