为什么C5公式普通输入和数组输入返回值不同？

lrlxxqxa

工作表中自定义名称：数量=OFFSET(Sheet1!$A$2,,,COUNTA(Sheet1!$A:$A)-1)

详见附件，同样的公式，在C5普通输入和数组三键输入后，返回结果不同：



原因是当直接输入时，excel按照隐含交叉（公式所在单元格与引用单元格之间的位置的交叉单元格的值）方式计算；而三键输入时，excel按照数组运算方式计算。

同理可以验证右边区域的那些公式。

注意：红色区域是普通公式，按照隐含交叉引用方式计算；蓝色区域是数组公式，按照数组运算方式计算。

[ 本帖最后由 lrlxxqxa 于 2010-6-27 15:38 编辑 ]

lrlxxqxa

结合此例说一下隐含交叉和数组运算这两种不同的运算方式：

数量=OFFSET(Sheet1!$A$2,,,COUNTA(Sheet1!$A:$A)-1)，这里是利用offset引用了一个动态区域，即A列的非空单元格（除了A1），返回到单元格中也就是一个内存数组：{2;3;2;4;8;1}

当我们在C5输入=数量时，公式所在单元格C5与“数量”引用的动态区域A2：A7，交叉在A5单元格，所以返回A5单元格的值=4。
如果还理解不了，可以想象C5是一个点（红色），A2：A7是一条线（黄色），红色和黄色垂直交叉的交集所在单元格的值，就是公式返回的结果。

当我们在C5三键输入={数量}时，excel就不会按照隐含交叉引用方式运算了。因为C5是一个单独的单元格，excel会把“数量”引用的动态区域的第一个值返回，即{2;3;2;4;8;1}里面的第一个元素=2。

另外，当普通公式输入区域与黄色区域没有交集的时候，结果出错#VALUE!；如果以数组公式输入则不会出错，因为excel根本就不会和黄色区域去交叉比对。

[ 本帖最后由 lrlxxqxa 于 2010-6-27 16:09 编辑 ]

lrlxxqxa

多说几句，数组运算并非是三键结束的公式才能告知excel执行，有的函数内部的运算执行的就是数组运算，如sumproduct，这时就没有必要使用三键结束输入。

只有当sumproduct函数嵌套其它函数使用（比如if）不能满足要求的运算方式时，才需要三键结束。
比如下面这样一个公式
    =SUMPRODUCT((MATCH(IF(F6:F16="",,$B6:$B16),IF(F6:F16="",,$B6:$B16),0)=ROW(1:11))*(F6:F16<>""))

这个公式必须用数组公式方式输入是因为IF的条件引用了多个单元格，如果不用数组公式方式输入excel就自动以“隐含的交叉引用”运算，而事实上公式所在格的行是不与这个区域相交的（因为区域是多行列的，在同一工作表中根本不可能交叉），那样IF的条件就是错误，IF返回的结果也是错误，最后公式的结果也是错误！如果以数组公式的方式输入，excel必定不用隐含交叉的，那样就是一个正常的数组运算。

[ 本帖最后由 lrlxxqxa 于 2010-6-27 15:52 编辑 ]

lrlxxqxa

个人理解：也不是说所有的数组公式（三键结束）都必须执行按照数组的运算方式执行运算。比如把一个常量数组{1;2;3;4;5}三键输入到单元格中，执行的就不是标准的数组内部运算，而是简单的把多个值和多个单元格一一对应。因为对于这样的公式，根本用不着以数组的模式来运算。

[ 本帖最后由 lrlxxqxa 于 2010-6-27 15:57 编辑 ]

su11wei18

领悟领悟，终于知道师兄为什么进步快，这样学想不进步都难，

xiejiamei516

数组还不会

lrlxxqxa

不会数组不影响理解隐含交叉引用的，结合实例就没那么难了。

simple666

我正纳闷为什么会#Value呢，答疑解惑帖原来在这里。谢谢lr

lhx120824

楼主领悟得够深了。

bluexuemei

隐含交叉一直搞不明白啊，学习了！
http://club.excelhome.net/viewth ... able&tid=559535
http://club.excelhome.net/viewth ... able&tid=161588
http://club.excelhome.net/viewth ... able&tid=168069

[ 本帖最后由 bluexuemei 于 2011-7-5 11:55 编辑 ]