HI~我是星光。今晚給大家詳細(xì)聊下Excel函數(shù)數(shù)組，篇幅較長(zhǎng)，建議沐浴更衣，先馬后看。

有人說(shuō)數(shù)組是Excel函數(shù)的精華，也有人說(shuō)數(shù)組是Excel函數(shù)的糟粕，男說(shuō)男有理女說(shuō)女有愛(ài)，各有各的理由——所以王源說(shuō)世間沒(méi)有真正的感同身受。但不管怎么說(shuō)吧，數(shù)組作為Excel函數(shù)進(jìn)階后的知識(shí)，是相當(dāng)部分Excel使用者都繞不過(guò)去的一條……河。

我想數(shù)組多少還是要學(xué)一下的。

那么什么是數(shù)組？什么是數(shù)組運(yùn)算？什么是數(shù)組公式？三者之間有何神秘關(guān)系？數(shù)組公式的概念為什么在坊間屢次引發(fā)了場(chǎng)面壯觀的口水戰(zhàn)？牽牽手，跟我來(lái)。

什么是數(shù)組

什么是數(shù)組運(yùn)算

既然有數(shù)組，也就有數(shù)組和數(shù)組之間的運(yùn)算，這就好比既然有男人，必然就有……你猜？——數(shù)組運(yùn)算又被稱為多項(xiàng)運(yùn)算，是指同時(shí)對(duì)一組或多組數(shù)組內(nèi)的元素執(zhí)行運(yùn)算。既然是運(yùn)算，必然有規(guī)則，這就好比既然有戰(zhàn)爭(zhēng)，必然就有……你再猜？——數(shù)組運(yùn)算規(guī)則總結(jié)起來(lái)有以下幾種情況。

……

第1種情況是一維數(shù)組和單值之間進(jìn)行運(yùn)算。

前面咱們講過(guò)，一維數(shù)組是指單行或單列元素構(gòu)成的數(shù)組。

單行數(shù)組又被稱為水平數(shù)組，例如單元格區(qū)域A1:D1，常量數(shù)組{1,2,3,4}。

單列數(shù)組又被稱為垂直數(shù)組，例如單元格區(qū)域A1:A4，常量數(shù)組{1;2;3;4}。

很明顯，單行數(shù)組由多列數(shù)據(jù)構(gòu)成，比如A1:D1，有A/B/C/D四列；單列數(shù)組由多行數(shù)據(jù)構(gòu)成，比如A1:A4，有第1/2/3/4四行。在常量數(shù)組中分號(hào)代表行（分號(hào)有兩層，只有行才分層，所以它代表行），逗號(hào)代表列。

數(shù)組和單值之間執(zhí)行多項(xiàng)運(yùn)算，必然返回同尺寸的數(shù)組。

舉個(gè)例子，如下所示的數(shù)組公式，單行數(shù)組{1,2,3,4}乘以2。

={1,2,3,4}*2

運(yùn)算過(guò)程是數(shù)組中的每個(gè)元素都乘以2。1*2、2*2、3*3、4*2，結(jié)果為內(nèi)存數(shù)組{2,4,6,8}。

再舉一個(gè)實(shí)戰(zhàn)的小案例。

如上圖所示，由于B列的工資為文本值，直接SUM函數(shù)求和結(jié)果會(huì)返回0。B6單元格改用公式如下。

=SUMPRODUCT(B2:B5*1)

B2:B5*1，B2:B5是一個(gè)垂直數(shù)組，運(yùn)算過(guò)程是B2:B5中的每一個(gè)元素均乘以1，通過(guò)數(shù)學(xué)運(yùn)算將文本型數(shù)值轉(zhuǎn)換為純數(shù)值。此時(shí)生成一個(gè)內(nèi)存數(shù)組{900;100;9999;99999}，SUMPRODUCT再執(zhí)行求和運(yùn)算返回正確結(jié)果。

如果把數(shù)組和單值運(yùn)算比作男女關(guān)系，單值就像皇帝，數(shù)組是它的后宮，數(shù)組內(nèi)每一個(gè)人都要給皇帝生娃娃……所以說(shuō)皇帝這個(gè)職業(yè)確實(shí)很——辛苦吶！

……

第2種情況是一維數(shù)組和一維數(shù)組之間的運(yùn)算。

由于一維數(shù)組有兩種形式，這種運(yùn)算就又產(chǎn)生了兩種情況。

一種是同方向一維數(shù)組之間的運(yùn)算。比如垂直數(shù)組和垂直數(shù)組或者水平數(shù)組和水平數(shù)值間的運(yùn)算。

這種情況的數(shù)組運(yùn)算是比較單純的男女關(guān)系，兩個(gè)數(shù)組內(nèi)的每個(gè)元素按照先后順序、一夫一妻制結(jié)婚。

舉個(gè)例子。

以上圖所示數(shù)據(jù)為例。計(jì)算商品售出總金額。公式如下：

=SUMPRODUCT(B2:B5*C2:C5)

B2:B5是垂直數(shù)組，C2:C5也是垂直數(shù)組，其運(yùn)算過(guò)程中是B2*C2、B3*C3、B4*C4、B5*C5……你看，是不是按照先后順序、一夫一妻制的規(guī)則進(jìn)行運(yùn)算的？它的計(jì)算結(jié)果是4個(gè)元素的垂直數(shù)組。

{12;70;30;15}

這規(guī)則似乎看起來(lái)讓單身汪感到特別美好，但其實(shí)并不盡然。男多女少或者女多男少的情況了解一下？

比如公式：

=SUMPRODUCT(B2:B5*C2:C4)

B2:B5是4個(gè)元素構(gòu)成的垂直數(shù)組，C2:C4是3個(gè)元素構(gòu)成的垂直數(shù)組；如果前者是男人，后者是女人，那就屬于男多女少了。

這個(gè)時(shí)候B2跟C2結(jié)婚了，B3跟C3結(jié)婚了，B4跟C4結(jié)婚了，那B5怎么辦呢？女人們都嫁了，剩下一個(gè)男人怎么辦？總不能送個(gè)充氣娃娃強(qiáng)制脫貧吧？——沒(méi)辦法，返回錯(cuò)誤值補(bǔ)位吧。

因此它的計(jì)算結(jié)果也是4個(gè)元素構(gòu)成一個(gè)內(nèi)存數(shù)組，只不過(guò)最后一個(gè)元素是錯(cuò)誤值。

{12;70;30;#N/A}

同樣的道理，公式：

=SUMPRODUCT(B2:B4*C2:C5)

B2和C2結(jié)婚，B3和C3結(jié)婚，B4和C4結(jié)婚。剩下一個(gè)女孩C5，但沒(méi)有男孩了，怎么辦呢？沒(méi)辦法，男女平等，也返回錯(cuò)誤值補(bǔ)位吧。

{12;70;30;#N/A}

……

總結(jié)一下。同方向一維數(shù)組之間的運(yùn)算，必須具有相同的元素?cái)?shù)量，否則結(jié)果中會(huì)產(chǎn)生錯(cuò)誤值進(jìn)行補(bǔ)位，它的運(yùn)算結(jié)果依然是同向的一維數(shù)組。

……

另外一種情況是不同向的兩個(gè)一維數(shù)值之間的運(yùn)算，也就是垂直數(shù)組和水平數(shù)組之間的運(yùn)算，這種情況男女關(guān)系比較復(fù)雜，身經(jīng)百戰(zhàn)的居委會(huì)大媽看了都得哭。

我舉個(gè)例子。

如上圖所示的數(shù)據(jù)。B6單元格輸入公式：

=SUMPRODUCT(A2:A4*B1:C1)

A2:A4是3個(gè)元素構(gòu)成的垂直數(shù)組，B1:C1是2個(gè)元素構(gòu)成的水平數(shù)組，它倆之間做乘法運(yùn)算，結(jié)果返回了一個(gè)3行2列的二維內(nèi)存數(shù)組：

{4,5;8,10;12,15}

它的運(yùn)算過(guò)程是這樣的。垂直數(shù)組中的每個(gè)元素分別和水平數(shù)組中的每個(gè)元素作運(yùn)算，如果把這比作男女關(guān)系——委實(shí)有點(diǎn)混亂，所以還是先別打這個(gè)比方了。

按照有序原則，首先運(yùn)算的是A2。

A2先和B1運(yùn)算，也就是A2*B1，然后再和C1做運(yùn)算，也就是A2*C1。

然后輪到A3。

A3先和B1運(yùn)算，也就是A3*B1，然后再和C1做運(yùn)算，也就是A3*C1。

最后輪到A4。

A4先和B1運(yùn)算，也就是A4*B1，然后再和C1做運(yùn)算，也就是A4*C1。

……

有朋友說(shuō)，這不是一夫多妻制嗎？同志，我說(shuō)你是不是對(duì)一夫多妻有啥誤解？你的意思是A2娶了兩個(gè)老婆，B1和C1；新婚第2天，A2出門遇見(jiàn)了A3，不聊不知道一聊才知道，原來(lái)A3昨天也新婚了，也娶了兩個(gè)老婆，這倆老婆也是B1和C1，你說(shuō)這尷尬不尷尬？

……

總結(jié)一下，兩個(gè)不同方向的一維數(shù)組，也就是X行垂直數(shù)組和Y列水平數(shù)組進(jìn)行運(yùn)算，其運(yùn)算方式是垂直數(shù)組中每一個(gè)元素分別與水平數(shù)組的每一個(gè)元素一一運(yùn)算，返回X行Y列的二維數(shù)組。

……

廣告時(shí)間，閉上眼睛，休息一下。

……

第3種情況是一維數(shù)組和二維數(shù)組之間的運(yùn)算。

再說(shuō)一下什么是二維數(shù)組，由多行多列元素構(gòu)成的數(shù)組是二維數(shù)組，比如單元格區(qū)域B2:D4是一個(gè)3行3列的二維數(shù)組。

一維數(shù)組和二維數(shù)組之間的運(yùn)算是什么情況呢？

還是舉個(gè)例子。

如上圖所示數(shù)據(jù)為例，A1:B4是不同次數(shù)考試成績(jī)的加權(quán)系數(shù)，D1:G4是該班成績(jī)明細(xì)，如果需要計(jì)算所有人考試成績(jī)加權(quán)系數(shù)后的總分，可以使用以下公式。

=SUMPRODUCT(B2:B4*E2:F4)

B2:B4是一維垂直數(shù)組，E2:F4是3行2列的二維數(shù)組，兩者之間做乘法運(yùn)算。

根據(jù)有序原則，首先運(yùn)算的是B2。

B2先和E2運(yùn)算，B2*E2，然后再和F2運(yùn)算，B2*F2。

B3先和E3運(yùn)算，B3*E3，然后再和F3運(yùn)算，B3*F3。

B4先和E4運(yùn)算，B4*E4，然后再和F4運(yùn)算，B4*F4。

最后SUMPRODUCT函數(shù)執(zhí)行匯總求和。

這就是典型的一夫多妻制了，當(dāng)然，也可以說(shuō)是一妻多夫制。按照先來(lái)后到的順序，B2娶了兩個(gè)老婆E2和F2、B3也娶了兩個(gè)老婆E3和F3……

有些男同胞又蠢蠢欲動(dòng)，以為這是盛世復(fù)興的景象……那可就又未必了，什么是先來(lái)后到了解一下？

比如公式：

=SUMPRODUCT(B2:B5*E2:F4)

B2:B5是4行元素的垂直數(shù)組，E2:G4是三行兩列元素的二維數(shù)組。前者比后者多了一行；當(dāng)B2娶走了E2和F2，B3娶走了E3和F3，B4娶走了E4和F4……剩下一個(gè)B5，沒(méi)有老婆可娶了，怎么辦呢？——老辦法，返回錯(cuò)誤值補(bǔ)位吧。

{48,83;27.6,19.2;10.4,18.2;#N/A,#N/A}

錯(cuò)誤值是無(wú)法統(tǒng)計(jì)求和的，因此這條SUMPRODUCT函數(shù)最后會(huì)返回了錯(cuò)誤值。

總結(jié)一下，一維數(shù)組和二維數(shù)組做運(yùn)算的過(guò)程是一維數(shù)組的每個(gè)元素和同方向二維數(shù)組的每個(gè)元素一一對(duì)應(yīng)運(yùn)算，最后結(jié)果返回一個(gè)二維數(shù)組。如果兩個(gè)數(shù)組相同方向的元素?cái)?shù)量不一致，會(huì)產(chǎn)生錯(cuò)誤值補(bǔ)位。

……

第4種情況是二維數(shù)組和二維數(shù)組之間的運(yùn)算。

二維數(shù)組相互運(yùn)算，要求兩者具有完全相同的尺寸，也就是行數(shù)和列數(shù)都要相同。運(yùn)算的過(guò)程是將每個(gè)相同位置的元素兩兩對(duì)應(yīng)，返回一個(gè)與它們尺寸一致的二維數(shù)組結(jié)果。

——如果兩個(gè)數(shù)組的尺寸大小不一樣呢？當(dāng)然還是老辦法，以錯(cuò)誤值進(jìn)行補(bǔ)位。

舉個(gè)例子。

如上圖所示，A1:D4是成績(jī)表；需要匯總大于等于60分的成績(jī)之和。

公式如下。

=SUMPRODUCT((B2:D4>=60)*B2:D4)

公式首先運(yùn)算B2:D4>=60部分，B2:D4是3行3列的二維數(shù)組，60是單值，因此這是二維數(shù)組和單值做比較運(yùn)算，數(shù)組的內(nèi)每個(gè)元素分別和60分比大小，大于等于60返回TRUE，否則返回FALSE，結(jié)果返回一個(gè)3行3列的由邏輯值構(gòu)成的二維內(nèi)存數(shù)組。

{FALSE,FALSE,TRUE;FALSE,TRUE,TRUE;TRUE,TRUE,TRUE}

將該數(shù)組映射到單元格中，如下圖所示。

公式繼續(xù)運(yùn)算，將這個(gè)二維數(shù)組和B2:D4做乘法運(yùn)算，這就屬于兩個(gè)二維數(shù)組之間的運(yùn)算了。

按照有序原則，系統(tǒng)會(huì)將兩個(gè)數(shù)組相同位置的元素一一運(yùn)算，是的，又回到一夫一妻制。A數(shù)組的第1個(gè)元素和B數(shù)組的第1個(gè)元素結(jié)婚，A數(shù)組的第2個(gè)元素和B數(shù)組的第2個(gè)元素結(jié)婚……直至兩個(gè)數(shù)組的元素用完；如果兩個(gè)數(shù)組元素不一樣多，照例用錯(cuò)誤值補(bǔ)位。

TRUE在數(shù)學(xué)運(yùn)算中視為1，F(xiàn)ALSE視為0，因此這一步返回內(nèi)存數(shù)組如下。

{0,0,91;0,74,89;65,80,60}

最后SUMPRODUCT執(zhí)行求和運(yùn)算，返回結(jié)果459.

……

第5種情況是多維數(shù)組的運(yùn)算。這個(gè)一般人一般時(shí)候也用不上，咱們還是放到引用和多維引用篇里再講。

……

話筒，做個(gè)全面總結(jié)：

數(shù)組和單值做運(yùn)算，是后宮和皇帝的關(guān)系，人人都得給皇帝生孩子；兩個(gè)相同尺寸的數(shù)組做運(yùn)算，會(huì)堅(jiān)持一夫一妻制，比如水平數(shù)組和水平數(shù)組、二維數(shù)組和二維數(shù)組等；一維數(shù)組和二維數(shù)組作運(yùn)算，那就是一夫多妻制（或者說(shuō)一妻多夫制）；而水平數(shù)組和垂直數(shù)組作運(yùn)算，徹底毀三觀，它奉行極端的多夫多妻制……

什么是數(shù)組公式

什么是數(shù)組公式？這個(gè)問(wèn)題很有意思，非常有意思，在ExcelHome論壇，它引發(fā)了一次又一次口水戰(zhàn)。

打個(gè)廣告，稍后回來(lái)，嘿嘿嘿。

話說(shuō)先前咱們聊了什么是數(shù)組運(yùn)算；有的函數(shù)天生就默認(rèn)執(zhí)行數(shù)組運(yùn)算，比如SUMPRODUCT、LOOKUP等；但絕大部分函數(shù)并不默認(rèn)執(zhí)行數(shù)組運(yùn)算，比如SUM，但有時(shí)候我們又需要它們執(zhí)行數(shù)組運(yùn)算，怎么呢？——可以強(qiáng)制它們執(zhí)行。

舉個(gè)例子。

如上圖所示的數(shù)據(jù)，需要統(tǒng)計(jì)人頭銷售總金額，可以使用公式：

=SUMPRODUCT(B2:B5*C2:C5)

也可以使用數(shù)組公式：

{=SUM(B2:B5*C2:C5)}

上面這個(gè)公式前后的大括號(hào)不是手工輸入的，而是在公式編輯結(jié)束時(shí)，同時(shí)按下<Ctrl+Shift+Enter>組合鍵后系統(tǒng)自動(dòng)產(chǎn)生的。

<Ctrl+Shift+Enter>也被稱為數(shù)組三鍵。它是數(shù)組運(yùn)算的啟動(dòng)鍵，等于告訴系統(tǒng)，老子是數(shù)組公式，不是普通函數(shù)，你丫的給我執(zhí)行多項(xiàng)運(yùn)算。

如果不按數(shù)組三鍵，而是直接輸入普通公式：

=SUM(B2:B5*C2:C5)

結(jié)果會(huì)怎么樣呢？

SUM函數(shù)只會(huì)按照正常模式運(yùn)算，也就是只運(yùn)算每個(gè)數(shù)組的首個(gè)元素，返回B2*C2的結(jié)果。

這就是數(shù)組三鍵的意義。

一切看起來(lái)都很正常……

然后口水戰(zhàn)就來(lái)了。

……

什么是數(shù)組公式？按照正常的思維邏輯，執(zhí)行了數(shù)組運(yùn)算的就是數(shù)組公式，對(duì)不對(duì)？

但微軟公司說(shuō)，不不不，只執(zhí)行數(shù)組運(yùn)算還不能算數(shù)組公式，做人得有儀式感，做函數(shù)也是一樣的，什么是數(shù)組公式？執(zhí)行了數(shù)組運(yùn)算，同時(shí)公式自身還得包括在大括號(hào)中的才算。

微軟這么說(shuō)，也有一點(diǎn)道理。就像前面所說(shuō)，雖然有的函數(shù)天生就默認(rèn)執(zhí)行數(shù)組運(yùn)算，但絕大部分函數(shù)確實(shí)沒(méi)有這個(gè)特性，它需要數(shù)組三鍵才能打開(kāi)數(shù)組運(yùn)算的開(kāi)關(guān)。

于是問(wèn)題就來(lái)了。比如說(shuō)，下面這個(gè)公式……

=SUMPRODUCT(B2:B5*C2:C5)

它默認(rèn)執(zhí)行了數(shù)組運(yùn)算，它是不是數(shù)組公式？微軟說(shuō)不是，因?yàn)樗鼪](méi)有包含在一對(duì)大括號(hào)中。

那好，我們使用數(shù)組三鍵給它加上大括號(hào)。

{=SUMPRODUCT(B2:B5*C2:C5)}

這樣算數(shù)組公式了嗎？微軟說(shuō)，是的，這就符合我們制定的數(shù)組公式的概念標(biāo)準(zhǔn)了。

但這個(gè)公式和上面的公式兩者的運(yùn)算過(guò)程有什么區(qū)別嗎？沒(méi)有，沒(méi)有任何區(qū)別…

于是口水就飛起來(lái)了。

甲：我軟，你說(shuō)你這是不是有病？
乙：我沒(méi)病，我頂多有點(diǎn)儀式感。
甲：你這是典型的形式主義。
乙：請(qǐng)不要將儀式感和形式主義混為一談。
甲：你就是有病。
乙：臥槽，產(chǎn)品是我家的，我說(shuō)了算，我的地盤我做主，懂不？
甲：你霸權(quán)主義。
乙：嗯？
甲：你虛偽
乙：呵呵
甲：你心虛了。
乙：鴿吻！
甲：我去，你流氓，居然非禮我！
乙：你語(yǔ)文是體育老師教的？連拼音都不會(huì)嗎？

……

吵到后來(lái)連王源都看不下去了，于是唱了一首歌，說(shuō)世界上沒(méi)有真正的感同身受。

什么是區(qū)域數(shù)組公式

咱們前面講過(guò)，數(shù)組公式返回的是一組元素；但是Excel一個(gè)單元格只能顯示數(shù)組元素中的一個(gè)結(jié)果（默認(rèn)為數(shù)組中的首個(gè)元素）。

比如，我們?cè)贒2單元格輸入數(shù)組公式{=B2:B5*C2:C5}，盡管該數(shù)組公式返回了多個(gè)結(jié)果，但D2單元格只顯示了B2*C2的值。

如果需要顯示數(shù)組公式的全部元素呢？——可以使用區(qū)域數(shù)組公式。

那么什么是區(qū)域數(shù)組公式？

在一個(gè)單元格中輸入的公式被稱為數(shù)組公式，在多個(gè)單元格中輸入同一數(shù)組公式就被稱為多單元格數(shù)組公式，也就是區(qū)域數(shù)組公式。區(qū)域數(shù)組公式可以有序返回結(jié)果數(shù)組中的每個(gè)元素。

舉個(gè)簡(jiǎn)單的例子（以后見(jiàn)面請(qǐng)尊稱我舉栗子大力星光上士）

如上圖所示的表格，選中D2:D5單元格區(qū)域，在編輯欄編寫(xiě)公式=B2:B5*C2:C5，然后按數(shù)組三鍵結(jié)束公式輸入，也就在D2:D5區(qū)域內(nèi)輸入了同一條數(shù)組公式，這就是區(qū)域數(shù)組公式。

該公式返回一個(gè)內(nèi)存數(shù)組{12;70;30;15}，系統(tǒng)會(huì)將數(shù)組的每個(gè)元素依次顯示在D2:D5區(qū)域中。

需要說(shuō)明的是，使用多單元格數(shù)組公式時(shí)，所選擇的單元格個(gè)數(shù)必須與公式最終返回的數(shù)組元素個(gè)數(shù)相同，如果所選區(qū)域單元格的個(gè)數(shù)大于公式最終返回的數(shù)組元素個(gè)數(shù)，多出部分將顯示為錯(cuò)誤值。老規(guī)矩，人頭不夠，錯(cuò)誤值來(lái)湊。

比如選中D2:D7輸入數(shù)組公式=B2:B5*C2:C5，D2:D7有6個(gè)房間，返回的內(nèi)存數(shù)組有4個(gè)元素，多出的房間顯示為錯(cuò)誤值。

但如果所選區(qū)域單元格的個(gè)數(shù)小于公式最終返回的數(shù)組元素個(gè)數(shù)，則結(jié)果會(huì)顯示不完整，畢竟規(guī)則是先來(lái)后到，一人一個(gè)單元格。

除此之外，區(qū)域數(shù)組公式還有一個(gè)特點(diǎn)，它們作為一個(gè)整體，系統(tǒng)不允許單獨(dú)更改其中一個(gè)單元格的公式。

如果需要修改或刪除，必須整體處理。

我嚴(yán)重懷疑區(qū)域數(shù)組公式不但沒(méi)聽(tīng)說(shuō)過(guò)槍打出頭鳥(niǎo)這句諺語(yǔ)，而且從小就是唱著團(tuán)結(jié)就是力量一起長(zhǎng)大的。

比如我們選中A1:D1區(qū)域，編寫(xiě)以下公式，并按數(shù)組三鍵結(jié)束。

={'ID','姓名','地址','電話'}

之后A:D列的數(shù)據(jù)就不能單獨(dú)刪除了，否則系統(tǒng)會(huì)彈出警告信息。

嘿！這是不是也是一種保護(hù)數(shù)據(jù)結(jié)構(gòu)的特別手段呢？

那么有沒(méi)有什么快捷方法選取當(dāng)前全部數(shù)組公式呢？

可以選擇任意一個(gè)存在區(qū)域數(shù)據(jù)公式的單元格，然后按<Ctrl+G>快捷鍵調(diào)出定位對(duì)話框，依次單擊【定位條件】→【當(dāng)前數(shù)組】功能，如下圖所示。

魯迅先生說(shuō)，能夠一口氣看到這里的，都是敢于面對(duì)慘淡函數(shù)人生的真正勇士~今天，不，今夜！你，勇士了嗎？

打個(gè)響指，就和大家聊到這吧~想到什么，在知識(shí)星球內(nèi)再補(bǔ)充。

文件下載百度網(wǎng)盤..▼

https://pan.baidu.com/s/1S1qsQnv_PMA7ZdTty_KMjQ