SUMIF、COUNTIF和SUMPRODUCT函数在成绩统计中的应用

..SUMIF、COUNTIF和SUMPRODUCT函数在成绩统计中的应用在学校组织的学年考试考务工作中,为了便于评卷实行流水作业和避免学生作弊,经常将同一年级不同班级的学生拆散混编。在考试登分完毕之后,如何做到不改变原始表格的状态而进行分班成绩统计呢？利用SUMIF、COUNTIF和SUMPRODUCT这三个函数,一切都将会变得十分轻松。下面举例说明。

一、造册登分

登分册一般有班级、考号、姓名及语文、数学等学科名称,我们可以把这个工作表建立在Sheet1,并将其重命名为"登分册",将学生成绩输入其中〔见图一。将Sheet2命名为"统计表",在其中设置好班级、科目、平均分、及格人数、及格率、优生人数、优生率等统计信息列标题,输入班级和需要统计的科目内容〔如图二。二、建立函数

在"统计表"中的C2单元格输入数组公式=SUMIF<登分册!$A$2：A$13,"一〔1",登分册!$D$2：D$13>,按住Ctrl和Shift键回车可以计算出一〔1班的总分,将公式向下复制到各单元格,并将其中的"一〔1"班分别改为相应班级名称。这个数组公式的含义是：在"登分册"工作表中查找"一〔1班对应的成绩,并返回一〔1班所有成绩之和。如图三

在"统计表"D2单元格输入公式=ROUND<C2/COUNTIF<登分册!$A$2：A$13,统计表!A2>,2>计算出一〔1班的平均分,向下拖动复制公式到相应单元格。该公式的含义是：将C2中的总分除以"登分册"A列中与A2显示的值〔一〔1>班相同的人数,并保留两位小数。

在E2中输入数组公式计算及格人数。该数组函数为=SUMPRODUCT<<登分册!$A$2：A$13=统计表!$A2>*<登分册!$D$2：D$13>=60>>,其含义是：若"登分册"工作表A列中有与"统计表"A2相同的信息,即"一〔1班",则返回与"一〔1班"相对应的成绩中大于或等于60分的人数〔如图四。将公式向下复制后,在F2单元格中输入公式=E2/COUNTIF<登分册!$A$2：A$13,统计表!$A2>计算该班的及格率。将E两列中的公式复制到G列,并将公式是的">=60"修改成学校规定的优生标准〔如是80分为优生,则将60改为80即可〔如图五。同理,将F列中的公式复制到H列即可得到优生率的统计结果。需要提醒大家的是,使用数组函数,一定不要忘记是Ctrl+Shift+回车。用VLOOKUP在教师工资表中实现错序修正问题的提出

工资改革后,县教育系统的工资信息每年都要整理一次,财政局常要求各学校按其下发的工资人员信息表的顺序来处理本校教师的工资信息。但由于各学校处理工资信息的人对计算机操作不熟练,做出来的顺序通常与财政局的工资表人员顺序不同,这导致财政局去处理这些信息时比较麻烦,要实现每个人员信息相对应,则要不停核对每一个人员信息,工作量非常大。

那我们使用计算机处理一下,是否会更快、更准确呢？答案是肯定的。我们只要掌握了WPS表格的函数VLOOKUP的应用就可以顺利解决上面的问题。

二、问题的处理

1、VLOOKUP的功能分析

作用：在表格或数值数组的首列查找指定的数值,并由此返回表格或数组当前行中指定列处的数值。即将一个数据通过与某数据表第一列比较,找出与其相对应的某列的值。

语法格式：VLOOKUP〔"查找值"、"数据表"、"列序数"、"匹配条件"

参数分析：

查找值：此参数表明需要在"数据表"中第一列需要查找的值。

数据表：此参数指的是数据在哪查找。

列序数：此参数指的是查找到符合值后,返回相对应的第几列数据。

匹配条件：设置是否为精确匹配。

错序的修正

对于有错序的修正,必须提供一份正确排序的数据信息。对于工资信息来讲,之前每个月发放工资的顺序是不变的,可以复制过来一份作为参数数据表。而由各学校未按顺序处理的表格,可参考这份正确顺序的信息表来修正。

假设我们在工作表"Sheet1"中保存了正确顺序的工资人员信息,如下图：

再假设我们在工作表"Sheet2"中保存了错序的工作人员信息,如下图：

对照两表,可以看到,只要我们能将错序的姓名加上正确的工资编号,然后再按工资编号排序一下就完成了修正工作。

在这里我们为了使用VLOOKUP函数结合姓名来查找到工资编号,我们将正确顺序的表sheet1工资编号与姓名位置互换一下。如下图：

下面我们使用VLOOKUP函数把各人的工资编号从sheet1表中找出来。

在Sheet2表B2单元格输入公式"=VLOOKUP<A2,Sheet1!A2:B12,2,FALSE>"并确定即可找到张诚的正确工资编号了。为复制公式时数据表不发生变化,我们将公式修改为

"=VLOOKUP<A2,Sheet1!$A$2:$B$12,2,FALSE>",然后再执行复制操作,即可找到各人所对应的正确工资编号了。

最后,我们将sheet2表,按工资编号进行升序排序一次,即可实现正确排序了。利用ET函数,轻松统计小学毕业考试成绩我县教研室自20XX开始实行全县小学毕业统一考试,印发的成绩登计册如图一所示。小学毕业考试一般以乡镇为单位,要求各学校的学生打乱编排考号。经过反复研究,我镇利用ET函数,解决了考场编排,登分统计的问题。一、建立全乡镇总册,表册栏目见图二,将工作表命名为"总册",并将各学校学生的信息以班级为单位逐一录入总册。

二、按图一所示建立各班级成绩登计表,将工作表按学校〔班级名称命名,以便查找,同时将总册中某一班级的学生姓名复制粘贴到成绩登计表中,并填写好表格上方的学校、班级、各科教师姓名、学生人数等信息。

三、建立总册函数。

首先在总册A2单元格中输入函数"＝rand<>"回车后拖动光标向下复制,A列的各单元格将产生一个随机数〔如图三。

选定表格,以"随机数"为关键字排序,然后在B2单元格输入0,B3单元格输入1,选中B2:B3,拖住B3单元格右下角十字光标向下复制到所需单元格〔如图四。

将F列格式设置为文本格式,在F2单元格按照文件要求输入准考证号06303610001,拖动F2右下角十字光标向下复制。在G2单元格公式"=INT<B2/30>+1"并向下复制,可以自动计算出各个考号对应的考场号〔如图五。至此,总册的函数编写完毕。

四、建立班级分表函数。

以安底小学六〔1班为例,我们首先将六〔1班的学生姓名复制粘贴到"安小1"工作表之中,在A5单元格中输入函数"=IF<$B5>0,VLOOKUP<$B5,<总册!$E$2:$K$65535>,2,FALSE>,"">、在C5中输入"=IF<$B5>0,VLOOKUP<$B5,<总册!$E$2:$K$65535>,4,FALSE>,"">"、在E5中输入"=IF<$B5>0,VLOOKUP<$B5,<总册!$E$2:$K$65535>,5,FALSE>,"">"、在G5中输入"=IF<$B5>0,VLOOKUP<$B5,<总册!$E$2:$K$65535>,6,FALSE>,"">",将这些函数分别向下复制。

同理,在K5中输入"=IF<$L5>0,VLOOKUP<$L5,<总册!$E$2:$K$65535>,2,FALSE>,"">"、M5中输入"=IF<$L5>0,VLOOKUP<$L5,<总册!$E$2:$K$65535>,4,FALSE>,"">"、O5中输入"=IF<$L5>0,VLOOKUP<$L5,<总册!$E$2:$K$65535>,5,FALSE>,"">",Q5中输入"=IF<$L5>0,VLOOKUP<$L5,<总册!$E$2:$K$65535>,6,FALSE>,"">"并向下复制。

上述函数建立后,整个班级学生的考号、各科成绩将自动显示。

若要将学生的成绩由分数转换为等第,可在D5中输入"=IF<B5>0,IF<C5>=80,"A",IF<C5>=70,"B",IF<C5>=60,"C",IF<ISNUMBER<C5>,"D",IF<ISBLANK<C5>,"">>>>>,"">"向下复制,然后将该列的公式复制到F、H、N、P、R列。

在表格上方的巩固率后面的单元格中,输入"=IF<C2>0,ROUND<S1/C2,2>,"">"可自动计算学生巩固率；在L2、O2、S2