Excel數(shù)組公式及運(yùn)用

第一部分：了解數(shù)組公式在開始講數(shù)組公式之前，我們先來認(rèn)識幾個必要的概念。

1、數(shù)組

什么是數(shù)組？仁者見仁，智者見智。

我個人的感覺是：數(shù)組是具有某種聯(lián)系的多個元素的組合。某班級里有50個學(xué)生，這里，如果班級是數(shù)組，50個學(xué)生就是數(shù)組里的50個元素。當(dāng)然，班級里的元素是可變的，可以是20個，可以是30個，也可以是60個。放到Excel里，班級就相當(dāng)于工作表，而學(xué)生就相當(dāng)于工作表里的單元格數(shù)值。所以，Excel里的數(shù)組，我還把它理解是為多個單元格數(shù)值的組合。

2、公式

如果你在使用Excel，如果你說你還沒聽過“公式”這個名詞，我只能說：“你太OUT了！”

什么是公式？我的理解是：在Excel里，凡是以半角符號“=”開始的、具有計(jì)算功能的單元格內(nèi)容就是所謂的Excel公式。如：=SUM(B2:D2)，=B2+C2+D2這些都是公式。

3、數(shù)組公式

數(shù)組公式是相對于普通公式而言的。普通公式（如上面的=SUM(B2:D2)，=B2+C2+D2等），只占用一個單元格，只返回一個結(jié)果。

而數(shù)組公式可以占用一個單元格，也可以占用多個單元格。它對一組數(shù)或多組數(shù)進(jìn)行多重計(jì)算，并返回一個或多個結(jié)果。

集合在教室外面的學(xué)生，老師把他們叫進(jìn)教室。老師說：“第一組第一桌的同學(xué)進(jìn)教室?！庇谑堑谝唤M第一桌的同學(xué)走進(jìn)教室。老師接著叫：“第一組第二桌的同學(xué)進(jìn)教室?！比缓笫堑诙赖耐瑢W(xué)進(jìn)教室。老師再叫：“第一組第三桌的同學(xué)進(jìn)教室?！比缓蟮谌赖耐瑢W(xué)走進(jìn)教室。接著是第四桌，第五桌……，就這樣一個學(xué)生一個學(xué)生的叫，這就是普通公式的做法，學(xué)生回到座位，就像數(shù)值回到工作表的單元格里，一個座位叫一次，就像一個單元格輸入一個公式。

如果老師說：“第一組的全部進(jìn)教室?！睂W(xué)生聽到命令后，第一桌的同學(xué)走進(jìn)去，然后是第二桌，第三桌……，老師不用再下第二個命令，這是數(shù)組公式的處理方法。

4、數(shù)組公式的標(biāo)志

在Excel中數(shù)組公式的顯示是用大括號對“{}”來括住以區(qū)分普通Excel公式。

如圖：

（1）數(shù)組公式：

\o"1-1.jpg下載次數(shù):499"

（2）普通公式：

\o"1-2.jpg下載次數(shù):374"

輸入數(shù)組公式：用Ctrl+Shift+Enter結(jié)束公式的輸入。

特別提醒：這是最關(guān)鍵的，這相當(dāng)于用戶告訴Excel：“我不是一般人，爺我是數(shù)組公式，你得對我特別關(guān)照?！庇谑?，Excel明白了，不能用常規(guī)的邏輯來對待這位大爺。當(dāng)你按下三鍵后，Excel會自動給公式加上“{}”以和普通公式區(qū)別開來，不用用戶輸入“{}”，但如是是想在公式里直接表示一個數(shù)組，就需要輸入“{}”來把數(shù)組的元素括起來。

如：

\o"1-3.jpg下載次數(shù):386"

=IF({1,0},D2:D8,C2:C8)這個公式里的數(shù)組{1,0}的括號就是用戶自己輸入的。

5、數(shù)組的維數(shù)

“維數(shù)”是數(shù)組里的又一個重要概念。數(shù)組有一維數(shù)組，二維數(shù)組，三維數(shù)組，四維數(shù)組……

在公式里，我們更多接觸到的只是一維數(shù)組和二維數(shù)組。

一維數(shù)組我們可以簡單地看成是一行的單元格數(shù)據(jù)集合，比如A1:F1。一維數(shù)組的各個元素間用英文的逗號“,”隔開(如果是單獨(dú)的一列時，用英文分號“;”隔開）。

\o"1-4.jpg下載次數(shù):510"

{1,2,3,4,5,6}，這就是一個有6個元素的一維數(shù)組，或者說，只有一行的數(shù)組。數(shù)組的各個元素間用逗號“,”分隔。如果想把這個數(shù)組輸入到工作表的單元格里，同時選中同一行里相領(lǐng)的六個單元格，輸入：={1,2,3,4,5,6}后，三鍵結(jié)束公式，你就可以看到這個一維數(shù)組被輸入到工作表的單元格里了。

自己動手試一試。

二維數(shù)組可以看成是一個多行多列的單元各數(shù)據(jù)集合，也可以看成是多個一維數(shù)組的組合。如單元格A1:D3，就是一個三行四列的二維數(shù)組。我們可以把它看成是A1:D1、A2:D2與A3:D3這三個一維數(shù)組的組合。二維數(shù)組里同行的元素間用逗號“,”分隔，不同的行用分號“;”分隔。

我們可以用上面的方法，在A1:D3區(qū)域輸入數(shù)據(jù)，并引用地址，按F9來查看。

\o"1-5.jpg下載次數(shù):516"

可以看到在數(shù)組里，換行的時候，元素間的分隔符是“;”，所以，要判斷一個數(shù)組是幾行幾列的數(shù)組，只需要看里面的逗號和分號就知道了。

如果需要把數(shù)把數(shù)組返回到單元格區(qū)域里，首先得看數(shù)組是幾行幾列，然后再選擇相應(yīng)的單元格區(qū)域，輸入數(shù)組，三鍵結(jié)束。

對了，是哪三鍵你還不要忘記了：Ctrl+Shift+Enter

記?。?/p>

（1）一維數(shù)組是單獨(dú)的一行或一列。二維數(shù)組是多行多列。

（2）數(shù)組里的元素，同一行內(nèi)的各元素用英文逗號“,”分開，用英文分號“;”將各行分開。

（3）二維數(shù)組的元素按先行后列的順序排列?？偸沁@樣：{第一行的第一個,第一行的第二個,第一行的第三個……;第二行的第一個，第二行的第二個，第二行的第三個……;第三行的第一個……}

第二部分：數(shù)組公式的初步認(rèn)識在對數(shù)組公式有了一個簡單的了解之后，這貼我們將通過一些簡單的例子來進(jìn)一步認(rèn)識數(shù)組公式。

問題1：在D2:D4求出商品的銷售金額。

\o"2-1.jpg下載次數(shù):429"

現(xiàn)在你解決這個問題會用什么辦法呢？

我知道很小兒科，千萬不要在心里罵我拿這種簡單的問題來考你。

是的，很簡單，在D2單元格輸入公式“=B2*C2”，下拉公式即可。

\o"2-2.jpg下載次數(shù):360"

在這里，D2:D4三個單元格輸入了三個普通公式，分別返回了三個值在三個單元格里。這就是老師在點(diǎn)學(xué)生進(jìn)教室，第一組第一桌的同學(xué)進(jìn)教室入座，第一組第二桌的同學(xué)進(jìn)教室入座……

我們試著用數(shù)組公式來解決這個問題，老師嗓子不好，讓他叫一次我們就乖乖進(jìn)教室去得了。

\o"2-3.jpg下載次數(shù):423"

選中D2:D4輸入公式“=B2:B4*C2:C4”，三鍵結(jié)束輸入數(shù)組公式，即可得到同樣的結(jié)果。

這就是一個多單元格的數(shù)組公式，多單元格數(shù)組公式是進(jìn)行批量計(jì)算，可節(jié)省計(jì)算的時間，同時，它還有一個特點(diǎn)。當(dāng)你輸入完數(shù)組公式后，請你嘗試修改公式區(qū)域里其中一個單元格的公式，看看會有什么結(jié)果。

\o"2-4.jpg下載次數(shù):379"

是的，你已經(jīng)發(fā)現(xiàn)了，會彈出一個對話框，提醒你：不能修改數(shù)組的某一部分。

這就是多單元格數(shù)組公式的一個重要的特點(diǎn)：保證公式集合的完整性不被修改。這可以防止用戶在操作時無意間修改到表格的公式。這是不是會安全得多？

當(dāng)然，如果你要修改公式的話，必須得選中公式所在的所有單元格。

問題2：在F1求出商品的銷售總金額

\o"2-5.jpg下載次數(shù):396"

這一題如果你用普通公式又怎么解決呢？我想象中可能有兩種方法：

A、插入輔助列，先求出各商品的銷售額，然后再求總和。

B、直接在F1輸入公式“=SUM(B2*C2,B3*C3,B4*C4)”，這樣看上去不錯，可是，如果有100行數(shù)據(jù)，一千行號數(shù)據(jù)呢？先不考慮單元格能容納多少字符的問題，就光輸入公式，累也得把你累趴下，顯然是行不通的。

這時候就需要用數(shù)組公式來完成了。

選中F1單元格，輸入公式“=SUM(B2:B4*C2:C4)”，三鍵確認(rèn)輸入即可。

\o"2-6.jpg下載次數(shù):379"

這是一個單個單元格的數(shù)組公式，B2:B4*C2:C4是兩個一維數(shù)組相乘，返回一個新的一維數(shù)組，最后用SUM函數(shù)對返回的數(shù)組進(jìn)行了求和。這里，用一個數(shù)組公式代替了多個公式的方式來完成了數(shù)據(jù)的計(jì)算。

做了這個問題，總結(jié)一下，什么時候會用到數(shù)組公式？

是的，當(dāng)運(yùn)算中存在著一些只有通過復(fù)雜的中間運(yùn)算過程才會等到結(jié)果的時候，就需要使用數(shù)組公式了。

這一貼的內(nèi)容非常簡單，記住幾點(diǎn)：

（1）三鍵輸入數(shù)組公式。

（2）數(shù)組公式同時進(jìn)行多個計(jì)算，可返回一個或多個結(jié)果。

（3）多單元格數(shù)組公式需選區(qū)多個單元格進(jìn)行輸入，多單元格數(shù)組公式具有保護(hù)公式的作用。

（4）數(shù)組公式可以完成復(fù)雜的中間運(yùn)算得到最終想要的運(yùn)算結(jié)果v

第三部分：數(shù)組公式的計(jì)算學(xué)習(xí)繼續(xù)，在對數(shù)組有了基本的認(rèn)識后，這貼我們將通過一些例子來講一講數(shù)組公式是怎么計(jì)算的。

1、行列數(shù)相同數(shù)組的運(yùn)算\o"3-1.jpg下載次數(shù):408"

數(shù)組1+數(shù)組2，這是一個多單元格的數(shù)組公式，第一個數(shù)組的第一個元素與第二個數(shù)組的第一個元素相加，結(jié)果作為數(shù)組公式結(jié)果的第一個元素，然后第一個數(shù)組的第二個元素與第二個數(shù)組的第二個元素相加，結(jié)果作為數(shù)組公式結(jié)果的第二個元素，接著是第三個元素……直到第N個。

\o"3-2.jpg下載次數(shù):348"

這是橫向的一維數(shù)組的計(jì)算，原理同上。

\o"3-3.jpg下載次數(shù):379"

這是二維數(shù)組與二維數(shù)組進(jìn)行計(jì)算，生成一個新的二維數(shù)組的多單元格數(shù)組公式。同樣的計(jì)算過程，第一個數(shù)組的第一行的第一個元素與第二個數(shù)組的第一行的第一個元素相乘，結(jié)果為數(shù)組公式的結(jié)果的數(shù)組的第一行的第一個元素，接著是第二個，第三個……直到第N個。

規(guī)律很簡單：兩個同行同列的數(shù)組計(jì)算是對應(yīng)元素間進(jìn)行運(yùn)算，并返回同樣大小的數(shù)組。

正如穿鞋要穿合腳的才走得了路一樣，在公式或函數(shù)中使用數(shù)組時，運(yùn)算對象或參數(shù)的數(shù)組維數(shù)要匹配，否則計(jì)算會出錯。教室里，第一排的有8個同學(xué)，第二排有9個同學(xué)，老師說：“第一排和第二排的同學(xué)交換作業(yè)，互相檢查。”第二排的第9個同學(xué)和誰交換？這就是數(shù)組的不匹配。數(shù)組不匹配時，工作就不能完成了。

你可以試著改一改數(shù)組的參數(shù)試試。

2、數(shù)組與單一的數(shù)據(jù)的運(yùn)算

\o"3-4.jpg下載次數(shù):354"

這相當(dāng)于在E42單元格輸入公式=A42*$C$42，然后下拉復(fù)制公式實(shí)現(xiàn)。

\o"3-5.jpg下載次數(shù):334"

等同于在B56輸入公式“=B52+$B$54”，然后右拉復(fù)制公式實(shí)現(xiàn)。

\o"3-6.jpg下載次數(shù):339"

等同于在C67單元格輸入公式“=A60+$E$60”然后右拉下拉復(fù)制公式實(shí)現(xiàn)。

不難看出：一個數(shù)組與一個單一的數(shù)據(jù)進(jìn)行運(yùn)算，是將數(shù)組的每一元素均與那個單一數(shù)據(jù)進(jìn)行計(jì)算，并返回同樣大小的數(shù)組。

3、單列數(shù)組與單行數(shù)組的計(jì)算

\o"3-7.jpg下載次數(shù):399"

兩個數(shù)組相加，查看結(jié)果是幾行幾列：在任意單元格輸入公式“=A80:A83+B87:E87”，抹黑公式，按F9鍵，可看到公式的計(jì)算結(jié)果為數(shù)組“{110,210,310,410;120,220,320,420;130,230,330,430;140,240,340,440}”通看看分號與逗號，我們知道這是一個四行四列的數(shù)組，選擇一個四行四列的單元格，輸入公式“=A80:A83+B87:E87”，三鍵結(jié)束，可看到返回的結(jié)果為：

\o"3-8.jpg下載次數(shù):434"

相當(dāng)于在E80輸入公式“=$A80+B$87”右拉下拉復(fù)制公式的結(jié)果。

單列數(shù)組與單行數(shù)組的計(jì)算：

A、計(jì)算結(jié)果返回一個多行列的數(shù)組；

B、返回?cái)?shù)組的行數(shù)同單列數(shù)組的行數(shù)相同、列數(shù)同單行數(shù)組的列數(shù)相同。

C、返回?cái)?shù)組中第R行第C列的元素是單列數(shù)組的第R個元素和單行數(shù)組的第C個元素運(yùn)算的結(jié)果。

4、行數(shù)（或列數(shù)）相同的單列（或單行）數(shù)組與多行多列數(shù)組的計(jì)算

（1）單列數(shù)組的行數(shù)與多行多列數(shù)組的行數(shù)相同時：

\o"3-9.jpg下載次數(shù):435"

（2）單行數(shù)組的列數(shù)與多行多列數(shù)組的列數(shù)相同時：

\o"3-10.jpg下載次數(shù):375"

計(jì)算規(guī)律同單行單列的數(shù)組計(jì)算的規(guī)律大同小異：

A、計(jì)算結(jié)果返回一個多行列的數(shù)組；

B、返回?cái)?shù)組的行、列數(shù)與多行多列數(shù)組的行列數(shù)相同；

C、單列數(shù)組與多行多列數(shù)組計(jì)算時，返回的數(shù)組的第R行第C列的數(shù)據(jù)等于單列數(shù)組的第R行的數(shù)據(jù)與多行多列數(shù)組的第R行第C列的數(shù)據(jù)的計(jì)算結(jié)果；

D、單行數(shù)組與多行多列數(shù)組計(jì)算時，返回的數(shù)組的第R行第C列的數(shù)據(jù)等于單行數(shù)組的第C列的數(shù)據(jù)與多行多列數(shù)組的第R行第C列的數(shù)據(jù)的計(jì)算結(jié)果。

＝＝＝＝＝＝＝留給你的思考題＝＝＝＝＝＝＝

講到這里，我們可以暫停一下進(jìn)度。課間休息，插播一段廣告：

你可以喝杯水，聽聽音樂，然后我們來看幾個例子：

圖1：

\o"3-11.jpg下載次數(shù):479"

圖2：

\o"3-12.jpg下載次數(shù):459"

圖3：

\o"3-13.jpg下載次數(shù):404"

上面的三張圖，第一個公式是我們前面講的例子，第二個公式是在第一個公式的基礎(chǔ)上對參與計(jì)算的數(shù)組區(qū)域進(jìn)行了修改，但是，兩個不同參數(shù)的公式，返回的結(jié)果卻都是一樣的。這里我只是舉了三個例子，你可以把前面我們講過的公式里的數(shù)組參數(shù)都修改修改，什么情況下，會返回相同的結(jié)果呢？它們又有什么共同的地方？知識總是光顧那些善于總結(jié)和發(fā)現(xiàn)的人。否則，踩著別人的腳印走，想要看到別人沒看到的風(fēng)景，你要等到猴年馬月？

好了，我也仿小學(xué)老師的口氣問問大家：“為什么兩個不同的公式，返回的結(jié)果都是一樣的呢？從上面的圖，你發(fā)現(xiàn)了什么？把你的發(fā)現(xiàn)說給你的伙伴聽一聽?！?/p>

這就是你今天的作業(yè)，如果你是真心想想學(xué)數(shù)組公式的，記得跟貼回復(fù)！

5、行、列數(shù)不相等的數(shù)組計(jì)算

（1）行數(shù)不相等的單列數(shù)組與與多行列數(shù)組的計(jì)算

\o"3-14.jpg下載次數(shù):377"

（2）列數(shù)不相等的單行數(shù)組與多行多列數(shù)組的計(jì)算

\o"3-15.jpg下載次數(shù):349"

（3）行、列數(shù)不相同的兩個多行多列數(shù)組的計(jì)算

\o"3-16.jpg下載次數(shù):364"

有了對前面例子的分析，再來看這三個例子就相對簡單了。它們的計(jì)算規(guī)則和前面都是一樣的，不難看出：

A、公式返回一個多行多列數(shù)組；

B、返回?cái)?shù)組的行數(shù)與參與計(jì)算的兩個數(shù)組中行數(shù)較大的數(shù)組的行數(shù)相同，列數(shù)與較大的列數(shù)的數(shù)組相同；

C、返回?cái)?shù)組的大于較小行數(shù)數(shù)組行數(shù)、大于較大列數(shù)數(shù)組列數(shù)的區(qū)域的元素均為#N/A。有效元素為兩個數(shù)組中對應(yīng)數(shù)組的計(jì)算結(jié)果。

需要提醒一點(diǎn)的是，對會返回#N/A的數(shù)組，在進(jìn)行再計(jì)算和處理時，考慮對#N/A值作相應(yīng)的處理！

比如我們想對上面數(shù)組與數(shù)組2相加后的結(jié)果進(jìn)行求和：

\o"3-17.jpg下載次數(shù):455"

正確的公式（數(shù)組）：=SUM(IF(ISNA(A213:B216+D213:F215),0,A213:B216+D213:F215))

通過ISNA函數(shù)對返回的數(shù)組里的各個元素進(jìn)行了判斷和處理，把把有的#N/A值替換成數(shù)值0，最后再用SUM函數(shù)對所有數(shù)值進(jìn)行求和。

我們說，數(shù)組計(jì)算時，得注意行列數(shù)的匹配，其實(shí)如果了解了數(shù)組的計(jì)算原理后，能正確處理那些返回的#N/A值的話，很多時候，并不會出錯的

第四部分：數(shù)組擴(kuò)充這一貼的內(nèi)容相對比較簡單，主要是對第三部分，數(shù)組的計(jì)算里提出的思考問題作出回復(fù)。昔日關(guān)云長溫酒斬華雄的故事聽過吧？如果你已認(rèn)真讀了前面的貼子，且用心總結(jié)了下，再來看此貼，相信你也會有“云長提華雄之頭，擲于地上，其酒尚溫”的豪氣。

呵呵……嫌我唐僧了吧？那端上一杯熱茶，快快進(jìn)入主題，當(dāng)讀完貼后，你的茶是否喝完？

讀完上一貼，了解了數(shù)組公式的計(jì)算規(guī)律后，我們知道，數(shù)組與數(shù)組計(jì)算，返回一個新的數(shù)組。返回的數(shù)組的行數(shù)與參與計(jì)算的數(shù)組中行數(shù)較大的數(shù)組的行數(shù)相同，列數(shù)與列烽較大的數(shù)組的列數(shù)相同。

但“為什么兩個不同的公式，返回的結(jié)果卻相同呢？”，這就是我們今天要講的一個新概念——數(shù)組擴(kuò)充。

數(shù)組計(jì)算時，參與計(jì)算的兩個數(shù)組得具有相同的維數(shù)，也就是得注意行列數(shù)的匹配。

對于行列數(shù)不匹配的數(shù)組，在計(jì)算時Excel會將數(shù)組對象進(jìn)行擴(kuò)展，以符合計(jì)算需要的維數(shù)。每一個參與計(jì)算的數(shù)組的行數(shù)必須與行數(shù)最大的數(shù)組的行數(shù)相同，列數(shù)必須與列數(shù)最大的數(shù)組的列數(shù)相同。

例1：

公式：=SUM({10,20,30,40}*10)里，第一個參數(shù){10,20,30,40}是一行四列的數(shù)組，第二個參數(shù)不是數(shù)組，只是一個數(shù)值，為了讓第二個數(shù)值能與第一個數(shù)組進(jìn)行專題片，這時，Excel會自動將第二參數(shù)的10擴(kuò)充成一個一行四列的數(shù)組{10，10，10，10}與第一參數(shù)匹配。所以，SUM({10,20,30,40}*10)最后是使用SUM({10,20,30,40}*{10,10,10,10})進(jìn)行計(jì)算，得到的結(jié)果是10*10，20*10，30*10，40*10的和。

\o"4-1.jpg下載次數(shù):359"

例2：

公式：={10;20;30;40}+{100,200}的第一個參數(shù){10;20;30;40}是一個四行一列的數(shù)組，{100,200}是一個一行二列的數(shù)組，在計(jì)算時，Excel會將第一個數(shù)組自動擴(kuò)充為一個四行二列的數(shù)組{10,10;20,20;30,30;40,40}，也會將第二個數(shù)組擴(kuò)充為一個四行二列的數(shù)組{100,200;100,200;100,200;100,200}，所以={10;20;30;40}+{100,200}這個公式最后是使用公式={10,10;20,20;30,30;40,40}+{100,200;100,200;100,200;100,200}進(jìn)行計(jì)算。公式最后返回的數(shù)組也是一個四行二列的數(shù)組，數(shù)組的第R行第C列的元素等于擴(kuò)充后的兩個數(shù)組的第R行第C列的元素的計(jì)算的結(jié)果。

\o"4-2.jpg下載次數(shù):367"

好了，在這一貼要講的已經(jīng)講完了。“數(shù)組擴(kuò)充”這個華雄是否已被你斬于馬下？也不知道你手里的茶喝完了沒？我希望聽到你回答的是：“華雄已斬，茶沒喝完，還溫著呢。”有興趣，記得跟貼告訴我一聲。呵呵……

繼續(xù)喝茶，休息。順便聽我再給你嘮叨幾句。

班里有50個學(xué)生，為了讓每個學(xué)生都有座位，需要預(yù)備50套課桌椅。如果只有30套課桌椅，那最后進(jìn)教室的20個同學(xué)將沒有座位，如果有60套課桌椅，將會有10套課桌椅空在教室里而別的班級需要課桌椅的同學(xué)又不能使用。浪費(fèi)啊……

學(xué)生就像數(shù)組里的元素，輸入數(shù)組公式返回?cái)?shù)組的元素就像叫學(xué)生進(jìn)教室，我們得給他們準(zhǔn)備好合適的座位。所以輸入多單元格數(shù)組公式時，應(yīng)先選中需要返回?cái)?shù)據(jù)的單元格區(qū)域，選中的單元格區(qū)域的行、列數(shù)應(yīng)與返回?cái)?shù)組的行、列數(shù)相同。否則，如果選中的區(qū)域小于數(shù)組返