您的位置: 首页 > EXCEL技巧 > Excel函数 >

返回一列中“第二大”或“第N大”数值的公式

时间:2014-10-12 整理:docExcel.net

通常,在Excel中要得到一列数值中的第二大数可用LARGE函数,如“=LARGE(A1:A10,2)”,但有时会遇到有多个最大值的情况,例如A列为“{9;9;9;4;8;4;8;5;5;6}”,上述公式返回的还是“9”,而我们要得到的“第二大”数值是“8”。这种情况下可用下面的一些公式,假如数据在A1:A10:

  返回“第二大”数值的几个公式示例

公式一:

=LARGE(A1:A10,COUNTIF(A1:A10,MAX(A1:A10))+1)

公式二(数组公式):

=MAX((A1:A10

输入完毕需按Ctrl+Shift+Enter结束,下同。

公式三:

=LARGE(IF(FREQUENCY(A1:A10,A1:A10),A1:A10),2)

公式四(数组公式):

=LARGE(IF(MATCH(A1:A10,A1:A10,)=ROW(1:10),A1:A10),2)

最后两个公式不仅能返回“第二大”数值,还能返回“第N大”数值。将其中的“2”改为N即可返回“第N大”数值。

说明:

1.第一个公式先统计A1:A10中最大值的数量,再加上“1”作为LARGE函数的第二个参数得到所需“第二大”数值。

2.第二个公式先求出所有小于A1:A10中最大值的数,再用MAX函数返回其中的最大值,即“第二大”数值。

3.FREQUENCY函数计算数值在区域内的出现频率,并返回一个垂直数组。第三个公式中FREQUENCY函数的两个参数都是“A1:A10”,返回的垂直数组对应A1:A10所有重复值的位置处为“0”。这样,结合IF函数可返回A1:A10中不包含重复值的数组,最后用LARGE函数返回“第二大”数值。之所以重复项对应位置返回“0”,这里笔者结合自己的理解简单解释一下,仅供参考。FREQUENCY函数的语法为:

FREQUENCY(data_array, bins_array)

本例A1:A10及FREQUENCY(A1:A10,A1:A10)的返回值分别是:

A1:A10:         {9;9;9;4;8;4;8;5;5;6}

返回的垂直数组: {3;0;0;2;2;0;0;2;0;1;0}

 可以看到,第二个、第三个“9”对应返回值的位置处都为“0”,第二次出现的“4”、“8”及“5”对应位置处也都为“0”。假如FREQUENCY函数第二个参数数组“bins_array”中有N个数值,即使该参数数组中的数值没有按从小到大的顺序排列(如本例), FREQUENCY函数也会在计算时自动将其按从小到大的顺序排列,并将整个数值区域划分为N+1个区间,计算第一个参数数组“data_array”中的数值在这N+1个区间中的出现数量,同时以垂直数组的形式返回,这也是 FREQUENCY函数返回的数组比第二个参数数组“bins_array”多一个数值的原因。可以看到,返回的垂直数组中各数值出现的位置与第二个参数数组“bins_array”中的数值位置是一一对应的,最后多出的一个数为“data_array”中大于“bins_array”最大值的数量。本例FREQUENCY函数第二个参数数组“bins_array”也为“A1:A10”,排序后为“{4;4;5;5;6;8;8;9;9;9}”,10个数共划分了11个区间,其中有三个“9”,第一个“9”对应的区间为“>8”且“<=9”,a1:a10中这样的数有3个(即3个“9”),返回“3”,第二个和第三个“9”所对应的区间都为“>9”且“<=9”,这个区间是不存在的,其对应位置都返回“0”。其他重复数值对应的返回值“0”依此类推,参考下表。< p="">

  

4.最后一个公式中“IF(MATCH(A1:A10,A1:A10,)=ROW(1:10),A1:A10)”的作用与第一个公式类似,不再赘述。

如何从一列数中获取最小的正数和最大的负数 问题:如何从一列数中获取最小的正数和最大的负数
回答:假如A1:A20区域中是一些数值,其中包含正数、负数和零,现在需要找出其中的最大的负数和最小的正数,可以用下面的数组公式: 获取最大的负数: =MAX(IF(A1:A20<0,A1:A20)) 由于是数组公式公式输入完毕后按Ctrl+Shift+Enter结束...
如何给一行数据中的第二大数表上颜色 问题:如何给一行数据中的第二大数表上颜色
回答:1、选中要标识的远隔区域,假设是b2:f182、点击条件格式,选择公式3、输入:=LARGE($B2:$F2,2)=B2 ,确定
设置Excel表格中的几组数值,最大的自动得100 问题:设置Excel表格中的几组数值,最大的自动得100,第二大数值得80,类推
回答:1、假如有6个数据在a1:6列。2、在b1中输入:=IF(RANK(A1,$A$1:$A$6)=1,100,IF(RANK(A1,$A$1:$A$6)=2,80,IF(RANK(A1,$A$1:$A$6)=3,60,IF(RANK(A1,$A$1:$A$6)=4,40,IF(RANK(A1,$A$1:$A$6)=5,20,IF(RANK(A...
为什么用公式“收入—支出+余额”计算的数值在 问题:为什么用公式“收入—支出+余额”计算的数值在Excel2010中没有显示
回答:你公式里D4和E4都是数值但是F3是文字,你这怎么可以算的出数值
对多个相同数值返回平均排位的公式 问题:对多个相同数值返回平均排位的公式
回答:在Excel中对区域中的数值进行排位时要用到RANK函数,RANK函数对于并列排位的处理方法是取其最小位次。例如对于一列数值“9,2,1,2,6,2,7,3,7”中有两个“7”,按照从大到小的顺序排位时这两个数都返回“2”,即并列第...
电子表格单元格设置好公式,如何在不输入数值 问题:电子表格单元格设置好公式,如何在不输入数值时,如何设置才不显示VID/0!
回答:此错误叫做被0除!可以在你原来的公式上,加一层错误处理:=IFERROR(原公式,0)意思是:如果原公式正常就按原公式处理,出错就按0处理。
相关推荐: