excel中average函数的使用

童鞋们可能都知道多条件求平均值或求和的函数是averageifs和sumifs，因为这两个函数就是excel专门用来进行多条件计算的。

但在今天这个示例中，当使用averageifs来求平均值，却遇到了一些麻烦。

下面我们输入一个averageifs函数公式：

=AVERAGEIFS(B2:B184,A2:A184,A169,B2:B184,">"&LARGE(B2:B184,11))

得到的结果与前面一张图的结果并不一致，是错误的结果，这是因为在公式中，第2个条件”排名前10“，使用了运算符，大于LARGE(B2:B184,11)，含义是大于排名前10的分数。

但这句话有一个易错点，即它是大于所有考生中排名前10的分数，重点是“所有”。

而我们的目的是计算B班的考生前10的平均分数。

因此从某种角度来讲，“B班排名前10的平均分”，两个条件具有一定的从属性，即第2个条件是在第1个条件的基础上来进行计算，而averageifs函数中的多个条件设置是并列的，并不存在所谓的从属关系。

那么，在这样的情形下，我们要怎么来求解平均值？

解题的思路是什么？

分为两个部分。

第一个部分是多条件的设置。

第二个部分则是求平均的函数设置。

我们先写下完整的公式，结合公式来讲解两个部分的含义。

完整公式为：{=AVERAGE(LARGE(N(A2:A184=A2)*B2:B184,ROW($1:$10)))}

在这个公式中，第一部分条件设置是LARGE(N(A2:A184=A2)*B2:B184,ROW($1:$10))，第二部分是AVERAGE函数设置。

我们知道average是计算平均值的函数，但它并不是专用来计算指定条件下的平均值。

但有一点，average函数的参数，是一组数据，这个组数据可以是单元格区域也可以是一个数组。那么，如果我们将符合条件的所有数据组合成一个数组，那是不是也能达到按条件求平均的目的！？

因此，现在有了思路的第一步，即第二部分的函数设置，之所以将函数设置设为第二部分内容，是因为多条件的设置才是关键，是整个公式中的精髓所在。

对于函数的选择，其实都是比较浅易的，在数学和统计函数中，求平均值，则使用average函数，求和则使用sum函数，求最大值用max，计数用count等等。

确定了使用的函数，我们就要思考如何将多条件转换为该函数的参数值。

在作者的《excel100个常见函数快速入门》中，第1节就介绍了逻辑函数的使用。

逻辑函数的作用是判断一个表达式是否成立，成立则返回true，换算成数值则等于1，不成立会返回false，换算成数值等于0.

而条件是可以通过判断得到一个true或者false的结果，也就是大家常说的条件判断。

所以某校B班这个条件，很明显可以设置为“A2:A184=A2”，A2:A184是条件的数据列，A2是条件的参照值，也就是等于A2时为true，不等于时为false。这里之所以输入A2，是因为A2就等于“B班”，所以直接引用了单元格地址，当然我们也可以手动输入“B班”的文本。

因此它的结果是一串true和false组成的数组数据。

接下来是B班前10的分数。

这句话我们可以这样来分解，首先计算B班所有考生的分数，在从中选择前10的分数。

而计算B班所有考生的分数，我们可以写下这个表达式：(A2:A184=A2)*B2:B184

从上面的说明我们知道A2:A184=A2的结果是true和false组成的一组逻辑值，用数值来表示就是1和0组成的一组数据，那么1和0乘以另一组数据，会得到不同的结果，0乘以任意数据则等于0，而1乘以任意数值则会等于该数值。

所以实质上，这个表达式其实是条件的两步走，第1步是等于B班的条件设置，第2步是返回得到分数值的条件转换。

符合B班的考生的结果是1，乘以B2:B184单元格区域中的分数，则返回该分数值，由于符合B班的条件结果有多个true值1，因此与B列数据相乘便得到了多个分数，而组成一个数组。

得到了B班所有考生的分数，再使用large函数选取指定排名的数据。

表达式为：LARGE(N(A2:A184=A2)*B2:B184,ROW($1:$10))

我们忽略表达式中的N函数，即“LARGE((A2:A184=A2)*B2:B184,ROW($1:$10))”。

(A2:A184=A2)*B2:B184是数据区域，ROW($1:$10)是要计算第几个最大值的数据。

这里又使用了row函数来运算得到一个从1到10的数组，作为large函数的第2参数，表示计算第1大到第10大的值。

于是它的结果也会得到(A2:A184=A2)*B2:B184结果中的第1到第10的一个分数数组。

large函数具体的语法解释和用法，可以参照作者在专栏中的讲解。

那回到开头所言：

但有一点，average函数的参数，是一组数据，这个组数据可以是单元格区域也可以是一个数组。那么，如果我们将符合条件的所有数据组合成一个数组，那是不是也能达到按条件求平均的目的！？

现在我们已经得到了一个符合班级、分数和排名条件的数组，便可以直接使用average函数来求平均了。

但需要注意的一点是，当一个数组作为它的一个参数，是一个数组公式，因此需要三键结束，才能得到正确结果。

综上所述，进行多条件求平均的思路是，首先确定当前情境需要使用的函数，求平均则average函数，然后进行多条件的设置，并将其转换为一个可以进行运算的数组。同时也要考虑某个条件是否可以通过其他途径来代替，比如这里排名前10，并没有设置成一个条件，而是使用了large函数来进行取值。

多条件的设置和转换，在表达式中的表现就是一个判断条件乘以另一列数据，这是一个比较固定的表达方式。

当我们添加一个条件，B班男生排名前10的平均分。

公式为：

{=AVERAGE(LARGE((A2:A184=A2)*(B2:B184="男")*C2:C184,ROW($1:$10)))}

条件多了一个“=男生”，我们直接在公式中再乘以一个条件“B2:B184="男"”，如果还需要添加一个条件，则可以再乘以一组条件，不能忘记的是记得最后乘以返回的数值区域，使条件得到转换而获得一个可运算的数组。

举报/反馈