Excel數組公式

我身邊的朋友有很多人使用Excel，但是都不了解Excel數組公式，就不能認為已經掌握了Excel。數組公式是Excel功能量強大的計算公式?？梢允褂帽磉_式和各種函救來創建數組公式。以解決用普通公式所無法解決的復雜問題。

所謂數組公式。就是對數組進行計算的公式。前面介紹的公式基本上都是執行一個簡單計算。并且返回一個計算結果。當需要對兩組或兩組以上的數據進行計算并返回一個或多個計算結果時。就需要使用數組公式了。

數組公式具有以下特征：

·單擊教組公式所在的任意單元格。就可以在公式編輯欄中看到公式前后出現的大括號“{}”。在公式編輯欄中單擊。大括號就會消失。

·輸入數組公式的每個單元格中的公式是完全相同的。

·必須按【Ctrl+Shift+Enter】組合鍵才能得到數組公式。否則。如果只按【Enter】鍵。那樣得到的是普通公式。

·公式中必定有單元格區域的引用?；蛘弑囟ㄓ袛到M常量。

·不能單獨對數組公式所涉及的單元格區域中的某一個單元格進行編輯、刪除或移動等操作。

·數組公式可以存在于多個單元格中。也可以僅輸入到一個單元格。即使是輸入到一個單元格的數組公式。也必須按【Ctrl+Shift+Enter】組合鍵來創建。

下面結合實際案例來說明數組公式的使用方法。

圖1所示的A列至C列數據是從數據庫導出的數據。其中C列的批次是數量和單位的混合字符串?，F在要求把批次分成數量和單位兩部分。分別保存在D列和E列。以便于以后進行統計分析。

圖1

顯然。這樣的問題不是一個簡單的公式所能解決的，需要使用數組公式。

要將批次中的數量和單位分開。需要了解批次數據中從左邊開始哪些字符是數字。到哪個字符就由數字變為了文本（漢字或字母），這樣才能利用文本函數LEFT把數量取出來。利用文本函數MID把單位取出來。下面以單元格C3的批次數據為例。說明解決問題的思路和公式的創建方法及步驟。

要想判斷批次數據中從左邊開始哪些字符是數字。到哪個字符就由數字變為了文本。需要知道批次數據有多少個字符。然后利用MID函數把每個字符取出來。因此。首先使用LEN函數得到批次數據的長度。再利用INDIRECT函數和ROW函數得到一個從1開始到批次數據長度結束的自然數序列。選擇單元格區域G2：G15（或者G列從第2行開始到某行為止。行數要大于批次數據字符長度），輸人數組公式“=ROW（INDIRECT（“1：”＆LEN（C3）））”，如圖2所示。

圖2

有了這個自然數序列。就可以利用MID函數把批次數據的各個字符分別取出來了。選擇單元格區域H2：H15.輸人數組公式“=MID（c3，G2：G15，1）”。如圖3所示。

圖3

由于利用MID函數從字符串中取出的不論是數字還是文本。都被處理成文本。因此需要把取出來的批次數據中代表數量的數字轉換成真正的數字。選擇單元格區域12：|15.輸入數組公式“=1*H2：H15”。如圖4所示。

圖4

顯然，從批次數據左側開始取出的各個數據。只要開始出現錯誤值。就表明從該字符開始就是單位了。因此需要判斷從哪個位置開始出現錯誤值。選擇單元格區域J2：J15.輸入數組公武“=ISERROR（12：|15）”。如圖5所示。

圖5

這樣，就知道了批次數據各個字符串是否為數字（FALSE表示是數字，TRUE表示是文本，第一個出現TRUE的位置就是單位開始出現的位置）。因此可以利用MATCH函數確定這個位置。選擇單元格K2.輸入公式“=MATCH（TRUE.J2:J15.0）”。即可得到這個位置的數宇。如圖6所示。

圖6

知道了批次數據中從哪個字符開始就是單位?？衫肔EFT函數很容易地把數量數字取出。利用MID函數把單位取出。選擇單元格L2.輸入公式“=1*LEFT（C3.K2—1）”。得到批次數據中的數量數字。如圖7所示。

圖7

最后。按照與上面相反的順序。將中間的計算過程逐一消除，把中間計算過程的計算公式綜合成一個公式。步驟如下：

1、單元格L3的公式引用了單元格K2的結果。而單元格K2的計算公式為“=MATCH（TRUE，J2：J15，O）”，這樣，把單元格K2的計算公式去掉等號，僅僅復制等號后面的函數本身，并用它替換掉單元格L2計算公式中的單元格引用K2.得到新的計算公式：

=1*LEFT（C3，MATCH（TRUE，J2：J15，0）-1）

2、這個公式中引用了單元格區域J2：J15的結果。而單元格區域J2：J15的計算公式為“=ISERROR（I2：I15）”，這樣，把上述公式中的單元格引用J2：J15替換為ISERROR（I2：I15），得到新的計算公式（注意從此開始就是數組公式了，因此要按[Ctrl+Shift+Enter】組合鍵）：

=1*LEFT（C3，MATCH（TRUE，ISERROR（I2：I15），0）-1）

3、這個公式中引用了單元格區域I2：I15的結果，而單元格區域I2：I15的計算公式為“=1*H2：H15”，這樣，把上述公武中的單元格引用I2：I15替換為1*H2：H15，得到新的計算公式：

=l*LEFT（C3，MATCH（TRUE，ISERROR（1*H2：H15）。0）一1）

4、這個公式中引用了單元格區域H2：H15的結果，而單元格區域H2：H15的計算公式為“=MID（C3，G2：G15.1）”，這樣，把上述公式中的單元格引用H2：H15替換為MlD（C3，G2：G15，1），得到斯的計算公式：

=1*LEFT（C3，MATCH（TRUE，ISERROR（1*MID（C3，G2：G15，1）），0）-1）

5、這個公式中引用了單元格區域G2：G15的結果，而單元格區域G2：G15的計算公式為“=ROW（INDIRECT（"1："&LEN（c3）））”，這樣，把上述公式中的單元格引用G2：G15替換為ROW（1NDIRECT（"1："&LEN（c3））），得到新的計算公式：

=1*LEFT（C3，MATCH（TRUE，ISERROR（1*MID（C3，ROW（INDIRECT（"1："&LEN（C3））），1））。0）-1）

6、這個公式就是最終需要的公式，將這個公式原封不動地復制到單元格D3中（要注意將公式復制到單元格后，按【Ctrl+Shift+Enter】組合鍵），就得到了批次數據中的數量。

7、在單元格E3中輸入公式“=MID（C3.LEN（D3）+1.999）”。就得到了批次數據中的單位。最后的結果如圖8所示。

圖8

有時候報表中會有一些錯誤值。這時如果使用SUM函數進行加總。就會出現錯誤。從而得不到正確的結果，如圖9所示。為了能夠得到正確的計算結果。也就是忽略錯誤值。僅僅計算單元格區域的數值。就需要使用下面的數組公式了（見圖9）：

=SUM（IF（ISERROR（B2：B6），""，B2：B6））或者 =SUM（IFERROR（B2：B6，""）） 

圖9

學會了Excel數組公式以后，我們就可以解決更加復雜的問題了，通過這節，我們首先了解了Excel數組公式是什么？根據實例簡單了解了下Excel數組公式的運算。