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

通常，在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”依此类推

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