算体重的公式excel中按某组数据按大小进行自动排序排名(RANK、COUNTIF、LOOKUP、SUMPRODUCT)

2020年9月19日发(作者：窦遴奇)
excel中按某组数据按大小进行自动排序排名（RANK、
COUNTIF、LOOKUP 、SUMPRODUCT）
(2011-03-16 16:00:46)
如开心网等游戏中效益自动排名或者按考试分数高低进行自动排名等
方法一：RANK 这个方法最简单，直接使用rank函数，如有第五名和第六名相等时，有两个第五名，则下
一个显 示第七名（没有第六名）。
在欲显示排名的列输入=RANK(D25,$$D$$25:$$D$$36) 则按降序排列，
=RANK(D25,$$D$$25:$$D$$36,1)则按升序排列。
方法二：COUNTIF
方法简单，直接使用countif函数：
1、在欲显示排名的列输入=COUNTIF($$N$$25:$$N$$36,则按降序排列，
=COUNTIF($$N$$25:$$N$$36,则按升序排列。
如有第五名和第六名相等时，有两个第六名（没有第五名），则下一个显示第七名。
2、在欲 显示排名的列输入=COUNTIF($$N$$25:$$N$$36,则按降序排列，同上，改
用小于号<即 可。
如有第五名和第六名相等时，有两个第五名（没有第六名），则下一个显示第七名。
方法三：LOOKUP
这个比较笨的方法，但也好理解，可以使用于显示排名名次且不局限于 数字形式，如可显示
冠军、亚军、第三名等等，如果有第五名和第六名相等时，可以有两个第五名可人工 来定下
一个是6还是7，主要看怎么定义，最大优点在于显示结果灵活，分3步来：
1、列F （随便找一个临时列，如果看着不爽干脆做好后直接隐藏）中输入
=SMALL($$D$$1:$$D$$20 ,ROW(A1)) ，把D1:D20中的数据从小到大排序，其中的绝对引用是
为了使用填充柄方便 操作，把排序的范围定在D1:D20，ROW（A1）取A1的行号1（可以
取任列意行号，主要偶看 A列开心顺意用A，如果用该函数从大到小排序则为把ROW(A1)
替换成count（$$D$$1:$$ D$$20）-ROW（A1）+1即可）。
2、在旁边找一空列列H，按大小进行排序，这个就是我们 希望排序排名是显示的（如果用
数字1、2、3、4显示排名则可以用省略这一步，直接引用行号）。这 个也可用IF来排名：
H1中输入1，然后在H2中输入=IF(F2=F1,H1,H1+1) ，如有第五名和第六名相等时，有两
个第五名，则下一个依然显示第六名，导致最后结果少一个。
3、在欲显示排名的列输入=LOOKUP(D1,$$F$$1:$$F$$20,$$H$$1:$$H$$20) ，LOOKUP为查找函数，
该语句作用为在$$F$$1:$$F$$20中查找到与D1相等的值，并返回$$ H$$1:$$H$$20中相应行的值，
注：$$H$$1:$$H$$20的类型不限制，但$$F$$1:$$F$$20 这一段数组只支持从小到大递增排序，否则
会出错。
方法四：SUMPRODUCT
这个方法比较简单，只要一个公式即可，但偶不明白他是怎么计算的，缺点是排名显示叫单
一，如有第 五名和第六名相等时，有两个第五名，则下一个依然显示第六名，导致最后结果
少一个，具体：
在欲显示排名的列输入
=SUMPRODUCT(($$D$$1:$$D$$20>=$$D1)COUNTI F($$D$$1:$$D$$20,$$D$$1:$$D$$20)) ，其中
SUMPRODUCT(array1,array2,array3, ...)在给定的几组 数组中，将数组间对应的元素相乘，并
返回乘积之和，COUNTIF(range,criteria )计算区域range中满足给定条件criteria的单元格的
个数。应该是计算满足大于或等于D 1个数，如果按数值最低为第1名即升序排列，则
($$D$$1:$$D$$20>=$$D1)替换为($$D$$ 1:$$D$$20<=$$D1)即可，比较方便，但是好像只能显示纯数值
的排名。
附以上几个函数的帮助信息参考：
一、SUMPRODUCT(array1,array2,array3, ...)
在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。
Array1, array2, array3, ... 为 2 到 30 个数组，其相应元素需要进行相乘并求和。
说明
数组参数必须具有相同的维数，否则，函数 SUMPRODUCT 将返回错误值 #VALUE!。
函数 SUMPRODUCT 将非数值型的数组元素作为 0 处理。
二、COUNT(value1,value2,...)和COUNTIF(range,criteria)
1、COUNT(value1,value2,...)
返回包含数字以及包含参数列表中的数字的单元格的个数。利用函数 COUNT 可以计算单
元格区域或数字数组中数字字段的输入项个数。
Value1, value2, ... 为包含或引用各种类型数据的参数（1 到 30个），但只有数字类型的数
据才被计算。
说明
函数 COUNT 在计数时，将把数字、日期、或以文本代表的数字计算在内；但是错 误值或
其他无法转换成数字的文字将被忽略。
如果参数是一个数组或引用，那么只统计数组或 引用中的数字；数组或引用中的空白单元格、
逻辑值、文字或错误值都将被忽略。如果要统计逻辑值、文 字或错误值，请使用函数
COUNTA。
2、COUNTIF(range,criteria)
计算区域中满足给定条件的单元格的个数。
Range 为需要计算其中满足条件的单元格数目的单元格区域。
Criteria 为确定哪些单元格将被 计算在内的条件，其形式可以为数字、表达式、单元格引
用或文本。其中数字可以直接写入，表达式和文 本必须加引号。例如，条件可以表示为 32、
、、或 B4。
说明
Microsoft Excel 还提供了其他一些函数，它们可根据条件来分析数据。
若要计算基于一个文本字符串或某范围内的一个数值的总和，可使用 SUMIF 工作表函数。
若要使公式返回两个基于条件的值之一，例如某指定销售金额的销售红利，可使用 IF 工作
表函数。
若要计算空或非空单元格的个数，可使用 COUNTA 和 COUNTBLANK 函数。
三、LOOKUP
从单行或单列区域或者从一个数组（ 数组：用于建立可生成多个结果或可对在行和列中排列
的一组参数进行运算的单个公式。数组区域共用一 个公式；数组常量是用作参数的一组常
量。）返回值。LOOKUP 函数具有两种语法形式：
1、向量 LOOKUP 的向量形式在单行区域或单列区域（称为“向量”）中查找值，然后返回第二个单行区域或单列区域中相同位置的值。
当要查询的值列表较大或者值可能会随时间而改变时，使用该向量形式。
向量是只含一行或一列的区域。LOOKUP 的向量形式在单行区域或单列区域（称为“向量”）中查找值，然后返回第二个单行区域或单列区域中相同位置的值。当要指定的区域包含要匹
配的值时 ，请使用 LOOKUP 函数的这种形式。LOOKUP 的另一种形式自动在第一行或第
一列中查找。
LOOKUP(lookup_value,l ookup_vector,result_vector)
Lookup_value LOOKUP 在第一个向量中搜索的值。Lookup_value 可以是数字、文本、逻辑
值、名称或对值的引用。
Lookup_vector 只包含一行或一列的区域。lookup_vector 中的值可以是文本、数字或逻辑值。
要点 lookup_vector 中的值必须以升序顺序放置：...,-2, -1, 0, 1, 2, ...；A-Z；FALSE, TRUE。
否则，LOOKUP 可能无法提供正确的值。大写文本和小写文本是等同的。
Result_vector 只包含一行或一列的区域。它必须与 lookup_vector 大小相同。
评论
如果 LOOKUP 找不到 lookup_value，则它与 lookup_vector 中小于或等于 lookup_value
的最大值匹配。
如果 lookup_value 小于 lookup_vector 中的最小值，则 LOOKUP 会提供 #NA 错误值。
如开心网等游戏中效益自动排名或者按考试分数高低进行自动排名等
方法一：RANK 这个方法最简单，直接使用rank函数，如有第五名和第六名相等时，有两个第五名，则下
一个显 示第七名（没有第六名）。
在欲显示排名的列输入=RANK(D25,$$D$$25:$$D$$36) 则按降序排列，
=RANK(D25,$$D$$25:$$D$$36,1)则按升序排列。
方法二：COUNTIF
方法简单，直接使用countif函数：
1、在欲显示排名的列输入=COUNTIF($$N$$25:$$N$$36,则按降序排列，
=COUNTIF($$N$$25:$$N$$36,则按升序排列。
如有第五名和第六名相等时，有两个第六名（没有第五名），则下一个显示第七名。
2、在欲 显示排名的列输入=COUNTIF($$N$$25:$$N$$36,则按降序排列，同上，改
用小于号<即 可。
如有第五名和第六名相等时，有两个第五名（没有第六名），则下一个显示第七名。
方法三：LOOKUP
这个比较笨的方法，但也好理解，可以使用于显示排名名次且不局限于 数字形式，如可显示
冠军、亚军、第三名等等，如果有第五名和第六名相等时，可以有两个第五名可人工 来定下
一个是6还是7，主要看怎么定义，最大优点在于显示结果灵活，分3步来：
1、列F （随便找一个临时列，如果看着不爽干脆做好后直接隐藏）中输入
=SMALL($$D$$1:$$D$$20 ,ROW(A1)) ，把D1:D20中的数据从小到大排序，其中的绝对引用是
为了使用填充柄方便 操作，把排序的范围定在D1:D20，ROW（A1）取A1的行号1（可以
取任列意行号，主要偶看 A列开心顺意用A，如果用该函数从大到小排序则为把ROW(A1)
替换成count（$$D$$1:$$ D$$20）-ROW（A1）+1即可）。
2、在旁边找一空列列H，按大小进行排序，这个就是我们 希望排序排名是显示的（如果用
数字1、2、3、4显示排名则可以用省略这一步，直接引用行号）。这 个也可用IF来排名：
H1中输入1，然后在H2中输入=IF(F2=F1,H1,H1+1) ，如有第五名和第六名相等时，有两
个第五名，则下一个依然显示第六名，导致最后结果少一个。
3、在欲显示排名的列输入=LOOKUP(D1,$$F$$1:$$F$$20,$$H$$1:$$H$$20) ，LOOKUP为查找函数，
该语句作用为在$$F$$1:$$F$$20中查找到与D1相等的值，并返回$$ H$$1:$$H$$20中相应行的值，
注：$$H$$1:$$H$$20的类型不限制，但$$F$$1:$$F$$20 这一段数组只支持从小到大递增排序，否则
会出错。
方法四：SUMPRODUCT
这个方法比较简单，只要一个公式即可，但偶不明白他是怎么计算的，缺点是排名显示叫单
一，如有第 五名和第六名相等时，有两个第五名，则下一个依然显示第六名，导致最后结果
少一个，具体：
在欲显示排名的列输入
=SUMPRODUCT(($$D$$1:$$D$$20>=$$D1)COUNTI F($$D$$1:$$D$$20,$$D$$1:$$D$$20)) ，其中
SUMPRODUCT(array1,array2,array3, ...)在给定的几组 数组中，将数组间对应的元素相乘，并
返回乘积之和，COUNTIF(range,criteria )计算区域range中满足给定条件criteria的单元格的
个数。应该是计算满足大于或等于D 1个数，如果按数值最低为第1名即升序排列，则
($$D$$1:$$D$$20>=$$D1)替换为($$D$$ 1:$$D$$20<=$$D1)即可，比较方便，但是好像只能显示纯数值
的排名。
附以上几个函数的帮助信息参考：
一、SUMPRODUCT(array1,array2,array3, ...)
在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。
Array1, array2, array3, ... 为 2 到 30 个数组，其相应元素需要进行相乘并求和。
说明
数组参数必须具有相同的维数，否则，函数 SUMPRODUCT 将返回错误值 #VALUE!。
函数 SUMPRODUCT 将非数值型的数组元素作为 0 处理。
二、COUNT(value1,value2,...)和COUNTIF(range,criteria)
1、COUNT(value1,value2,...)
返回包含数字以及包含参数列表中的数字的单元格的个数。利用函数 COUNT 可以计算单
元格区域或数字数组中数字字段的输入项个数。
Value1, value2, ... 为包含或引用各种类型数据的参数（1 到 30个），但只有数字类型的数
据才被计算。
说明
函数 COUNT 在计数时，将把数字、日期、或以文本代表的数字计算在内；但是错 误值或
其他无法转换成数字的文字将被忽略。
如果参数是一个数组或引用，那么只统计数组或 引用中的数字；数组或引用中的空白单元格、
逻辑值、文字或错误值都将被忽略。如果要统计逻辑值、文 字或错误值，请使用函数
COUNTA。
2、COUNTIF(range,criteria)
计算区域中满足给定条件的单元格的个数。
Range 为需要计算其中满足条件的单元格数目的单元格区域。
Criteria 为确定哪些单元格将被 计算在内的条件，其形式可以为数字、表达式、单元格引
用或文本。其中数字可以直接写入，表达式和文 本必须加引号。例如，条件可以表示为 32、
、、或 B4。
说明
Microsoft Excel 还提供了其他一些函数，它们可根据条件来分析数据。
若要计算基于一个文本字符串或某范围内的一个数值的总和，可使用 SUMIF 工作表函数。
若要使公式返回两个基于条件的值之一，例如某指定销售金额的销售红利，可使用 IF 工作
表函数。
若要计算空或非空单元格的个数，可使用 COUNTA 和 COUNTBLANK 函数。
三、LOOKUP
从单行或单列区域或者从一个数组（ 数组：用于建立可生成多个结果或可对在行和列中排列
的一组参数进行运算的单个公式。数组区域共用一 个公式；数组常量是用作参数的一组常
量。）返回值。LOOKUP 函数具有两种语法形式：
1、向量 LOOKUP 的向量形式在单行区域或单列区域（称为“向量”）中查找值，然后返回第二个单行区域或单列区域中相同位置的值。
当要查询的值列表较大或者值可能会随时间而改变时，使用该向量形式。
向量是只含一行或一列的区域。LOOKUP 的向量形式在单行区域或单列区域（称为“向量”）中查找值，然后返回第二个单行区域或单列区域中相同位置的值。当要指定的区域包含要匹
配的值时 ，请使用 LOOKUP 函数的这种形式。LOOKUP 的另一种形式自动在第一行或第
一列中查找。
LOOKUP(lookup_value,l ookup_vector,result_vector)
Lookup_value LOOKUP 在第一个向量中搜索的值。Lookup_value 可以是数字、文本、逻辑
值、名称或对值的引用。
Lookup_vector 只包含一行或一列的区域。lookup_vector 中的值可以是文本、数字或逻辑值。
要点 lookup_vector 中的值必须以升序顺序放置：...,-2, -1, 0, 1, 2, ...；A-Z；FALSE, TRUE。
否则，LOOKUP 可能无法提供正确的值。大写文本和小写文本是等同的。
Result_vector 只包含一行或一列的区域。它必须与 lookup_vector 大小相同。
评论
如果 LOOKUP 找不到 lookup_value，则它与 lookup_vector 中小于或等于 lookup_value
的最大值匹配。
如果 lookup_value 小于 lookup_vector 中的最小值，则 LOOKUP 会提供 #NA 错误值。
2、数组 LOOKUP 的数组形式在数组的第一行或第一列中查找指定的值，然后返回数组的
最后一行或最后一 列中相同位置的值。
当要查询的值列表较小或者值在一段时间内保持不变时，使用该数组形式。
LOOKUP 的数组形式在数组（数组：用于建立可生成多个结果或可对在行和列中排列的一
组参数进行运算的单个公式。数组区域共用一个公式；数组常量是用作参数的一组常量。）
的第一行或第 一列中查找指定的值，并返回数组最后一行或最后一列内同一位置的值。当要
匹配的值位于数组的第一行 或第一列中时，使用 LOOKUP 的这种形式。当要指定列或行的
位置时，使用 LOOKUP 的另一种形式。
提示
一般而言，最好使用 HLOOKUP 或 VLOOKUP 函数而不是 LOOKUP 的数组形式。
LOOKUP 的这种形式是为了与其他电子表格程序兼容而提供的。
LOOKUP(lookup_value,array)
Lookup_value LOOKUP 在数组中搜索的值。Lookup_value 可以是数字、文本、逻辑值、名
称或对值的引用。
如果 LOOKUP 找不到 lookup_value，它会使用数组中小于或等于 lookup_value 的最大值。
如果 lookup_value 小于第一行或第一列中的最小值（取决于数组维度），LOOKUP 会返回
#NA 错误值。
数组 包含要与 lookup_value 进行比较的文本、数字或逻辑值的单元格区域。
LOOKUP 的数组形式与 HLOOKUP 和 VLOOKUP 函数非常相似。区别在于：HLOOKUP
在第一行中搜索 lookup_value，VLOOKUP 在第一列中搜索，而 LOOKUP 根据数组维度
进行搜索。
如果数组包含宽度比高度大的区域（列数多于行数），LOOKUP 会在第一行中搜索
lookup_value。
如果数组是正方的或者高度大于宽度（行数多于列数），则 LOOKUP 在第一列中进行搜索。
使用 HLOOKUP 和 VLOOKUP，可以通过索引以向下或遍历的方式搜索，但是 LOOKUP
始终选择行或列中的最后一个值。
要点 数组中的值必须以升序顺序放置：...,-2, -1, 0, 1, 2, ...；A-Z；FALSE, TRUE。否则，
LOOKUP 无法提供正确的值。大写文本和小写文本是等同的。
注释 对于详细的测试或七种以上条件的测试，还可以使用 LOOKUP 函数作为 IF 函数的
替代函数。请参阅数组形式的示例。
四、SMALL(array,k)
返回数据集中第 k 个最小值。使用此函数可以返回数据集中特定位置上的数值。
Array 为需要找到第 k 个最小值的数组或数字型数据区域。
K 为返回的数据在数组或数据区域里的位置（从小到大）。
说明
如果 array 为空，函数 SMALL 返回错误值 #NUM!。
如果 k ≤ 0 或 k 超过了数据点个数，函数 SMALL 返回错误值 #NUM!。
如果 n 为数组中的数据点个数，则 SMALL(array,1) 等于最小值，SMALL(array,n) 等于最
大值。
五、RANK(number,ref,order)
返回一个数字 在数字列表中的排位。数字的排位是其大小与列表中其他值的比值（如果列表
已排过序，则数字的排位就 是它当前的位置）。
Number 为需要找到排位的数字。
Ref 为数字列表数组或对数字列表的引用。Ref 中的非数值型参数将被忽略。
Order 为一数字，指明排位的方式。
如果 order 为 0（零）或省略，Microsoft Excel 对数字的排位是基于 ref 为按照降序排列
的列表。
如果 order 不为零，Microsoft Excel 对数字的排位是基于 ref 为按照升序排列的列表。
说明
函数 RANK 对重复数的排位相同。但重复数的存在将影响后续数值的排位。例如， 在一列
按升序排列的整数中，如果整数 10 出现两次，其排位为 5，则 11 的排位为 7（没有排位
为 6 的数值）。
由于某些原因，用户可能使用考虑重复数字的排位定义。在前面的示例中，用户可能要将整
数 10 的排位改为 5.5。这可通过将下列修正因素添加到按排位返回的值来实现。该修正因
素对于按 照升序计算排位（顺序 = 非零值）或按照降序计算排位（顺序 = 0 或被忽略）的
情况都是正确的。
重复数排位的修正因素 =[COUNT(ref) + 1 – RANK(number, ref, 0) – RANK(number, ref,
1)]2。
在下列示例中，RANK(A2,A1:A5,1) 等于 3。修正因素是 (5 + 1 – 2 – 3)2 = 0.5，考虑重复
数排位的修改排位是 3 + 0.5 = 3.5。如果数字仅在 ref 出现一次，由于不必调整 RANK，
因此修正因素为 0。