原标题:如何不计重复返回一组数中的次大、次小值?
如何不计重复返回一组数中的次大、次小值
在统计数据的时候,有时不仅需要找出一组数据的最大、最小值,还需要排除重复值后找出其次大、次小值。如图 100‑1所示为某校2010年春季学期初二年级期末考试成绩表,如何不计重复值找出学生成绩总分的次大、次小值?
图100‑1不计重复的次大、次小数据
1
解决方案
使用LARGE、SMALL函数与COUNTIF函数组合不计重复提取数据次大、次小值。
2
操作方法
在E20单元格输入下列公式,按Enter键结束。
=LARGE(H3:H18,COUNTIF(H3:H18,MAX(H3:H18))+1)
在E21单元格输入下列公式,按Enter键结束。
=SMALL(H3:H18,COUNTIF(H3:H18,MIN(H3:H18))+1)
3
原理分析
提取不计重复的次大、次小值
1.使用COUNTIF统计H列总分最大值414的个数(共3个),然后加1即可得出次大值的排位(从大到小排),最后使用LARGE函数提取总分第4大值,即次大值。
2.同理,使用COUNTIF总分最小值的个数并加1即可得出次小值的排位(从小到大排),最后使用SMALL函数提取次小值。
LARGE、SMALL函数用于返回数据集中第k个最大、最小值。语法如下:
LARGE(array, k)
SMALL(array, k)
其中,第1参数array为需要确定第k 个最大、最小值的数组或数据区域,第2个参数k为返回值在数组或数据单元格区域中的位置,如果区域中数据点的个数为n,则LARGE(array,1) 返回最大值,LARGE(array,n) 返回最小值,SMALL函数与之相反。
4
知识扩展
此外,还可以使用FREQUENCY函数或MATCH=ROW法判断不重复数值后提取次大或次小值,例如以下4个公式,其中公式1和公式2使用FREQUENCY函数判别重复,无需按组合键形成数组公式,而公式3和公式4则必须使用数组公式:
公式1:
=LARGE(IF(FREQUENCY(H3:H18,H3:H18),H3:H18),2)
公式2:
=SMALL(IF(FREQUENCY(H3:H18,H3:H18),H3:H18),2)
公式3:
{=LARGE(IF(MATCH(H3:H18,H3:H18,0)=ROW(H3:H18)-2,H3:H18),2)}
公式4:
{=SMALL(IF(MATCH(H3:H18,H3:H18,0)=ROW(H3:H18)-2,H3:H18),2)}
版权所有 转载须经Excel技巧网/Office学吧允许
【 Excel 】:用鼠标右键拖动单元格区域至目标区域返回搜狐,查看更多
责任编辑: