數組公式入門——開開啟函數公式的新大門

一、數組的概念及分類

在Excel中，數組是由（Array）是由一個或者多個元素按照多行或者多列排列方式進行組合成的集合，這些元素可以是文本、數值、邏輯值、日期、錯誤值等。根據數組的存在形式，可為常量數組、區域數組和內存數組。

1、常量數組

常量數組的組成元素均為常量數據，其文本必須由一對半形雙引號包括起來。常量數組的表示方法為一對大括弧（{}）將構成數組的常量包括起來，各常量數組之間是用分隔符間隔。可以使用的分隔符包括半形分號（;）和半形逗號（,），其中分號用於間隔的元素是按行排列的元素，逗號用於間隔按列排列的元素。

例如：{0,"不及格";60,"及格"}，這是一個2行2列的數組。

2、區域數組

區域數組實際上就是公式是對單元格區域直接引用。例如：

=SUMPRODUCT(J3:J10*K3:K10)，公式中的J3:J10，K3:K10都是區域的數組。

3、內存數組

內存數組是指通過公式計算返回的結果在內存是臨時構成，並且還可以作為一個整體直接嵌套到其他公式中繼續參與計算的數組。常量數組是不依賴於單元格而存在的，但與內存數據不同的是常量數組不是通過公式計算獲取，而是在公式中直接輸入的。

例如：{=SMALL(J5:K14,{1,2,3})}，這個公式中{1,2,3}是常量數組，而整個公式得到的計算結果為J5:K14數據區域中的最小的3個數組成的1列3行的內存數組。

二、數組公式的維度與尺寸

數組具有行、列及尺寸的特徵，常量數組中用分號或者逗號隔開來辨別行列，而區域數組的行列結構則與某引用的單元格區域保持一致。數組的尺寸同時由兩個元素來確定，M行與N列的二維數組是由M*N個元素來組成。

例如常量數組{0,"不及格";60,"及格"; 70,"中午";80,"良好";90,"優秀"}，它包含了5洗2列，一共由5*2=10個元素構成。如圖所示：

數組中的各行與各列的元素數必須保持一致，也就是其尺寸必須保持一致，不然的話將會返回錯誤值。

同時包含兩個方向的無數的數組稱之為「二維數組」，與其區分的是：如果數組的元素在同一行或者同一列中，那麼就稱之為「一維數組」。例如{1,2,3,4}都在同一行中，它的所有的元素都是同一行 ，那麼我們就稱之為「水平數組」，而與之相反的如{1;2;3;4}是一個單列數組，所有的元素都在同一列，那麼稱之為「垂直數組」。

如果數組中只包含一個元素，那第稱之為「，單元素數組」，如{1}，ROW(1:1)等等，與單個數據不同，單元素數組也包含「維」的特徵，可以看作是1行1列的一維水平數組或者垂直數組。

三、多項計算與數組公式

公式中使用數組進行運算，根據公式或者函數的用法以及目的不同，通常有以下兩種不同的計算方式。

一種是以將數組作為一個整體進行運算，運算的結果也通常只有單個數據。例如公式：=SUM(A1:A10),公式是是對它們的整體進行運算，求取他們的合值。

另一種就是將數組中的每個元素同時分別運算，數組的直接運算結果或者公式的最終結果會返回一組數據，例如公式：=SUM(A1:A6*(A1:A6>0))，這個公式中的A1:A6>0是對區域數組中每個元素進行了比較運算符的運算，判斷是否大於0，得到了一組邏輯值結果，然後再用邏輯值與這個截獲數組中的每個元素進行相乘。相乘的結果是又將兩個數組中的每個元素分別對應相乘，得到一個新的數組。這個新的數組中包含大於0的，小於0與將小於0的轉換為0 的元素。最後才由SUM函數將其相加，經其結果也就是A1:A10區域中的所有的正數的相加。此類將數組參數的各項元素進行計算的過程稱之為「多項計算」。

對於上面的例子中的運算的過程如下:

數組公式就是對一組或多組值執行多項計算，並返回一個或者多個結果，其公式括於大括弧中，按<Ctrl Shift Enter>一組合鍵結束的就稱為數組公式。

數組公式可以執行多項計算，但是執行了多項計算的公式並不都是數組公式，如下面的公式：=SUM({1;2;3;4;5}*{-1;-2;3;6;7}>0),這個公式可以執行多項計算並且得到正確的結果，但是災個公式暗藏不是數組公式。

除此之外 有些函數也不需要使用數組公式就以進行多項計算，例如：SUMPRODUCT、LOOKUP等函數。因此，數組公式並不能與多項計算划上等號。

四、多單元格數組公式

在單個單元格中使用數組公式進行多項計算後，有時可以返回一組運算結果，但單元格中只能顯示單個值（通常是結果中的首個元素），而無法顯示整組運算結果。而使用多單元格數組公式，則可以將結果數組中的每一個元素分別顯示在不同的單元格中。

例如：{=SUM(A1:A6*(A1:A6>0))}，選定要輸入的區域B1:B10，按組鍵完成輸入後，就可以得到一組結果。稱為「多單元格數組」。

使用多單元格數組公式能夠保證在同一個範圍內的公共具有同一性，並且在選定的範圍內分別顯示數組公式的各個運算結果。創建此類公式後， 公式所在單元格都不能被獨立地編輯，否則將會出現警告對話框。

使用多單元格數組公式，也只是輸入方式上的一種特殊，根據公式不同，這返回的結果也有可能是單值。

五、數組的直接運算

1、數組與單值的直接運算

數組與單值（或單元元素數組）可以直接運算（所謂「直接運算」，指的是不使用函數，直接使運算符對數組進行運算），返回一個結果，並與原數組相同尺寸，如表所示：

2、同方向一維數組之間的直接運算

兩個方向的一維數組直接進行運算，會根據元素的位置進行一一對應運算，生成一個新的數組結果，例如公式：={1;2;3;4}>{2;1;4;3}，返回的結果為：{FALSE; TRUE；FALSE; TRUE}，公式的運算結果如下：

參與運算的兩個一維數據通常需要有相同的尺寸，否則結果會出現錯誤值，如：={1;2;3;4}>{1;2},則返回的結果為{FALSE;TRUE;#N/A;#N/A}，超出較小的那個數組的尺寸的部分會出現錯誤。

3、不同方向一維數組之間的直接運算

兩個不同方向的一維數組即M行垂直數組與N列水平數組進行運算，其運算的方式是：數組中的每一個元素分別與另一數組中的每一個元素進行運算，返回M*N的二維數組。

 如：={1;2;3;4}*{1,2,3},返回的結果為{1,2,3;2,4,6;3,6,9;4,8,12},具體的運算如圖：

4、一維數組與二維數組之間的直接運算

如果一個一維數組與另一個二維數組的同一方向上的尺寸一致時，可以在這個方向上與數組中的每一個元素進行一一對應的運算。即M行N列的二維數組可以與M行N列的另外一個數組進行運算，即返回一個M*N的二維數組。

如：={1;2;3;4}*{1,2;4,5;6,7;8,9},返回的結果為{1,2;8,10;18,21;32,36}。運算過程如下圖

如果兩個數組之間的尺寸不一致的時候 ，就會出現錯誤。

5、下維數組之間的直接運算

兩個二維數組如果具有完全相同的尺寸，也可以直接進行運算，運算中將每個相同位置的元素兩兩對應進行運算，返回一個與他們尺寸一致的二維數組結果。

如：={1，2，3；2，3，4；3，5，6}*{1，4，5；2，5，6；3，5，4}，返回的結果為：{1,8,15;4,15,24;9,25,24}.

如果參與運算的兩個二維數組尺寸不一致，那麼生成的結果以兩個數組中的最大行列尺寸為新的數組尺寸，但超出小尺寸的數組部分會產生錯誤值。

除了上述所說的數組的直接運算外， 數組之間的運算還包括使用函數。部分函數對參與運算的數組尺寸有特定的要求，比如MMULT函數要求Array1的列數與Array2的行數相同，而不一定遵循直接運算的規則。

-US>={1，2，3；2，3，4；3，5，6}*{1，4，5；2，5，6；3，5，4}，返回的結果為：{1,8,15;4,15,24;9,25,24}.

六、數據公式中的邏輯運算

AND與OR函數分別可以進行「邏輯與」、「邏輯或」計算，但是在需要進行多項計算的數組公式中，這兩個函數只能返回單個TRUE或FALSE，無法返回數組結果。

如：假定A1:A9單元格包含了一組數據，要統計其中大於60小於70的個數。

如果單純地從And角度出發，可能寫成的公式為：{=SUM(AND(A1:A9>60,A1:A9<70)*1)}。但事實上，這個公式不能有效地返回正確的結果，原因就在於AND不能執行多項計算，不會將兩個邏輯值進行每一項元素的計算，而只會將兩個數組中的元素看作是一個整體，只能返回單值。上這個公式的返回的結果為：

{=SUM(AND({TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}*1))正確的做法應該是使用簡潔運算符代替AND函數，故可以將上述的公式代替為：

{=SUM((A1:A9>60)*(A1:A9<70))}，按組合鍵結束，其運算的過程為：

這種數組的多項邏輯計算在數組公式中是非常地常見的，對於上面的公式也可以拓展到求和與求平均值的用法。

    該方法就是利用將邏輯值轉化為0與1的數值來參與運算從而來構建公式。

推薦閱讀：