Excel做成绩表时自动实现同分同名次

Excel做成绩 表 关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf 时自动实现同分同名次 2007-08-24 09:57:18　来源: 天极　网友评论 0 条 进入论坛 在用Excel统计成绩时，我们一般会根据成绩高低进行排序，然后按序列自动填充出名次。这种方法得出的名次与总分没有关联，即使成绩相同，也会填充出不同的名次。 如果数据较少，我们可以采用手动的方法将成绩相同的人员改成相同的名次，但数据较多时就很麻烦了。经过实践，以下三种方法可以自动实现同分同名次的操作。假设有1000名考生，字段位置如上图所示。 一、使用排位函数RANK 在H2单元格中输入函数“=RANK(G2,G$2:G$1001,0)”，回车后H2单元格中会出现名次“1”，然后选定H2，拖动其填充柄向下填充即可自动实现。 该函数的功能是返回一个数字(上述函数中的G2，此处采用相对应用，填充时随行的变化而变化)在数字列表(上述函数中的G$2:G$1001，此处采用绝对应用，填充时不发生变化)中的排位。数字的排位是其大小与列表中其他值的比值。该函数在使用时，即使总分没有排序，它也可以直接求出所对应总分的名次;如果总分已排过序，则数字的排位就是它当前的位置。上述函数中最后的“0”指明排位的方式，当其为 0或省略时，Excel 对数字的排位是按照降序排列的。 如果不为零， Excel 对数字的排位则是按照升序排列的。该函数对重复数的排位相同，而下一名次则将前面的重复数计算在内。 二、使用计数函数COUNTIF 在H2单元格中输入函数“=COUNTIF(G$2:G$1001,">"&G2)+1”，然后向下自动填充即可实现该操作。 该函数的功能是计算区域中满足给定条件的单元格个数。上述函数中采用绝对引用指定计算区域G$2:G$1001，名次所对应的单元格G2采用相对引用。即求出在G2:G1001区域中大于名次所对应总分的单元格的个数，然后加上1就可以得出该单元格中数值的名次。该操作不管有几个重复的，大于它的个数却是固定的，所以重复数据的名次也就是相同的了。同RANK函数一样，该函数不管是否已经按总分进行排序均可进行上述操作。 三、采用IF语句加COUNT函数 该操作与上述两种方法不同，必须先将总分按照降序排列。然后将第一名的名次“1”输入，再在H3单元格中输入函数“=IF(G3=G2,H2,COUNT($G$2:G3))”，确定后向下自动填充也可实现同分同名次的操作。 COUNT函数的功能是返回指定区域中数字单元格的个数。因为G列全是数字单元格，所以该总分处在第几位上名次就是几。上述语句的意思就是如果当前总分和上一个总分相同，则输入上一个总分的名次，否则执行COUNT语句，统计个数。 以上三种方法均可自动实现同分同名次操作。本文虽然有孔乙己先生关于“回”字有四种写法的嫌疑，但笔者的目的却只有一个，那就是抛砖引玉，让Excel函数更好的为我们服务。 使用Excel按成绩分数统计出同分数的相同排名 一般情况，我们是这样来实现的！ 在使用Excel统计成绩时，我们一般会根据成绩高低进行排序，然后按序列自动填充出名次。这种方法得出的名次与总分没有关联，即使成绩相同，也会填充出不同的名次。 如果数据较少，我们可以采用手动的方法将成绩相同的人员改成相同的名次，但数据较多时就很麻烦了。 很明显，上述做法是不科学的，即，相同分数排名却不同，这对学生来生是不公平的！也是不符合实际情况的！ 那么我们如何才能做到如题所示呢，本例介绍三种方法，择其一而使用！ 一、使用排位函数RANK 假设存在如下图的数据，有地理，政治，历史，总分，现在我们按总分来进行排名；假设有7个学生的成绩； 在K2单元格中输入函数“=RANK(J2,J$2:J$8,0)”，回车后K3单元格中会出现名次“2”；如下图 接着选定K2，双击其填充柄向下填充即可自动实现，如下图： 该函数的功能是返回一个数字(上述函数中的J2，此处采用相对应用，填充时随行的变化而变化)在数字列表(上述函数中的J$2:J$8，此处采用绝对应用，填充时不发生变化)中的排位。数字的排位是其大小与列表中其他值的比值。该函数在使用时，即使总分没有排序，它也可以直接求出所对应总分的名次;如果总分已排过序，则数字的排位就是它当前的位置。上述函数中最后的“0”指明排位的方式，当其为 0或省略时，Excel 对数字的排位是按照降序排列的。 如果不为零， Excel 对数字的排位则是按照升序排列的。该函数对重复数的排位相同，而下一名次则将前面的重复数计算在内。 二、使用计数函数COUNTIF 在J2单元格中输入函数"=COUNTIF(J$3:J$8,">"&J2)+1”，然后向下自动填充即可实现该操作。方法同上； 该函数的功能是计算区域中满足给定条件的单元格个数。上述函数中采用绝对引用指定计算区域J$3:J$8，名次所对应的单元格J2采用相对引用。即求出在J3:J8区域中大于名次所对应总分的单元格的个数，然后加上1就可以得出该单元格中数值的名次。该操作不管有几个重复的，大于它的个数却是固定的，所以重复数据的名次也就是相同的了。同RANK函数一样，该函数不管是否已经按总分进行排序均可进行上述操作。 三、采用IF语句加COUNT函数 该操作与上述两种方法不同，必须先将总分按照降序排列。 这种大家自己动手试试看吧！ · excel统计学生成绩时的四个难题及解决_excel教程 · 来源：作者： 发布时间:2007-12-19 18:03:55 · 域名注册 · 域名惊喜价格 cn域名1元注册 · com域名39.9元 虚拟主机 · 主机按月支付,低至19元/月 · 超大流量,可开子站点 VPS主机 · 特惠VPS168元/月,4-8M独享带宽保证 · 独立操作系统,无限开站点 　　一、前言 　　对于教师而言，经常需要用Excel进行学生成绩统计，会被一些常见问题难住。碰到的难题主要有：怎么统计不同分数段人数、怎么在保持学号不变前提下进行排名、怎么将百分制转换成不同分数段和怎么用红色显示不及格的分数等，本文着重对这些问题的解决方法和技巧加以分析和讨论。 　　本文假设读者已对Excel的基本操作已有一定基础，已掌控怎么进行求和、求平均和怎么使用自动填充柄进行复制公式等操作，本文对这些一般性操作不做周详介绍，仅对一些难度较大的操作技巧进行讨论。 　　二、Excel统计学生成绩时的四个难题 　　假设在统计学生成绩时，我们需要统计出如图1所示的相关结果。 在 图1　学生成绩统计所需要的结果图 　　这里，假设学号、姓名、成绩等列及行15都已事先输好，需要让Excel统计其他的相关数据结果。这时，成绩统计中主要难解决的问题及他们在图中的位置如下： 　　问题1：怎么统计不同分数段的学生人数?(图中A16~E16) 　　问题2：怎么在保持学号顺序不变的前提下进行学生成绩名次排定?(图中F2~F13) 　　问题3：怎么将百分制转换成不同的等级分?(图中“等级1”和“等级2”列) 　　问题4：怎么使不及格的分数以红色显示?(图中红色显示部分，即第12行)   　　三、解决统计学生成绩时的四个难题的方法 　　下面，针对上面提出的四个难题分别讨论解决的方法和技巧。 　　1、统计不同分数段的学生人数 　　统计不同分数段的学生人数是非常常见的需求，其所需结果如图1中A16~E16所示。这里，假设需要统计90~100、80~89、70~79、60~69及低于60分五个不同分数段的人数。 　　通常，统计不同分数段最佳的方法是利用COUNTIF(X，Y)函数。其中有两个参数，第一个参数X为统计的范围，一般最佳用绝对引用;第二个参数Y为统计条件，要加引号。 　　对于小于60分的人数只要用一个COUNTIF( )函数，如在E16单元格中输入公式：=COUNTIF($C$2:$C$13,"<60")。 　　对于其他在两个分数之间的分数段的人数统计，需要用两个COUNTIF( )函数相减。如在A16单元格中输入公式：=COUNTIF($C$2:$C$13,"<=100")-COUNTIF($C$2:$C$13,"<90")，即用小于等于100的人数减去小于90的人数。 　　如果要统计80~89、70~79和60~69分数段的人数，只要利用自动填充柄将该公式复制到右边三个单元格，再把"<=100"和"<90"作相应的修改，就能得到正确的结果。 　　2.保持学号顺序不变的前提下进行成绩排名 　　学生成绩排定在学生成绩统计中经常用到。特别要强调的是，这里所谈的方法不是一般的排序，因为那样会使学生的学号顺序发生变化。这里所需要的是在保持学号顺序不变的情况下进行学生成绩名次排定的功能，其所需结果如图1中F2~F13所示。 　　要进行保持学号顺序不变的情况下进行学生成绩名次的排定，最佳使用RANK(X，Y，Z)函数。其中有三个参数，第一个参数X为某个学生的成绩所在单元格;第二个参数Y为整个班级成绩所在的区域;第三个参数Z是可选的，表示统计方式，若省写或写0，则成绩高的名次靠前，一般都使用这种方式，如果写1，则成绩高的名次靠后，这种情况一般较少用。 　　为了在保持学号顺序不变的前提下进行学生成绩名次排定，能在F2单元格中输入公式：=RANK(C2,$C$2:$C$13,0)，然后，利用自动填充柄将其复制到下方的几个单元格。注意，这里$C$2:$C$13用的是绝对地址，是为了确保公式在复制时此处不变，因为作为第二个参数，这里都是指整个班级成绩所在的区域，这个区域是相同的。 　　3、将百分制转换成不同的等级分 　　将百分制转换成不同的等级分有多种不同的划分方法，其所需结果如图1中“等级1”和“等级2”列所示。这里，“等级1”列是将百分制的分数转换成A(90~100)、B(80~89)、C(70~79)、D(60~69)和E(低于60)五个等级;“等级2”列是将百分制的分数转换成优(90~100)、良(75~89)、中(60~74)和不及格(低于60)四个等级。具体使用哪种等级划分方法可根据实际情况自己确定。 　　在百分制转换成不同的等级分时，一般使用IF(X，Y，Z)函数。其中有三个参数，第一个参数X为条件，不能加引号;第二个参数为条件成立时的结果，如果是显示某个值，则要加引号;第三个参数为条件不成立时的结果，如果是显示某个值，同样要加引号。该函数能嵌套，即在第二个或第三个参数处能再写一个IF函数。 　　为了得到“等级1”列所要的等级结果，能在D2单元格中输入公式：=IF(C2>=90,"A",IF(C2>=80,"B",IF(C2>=70,"C",IF(C2>=60,"D","E"))))，然后，利用自动填充柄将其复制到下方的几个单元格。 　　为了得到“等级2”列所要的等级结果，能在E2单元格中输入公式：=IF(C2>=90,"优",IF(C2>=75,"良",IF(C2>=60,"中","不及格")))，然后，利用自动填充柄将其复制到下方的几个单元格。 　　4、使不及格的分数以红色显示 　　统计学生成绩时经常需要将不及格的分数用红色显示，其结果如图1中红色显示部分(如第12行)。 　　使不及格的分数以红色显示需要使用“格式”菜单中的“条件格式”命令。该命令会弹出一个对话框，其中需求确认条件和相应的格式。 　　对于“成绩”列，可先选中C2：C13，然后使用“格式”菜单中的“条件格式”命令，在弹出的对话框中，左边使用默认的“单元格数值”，中间选“小于”，右边填写60，然后单击右边的“格式”按钮，从中选择红色，最后单击两次“确定”按钮。 　　对于“等级1”列，可先选中D2：D13，然后使用“格式”菜单中的“条件格式”命令，在弹出的对话框中，左边使用默认的“单元格数值”，中间选“等于”，右边填写E，然后单击右边的“格式”按钮，从中选择红色，最后单击两次“确定”按钮。“等级2”列类似。 　　对于其他的一些统计计算需求，如怎样计算各分数段的百分比、怎么计算机优良率和合格率等功能，应该比较简单，本文此处不赘述。