巧用WPS表格数组公式分析班级学生成绩

学校对于某年级各教学班的考试成绩分析,一般要统计每个班的参加考试人数、及格率、平均分(或每班前若干名学生的平均分)、学生个体在本教学班中的名次等,如果仅用WPS表格的内置函数计算,则需要一个班一个班的分块后计算,非常麻烦。笔者通过巧妙组合WPS表格的内置函数编写出数组公式,简捷方便地完成了以上各种特征量数计算(如图1所示)。



图1 班级学生成绩统计

1、每个班参加考试人数

假设某年级有4个班,学生总数不大于260名,班号在A列(用01、02、03…表示各班号),从261行开始为汇总行。要统计的是语文学科(C 列)的各班参加考试人数(每个班有得分的学生数,即非空格数),则在要计算各班参加考试人数的纵向连续的第一个单元格C262中输入公式:

=SUM((A$2:A$260=0&ROW(A1))*(C$2:C$260<>""))

按Ctrl+Shift+Enter组合键确认输入后,向下拖动填充到全部班级单元格C262 :C265,即可得到各班的该学科参加考试人数。

上述公式为数组公式,在编辑栏输入这种公式后,必须按Ctrl+Shift+Enter组合键确认,确认后在公式两边自动加上花括号“{}”(如图2)。



图2 学科实考统计

推荐:点击免费下载最新版WPS办公软件 》》查看更多

公式解释:将班号列所在区域A$2:A$260为“某班”(0&ROW(A1))和某学科考分列所在区域C$2:C$260为非空单元格 (<>"")两个条件做乘法运算。当两个条件都成立时返回1(TRUE* TRUE=1),否则返回0(TRUE* FALSE=0、FALSE *TRUE =0、FALSE * FALSE=0)。逻辑判断的结果组成0、1数组,通过函数SUM()将所有的0、1值求和,得出该学科参加考试人数。

2、每个班所有考生的总平均分

假设E列为“总分”列,在要计算各班总平均分的纵向连续的第一个单元格E262中输入数组公式:

=AVERAGE(IF(A$2:A$260=0&ROW(A1),E$2:E$260))

按Ctrl+Shift+Enter组合键确认后向下拖动填充到全部班级单元格E262 :E265,得到每个班所有考生的平均分(如图3)。



图3 每个班的平均分

公式解释:首先通过IF()函数,在总分所在区域E$2:E$260中筛选满足条件为“某班”的一组数值,然后通过AVERAGE()对所筛选的数值求平均值。

3、每个班前若干名学生的平均分

为了分层测试教学效果,往往按截取一部分学生的成绩作为样本计算。例如,计算各班前30%学生的平均分,需在F262单元格中输入数组公式如下:

=AVERAGE(LARGE(IF($A$2:$A$260=0&ROW(A1),E$2:E$260),ROW(INDIRECT("1:"&ROUND(COUNTIF(A$2:A$260,0&ROW(A1))*0.3,0)))))

然后将此公式向下拖动到F265单元格(如图4)。



图4 各班前30%平均分

公式解释:首先通过ROUND(COUNTIF(A$2:A$260,0&ROW(A1))*0.3,0)(以下简称为X),计算前30% 的人数有多少,并将其四舍五入到整数;再通过ROW(INDIRECT("1:"&X) (以下简称为Y) 由小到大数排列1到前30%的人数的若干个自然数(数组),由LARGE(IF($A$2:$A$260=0& ROW(A1),E$2:E$260),Y)抽取总分所在区域E$2:E$260中某班前30%学生的得分(数组),最后通过AVERAGE()求选取数值的平均分。

4、每个班的学科及格率:

假如语文学科(C列)及格分数为60分,则在要计算各班及格率的纵向连续的第一个单元格G262中输入数组公式:

=SUM((A$2:A$260=0&ROW(A1))*(C$2:C$260>=60))/SUM((A$2:A$260=0&ROW(A1))*(C$2:C$260<>""))

按Ctrl+Shift+Enter组合键确认后向下拖动填充到全部班级单元格G262:G265,得到各班该学科的及格率(如图5)。



图5 学科及格率

假如语文(C列)、数学(D列)中的学科及格分数分别为60分、72分,则计算每个班这两个学科都及格的人数的数组公式为:

=SUM((A$2:A$260=0&ROW(A1))*(C$2:C$260>=60)*(D$2:D$260>=72))

5、每个学生在本班的名次

一个学生在全年级的名次,可用函数RANK()求出,而该学生在本班内的名次,却没有现成的内置函数可用,我们仍然可以巧用数组公式来解决。在名次列的第一个学生成绩行所在单元格F2中输入数组公式:

=SUM((A$2:A$260=A2)*(E$2:E$260>E2),1)

按Ctrl+Shift+Enter组合键确认后向下拖动到最后一行,得到每个学生在本班的名次(如图6)。



图6 班内排名次

公式解释:此公式是通过求本班大于该学生总分的人数与1的和,得到该学生在本教学班的名次。

如下的两个数组公式与上述公式等价(见G列和H列的名次1和名次2):

=SUM(((A$2:A$260=A2)*E$2:E$260>E2)*1,1)

=SUM(IF((A$2:A$260=A2)*E$2:E$260>E2,1))+1

由上面几个例子可见,数组公式可以解决许多有条件的统计问题。为了方便,我们可以根据上面讲到的各数组公式,设置成考试成绩统计模板应用于每次考试,起到一劳永逸的作用。

(0)

相关推荐

  • 用WPS表格的高级筛选功能分析学生成绩的方法(图文教程)

    考试结束班主任都要对学生的成绩进行一番分析,有时还需要复制总分比较高但某门课比较弱的学生全部成绩送给任课老师以便有针对性地进行辅导。类似的工作在WPS表格中我们可以借助数据的高级筛选功能来轻松完成。如 ...

  • 巧用WPS表格综合分析学生成绩信息

    一、班级排名 首先如图1.1所示设计表格并输入内容,学号部分可以输入第一项“20080801”然后选中此单元格,将鼠标移动到单元格右下角待其变为实心十字“+” 后,按住左键下拉拖动至需要填充的表格行数 ...

  • WPS表格简单公式运算

    WPS表格简单公式运算 操作方法 01 选中要得到结果的单元格 02 输入等于号 03 选择公式的第一单元格 04 输入运算符(我这里做个减法运算) 05 单击要减去的单元格 06 回车,得出值

  • WPS表格求和公式怎么使用

    WPS表格求和公式怎么使用呢?很多人都不会,下面小编来教大家. 操作方法 01 首先,我们打开我们电脑上面的wps表格,然后我们在里面输入一些数据: 02 然后我们选中图示中的单元格,之后点击工具栏中 ...

  • 用ET表格打造更直观的学生成绩分析之相关函数

    前些天在论坛发了一帖:《用ET表格打造更直观的学生成绩分析》(以下简称为《成绩分析》),http://bbs.wps.cn/thread-21963426-1-1.html。很多坛友对文中所涉及的函数 ...

  • 巧用WPS表格提取身份证个人信息

    一、文章简介: 本文主要向读者介绍如何利用WPS表格的函数功能,根据个人身份证号,一劳永逸地提取出生日期、性别等个人信息,实现个人信息的自动录入。 二、巧用WPS函数功能 最近,单位需要上报人事局一份 ...

  • WPS表格高级筛选分析学生成绩

    考试结束班主任都要对学生的成绩进行一番分析,有时还需要复制总分比较高但某门课比较弱的学生全部成绩送给任课老师以便有针对性地进行辅导。类似的工作在WPS表格中我们可以借助数据的高级筛选功能来轻松完成。如 ...

  • 巧用WPS表格制作倒计时

    倒计时把重要时刻以倒计的方式呈现在人们面前,简单明了,清楚准确,突出地表示出时间的逐渐减少,使人们产生紧迫感,相伴产生的是一种期待心理,是较为科学的计时方法。你知道吗?WPS表格可以轻松制作出倒计时。 ...

  • WPS表格中公式的应用?

    在我们使用wps表格软件处理数据时,经常会用到很多公式去计算数据的结果,但是当我们看到wps表格所有的公式,那么多的公式有没有让你不知所措,不知道用哪个,或者说,哪个是需要的.下面,小编来为大家整理了 ...