Excel函数应用之信息函数等 联系客服

发布时间 : 星期三 文章Excel函数应用之信息函数等更新完毕开始阅读

在成绩表中根据不同的成绩区分合格与不合格。现在我们就以某班级的英语成绩为例具体说明用法。

图6

某班级的成绩如图6所示,为了做出最终的综合评定,我们设定按照平均分判断该学生成绩是否合格的规则。如果各科平均分超过60分则认为是合格的,否则记作不合格。

根据这一规则,我们在综合评定中写公式(以单元格B12为例):

=IF(B11>60,\合格\不合格\

语法解释为,如果单元格B11的值大于60,则执行第二个参数即在单元格B12中显示合格字样,否则执行第三个参数即在单元格B12中显示不合格字样。

在综合评定栏中可以看到由于C列的同学各科平均分为54分,综合评定为不合格。其余均为合格。

3、 多层嵌套函数的应用

在上述的例子中,我们只是将成绩简单区分为合格与不合格,在实际应用中,成绩通常是有多个等级的,比如优、良、中、及格、不及格等。有办法一次性区分吗?可以使用多层嵌套的办法来实现。仍以上例为例,我们设定综合评定的规则为当各科平均分超过90时,评定为优秀。如图7所示。

图7

说明:为了解释起来比较方便,我们在这里仅做两重嵌套的示例,您可以按照实际情况进行更多重的嵌套,但请注意Excel的IF函数最多允许七重嵌套。

根据这一规则,我们在综合评定中写公式(以单元格F12为例):

=IF(F11>60,IF(AND(F11>90),\优秀\合格\不合格\

语法解释为,如果单元格F11的值大于60,则执行第二个参数,在这里为嵌套函数,继续判断单元格F11的值是否大于90(为了让大家体会一下AND函数的应用,写成AND(F11>90),实际上可以仅写F11>90),如果满足在单元格F12中显示优秀字样,不满足显示合格字样,如果F11的值以上条件都不满足,则执行第三个参数即在单元格F12中显示不合格字样。

在综合评定栏中可以看到由于F列的同学各科平均分为92分,综合评定为优秀。

(三)根据条件计算值

在了解了IF函数的使用方法后,我们再来看看与之类似的Excel提供的可根据某一条件来分析数据的其他函数。例如,如果要计算单元格区域中某个文本串或数字出现的次数,则可使用 COUNTIF 工作表函数。如果要根据单元格区域中的某一文本串或数字求和,则可使用 SUMIF 工作表函数。关于SUMIF函数在数学与三角函数中以做了较为详细的介绍。这里重点介绍COUNTIF的应用。

COUNTIF可以用来计算给定区域内满足特定条件的单元格的数目。比如在成绩表中计算每位学生取得优秀成绩的课程数。在工资表中求出所有基本工资在2000元以上的员工数。

语法形式为COUNTIF(range,criteria)。其中Range为需要计算其中满足条件的单元格数目的单元格区域。Criteria确定哪些单元格将被计算在内的条件,其形式可以为数字、表达式或文本。例如,条件可以表示为 32、\、\、\。

1、成绩表

这里仍以上述成绩表的例子说明一些应用方法。我们需要计算的是:每位学生取得优秀成绩的课程数。规则为成绩大于90分记做优秀。如图8所示

图8

根据这一规则,我们在优秀门数中写公式(以单元格B13为例):

=COUNTIF(B4:B10,\

语法解释为,计算B4到B10这个范围,即jarry的各科成绩中有多少个数值大于90的单元格。

在优秀门数栏中可以看到jarry的优秀门数为两门。其他人也可以依次看到。

2、 销售业绩表

销售业绩表可能是综合运用IF、SUMIF、COUNTIF非常典型的示例。比如,可能希望计算销售人员的订单数,然后汇总每个销售人员的销售额,并且根据总发货量决定每次销售应获得的奖金。

原始数据表如图9所示(原始数据是以流水单形式列出的,即按订单号排列)

图9 原始数据表

按销售人员汇总表如图10所示

图10 销售人员汇总表

如图10所示的表完全是利用函数计算的方法自动汇总的数据。首先建立一个按照销售人员汇总的表单样式,如图所示。然后分别计算订单数、订单总额、销售奖金。

(1) 订单数 --用COUNTIF计算销售人员的订单数。

以销售人员ANNIE的订单数公式为例。公式:

=COUNTIF($C$2:$C$13,A17)

语法解释为计算单元格A17(即销售人员ANNIE)在\销售人员\清单$C$2:$C$13的范围内(即