举个例子,以下公式返回结果为2,说明A2:A10区域中2出现的次数最多。
=MODE(A2:A10)
这时出现了一个问题,当区域内出现次数最多的数有多个不同值时,MODE函数只会返回其中一个。
如下图所示,A2:A10区域2和3都出现了3次,但MODE函数只返回一个结果:2。
为了解决这个问题,微软爸爸响应号召给MODE又生了个弟弟:MODE.MULT。这家伙可以从一组数据中返回出现次数最多的多个值,组成一个垂直数组。
依然以上图所示数据为例,在C2单元格输入以下公式,会返回出现次数最多的两个数值:2和3。
=MODE.MULT(A2:A10)
打个响指,这就是MODE.MULT函数的 家庭基本情况,下面再给大家聊聊这家伙具体有啥用法。
我举两个例子。
1
优次最多的学员名单
如下图所示,A:C是数据源,C列是成绩,同一个人不存在多个相同科目的情况。现在,假设85分以上为优,需要统计科目成绩为优次数最多的学员名单。F列是模拟结果。
E2单元格输入以下公式,向下复制即可:
案例1 ▼
=IFERROR(INDEX(A:A,SMALL(MODE.MULT(IF(C$2:C$18>85,MATCH(A$2:A$18,A:A,))),ROW(A1))),"")
公式看起来比较长,但逻辑并不复杂。
IF(C$2:C$18>85,MATCH(A$2:A$18,A:A,0))
使用IF函数判断C列的成绩是否大于85分,如果条件不成立,返回逻辑值FALSE,否则运行MATCH函数。MODE.MULT函数不能直接计算非数值,而MATCH函数返回的结果是每个人名在A列首次出现时的行号,等于将人名替换成了可以被计算的行号。
然后使用MODE.MULT函数计算出现次数最多的行号,返回一个垂直内存数组{2;4;9},它代表了成绩为优次数最多的人所在的行号。
使用SMALL函数从中依次取出每个行号,再使用INDEX函数按行索骥从A列获取人名,最后使用IFERROR函数屏蔽可能出现的错误值。
2
指定科目全优的学员名单
把上面的问题再扩展一下,就成了第2个例子:查询数学和英语两个科目全优的学员名单。
参考公式如下:
案例2 ▼
=INDEX(A:A,SMALL(MODE.MULT(IF((B$2:B$18={"数学","英语"})*($C$2:$C$18>85),MATCH($A$2:$A$18,A:A,0))),ROW(A1)))
虽然问题稍有不同,但和案例1相比,依然是相同的配方熟悉的味道。
首先使用IF函数判断B列的科目是否等于{"数学","英语"},同时C列的成绩大于85分,如果两个条件都成立,返回A列人名所在的行号,否则返回逻辑值FALSE。
然后使用MODE.MULT函数计算出现次数最多的行号,最后通过INDEX按行索骥取出人名,即为结果。
……
嘿!最后留一道练习题。
如上图所示,A2单元格有一段数据,需要计算其中出现次数最多的字符,A5单元格是模拟结果。
没了,左上角点关注,右下角点个赞,有啥问题可以在VIP微信会员群中提问交流,挥挥手咱们明天再见。
联系客服