excel數組公式詳解? (2016-01-29 22:58:40)轉載，下面我們就來聊聊關于excel數組公式詳解?接下來我們就一起去了解一下吧!

excel數組公式詳解

(2016-01-29 22:58:40)

轉載

▼

标簽： excel公式教程 數組公式分類： Excel公式教程-原理篇

在單元格輸入普通公式，按Enter鍵或編輯欄左側的“輸入”按鈕完成輸入；而在單元格輸入數組公式需要按Ctrl Shift Enter三鍵完成輸入，所以數組公式也叫做CSE公式。

按Ctrl Shift Enter三鍵輸入數組公式後，在編輯欄顯示該公式用大括号“{}”括住。不要直接輸入這對大括号來代替按三鍵，那樣輸入的隻是文本，不是公式。

數組公式通過把普通公式中的單值構建成數組，并按數組中元素的個數進行重複計算，稱為數組運算。

如圖，根據單價和數量，直接求總金額。

在C2輸入“=SUM(A2:A3*B2:B3)”，然後按Ctrl Shift Enter三鍵，Excel就自動給公式加上“{}”，并計算出總金額340。

Excel是怎麼計算出這個結果的？

首先，分析公式中哪些部分要重複計算，或者說哪些部分由普通公式的單值變成了數組。

這裡SUM函數的參數是一個乘法式子，在普通公式中乘法運算符前後都是一個單值，而現在都是一個區域引用，所以這個乘法式子要進行重複計算，就是要重複進行多次乘法運算。

然後，分析參與運算的數組的維度和大小，并據此确定該數組運算需要重複計算的次數，以及返回的數組的維度和大小。

這個乘法式子的兩個操作數A2:A3和B2:B3都是一個單列兩行的數組，各有兩個元素。于是我們可以确定：該乘法式子将重複計算兩次，并返回一個單列兩行的數組。

具體計算步驟如下：第一次計算取第一個數組的第一個元素A2，跟第二個數組的第一個元素B2相乘，得到的100作為返回數組的第一個元素；第二次計算取第一個數組的第二個元素A3，跟第二個數組的第二個元素B3相乘，得到的240作為返回數組的第二個元素。乘法式子返回數組{100;240}。（在編輯欄裡，用鼠标拉黑公式中的“A2:A3*B2:B3”，然後按F9，可以看到該部分的計算結果。）

最後計算SUM({100;240})得到最終結果340。

如果輸入公式後沒有按Ctrl Shift Enter三鍵，而是直接按了回車，會是什麼情況呢？

在C2輸入上述公式後直接按回車，結果返回100。

在C3輸入上述公式後直接按回車，結果返回240。

如果在C4輸入上述公式後直接按回車，卻返回錯誤值#VALUE!。

可見，沒有按三鍵，那個乘法式子隻能計算一次，并且按照絕對交集隻取公式所在行的A列和B列數據相乘。按三鍵是告訴Excel需要進行數組運算。

我們再來看另一個公式，在C2輸入“=SUMPRODUCT(A2:A3*B2:B3)”後直接按回車，結果是340。為什麼這個公式不用按三鍵也可以計算出正确結果呢？

這是因為Excel能按公式所需自動轉換數值類型。SUMPRODUCT函數的參數要求是數組，Excel能自動對裡面的乘法式子進行數組運算，并返回數組。

如圖，對A列的數字值和文本型數字求和。（單元格A3的“04”是文本型數字。）

在B1輸入“=SUMPRODUCT(--TEXT(A1:A3,"0;-0;0;!0"))”後直接按回車，就能計算出正确的結果7。數組A1:A3一共3個元素，公式對“--TEXT”進行3次計算，并返回一個單列3行的數組。

在B1輸入“=SUMPRODUCT(IF(ISERR(-A1:A3),,--A1:A3))”後直接按回車，結果返回3；改為按三鍵輸入公式，才可以返回正确結果7。為什麼會這樣呢？因為SUMPRODUCT函數所需數組需要經IF函數計算後才可以确定，而IF函數不能自動進行數組運算，所以公式需要按三鍵。

三維引用是對連續多張工作表上相同單元格或單元格區域的引用。三維引用跟三維數組不同。三維引用不能分次計算，計算後隻能返回單值而不能返回數組。

Excel公式是在工作表這個平台上運行的，一種工作表就是一個二維數組。在數組公式中，使用OFFSET和INDIRECT函數可以生成所謂的三維、四維數組，但嚴格來說那不是真正意義的三維、四維，隻不過是若幹個二維數組而已，數組公式對這些二維數組進行分次計算。