HI~我是星光。今晚給大家詳細(xì)聊下Excel函數(shù)數(shù)組，篇幅較長，建議沐浴更衣，先馬后看。
有人說數(shù)組是Excel函數(shù)的精華，也有人說數(shù)組是Excel函數(shù)的糟粕，男說男有理女說女有愛，各有各的理由——所以王源說世間沒有真正的感同身受。但不管怎么說吧，數(shù)組作為Excel函數(shù)進階后的知識，是相當(dāng)部分Excel使用者都繞不過去的一條……河。
什么是數(shù)組
什么是數(shù)組運算
既然有數(shù)組，也就有數(shù)組和數(shù)組之間的運算，這就好比既然有男人，必然就有……你猜？——數(shù)組運算又被稱為多項運算，是指同時對一組或多組數(shù)組內(nèi)的元素執(zhí)行運算。既然是運算，必然有規(guī)則，這就好比既然有戰(zhàn)爭，必然就有……你再猜？——數(shù)組運算規(guī)則總結(jié)起來有以下幾種情況。
……
第1種情況是一維數(shù)組和單值之間進行運算。
前面咱們講過，一維數(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ù)組中分號代表行（分號有兩層，只有行才分層，所以它代表行），逗號代表列。
數(shù)組和單值之間執(zhí)行多項運算，必然返回同尺寸的數(shù)組。
舉個例子，如下所示的數(shù)組公式，單行數(shù)組{1,2,3,4}乘以2。
={1,2,3,4}*2
運算過程是數(shù)組中的每個元素都乘以2。1*2、2*2、3*3、4*2，結(jié)果為內(nèi)存數(shù)組{2,4,6,8}。
再舉一個實戰(zhàn)的小案例。
如上圖所示，由于B列的工資為文本值，直接SUM函數(shù)求和結(jié)果會返回0。B6單元格改用公式如下。
=SUMPRODUCT(B2:B5*1)
B2:B5*1，B2:B5是一個垂直數(shù)組，運算過程是B2:B5中的每一個元素均乘以1，通過數(shù)學(xué)運算將文本型數(shù)值轉(zhuǎn)換為純數(shù)值。此時生成一個內(nèi)存數(shù)組{900;100;9999;99999}，SUMPRODUCT再執(zhí)行求和運算返回正確結(jié)果。
如果把數(shù)組和單值運算比作男女關(guān)系，單值就像皇帝，數(shù)組是它的后宮，數(shù)組內(nèi)每一個人都要給皇帝生娃娃……所以說皇帝這個職業(yè)確實很——辛苦吶！
……
第2種情況是一維數(shù)組和一維數(shù)組之間的運算。
由于一維數(shù)組有兩種形式，這種運算就又產(chǎn)生了兩種情況。
一種是同方向一維數(shù)組之間的運算。比如垂直數(shù)組和垂直數(shù)組或者水平數(shù)組和水平數(shù)值間的運算。
這種情況的數(shù)組運算是比較單純的男女關(guān)系，兩個數(shù)組內(nèi)的每個元素按照先后順序、一夫一妻制結(jié)婚。
舉個例子。
以上圖所示數(shù)據(jù)為例。計算商品售出總金額。公式如下：
=SUMPRODUCT(B2:B5*C2:C5)
B2:B5是垂直數(shù)組，C2:C5也是垂直數(shù)組，其運算過程中是B2*C2、B3*C3、B4*C4、B5*C5……你看，是不是按照先后順序、一夫一妻制的規(guī)則進行運算的？它的計算結(jié)果是4個元素的垂直數(shù)組。
{12;70;30;15}
這規(guī)則似乎看起來讓單身汪感到特別美好，但其實并不盡然。男多女少或者女多男少的情況了解一下？
比如公式：
=SUMPRODUCT(B2:B5*C2:C4)
B2:B5是4個元素構(gòu)成的垂直數(shù)組，C2:C4是3個元素構(gòu)成的垂直數(shù)組；如果前者是男人，后者是女人，那就屬于男多女少了。
這個時候B2跟C2結(jié)婚了，B3跟C3結(jié)婚了，B4跟C4結(jié)婚了，那B5怎么辦呢？女人們都嫁了，剩下一個男人怎么辦？總不能送個充氣娃娃強制脫貧吧？——沒辦法，返回錯誤值補位吧。
因此它的計算結(jié)果也是4個元素構(gòu)成一個內(nèi)存數(shù)組，只不過最后一個元素是錯誤值。
{12;70;30;#N/A}
同樣的道理，公式：
=SUMPRODUCT(B2:B4*C2:C5)
B2和C2結(jié)婚，B3和C3結(jié)婚，B4和C4結(jié)婚。剩下一個女孩C5，但沒有男孩了，怎么辦呢？沒辦法，男女平等，也返回錯誤值補位吧。
{12;70;30;#N/A}
……
總結(jié)一下。同方向一維數(shù)組之間的運算，必須具有相同的元素數(shù)量，否則結(jié)果中會產(chǎn)生錯誤值進行補位，它的運算結(jié)果依然是同向的一維數(shù)組。
……
另外一種情況是不同向的兩個一維數(shù)值之間的運算，也就是垂直數(shù)組和水平數(shù)組之間的運算，這種情況男女關(guān)系比較復(fù)雜，身經(jīng)百戰(zhàn)的居委會大媽看了都得哭。
我舉個例子。
如上圖所示的數(shù)據(jù)。B6單元格輸入公式：
=SUMPRODUCT(A2:A4*B1:C1)
A2:A4是3個元素構(gòu)成的垂直數(shù)組，B1:C1是2個元素構(gòu)成的水平數(shù)組，它倆之間做乘法運算，結(jié)果返回了一個3行2列的二維內(nèi)存數(shù)組：
{4,5;8,10;12,15}
它的運算過程是這樣的。垂直數(shù)組中的每個元素分別和水平數(shù)組中的每個元素作運算，如果把這比作男女關(guān)系——委實有點混亂，所以還是先別打這個比方了。
按照有序原則，首先運算的是A2。
A2先和B1運算，也就是A2*B1，然后再和C1做運算，也就是A2*C1。
然后輪到A3。
A3先和B1運算，也就是A3*B1，然后再和C1做運算，也就是A3*C1。
最后輪到A4。
A4先和B1運算，也就是A4*B1，然后再和C1做運算，也就是A4*C1。
……
有朋友說，這不是一夫多妻制嗎？同志，我說你是不是對一夫多妻有啥誤解？你的意思是A2娶了兩個老婆，B1和C1；新婚第2天，A2出門遇見了A3，不聊不知道一聊才知道，原來A3昨天也新婚了，也娶了兩個老婆，這倆老婆也是B1和C1，你說這尷尬不尷尬？
……
總結(jié)一下，兩個不同方向的一維數(shù)組，也就是X行垂直數(shù)組和Y列水平數(shù)組進行運算，其運算方式是垂直數(shù)組中每一個元素分別與水平數(shù)組的每一個元素一一運算，返回X行Y列的二維數(shù)組。
……
廣告時間，閉上眼睛，休息一下。
……
第3種情況是一維數(shù)組和二維數(shù)組之間的運算。
再說一下什么是二維數(shù)組，由多行多列元素構(gòu)成的數(shù)組是二維數(shù)組，比如單元格區(qū)域B2:D4是一個3行3列的二維數(shù)組。
一維數(shù)組和二維數(shù)組之間的運算是什么情況呢？
還是舉個例子。
如上圖所示數(shù)據(jù)為例，A1:B4是不同次數(shù)考試成績的加權(quán)系數(shù)，D1:G4是該班成績明細(xì)，如果需要計算所有人考試成績加權(quán)系數(shù)后的總分，可以使用以下公式。
=SUMPRODUCT(B2:B4*E2:F4)
B2:B4是一維垂直數(shù)組，E2:F4是3行2列的二維數(shù)組，兩者之間做乘法運算。
根據(jù)有序原則，首先運算的是B2。
B2先和E2運算，B2*E2，然后再和F2運算，B2*F2。
B3先和E3運算，B3*E3，然后再和F3運算，B3*F3。
B4先和E4運算，B4*E4，然后再和F4運算，B4*F4。
最后SUMPRODUCT函數(shù)執(zhí)行匯總求和。
這就是典型的一夫多妻制了，當(dāng)然，也可以說是一妻多夫制。按照先來后到的順序，B2娶了兩個老婆E2和F2、B3也娶了兩個老婆E3和F3……
有些男同胞又蠢蠢欲動，以為這是盛世復(fù)興的景象……那可就又未必了，什么是先來后到了解一下？
比如公式：
=SUMPRODUCT(B2:B5*E2:F4)
B2:B5是4行元素的垂直數(shù)組，E2:G4是三行兩列元素的二維數(shù)組。前者比后者多了一行；當(dāng)B2娶走了E2和F2，B3娶走了E3和F3，B4娶走了E4和F4……剩下一個B5，沒有老婆可娶了，怎么辦呢？——老辦法，返回錯誤值補位吧。
{48,83;27.6,19.2;10.4,18.2;#N/A,#N/A}
錯誤值是無法統(tǒng)計求和的，因此這條SUMPRODUCT函數(shù)最后會返回了錯誤值。
總結(jié)一下，一維數(shù)組和二維數(shù)組做運算的過程是一維數(shù)組的每個元素和同方向二維數(shù)組的每個元素一一對應(yīng)運算，最后結(jié)果返回一個二維數(shù)組。如果兩個數(shù)組相同方向的元素數(shù)量不一致，會產(chǎn)生錯誤值補位。
……
第4種情況是二維數(shù)組和二維數(shù)組之間的運算。
二維數(shù)組相互運算，要求兩者具有完全相同的尺寸，也就是行數(shù)和列數(shù)都要相同。運算的過程是將每個相同位置的元素兩兩對應(yīng)，返回一個與它們尺寸一致的二維數(shù)組結(jié)果。
——如果兩個數(shù)組的尺寸大小不一樣呢？當(dāng)然還是老辦法，以錯誤值進行補位。
舉個例子。
如上圖所示，A1:D4是成績表；需要匯總大于等于60分的成績之和。
公式如下。
=SUMPRODUCT((B2:D4>=60)*B2:D4)
公式首先運算B2:D4>=60部分，B2:D4是3行3列的二維數(shù)組，60是單值，因此這是二維數(shù)組和單值做比較運算，數(shù)組的內(nèi)每個元素分別和60分比大小，大于等于60返回TRUE，否則返回FALSE，結(jié)果返回一個3行3列的由邏輯值構(gòu)成的二維內(nèi)存數(shù)組。
{FALSE,FALSE,TRUE;FALSE,TRUE,TRUE;TRUE,TRUE,TRUE}
將該數(shù)組映射到單元格中，如下圖所示。
公式繼續(xù)運算，將這個二維數(shù)組和B2:D4做乘法運算，這就屬于兩個二維數(shù)組之間的運算了。
按照有序原則，系統(tǒng)會將兩個數(shù)組相同位置的元素一一運算，是的，又回到一夫一妻制。A數(shù)組的第1個元素和B數(shù)組的第1個元素結(jié)婚，A數(shù)組的第2個元素和B數(shù)組的第2個元素結(jié)婚……直至兩個數(shù)組的元素用完；如果兩個數(shù)組元素不一樣多，照例用錯誤值補位。
TRUE在數(shù)學(xué)運算中視為1，F(xiàn)ALSE視為0，因此這一步返回內(nèi)存數(shù)組如下。
{0,0,91;0,74,89;65,80,60}
最后SUMPRODUCT執(zhí)行求和運算，返回結(jié)果459.
……
第5種情況是多維數(shù)組的運算。這個一般人一般時候也用不上，咱們還是放到引用和多維引用篇里再講。
……
話筒，做個全面總結(jié)：
數(shù)組和單值做運算，是后宮和皇帝的關(guān)系，人人都得給皇帝生孩子；兩個相同尺寸的數(shù)組做運算，會堅持一夫一妻制，比如水平數(shù)組和水平數(shù)組、二維數(shù)組和二維數(shù)組等；一維數(shù)組和二維數(shù)組作運算，那就是一夫多妻制（或者說一妻多夫制）；而水平數(shù)組和垂直數(shù)組作運算，徹底毀三觀，它奉行極端的多夫多妻制……
什么是數(shù)組公式
什么是數(shù)組公式？這個問題很有意思，非常有意思，在ExcelHome論壇，它引發(fā)了一次又一次口水戰(zhàn)。
打個廣告，稍后回來，嘿嘿嘿。
話說先前咱們聊了什么是數(shù)組運算；有的函數(shù)天生就默認(rèn)執(zhí)行數(shù)組運算，比如SUMPRODUCT、LOOKUP等；但絕大部分函數(shù)并不默認(rèn)執(zhí)行數(shù)組運算，比如SUM，但有時候我們又需要它們執(zhí)行數(shù)組運算，怎么呢？——可以強制它們執(zhí)行。
舉個例子。
如上圖所示的數(shù)據(jù)，需要統(tǒng)計人頭銷售總金額，可以使用公式：
=SUMPRODUCT(B2:B5*C2:C5)
也可以使用數(shù)組公式：
{=SUM(B2:B5*C2:C5)}
上面這個公式前后的大括號不是手工輸入的，而是在公式編輯結(jié)束時，同時按下<Ctrl+Shift+Enter>組合鍵后系統(tǒng)自動產(chǎn)生的。
<Ctrl+Shift+Enter>也被稱為數(shù)組三鍵。它是數(shù)組運算的啟動鍵，等于告訴系統(tǒng)，老子是數(shù)組公式，不是普通函數(shù)，你丫的給我執(zhí)行多項運算。
如果不按數(shù)組三鍵，而是直接輸入普通公式：
=SUM(B2:B5*C2:C5)
結(jié)果會怎么樣呢？
SUM函數(shù)只會按照正常模式運算，也就是只運算每個數(shù)組的首個元素，返回B2*C2的結(jié)果。
這就是數(shù)組三鍵的意義。
一切看起來都很正?！?br>
然后口水戰(zhàn)就來了。
……
什么是數(shù)組公式？按照正常的思維邏輯，執(zhí)行了數(shù)組運算的就是數(shù)組公式，對不對？
但微軟公司說，不不不，只執(zhí)行數(shù)組運算還不能算數(shù)組公式，做人得有儀式感，做函數(shù)也是一樣的，什么是數(shù)組公式？執(zhí)行了數(shù)組運算，同時公式自身還得包括在大括號中的才算。
微軟這么說，也有一點道理。就像前面所說，雖然有的函數(shù)天生就默認(rèn)執(zhí)行數(shù)組運算，但絕大部分函數(shù)確實沒有這個特性，它需要數(shù)組三鍵才能打開數(shù)組運算的開關(guān)。
于是問題就來了。比如說，下面這個公式……
=SUMPRODUCT(B2:B5*C2:C5)
它默認(rèn)執(zhí)行了數(shù)組運算，它是不是數(shù)組公式？微軟說不是，因為它沒有包含在一對大括號中。
那好，我們使用數(shù)組三鍵給它加上大括號。
{=SUMPRODUCT(B2:B5*C2:C5)}
這樣算數(shù)組公式了嗎？微軟說，是的，這就符合我們制定的數(shù)組公式的概念標(biāo)準(zhǔn)了。
但這個公式和上面的公式兩者的運算過程有什么區(qū)別嗎？沒有，沒有任何區(qū)別…
于是口水就飛起來了。
甲：我軟，你說你這是不是有??？
乙：我沒病，我頂多有點儀式感。
甲：你這是典型的形式主義。
乙：請不要將儀式感和形式主義混為一談。
甲：你就是有病。
乙：臥槽，產(chǎn)品是我家的，我說了算，我的地盤我做主，懂不？
甲：你霸權(quán)主義。
乙：嗯？
甲：你虛偽
乙：呵呵
甲：你心虛了。
乙：鴿吻！
甲：我去，你流氓，居然非禮我！
乙：你語文是體育老師教的？連拼音都不會嗎？
……
吵到后來連王源都看不下去了，于是唱了一首歌，說世界上沒有真正的感同身受。
什么是區(qū)域數(shù)組公式
咱們前面講過，數(shù)組公式返回的是一組元素；但是Excel一個單元格只能顯示數(shù)組元素中的一個結(jié)果（默認(rèn)為數(shù)組中的首個元素）。
比如，我們在D2單元格輸入數(shù)組公式{=B2:B5*C2:C5}，盡管該數(shù)組公式返回了多個結(jié)果，但D2單元格只顯示了B2*C2的值。
如果需要顯示數(shù)組公式的全部元素呢？——可以使用區(qū)域數(shù)組公式。
那么什么是區(qū)域數(shù)組公式？
在一個單元格中輸入的公式被稱為數(shù)組公式，在多個單元格中輸入同一數(shù)組公式就被稱為多單元格數(shù)組公式，也就是區(qū)域數(shù)組公式。區(qū)域數(shù)組公式可以有序返回結(jié)果數(shù)組中的每個元素。
舉個簡單的例子（以后見面請尊稱我舉栗子大力星光上士）
如上圖所示的表格，選中D2:D5單元格區(qū)域，在編輯欄編寫公式=B2:B5*C2:C5，然后按數(shù)組三鍵結(jié)束公式輸入，也就在D2:D5區(qū)域內(nèi)輸入了同一條數(shù)組公式，這就是區(qū)域數(shù)組公式。
該公式返回一個內(nèi)存數(shù)組{12;70;30;15}，系統(tǒng)會將數(shù)組的每個元素依次顯示在D2:D5區(qū)域中。
需要說明的是，使用多單元格數(shù)組公式時，所選擇的單元格個數(shù)必須與公式最終返回的數(shù)組元素個數(shù)相同，如果所選區(qū)域單元格的個數(shù)大于公式最終返回的數(shù)組元素個數(shù)，多出部分將顯示為錯誤值。老規(guī)矩，人頭不夠，錯誤值來湊。
比如選中D2:D7輸入數(shù)組公式=B2:B5*C2:C5，D2:D7有6個房間，返回的內(nèi)存數(shù)組有4個元素，多出的房間顯示為錯誤值。
但如果所選區(qū)域單元格的個數(shù)小于公式最終返回的數(shù)組元素個數(shù)，則結(jié)果會顯示不完整，畢竟規(guī)則是先來后到，一人一個單元格。
除此之外，區(qū)域數(shù)組公式還有一個特點，它們作為一個整體，系統(tǒng)不允許單獨更改其中一個單元格的公式。
如果需要修改或刪除，必須整體處理。
我嚴(yán)重懷疑區(qū)域數(shù)組公式不但沒聽說過槍打出頭鳥這句諺語，而且從小就是唱著團結(jié)就是力量一起長大的。
比如我們選中A1:D1區(qū)域，編寫以下公式，并按數(shù)組三鍵結(jié)束。
={'ID','姓名','地址','電話'}
之后A:D列的數(shù)據(jù)就不能單獨刪除了，否則系統(tǒng)會彈出警告信息。
嘿！這是不是也是一種保護數(shù)據(jù)結(jié)構(gòu)的特別手段呢？
那么有沒有什么快捷方法選取當(dāng)前全部數(shù)組公式呢？
可以選擇任意一個存在區(qū)域數(shù)據(jù)公式的單元格，然后按<Ctrl+G>快捷鍵調(diào)出定位對話框，依次單擊【定位條件】→【當(dāng)前數(shù)組】功能，如下圖所示。
魯迅先生說，能夠一口氣看到這里的，都是敢于面對慘淡函數(shù)人生的真正勇士~今天，不，今夜！你，勇士了嗎？
打個響指，就和大家聊到這吧~想到什么，在知識星球內(nèi)再補充。
文件下載百度網(wǎng)盤..▼
https://pan.baidu.com/s/1S1qsQnv_PMA7ZdTty_KMjQ
