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

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


方法一:RANK 应在总分后面的单元格输入如:=rank(E2,E$2:E12) 就可以了 这个方法最简单,直接使用 rank 函数,如有第五名和第六名相等时,有两个第五名,则下 一个显示第七名(没有第六名) 。 在 欲 显 示 排 名 的 列 输 入 =RANK(D25,$D$25:$D$36) =RANK(D25,$D$25:$D$36,1)则按升序排列。 方法二:COUNT

IF 方法简单,直接使用 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中按某组数据按大小进行自动排序排名(RANK、COUNTIF、LOOKUP、SUMPRODUCT)
excel中按某组数据按大小进行自动排序排名(RANK、COUNTIF、LOOKUP、SUMPRODUCT)_电脑基础知识_IT/计算机_专业资料。excell学习excel 中按某组数据按大小进行自动排序...
大学计算机第四章练习题
删除图表中某数据系列,源 数据表中对应的行数据不会自动删除 参考答案:A ...在 Excel 2003 中可以依据字母、数字或日期等数据类型按一定顺序进行排列,这种...
运用EXCEL实现数据的自动排列
图4 4、然后在排名表中分别输入名次,如图 5 所示。 图5 5、对排序数据进行...excel中按某组数据按大小... 8页 1下载券 在Excel中给数据排序的常... 2...
继续教育考试 实用软件应用技巧 90分钟 81分
“格式/自动套用格式”命令 (D) “数据/排序”...大小 (B) 文字添加边框,设置透明度、对齐方位 (C)...Excel某数据清单中,()操作可以将A列与B列数据交换...
文字版 excel试题答案
若需要将工作表中某列上大于某个值的记录挑选出来,...在样表中按总分进行降序排序,先选中“总分”单元格...因为参加排序的单元格不具有相同大小 19、排序:在...
在excel中如何同时把多列数据同时排序
方法一:在 Excel 2003 中,先对第一列数据执行“数据”——“排序”——按...输入的公式字母大小写要按照要求。 方法五:你这样的排序用操作是无法自动做到的...
excel中对学生成绩自动排名
(老师掌握),一般的做法是将成绩排序(降序)后, 将第一名和第二名手工填入 1...EXCEL分数排名 9页 免费 excel中按某组数据按大小... 8页 免费 在EXCEL...
excel中多行多个单元格打乱顺序排列的数字如何自动从小到大顺序排列
[ 标签:excel, 大小顺序, 从小到大 ] 匿名 2011-05-03 22:28 问题补充: 不是按列或者是按行排列,是列或者行里面的单独的几个数字从小到大排列 满意答案 ...
EXCEL复习题(答案)
一含标题行的工作表进行排序,当在"排序"对话框中...自动筛选和高级筛选都可以将结果筛选至另外的区域中....在 Excel 的数据清单中,若根据某列数据对数据清单进行...
更多相关标签:
excel按排名排序 | excel排名排序 | excel按数字大小排序 | excel数字大小排序 | excel大小排序 | excel按数值大小排序 | excel按大小排序 | excel按大小排序函数 |