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

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

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

  一、SUMIF函数

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

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

  图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所示。

  图3

  公式的含义是在P2:R11单元格区域的首列查找值为“教师04”的单元格,并返回其所在行的第三列数据。对照表格看一下就清楚了。

  在《成绩分析》一文中的VLOOKUP函数中,使用了另一函数COLUMN(),它返回的是单元格所在的列数。比如公式“=COLUMN(D3)”的结果就是“4”。而不带任何参数的“COLUMN()”返回的则是当前单元格所在的列数。

  好了,《成绩分析》一文中涉及的函数基本交待完了。如何有机会实践一下,必定能体会到使用这些函数的乐趣。

    无相关信息