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