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

《返回一列中“第二大”或“第n大”数值公式》由会员上传分享，免费在线阅读，更多相关内容在行业资料-天天文库。

1、通常，在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

2、+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中最大

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

4、REQUENCY(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个

5、区间，计算第一个参数数组“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个

6、区间，其中有三个“9”，第一个“9”对应的区间为“>8”且“<=9”,A1:A10中这样的数有3个（即3个“9”），返回“3”，第二个和第三个“9”所对应的区间都为“>9”且“<=9”，这个区间是不存在的，其对应位置都返回“0”。其他重复数值对应的返回值“0”依此类推，参考下表。        4.最后一个公式中“IF(MATCH(A1:A10,A1:A10,)=ROW(1:10),A1:A10)”的作用与第一个公式类似，不再赘述。