Excel動態陣列公式介紹(Dynamic Array Formulas)

Excel動態陣列公式的出現可以說是Excel史上最突破性的發展之一，它簡化及加速了Excel的公式計算引擎，並將既有公式特性：輸入1個儲存格公式僅可輸出顯示於1個儲存格中，改變為輸入1個儲存格公式可以輸出顯示到公式資料範圍涵蓋的所有儲存格中，講地誇張一點，輸入動態陣列公式於幾個儲存格中，就可以製作出一整個工作表的資料。動態陣列公式對於使用者來說就像是手中握有超能力般，用過一次就回不去了。

本篇文章將說明Excel動態陣列公式與陣列公式的差異，及介紹動態陣列的使用方式及其應用。

(新)動態陣列公式 vs (舊)陣列公式 Spilling行為及#SPILL!異常提示 如何參照動態陣列 動態陣列公式應用範例 總結與補充 (動態陣列函數)

文章開始前，建議對於儲存格參照還不了解的讀者先參考閱讀此篇文章：Excel儲存格參照的絕對與相對關係。

Microsoft公司早在2018年9月就已於Microsoft 365版本的Excel中推出了動態陣列公式(Dynamic Array Formulas)，近期亦將此功能加入到Excel 2021版本中。
PS Excel 2019以前的版本無法使用。

(新)動態陣列公式 vs (舊)陣列公式

動態陣列公式是什麼呢？先請大家看看以下動圖，小編於儲存格C1輸入公式=A1:A10並按下Enter後，成功地將儲存格A1到A10的數據回傳到儲存格C1到C10。

以上公式=A1:A10就是屬於動態陣列公式(Dynamic Array Formulas)，動態陣列公式簡單來說就是於1個儲存格中寫公式去呼叫(參照)陣列或進行陣列的計算；而於此範例中利用動態陣列公式得到的陣列(C1:C10)就稱做動態陣列(Dynamic Array)；若選取此動態陣列範圍中任一儲存格，此陣列範圍周界會有深藍色的框線顯現出來；另外，此動態陣列只有起始儲存格有公式，要刪除動態陣列只要將起始儲存格公式刪除即可。

動態陣列公式對於新的Excel使用者來說可能會覺得：

「喔，還不錯的功能阿。」

但對於使用Excel數年甚至幾十年的用戶來說絕對會說：

「哇，傑克，這真是太神奇了！感覺像有了超能力般！」

這就是動態陣列公式的魅力所在：簡單、快速、自動化。

那讓我們來看看如果在舊版的Excel中輸入此公式會怎麼樣。(如下動圖)

上圖為於Excel 2016版中進行錄製，各位可以看到Excel只有回傳A1的數值到C1儲存格中，沒有辦法一次回傳A1:A10的陣列值到C1:C10儲存格中(Excel 2019版本前都是一樣的情形)。

那要如何在舊版的Excel中使用陣列公式的功能呢？其實也有辦法，Excel進階使用者應該都知道，那就是使用鍵盤Ctrl+Shift+Enter組合鍵來製作陣列公式，操作方式為：先選擇欲回傳陣列大小(列*欄)的所有儲存格後(例如C1:C10)，於公式欄輸入陣列公式(例如=A1:A10)，然後按下Ctrl+Shift+Enter鍵即可完成製作出陣列公式。(如下動圖)

各位可以看到在按下Ctrl+Shift+Enter後，儲存格C1到C10的公式都變成{=A1:A10}，此括號”{…}”即為陣列公式的意思。

使用陣列公式的優點在於：
(1)可統一、簡化儲存格公式(例如此範例中不需要於儲存格C1寫公式=A1、C2寫公式=A2，每個儲存格公式都不一樣)。
(2)公式較不會被誤刪除，因用戶無法刪除此陣列中任一儲存格的公式，要刪除陣列需先全選陣列範圍後，再按Delete刪除。(如下動圖)

針對陣列公式Ctrl+Shift+Enter的原理及應用小編將另寫文章說明，這部分較Old School，小編也還在重新研讀中，有興趣的朋友可以參考以下書籍：Ctrl+Shift+Enter/ Mastering Excel Array Formulas。

講了這麼多陣列公式的好，那動態陣列公式到底比陣列公式好在什麼地方呢，以下簡單介紹：

1. 動態陣列公式基本上幾乎可以完全取代陣列公式，或說取代Ctrl+Shift+Enter的用法。
2. 動態陣列公式只需要在一個儲存格輸入公式即可，不用先選取欲回傳陣列大小(列*欄)的儲存格，使用上較方便，及簡化、加速了Exce的l公式計算引擎。
3. 若欲回傳陣列大小(列*欄)有改變，動態陣列公式會自動調整動態陣列大小，而陣列公式則需重新選取相應大小的儲存格範圍後，再重新設定陣列公式。

另因本範例=A1:A10太簡單，下圖再舉一個複雜一點的動態陣列公式應用給大家參考：
(儲存格D1=(A1:A10+B1:B10)*C1&”台幣”)

Spilling行為及#SPILL!異常提示

接著來介紹一下什麼是Spilling行為，Spilling中文為溢出、灑出，Spilling行為是指：除了回傳陣列的第一個值到輸入公式的儲存格之外，也回傳陣列的其他所有值(溢出)到相鄰的儲存格中。

如先前的範例，在C1儲存格輸入公式=A1:A10，它除了回傳A1的值到C1外，也將此陣列其他數值依序回傳到C2:C10儲存格中，打破了以往輸入1個儲存格公式僅能輸出顯示於1個儲存格中的概念。

另外，若在Spilling行為範圍中的任一儲存格內輸入數值，那會阻礙到Spilling行為，Excel會出現#SPILL!的異常提示，要除錯只要將阻礙Spilling行為的儲存格數值刪除即可。(如下動圖)

PS 當#SPILL!異常提示發生時，若點選輸入公式的儲存格(即顯示#SPILL!的儲存格)，Excel會將此陣列的範圍以深藍色虛線標示出來。

如何參照動態陣列

動態陣列公式除了將舊有的陣列公式簡化外，還有一個優點為易於參照，以進行進一步的計算或其他用途。

參照動態陣列的方法為輸入公式=動態陣列起始的儲存格+#符號(本範例為=C1#)，或以滑鼠全選動態陣列範圍也可以看到公式自動變成=動態陣列起始的儲存格+#符號。(如下動圖)
PS 儲存格C1到C10為先前範例的動態陣列。

動態陣列公式應用範例

1.動態陣列公式呼叫(參照)Excel資料表(Table)：
動態陣列公式除了可以選取陣列外，也可以選取Excel資料表。此範例中的資料表為NBA勇士隊的球員資料(命名Warriors)，若於儲存格A17輸入公式=Warriors，即可回傳一整個資料表的資料到A17:H30儲存格中。另可輸入公式=Warriors[[PLAYER]:[TEAM]]，只回傳資料表的前兩個欄位(PLAYER及TEAM)。(如下影片) <meta itemprop="url" content="https://fanlovexcel.com/wp-content/uploads/2022/08/20220824_V1.mp4"> 2.圖表資料來源序列自動化設定：
動態陣列公式也很適合用在作圖上面，圖表要做得好、做得自動化，資料來源序列自動化設定是很重要的。例如要做一個最近30天的銷售量趨勢圖，可以使用FILTER函數來自動產生最近30天的日期序列及銷售量序列，公式分別為：
日期：=FILTER(Sales[日期],(Sales[日期]<E1)*(Sales[日期]>=E1-30))
銷售量：=FILTER(Sales[銷售量],(Sales[日期]<E1)*(Sales[日期]>=E1-30))
其中E1儲存格為今天的日期，或可手動調整日期，趨勢圖會自動更新到此日期下30天內的資料趨勢。(如下影片) <meta itemprop="url" content="https://fanlovexcel.com/wp-content/uploads/2022/08/20220824_V2.mp4">

總結及補充 (動態陣列函數)

總結一下，動態陣列公式與陣列公式的主要差別為動態陣列公式只需在第一個儲存格輸入公式，而陣列公式則需先選取欲回傳陣列大小(列*欄)的所有儲存格後，於公式欄輸入要回傳的資料範圍，再按下Ctrl+Shift+Enter製作出陣列公式。另若欲回傳陣列大小(列*欄)有改變，動態陣列公式會隨著輸出陣列大小(列*欄)的改變自動調整動態陣列大小(列*欄)，而陣列公式則需重新選取相應大小的儲存格範圍後，再設定陣列公式。

動態陣列公式會有Spilling行為(溢出行為)，Spilling行為的意思是僅輸入公式在一個儲存格，輸出的資料範圍大小(列*欄)若大於1個儲存格，那會自動在與其相鄰的儲存格中顯示數據，資料溢流出來的意思。

要參照動態陣列，可輸入公式=動態陣列起始的儲存格+#，例如=C1#。

另外，本篇文章僅介紹了動態陣列公式的概念，真正改變Excel操作行為的是動態陣列函數(Dynamic Array Functions)，動態陣列函數包含(1)在動態陣列公式功能出現後，Microsoft開始陸續開發出新的動態陣列函數，如：
XLOOKUP (請先參考此篇)、
FILTER (請先參考此篇)、
TEXTJOIN (TEXTJOIN函數介紹，將陣列濃縮為字串)、
SORT (請先參考此篇)、
LET (LET函數介紹，讓大家都懂你)、
SEQUENCE (數列功能及SEQUENCE函數介紹)、
UNIQUE、
RANDARRAY
等等許多功能強大的函數；及(2)除了新的動態陣列函數外，幾乎所有舊的Excel函數(如SUM、VLOOKUP、INDEX等)都升級成了動態陣列函數，多了許多有趣、實用的應用，未來小編將陸續進行介紹。