关键词不能为空

当前您在: 主页 > 高中公式大全 >

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

作者:高考题库网
来源:https://www.bjmy2z.cn/gaokao
2020-09-24 20:20
tags:名次公式

英语字母的读法-英文1到10怎么写

2020年9月24日发(作者:籍孝宏)

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


图1 班级学生成绩统计

1、每个班参加考试人数

假设某年级有4个班,学生总数不大于260名,班号在A列(用01、02、03表示各班号),
从2 61行开始为汇总行。要统计的是语文学科(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$$26 0中筛选满足条件为某班的一
组数值,然后通过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(COUNT IF(A$$2:A$$260,0&ROW(A1))*0.3,0)(以下简称为X),
计算前 30% 的人数有多少,并将其四舍五入到整数;再通过ROW(INDIRECT(1:&X) (以下
简称为Y) 由小到大数排列1到前30%的人数的若干个自然数(数组),由
LAR GE(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;=6 0)*(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

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

入木三分-中秋节诗句古诗大全


相互独立事件-笑死


大连的大学排名一览表-圆周运动加速度


谁为大王为此计者翻译-去新加坡留学费用


外地户口孩子上学政策-胡彦斌好听的歌


如何学好英语-四级各题分值


好句摘抄大全-lighted


授时历作者-高一英语必修一unit1



本文更新与2020-09-24 20:20,由作者提供,不代表本网站立场,转载请注明出处:https://www.bjmy2z.cn/gaokao/408665.html

巧用WPS表格数组公式分析班级学生成绩的相关文章