考试成绩汇总函数

《考试成绩汇总函数》由会员分享，可在线阅读，更多相关《考试成绩汇总函数（57页珍藏版）》请在金锄头文库上搜索。

1、考试结束，各学科考试成绩迅速汇总到班主任这里。这时候，班主任最忙的，就是要迅速统计各学科成绩的各项指标，比如平均分、最高分、优秀率、及格率以及各学科分数的频率统计等等。虽然现在普遍都在使用 Excel 进行这项工作，不过，要想使这项工作能够高效准确地完成，那还得请 Excel 函数来帮忙才行。先点击 M3 单元格，输入如下公式：=AVERAGE(C2:C95)，回车后即可得到语文平均分。点击 M4 单元格，输入公式：=MAX(C$2:C$95)，回车即可得到语文成绩中的最高分。优秀率是计算分数高于或等于 85 分的学生的比率。点击 M5 单元格，输入公式：=COUNTIF(C$2:C$95,“

2、=85“)/COUNT(C$2:C$95)，回车所得即为语文学科的优秀率。点击 M6 单元格，输入公式：=COUNTIF(C$2:C$95,“=60“)/COUNT(C$2:C$95)，回车所得即为及格率。选中 M3:M6 单元格，拖动填充句柄向右填充公式至 Q6 单元格，松开鼠标，各学科的统计数据就出来了。再选中 M5:Q6 单元格区域，点击菜单命令“格式单元格”，打开“单元格格式”对话框。点击“数字”选项卡，在左侧“分类”列表中选择“百分比”，如图 3 所示，确定后可将 M5:Q6 单元格区域的数据转变成百分比形式。至于各科分数段人数的统计，那得先选中 M8:M15 单元格，在编辑栏中输入

3、公式：=FREQUENCY(C$2:C$95,$K$8:$K$15)。然后按下“CtrlShiftEnter”快捷键，可以看到在公式的最外层加上了一对大括号。现在，我们就已经得到了语文学科各分数段人数了。在 K列中的那些数字，就是我们统计各分数段时的分数分界点。现在再选中 M8:M15 单元格，拖动其填充句柄向右至 Q 列，那么，其它学科的分数段人数也立即显示在我们眼前了。如果觉得 K 列的数据有碍观瞻，那么可以选中它们，然后设置它们的字体颜色为白色就可以了。 一、前言 对于教师而言，经常需要用 Excel 进行学生成绩统计，会被一些常见问题 难住。碰到的难题主要有：如何统计不同分数段人数、如

4、何在保持学号不变前 提下进行排名、如何将百分制转换成不同分数段与如何用红色显示不及格的分 数等，本文着重对这些问题的解决方法与技巧加以分析和讨论。本文假设读者已对 Excel 的基本操作已经有一定基础，已经掌握如何进行 求和、求平均和如何使用自动填充柄进行复制公式等操作，本文对这些一般性 操作不做详细介绍，仅对一些难度较大的操作技巧进行讨论。二、二、ExcelExcel 统计学生成绩时的四个难题统计学生成绩时的四个难题假设在统计学生成绩时，我们需要统计出如图 1 所示的相关结果。图 1 学生成绩统计所需要的结果图这里，假设学号、姓名、成绩等列及行 15 都已经事先输好，需要让 Excel 统计

5、其他的相关数据结果。这时，成绩统计中主要难解决的问题及它们在图中 的位置如下：问题 1：如何统计不同分数段的学生人数?(图中 A16E16)问题 2：如何在保持学号顺序不变的前提下进行学生成绩名次排定?(图中 F2F13)问题 3：如何将百分制转换成不同的等级分?(图中“等级 1”与“等级 2” 列)问题 4：如何使不及格的分数以红色显示?(图中红色显示部分，即第 12 行)三、解决统计学生成绩时的四个难题的方法三、解决统计学生成绩时的四个难题的方法下面，针对上面提出的四个难题分别讨论解决的方法与技巧。1 1、统计不同分数段的学生人数、统计不同分数段的学生人数统计不同分数段的学生人数是非常常见

6、的需求，其所需结果如图 1 中 A16E16 所示。这里，假设需要统计 90100、8089、7079、6069 及低于 60 分五个不同分数段的人数。通常，统计不同分数段最好的方法是利用 COUNTIF(X，Y)函数。其中有两 个参数，第一个参数 X 为统计的范围，一般最好用绝对引用;第二个参数 Y 为统 计条件，要加引号。对于小于 60 分的人数只要用一个 COUNTIF( )函数，如在 E16 单元格中输 入公式：=COUNTIF($C$2:$C$13,“= 90,“A“,IF(C2=80,“B“,IF(C2=70,“C“,IF(C2=60,“D“,“E“)，然 后，利用自动填充柄将其复

7、制到下方的几个单元格。为了得到“等级 2”列所要的等级结果，可以在 E2 单元格中输入公式： =IF(C2=90,“优“,IF(C2=75,“良“,IF(C2=60,“中“,“不及格“)，然后，利用 自动填充柄将其复制到下方的几个单元格。4 4、使不及格的分数以红色显示、使不及格的分数以红色显示统计学生成绩时经常需要将不及格的分数用红色显示，其结果如图 1 中红 色显示部分(如第 12 行)。使不及格的分数以红色显示需要使用“格式”菜单中的“条件格式”命令。 该命令会弹出一个对话框，其中要求确认条件与相应的格式。对于“成绩”列，可先选中 C2：C13，然后使用“格式”菜单中的“条件 格式”命令

8、，在弹出的对话框中，左边使用默认的“单元格数值”，中间选 “小于”，右边填写 60，然后单击右边的“格式”按钮，从中选择红色，最后 单击两次“确定”按钮。对于“等级 1”列，可先选中 D2：D13，然后使用“格式”菜单中的“条件 格式”命令，在弹出的对话框中，左边使用默认的“单元格数值”，中间选 “等于”，右边填写 E，然后单击右边的“格式”按钮，从中选择红色，最后 单击两次“确定”按钮。“等级 2”列类似。对于其他的一些统计计算要求，如怎样计算各分数段的百分比、如何计算 机优良率与合格率等功能，应该比较简单，本文此处不赘述。用 Excel 函数轻松搞定学生成绩等级评定转 2009-01-31

9、 02:42 Excel 函数可以帮助各位老师用字母 A、B、C 给学生打成绩。我们定的标准是 80 分以上为 A，60 分至 79 分为 B，60 分以下为 C。下面我们看如何实现这一 效果。 一、利用公式计算等级：一、利用公式计算等级：在 Excel 中把成绩录入完成，然后在表头行的最后边再添加上一个“等级” 列。在 F2 单元格中输入：=CONCATENATE（IF （C2=80，“A“，IF（C2=60，“B“，“C“）， IF（D2=80，“A“，IF（D2=60，“B“，“C“）， IF（E2=80，“A“，IF（E2=60，“B“，“C“），然后把鼠标指针指向 F2 单 元格的右

10、下角，等鼠标指针变成黑色十字加号时，按住左键向右拖动到这列单 元格的最后放手。（如图）输入公式也可以在 F2 单元格中输入： =IF（C2=80，“A“，IF（C2=60，“B“，“C“）value_if_true：表示 logical_test 为 TRUE(也就是真或正确)时返回的值;value_if_false：表示 logical_test 为 FALSE(也就是假或错误)时返回的值。 而本例中的公式 IF(C2=80,“A“,IF(C2=60,“B“,“C“)意思是如果 C2 单元格中的语文分数大于或等于 80 分，则在 F2 单元格中显示字母 A，而如果小于 80 分则再重新判断是

11、否大于等于 60 分，如果大于或等于 60 分则在 F2 单元格中显示字母 B，而如果小于 60 分则显示字母 C。 CONCATENATE 是将若干个文字项合并至一个文字项中的函数。其语法格式是：CONCATENATE(text1,text2)。而text2 和numb2 是指 IF(D2=80,“A“,IF(D2=60,“B“,“C“)，即数学这列成绩的等级;text3 和 numb3 是指 IF(E2=80,“A“,IF(E2=60,“B“,“C“)，即英语这列成绩的等级。期末考试结束后，我们都需要对各科成绩进行分析、统计，流程包括录入各 科成绩计算总分、平均分并排定名次统计各科分数段人

12、数、及格率、优秀 率及综合指数打印各种统计报表制作各科统计分析图表等。有了 EXCEL， 我们可用不着躬着身、驼着背、拿着计算器一个一个算着学生的成绩了！启动 EXCEL2000，录入学生的考试成绩，如图 1 所示。然后在 J2 单元格处 输入公式“=sum(c2:i2)“,然后拖动填充柄向下填充，便得到了每人的总分。接着在 k2 单元格处输入公式“=average(c2:i2)“，然后拖动填充柄向下填充，便 得到了每人的平均分。 图 1平均分只需保留一位小数，多了没用。所以选中第 k 列，用鼠标右键单击 ，从弹出的快捷菜单中选“设置单元格格式（F）“,如图 2 所示，在数字标签 中选中“数值

13、“，小数位数设置为 1 位。 图 2下面按总分给学生排出名次。在 L2 单元格处输入公式“RANK(J2,J$2:J$77,0)“,然后拖动填充柄向下填充 ，即可得到每人在班中的名次（请参考图 1）。 说明：此处排名次用到了 RANK 函数，它的语法为：RANK(number,ref,order)其中 number 为需要找到排位的数字。(即你准备对哪个单元格进行排名）Ref 为包含一组数字的数组或引用。Ref 中的非数值型参数将被忽略。（ 排名区域，注意绝对符号$ 的使用） Order 为一数字，指明排位的方式。 如果 order 为 0 或省略，Microsoft Excel 将 ref

14、当作按降序排列的 数据清单进行排位。如果 order 不为零，Microsoft Excel 将 ref 当作按升序排列的数据 清单进行排位。 最后，单击 L1 单元格，然后在“工具”菜单中选“排序”“升序”， 即可按照名次顺序显示各学生成绩。 另外，我们还希望把不及格的学科突出显示，最好用红色显示。于是拖拉 选择 C2:E78(即所有学生语、数、外三科成绩)，然后执行“格式“菜单下“条件 格式“命令，弹出“条件格式对话框“。我们把条件设为小于 72 分的用红色显示 （因为这三科每科总分为 120 分），点击“格式“按钮，把颜色设为红色。再按“ 确定“按钮。然后用同样的方法把理、化、政、历四科

15、小于 60 分的也用红色显 示（因为这四科每科总分为 100 分）。下面我们来统计各科的分数段以及及格率、优生率、综合指数等。下面我们来统计各科的分数段以及及格率、优生率、综合指数等。（1）60 分以下人数：在 C78 单元格处输入公式“=COUNTIF(C2:C77,“=60 “)-COUNTIF(C2:C77,“=70“)“，拖动填充柄向右填充；（3）70 分79 分人数：在 C80 单元格处输入公式“=COUNTIF(C2:C77,“=70 “)-COUNTIF(C2:C77,“=80“)“，拖动填充柄向右填充；（4）80 分89 分人数：在 C81 单元格处输入公式“=COUNTIF(

16、C2:C77,“=80 “)-COUNTIF(C2:C77,“=90“)“，拖动填充柄向右填充；（5）90 分以上人数：在 C82 单元格处输入公式“=COUNTIF(C2:C77,“=90“)“ ，拖动填充柄向右填充；（6）平均分：在 C83 单元格处输入公式“=AVERAGE(C2:C77)“,拖动填充柄 向右填充至 I83；（7）最高分：在 C84 单元格处输入公式“=MAX(C2:C77)“,拖动填充柄向右 填充至 I84；（8）低分率：是指各科 40 分以下人数与总人数的比值。在 C85 单元格处 输入公式“=COUNTIF(C2:C77,“=72“)/COUNT(C2:C77)*100“，并拖动填充柄向右填 充至 E86；而理、化、政、历等四科及格分 60 分，所以在 F86 单元格处输入公 式“=(COUNTIF(F2:F77,“=60“)/COUNT(F2:F77)*100“，并拖动填充柄向右填充 至 I86；（10）优生率：语、数、外