《怎样用Excel函数快速统计学生期中考试成绩.docx》由会员分享，可在线阅读，更多相关《怎样用Excel函数快速统计学生期中考试成绩.docx（10页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、怎样用Excel函数快速统计学生期中考试成绩当前位置：文档视界怎样用Excel函数快速统计学生期中考试成绩怎样用Excel函数快速统计学生期中考试成绩先点击M3单元格，输入如下公式：=AVERAGE(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
2、:C$95)，回车所得即为及格率。选中M3:M6单元格，拖动填充句柄向右填充公式至Q6单元格，松开鼠标，各学科的统计数据就出来了。再选中M5:Q6单元格区域，点击菜单命令“格式单元格，打开“单元格格式对话框。点击“数字选项卡，在左侧“分类列表中选择“百分比，如图3所示，确定后可将M5:Q6单元格区域的数据转变成百分比形式。当前位置：文档视界怎样用Excel函数快速统计学生期中考试成绩怎样用Excel函数快速统计学生期中考试成绩当前位置：文档视界怎样用Excel函数快速统计学生期中考试成绩怎样用Excel函数快速统计学生期中考试成绩以降序方式给出结果，反之按升序方式)。例如：在E2:E50单元格
3、区域中存放着某一个班的总分，那么计算总分名次的方法是：在F2单元格中输入“=RANK(E2,$E$2:$E$50)按回车键可算出E2单元格内总分在班内的名次，我们再选定F2单元格，把鼠标指针移动到填充柄上按下鼠标左键向下拖动鼠标即可算出其他总分在班内的名次。在计算的经过中我们需要注意两点：首先当RANK函数中的Number不是一个数时，其返回值为“#VALUE!，影响美观。另外，Excel有时将空白单元格当成是数值“0处理，造成所有成绩空缺者都是最后一名，看上去也很不舒适。此时，可将上面的公式“=RANK(E2,$E$2:$E$50)改为“=IF(ISNUMBER(E2),RANK(E2,$e
4、$2:$E$50),)。其含义是先判定E2单元格里面有没有数值，假如有则计算名次，没有则空白。其次当使用RANK函数计算名次时，一样分数算出的名次也一样，这会造成后续名次的空缺，但这并不影响我们的工作。同样的道理，我们可以以算出一个学生的总分在年级内的名次以及各科的班名次和年级名次，但是必须注意介入计算的数字单元格区域不一样。二、求各种分数求总分：主要用SUM函数，其语法格式为SUM(Ref)，此处Ref为介入计算的单元格区域。例如：SUM(B2:E2)是表示求B2、C2、D2、E2四个单元格内数字的和。另外还用到SUMIF函数，语法格式为SUMIF(Range,Criteria,Sum_ra
5、nge)，其功能是根据指定条件对若干单元格求和，参数Range表示引用，用于条件判定的单元格区域。Criteria表示数字、表达式或文本，指出哪些单元格符合被相加求和的条件。Sum_range表示引用，需要求和的实际单元格。注意：Criteria假如是文本，那么引号应该是半角的，而不是全角的，否则会出错！求平均分：用AVERAGE函数，其语法格式为AVERAGE(Ref)，此处Ref为介入计算的单元格区域。例如AVERAGE(F2:F50)是求F2:F50区域内数字的平均值。默认情况下，Excel2002会忽略掉空白的单元格，但是它不忽略数值为0的单元格，要想忽略数值为0的单元格需要用到COU
6、NTIF函数，其语法为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中去掉两个最高分和两个最低分之后的平均分能够这样计算：“SUM(F2:F50)-LARGE(F2:F50,1)-L
7、ARGE(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(B2:B50)。求优秀率：例如：B2:B50存放的是初逐一班的语文期末考试成绩，B2:B500存
8、放的是初一全年级语文考试的成绩，假如规定全年级20%的学生为优秀，那么初逐一班语文的优秀率应该这样计算：“COUNTIF(B2:B50,=&LARGE(B2:B500,INT(0.2COUNT(B2:B500)/COUNT(B2:B50)，其中LARGE(B2:B500,INT(0.2COUNT(B2:B500)所求的是全年级语文分数前20%中最低的一个同学的分数，COUNTIF(B2:B50,=&LARGE(B2:B500,INT(0.2*COUNT(B2:B500)则是求出了初逐一班语文高于或等于这个同学分数的人数，最后再除以初逐一班的总人数COUNT(B2:B50)，所得就是初逐一班的语
9、文优秀率，假如想求其他班其他科目的优秀率，道理都是一样。1考试混合编，成绩统一理-老方法碰到新问题关于使用Excel进行学生成绩处理，已经是老话题了。但在实际工作中还是会有很多新问题，例如，如今很多学校都是全年级各班混在一起考试，以防考试改卷中的不正当竞争。而统计成绩时，则是将已判分但未拆封的考卷统一交到教务处，先按座位号顺序(每本考卷的自然顺序)录入各科分数，再分析统计出全年级各科成绩。举例讲明，如图1(记录11至830隐藏了)，要统计二(1)班优秀人数，传统做法就是先按考试号排序，再通过公式“=COUNTIF(分数!D2:D69,=96)求出。它的弊端是要手工逐个修改“D2:D69这个参数
10、中的两个行号(2和69)，这可是一项工作量很大的工作。当然，简单的方法还是有的，往下看吧。图1原始成绩表2初步准备-考试号里提班级如图1，从B列的考试号中取出前三位(班级编号)放在S列，即在单元格S2输入公式“=LEFT(B2,3)，然后双击(或拖动)S2单元格右下角的填充柄即可。3再做辅表-班级等级二合一在图1所示的工作簿中再新建一工作表，并将其命名为“等级，在单元格A1中输入公式“=分数!A1，回车，选定A1，按住A1右下角的填充柄向右下拖至C840单元格，将“分数工作表中的姓名、考号、座位号引用到“等级工作表中(注意，千万不能复制粘贴过来，这样不能保持两表数据的一致性)。再选定C1，按住
11、C1右下角的填充柄向右拖至L1单元格，将语文、数学等9个学科科目引用过来。接着，在D2单元格中输入IF嵌套公式“=IF(分数!D2=96,分数!$S2&a,IF(分数!D2=72,分数!$S2&b,IF(分数!D24最终统计-所需数据霎时齐辅表制好之后，言归正传回到“统计工作表(如图2)中，在A17到E28单元格区域中利用自动填充功能再制作一小块辅助数据(如图2)。图2“统计工作表万事俱备，下面开场班级总人数及优秀率、及格率等的统计了。仍以二(1)班优秀率为例，如今就改用这样的公式了“COUNTIF(等级!$D:$D,$B17)，即对“等级工作表中D列所有单元格进行统计(等级!$D:$D)，找
12、出值为“201a(本工作表即“统计工作表的$B17的值，代表二(1)班优秀率)的单元格数目。详细做法如下：(1)班级总人数(在B4单元格中输入)：“=COUNTIF(分数!$S:$S,A17)；(2)优秀人数在C4单元格中输入)：“=COUNTIF(等级!$D:$D,$B17)；(3)优秀率在D4单元格中输入)：“=C4/$B4100；(4)及格人数在E4单元格中输入)：“=COUNTIF(等级!$D:$D,$B17)+COUNTIF(等级!$D:$D,$C17)；(5)及格率(在F4单元格中输入)：“=E4/$B4100；(6)低分人数(在G4单元格中输入)：=COUNTIF(等级!$D:$D,$E17)；(7)低分率(在H4单元格中输入)：“=G4/$B4100；到此为止，其余数据通过自动填充功能，霎时即可完成。5方法点评-一表成，终年用，一劳而永逸(1)不同年级成绩统计的简单套用：比方，首先制作好了一年级的统计表，通过复制粘贴将第一个工作表(“分数工作表)的内容更改为二年级的数据表，则二年级的成绩统计便自然而成。(2)屡次考试成绩统计的简单套用：这次考试的统计表，到下次考试成绩统计时，照用不误，只将第一个工作表换成新生的成绩记载就能够了。