excel中按某组数据按大小进行自动排序排名(RANK、COUNTIF、LOOKUP、SUMPRODUCT)

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,">="&N25)则按降序排列, =COUNTIF($N$25:$N$36,"<="&N25)则按升序排列。 如有第五名和第六名相等时,有两个第六名(没有第五名),则下一个显示第七名。 2、在欲显示排名的列输入=COUNTIF($N$25:$N$36,">"&N25)+1则按降序排列,同上,改用小于号<即可。 如有第五名和第六名相等时,有两个第五名(没有第六名),则下一个显示第七名。 方法三: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)/COUNTIF($D$1:$D$20,$D$1:$D$20)) ,其中SUMPRODUCT(array1,array2,array3, ...)在给定的几组数组中,将数组间对应的元素相乘,并 返回乘积之和,COUNTIF(range,criteria)计算区域range中满足给定条件criteria的单元格的个数。应该是计算满足大于或等于D1个数,如果按数值最低为第1名即升序排列,则($D$1:$D$20>=$D1)替换为($D$1:$D$20<=$D1)即可,比较方便,但是好像只能显示纯数值的排名。 附以上几个函数的帮助信息参考: 一、SUMPRODUCT(array1,array2,array3, ...) 在给定的几组数组中,将数组间对应的元素相乘,并返回乘积之和。 Array1, array2, array3, ... 为2 到30 个数组,其相应元素需要进行相乘并求和。 说明 数组参数必须具有相同的维数,否则,函数SUMPRODUCT 将返回错误值#V ALUE!。 函数SUMPRODUCT 将非数值型的数组元素作为0 处理。 二、COUNT(value1,value2,...)和COUNTIF(range,criteria) 1、COUNT(value1,value2,...) 返回包含数字以及包含参数列 表 关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf 中的数字的单元格的个数。利用函数COUNT 可以计算单元格区域或数字数组中数字字段的输入项个数。 Value1, value2, ... 为包含或引用各种类型数据的参数(1 到30个),但只有数字类型的数据才被计算。 说明 函数COUNT 在计数时,将把数字、日期、或以文本代表的数字计算在内;但是错误值或其他无法转换成数字的文字将被忽略。 如果参数是一个数组或引用,那么只统计数组或引用中的数字;数组或引用中的空白单元格、逻辑值、文字或错误值都将被忽略。如果要统计逻辑值、文字或错误值,请使用函数COUNTA。 2、COUNTIF(range,criteria) 计算区域中满足给定条件的单元格的个数。 Range 为需要计算其中满足条件的单元格数目的单元格区域。 Criteria 为确定哪些单元格将被计算在内的条件,其形式可以为数字、表达式、单元格引用或文本。其中数字可以直接写入,表达式和文本必须加引号。例如,条件可以表示为32、"32"、">32" 、"apples" 或B4。 说明 Microsoft Excel 还提供了其他一些函数,它们可根据条件来分析数据。 若要计算基于一个文本字符串或某范围内的一个数值的总和,可使用SUMIF 工作表函数。若要使公式返回两个基于条件的值之一,例如某指定销售金额的销售红利,可使用IF 工作表函数。 若要计算空或非空单元格的个数,可使用COUNTA 和COUNTBLANK 函数。 三、LOOKUP 从单行或单列区域或者从一个数组(数组:用于建立可生成多个结果或可对在行和列中排列的一组参数进行运算的单个公式。数组区域共用一个公式;数组常量是用作参数的一组常量。)返回值。LOOKUP 函数具有两种语法形式: 1、向量LOOKUP 的向量形式在单行区域或单列区域(称为“向量”)中查找值,然后返回第二个单行区域或单列区域中相同位置的值。 当要查询的值列表较大或者值可能会随时间而改变时,使用该向量形式。 向量是只含一行或一列的区域。LOOKUP 的向量形式在单行区域或单列区域(称为“向量”)中查找值,然后返回第二个单行区域或单列区域中相同位置的值。当要指定的区域包含要匹 配的值时,请使用LOOKUP 函数的这种形式。LOOKUP 的另一种形式自动在第一行或第一列中查找。 LOOKUP(lookup_value,lookup_vector,result_vector) Lookup_value LOOKUP 在第一个向量中搜索的值。Lookup_value 可以是数字、文本、逻辑值、名称或对值的引用。 Lookup_vector 只包含一行或一列的区域。lookup_vector 中的值可以是文本、数字或逻辑值。 要点 综治信访维稳工作要点综治信访维稳工作要点2018综治平安建设工作要点新学期教学工作要点医院纪检监察工作要点 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 会提供#N/A 错误值。如开心网等游戏中效益自动排名或者按考试分数高低进行自动排名等 方法一:RANK 这个方法最简单,直接使用rank函数,如有第五名和第六名相等时,有两个第五名,则下一个显示第七名(没有第六名)。 在欲显示排名的列输入=RANK(D25,$D$25:$D$36) 则按降序排列, =RANK(D25,$D$25:$D$36,1)则按升序排列。 方法二:COUNTIF 方法简单,直接使用countif函数: 1、在欲显示排名的列输入=COUNTIF($N$25:$N$36,">="&N25)则按降序排列, =COUNTIF($N$25:$N$36,"<="&N25)则按升序排列。 如有第五名和第六名相等时,有两个第六名(没有第五名),则下一个显示第七名。 2、在欲显示排名的列输入=COUNTIF($N$25:$N$36,">"&N25)+1则按降序排列,同上,改用小于号<即可。 如有第五名和第六名相等时,有两个第五名(没有第六名),则下一个显示第七名。 方法三: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)/COUNTIF($D$1:$D$20,$D$1:$D$20)) ,其中SUMPRODUCT(array1,array2,array3, ...)在给定的几组数组中,将数组间对应的元素相乘,并返回乘积之和,COUNTIF(range,criteria)计算区域range中满足给定条件criteria的单元格的个数。应该是计算满足大于或等于D1个数,如果按数值最低为第1名即升序排列,则($D$1:$D$20>=$D1)替换为($D$1:$D$20<=$D1)即可,比较方便,但是好像只能显示纯数值的排名。 附以上几个函数的帮助信息参考: 一、SUMPRODUCT(array1,array2,array3, ...) 在给定的几组数组中,将数组间对应的元素相乘,并返回乘积之和。 Array1, array2, array3, ... 为2 到30 个数组,其相应元素需要进行相乘并求和。 说明 数组参数必须具有相同的维数,否则,函数SUMPRODUCT 将返回错误值#V ALUE!。 函数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、"32"、">32" 、"apples" 或B4。 说明 Microsoft Excel 还提供了其他一些函数,它们可根据条件来分析数据。 若要计算基于一个文本字符串或某范围内的一个数值的总和,可使用SUMIF 工作表函数。若要使公式返回两个基于条件的值之一,例如某指定销售金额的销售红利,可使用IF 工作表函数。 若要计算空或非空单元格的个数,可使用COUNTA 和COUNTBLANK 函数。 三、LOOKUP 从单行或单列区域或者从一个数组(数组:用于建立可生成多个结果或可对在行和列中排列 的一组参数进行运算的单个公式。数组区域共用一个公式;数组常量是用作参数的一组常量。)返回值。LOOKUP 函数具有两种语法形式: 1、向量LOOKUP 的向量形式在单行区域或单列区域(称为“向量”)中查找值,然后返回第二个单行区域或单列区域中相同位置的值。 当要查询的值列表较大或者值可能会随时间而改变时,使用该向量形式。 向量是只含一行或一列的区域。LOOKUP 的向量形式在单行区域或单列区域(称为“向量”)中查找值,然后返回第二个单行区域或单列区域中相同位置的值。当要指定的区域包含要匹配的值时,请使用LOOKUP 函数的这种形式。LOOKUP 的另一种形式自动在第一行或第一列中查找。 LOOKUP(lookup_value,lookup_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 会提供#N/A 错误值。 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 会返回#N/A 错误值。 数组包含要与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。