如何用Excel函数快速统计学生期中考试成绩41745.docx

《如何用Excel函数快速统计学生期中考试成绩41745.docx》由会员分享，可在线阅读，更多相关《如何用Excel函数快速统计学生期中考试成绩41745.docx（11页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、用Excel函函数快速统计计学生期中考考试成绩期中考试结束，各各学科考试成成绩迅速汇总总到班主任这这里。这时候候，班主任最最忙的，就是是要迅速统计计各学科成绩绩的各项指标标，比如平均均分、最高分分、优秀率、及及格率以及各各学科分数的的频率统计等等等。虽然现现在普遍都在在使用Excel进行这项工工作，不过，要要想使这项工工作能够高效效准确地完成成，那还得请请Excel函数来帮忙忙才行。汇总到班主主任这里的成成绩表如图1所示，各科科成绩分布在在C2:C95单元格区域域。我们先在在K2:Q15单元格区域域建立如图2所示表格用用以存放各项项统计结果。先点击M33单元格，输输入如下公式式：=AVERRA

2、GE(CC2:C955)，回车后即即可得到语文文平均分。点击M4单单元格，输入入公式：=MAX(C$2:CC$95)，回车即可可得到语文成成绩中的最高高分。优秀率是计计算分数高于于或等于85分的学生的的比率。点击击M5单元格，输输入公式：=COUNNTIF(CC$2:C$95,=85)/COUNNT(C$22:C$955)，回车所得得即为语文学学科的优秀率率。点击M6单单元格，输入入公式：=COUNNTIF(CC$2:C$95,=60)/COUNNT(C$22:C$955)，回车所得得即为及格率率。选中M3:M6单元格，拖动动填充句柄向向右填充公式式至Q6单元格，松开开鼠标，各学学科的统计数数

3、据就出来了了。再选中M55:Q6单元格区域域，点击菜单单命令“格式单元格”，打开“单元格格式”对话框。点点击“数字”选项卡，在在左侧“分类”列表中选择“百分比”，如图3所示，确定定后可将M5:Q6单元格区域域的数据转变变成百分比形形式。至于各科分分数段人数的的统计，那得得先选中M8:M115单元格，在在编辑栏中输输入公式：=FREQQUENCYY(C$2:C$95,$K$8:$K$155)。然后按下“Ctrl+Shiftt+Enteer”快捷键，可可以看到在公公式的最外层层加上了一对对大括号。现现在，我们就就已经得到了了语文学科各各分数段人数数了。在K列中的那些些数字，就是是我们统计各各分数段

4、时的的分数分界点点。现在再选中中M8:M115单元格，拖拖动其填充句句柄向右至Q列，那么，其其它学科的分分数段人数也也立即显示在在我们眼前了了。最终的结果果如图4所示。如果果觉得K列的数据有有碍观瞻，那那么可以选中中它们，然后后设置它们的的字体颜色为为白色就可以以了。在学校的教学过过程中，对学学生成绩的处处理是必不可可少的，为了了在教学中提提高成绩，我我们需要对学学生的考试成成绩进行认真真的分析，这这就要求我们们算出与之相相关的一些数数值：像每一一个同学的总总分及班名次次、级名次，各各科分数的平平均分，各科科的优秀率及及及格率等等等，如果用Excel来处理这些些数据则非常常简单，下面面就我在实

5、际际工作中的一一点儿经验，简简单谈一谈用用Excel处理学生成绩绩。 一、排列名名次 要用到RANK函数，它是Excel中计算序数数的主要工具具，它的语法法为：RANK(Numbeer,Reff,Ordeer)，其中Numbeer为参与计算算的数字或含含有数字的单单元格，Ref是对参与计计算的数字单单元格区域的的绝对引用，Order是用来说明明排序方式的的数字(如果Order为零或省略略，则以降序序方式给出结结果，反之按按升序方式)。例如：在E2:E550单元格区域域中存放着某某一个班的总总分，那么计计算总分名次次的方法是：在F2单元格中输输入“=RANKK(E2,$E$2:$E$50)”按回

6、车键可算算出E2单元格内总总分在班内的的名次，我们们再选定F2单元格，把把鼠标指针移移 动到填充柄上上按下鼠标左左键向下拖动动鼠标即可算算出其他总分分在班内的名名次。 在计算的过过程中我们需需要注意两点点：首先当RANK函数中的Numbeer不是一个数数时，其返回回值为“#VALUUE!”，影响美观观。另外，Excel有时将空白白单元格当成成是数值“0”处理，造成成所有成绩空空缺者都是最最后一名，看看上去也很不不舒服。此时时，可将上面面的公式“=RANKK(E2,$E$2:$E$50)”改为“=IF(IISNUMBBER(E22),RANNK(E2,$e$2:$E$500),)”。其含义是是先

7、判断E2单元格里面面有没有数值值，如果有则则计算名次，没没有则空白。其其次当使用RANK函数计算名名次时，相同同分数算出的的名次也相同同，这会造成成后续名次的的空缺，但这这并不影响我我们的工作。同同样的道理，我我们也可以算算出一个学生生的总分在年年级内的名次次以及各科的的班名次和年年级名次，但但是必须注意意参与计算的的数字单元格格区域不一样样。 二、求各种种分数 求总分：主主要用SUM函数，其语语法格式为SUM(RRef)，此处Ref为参与计算的单元元格区域。例例如：SUM(B22:E2)是表示求B2、C2、D2、E2四个单元格格内数字的和和。另外还用到SUUMIF函数，语法法格式为SUMIF

8、F(Rangge,Criiteriaa,Sum_rangee)，其功能是是根据指定条条件对若干单单元格求和，参参数Range表示引用，用用于条件判断断的单元格区区域。Criteeria表示数字、表表达式或文本本，指出哪些些单元格符合合被相加求和和的条件。Sum_rrange表示引用，需需要求和的实实际单元格。注注意：Criteeria如果是文本本，那么引号号应该是半角角的，而不是是全角的，否否则会出错！求平均均分：用AVERAAGE函数，其语语法格式为AAVERAGGE(Reff)，此处Ref为参与计算算的单元格区区域。例如AVERAAGE(F22:F50)是求F2:F550区域内数字的的平均

9、值。默默认情况下，Excell 20022会忽略掉空空白的单元格格，但是它不不忽略数值为为0的单元格，要要想忽略数值值为0的单元格需需要用到COUNTTIF函数，其语语法为COUNTTIF(Raange,CCriterria)，其含义是是计算某个区区域中满足给给定条件的单单元格数目。本本例求F2:F550的平均分，如如果忽略数值值为0的单元格可可以这样计算算：SUM(FF2:F500)/COUUNTIF(F2:F550,0)。另外如果果要求去掉几几个最高分和和几个最低分分然后取平均均分的话，用用到LARGE和SMALL函数，其语语法格式为LARGEE(arraay,k)，含义是返返回数组中第第

10、k个最大值，SMALLL(arraay,k)的含义是返返回数组中第第k个最小值。如如果我们求F2:F550中去掉两个个最高分和两两个最低分之之后的平均分分可以这样计计算：“SUM(FF2:F500)-LARRGE(F22:F50,1)-LAARGE(FF2:F500,2)-SSMALL(F2:F550,1)-SMALLL(F2:FF50,2)/COUUNTIF(F2:F550-4)”。求最最高分、最低低分：MAX和MIN函数，语法法格式分别为为MAX(RRef)和MIN(RRef)，如上例中中求F2:F550的最高分和和最低分，应应该这样：MAX(F22:F50)和MIN(FF2:F500)。

11、 三、求及格格率、优秀率率求及格格率：及格率率即一个班级级中某一科大大于等于60分的比例，例例如：B2:B550中是某一个个班的语文成成绩，可以这这样求及格率率：COUNTTIF(B22:B50,=60)/COUNNT(B2:B50)。求优优秀率：例如如：B2:B550存放的是初初一一班的语语文期末考试试成绩，B2:B5500存放的是初初一全年级语语文考试的成成绩，如果规规定全年级20%的学生为优优秀，那么初初一一班语文文的优秀率应应该这样计算算：“COUNTTIF(B22:B50,=&LLARGE(B2:B5500,INNT(0.22 COUNNT(B2:B500)/CCOUNT(B2:B5

12、50)”，其中LARGEE(B2:BB500,IINT(0.2 COUUNT(B22:B5000)所求的是全全年级语文分分数前20%中最低的一一个同学的分分数，COUNTTIF(B22:B50,=&LLARGE(B2:B5500,INNT(0.22*COUNNT(B2:B500)则是求出了了初一一班语语文高于或等等于这个同学学分数的人数数，最后再除除以初一一班班的总人数COUNTT(B2:BB50)，所得就是是初一一班的的语文优秀率率，如果想求求其他班其他他科目的优秀秀率，道理都都是一样。1考试混合编编，成绩统一一理-老方法遇到到新问题 关于使用Excel进行学生成成绩处理，已已经是老话题题了

13、。但在实实际工作中还还是会有很多多新问题，例例如，现在很很多学校都是是全年级各班班混在一起考考试，以防考考试改卷中的的不正当竞争争。而统计成成绩时，则是是将已判分但但未拆封的考考卷统一交到到教务处，先先按座位号顺顺序(每本考卷的的自然顺序)录入各科分分数，再分析析统计出全年年级各科成绩绩。举例说明明，如图1(记录11至830隐藏了)，要统计二(1)班优秀人数数，传统做法法就是先按考考试号排序，再再通过公式“=COUNNTIF(分数!D2:DD69,=96)”求出。它的的弊端是要手手工逐个修改改 “D2:D669”这个参数中中的两个行号号(2和69)，这可是一一项工作量很很大的工作。当当然，简单

14、的的方法还是有有的，往下看看吧。 图1 原始始成绩表 2初初步准备-考试号里提提班级 如图1，从B列的考试号号中取出前三三位(班级编号)放在S列，即在单单元格S2输入公式“=LEFTT(B2,33)”，然后双击(或拖动)S2单元格右下下角的填充柄柄即可。 3再再做辅表-班级等级二二合一 在图1所示的工作作簿中再新建建一工作表，并并将其命名为为“等级”，在单元格A1中输入公式“=分数!A1”，回车，选选定A1，按住A1右下角的填填充柄向右下下拖至C840单元格，将“分数”工作表中的的姓名、考号号、座位号引引用到“等级”工作表中(注意，千万万不能复制粘粘贴过来，这这样不能保持持两表数据的的一致性)

15、。再选定C1，按住C1右下角的填充充柄向右拖至至L1单元格，将将语文、数学学等9个学科科目目引用过来。接接着，在D2单元格中输输入IF嵌套公式“=IF(分数!D2=96,分数!$S2&a,IF(分数!D2=72,分数!$S2&b,IF(分数!D2448,分数!$S2&d,分数!$S2&c)”。D2单元格中公公式的含义是是：看“分数!D2”单元格中的的分数(即“分数”工作表中李李悦的语文分分数)是否大于等等于96。如果是，则则在D2单元格中填填入“201a”“分数”工作表中S2单元格中的的字符“201”加上“a”(“201”表示二(1)班，“a”表示成绩等等级为“优秀”)；如果不是(即小于96)

16、，再看是否否大于等于72。如果是，则则在D2单元格中填填入“201b”；如果不是(即小于72)，再看是否否小于48。如果是，则则在D2单元格中填填入“201d”；如果不是(即小于72大于48)，则在D2单元格中填填入“201c”。最后按住D2单元格右下下角的填充柄柄向右下拖至至L840单元格，就就可以将每个个学生各科成成绩的等级及及所属班级都都填好了 4最最终统计-所需数据瞬瞬间齐 辅表制好之之后，言归正正传回到“统计”工作表(如图2)中，在A17到E28单元格区域中中利用自动填填充功能再制制作一小块辅辅助数据(如图2)。 图2 “统统计”工作表 万事俱俱备，下面开开始班级总人人数及优秀率率、

17、及格率等等的统计了。仍仍以二(1)班优秀率为为例，现在就就改用这样的的公式了“COUNTTIF(等级!$D:$D,$B117)”，即对“等级”工作表中D列所有单元元格进行统计计(等级!$D:$D)，找出值为“201a”(本工作表即“统计”工作表的$B17的值，代表表二(1)班优秀率)的单元格数数目。 具体做法如如下： (1)班级总人数(在B4单元格中输输入)：“=COUNNTIF(分数!$S:$S,A177)”； (2)优秀人数（在在C4单元格中输输入)：“=COUNNTIF(等级!$D:$DD,$B177)”； (3)优秀率（在D4单元格中输输入)：“=C4/$B4100”； (4)及格人数

18、（在在E4单元格中输输入)：“=COUNTTIF(等级!$D:$D,$B117)+COOUNTIFF(等级!$D:$D,$C117)”； (5)及格率(在F4单元格中输输入)：“=E4/$B4100”； (6)低分人数(在G4单元格中输输入)：”=COUNNTIF(等级!$D:$D,$E117)”； (7)低分率(在H4单元格中输输入)：“=G4/$B4100”； 到此为止，其余余数据通过自自动填充功能能，瞬间即可可完成。 5方方法点评-一表成，终终年用，一劳劳而永逸 (1)不同年级成成绩统计的简简单套用：比比如，首先制制作好了一年年级的统计表表，通过复制制粘贴将第一一个工作表(“分数”工作表)的内容更改改为二年级的的数据表，则则二年级的成成绩统计便自自然而成。 (2)多次考试成成绩统计的简简单套用：这这次考试的统统计表，到下下次考试成绩绩统计时，照照用不误，只只将第一个工工作表换成新新生的成绩记记载就可以了了。