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

excel函数在学生成绩统计中的应用Excel俗称电子表格，是集表格、计算和数据库为一身的优秀软件，其数据处理的核心是函 数和数据库，本文仅介绍用Excel2KX进行成绩统计和一些日常统计的函数。一.考试成绩的统计（一）对班学生成绩的总体评估统计1. 假设E4: E53单元格存放某班学生的语文考试成绩，若要计算该科的总分、人平分、及 格人数、及格率、优秀人数、优秀率、最高（低）分、分段统计等。（1）参考人数统计：若要将参考人数放在 E54单元格中，只需选定该单元格并插入“计数函数”：“COUNT”并选定要计数的单元格区间E4:E53或（输入）公式：“ =COUNT（E4:E53） ”回车即可；（2）学科总分的统计：若要将该科的总分存放在 E55单元格中，只需选定该单元格并插入“求和函数”：“SUM ”并选定要计数的单元格区间E4:E53或（输入）公式：“ =SUM （E4:E53） ”回车后即可计算出该科总分（注：在E55单元格只显示计算结果。如要修改该公式，只需选定该单元格，在 编辑栏内即可修改）。（3）学科平均分的统计：若要将该科人平分存放在 E56单元格中，选定该单元格插入平均值函数“ AVERAGE ”并选定求值区间“ E4: E53”或输入公式：“=AVERAGE（E4 : E53）”，回车后即可计算出人平 分（注：数组或单元格引用中的文字、逻辑值或空白单元格将被忽略，但单元格中的“0”则参与运算）。（3）学科及格人数统计：若要将该科的及格人数存放在E57单元格中，选定该单元格插入计算某个区域内满足指定条件的单元格数目函数的条件函数：“COUNTIF ”，插入函数的方法是单击“插入”菜单中的“插入函数（F）, ”命令，弹出一个“插入函数”对话框，在其中的“选择函数”下 的列表框中选定选定“ COUNTIF ”函数，单击“确定”，弹出一个“函数参数”指定对话 框，在其中的“ Range”右边矫形框中输入统计的区间E4: E53或（单击矫形框右端的压缩图标，再在工作表中选择统计区间 E4： E53，再单击“函数参数”压缩框中右端的扩展图标,Criteria ”右边的矩形框，并键返回扩展的“函数参数”对话框中)，再单击对话框中的入及格条件：“ =60”，最后单击“确定”即可；也可以手动编辑输入公式及条件：“=COUNTIF(E4 : E53,= 60)”回车，即可计算出该科的及格人数(空白单元格、文字、逻辑值、错误值均被忽略 )。(4) 学科及格率统计：若要将该科的及格率存放在E58中，选定该单元格并输入公式“= COUNTIF(E4 : E53,=60)/ COUNT (E4: E53)”回车(COUNT为计数函数，用法是“计数函数”加上“计 数区间”)，即可计算出该科的及格率(其中“ COUNT (E4: E53)”是计算E4: E53区间内 单元格中的实际参考人数，空白单元格将被忽略)。(5) 学科优生人数统计:若要统计学科优生人数与学科及格人数统计类似。如:将该科的优秀人数存放在E59 单元格中，选定该单元格插入条件函数:“ COUNTIF ”，方法与统计及格人数一样，只是函数 参数对对话框中“ Criteria ”右边的矩形框中的函数参数要修改为：“=80” (优分标准);(6) 学科优分率统计:学科优生百分率的统计与学科及格率的统计相同。如:将该科的优秀率存放在E60 单元格中，选定该单元格并输入公式：“=COUNTIF(E4 : E53, = 80 COUNT (E4 : E53)”。这与及格率公式的编辑只是参数“=60”变成了 “= 80”而已。求优秀率： 例如： B2:B50 存放的是初一一班的语文期末考试成绩， B2:B500 存放的是初一 全年级语文考试的成绩，如果规定全年级 20%的学生为优秀，那么初一一班语文的优秀率 应该这样计算： “COUNTIF(B2:B50,=&LARGE(B2:B500,INT(0.2COUNT(B2:B500)/ COUNT(B2:B50) ”，其中 LARGE(B2:B500,INT(0.2所求的是全年级语文分数前 20%中最低的一个同学的分数，COUNTIF(B2:B50,=&LARGE(B2:B500,INT(0.2*COUNT(B2:B500) 则是求出了初一一班语文高于或等于这个同学分数的人数，最后再除 以初一一班的总人数 COUNT(B2:B50) ，所得就是初一一班的语文优秀率，如果想求其他班 其他科目的优秀率，道理都是一样。(7) 学科最高分的查询统计：若要查询统计学科最高分，则可利用“ MAX ” (最大值 )函数。如将 E4：E53 区域中存放着E61和E62单元格的全班 50 名学生语文科的考试成绩，将其学科最高分、最低分别存放在 中，则可选中 E61 单元格，插入“最大值”函数： “MAX ”并指定统计数据的区域：“ E4：E53”或在编辑栏输入公式“=MAX（E4 : E53） ”回车后即可计算出其中的最高分。（ 8）学科最低分的查询统计：如将 E4: E53 区域中存放着的全班 50 名学生语文科的考试成绩，将其学科最低分别存放在E62单元格中，则可选中 E62单元格，插入“最小值”函数：“MIN ”并指定统计数据的区域：“ E4: E53”或在编辑栏输入公式“= MIN（E4 : E53）”回车后即可计算出其中的最低 分。（9）若在“ F4:F53”、“ G4:G53 ” ” “K4:K53 ”区域中分别存放数学、英语、计算机、音、体、美等学科成绩，则只需选定E54： E62 区域，移动空心十字光标至单元格右下角。待光标变成小实心十字光标时，按住鼠标左键沿行拖动控制手柄至K 列即可，放开鼠标左键后，则各科的科总分、人平分、及格人数、及格率、优秀人数、优秀率、最高分、最低分 等立即计算出来。2. 班学生成绩的分段统计：若要将学生成绩按分数段进行统计， 则可利用 FREQUENCY 函数（以一列垂直数组及对该数 组设定进行频率颁布计算的分段点计算出一组数据的频率颁布）。假设E4: E53共有50个学生的语文科测试成绩，那么可在 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 键敲回车 （这是生成数组公式的关键环节 ）， Exce l会自动在公式两侧加上大括号，公式成为“ = FREQUENCY（E4 : E53, $B$66 : $B$70） ” ,则区间 C66:C70 各单元格中立刻计算出对应各分数段中成绩的个数。（二）对班中学生个体成绩的评估统计1. 学生个体总成绩的统计若在工作表中 E4 K53 区域存放某班 50 个学生各科的成绩， 而 E4:K4、 E5 K5、 ,E53K53 中分别各个学生语文、数学、美术等各科成绩记录。则需在 L 列中的 L4 L53L4单元格插入求和函数“S区间单元格中分别存放各学生的各门学科成绩总分，则先选定UM”并选定求和区域回车或手动输入公式“=SUM (E4:K4) ”回车即可计算出该生各科的总分；然后选定 L4 单元格，并将鼠标指针指向选择框右下角的控制柄并沿列拖动鼠标至L53 即可算出全班其余学生各科的总分。 若某生未考试， 但不希望在其总分单元格填入“0”，只需将公式修改为“=IF(ISNUMBER(E4 : L4), SUM(E4 : L4)”回车，单元格中会 填入空格而不会出现“ 0”。2. 学生个体班级名次的统计:若要将该班学生的班级排名计算出来，但不希望打乱学生原来按学号的顺序，则可利用RANK 函数轻松完成，该函数的最大优点是，可以处理重名次，可使数值相同的单元格计算出 的名次相同。如上例在 L4: L53 区域中存放某班学生的总分，要将学生在班级排名的结果 放在N4 : N53区域中，只需在 N4中输入公式“= RANK(L4 , $L$4 : $L$53) ”回车，即可 计算出该生在班级中所排名次， 然后沿列拖动复制， 即将全班学生的排名计算出来。 当有并 列重复名次时，并列名次后缺少名次数等于名次并列重复数。如果某些学生未参加考试，又不希望在其存放名次的单元格内显示出错信息(如： #VALUE!)，则可将公式修改为 “= IF(ISNUMBER(L4) , RANK(L4 , $L$4:$L$53),” )”。 在计算的过程中我们需要注意两点:首先当 RANK 函数中的 Number 不是一个数时，其返 回值为 “#VALUE”! ，影响美观。另外， Excel 有时将空白单元格当成是数值“0”处理，造成所有成绩空缺者都是最后一名， 看上去也很不舒服。 此时， 可将上面的公式 “=RANK(E2,$E$2: $E$50) ”改为 “ =IF(ISNUMBER(E2),RANK(E2,$e$2:$E$50),) 。其含”义是先判断 E2 单元格里 面有没有数值，如果有则计算名次，没有则空白。其次当使用RANK 函数计算名次时，相同分数算出的名次也相同， 这会造成后续名次的空缺， 但这并不影响我们的工作。 同样的道 理，我们也可以算出一个学生的总分在年级内的名次以及各科的班名次和年级名次，但是必须注意参与计算的数字单元格区域不一样。3. 学生个体等级评定成绩的统计:如学校采用等级评定考试成绩，若标准为:考试各科平均分数高于或等于85 分为 A 等；考IF试分数低于 85 分且高于或等于 70 分为 B 等；考试分数低于 70 分且高于或等于 60 分为 C 等；考试分数低于 60分为 D 等；没有参加考试的不划等级。假设平均分数存放在 M 列的 M4:M53区域，计算结果存入 0列04:053区域，则用Excel计算等第的公式如下：“=(M4 = 85, ” A ，IF(M4 = 70,B，IF(M4 = 60, C，IF(ISNUMBER(M4)ffD，IF（ISBLANK（M4） ,），”公式中的等级代码为大写时显示大写，小写时显示小写,其余字母不区分大小写 ）这是一个 IF 函数嵌套公式, 式中第二个 IF 语句是第一个 IF 语句 的参数,第三个 IF 语句则是第二个 IF 语句的参数,以此类推。如果第一个逻辑判断表达式M4 = 85为TRUE（真），则04单元格式被填入“ A ”；如果为FALSE（假），则计算第二个IF语句“ IF（M4 = 70” ；以此类推直至计算结束。其中ISNUMBER函数在M4为空时返回 FALSE（ 假）,接着执行最后一个 IF 语句,否则在 04 单元格中填入“ D”。 ISBLANK 函 数在M4为空时返回TRUE（真），则在04单元格中填入一个鉴定会格。使用ISNUMBER函数和 ISBLANK 函数,可防止某个学生没有参加考试（即考试成绩为空 ）,但仍然给他评定为D 等的情况发生。计算其他学生的成绩等级时,选中04，鼠标指向选定框右下角的控制手柄并拖动到 053 单元格将公式复制即可。如果成绩等级划分标准发生了变化,只须改变逻辑判断式中的值 （85, 70, 60）即可,也可将 等级代码“ A、B、C、D”分别换成“优秀、良好、及格、不及格”等。