您的位置: 首页 > 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处理。
相关知识:

下面内容对您也许有用

      话题:EXCEL中如何让第一列中相同东西所对应的第二列的数值相加,并且
      问:如:A列 B列 8100 5 8100 10 8101 5 8101 5 8101 11 8102 11 8103 2 8103 5 8103
      答:具体操作如下 1)点击A列列标"A",点击菜单中的"数据","筛选","高级筛选"2)"将筛选结果复制到其他位置"勾上,"选择不重复数据"勾上 3)"复制到"那里填入:C:C 4)确定 EXCEL中如何让第一列中相同东西 excel 判断第一列是否有相同的
      话题:EXCEL中怎样在一行里面查找指定数值返回同-列中的下一行的数值
      问:EXCEL中怎样在一行里面查找指定数值返回到同-列中下一行的数值?我要在第二
      答:MATCH配合偏移函数 Excel 区域内查找一个数值后, Excel 区域内查找一个数值,然后
      话题:excel 中多列查找,返回同一行中某列的值
      Col_index_num 为 1 时,返回 table_array 第一列中数值;Col_index_num 为 2 时,返回 table_array 第二列中数值,以此类推。如果Col_index_num 小于 1,函数 VLOOKUP 返回错误值# VALUE!
      话题:EXCEL中,如何返回最大数值所在列中第二行或者第三行,或者
      问:张三 李四 王五 赵六 张七 李八 王九 返回数值 51.87 82.33 19.47 64.69 15.37
      答:假如张三为A2单元格 H3输入=INDEX($A$2:$G$2,MATCH(MAX(A3:G3),A3:G3,0))往下填充 如何在excel中查到指定的数值后, EXCEL中怎样在一列中查找指定
      话题:excel中怎么才能查找一列中不论在哪行的某个数值,返回的都是
      问:excel中怎么才能在一个范围内查找一个数值,这个数值不论在这个范围的哪个位置,
      答:追问 感谢,但是我跨工作表用相同的公式的结果为什么是#REF?我表格里全部为文字,有影响吗?我的QQ1 3 1 7 2 5 8 6 3 9 回答 跨工作表用 公式需要改一下 表格里 excel里怎么查找一个数据列里的 EXCEL中查询某一数值返回数值
      话题:Excel求教:如何在一列数值中‘排序’并返回‘1,2,3,4’
      问:黄色区域的顺序不能变,只能在C列返回排名次序(按销售从大到小)。
      答:rank(B2,$B$2:$B$6)公式下拉 以上…-本人新建团“数字精灵”: zhidao.baidu./team/view/%CA%FD%D7%D6%BE%AB%C1%E9诚邀高手加入… excel 中怎样将3组数值排序 3、Excel2003中可以对3个以上
      话题:求excel 返回某列中相同的某一文本中所对应的另一行的数值中第
      问:如:A B 苹果 10 苹果 45 香蕉 32 苹果 99 香蕉 18 苹果 50 求苹果中排名第3的
      答:LARGE(IF((A1:A10="苹果"),B1:B10),3)数组公式,按下SHIFT+CTRL+ENTER三键结束输入 excel提取数值 用什么函数把 excel函数:将数值格式转换为
      话题:寻找某一列单元格数值大于0,返回它所对应的那一行的某一个单元
      问:我的目的是找某一列单元格数值大于0的数据,返回它对应的那一行的某一单元格的
      答:何必用公式。E列排序后,不就可以直接复制B列的内容了么。 请问在EXCEL中如何将条件格式与 一张excel表好多数据,如何查找
      话题:xcel中一个数值段返回一个值 例:在A列中输入数值 在B列中输入
      问:A列输入:2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21…B列返回1 1
      答:B1输入公式=int((A1-1)/5)+1 XCEL表格中如何自动取中间值(A1 返回值是怎么返回值?返回值是给
      话题:excel中如何比较多个单元格中数值的大小,并返回数值大的那个
      问:excel中如何比较多个单元格中数值的大小,并返回数值大的那个单元格所在的行号和
      答:根据你的补充=MAX(IF(A1:K1="红",A2:K2))-OFFSET($A$1,1,MATCH("黑",A1:K1,)-1,)数组公式 同时按Ctrl+Alt+Enter三键结束 如不行,发表 Q 306212368 怎样在excel中返回包含某一数值 excel中如何返回最大数值对应
最新评论