全能王函数aggregate

流浪铁匠

【题外话，这篇是我在EH公众号投稿的原文文件，公众号上的文章祝老师嫌难了略有删减（吐血），而且由于文章里我的原文的一个备注，其中一个案例的参数在实际文章里被祝老师略微修改引起读者质疑，因此这里顺道解释下】

当时考虑是微信文章的投稿，故篇幅问题这函数介绍得不算详细
这里先解释下这函数的3个优越性质：
1，可以忽略内存数组里的错误值
2，在很多情况下可以使数组运算公式且不需要三键!
3，多种统计效果（这函数1/2参数统计方式过多请自行使用F1查看）

应用案例
1,多个不连续区域忽略错误值直接求和
这个函数的强大之处就是在于2参可以指定参数来忽略错误值直接统计


2,筛选状态下忽略错误值直接统计

流浪铁匠

3,  1/2参数配合下实现1个公式解决多种统计效果（区域数组公式）


区域数组公式,要选取完c17:d22区域后在编辑栏写完公式ctrl+shift+enter三键录入


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


除了向下求和的方向外还有隐藏和错误值,这是subtotal+sum(if)都无法实现的统计效果
(录入方法是选取区域定位空值后编辑栏写完公式ctrl+enter批量填充)

5,这条开始才是重点-条件极值统计
这函数的出现提早了2个版本就实现了maxifs和minifs(2016新增函数)的统计效果而且不需要三键(条件第n小其实minifs都不方便实现)



6,不重复的第n大和第n小
又是个借助aggregate性能不三键实现的公式,如果看完上面的例子,会知道这里要统计不重复的第n大的话把1参数改为14即可

7,众所周知,万金油组合在2019被filter函数秒杀
但2010-2016阶段想要不三键的一对多公式，万金油组合用aggregate函数替代下也是能实现的



8,内存数组忽略错误值统计最大值



这题如果用max，除了三键外通常要配合text将内存数组的文本内容强制转化为0，避免 - -的减负运算转数值时产生的错误值
aggregate比较暴力,直接忽略错误值取最大值了

流浪铁匠

9,多种组合解决条件双极值问题
一题多解,错了,是一个函数的多解:同时求条件对应的最大最小值,
如果分开用max/min+if不提,函数里有所谓四大金刚,因为这4个函数可以利用2参数k值的变化来分别同时统计最大值和最小值
然而aggregate可以实现这4个函数的全部原理解法,并有着自己的参数性质才能实现的第5个解法，堪称四大金刚之五



关于最后这个案例,原文里解法5备注14可以改为16，而祝老师在文章里也使用了16
被读者质疑要改回14，但这其中只是不同参数下对应函数不同罢了
14对应larege
而16对应percentile函数，这是百分比值函数，此时k值为1同样取对应的条件最大值如果使用17,对应四分位函数quartile，此时若求对应最大值，k值需要使用4

这个函数，其特性就在于1参数为14-19范围时存在4参数k值，此时3参数是支持数组的，
此时就能玩出各种应用来替代不能直接忽略错误值的small/large等函数
也能替代不支持数组的subtotal函数
但缺陷也在这里，这函数只有1参数为14-19时，3参数才支持数组，这严重影响了这个函数的应用范围
(我一直有句戏言"请支持aggregate函数全参数支持数组法案"，就是这个原因，如果真的能全参数支持数组，那么基本没sum，max这些函数的事了)

当然,这函数有1点是无法替代subtotal的,因为aggregate不支持多维引用
筛选状态下涉及多维引用的问题，目前subtotal还是无法替代的，这个下次再聊

sucx007

谢谢老铁，学习了

liu2336904

先标记一下，慢慢学习

1055751654

标记一下，试一下是不是就不卡了

朝花夕拾2018

果然功能强大。

kuangben8

感谢分享！学习了。不过2016版本里有maxifs函数吗？好像没有啊。

流浪铁匠

kuangben8 发表于 2019-6-13 17:15
感谢分享！学习了。不过2016版本里有maxifs函数吗？好像没有啊。

之前有段时间可以，现在需要是365或2019了
但这批函数最早出现就是在2016版本（switch，ifs，maxifs，minifs，concat，textjoin）
所以在2010-2016阶段都可以用aggregate替代maxifs

1055751654

呼叫铁匠，呼叫铁匠，听到请回答

改公式晕倒了，没有两百万不起来