疑难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.公式计算结果为数组,需要使用多单元格存储计算产生的多个结果。
|
|
来自: 新华书店好书榜 > 《「OFFICE」》