《2022年用Excel函数快速统计学生期中考试成绩 .pdf》由会员分享，可在线阅读，更多相关《2022年用Excel函数快速统计学生期中考试成绩 .pdf（12页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、用 Excel函数快速统计学生期中考试成绩期中考试结束，各学科考试成绩迅速汇总到班主任这里。这时候，班主任最忙的，就是要迅速统计各学科成绩的各项指标，比如平均分、最高分、优秀率、及格率以及各学科分数的频率统计等等。虽然现在普遍都在使用 Excel 进行这项工作，不过，要想使这项工作能够高效准确地完成，那还得请Excel 函数来帮忙才行。汇总到班主任这里的成绩表如图1 所示，各科成绩分布在 C2:C95单元格区域。我们先在K2:Q15单元格区域建立如图2 所示表格用以存放各项统计结果。名师资料总结-精品资料欢迎下载-名师精心整理-第 1 页，共 12 页 -先点击 M3单元格，输入如下公式：=A
2、VERAGE(C2:C95)，回车后即可得到语文平均分。点击 M4单元格，输入公式：=MAX(C$2:C$95)，回车即可得到语文成绩中的最高分。优秀率是计算分数高于或等于85 分的学生的比率。点击 M5单元格，输入公式：=COUNTIF(C$2:C$95,=85)/COUNT(C$2:C$95)，回车所得即为语文学科的优秀率。点击 M6单元格，输入公式：=COUNTIF(C$2:C$95,=60)/COUNT(C$2:C$95)，回车所得即为及格率。选中 M3:M6单元格，拖动填充句柄向右填充公式至Q6单元格，松开鼠标，各学科的统计数据就出来了。名师资料总结-精品资料欢迎下载-名师精心整理-
3、第 2 页，共 12 页 -再选中 M5:Q6单元格区域，点击菜单命令“格式单元格”，打开“单元格格式”对话框。点击“数字”选项卡，在左侧“分类”列表中选择“百分比”，如图 3 所示，确定后可将 M5:Q6单元格区域的数据转变成百分比形式。至于各科分数段人数的统计，那得先选中M8:M15单元格，在编辑栏中输入公式：=FREQUENCY(C$2:C$95,$K$8:$K$15)。然后按下“Ctrl+Shift+Enter”快捷键，可以看到在公式的最外层加上了一对大括号。现在，我们就已经得到了语文学科各分数段人数了。在 K列中的那些数字，就是我们统计各分数段时的分数分界点。现在再选中 M8:M15
4、单元格，拖动其填充句柄向右至Q列，那么，其它学科的分数段人数也立即显示在我们眼前了。名师资料总结-精品资料欢迎下载-名师精心整理-第 3 页，共 12 页 -最终的结果如图4 所示。如果觉得 K 列的数据有碍观瞻，那么可以选中它们，然后设置它们的字体颜色为白色就可以了。在学校的教学过程中，对学生成绩的处理是必不可少的，为了在教学中提高成绩，我们需要对学生的考试成绩进行认真的分析，这就要求我们算出与之相关的一些数值：像每一个同学的总分及班名次、级名次，各科分数的平均分，各科的优秀率及及格率等等，如果用Excel来处理这些数据则非常简单，下面就我在实际工作中的一点儿经验，简单谈一谈用 Excel
5、处理学生成绩。一、排列名次名师资料总结-精品资料欢迎下载-名师精心整理-第 4 页，共 12 页 -要用到 RANK 函数，它是 Excel 中计算序数的主要工具，它的语法为：RANK(Number,Ref,Order)，其中 Number为参与计算的数字或含有数字的单元格，Ref 是对参与计算的数字单元格区域的绝对引用，Order 是用来说明排序方式的数字(如果 Order 为零或省略，则以降序方式给出结果，反之按升序方式)。例如：在 E2:E50 单元格区域中存放着某一个班的总分，那么计算总分名次的方法是：在F2单元格中输入“=RANK(E2,$E$2:$E$50)”按回车键可算出E2单元
6、格内总分在班内的名次，我们再选定 F2 单元格，把鼠标指针移动到填充柄上按下鼠标左键向下拖动鼠标即可算出其他总分在班内的名次。在计算的过程中我们需要注意两点：首先当RANK 函数中的Number不是一个数时，其返回值为“#VALUE!”，影响美观。另外，Excel 有时将空白单元格当成是数值“0”处理，造成所有成绩空缺者都是最后一名，看上去也很不舒服。此时，可将上面的公式“=RANK(E2,$E$2:$E$50)”改为“=IF(ISNUMBER(E2),RANK(E2,$e$2:$E$50),)”。其含义是先判断E2单元格里面有没有数值，如果有则计算名次，没有则空白。其次当使用 RANK 函数
7、计算名次时，相同分数算出的名次也相同，这会造成后续名次的空缺，但这并不影响我们的工作。同样的道理，我们也可以算出一个学生的总分在年级内的名次以及各科的班名次和年级名次，但是必须注意参与计算的数字单元格区域不一样。二、求各种分数名师资料总结-精品资料欢迎下载-名师精心整理-第 5 页，共 12 页 -求总分：主要用SUM 函数，其语法格式为SUM(Ref)，此处Ref 为参与计算的单元格区域。例如：SUM(B2:E2)是表示求 B2、C2、D2、E2四个单元格内数字的和。另外还用到 SUMIF函数，语法格式为SUMIF(Range,Criteria,Sum_range)，其功能是根据指定条件对若
8、干单元格求和，参数Range表示引用，用于条件判断的单元格区域。Criteria表示数字、表达式或文本，指出哪些单元格符合被相加求和的条件。Sum_range表示引用，需要求和的实际单元格。注意：Criteria如果是文本，那么引号应该是半角的，而不是全角的，否则会出错！求平均分：用 AVERAGE函数，其语法格式为AVERAGE(Ref)，此处 Ref 为参与计算的单元格区域。例如AVERAGE(F2:F50)是求 F2:F50 区域内数字的平均值。默认情况下，Excel 2002 会忽略掉空白的单元格，但是它不忽略数值为0 的单元格，要想忽略数值为0 的单元格需要用到COUNTIF 函数，
9、其语法为COUNTIF(Range,Criteria)，其含义是计算某个区域中满足给定条件的单元格数目。本例求F2:F50 的平均分，如果忽略数值为0 的单元格可以这样计算：SUM(F2:F50)/COUNTIF(F2:F50,0)。另外如果要求去掉几个最高分和几个最低分然后取平均分的话，用到LARGE和 SMALL 函数，其语法格式为LARGE(array,k)，含义是返回数组中第 k 个最大值，SMALL(array,k)的含义是返回数组中第k 个最小值。如果我们求 F2:F50 中去掉两个最高分和两个最低分之后的平均分可以这样计算：名师资料总结-精品资料欢迎下载-名师精心整理-第 6 页
10、，共 12 页 -“SUM(F2:F50)-LARGE(F2:F50,1)-LARGE(F2:F50,2)-SMALL(F2:F50,1)-SMALL(F2:F50,2)/COUNTIF(F2:F50-4)”。求最高分、最低分：MAX 和 MIN函数，语法格式分别为MAX(Ref)和 MIN(Ref)，如上例中求 F2:F50 的最高分和最低分，应该这样：MAX(F2:F50)和MIN(F2:F50)。三、求及格率、优秀率求及格率：及格率即一个班级中某一科大于等于 60 分的比例，例如：B2:B50 中是某一个班的语文成绩，可以这样求及格率：COUNTIF(B2:B50,=60)/COUNT(
11、B2:B50)。求优秀率：例如：B2:B50 存放的是初一一班的语文期末考试成绩，B2:B500 存放的是初一全年级语文考试的成绩，如果规定全年级20%的学生为优秀，那么初一一班语文的优秀率应该这样计算：“COUNTIF(B2:B50,=&LARGE(B2:B500,INT(0.2 COUNT(B2:B500)/COUNT(B2:B50)”，其中 LARGE(B2:B500,INT(0.2 COUNT(B2:B500)所求的是全年级语文分数前20%中最低的一个同学的分数，COUNTIF(B2:B50,=&LARGE(B2:B500,INT(0.2*COUNT(B2:B500)则是求出了初一一班
12、语文高于或等于这个同学分数的人数，最后再除以初一一班的总人数 COUNT(B2:B50)，所得就是初一一班的语文优秀率，如果想求其他班其他科目的优秀率，道理都是一样。名师资料总结-精品资料欢迎下载-名师精心整理-第 7 页，共 12 页 -1考试混合编，成绩统一理-老方法遇到新问题关于使用 Excel 进行学生成绩处理，已经是老话题了。但在实际工作中还是会有很多新问题，例如，现在很多学校都是全年级各班混在一起考试，以防考试改卷中的不正当竞争。而统计成绩时，则是将已判分但未拆封的考卷统一交到教务处，先按座位号顺序(每本考卷的自然顺序)录入各科分数，再分析统计出全年级各科成绩。举例说明，如图 1(
13、记录 11 至 830隐藏了)，要统计二(1)班优秀人数，传统做法就是先按考试号排序，再通过公式“=COUNTIF(分数!D2:D69,=96)”求出。它的弊端是要手工逐个修改“D2:D69”这个参数中的两个行号(2 和 69)，这可是一项工作量很大的工作。当然，简单的方法还是有的，往下看吧。名师资料总结-精品资料欢迎下载-名师精心整理-第 8 页，共 12 页 -图 1 原始成绩表 2初步准备-考试号里提班级如图 1，从 B列的考试号中取出前三位(班级编号)放在 S列，即在单元格 S2输入公式“=LEFT(B2,3)”，然后双击(或拖动)S2 单元格右下角的填充柄即可。3再做辅表-班级等级二
14、合一在图 1 所示的工作簿中再新建一工作表，并将其命名为“等级”，在单元格 A1中输入公式“=分数!A1”，回车，选定 A1，按住A1右下角的填充柄向右下拖至C840单元格，将“分数”工作表中的姓名、考号、座位号引用到“等级”工作表中(注意，千万不能复制名师资料总结-精品资料欢迎下载-名师精心整理-第 9 页，共 12 页 -粘贴过来，这样不能保持两表数据的一致性)。再选定 C1，按住 C1右下角的填充柄向右拖至L1 单元格，将语文、数学等9 个学科科目引用过来。接着，在 D2单元格中输入 IF 嵌套公式“=IF(分数!D2=96,分数!$S2&a,IF(分数!D2=72,分数!$S2&b,I
15、F(分数!D248,分数!$S2&d,分数!$S2&c)”。D2单元格中公式的含义是：看“分数!D2”单元格中的分数(即“分数”工作表中李悦的语文分数)是否大于等于 96。如果是，则在 D2单元格中填入“201a”“分数”工作表中 S2 单元格中的字符“201”加上“a”(“201”表示二(1)班，“a”表示成绩等级为“优秀”)；如果不是(即小于 96)，再看是否大于等于72。如果是，则在 D2单元格中填入“201b”；如果不是(即小于 72)，再看是否小于 48。如果是，则在 D2单元格中填入“201d”；如果不是(即小于 72 大于 48)，则在 D2单元格中填入“201c”。最后按住 D
16、2单元格右下角的填充柄向右下拖至L840 单元格，就可以将每个学生各科成绩的等级及所属班级都填好了 4最终统计-所需数据瞬间齐辅表制好之后，言归正传回到“统计”工作表(如图 2)中，在 A17到 E28单元格区域中利用自动填充功能再制作一小块辅助数据(如图2)。名师资料总结-精品资料欢迎下载-名师精心整理-第 10 页，共 12 页 -图 2“统计”工作表万事俱备，下面开始班级总人数及优秀率、及格率等的统计了。仍以二(1)班优秀率为例，现在就改用这样的公式了“COUNTIF(等级!$D:$D,$B17)”，即对“等级”工作表中D列所有单元格进行统计(等级!$D:$D)，找出值为“201a”(本
17、工作表即“统计”工作表的$B17的值，代表二(1)班优秀率)的单元格数目。具体做法如下：(1)班级总人数(在 B4单元格中输入)：“=COUNTIF(分数!$S:$S,A17)”；(2)优秀人数（在 C4单元格中输入)：“=COUNTIF(等级!$D:$D,$B17)”；名师资料总结-精品资料欢迎下载-名师精心整理-第 11 页，共 12 页 -(3)优秀率（在 D4单元格中输入)：“=C4/$B4100”；(4)及格人数（在 E4单元格中输入)：“=COUNTIF(等级!$D:$D,$B17)+COUNTIF(等级!$D:$D,$C17)”；(5)及格率(在 F4 单元格中输入)：“=E4/
18、$B4100”；(6)低分人数(在 G4单元格中输入)：”=COUNTIF(等级!$D:$D,$E17)”；(7)低分率(在 H4单元格中输入)：“=G4/$B4100”；到此为止，其余数据通过自动填充功能，瞬间即可完成。5方法点评-一表成，终年用，一劳而永逸 (1)不同年级成绩统计的简单套用：比如，首先制作好了一年级的统计表，通过复制粘贴将第一个工作表(“分数”工作表)的内容更改为二年级的数据表，则二年级的成绩统计便自然而成。(2)多次考试成绩统计的简单套用：这次考试的统计表，到下次考试成绩统计时，照用不误，只将第一个工作表换成新生的成绩记载就可以了。名师资料总结-精品资料欢迎下载-名师精心整理-第 12 页，共 12 页 -