社会财经
社会杂谈 政治军事 科学探索 商业财经
居家生活
百科大全 手工制作 亲子育儿 美食菜谱 居家装修 驾车宝典 安全知识 智能手机 保险理财
电脑办公
电脑设计 系统/上网/安全 优化/故障/维护 办公软硬件 电脑教程 图片素材 其它
健康养生
健康常识 健康饮食 养生保健 亚健康 两性健康 医药大全 疾病防治 母婴常识
学习教育
小学课堂 初中/中考 高中/高考 大学/成教 教师/教学 公务员考试 英语学习 作文大全 论文 范文大全 其它
资讯八卦
热点资讯 娱乐资讯 科技资讯 体育资讯 军事资讯 房产资讯 职场资讯 教育资讯 灵异事件 爆笑段子 奇闻趣事
兴趣爱好
影视曲 解梦 佛教 钓鱼 园艺 宠物 星座 游戏 风水 摄影 收藏 旅游/汽车 乐器 茶艺 书画 其它
时尚爱美
发型发艺 瘦身减肥 健身运动 美容护肤 化妆技巧 香水精油 整容整形 美体塑身 穿衣搭配 时尚资讯
情感心理
两性心理 解读男人 女性课堂 心理知识 情绪管理 情感文章 爱情攻略 励志成长
历史文化
文化杂谈 美文欣赏 中国历史 世界历史 野史秘闻 诗词古文 短篇小说 故事大全 原创文化 其它
首页电脑办公办公软硬件
      

Excel函数学习41:AGGREGATE函数

时间:2018-01-11  热:0℃  分享:鱼啊鱼

 

AGGREGATE函数返回列表或数据库中的合计。AGGREGATE函数消除了条件格式的限制,如果区域中存在错误,则数据条、图标集和色阶将无法显示条件格式。这是因为当计算区域存在错误时,MINMAXPERCENTILE函数不进行计算。同样,LARGESMALLSTDEVP函数也会影响某些条件格式规则的相应功能。通过使用AGGREGATE函数,将忽略这些错误。AGGREGATE函数可以将不同的聚合函数应用于列表或数据库,并提供忽略隐藏行和错误值的选项。

1

 

什么情况下使用AGGREGATE函数?

AGGREGATE函数是在Excel 2010中引入的一个非常强大的函数,可以对列表或数据库应用不同的聚合函数并忽略隐藏行和错误值。它能够:

 

AGGREGATE函数语法

AGGREGATE函数有两种形式,一种是引用形式,一种是数组形式。其引用形式语法如下:

其数组形式语法如下:

 

AGGREGATE函数陷阱

如果第二个引用参数是必需的但未提供,那么AGGREGATE将返回错误值#VALUE!。如果有一个或多个引用是三维引用,那么AGGREGATE将返回错误值#VALUE!。如果在Excel 2007或之前的版本打开AGGREGATE工作簿将返回#NAME?

如果ref1,ref2,…中有其他AGGREGATE(或嵌套AGGREGATE),将忽略这些嵌套AGGREGATE,避免重复计算。如果AGGREGATE函数的引用中包含SUBTOTAL,那么将忽略这些SUBTOTAL。如果SUBTOTAL函数中包含AGGREGATE,那么将忽略这些AGGREGATE

AGGREGATE函数适用于数据列或垂直区域,不适用于数据行或水平区域。

AGGREGATE函数仅适用于2010及其后的版本。参数function_num指定的函数代号中,113不能处理数组操作,1419可以处理数组操作。例如,5代表MIN函数,但不能用于获取最小值的数组运算,可以使用15代表的SMALL函数来获取最小值。

 

示例1: 计算最大值、最小值、中值等

在下图2所示的工作表,数据单元格区域为A1:B11,其中单元格A1A4中含有错误值。使用AGGREGATE函数来计算最大值、最小值、中值等。

2

 

示例2: 计算满足多条件的数据最大值

如下图3所示的工作表,在单元格区域A1:C12中是不同超市的水果销售数据。现在想要知道,除中心超市外,榴莲和苹果在其他超市的最大销量。在单元格A16A17中列出了水果名,在B16中的公式为:

=AGGREGATE(14,6,$C$2:$C$12/(($A$2:$A$12<>$E$2)*($B$2:$B$12=A16)),1)

向下复制到单元格B17

3

 

示例3: 提取满足多个条件的数据

如图4所示,在单元格区域A4:D14中是数据,在单元格区域B1:D2中设置了条件,要从A4:D14中提取满足B1:D2条件的数据并放置到单元格F5开始的区域中。本例中,要提取张三在201751日至2017121日之间的销售数据。

4


在单元格F5中的公式为:

=IFERROR(INDEX(A$5:A$14,AGGREGATE(15,6,(ROW($A$5:$A$14)-ROW($A$5) 1)/(($A$5:$A$14>=$B$2)*($A$5:$A$14<=$C$2)*($C$5:$C$14=$D$2)),ROWS(F$5:F5))),'')

拖动公式单元格至I5,然后向下拖动至没有数据即可。

 


 

本文属原创文章,转载请注明出处。

欢迎在下面留言,完善本文内容,让更多的人学到更完美的知识。


百度搜索“就爱阅读”,专业资料,生活学习,尽在就爱阅读网92to.com,您的在线图书馆!
标签:
分享此文
猜你喜欢