为什么不同位置相同公式的计算结果不同？ / 开普饭

如图 47‑1所示，E4单元格与E9单元格公式均为普通公式且公式相同，为什么计算结果不同？

=SUM(B3:B7*C3:C7)

图47‑1 不同位置相同公式的计算结果不同

→ 解决方案：

了解“绝对交集”引用概念并正确使用数组公式。

→ 操作方法

方法1 单击E4单元格，按<F2>键或者单击编辑栏进入编辑模式，再按<Ctrl+Shift+Enter>组合键结束编辑，形成数组公式。用同样方法处理E9单元格，两者计算结果即可一致。

方法2 将两处公式中的SUM函数改为SUMPRODUCT函数。

→ 原理分析

什么是绝对交集引用

本例中，E4单元格使用普通公式执行运算时，对单元格区域B3:B7、C3:C7的引用是按照单个单元格进行计算。如图 47‑2所示，因为E4单元格与B3:B7、C3:C7虽然在列方向是平行的，但在行方向上与B4、C4单元格位于同一行，存在“绝对交集”，则Excel默认将B4、C4的值代入计算，相当于公式：

=SUM(B4*C4)

图47‑2 绝对交集引用

在E10单元格中，列方向与B3:B7、C3:C7平行、行方向上没有存在交集，因而返回#VALUE!错误。因此，如果需要计算多个单价与数量乘积之和，需要使用数组公式或者使用默认支持非常量数组多项运算的函数。

→ 知识扩展

数组的绝对交集

在单元格A1:C2中输入以下公式并按<Ctrl+Shift+Enter>组合键形成多单元格数组公式：

{=INDEX({'网站名称','网址','办站宗旨';'Excel技巧网','www.exceltip.net','原创为主，实用为王'},ROW(1:2),COLUMN(A:C))}

图47‑3 数组的绝对交集

如图 47‑3所示，公式中虽然INDEX函数的第2参数、第3参数都是数组，选取其中任意一个公式在编辑栏按<F9>键都返回单值结果，实际上原理与“绝对交集”引用相似，即对于数组ROW(1:2)和COLUMN(A:C)是按照单个值计算，并在对应的行、列位置返回第1参数常量数组中的元素，但未生产内存数组。

如何判别公式是否生成内存数组

要在单元格区域中显示内存数组，必须使用多单元格数组公式。但多单元格数组公式的计算结果可以是内存数组，也可是单值。一般可以使用<F9>键或ROWS、COLUMNS函数判断一个公式产生的结果是否内存数组。

例如，在C1:C3单元格输入多单元格数组公式：

{=INDEX(A:A,ROW())}

D1:D3单元格输入多单元格数组公式：

{=INDEX(A:A,{2;5;7})}

以上两个公式都返回单个计算结果，在其中任意一个单元格编辑栏按<F9>键的计算结果与多单元格数组公式的整体结果不一致，而是按照“绝对交集”的位置对应关系显示。使用ROWS、COLUMNS函数判断时，内存数组的行、列数应与多单元格数组公式的整体结果相符，例如：

=ROWS(INDEX(A:A,{2;5;7}))

返回计算结果为1，而不是3行。因此可以判定以上两个公式的计算结果不是内存数组。用同样的方法可以判定以下公式产生的结果为内存数组：

=OFFSET(A1,{1;4;6},0)

为什么不同位置相同公式的计算结果不同？

相关推荐