物理公式推导巧用EXCEL公式进行成绩统计与分析

用EXCEL公式对学生进行成绩统计与分析
成绩的统计与分析是学校教学中的一个重要环节 。通过对学生每次考试的成
绩的合理分析，可以及时了解学生在学习过程中出现的问题和老师在教学中存 在
的不足，进而及时改正。很多老师在教学管理中或多或少的使用EXCEL进行成
绩的统计与 分析，然而由于对EXCEL使用不够深入，不能正确合理的使用公式，
往往也要花费很大的精力。其实 只要熟练地掌握以下几个公式，就可以大大地提
高工作的效率。下面就以我在实践中得出的一点经验为例 ，讲解一下学生成绩的
统计与分析一般过程，以期与大家交流与分享：
一、成绩的录入 1、单科成绩录入。现在很多学校都是全年级各班混在一起考试，以防考试
改卷中的不正当竞争，考 前全年级统一进行考场和座号的安排，每个考场里分布
几个甚至十几个班的学生，考完试卷按考场装订阅 卷，所以成绩录入时应按考场
和考号顺序录入。这样增加了透明度，公平合理。如表1：

2、成绩汇总。把各个备课组送交上的成绩表，通过按学号排序，全年级按
班以及班里学号的顺 序将成绩汇总成一张总表，如表2：

二、成绩分析
1、排名统计。很多学校都 是把每次考试成绩来做为每个科任老师教学能力
的重要指标，统计每个科任老师所教科目在全年级各名次 段中占有的人数比例是
必不可少的内容。用“=RANK(D28,$$D$$28:$$D$$32)”求出每 个学生的各科成绩在
全年级的排名，注意“$$”符号在该函数中的作用，是不能省略的。然后统计每个科目在每个名次段的人数。假设在D列是一个班的语文成绩的排名我们可以
采用EXCEL公式“ =COUNTIF(成绩表!D28:D32,“来完成计算该科任
老师所教科目在全年级前200名的 学生人数。如表3：

2、利用图表分析每个班的每个科目的情况。根据该班某科的分数段 分布可
清晰得出是否发展正常。

利用公式“=COUNTIF(成绩表!C28:C 32,成绩表!C28:C32,
”可以轻松获得成绩在130~140的人数，如表4。然后通过该表 绘制相
关图表，如表5。




三、查询技巧

许多学校为了鼓励一些家庭困难的学生，根据考试成绩对品学兼优的学生进
行一些奖励和表扬措 施。学校为这些学生建立了电子档案，这就要求我们查找相
关学生的学习成绩，以往很多人都是通过EX CEL提供的查找功能，查找到该学
生的学号或姓名，然后复制该同学的成绩，这就大大增加了我们的工 作量，其实
可以通过公式，顺利的完成这项工作。假设全年级成绩表中的区域大小为A1：
DV 1209（即包含所有学生的成绩），我们在奖励一表的C30单元格里输入公式
“=IF(ISERR OR(VLOOKUP(A30,成绩表!$$A$$1: $$DV$$1209,2,FALSE)),
KUP(A30,成绩表!$$A$$1: $$DV$$1209, 2,FALSE))”即可求得该学生的语文成绩。下
面详细说明上述公式中各项的作用：公式的意思是 以单元格A30作为查询内容，
查找在成绩表中A1：DV1209区域中的值等于A30的行的第二列 的值，如果查
不到则显示为空，否则显示该单元格的值。当然我们也可以灵活改动为“无此
人” 等。这个函数较为复杂，是一个公式的牵套。下面我们对每个公式进行详细
说明：
1、ISE RROR函数的主要作用其实就是判断一个单元格是否是错误提示，语法是
ISERROR( 单元格 )，如果一个单元格出错，那么ISERROR的返回结果将是T
RUE，否则就是FALSE。当然， 单独使用这个函数还仍然无法达到我们隐藏错
误提示的目的，下面就要给大家介绍完成这项任务的另一员 大将—— IF函数
2、IF函数的主要作用也很简单，它可以根据条件来返回不同的数值，语法是I
F(条件 , 当条件为真时的值 , 当条件为假时的值)，比如我们将公式设为IF(A1>
0 , “通过” , “拦截”)，那么在工作时Excel会自动判断A1单元格的值，如
果它大于0，那么当前单元格便会 显示出“通过”两字，反之如果A1单元格的
值小于或等于0，那么当前单元格便会显示出“拦截”两个 字。这样，通过将I
F和ISERROR两个函数配合使用，就可以先让ISERROR判断目标单元格 是否
显示错误，如果是，那么ISERROR就返回一个真值给IF，而IF函数再根据这
个真 值在当前单元格显示一个空白（比如“”）来盖住原来的出错提示，而如果
目标单元格的值不是错误，那 么直接显示原来的公式就可以了。
3、VLOOKUP(lookup_,table_array, col_index_num,range_lookup)
（1）Lookup_ 为需要在数据表第一列中查找的数值。Lookup_ 可以为数值、
引用或文字串。（2）Col_index_num 为 table_array 中待返回的匹配值的列序
号。Col_index_num 为 1 时，返回 table_array 第一列中的数值；col_index_nu
m 为 2，返回 table_array 第二列中的数值，以此类推。如果 col_index_num
小于 1，函数 VLOOKUP 返回错误值 #!；如果 col_index_num 大于 table_arr
ay 的列数，函数 VLOOKUP 返回错误值 #REF!。（3）Range_lookup 为一逻
辑值，指明函数 VLOOKUP 返回时是精确匹配还是近似匹配。如果为 TRUE
或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于 l
ookup_ 的最大数值；如果 range_ 为 FALSE，函数 VLOOKUP 将返回精确匹
配值。如果找不到，则返回错误值 #NA。
EXCEL已经成为办公 自动化软件中最常用的软件之一，小到课程表，大到
数据分析，受到越来越多人的青睐。熟练使用EXC EL可以解决教育教学管理很
多数据的分析与统计。