Excel數(shù)組公式

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

所謂數(shù)組公式。就是對數(shù)組進行計算的公式。前面介紹的公式基本上都是執(zhí)行一個簡單計算。并且返回一個計算結(jié)果。當(dāng)需要對兩組或兩組以上的數(shù)據(jù)進行計算并返回一個或多個計算結(jié)果時。就需要使用數(shù)組公式了。

數(shù)組公式具有以下特征：

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

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

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

·公式中必定有單元格區(qū)域的引用?；蛘弑囟ㄓ袛?shù)組常量。

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

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

下面結(jié)合實際案例來說明數(shù)組公式的使用方法。

圖1所示的A列至C列數(shù)據(jù)是從數(shù)據(jù)庫導(dǎo)出的數(shù)據(jù)。其中C列的批次是數(shù)量和單位的混合字符串?，F(xiàn)在要求把批次分成數(shù)量和單位兩部分。分別保存在D列和E列。以便于以后進行統(tǒng)計分析。

圖1

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

要將批次中的數(shù)量和單位分開。需要了解批次數(shù)據(jù)中從左邊開始哪些字符是數(shù)字。到哪個字符就由數(shù)字變?yōu)榱宋谋荆h字或字母），這樣才能利用文本函數(shù)LEFT把數(shù)量取出來。利用文本函數(shù)MID把單位取出來。下面以單元格C3的批次數(shù)據(jù)為例。說明解決問題的思路和公式的創(chuàng)建方法及步驟。

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

圖2

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

圖3

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

圖4

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

圖5

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

圖6

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

圖7

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

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

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

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

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

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

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

4、這個公式中引用了單元格區(qū)域H2：H15的結(jié)果，而單元格區(qū)域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、這個公式中引用了單元格區(qū)域G2：G15的結(jié)果，而單元格區(qū)域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、這個公式就是最終需要的公式，將這個公式原封不動地復(fù)制到單元格D3中（要注意將公式復(fù)制到單元格后，按【Ctrl+Shift+Enter】組合鍵），就得到了批次數(shù)據(jù)中的數(shù)量。

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

圖8

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

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

圖9

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