有人說數組是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星球

圖文作者：看見星光

更多精彩资讯请关注tft每日頭條，我们将持续为您更新最新资讯!