Excel函数公式在高校“个性化”成绩分析表中的运用

文/陈英芳

1 案例分析，制作成绩表

班级：普通专科财务管理18-1班，课程：《计算机应用基础》，人数：32人，班上学习积极，但是计算机水平参差不齐，部分学生基础好，但是班上的几位藏族同学在以前很少接触过电脑，基础差，因此成绩表要反应出学生名次、全班平均成绩、汉族和藏族学生差异、各分数段人数。本课程属于考查课，注重过程化评价，平时成绩40%，期末上机卷面成绩占60%，其中平时成绩=课堂活动表现*40%+模拟考试一*30%+模拟考试二*30%（所有成绩百分制），成绩表中需反应出平时成绩与期末卷面成绩的差距，关注考试“失常”考生，并备注：可疑。根据以上需求，建立如图1所示的工作表。

2 利用Excel函数公式完善工作表

（1）用Excel公式计算平时和期末成绩，在目标单元格输入：=表达式，在本案例中，在H2单元格输入：=E2*40%+F2*30%+G2*30%，在J2单元格输入：=H2*40%+I2*60%，然后利用Excel的自动填充功能向下拖动鼠标，平时成绩和综合成绩就全算出来了。

（2）利用RANK函数计算成绩排名。RANK函数在不改变原表单顺序情况下进行排序,语法为：RANK（Number,ref,order),主要功能是返回某数字在一列数字中相对于其他数字的大小排位。其中“Number”是要查找排名的数字（单元格地址），“ref”是一组数或对一个数据列表的引用，“order”表示排序方式参数，如果为0或忽略，降序；非零值，升序。本案例中，如图2，在K2单元格输入=RANK(J2,$J$2:$J$33,0)，然后利用Excel的自动填充功能向下拖动鼠标，名次就全算出来了，需要注意的是：如果采用自动填充功能，ref的参数框要用绝对地址$J$2:$J$33，不然会出错。

图1

图2

（3）利用IF函数判定成绩是否异常。平时成绩和卷面成绩的差距过大，则考试可能有异常情况，先计算平时成绩和卷面成绩的差距，若差距大于20，在备注栏标记可疑。具体方法：在L2单元格输入公式：=I2-H2然后按回车键，利用Excel的自动填充功能，得出所有学生的平时成绩和卷面成绩之差，然后用IF函数进行判定，在M2单元格输入：=IF(OR(L2>20,L2<-20),"可疑"," ")，这里用到了IF函数的“或”关系，指平时成绩和卷面成绩相差20分即标注为可疑，其他显示为空白。

（4）利用AVERAGE函数计算全班学生的平均成绩。AVERAGE函数的语法为：AVERAGE(number1,number2……)其功能是计算所有参数数值的平均值。本案例中在O3单元格输入：=AVERAGE(J2:J33)就得到全班平均成绩。

（5）利用COUNTIF函数计算汉族学生人数和藏族学生人数。COUNTIF函数的主要功能是计算区域中满足给定条件的单元格个数，在本案例中，O5单元格输入：=COUNTIF(D2:D33,"汉")，O6单元格输入：=COUNTIF(D2:D33,"藏")即可得到相应人数。

（6）利用SUMIF（）函数求出汉族学生成绩之和和藏族学生成绩之和，除以相应人数得到汉族和藏族同学的平均成绩。

本案例中，在O8单元格输入：=SUMIF(D2:J33,"汉",J2:J33)/O5得到汉族学生平均成绩，在O9单元格输入：=SUMIF(D2:J33,"藏",J2:J33)/O6得到藏族学生平均成绩，了解民族学生之间的计算机水平差异能更好地因材施教，促进教学。

（7）FREQUENCY函数是一个频率分布函数，其语法为：FREQUENCY(data array,bins array)，它是以一列垂直数组返回一组数组的频率分布，其中，“data array”是用来计算频率的数组，“bins array”是数据接收区间，也就是分段的界值，该函数返回的是数组，必须以数组公式的形式进行输入。本案例中，学生综合成绩保留的一位小数，分段界值设置为59.9，如图3，选中P13到p17五个单元格区域，插入函数FREQUENCY,输入第一个参数J2:J33,第二个参数O13:O17，然后按下组合键Ctrl+Shift+Enter，就得到各分数段人数。

3 结语

结合本班需求的“个性化”成绩分析表能更好地反应出本班的学习情况，反馈和导向教师教学，Excel拥有强大的数据处理功能，合理运用函数公式来制作“个性化”成绩分析表，教师充分地利用“数据”反馈教学效果，调整教学，有效促进教学。

图3