阿松公式excel中按某组数据按大小进行自动排序排名

2020年9月24日发(作者：池寅)
方法一：RANK

应在总分后面的单元格输入如：=rank(E2,E$$2:E12) 就可以了
这个方法最 简单，直接使用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（随便找一个临时列，如果看着不爽干脆做好后直接隐藏）中输入
=SMA LL($$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

这个方法比较简单，只要一个公式即可，但偶不明 白他是怎么计算的，缺点是排名显示叫单
一，如有第五名和第六名相等时，有两个第五名，则下一个依然 显示第六名，导致最后结果
少一个，具体：

在欲显示排名的列
=SUMP RODUCT(($$D$$1:$$D$$20>=$$D1)COUNTIF($$D$$1:$$D$$20,$$D$$1:$$D $$20))
输
，其
入
中
SUMPRODUCT(array1,a rray2,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 将返回错误值 #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_va lue,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 会提供 #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。

此外还可参考：

1、Excel排序技巧大整理

2、用Excel做数据排序的常用方法与技巧



运行Exc el2003，在成绩表第一行中输入以下内容：学号、姓名、各考试科目名称、总分、
名次，输入学号 时，用填充柄自动填充。在学号下方依次输入“总人数”、“及格人数”、“及
格率”、“总分”、“均 分”、“最高分”、“最低分”。

各段成绩格式不同

选定所有录入学 生成绩的单元格，进入“格式→条件格式”，在“单元格数值”范围中填入
“80”与“100”，单击 “格式”按钮，将单元格数值设置为自己喜欢的格式：如数值颜色为
蓝色、字形为倾斜等，这样，在80 到100之间的分数在输入后自动变为蓝色并倾斜。确定，
返回条件格式对话框，单击“添加”，设置6 0到79、小于60分等分数段的格式。

个人总分

单击总分下的单元 格，在编辑栏中输入=SUM(C2:H2)，在下一个单元格中输入=SUM(C3:H3)，
然后选 定这两个单元格，将光标移动到两个单元格的右下角，到变成细十字，按住鼠标左键
向下拖拉至最后一名 学生，以后只要输入学生各科成绩，每位学生的总分即自动求出。

个人名次
< br>在Excel中可以用单元格自动排序的方式给成绩排序，但不好处理同分数的名次，利用RANK
函数则没有这样的问题。

方法是：总分位于I列，名次位于J列，在第一名学生的名次单 元格中输入=RANK(I3,$$I$$3:$$I$$59)，
该公式的目的是排出I3单元格的数据从I2 到I59所有数据中的位次(假设该班共56名学生，
学生的姓名、成绩等数据从第3行排到第59行) 。在第二名学生的名次单元格中输入
=RANK(I4,$$I$$3:$$I$$59)，然后拖动鼠标，选中 这两个单元格，利用自动填充的功能，在每个学
生的名次单元格中输入RANK函数的公式。这样只要将 学生各科成绩输入到对应的单元格中
后，RANK函数即自动求出该学生的名次，同分数的名次则自动排 为相同的名次，下一个名
次数值自动空出。

各科逐一分析

以 “语文”为例。在“总人数”单元格中输入公式=COUNT(C3:C59)，在“及格人数”单元格
中输入=COUNTIF(C3:C59,，在“及格率”单元格中输入
=COUNTIF(C3:C5 9,，[满分为120分为，若满分为100分则为
，“总分”单元格中输入=SUM(C3:C59) ，“平均分”单元格中输入=AVERAGE(C3:C59)，
“最高分”输入=MAX(C3:C5 9)，“最低分”单元格输入=MIN(C3:C59)。以上公式输入后可利
用自动填充功能填充其他 各科的公式。

每科各分数段人数

可用FREQUENCY函数来实现 。在（A67：A74）区域输入39、49、59、69、79、89、99、109
（即统计出40 分以下~120分范围内组距为10的各分数段人数），同时选中c67至c75单元
格区域，输入公式 :=FREQUENCY(C3:C59,$$A$$67:$$A$$74)，按下“Ctrl+Shift+Ente r”组合键进行
确认，即可求出c67至c75区域中，按A67:A74区域进行分隔的各段数值的出 现频率数目。
其他单元格可用自动填充功能实现。

有时要将优秀人数和不及格人数 统计出来，在语文优秀人数的单元格中输入公式
=COUNTIF(C3:C59,“>=80”)(假 设各科的满分为100分)。及格人数改为>=60，不及格人数改
为<=59即可。这样Excel就 自动统计出各分数段人数了。

保存为模板文件

在“打印预览”中，按 下“设置”，单击“工作表”标签，选中打印风格线，单击“页眉
页脚”标签，单击“自定义”，设置表 格标题、页眉、页脚、纸张的大小、页边距，调整字
体、字号、单元格的宽度及高度，然后“另存为”为 “模板”，取名为“成绩统计”，确定，
保存为模板文件。

最后，我们就可以使用已建立的模板来处理学生的成绩。

启动Excel，执行“ 文件”菜单中的“新建”，在对话框中出现的“常用”标签下找到已建立
的“成绩统计”模板，双击，出 现了原来的模板文件，输入学生新的成绩总分、各科的平均
分、各分数段的人数，最高分、各学生的总分 名次即自动统计得出。最后打印输出，既美观
又便于分析。