我和數組公式有個約會之相識

06-10

一、數組的概念

什麼是數組，數組就是具有某種聯繫的多個元素的組合，這些元素可以是文本、數值、邏輯值、日期、錯語值等。在Excel中，單行或單列的數組是一維數組，多行多列的數組是二維數組。

如下面圖一，隨機分布的數字單元格，則為無序數，不屬於數組。

圖一：無序數圖二：數組

數組的書寫方式：同行的元素間用逗號「,」分隔，不同的行用分號「;」分隔。

水平數組：｛1,2,3,4,5,6,7,8,9……,N｝。

垂直數組：｛1;2;3;4;5;6;7;8;9……;N｝。

二維數組：｛1,2;3,4;5,6;7,8;9……;N｝。

在實際使用中，由於較長的數組書寫不方便，故可以使用公式產生。可由COLUMN（A:D）函數產生如{1,2,3,4}，而ROW(1:4)可產生｛1;2;3;4｝。

另外，因水平數組中COLUMN()的參數為英文字母列號，運算時不好計數列號，通常情況使用TRANSPOSE使其轉換成ROW()的數字參數，如TRANSPOSE(ROW(1:4)) 結果為｛1;2;3;4｝，更加方便用戶計數運算。

對於二維數組，遵循先行後列的規則，而INDEX()函數、OFFSET()函數都是先行後列的排列，可以結合其他函數（如MATCH）產生而未數組。

二、數組運算

單值運算A1+1 àF(X)

數值運算A1+｛1;2;3;｝àF{X1;X2;X3}

當數值運算中的參數變為數組時，即為數組運算。但並不是參數為數組形式時就是數組運算，如SUM()函數，參數本來就可以引用數組的方式，但這個不是數組運算。

三、數組公式

數組公式就是包含數組運算的公式。

輸入數組公式時，用Ctrl+Shift+Enter結束公式的輸入。這是最關鍵的，這相當於用戶告訴Excel：「這個公式是數組，按照數組的方式來」。於是，Excel不能用常規的邏輯計算。當你按下三鍵後，Excel會自動給公式加上「{}」以和普通公式區別開來，不用用戶輸入「{}」，但如是是想在公式里直接表示一個數組，就需要輸入「{}」來把數組的元素括起來。

如=SUM（2+ROW（1：3）），當錄入結束後按ENTER，那麼結果為3；而按CTRL+SHIFT+ENTER結束，返回的結果為12。

計算規則：

3.1兩個同行同列的數組計算是對應元素間進行運算，並返回同樣大小的數組。

3.2一個數組與一個單一的數據進行運算，是將數組的每一元素均與那個單一數據進行計算，並返回同樣大小的數組。

3.3單列數組與單行數組的計算：A、計算結果返回一個多行列的數組；B、返回數組的行數同單列數組的行數相同、列數同單行數組的列數相同。C、返回數組中第R行第C列的元素是單列數組的第R個元素和單行數組的第C個元素運算的結果。

3.4行數（或列數）相同的單列（或單行）數組與多行多列數組的計算：

A、計算結果返回一個多行列的數組；B、返回數組的行、列數與多行多列數組的行列數相同；C、單列數組與多行多列數組計算時，返回的數組的第R行第C列的數據等於單列數組的第R行的數據與多行多列數組的第R行第C列的數據的計算結果；D、單行數組與多行多列數組計算時，返回的數組的第R行第C列的數據等於單行數組的第C列的數據與多行多列數組的第R行第C列的數據的計算結果。

3.5行、列數不相等的數組計算：

A、公式返回一個多行多列數組；B、返回數組的行數與參與計算的兩個數組中行數較大的數組的行數相同，列數與較大的列數的數組相同；C、返回數組的大於較小行數數組行數、大於較大列數數組列數的區域的元素均為#N/A。有效元素為兩個數組中對應數組的計算結果。

PS：對於行列數不匹配的數組，在計算時Excel會將數組對象進行擴展，以符合計算需要的維數。每一個參與計算的數組的行數必須與行數最大的數組的行數相同，列數必須與列數最大的數組的列數相同。

數組公式分類：

單單元格公式：數組公式應用單個單元格，一般只返回一個運算結果。

多單元格公式：數組公式應用多個單元格，一般返回多個運算結果，對應相應的單元格內。

輸入多單元格數組公式時，應先選中需要返回數據的單元格區域，選中的單元格區域的行、列數應與返回數組的行、列數相同。否則，如果選中的區域小於數組返回的行列數，則返回結果不完整。如果選擇的區域大於數組返回的行列數，那超出的區域將會返回#N/A值。

案例：

單單元格公式：

多單元格公式：

四、參數驅動

函數的參數或運算符的操作數從單值升級為數組，那麼就得到相應的數組返回值形式。要得到什麼樣的結果，就用參數去考慮。

函數運算中單值參數升級變為數組後，函數的返回值就由參數的改變而得到數組的結果，運算的結果的形式由參數決定。利用參數決定返回結果的指導思想，可以有目的地數組化參數，從而得到一維數組或二維數組。

五、貼近實戰

Row()函數的標記作用

對於excel數組來說，行列確定也就確定了位置。通過row()進行標記，可以得到需要的信息，從而能夠對數組進行處理變換。

如課程中的案例，通過($B$6:$B$11=$E$7)*ROW($1:$6)，按F9查看，可得到{FALSE;FALSE;TRUE;FALSE;TRUE;FALSE}*{1;2;3;4;5;6}，此部分的可得到數組{0;0;3;0;5;0}，從而標記出了代碼C出現的行數。

這裡也可以看出數組運算的優勢，即可以返回多個值。

篩選運算元

主要起邏輯強化的作用，仍如($B$6:$B$11=$E$7)*ROW($1:$6)，這裡使用「*」，來達到邏輯與的作用，通過乘法運算得到非0項，也就是我們需要的結果{0;0;3;0;5;0}。

不重複運算元

由於MATCH函數只能獲得各個數據第一次出現的位置，與ROW（1:x）相比較後，兩者相同的位置即為不重複數據，兩者不同則是重複的數據（如下圖）。或配合LARGE或SMALL取出所需數組（如之前ROW函數例子）。

同樣的道理，當我們查找不重複值是在一個字元串里，我們還可以使用FIND()、MID()函數來生成不重複運算元。

參數驅動

這裡展現的是逐步將參數數組化，從而最終得到一維數組、二維數組甚至多維數組。這種思想起著嚮導作用，如果面對複雜的問題採取逐步分解，一步步達到最終結果。

第一步，行方向運用數組

第二步：列方向也數組化

六、總結

剛接觸數組時，可能會感覺太難，但就像老師所講的參數驅動，我們可以一步步來，從而完成一個數組公式。在論壇中，也可以找到很多帖子來幫助我們學習。

這個帖子很適合初學者，分享跟大家。謝謝！

http://club.excelhome.net/thread-511876-1-1.html

更多精彩，可登陸ExcelHome查看。

網址：http://club.excelhome.net/forum.php