一个花了很长时间才想明白的公式错误

这个问题花了我很长时间才想明白

这是一个朋友的求助问题。同样的两个公式为什么一个可以得到结果，另外一个却返回错误值：

两个Offset函数，一个是从透视表的行字段（销售量）区域取出第一个值，一个是从透视表的值字段（销售额）区域取出第一个值，公式的区别就是从B改成了A，结果就是一个正确，一个错误？

这是为什么？大家可以开动脑筋，一起来想一下

看到这个问题我也很困惑！为了让大家排除一些干扰项，首先我要说，即使把公式缺少的参数填上，结果也不变：

有些朋友可能会认为是透视表造成的，事实上即使是普通的数据区域，结果依旧：

排除是公式或透视表造成的问题。

这个问题直到我自己重新写了一个公式，我才恍然大悟。

你可以在随便一个单元格，比如D7行重新写一个公式：

=OFFSET($A$4,,,COUNTA($A$4:$A$100),1)

这次，公式没有返回错误值，而是返回了一个结果。但是这个结果跟我们的期望值不一样，是364，返回的是A7的值。

原来都是由于数组惹的祸！！！

我们要注意到由于公式中Offset的第4个参数是Counta(A4:A19)=16，所以实际上这个公式是返回了一个16行1列的数组。

如果你在单元格E3中输入这个公式：

=OFFSET($A$4,,,COUNTA($A$4:$A$100),1)

然后按Ctrl+Shift+Enter键输入数组公式的话，结果就是正确的：

那么为什么我在D7中输入普通公式（按Enter输入），也得到了结果，而其结果不是我需要的，而且E4单元格的普通公式也得到了正确（期望的）结果。

这要从数组的处理方式说起。我们先选择D7:D22(16个单元格）区域，然后输入这个公式：

=OFFSET($A$4,,,COUNTA($A$4:$A$100),1)

输入后按Ctrl+Shift+Enter输入数组公式：

然后在E7:E22区域输入公式：

=OFFSET($A$4,,,COUNTA($A$4:$A$100),1)

按回车输入普通公式：

仔细一对比，我们就能发现问题了:

由于Offset返回的是一个数组，因此如果你明确指定是数组公式的情况下，Excel将数组元素自上而下的输出。所以D7:D22返回的是正确结果。

如果没有指明是数组公式的话，Excel缺乏明确只是需要返回数组中哪个元素，所以就根据结果区域和源区域的相对位置去确定需要返回哪个元素，由于D7单元格相对A4:A19中是第4个元素，因此返回的数组的第4个元素。最后3行（20，21，22）已经超出了A4:A19范围，所以返回错误值。

同样，E3单元格超出了A4:A19的范围，所以返回错误值。如果将E3复制到比如F4，结果就是期望的结果了！

就是这样。

取得本文模板文件的方式：

本文没有模板文件