函数在教学成绩统计中的应用

1、函数在教学成绩统计中的应用（一）EXCEL 成绩统计1.排序： =RANK(F2,F$2：F$77,2) (后面的逻辑数字：忽略或0时,为升序;是1以上的自然数时,为降序.) 在”数据”栏中找”排序”2.找最大值和最小值：(即是最高分,最低分.)=MAX(C2：C77) =MIN(C2：C77)（如在另外一个”学生成绩表”中找）=MAX（学生成绩表！C2：C77 ） =MIN（学生成绩表！C2：C77） 3.平均分=AVERAGE(C2：C77) 当不输入成绩时会显示错误值的符号.(#DIV/0!) =IF(ISERROR(AVERAGE(C2：C77),”,AVERAGE(C2：C77) 当

2、不输入成绩时不显示错误值的符号.4.用求和公式求总分.5.各分层分数分布表： 100分： =COUNTIF(C2：C77,=100)9099分： =COUNTIF(C2：C77,=90)-COUNTIF(C2：C77,=100)8089分： =COUNTIF(C2：C77,=80)-COUNTIF(C2：C77,=90)7079分： =COUNTIF(C2：C77,=70)-COUNTIF(C2：C77,=80)6069分： =COUNTIF(C2：C77,=60)-COUNTIF(C2：C77,=70)5059分： =COUNTIF(C2：C77,=50)-COUNTIF(C2：C77,=6

3、0)4049分： =COUNTIF(C2：C77,=40)-COUNTIF(C2：C77,=50)3039分： =COUNTIF(C2：C77,=30)-COUNTIF(C2：C77,=40)2029分： =COUNTIF(C2：C77,=20)-COUNTIF(C2：C77,=30)1019分： =COUNTIF(C2：C77,=10)-COUNTIF(C2：C77,=20) 109分： =COUNTIF(C2：C77,=1)-COUNTIF(C2：C77,=10)0分或缺： =COUNTIF(C2：C77,=0)6.合格人数： =COUNTIF(C2：C77,=60)7.合格率： =(CO

4、UNTIF(F2：F77,=60)/COUNT(F2：F77)*1008. 60分以下人数：=COUNTIF(C2：C77,60)9.不合格率： =(COUNTIF(F2：F77, 60)/COUNT(F2：F77)*10010.低分率：假设40分以下为差生.是指各科40分以下人数与总人数的比值。=COUNTIF(C2：C77,=96)/COUNT(C2：C77)*10012.要显示各种学生情况,如不及格的,可用条件格式来实现.（二）Excel应对特殊成绩统计 1 考试混合编，成绩统一理老方法遇到新问题 关于使用Excel实行学生成绩处理，已经是老话题了。但在实际工作中还是会有很多新问题，例如

5、，现在很多学校都是全年级各班混在一起考试，以防考试改卷中的不正当竞争。而统计成绩时，则是将已判分但未拆封的考卷统一交到教务处，先按座位号顺序（每本考卷的自然顺序）录入各科分数，再分析统计出全年级各科成绩。举例说明，如图1（记录11至830隐藏了），要统计二（1）班优秀人数，传统做法就是先按考试号排序，再通过公式“=COUNTIF(分数!D2：D69,=96)”求出。它的弊端是要手工逐个修改 “D2：D69”这个参数中的两个行号（2和69），这不过一项工作量很大的工作。当然，简单的方法还是有的，往下看吧。 图1 原始成绩表 2 初步准备考试号里提班级 如图1，从B列的考试号中取出前三位（班级编号

6、）放在S列，即在单元格S2输入公式“=LEFT(B2,3)”，然后双击（或拖动）S2单元格右下角的填充柄即可。 3 再做辅表班级等级二合一 在图1所示的工作簿中再新建一工作表，并将其命名为“等级”，在单元格A1中输入公式“=分数!A1”，回车，选定A1，按住A1右下角的填充柄向右下拖至C840单元格，将“分数”工作表中的姓名、考号、座位号引用到“等级”工作表中（注意，千万不能复制粘贴过来，这样不能保持两表数据的一致性）。再选定C1，按住C1右下角的填充柄向右拖至L1单元格，将语文、数学等9个学科科目引用过来。接着，在D2单元格中输入IF嵌套公式“=IF(分数!D2=96,分数!$S2&a,IF

7、(分数!D2=72,分数!$S2&b,IF(分数!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”

8、。最后按住D2单元格右下角的填充柄向右下拖至L840单元格，就能够将每个学生各科成绩的等级及所属班级都填好了转载自：（三）用Excel实行学生成绩统计分析1我们可采用以下方法：新建空白Excel工作簿，将自动建立的Sheet1、Sheet2、Sheet3工作表重命名为成绩表、分析表、分数段统计表。其中各科考试成绩表用于输入各科成绩。 (一)设计各科成绩表 在各科考试成绩表第一行中输入分析名称，如“* 中学20072008 学年度第一学期高一月考成绩表”，在第二行从A1单元格开始顺序输入记录字段名：考号、姓名、班级、语文、数学、英语、物理、化学等。假设全级学生有674 人，10个班,所以从第3行

9、到第676 行用来输入所有学生各科的成绩。如图1所示.1、求总分：主要用SUM函数，其语法格式为SUM(Ref)，此处Ref为参与计算的单元格区域。例如：SUM(B2：E2)是表示求B2、C2、D2、E2四个单元格内数字的和。另外还用到SUMIF函数，语法格式为SUMIF(Range,Criteria,Sum_range)，其功能是根据指定条件对若干单元格求和，参数Range表示引用，用于条件判断的单元格区域。Criteria表示数字、表达式或文本，指出哪些单元格符合被相加求和的条件。Sum_range表示引用，需要求和的实际单元格。注意：Criteria如果是文本，那么引号应该是半角的，而不

10、是全角的，否则会出错！在O3单元格中输入=sum(F3：N3),便可求出第一个同学韩威的总分,然后选中O3单元格,当右下角出现小”+”字图标时时使用拖拉大法往下自动填充,或左键双击即可.2排名次：Microsoft Excel中用RANK函数求名次（排序）,它的语法为： RANK(Number，Ref，Order) 返回一个数字在数字列表中的排位。 Number 为需要找到排位的数字。Ref 为数字列表数组或对数字列表的引用。Ref 中的非数值型参数将被忽略。如果使用公式复制，最好使用绝对地址。如 =RANK(E2,$E$2：$E$50)。注意，其中的标点符号用英文半角。Order 为一数字，

11、指明排位的方式。如果order为0（零）或省略，Microsoft Excel 对数字的排位是基于ref为按照降序排列的列表。如果order不为零，Microsoft Excel对数字的排位是基于ref为按照升序排列的列表。在计算的过程中我们需要注意两点：首先当RANK函数中的Number不是一个数时，其返回值为“#VALUE!”，影响美观。另外，Excel有时将空白单元格当成是数值“0”处理，造成所有成绩空缺者都是最后一名，看上去也很不舒服。此时，可将上面的公式“=RANK(E2,$E$2：$E$50)”改为“=IF(ISNUMBER(E2),RANK(E2,$e$2：$E$50),)”。其

12、含义是先判断E2单元格里面有没有数值，如果有则计算名次，没有则空白。其次当使用RANK函数计算名次时，相同分数算出的名次也相同，这会造成后续名次的空缺，但这并不影响我们的工作。同样的道理，我们也能够算出一个学生的总分在年级内的名次以及各科的班名次和年级名次，但是必须注意参与计算的数字单元格区域不一样。使用RANK()函数排名的两种情况：(1)同分同名次,但后续名次空缺假定E列为成绩，F列为名次，F2单元格公式如下： =RANK(E2,E：E) 或=rank(e2,$E$2：$E$10000)这种方法，分数相同时名次相同，随后的名次将空缺。 例如：两个人99分，并列第2名，则第3名空缺，接下来是

13、第4名。 (2)同分同名次,但后续名次不空缺假定成绩在E列,则在F2单元格中输入公式： =SUMPRODUCT(INDIRECT(E$2：E$&MAX(IF(E$2：E$10000,ROW(E$2：E$10000)E2)*(1/COUNTIF(INDIRECT(E$2：E$&MAX(IF(E$2：E$10000,ROW(E$2：E$10000),INDIRECT(E$2：E$&MAX(IF(E$2：E$10000,ROW(E$2：E$10000)+1 公式以Ctrl+Shift+Enter三键结束。 通常我们在排名次时大多采用的是同分同名次,允许后续名次空缺.（1）排总名次：在P3单元格输入=

14、RANK(O3,$O$3：$O$676)，回车，便得到韩威同学在全年级的总分名次，然后选中P3单元格,当右下角出现小“+”字图标时时使用拖拉大法往下自动填充,或左键双击即可.（2）排班名次：在Q3单元格中输入=SUMPRODUCT($C$3：$C$376=$C3)*($O3=90),1,0)”，按下SHIFTCTRL键敲回车，Excel会自动在公式两侧加上大括号,生成数组公式。然后对D5至D14单元格实行向下自动填充操作。最后在D15 中输入“=SUM(D5：D14)”，求出全级及格人数。公式中的1表示当条件为真时返回结果计为1个,即及格1人,否则为0;4.求各班及格率我们可在E5 单元格中输

15、入“=IF(B5=0,0,D5/B54)*100”，为避免分母为0的错误，用了IF 函数。公式的作用为：若1班参考人数不为0，则及格率为及格人数除以参考人数后乘以100。然后对E5 至E14 单元格实行向下自动填充操作。然后在E15 中输入Average(E5：E14),算出全年级的平均合格率。5.按及格率班级排名次在F5单元格中输入 =RANK(E5,$E$5：$E$14),然后往下自动填充.得到各班的及格率名次.6.求各班优秀人数首先在I5单元格中输入“=SUM(IF(成绩表!$C$3： 成绩表!$C$676=A5)*( 成绩表! $ F $ 3 ： 成绩表!$F$676,=100),1,

16、0)”然后对I5至I14单元格实行向下自动填充操作。最后在I15单元格中输入“=SUM(I5：I14)”，以求得全年级优秀人数。如果规定全年级20%以上的学生为优秀人数，能够这样算：=SUM(IF(成绩表!$C$3：成绩表!$C$676=A5)*(成绩表!$F$3：成绩表!$F$676=LARGE(成绩表!$F$3：$F$676,INT(0.2*COUNT(成绩表!$F$3：$F$676),1,0)其中LARGE(成绩表!$F$3：$F$676,INT(0.2*COUNT(成绩表!$F$3：$F$676)所求的是全年级语文分数前20%中最低的一个同学的分数，SUM(IF(成绩表!$C$3： 成

17、绩表!$C$676=A5)*( 成绩表! $ F $ 3 ： 成绩表!$F$676,”=”LARGE(成绩表!$F$3：$F$676,INT(0.2*COUNT(成绩表!$F$3：$F$676)则是求出1班语文学科高于或等于这个同学分数的人数. 8.求各班优秀率跟求合格率类似，首先在J5单元格中输入“=IF(B5=0,0,I5/B5)*100”，为避免分母为0 的错误，用了IF 函数。公式的作用为：若107班参考人数不为0，则优生率为优生人数除以参考人数后乘以100。然后对J5 至J14 单元格实行向下自动填充操作。最后在J15中输入“=average(J5：J14)”算出全年级的平均优秀率。

18、9.求各班平均分求平均分用AVERAGE函数，其语法格式为AVERAGE(Ref)，此处Ref为参与计算的单元格区域。例如AVERAGE(F2：F50)是求F2：F50区域内数字的平均值。默认情况下，Excel 2003会忽略掉空白的单元格，但是它不忽略数值为0的单元格，要想忽略数值为0的单元格需要用到COUNTIF函数，其语法为COUNTIF(Range,Criteria)，其含义是计算某个区域中满足给定条件的单元格数目。本例求F2：F50的平均分，如果忽略数值为0的单元格能够这样计算：SUM(F2：F50)/COUNTIF(F2：F50,0)。另外如果要求去掉几个最高分和几个最低分然后取平

19、均分的话，用到LARGE和SMALL函数，其语法格式为LARGE(array,k)，含义是返回数组中第k个最大值，SMALL(array,k)的含义是返回数组中第k个最小值。如果我们求F2：F50中去掉两个最高分和两个最低分之后的平均分能够这样计算：“SUM(F2：F50)-LARGE(F2：F50,1)-LARGE(F2：F50,2)-SMALL(F2：F50,1)-SMALL(F2：F50,2)/COUNTIF(F2：F50-4)”。在N5单元格中输入 =SUMIF(成绩表!$C$3：成绩表!$C$676,A5,成绩表!$F$3：成绩表!$F$676)/B5,然后向下自动填充,便可得到各班

20、的语文平均分,最后在N15单元格中输入 =AVERAGE(N5：N14),得到全年级语文平均分.10.求各班最高分求最高分可用MAX函数，这个函数的作用是求出参数中的最大值。所以在R5单元格中输入“=MAX(IF(成绩表!$C$3： 成绩表!$C$676=A5,成绩表!$F$3：成绩表!$F$676)” 按下SHIFT CTRL键敲回车，生成数组公式。即可算出1班语文科中的最高分。然后向下自动填充操作。最后在R15单元格中输入“=MAX(R5：R14)”，以求得全年级的最高分。(三)设计分数段统计表将A1：F1 合并并居中，输入“* 中学20072008 学年度第一学期高一月考分数段统计表”。

21、其他文字按表格内容输入即可。然后从A3至A12依次输入1,2,310班级名.1.求总分在600分以上的人数计算分数段人数仍可用数组的方法，可在C3单元格中输入 “=SUM(IF(成绩表!$C$3： 成绩表!$C$676=A3)*(成绩表! $ O $ 3 ： 成绩表!$O$676=600),1,0)”，按下SHIFT CTRL 键敲回车，然后实行向下自动填充操作。然后在C13单元格中输入“=SUM(C3：C12)”，以求得全年级600 分以上的人数。2.求500599 分数段人数求各班总分在500599 分数段人数，将用到三个条件实行标准单元计算。首先在D3中输入=SUM(IF(成绩表!$C$

22、3：成绩表!$C$676=A3)*(成绩表!$O$3： 成绩表!$O$676=500),1,0)按下SHIFTCTRL键敲回车，生成数组公式。然后实行向下自动填充操作。最后在D13 单元格中输入“=SUM(D3：D12)”，求出全年级总分在500599 分数段人数。3.求400499 分数段人数求各班总分在400499分数段人数，类似上述方法，只改变条件即可，首先在E3 中输入=SUM(IF(成绩表!$C$3：成绩表!$C$676=A3)*(成绩表!$O$3： 成绩表!$O$676=400),1,0),按下SHIFT CTRL 键敲回车，生成数组公式。然后实行向下自动填充操作。最后在E13单元

23、格中输入“=SUM(E3：E12)”，求出全年级400499 分数段人数。同样的操作，能够分别分析各班总分各分数段人数。实行分数段统计的另外两种方法：用Excel怎样统计出学生成绩各分数段内的人数分布时,很多文章都推荐使用CountIF函数，不过每统计一个分数段都要写一条函数，十分麻烦。例如，要统计高一1班的语文成绩的分数段人数分布情况,可在在T76：T80内统计显示F3：F72内小于60分、60至70之间、70至80之间、80至90之间、90至100之间的分数段内人数分布情况，要输入以下5条公式： 1. 在C58内输入公式统计少于60分的人数：=CountIF(F3：F74,=90)3. 在

24、C60内输入公式统计80至90之间的人数：=CountIF(F3：F74,=80)-CountIF(F3：F74,=90)，4. 在C61内输入公式统计70到80之间的人数：=CountIF(F3：F74,=70)-CountIF(F3：F74,=80)，5. 在C62内输入公式统计60到70之间的人数：=CountIF(F3：F74,=60)-CountIF(F3：F74,=70)。如果要把0至10之间、10至20之间、20至3090至100、100至110、110至120、120至130、130至140、140至150分之间这么多个分数段都统计出来，就要写上十几条公式了。其实，Excel已

25、经为我们提供了一个实行频度分析的FreQuency数组函数，它能让我们用一条数组公式就轻松地统计出各分数段的人数分布。例如，我们要统计出总分在O3：O676区域内高一1班至高一10班10个班级，300分以下，300至400，400至500，500至600，600分以上每个分数段内的人数分布：如图41. 在A4：A8内输入：0、299、399、499、599。2. 用鼠标选择区域C4至C9，在编辑栏内输入“=FREQUENCY(IF(成绩表!$C$3：$C$676=C2,成绩表!$O$3：$O$676,),$A$4：$A$8)”。3. 按“Crtl+Shift+Enter”组合键产生数组公式“=

26、 =FREQUENCY(IF(成绩表!$C$3：$C$676=C2,成绩表!$O$3：$O$676,),$A$4：$A$8)”，这里要注意“ ”不能手工键入，必须按下“Crtl+Shift+Enter”组合键由系统自动产生。完成后C4：C9将显示如图所示的分数分布情况。然后向右自动填充，得到其它各个班的分数段人数分布情况。用CountIF函数统计分数段的方法流传很广，但效率并不高,而用FREQUENCY()函数就非常方便且高效函数FREQUENCY 主要功能是以一列垂直数组返回某个区域中数据的频率分布。 其语法格式：FREQUENCY(data_array,bins_array) 参数说明：D

27、ata_array表示用来计算频率的一组数据或单元格区域；Bins_array表示为前面数组实行分隔一列数值。 4.创建图表：按以上方法创建好工作表，设置好打开权限密码和修改密码，保护好里面的数据，这样就能够快速准确地对学生成绩实行管理了。既减轻了教师的工作负担，又提升了准确性和工作效率。 （五）Excel函数在学生成绩统计中的应用在学校的教学过程中，对学生成绩的处理是必不可少的，为了在教学中提升成绩，我们需要对学生的考试成绩实行认真的分析，这就要求我们算出与之相关的一些数值：像每一个同学的总分及班名次、级名次，各科分数的平均分，各科的优秀率及及格率等等，如果用Excel来处理这些数据则非常简

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

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

30、会造成后续名次的空缺，但这并不影响我们的工作。同样的道理，我们也能够算出一个学生的总分在年级内的名次以及各科的班名次和年级名次，但是必须注意参与计算的数字单元格区域不一样。 二、求各种分数 求总分：主要用SUM函数，其语法格式为SUM(Ref)，此处Ref为参与计算的单元格区域。例如：SUM(B2：E2)是表示求B2、C2、D2、E2四个单元格内数字的和。另外还用到SUMIF函数，语法格式为SUMIF(Range,Criteria,Sum_range)，其功能是根据指定条件对若干单元格求和，参数Range表示引用，用于条件判断的单元格区域。Criteria表示数字、表达式或文本，指出哪些单元格

31、符合被相加求和的条件。Sum_range表示引用，需要求和的实际单元格。注意：Criteria如果是文本，那么引号应该是半角的，而不是全角的，否则会出错！ 求平均分：用AVERAGE函数，其语法格式为AVERAGE(Ref)，此处Ref为参与计算的单元格区域。例如AVERAGE(F2：F50)是求F2：F50区域内数字的平均值。默认情况下，Excel 2002会忽略掉空白的单元格，但是它不忽略数值为0的单元格，要想忽略数值为0的单元格需要用到COUNTIF函数，其语法为COUNTIF(Range,Criteria)，其含义是计算某个区域中满足给定条件的单元格数目。本例求F2：F50的平均分，如

32、果忽略数值为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)-LARGE(F2：F50,2)-SMALL(F2：F50,1)-SMALL(F2：F50,2)/COUNTIF(F2：F50-4)”。 求最高分、最低

33、分：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存放的是初一全年级语文考试的成绩，如果规定全年级20%的学生为优秀，那么初一一班语文的优秀率应该这样计算：“COUNTIF(B2：B50,=

34、&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)则是求出了初一一班语文高于或等于这个同学分数的人数，最后再除以初一一班的总人数COUNT(B2：B50)，所得就是初一一班的语文优秀率，如果想求其他班其他科目的优秀率，道理都是一样。 （六）Excel及其函数在学科成绩统计与日常生活中的应用Excel俗称电子

35、表格，是集表格、计算和数据库为一身的优秀软件，其数据处理的核心是函数和数据库，本文仅介绍用Excel2KX实行成绩统计和一些日常统计的函数。一考试成绩的统计（一）对班学生成绩的总体评估统计1. 假设E4：E53单元格存放某班学生的语文考试成绩，若要计算该科的总分、人平分、及格人数、及格率、优秀人数、优秀率、最高（低）分、分段统计等。（1）参考人数统计：若要将参考人数放在E54单元格中，只需选定该单元格并插入“计数函数”：“COUNT”并选定要计数的单元格区间E4：E53或（输入）公式：“=COUNT(E4：E53)” 回车即可；（2）学科总分的统计：若要将该科的总分存放在E55单元格中，只需选

36、定该单元格并插入“求和函数”：“SUM”并选定要计数的单元格区间E4：E53或（输入）公式：“= SUM (E4：E53)”回车后即可计算出该科总分(注：在E55单元格只显示计算结果。如要修改该公式，只需选定该单元格，在编辑栏内即可修改)。（3）学科平均分的统计：若要将该科人平分存放在E56单元格中，选定该单元格插入平均值函数“AVERAGE”并选定求值区间“E4：E53”或输入公式：“AVERAGE(E4：E53)”，回车后即可计算出人平分(注：数组或单元格引用中的文字、逻辑值或空白单元格将被忽略，但单元格中的“0”则参与运算)。（3）学科及格人数统计：若要将该科的及格人数存放在E57单元格

37、中，选定该单元格插入计算某个区域内满足指定条件的单元格数目函数的条件函数：“COUNTIF”，插入函数的方法是单击“插入”菜单中的“插入函数（F）”命令，弹出一个“插入函数”对话框，在其中的“选择函数”下的列表框中选定选定“COUNTIF”函数，单击“确定”，弹出一个“函数参数”指定对话框，在其中的“Range”右边矫形框中输入统计的区间E4：E53或（单击矫形框右端的压缩图标，再在工作表中选择统计区间E4：E53，再单击“函数参数”压缩框中右端的扩展图标，返回扩展的“函数参数”对话框中），再单击对话框中的“Criteria”右边的矩形框，并键入及格条件：“=60”，最后单击“确定”即可；也能

38、够手动编辑输入公式及条件：“COUNTIF(E4：E53，60)”回车，即可计算出该科的及格人数(空白单元格、文字、逻辑值、错误值均被忽略)。（4）学科及格率统计：若要将该科的及格率存放在E58中，选定该单元格并输入公式“COUNTIF(E4：E53，60)/ COUNT (E4：E53)”回车(COUNT为计数函数，用法是“计数函数”加上“计数区间”)，即可计算出该科的及格率(其中“COUNT (E4：E53)”是计算E4：E53区间内单元格中的实际参考人数，空白单元格将被忽略)。（5）学科优生人数统计：若要统计学科优生人数与学科及格人数统计类似。如：将该科的优秀人数存放在E59单元 格中，

39、选定该单元格插入条件函数：“COUNTIF”，方法与统计及格人数一样，仅仅函数参数对对话框中“Criteria”右边的矩形框中的函数参数要修改为：“=80”(优分标准)；（6）学科优分率统计：学科优生百分率的统计与学科及格率的统计相同。如：将该科的优秀率存放在E60单元格中，选定该单元格并输入公式：“COUNTIF(E4：E53，80)/COUNT (E4：E53)”。这与及格率公式的编辑仅仅参数“60” 变成了“80”而已。求优秀率：例如：B2：B50存放的是初一一班的语文期末考试成绩，B2：B500存放的是初一全年级语文考试的成绩，如果规定全年级20%的学生为优秀，那么初一一班语文的优秀率

40、应该这样计算：“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)则是求出了初一一班语文高于或等于这个同学分数的人数，最后再除以初一一班的总人数COUNT(B2：B50)，所得就是初一一班的语文优秀率，如果想求其他班其他科目的优秀率，道理都是一样。（7）学科最高分的查询统计：

41、若要查询统计学科最高分，则可利用“MAX”(最大值)函数。如将E4：E53区域中存放着的全班50名学生语文科的考试成绩，将其学科最高分、最低分别存放在E61和E62单元格中，则可选中E61单元格，插入“最大值”函数：“MAX”并指定统计数据的区域：“E4：E53”或在编辑栏输入公式“MAX(E4：E53)”回车后即可计算出其中的最高分。（8）学科最低分的查询统计：如将E4：E53区域中存放着的全班50名学生语文科的考试成绩，将其学科最低分别存放在E62单元格中，则可选中E62单元格，插入“最小值”函数：“MIN”并指定统计数据的区域：“E4：E53”或在编辑栏输入公式“MIN(E4：E53)”

42、回车后即可计算出其中的最低分。（9）若在“F4：F53”、“G4：G53”“K4：K53”区域中分别存放数学、英语、计算机、音、体、美等学科成绩，则只需选定E54：E62区域，移动空心十字光标至单元格右下角。待光标变成小实心十字光标时，按住鼠标左键沿行拖动控制手柄至K列即可，放开鼠标左键后，则各科的科总分、人平分、及格人数、及格率、优秀人数、优秀率、最高分、最低分等立即计算出来。2. 班学生成绩的分段统计：若要将学生成绩按分数段实行统计，则可利用FREQUENCY函数(以一列垂直数组及对该数组设定实行频率颁布计算的分段点计算出一组数据的频率颁布)。假设E4：E53共有50个学生的语文科测试成绩

43、，那么可在B66：B70单元格分别输入59、69、79、89、100，则表示统计59(包括59)分以下、6069、7079、8089、90100分数段的成绩个数。若要将计算结果存入C66：C70，则选中C66：C70单元格区域，在编辑栏内输入公式“FREQUENCY (E4：E53，$B$66：$B$70)”,（注：公式中区域：“E4：E53”是用来计算频率的数组区域班学生学科成绩的单元区域，公式中区域：“$B$70：$B$70”是对所计算频率数组班学生学科成绩实行频率计算的分段界点的数组区域的绝对引用），输完后让光标停在公式末尾，按下SHIFTCTRL键敲回车(这是生成数组公式的关键环节)，

44、Excel会自动在公式两侧加上大括号，公式成为“FREQUENCY(E4：E53，$B$66：$B$70)”,则区间C66：C70各单元格中立刻计算出对应各分数段中成绩的个数。（二）对班中学生个体成绩的评估统计1. 学生个体总成绩的统计：若在工作表中E4：K53区域存放某班50个学生各科的成绩，而E4：K4、E5：K5、E53：K53中分别各个学生语文、数学、美术等各科成绩记录。则需在L列中的L4：L53区间单元格中分别存放各学生的各门学科成绩总分，则先选定L4单元格插入求和函数“”并选定求和区域回车或手动输入公式“=(E4：K4)”回车即可计算出该生各科的总分；然后选定L4单元格，并将鼠标指

45、针指向选择框右下角的控制柄并沿列拖动鼠标至L53即可算出全班其余学生各科的总分。若某生未考试，但不希望在其总分单元格填入“0”，只需将公式修改为“IF(ISNUMBER(E4：L4)，SUM(E4：L4)，)”回车，单元格中会填入空格而不会出现“0”。2. 学生个体班级名次的统计：若要将该班学生的班级排名计算出来，但不希望打乱学生原来按学号的顺序，则可利用RANK函数轻松完成，该函数的最大优点是，能够处理重名次，可使数值相同的单元格计算出的名次相同。如上例在L4：L53区域中存放某班学生的总分，要将学生在班级排名的结果放在N4：N53区域中，只需在N4中输入公式“RANK(L4，$L$4：$L

46、$53)”回车，即可计算出该生在班级中所排名次，然后沿列拖动复制，即将全班学生的排名计算出来。当有并列重复名次时，并列名次后缺少名次数等于名次并列重复数。如果某些学生未参加考试，又不希望在其存放名次的单元格内显示出错信息(如：#！)，则可将公式修改为“IF(ISNUMBER(L4)，RANK(L4，$L$4：$L$53), )”。在计算的过程中我们需要注意两点：首先当RANK函数中的Number不是一个数时，其返回值为“#!”，影响美观。另外，Excel有时将空白单元格当成是数值“0”处理，造成所有成绩空缺者都是最后一名，看上去也很不舒服。此时，可将上面的公式“=RANK(E2,$E$2：$E

47、$50)”改为“=IF(ISNUMBER(E2),RANK(E2,$e$2：$E$50),)”。其含义是先判断E2单元格里面有没有数值，如果有则计算名次，没有则空白。其次当使用RANK函数计算名次时，相同分数算出的名次也相同，这会造成后续名次的空缺，但这并不影响我们的工作。同样的道理，我们也能够算出一个学生的总分在年级内的名次以及各科的班名次和年级名次，但是必须注意参与计算的数字单元格区域不一样。3. 学生个体等级评定成绩的统计：如学校采用等级评定考试成绩，若标准为：考试各科平均分数高于或等于85分为A等；考试分数低于85分且高于或等于70分为B等；考试分数低于70分且高于或等于60分为C等；

48、考试分数低于60分为D等；没有参加考试的不划等级。假设平均分数存放在M列的M4：M53区域，计算结果存入O列O4：O53区域，则用Excel计算等第的公式如下：“IF(M485，A，IF(M470，B，IF(M460，C，IF(ISNUMBER(M4)，D，IF(ISBLANK(M4)，)”，(公式中的等级代码为大写时显示大写,小写时显示小写,其余字母不区分大小写)这是一个IF函数嵌套公式，式中第二个IF语句是第一个IF语句的参数，第三个IF语句则是第二个IF语句的参数，以此类推。如果第一个逻辑判断表达式M485为TRUE(真)，则O4单元格式被填入“A”；如果为FALSE(假)，则计算第二个

49、IF语句“IF(M470”；以此类推直至计算结束。其中ISNUMBER函数在M4为空时返回FALSE(假)，接着执行最后一个IF语句，否则在O4单元格中填入“D”。ISBLANK函数在M4为空时返回TRUE(真)，则在O4单元格中填入一个鉴定会格。使用ISNUMBER函数和ISBLANK函数，可防止某个学生没有参加考试(即考试成绩为空)，但仍然给他评定为D等的情况发生。计算其他学生的成绩等级时，选中O4,鼠标指向选定框右下角的控制手柄并拖动到O53单元格将公式复制即可。如果成绩等级划分标准发生了变化，只须改变逻辑判断式中的值(85，70，60)即可，也可将等级代码“A、B、C、D”分别换成“优

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

51、后即可得到语文平均分。 点击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单元格，松开鼠标，各学科的统计数据就出来了。 再选中M5：Q6单元格区域，点击菜单命令“格式单元格”，打开“单元格格式”对话框

52、。点击“数字”选项卡，在左侧“分类”列表中选择“百分比”，如图3所示，确定后可将M5：Q6单元格区域的数据转变成百分比形式。 至于各科分数段人数的统计，那得先选中M8：M15单元格，在编辑栏中输入公式：=FREQUENCY(C$2：C$95,$K$8：$K$15)。然后按下“Ctrl+Shift+Enter”快捷键，能够看到在公式的最外层加上了一对大括号。现在，我们就已经得到了语文学科各分数段人数了。在K列中的那些数字，就是我们统计各分数段时的分数分界点。 现在再选中M8：M15单元格，拖动其填充句柄向右至Q列，那么，其它学科的分数段人数也立即显示在我们眼前了。 最终的结果如图4所示。如果觉得

53、K列的数据有碍观瞻，那么能够选中它们，然后设置它们的字体颜色为白色就能够了。（八）教学成绩统计表中常用的几个函数简介教师特别是学校的教务处的同志经常要统计学生成绩，实行质量分析，以期改进教学工作，但在使用电子表统计成绩时，绝大部分同志都忽视函数的使用，其实使用函数能够制作成完全自动化的质量分析表，每次考试成绩一出来，只要复制成绩表到统计文件中，质量分析表就能够自动生成了，分年级分班统计都是自动的，想用什么格式的表格就用什么样式的表格，一劳永逸，或乐而不为？以下是质量分析表中常用的几个函数说明，附件中有具体的统计示例请发回复后再下载，谢谢！!i | a U q e%Ap1、函数： COUNT 说明： 返回包含数字以及包含参数列表中的数字的单元格的个数。利用函数 COUNT 能够计算单元格区域或数字数组中数字字段的输入项个数。 语法： COUNT(value1,value2,.) 参数说明： value1, value2, . 为包含或引用各种类型数据的参数（1 到 30个），但只有数字类型的数据才被计算。说明 函数 COUNT 在计数时，将把数字、日期、或以文本代表的数字计算在内；但是错误值或其他无法转换成数字的文字将被忽略。 如果参数是一个数组或引用，那么只统计数组或引用中的数字；数组或引用中的空白单元格、逻辑值、文字或错误值都将被忽略。 如果要统计逻辑