【 Excel 】如何判断多项运算是否需要使用数组公式

疑难45 如何判断多项运算是否需要使用数组公式

在疑难42和疑难44例子中，均存在B3:B7*C3:C7的多项运算过程，但是使用SUM函数的公式需要按<Ctrl+Shift+Enter>组合键形成数组公式才可以得到正确结果，而使用SUMPRODUCT函数则可以用普通公式实现计算。该如何判别哪些多项运算需要用数组公式完成？

→ 解决方案：

了解<Ctrl+Shift+Enter>组合键对于数组公式的意义。

→ 操作方法

图解数组公式与普通公式运算差异

步骤1 在C10单元格输入以下公式并按<Enter>键结束编辑，得到普通公式：

=SUM(B3:B7*C3:C7)

步骤2 选择C10单元格，单击“公式”选项卡，单击“公式审核”组的“公式求值”按钮，如图 45?1所示，在计算过程中，B3:B7、C3:C7分别返回#VALUE!错误值代入计算，导致结果出错。

图45?1 未按数组公式结果出错

步骤3 单击C10单元格，在编辑栏按<Ctrl+Shift+Enter>组合键结束公式编辑形成数组公式后，再次进行“公式求值”，结果如图 45?2所示：

图45?2 使用数组公式得到正确结果

→ 原理分析

<Ctrl+Shift+Enter>组合键对于数组公式的意义

通过本例中同一个公式使用不同方式的计算过程和结果对比，表明按<Ctrl+Shift+Enter>组合键相当于给Excel下达执行多项运算的命令。

如图 45?2与图 43?2所示，使用SUM函数数组公式计算过程与使用SUMPRODUCT函数普通公式的计算过程相似，因为SUMPRODUCT函数自身支持非常量数组的多项运算，所以按<Enter>键结束编辑的普通公式也可以实现相同的计算结果。但并非使用SUMPRODUCT函数就可以避免所有的数组公式。例如公式：

=SUMPRODUCT(IF(B3:B7>0,B3:B7)*C3:C7)

同理，可以通过“公式求值”操作对该公式在数组公式和普通公式形式时的计算过程进行对比。因为运算顺序是内层嵌套优先，而IF函数本身不支持非常量数组多项运算，所以该公式需要按<Ctrl+Shift+Enter>组合键结束公式编辑，给Excel下达执行多项运算指令，方可得到正确结果。反之，例如公式：

=IF(SUMPRODUCT(B3:B7*C3:C7)>1500,"超支","在预算内")

因为内层嵌套优先计算SUMPRODUCT部分，得到合计金额是单个结果，在外层的IF函数运算时就不存在多项运算，因而可以使用普通公式。

此外，在使用常量数组进行多项运算时，也可使用普通公式形式进行编辑，例如公式：

=SUM({3.5;1.2;18.9;3.2;12.3}*{35;48;45;32;35})

总而言之，按<Ctrl+Shift+Enter>组合键的意义在于下达多项运算指令，具有以下两种情况之一时必须使用数组公式方可得到正确结果：

1．公式的计算过程中存在多项运算，且函数自身不支持非常量数组的多项运算，

2．公式计算结果为数组，需要使用多单元格存储计算产生的多个结果。