一、案例
公司组织员工旅游,有三个景点可供选择:羊羊村、巴拉梦幻岛、二熊山。每个员工选择一个景点,以被选择次数最多的景点作为旅游地。
如下图所示,A1:B10为每个员工选择景点的情况。要求找到被选择次数最多的景点。
观察A2:B10单元格区域,我们可以发现“羊羊村”和“巴拉梦幻岛”出现次数最多,均为4次。
我们在设置公式时,就要考虑到出现次数最多的文本可能不止一个。
二、计算步骤
在单元格D2输入公式
=IFERROR(INDEX($B$2:$B$10,SMALL(MODE.MULT(MATCH($B$2:$B$10,$B$2:$B$10,0)),ROW(1:1))),"")
拖动填充柄向下复制公式,知道出现空值为止。
公式解析:
(1)MATCH函数用于返回查找值在数组中的相对位置,如果查找值不止出现一次,返回第一次出现的位置。
MATCH($B$2:$B$10,$B$2:$B$10,0)返回B2:B10每个单元格的文本在B2:B10单元格区域出现的相对位置,返回值为{1;2;3;1;2;2;1;2;1}。
即“羊羊村”出现在B2:B10的第1行,“巴拉梦幻岛”出现在B2:B10的第2行,“二熊山”出现在B2:B10的第3行。B2单元格的“羊羊村”第二次出现在B2:B10,MATCH函数返回其第一次出现的位置,即B2:B10的第1行。
(2)MODE.MULT返回一组数值中出现频率最高的垂直数组。
MODE.MULT(MATCH($B$2:$B$10,$B$2:$B$10,0))即
MODE.MULT({1;2;3;1;2;2;1;2;1})。其中“1”和“2”出现的频率最高,均为4次。MODE.MULT返回结果为{1;2}
(3)SMALL函数用于返回数组中的第k个最小值。
SMALL(MODE.MULT(MATCH($B$2:$B$10,$B$2:$B$10,0)),ROW(1:1))即
SMALL({1;2},1),返回数组{1;2}中的第一个最小值,也就是“1”。
ROW(1:1)为相对引用,当D2单元格公式向下复制到D3时,SMALL函数返回数组{1;2}中的第二个最小值,也就是“2”。
(4)
INDEX($B$2:$B$10,SMALL(MODE.MULT(MATCH($B$2:$B$10,$B$2:$B$10,0)),ROW(1:1))),即INDEX($B$2:$B$10,1),返回B2:B10第1行的文本,即“羊羊村”。当D2单元格公式向下复制到D3时,SMALL函数返回“2”,INDEX($B$2:$B$10,2),返回B2:B10第2行的文本,即“巴拉梦幻岛”。
(5)IFERROR函数用于屏蔽错误值。
拓展:
如果确定出现次数最多的文本只有一个,则函数可以简化为
=INDEX($B$2:$B$10,MODE(MATCH($B$2:$B$10,B2:$B$10,0)))
END
关于IFERROR+INDEX+SMALL+ROW函数组合用法的文章:
【Excel教程】多次走访同一客户,如何提取最近一次走访客户的日期?
关于文本查找和统计的文章:
联系客服