excelperfect
引言:本文的练习整理自chandoo.org。多练习,这是我们从小就在使用的学习方法。在练习的过程中,认真思考,不断尝试,以此来磨练自己的公式与函数应用技能,也让研究Excel的大脑时刻保持着良好的状态。同时,想想自己怎么解决这个问题,看看别人又是怎样解决的,从而快速提高Excel公式应用水平。
Q:有一组数据,如何使用公式返回其中出现次数最多的5个数。示例数据如下图1所示,出现次数最多的5个数依次为:18,19,16,17,20。
图1
如何编写公式?
A:先给出答案,然后再解释。
在单元格F2中输入数组公式:
=MODE(IF(COUNTIF(F$1:F1,A$1:D$36),'',A$1:D$36))
向下拖拉复制到单元格F6,结果如下图2所示。
图2
公式中,
COUNTIF(F$1:F1,A$1:D$36)
查找数据区域A1:D36中每个单元格中的数据在公式所在单元格之前的列F中的单元格中出现的次数。反过来说,区域A1:D36中已经在列F中公式所在单元格之前出现过的数据。大家可以选择公式中的这部分,然后按F9查看结果。结果中,区域A1:D36中已经在列F中公式所在单元格之前出现过的数据会显示出现的次数,没有出现过的数据则为0。根据规则,非0数字等价于TRUE,0等价于FALSE,这样,IF函数对于已经出现过的数字会返回空,而没有出现过的数字则返回区域A1:D36中原位置所在的数字,将生成的结果数组传递到MODE函数中,返回结果数组中出现次数最多的数字。
也就是说,第一个公式获取出现次数最多的数字后,第二个公式会排除这个数字,而从剩余的数字中获取出现次数最多的数字(即整个数据中出现次数第二多的数字),依此类推。
欢迎在下面留言,完善本文内容,让更多的人学到更完美的知识。
欢迎到知识星球:完美Excel社群,进行技术交流和提问,获取更多电子资料,并通过社群加入专门的微信讨论群,更方便交流。
联系客服