• 官方平台
  • 微信微信
  • 微博微博
400-6599-650
400-6599-650
上海来亿财富投资管理有限公司
首页 » 综合资讯

来亿率先引入国外先进信用管理理念

这个函数牛到不行了,7.53%的人竟然还不会用

时间 : 2019-06-24服务:综合资讯

小伙伴们好啊,今天介绍一个还有7.53%的人不认识的函数:AGGREGATE。 AGGREGATE函数用法与SUBTOTAL函数类似,但在功能上比SUBTOTAL函数更加强大,不仅可以实现诸如SUM、AVERAGE、COUNT、LARGE、MAX等19个函数的功能,而且还可以忽略隐藏行、错误值、空值等,并且支持常量数组。 该函数的第一参数是1到19之间的数字,用于指定要使用的汇总方式: 第二参数是介于0到7之间的数字,指定在计算区域内要忽略哪些类型的值: 接下来咱们就说说这个函数的一些典型用法: 1,多个不连续区域忽略错误值直接求和 这个函数的强大之处就是在于2参可以指定参数来忽略错误值直接统计。 如下图,蓝色区域中包含有不同的错误值,现在要对这几个不连续的区域求和。 公式为: =AGGREGATE(9,6,A3:A7,C3:C4,D6:F7) 2,筛选状态下忽略错误值 如下图,在筛选后的数据区域中包含有错误值,如何对可见单元格进行统计呢? 公式为: =AGGREGATE(9,7,B6:B18) 第一参数使用9,表示求和,第二参数使用7,表示忽略隐藏行和错误值。 3,一个公式解决多种统计效果 如下图,A3:B14单元格区域中是筛选后的的数据,要分别统计在可见区域和所有数据的最大、最小、平均、总和、计数和中位数。 只要一个公式就够了: =AGGREGATE({4;5;1;9;3;12},{5,0},B4:B14) 注意是区域数组公式,先选取c17:d22区域,然后在编辑栏写上公式,最后按ctrl+shift+enter三键录入。 4,向上求和你们都会,哪怕是筛选下的,向下呢? =AGGREGATE(9,3,A4:A$18)*2-AGGREGATE(9,7,A4:A$18) 除了向下求和的方向外还有隐藏和错误值,这是subtotal+sum(if)都无法实现的统计效果 (录入方法是选取区域定位空值后编辑栏写完公式ctrl+enter批量填充) 5,这条开始才是重点-条件极值统计 这个函数提早五年就实现了2016才有的maxifs和minifs函数的统计效果,而且不需要三键。 如下图,要计算1车间对应的最小值,公式为: =AGGREGATE(15,6,B4:B15/(A4:A15=”1车间”),1) 公式中的第一参数使用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))),””) 这个公式的思路和第五个公式基本相同。 7,统计同一单元格中的最大值 如下图,B列多人的考核情况被写到同一个单元格内,要统计其中的最大值。公式为: =AGGREGATE(14,6,–MID(B4,ROW($1:$50),COLUMN(A:AZ)),1) 公式中的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) AGGREGATE第一参数使用常量数组{16,15},表示分别使用最大值和最小值的计算规则。 最终的结果也是一个内存数组,所以要同时选中两个单元格输入。 这个函数的特性在于第一参数为14~19时,可以使用第四参数,此时的第四参数是支持数组的,因此就能玩出各种应用,来替代不能直接忽略错误值的small、large等函数。 当然,这函数有一点是无法替代subtotal的,因为aggregate不支持多维引用,所以目前subtotal还是无法完全替代的,这个咱们下次再聊。 今天的练习文件在此: 链接: https://pan.baidu.com/s/1hOJgByy-IdALhGoHBNLgRw  提取码: mu5n —转载于微信公众号”Excel之家ExcelHome(作者:流浪铁匠)” 关于我们 —上海来亿创业投资服务中心:投融资服务,内资、外资公司注册,自贸区公司注册,代理记账,法律咨询; —来电咨询热线:400-6588-650、400-6599-650 ; —电话微信同步:18501671702; —微信公众号:来亿创投;

相关服务 /RELATED PROJECTS

  • 金融服务

    金融理财 项目融资
    股票/期货配置 上市辅导

    查看详情
  • 商务服务

    公司注册 财务代理
    法务咨询 税收筹划

    查看详情
  • 生态科技

    植物工厂/高科技农业/智慧农
    业的规划 设计 建设 运营托管

    查看详情
X 热线咨询