如何判斷多項運算是否需要使用數組公式?

??????18萬粉絲共同關注

每日Excel/Word/PPT技術分享

請點左下角的「閱讀原文」

如何判斷多項運算是否需要使用數組公式

在疑難42和疑難44例子中,均存在B3:B7*C3:C7的多項運算過程,但是使用SUM函數的公式需要按組合鍵形成數組公式才可以得到正確結果,而使用SUMPRODUCT函數則可以用普通公式實現計算。該如何判別哪些多項運算需要用數組公式完成?

→ 解決方案:

了解組合鍵對於數組公式的意義。

→ 操作方法

圖解數組公式與普通公式運算差異

步驟1 在C10單元格輸入以下公式並按鍵結束編輯,得到普通公式:

=SUM(B3:B7*C3:C7)

步驟2 選擇C10單元格,單擊「公式」選項卡,單擊「公式審核」組的「公式求值」按鈕,如圖 45?1所示,在計算過程中,B3:B7、C3:C7分別返回#VALUE!錯誤值代入計算,導致結果出錯。

圖45?1 未按數組公式結果出錯

步驟3 單擊C10單元格,在編輯欄按組合鍵結束公式編輯形成數組公式後,再次進行「公式求值」,結果如圖 45?2所示:

圖45?2 使用數組公式得到正確結果

→ 原理分析

組合鍵對於數組公式的意義

通過本例中同一個公式使用不同方式的計算過程和結果對比,表明按組合鍵相當於給Excel下達執行多項運算的命令。

如圖 45?2與圖 43?2所示,使用SUM函數數組公式計算過程與使用SUMPRODUCT函數普通公式的計算過程相似,因為SUMPRODUCT函數自身支持非常量數組的多項運算,所以按鍵結束編輯的普通公式也可以實現相同的計算結果。但並非使用SUMPRODUCT函數就可以避免所有的數組公式。例如公式:

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

同理,可以通過「公式求值」操作對該公式在數組公式和普通公式形式時的計算過程進行對比。因為運算順序是內層嵌套優先,而IF函數本身不支持非常量數組多項運算,所以該公式需要按組合鍵結束公式編輯,給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})

總而言之,按組合鍵的意義在於下達多項運算指令,具有以下兩種情況之一時必須使用數組公式方可得到正確結果:

1.公式的計算過程中存在多項運算,且函數自身不支持非常量數組的多項運算,

2.公式計算結果為數組,需要使用多單元格存儲計算產生的多個結果。

版權所有 轉載須經Excel技巧網許可

Office交流 QQ 群

進入 Excel技巧網 公眾號

回復 QQ 查詢 Office交流群號,與高手們一同學習

推薦閱讀:

大六壬判斷入手5
深度揭秘八字十神所代表的內涵,如何判斷一個人的性格特徵?
如何判斷一個人愛不愛你
假從格該如何判斷呢?易天行老師帶你走進八字命理的高層占斷!
一招判斷長短擇,節約你最寶貴的青春。

TAG:公式 | 判斷 | 數組 | 運算 | 是否 |