条件求平均函数averageif的进阶用法

同学们，大家好。今天终于告别复杂的frequency函数，回归基础的averageif函数。averageif是条件求平均函数，它的用法和属性基本上和sumif是相通的。大家掌握好sumif的用法，再来套用在averageif上就比较好理解。下面来看两个实例吧。

1.求最后3个月的平均值

如下图所示，是一个成绩表。求每人最后3个月的平均成绩。最后3个月的成绩我用红色标记出来了。比如薛梅玲最后3个月的成绩分别是96,70,59，那么她最后3个月的平均成绩就是75。这个问题该怎么解决呢？如果让用offset，match，lookup等函数，还是挺好解决的。但只让用averageif来完成，还是有点懵的，感觉挺困难的。

其实这个就要看思路了，这里用的是错位的思路。还是先说公式吧，在O2单元格输入公式=AVERAGEIF(E2:N2,"",B2:K2)，向下填充，就可以了。大家有没有发现averageif的第1参数是E2:N2，这是什么意思呀？

其实，我在第1张图里隐藏了3列，即L、M、N列，不知道大家发现了没有了。实际它是下图这样，相当于借用了3个辅助的空列。目的就是利用错位来解决这个问题。

那到底是怎么利用错位的呢？下面我们以“薛梅玲”为例说明，公式为=AVERAGEIF(E2:N2,"",B2:K2)。由于第1参数和第3参数是错位的关系，在公式中不好说明，所以我们要单独把它们拿出来，并且对齐，这样就好看了。如下图所示。

上面的一行是第1参数E2:N2，下面的一行是第3参数B2:K2。而条件是空，这样的话，只要第1参数中是空单元格的，那么就对第3参数相应的区域求平均值。第3参数又会忽略文本和空单元格，实际就是对最后3个数字96，70，59求平均值。其他人的最后3个月的成绩，大家可以按照这个方式来理解。

2.统计数字去重后的个数

如下图所示，是个成绩表，其中成绩中会有重复的，现在要算的是删除重复项后的个数，也就是统计成绩去重后的个数。去重统计个数的方法有挺多的，经典的有countif，match=row，还有offset的多维引用，其他的方法有之前说过的frequency。但是今天用averageif来完成一下。在D2单元格输入公式=COUNT(AVERAGEIF(B2:B10,ROW(80:100)))，按ctrl+shift+enter三键回车。

先来看AVERAGEIF(B2:B10,ROW(80:100))这部分，averageif的第2参数条件是一个数组ROW(80:100)，那么averageif返回的结果也是一个数组。意思就是在B2:B10这个区域中，求等于80的平均分，等于81的平均分，等于82的平均分，......一直到等于100的平均分，返回的结果为{#DIV/0!;81;#DIV/0!;#DIV/0!;84;#DIV/0!;86;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;92;#DIV/0!;94;#DIV/0!;96;#DIV/0!;#DIV/0!;99;#DIV/0!}。

从averageif返回的结果可以看到，80在B2:B10这个区域中没有出现，那么它返回错误值，81在B2:B10这个区域中有出现，那它还是81，由于是求平均，不管是出现一个81，还是2个81，求平均值还是81。其他的也是这样算的，所以在B2:B10这个区域中有出现的，还是返回它自己，没有出现过的返回错误值。这样只需统计数字的个数，就是去重后的个数。

这里第2参数为什么是ROW(80:100)呢？因为B2:B10这个区域的数字范围是在80-100之间，也就是第2参数的范围必须要包含第1参数的范围，且第2参数中没有重复的。从这个角度来说，我又想到了另外的一个公式=COUNT(MATCH(ROW(80:100),B2:B10,))，同样按ctrl+shift+enter三键回车。

链接：

https://pan.baidu.com/s/1KFbfUY5WxSq4kHGzq3zO5Q

提取码：6j9b