arrays - Excel 不正确的数组公式使用说明

Question

这是我的数据

Region    Value
East       9800
East       5592
East       6966
West       5418

当我做

=AVERAGE(IF(A2:A5="East",B2:B5)) CTRL+SHIFT+ENTER

我明白了

7452.667

这是正确的答案。但是当我这样做时

    =AVERAGE(IF(A2:A5="East",B2:B5))  ENTER

我没有收到错误。我明白了

6944

如果我只是输入

 =IF(A2:A5="East",B2:B5)   ENTER

我明白了

 9800

所以 6944 不是 9800 的平均值。 Excel 在这里做什么？

当我做

=IF(A2:A5="West",B2:B5)  ENTER

我明白了

 FALSE

所以看起来 IF 正在读取第一行.. 但随后在 Average(If()) 上发生了一些奇怪的事情

我有一个/两个问题和一个祷告:

问题: excel 做什么才能达到 6944？为什么当我执行 ENTER 而不是 CTRL+SHIFT+ENTER 时没有出现错误，有没有办法提醒 excel 告诉我在处理数组时做正确的事情？

祈祷:我只能希望在过去的几周里我没有产生很多垃圾，因为我忽略了 CTRL-SHIFT-ENTER 并错误地执行了 ENTER 并且没有出错并用我得到的东西运行。

Answer 1

6944是所有值的平均值。你会得到它使用=SUM(B2:B5)/4也是。

当您不使用 CSE 时，IF将评估如下:

=IF(A2="East",B2:B5)

当您在 excel 中的非数组公式中有一个数组时，您将只获得该数组的一项(或元素)。例如，如果你把上面的公式(没有 AVERAGE )， IF计算与插入公式的行相对应的单元格。如果将其放在 excel 的第二行(在任何单元格 C2、D2、E2 等中)，实际的“结果”公式将如下所示:

=IF(A2="East", B2)

如果你把它放在单元格 C3 中会发生什么:

=IF(A3="East", B3)

如果你把它放在 C6... A2:A5将返回 #VALUE! ，因为 A2:A5 中对应的 6 行不存在!。当然，在 C5 中，你会得到 =IF(A5="East", B5)这将返回 FALSE与您的样本值。

根据初始公式，由于 A2确实是 East , 你会得到 AVERAGE(B2:B5) 的结果.

与其他功能相同:

=IF(A2="West",B2:B5)

返回 FALSE , 和 FALSE 的平均值是 0 .

您可以使用函数 AVERAGEIF不需要输入数组即可工作，但前提是您拥有 Excel 2007 或更高版本:

=AVERAGEIF(A2:A5,"East",B2:B5)

如果您不想使用数组函数并且卡在 2007 年之前的 excel 版本中，例如 Excel 2003，您可以尝试:

=SUMIF(A2:A5,"East",B2:B5)/COUNTIF(A2:A5,"East")