【原】動態數組很強大，Excel的“新”功能

今天介紹下Excel的新功能，動態數組。

當然，這個功能并不算新，不過對于大部分使用Excel的朋友，這應該是個新功能。尤其是對于關注本公眾號的粉絲來說，這個功能我們還沒有介紹過。也沒有在任何案例中用過。

這個功能只能在Excel 2019或者Office 365中使用。這也是我一直沒有給大家介紹的最主要的原因。

但是，以動態數組為代表的一大批功能越來越多的出現在了Office 365和Excel 2019中。跟Power Query和Power Pivot不一樣，這些功能都是對每個人的日常工作非常有幫助的，可以說是提高我們工作效率的利器，因此，還是很有必要了解和掌握一下的。

認識動態數組

在Excel中，數組都是被作為“高級”功能看待的！一般，說到數組公式，很多人心里就先有了三分敬畏。確實，一般用到數組公式的地方，那個公式往往比較復雜。而且，輸入的時候還必須使用CTRL+SHIFT+回車三個鍵一起輸入。

下圖就是一個在以前版本的Excel中，使用數組公式的例子：

這里，我們使用了公式：

這就是一個數組公式。

普通的IF公式是這樣的，

意思是判斷B2是否大于4，如果是，則返回B2，否則就返回0。

而公式：

的意思則是，判斷B2:B7中的每個單元格的值是否大于4，如果是，則返回對應單元格的值，否則就返回0。

這個公式會返回一個1列6行的數據結果，所以是個數組公式，必須通過CTRL+SHIFT+ENTER輸入：

這個公式的返回結果區域（D2:D7)是一個整體，如果你想操作修改其中的一個單元格（比如刪除第一個單元格的公式），就會報錯：

這種傳統的數組公式，有兩個不太方便的地方：

1. 公式輸入必須實現確定范圍，比如，我必須首先圈定跟返回數組一樣大小（7行1列）的區域

2. 刪除公式時必須選中整個結果區域一起操作

3. 輸入公式必須使用CTRL+SHIFT+ENTER3鍵

而在新版本的Excel中，動態數組是這樣的：

只要在D2單元格中輸入公式：

按回車（只要按回車就可以，不需要三鍵），公式輸入成功：

注意看公式編輯欄中，公式并沒有{}。

這就是動態數組！！！

不用實現選定結果區域，不用按三鍵輸入。

動態數組詳解

從上面的例子大家已經看到了，只要在一個單元格中輸入公式，Excel就會自動將結果擴展到一個合適大小的區域，這個區域跟公式返回的數組行列數一致。

這種行為，在Excel中稱為“溢出”，英文是SPILLING。

這是“動態”的一個基本含義，Excel自行判斷返回值需要占據多大的區域，然后就將這個區域用公式結果填充。

使用動態數組公式有一個推薦的使用場景，即將源數據（公式引用的參數）放在超級表中，這樣這個區域就是一個可以變化的區域。例如，我們可以將B2:B7轉換為超級表，然后在數組公式中引用超級表：

這個公式的行為跟上面引用普通區域沒什么區別。但是如果超級表的內容發生變化：

這是“動態”的另一重含義。實際上我們利用這個特性可以做出非常強大的應用效果，我會在后續其他文章中為大家介紹一些案例。

如果我們選中動態數組公式結果區域的任意的單元格，就會發現整個動態數組返回的區域被加上了一個藍色邊框：

邊框提示我們這個區域是一個整體。邊框內的任意單元格都不可更改（例如，你不能刪除第2個單元格。如果你試圖刪除，也沒有提示，只是沒有反應而已，邊框內只有左上角的單元格是可以被修改的，這個修改分為兩種情況：

1. 改成另外的數組公式
   結果區域重新變化，并用新的公式結果填充區域

2. 改成非數組公式，例如輸入一個文本“E學會”，或者干脆刪除公式
   結果區域的邊框不見了，對整個區域的鎖定消失了，左上角的單元格變成了一個普通的單元格。

提示，如果你想刪掉這個區域，只要刪除左上角單元格的公式就可以了。

實際上，你選擇不同的單元格，在編輯欄中會發現不同：

幾個注意的問題

這里有幾個需要注意的問題。

比如，如果我們在D2中輸入數組公式，這個公式預計會溢出到D2:D13，但是，現在D13中已經有內容了：

此時，按回車輸入公式后，在D2中會返回錯誤值：

盡管我們建議把動態數組公式的引用參數放在超級表中，但是在超級表中是不能使用數組公式的：

我們在超級表的第二列中，使用數組公式，期望溢出到整個列中，失敗了：

其實，這種情況，根本不用數組公式：

答案是不會的。在動態數組公式這個問題上，Excel會智能的實現自動切換。具體的來說：

• 如果在新版本創建了動態數組公式，保存后，再用舊版本打開時，動態數組公式會變成傳統的數組公式（編輯欄中有大括號）。而這個文件在此用新版本打開后，這些公式又變成了動態數組公式了。

• 如果在舊版本中創建了傳統數組公式（用三鍵輸入），用新版本打開時，仍然是傳統的數組公式。

其他

在新版本的Excel中，微軟還推出了一系列的返回動態數組的函數：

• SORT

• SORTBY

• UNIQUE

• FILTER

• RANDARRAY

• SEQUENCE

這些函數極大的豐富了我們進行數據處理的武器庫。在后續文章中，我會為大家詳細介紹

加入E學會，學習更多Excel函數，數據處理方法和案例。一次加入，永久有效，學習多達20門Excei精品課程