Excel及其函数在学科成绩统计与日常生活中的应用.docx.pdf

《Excel及其函数在学科成绩统计与日常生活中的应用.docx.pdf》由会员分享，可在线阅读，更多相关《Excel及其函数在学科成绩统计与日常生活中的应用.docx.pdf（9页珍藏版）》请在三一文库上搜索。

1、Excel及其函数在学科成绩统计与日常生活中的应用 Excel俗称电子表格，是集表格、计算和数据库为身的优秀软 件，其数据处理的核心是函数和数据库，木文仅介绍用E xcel2KX进 行成绩统计和一些日常统计的函数。 一. 考试成绩的统计 (-) 对班学生成绩的总体评估统计 1.假设E4： E53单元格存放某班学生的语文考试成绩，若要计 算该科的总分、人平分、及格人数、及格率、优秀人数、优秀率、最 高( 低) 分、分段统计等。 (1)参考人数统计： 若要将参考人数放在E54单元格中，只需选定该单元格并插入 “计数函数”COUNT”并选定要计数的单元格区间E4:E53 或( 输 入) 公式: u=

2、COUNT(E4:E53)n 回车即可 ; (2)学科总分的统计： 若耍将该科的总分存放在E55单元格中，只需选定该单元格并插 入“求和函数”：“SUM ”并选定要计数的单元格区间E4:E 53或( 输 入) 公式: M = SUM (E4:E53) ,回车后即可计算出该 科总分（注：在E55 单元格只显示计算结果。如要修改该公式, 只需选定该单元格，在编 辑栏内即可修改）。 （3）学科平均分的统计： 若要将该科人平分存放在E56单元格中，选定该单元格插入平均 值函数“AVERAGE”并选定求值区间“E4：E53”或输入公式: U = AVERAGE （E4： E53）”，回车后即可计算出人平

3、分（注：数组或 单元格引用中的文字、逻辑值或空白单元格将被忽略，但单元格中的 “0”则参与运算）。 （3）学科及格人数统计： 若要将该科的及格人数存放在E57单元格中，选定该单元格插入 计算 某个区域内满足指定条件的单元格数目函数的条件 函数 ： “COUNTIF ”，插入函数的方法是单击“插入”菜单中的“插入函数 （E）?”命令，弹出一个“插入函数”对话框，在其中 的“选择 函数”下的列表框屮选定选定“COUNTIF ”函数，单击“确定”，弹出 一个“函数参数”指定对话框，在其中的“Range ”右边 矫形框中输入统 计的区间E4： E53或（单击矫形框右端的压缩图标，再在工作表中选 择统计

4、区间E4： E53,再单击“函数参数”压缩框中右端的扩展图标， 返回扩展的“函数参数”对话框屮），再单击对话框屮的“Ciiteda ”右 边的矩形框，并键入及格条件：、 =60”，最后单击“确定”即可；也 可以手动编辑输入公式及条件 : M = C0UNTIF（E4：E53, ”=60“）” 冋车，即可计算岀该科的及格人数（空白单元格、文字、逻辑值、错误 值均被忽略）。 （4）学科及格率统计 : 若要将该科的及格率存放在E58中，选定该单元格并输入公式 H = COUNTIF （E4：E53, ”=60“）/ COUNT （E4： E53）” 回车 （COUNT为计数函数，用法是“计数函数”加

5、上“计数区间）即可 计算出该科的及格率（其中“COUNT （E4： E53）”是计算E4： E53区间内单元格中的实际参考人数，空白单元格将被忽略）。 （5）学科优生人数统计： 若要统计学科优生人数与学科及格人数统计类似。女口：将该科 的优秀人数存放在E59单元格中，选定该单元格插入条件函数： “COUNTIF ”，方法与统计及格人数一样，只是函数参数对对话框屮 “Criteria”右边的矩形框中的函数参数要修改为：、=80” （优分标 准）； （6）学科优分率统计： 学科优生百分率的统计与学科及格率的统计相同。女II：将 该科 的优秀率存放在E60单元格中，选定该单元格并输入公式： U =

6、COUNTIF （E4： E53, “ = 80r,）/COUNT （E4： E53）” 。 这与及格率公式的编辑只是参数 f=60n变成了 u = 8(r而Lio 求优秀率：例如：B2:B50存放的是初班的语文期末考试成绩， B2:B500存放的是初一全年级语文考试的成绩，如果规定全年级20% 的 学 生 为 优 秀 ， 那 么 初 一 一 班 语 文 的 优 秀 率 应该 这 样 计 算: UCOUNTIF(B2:B50 ； ,=M &LARGE(B2:B500,INT (0.2 COUNT(B2:B500)/COUNT(B2:B50) , 其中 LARGE(B 2:B500,INT(0.

7、2 COUNT(B2:B500)所求的是全年级语文分 数前20%屮最低的一个同学的分数，COUNTIF(B2:B50, n=n& &LARGE(B2:B500,INT(0.2*COUNT(B2:B500)则 是求出了 初 一一班语文高于或等于这个同学分数的人数，最后再除以初一一班的 总人数COUNT(B2:B50),所得就是初班的语文优秀率，如果想 求其他班其他科目的优秀率，道理都是一样。 (7)学科最高分的查询统计： 若要查询统计学科最高分，则可利用“MAX ”( 最大值 ) 函数。如将 E4： E53区域中存放着的全班50名学生语文科的考试成绩，将其学 科最高分、最低分别存放在E61和E6

8、2单元格中， 则可选中E61单元 格， 插入“最大值”函数：“MAX ”并指定统计数据的区域：“E4：E53” 或在编辑栏输入公式 U = MAX(E4： E5 3)”回车后即可计算出其中的 最高分。 （8）学科最低分的查询统计： 如将E4：E53区域中存放着的全班50名学生语文科的考试成 绩，将其学科最低分别存放在E62单元格中，则可选中E62 单元格， 插入“最小值”函数MIN ”并指立统计数据的区域E 4：E53”或在 编辑栏输入公式 U = MIN （E4： E53）”回车后即可计算出其中的最 低分。 （9） 若在“F4:F53”、“G4:G53 ” “K4:K53”区域中分别存 放数

9、学、英语、计算机、音、体、美等学科成绩，则只需选定E54： E62区域，移动空心十字光标至单元格右下角。待光标变成小实心十 字光标时，按住鼠标左键沿行拖动控制手柄至K 列即可，放开鼠标左 键后，则各科的科总分、人平分、及格人数、及格率、优秀人数、优 秀率、最高分、最低分等立即计算出来。 2.班学生成绩的分段统计： 若要将学生成绩按分数段进行统计，则可利用FREQUENC Y函数 （以一列垂直数组及对该数组设定进行频率颁布计算的分段点计算 出组数据的频率颁布）。假设E4： E53共有50个 学生的语文科测 试成绩，那么可在B66： B70单元格分别输入59、69、79、89、100, 则表示统计

10、59（包括59）分以下、606 9、7079、8089、90 100分数段的成绩个数。 若要将计算结果存入C66： C70,则选中C66： C70单元格 区域， 在编辑栏内输入公式FREQUENCY (E4： E53, $B$6 6：$B$70)”， ( 注：公式中区域 : M E4：E53”是用来计算频率的数组区域一一班学生 学科成绩的单元区域，公式屮区域: U$B$7 0 ：$B$70”是对所计算频率 数组一一班学生学科成绩进行频率计算的分段界点的数组区域的绝对 引用) ，输完后让光标停在公式末尾，按下SHIFT+CTRL键敲回车 ( 这 是生成数组公式的关键环节 ),Excel会口动在公

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

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

13、 在N4： N53区域中，只需在N4 屮输入公式 U = RANK(L4, $L$4： $L$53) ”冋车，即可计算出该生在班级中所排名次， 然后沿列拖动复制， 即将全班学生的排名计算出来。 当有并列重复名次时， 并列名次后缺少 名次数等于名次并列重复数。 如果某些学生未参加考试， 又不希望在其存放名次的单元格内显示 出错信息( 如： #!),则可将公式修改为 U = IF(ISNUM BER(L4), RANK(L4, $L$4:$L$53),“ ”) ”。 在计算的过程中我们需要注意两点：首先当RANK函数中的 Number不是一个数时，其返回值为篁#! 二 影响美观。另夕卜，Excel

14、 有吋将空口单元格当成是数值V处理，造成所有成绩空缺者都是最后 一 名 ， 看 上 去 也 很 不 舒 服 。 此 时 ， 可 将 上 而 的 公式 “=RANK(E2,$E$2:$E$50 广改为“=IF(ISNUMBER(E2),RANK (E2,$e$2:$E$50),y。其含义是先判断E2单元格里面有没有数值，如 果有则计算名次，没有则空白。其次当使用RANK函 数计算名次 吋，相同分数算出的名次也相同，这会造成后续名次的空缺，但这并 不影响我们的工作。同样的道理，我们也可以算出一个学生的总分在 年级内的名次以及各科的班名次和年级名次，但是必须注意参与计算 的数字单元格区域不-?样。

15、3.学生个体等级评定成绩的统计： 如学校采用等级评定考试成绩，若标准为： 考试各科平均分数高于 或等于85分为A等； 考试分数低于85分且高于或等于70分为B等； 考试分数低于70分且高于或等于60分为C 等；考试分数低于60分为 D等； 没有参加考试的不划等级。假设平均分数存放在M列的M4:M53 区域，计算结果存入O 列04:053区域，则用Excel计算等第的公式如 下：=IF(M4 =85, “A“, IF(M4=70, “B“, IF(M4=60, “C“, IF(I SNUMBER(M4), “D“, IF(ISBLANK(M4), “)”,( 公式中的等 级代码为大写时显示大写，

16、小写时显示小写, 其余字母不区分大小写 ) 这是一个IF函数嵌套公式，式中第二个IF语句是第一个IF语句的参 数，第三个IF语句则是第二个IF语句的参数， 以此类推。如果第一个 逻辑判断表达式M4 = 85为TRUE ( 真)， 则04单元格式被填入“A”； 如果为FALSE(假)，则计算 第二个IF语彳IF(M4=70”；以此类推 直至计算结束。其中 ISNUMBER函数在M4为空时返冋FALSER）,接着执行最后一个 IF语句，否则在04单元格中填入 M D no ISBLANK 函数在M4为空吋 返回TRUE（真），则在04单元格中填入一个鉴定会格。使用 ISNUMBER函数和ISBLANK函数，可防止某个学生没有参加考试 （即考试成绩为空），但仍然给他评定为D等的情况发生。计算其他 学生的成绩等级时，选中04,鼠标指向选定框右下角的控制手柄并拖动 到053单元格将公式复制即可。 如果成绩等级划分标准发生了变化，只须改变逻辑判断式屮的值 （85, 70, 60）即可，也可将等级代码“A、B、C、D”分别 换成“优 秀、良好、及格、不及格”等。