if函数数组和嵌套的结合应用
今天来1个进阶应用,把if函数的数组和嵌套结合起来使用。
求出科目为语文且分数大于80分的平均分
这种类型的题目也见过很多了,实际就是多条件求平均值。我已经将满足条件的用条件格式标记出来,也就是求出标绿的这些分数的平均分。B列的科目是用公式=INDEX({"语文","数学","英语"},RANDBETWEEN(1,3))随机生成的,C列的分数也是用随机函数=RANDBETWEEN(50,100)生成的,按F9是会变化的。
![](http://pic.ikafan.com/imgp/L3Byb3h5L2h0dHBzL2ltYWdlMTA5LjM2MGRvYy5jbi9Eb3dubG9hZEltZy8yMDIxLzA0LzAyMTEvMjE5MTc4MDYxXzFfMjAyMTA0MDIxMTQ3MTUyODQ=.jpg)
可以用多条件求平均函数averageifs来完成,在E2单元格中输入公式=AVERAGEIFS(C2:C11,B2:B11,"语文",C2:C11,">80")。这种方法比较简单,这个函数以后也会说的,但今天我们主要说怎么用if函数来完成。我之所以说这个方法,是想向你传达一种思想,在excel中,解决一个问题的方法有好多种,条条大路通罗马。
用if函数来完成的话,在E3单元格中输入公式=AVERAGE(IF(B2:B11="语文",IF(C2:C11>80,C2:C11,""),"")),按ctrl+shift+enter三键。可以看到,既用到了数组,又用到了if的嵌套。看起来比较复杂,该怎么理解呢?
简单来说,首先判断科目这1列中的数据是否是语文,如果是语文,接着再判断对应的分数这1列是否大于80,如果大于80,就返回它的分数,如果不大于80,就返回空,这样就把科目是语文的判断完了。如果科目这1列中的数据不是语文,那么返回空。和单独判断一个数据是一样的逻辑,只不过现在是一组数据。
如果你还是不明白,可以按数组的方式来理解。if的第1参数会形成一个数组,第2参数是个if的嵌套,最后还是一个数组,第3参数空,可以将看作一个数组。如下图所示。
![](http://pic.ikafan.com/imgp/L3Byb3h5L2h0dHBzL2ltYWdlMTA5LjM2MGRvYy5jbi9Eb3dubG9hZEltZy8yMDIxLzA0LzAyMTEvMjE5MTc4MDYxXzJfMjAyMTA0MDIxMTQ3MTU2NjA=.jpg)
第1参数形成true和false的数组,第2参数大于80分的,返回分数,否则返回空,还是个一维数组。第3参数空,也可以看作一个数组。这样就一一对应了。你可以慢慢研究它的对应关系。最后用average对结果列就平均就可以了。
如果你还是不明白,只能用下面的方法了。在D列添加辅助列,在D2单元格中输入公式=IF(B2="语文",IF(C2>80,C2,""),""),向下填充。这样就是对1个单元格的if嵌套,可以看到辅助列的结果和J列的结果是一样的。最后把公式中的B2改为B2:B11,C2改为C2:C11就可以了。
![](http://n4.ikafan.com/assetsj/blank.gif)
之前我们学过if函数的简写,还可以对公式简写。在E4单元格中输入公式=AVERAGE(IF(B2:B11="语文",IF(C2:C11>80,C2:C11)))。也就是if函数第3参数的简写形式。这样if函数的结果如F列所示,就是把空变成了false。average会忽略逻辑值false。
![](http://n4.ikafan.com/assetsj/blank.gif)
文件链接:
https://pan.baidu.com/s/1HiNou_3OZgeQwYLnOs_EPA
提取码:eu8b
好了,今天的内容就说到这里。如果你还想了解其他函数的用法,请投票。