当前位置:首页 >> 数学 >>

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


方法一: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,">="&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 将返回错误值 #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、 "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。 此外还可参考: 1、Excel 排序技巧大整理 2、用 Excel 做数据排序的常用方法与技巧

运行 Excel2003,在成绩表第一行中输入以下内容:学号、姓名、各考试科目名称、总分、 名次,输入学号时,用填充柄自动填充。在学号下方依次输入“总人数”“及格人数”“及 、 、 格率”“总分”“均分”“最高分”“最低分” 、 、 、 、 。 各段成绩格式不同 选定所有录入学生成绩的单元格,进入“格式→条件格式” ,在“单元格数值”范围中填入 “80”与“100” ,单击“格式”按钮,将单元格数值设置为自己喜欢的格式:如数值颜色为 蓝色、 字形为倾斜等, 这样, 80 到 100 之间的分数在输入后自动变为蓝色并倾斜。 在 确定, 返回条件格式对话框,单击“添加” ,设置 60 到 79、小于 60 分等分数段的格式。 个人总分

单击总分下的单元格,在编辑栏中输入=SUM(C2:H2),在下一个单元格中输入=SUM(C3:H3), 然后选定这两个单元格,将光标移动到两个单元格的右下角,到变成细十字,按住鼠标左键 向下拖拉至最后一名学生,以后只要输入学生各科成绩,每位学生的总分即自动求出。 个人名次 在 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,">=72") , 在 “ 及 格 率 ” 单 元 格 中 输 入 =COUNTIF(C3:C59,">=72")/COUNT(C3:C59),[满分为 120 分为">=72",若满分为 100 分则为 ">=60"], “总分”单元格中输入=SUM(C3:C59), “平均分”单元格中输入=AVERAGE(C3:C59), “最高分”输入=MAX(C3:C59), “最低分”单元格输入=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+Enter”组合键进行 确认,即可求出 c67 至 c75 区域中,按 A67:A74 区域进行分隔的各段数值的出现频率数目。 其他单元格可用自动填充功能实现。 有时要将优秀人数和不及格人数统计出来,在语文优秀人数的单元格中输入公式 =COUNTIF(C3:C59,“>=80”)(假设各科的满分为 100 分)。及格人数改为>=60,不及格人数改 为<=59 即可。这样 Excel 就自动统计出各分数段人数了。 保存为模板文件 在“打印预览”中,按下“设置” ,单击“工作表”标签,选中打印风格线,单击“页眉/ 页脚”标签,单击“自定义” ,设置表格标题、页眉、页脚、纸张的大小、页边距,调整字 体、字号、单元格的宽度及高度,然后“另存为”为“模板” ,取名为“成绩统计” ,确定, 保存为模板文件。

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


相关文章:
excel中对学生成绩自动排名
excel中对学生成绩自动排名_其它课程_初中教育_教育...对 该列数据按升序排名)然后按下回车键,第一位...一般我们会按照成绩总分来做倒序排序, 然后再用下来...
在Excel中给数据排序的常用方法与技巧
Excel中数据排序的常用方法与技巧_IT/计算机_专业资料。在 Excel 中数据...一、快速排序 如果我们希望对员工资料按某列属性(如“工龄”由长到短)进行排列...
在excel中如何同时把多列数据同时排序_图文
通过以下步骤对图 10-2 中的数据进行列排序。 ...方法五:你这样的排序用操作是无法自动做到的,还是用...excel中按某组数据按大小... 8页 免费 Excel数据...
在Excel中利用数据的排序功能可以很轻松地进行排序
Excel中利用数据排序功能可以很轻松地进行排序_计算机软件及应用_IT/计算机_...一小十字符号的时候,按住鼠标左键 向下拉动,则此列的单元格中会自动加上...
运用EXCEL实现数据的自动排列
图4 4、然后在排名表中分别输入名次,如图 5 所示。 图5 5、对排序数据进行...excel中按某组数据按大小... 8页 1下载券 在Excel中给数据排序的常... 2...
excel排序—相同的数值排序相同
成绩相同,有相同的排名,根据已有的成绩进行排序,相同的数值拥有相同的排名序列 ...excel中按某组数据按大小... 8页 1下载券 EXCEL分数排名 9页 1下载券 ...
在Excel的数据清单中,若根据某列数据对数据清单进行排...
Excel的数据清单中,若根据某列数据对数据清单进行排序,可以利用工具栏上的“降序”按钮,此时应先( )。 A.选取该列数据 B.单击该列数据中任一单元格C.选取...
excel中多行多个单元格打乱顺序排列的数字如何自动从小...
等等很多行数据 要求自动每行按从小到大自然顺序自动生成排列,如 A1 经过自动...转载到空间 转播到腾讯微博 Excel 里面怎样才能把单独的几个按大小顺序自动排列...
在Excel的数据清单中,若根据某列数据对数据清单进行排...
Excel的数据清单中,若根据某列数据对数据清单进行排序,可以利用工具栏上“降序”按钮,此时应先( )。 A.选取该列数据 B.单击该列数据中任意单元格C.选取整个...
在Excel的数据清单中,若根据某列数据对数据清单进行排...
Excel的数据清单中,若根据某列数据对数据清单进行排序,可以利用工具栏上“降序”按钮,此时应先( )。 A.选取该列数据 B.单击该列数据中任意单元格C.选取整个...
更多相关标签: