【 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．公式計算結果為數組，需要使用多單元格存儲計算產生的多個結果。