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

sou6 2016-08-05 08:22:17 来源: 互联网

　　前些天在论坛发了一帖：《用ET表格打造更直观的学生成绩分析》(以下简称为《成绩分析》)，http://bbs.wps.cn/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所示。