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

摘要：前些天在论坛发了一帖：《用ET表格打造更直观的学生成绩分析》(以下简称为《成绩分析》)，/thread-21963426-1-1.html。...

前些天在论坛发了一帖：《用ET表格打造更直观的学生成绩分析》(以下简称为《成绩分析》)，/thread-21963426-1-1.html。很多坛友对文中所涉及的函数非常感兴趣。今天再发一帖对相关的函数作些解释以作前文的补充。

前文中所涉及的函数主要有这么几个：SUMIF、COUNTIF、SUMPRODUCT、VLOOKUP。这几个函数在成绩分析统计中经常用得到，对于教师来说可谓是有用之极。我们且一一道来。

一、SUMIF函数

SUMIF函数的作用是根据指定条件对若干单元格、区域或引用求和。其语法为SUMIF(用于条件判断的单元格区域，由数字、逻辑表达式等组成的判定条件，为需要求和的单元格、区域或者是引用)。以图1所示表格为例。

图1

我们希望在D13单元格中显示表格中2班学生的语文成绩总分。分析可以看到学生的班级在B2:B11单元格区域，语文成绩则分布在D2:D11单元格区域。所以，根据SUMIF函数的语法，我们只需要在D13单元格输入公式=SUMIF($B$2:$B$11,2班,D2:D11)就可以了。其中参数2班为判断条件，$B$2:$B$11为提供逻辑判断依据的单元格区域，而D2:D11则为实际求和的单元格区域。所以，公式 =SUMIF($B$2:$B$11,2班,D2:D11)可以翻译为：在B2:B11单元格中值为2班的，对其对应的D列单元格数据进行求和。

在《成绩分析》一文中，公式=SUMIF($B:$B,$Q$3,D:D)就很容易理解了：在B列中其值与Q3单元格相等的，对其对应的D列单元格进行求和。

二、COUNTIF函数

COUNIT函数的作用是计算区域中满足给定条件的单元格的个数。语法与SUMIF函数类似：COUNTIF(为需要计算其中满足条件的单元格数目的单元格区域，统计条件)。其中统计条件可以为数字、表格式或文本。简单地理解就是COUNTIF(在哪里计数，根据什么计数)。

仍以图1所示表格为例。我们如果输入公式=COUNTIF($B$2:$B$11,2班)，那么自然就可以得到B2:B11单元格区域中值为2班的单元格数目。所以，D14单元格要统计2班语文平均分就简单多了，只需要输入公式=D13/COUNTIF($B$2:$B$11,2 班)就OK了。

三、SUMPRODUCT函数

该函数可用于多条件计数，即计算符合2个及以上条件的单元格个数。其语法为SUMPRODUCT((条件1)*(条件2)* (条件))。如图2所示表格。

图2

我们如果要统计表格中职称为中高的男教师数，那么只需要在单元格中输入公式=SUMPRODUCT((Q2:Q11=男)*(R2:R11=中高))即可。相信对照表格和公式，公式的含义自然就清楚了。

图1所示表格中班级在B列，语文成绩在D列。假如要计算2班语文科目的及格率，那么就需要先统计符合两个条件的单元格数目。条件1：B列为2班，条件2：D列大于或等于60分。公式=SUMPRODUCT(($B$2:$B$11=2班)*(D2:D11=60))就可以满足要求，然后再除以人数(COUNTIF($B$2:$B$11,2班))不就是及格率了?

成绩分析统计中的优秀率也是这样统计，只是把分数从60换成设定的成绩就行了。

四、VLOOKUP函数

VLOOKUP函数的作用是在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。其语法是 VLOOKUP(查找值，数据表，列序数，匹配条件)。所谓查找值是指需要在数据表第一列中查找的数值，它可以是数值、引用或文字串。数据表为需要在其中查找数据的数据表，可以使用对区域或区域名称的引用。列序数是在数据表中待返回匹配数据所在的列序号。匹配条件为FALSE是返回精确匹配值，如果为TRUE或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于查找值的最大数值。

还是举例来说更清楚些。假设我们想知道在图2所示表格中教师04的职称是什么。那么我们就可以在单元格中输入公式=VLOOKUP(教师04,P2:R11,3,FALSE)，回车就出结果了，如图3所示。