具有或功能的Excel平均值 [英] Excel averageifs with or function

问题描述

我正在使用averageifs函数，并且在任一条件为真的情况下，我有一列需要计算平均值.

I am using the averageifs function, and have one column where I need to calculate the average if either of the criteria are true.

我尝试使用OR函数，并尝试了大括号，但都给了我错误.

I have tried using the OR function, and tried the curly brackets, but both give me errors.

= AVERAGEIFS(N1:N612，I1:I612，{"FL"，"IF"})

=AVERAGEIFS(N1:N612,I1:I612,{"FL","IF"})

还有更多范围，但这些范围的编码很好.

There are more ranges, but the coding for those is fine.

要清楚，如果"I"列包含(具体来说)字母FL， OR 字母IF，我想返回一个平均值.其他任何字母应表示该条目未取平均值.

To be clear, I want to return an average if column "I" contains (specifically) the letters FL, OR the letters IF. Any other letters should mean that entry is not averaged.

TIA！

推荐答案

AVERAGEIFS附加条件是布尔AND运算符，它无法执行OR运算.但是，由于您的列不变，因此我们可以自己实现以下目的:

AVERAGEIFS extra criteria is a boolean AND operator, it can't perform OR operations. But since your columns do not change we can somewhat implement this ourselves:

这是使用数组公式的简单方法(输入 ctrl + shift + enter ):

Here is a simple way using an array formula (entered with ctrl + shift + enter):

=AVERAGE(IF((I1:I612="IF")+(I1:I612="FL"),N1:N612))

或者，如果您不喜欢使用数组公式，则基本上可以使用SUMPRODUCT和COUNTIF手动完成此操作:

Or if you don't like using array formulas you basically do this manually using SUMPRODUCT and COUNTIF:

=SUMPRODUCT((I1:I612="IF")*(N1:N612)+(I1:I612="FL")*(N1:N612))/(COUNTIF(I1:I612,"IF")+COUNTIF(I1:I612,"FL"))

这篇关于具有或功能的Excel平均值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！