Excel動態陣列，功能和公式

Excel 365的革命性計算引擎更新讓數組公式變得簡單易懂，不再只是高級用戶的專屬技能。本教程講解Excel動態數組的概念，並展示如何利用它們提高工作表效率，簡化設置。

Excel數組公式一直被認為是專家和公式高手的專利。 “可以用數組公式解決”這句話，常常會讓許多用戶立刻反應：“有沒有其他方法？”

動態數組的引入是期待已久且非常受歡迎的改變。由於它們能夠以簡單的方式處理多個值，無需任何技巧和竅門，每個Excel用戶都能理解和輕鬆創建動態數組公式。

- Excel動態數組

• 動態數組可用性
• 動態數組函數
• 動態數組公式示例
• 溢出區域- 一個公式，多個單元格
• 溢出區域引用（# 符號）
• 隱式交集和@字符
• 動態數組的優勢
• 動態數組的局限性
• 動態數組與傳統的CSE數組公式
• 向後兼容性
• Excel動態數組公式無效

Excel動態數組

動態數組是可調整大小的數組，它們會自動計算並將值返回到多個單元格中，而這些值都基於單個單元格中輸入的公式。

三十多年來，Microsoft Excel經歷了許多變化，但有一點始終如一：一個公式，一個單元格。即使使用傳統的數組公式，也需要在每個希望顯示結果的單元格中輸入公式。有了動態數組，這條規則不再適用。現在，任何返回數組值的公式都會自動溢出到相鄰單元格中，無需按Ctrl Shift Enter或執行任何其他操作。換句話說，操作動態數組就像操作單個單元格一樣簡單。

讓我們用一個非常基本的例子來說明這個概念。假設您需要將兩組數字相乘，例如，計算不同的百分比。

在Excel的非動態版本中，下面的公式僅適用於第一個單元格，除非您在多個單元格中輸入它並按Ctrl Shift Enter將其顯式地設為數組公式：

=A3:A5*B2:D2

現在，看看在Excel 365中使用相同的公式會發生什麼。您只需在一個單元格（在本例中為B3）中輸入它，按Enter鍵……整個區域就會立即填充結果：

用單個公式填充多個單元格稱為溢出，填充的單元格區域稱為溢出區域。

需要注意的是，最近的更新不僅僅是處理Excel數組的一種新方法。事實上，這是對整個計算引擎的突破性改變。有了動態數組，Excel函數庫中添加了一系列新函數，現有函數也開始運行得更快、更高效。最終，新的動態數組應該會完全取代使用Ctrl Shift Enter快捷鍵輸入的舊式數組公式。

Excel動態數組可用性

動態數組於2018年在Microsoft Ignite大會上推出，並於2020年1月發布給Office 365訂閱者。目前，它們可在Microsoft 365訂閱和Excel 2021中使用。

以下版本支持動態數組：

• Windows版Excel 365
• Mac版Excel 365
• Excel 2021
• Mac版Excel 2021
• iPad版Excel
• iPhone版Excel
• Android平板電腦版Excel
• Android手機版Excel
• 網頁版Excel

Excel動態數組函數

作為新功能的一部分，Excel 365中引入了6個新函數，這些函數可以原生處理數組並將數據輸出到單元格區域。輸出始終是動態的——當源數據發生任何變化時，結果會自動更新。因此，該組的名稱是——動態數組函數。

這些新函數可以輕鬆應對許多傳統上被認為難以解決的任務。例如，它們可以刪除重複項、提取和計數唯一值、過濾空值、生成隨機整數和小數、按升序或降序排序等等。

下面簡要介紹每個函數的功能以及深入教程的鏈接：

• UNIQUE - 從單元格區域中提取唯一項。
• FILTER - 根據您定義的條件過濾數據。
• SORT - 按指定的列對單元格區域進行排序。
• SORTBY - 按另一個區域或數組對單元格區域進行排序。
• RANDARRAY - 生成一個隨機數數組。
• SEQUENCE - 生成一個序列號列表。
• TEXTSPLIT - 按指定的定界符將字符串拆分為列或/和行。
• TOCOL - 將數組或區域轉換為單列。
• TOROW - 將區域或數組轉換為單行。
• WRAPCOLS - 根據每行指定的值數將行或列轉換為二維數組。
• WRAPROWS - 根據每列指定的值數將行或列重新整形為二維數組。
• TAKE - 從數組的開頭或結尾提取指定數量的連續行或列。
• DROP - 從數組中刪除一定數量的行或列。
• EXPAND - 將數組擴展到指定數量的行和列。
• CHOOSECOLS - 從數組中返回指定的列。
• CHOOSEROWS - 從數組中提取指定的行。
• GROUPBY - 基於一列或多列中的值按行對數據進行分組和聚合。
• PIVOTBY - 按行和列對數據進行分組，並聚合關聯的值。

此外，還有兩個流行的Excel函數的現代替代品，它們並非正式屬於該組，但可以利用動態數組的所有優勢：

• XLOOKUP - 是VLOOKUP、HLOOKUP和LOOKUP功能更強大的後繼者，它可以在列和行中查找並返回多個值。
• XMATCH - 是MATCH函數功能更通用的後繼者，它可以執行垂直和水平查找並返回指定項的相對位置。

Excel動態數組公式

在現代版本的Excel中，動態數組行為已深度集成，並成為所有函數的原生行為，即使這些函數最初並非設計為與數組一起工作。簡而言之，對於任何返回多個值的公式，Excel都會自動創建一個可調整大小的區域，將結果輸出到該區域。由於這種能力，現有函數現在可以發揮神奇的作用！

下面的例子展示了動態數組公式的實際應用，以及動態數組對現有函數的影響。

示例1. 新的動態數組函數

此示例演示了使用Excel動態數組函數可以實現多麼快速簡單的解決方案。

要從列中提取唯一值列表，您通常會使用如下所示的複雜CSE公式。在動態Excel中，您只需要一個基本形式的UNIQUE公式：

=UNIQUE(B2:B10)

您在任何空單元格中輸入公式並按Enter鍵。 Excel會立即提取列表中的所有不同值，並將它們輸出到從您輸入公式的單元格（在本例中為D2）開始的單元格區域中。當源數據發生變化時，結果會自動重新計算和更新。

示例2. 在一個公式中組合多個動態數組函數

如果沒有辦法用一個函數完成任務，可以將幾個函數鏈接在一起！例如，要根據條件過濾數據並按字母順序排列結果，可以將SORT函數包裝在FILTER周圍，如下所示：

=SORT(FILTER(A2:C13, B2:B13=F1, "No results"))

其中A2:C13是源數據，B2:B13是要檢查的值，F1是條件。

示例3. 將新的動態數組函數與現有函數一起使用

由於Excel 365中實現的新計算引擎可以輕鬆地將傳統公式轉換為數組，因此沒有什麼可以阻止您將新舊函數組合在一起。

例如，要計算某個區域中共有多少唯一值，可以將動態數組UNIQUE函數嵌套在舊的COUNTA函數中：

=COUNTA(UNIQUE(B2:B10))

示例4. 現有函數支持動態數組

如果您在較舊版本的Excel（例如Excel 2016或Excel 2019）中向TRIM函數提供單元格區域，它將為第一個單元格返回單個結果：

=TRIM(A2:A6)

在動態Excel中，相同的公式會處理所有單元格並返回多個結果，如下所示：

示例5. 返回多個值的VLOOKUP公式

眾所周知，VLOOKUP函數旨在根據您指定的列索引返回單個值。但是，在Excel 365中，您可以提供一組列號以從幾列返回匹配項：

=VLOOKUP(F1, A2:C6, {1,2,3}, FALSE)

示例6. 簡化的TRANSPOSE公式

在早期版本的Excel中，TRANSPOSE函數的語法不容出錯。要旋轉工作表中的數據，您需要計算原始列和行，選擇相同數量的空單元格但更改方向（在大型工作表中這是一項令人費解的操作！），在選定的區域中鍵入TRANSPOSE公式，然後按Ctrl Shift Enter才能正確完成。呼！

在動態Excel中，您只需在輸出區域的最左單元格中輸入公式並按Enter鍵：

=TRANSPOSE(A1:B6)

搞定！

溢出區域- 一個公式，多個單元格

溢出區域是包含動態數組公式返回的值的單元格區域。

當選擇溢出區域中的任何單元格時，會出現藍色邊框，以顯示其內部的所有內容都是由左上角單元格中的公式計算的。如果您刪除第一個單元格中的公式，所有結果都將消失。

溢出區域是一個非常棒的功能，它使Excel用戶的操作更加輕鬆。以前，使用CSE數組公式時，我們必須猜測要將它們複製到多少個單元格中。現在，您只需在第一個單元格中輸入公式，然後讓Excel處理其餘部分。

注意。如果其他數據阻塞了溢出區域，則會發生#SPILL錯誤。一旦移除阻塞數據，錯誤就會消失。

有關更多信息，請參閱Excel溢出區域。

溢出區域引用（# 符號）

要引用動態數組公式返回的整個溢出區域，請在區域左上角單元格的地址後加上井號或磅符號（#）。

例如，要查找A2中RANDARRAY公式生成的隨機數有多少個，請將溢出區域引用提供給COUNTA函數：

=COUNTA(A2#)

要將溢出區域中的值相加，請使用：

=SUM(A2#)

提示：

• 要快速引用溢出區域，只需使用鼠標選擇藍色框內的所有單元格，Excel就會為您創建溢出引用。
• 與常規區域引用不同，溢出區域引用是動態的，會自動響應區域大小調整。 有關更多詳細信息，請參閱溢出區域運算符。

隱式交集和@字符

在動態數組Excel中，公式語言還有一個重要的變化——引入了@字符，稱為隱式交集運算符。

在Microsoft Excel中，隱式交集是一種公式行為，它將多個值減少為單個值。在舊版Excel中，單元格只能包含單個值，因此這是默認行為，不需要特殊的運算符。

在新版Excel中，所有公式默認都被視為數組公式。如果您不希望在特定公式中使用數組行為，則可以使用隱式交集運算符。換句話說，如果您希望公式只返回一個值，請在函數名稱前加上@，它就會像傳統Excel中的非數組公式一樣運行。

要查看它在實踐中的工作方式，請查看下面的屏幕截圖。

在C2中，有一個動態數組公式，它將結果溢出到許多單元格中：

=UNIQUE(A2:A9)

在E2中，函數前綴為@字符，該字符調用隱式交集。結果，只返回第一個唯一值：

=@UNIQUE(A2:A9)

有關更多信息，請參閱Excel中的隱式交集。

Excel動態數組的優勢

毫無疑問，動態數組是多年來Excel最好的增強功能之一。與任何新功能一樣，它們都有優缺點。幸運的是，新的Excel動態數組公式的優點是壓倒性的！

簡單而強大

動態數組使創建更強大的公式變得更加簡單。以下是一些示例：

• 提取唯一值：傳統公式| 動態數組函數
• 計數唯一值和不同值：傳統公式| 動態數組函數
• 按字母順序對列進行排序：傳統公式| 動態數組函數

所有公式的原生支持

在動態Excel中，您無需擔心哪些函數支持數組，哪些函數不支持。如果公式可以返回多個值，它將默認這樣做。這也適用於算術運算和傳統函數，如本例所示。

嵌套動態數組函數

要解決更複雜的任務的解決方案，您可以自由地組合新的Excel動態數組函數，或將它們與舊函數一起使用，如此處和此處所示。

相對和絕對引用不太重要

由於採用了“一個公式，多個值”的方法，因此無需使用$符號鎖定區域，因為從技術上講，公式只在一個單元格中。因此，在大多數情況下，使用絕對、相對或混合單元格引用（這始終是新手用戶困惑的來源）實際上並不重要——動態數組公式無論如何都會產生正確的結果！

動態數組的局限性

新的動態數組很棒，但與任何新功能一樣，也有一些注意事項。

結果無法以通常的方式排序

動態數組公式返回的溢出區域無法使用Excel的排序功能進行排序。任何此類嘗試都會導致“無法更改數組的一部分”錯誤。要將結果從小到大或從大到小排列，請將當前公式包裝在SORT函數中。例如，這就是您可以同時過濾和排序的方式。

無法刪除溢出區域中的任何值

由於同樣的原因，無法刪除溢出區域中的任何值：無法更改數組的一部分。此行為是預期且合乎邏輯的。傳統的CSE數組公式也是這樣工作的。

不受Excel表格支持

此功能（或錯誤？）相當出乎意料。動態數組公式不適用於Excel表格中，僅適用於常規區域。如果您嘗試將溢出區域轉換為表格，Excel會這樣做。但是，您只會看到#SPILL！錯誤，而不是結果。

不適用於Excel Power Query

動態數組公式的結果無法加載到Power Query中。例如，如果您嘗試使用Power Query將兩個或多個溢出區域合併在一起，則此操作無效。

動態數組與傳統的CSE數組公式

隨著動態數組的引入，我們可以討論兩種類型的Excel：

1. 動態Excel完全支持動態數組、函數和公式。目前只有Excel 365和Excel 2021。
2. 傳統Excel ，也稱為非動態Excel，其中只支持Ctrl Shift Enter數組公式。它是Excel 2019、Excel 2016、Excel 2013及更早版本。

不用說，動態數組在各個方面都優於CSE數組公式。儘管出於兼容性原因保留了傳統的數組公式，但從現在開始，建議使用新的數組公式。

以下是最重要的區別：

• 動態數組公式在一個單元格中輸入，並使用常規Enter鍵完成。要完成舊式的數組公式，您需要按Ctrl Shift Enter。
• 新的數組公式會自動溢出到多個單元格。 CSE公式必須複製到單元格區域才能返回多個結果。
• 動態數組公式的輸出會隨著源區域中的數據變化而自動調整大小。如果返回區域太小，CSE公式會截斷輸出；如果返回區域太大，則會在多餘的單元格中返回錯誤。
• 動態數組公式可以輕鬆地在單個單元格中編輯。要修改CSE公式，您需要選擇並編輯整個區域。
• 在CSE公式區域中無法刪除和插入行——您需要先刪除所有現有公式。使用動態數組，插入或刪除行不是問題。

向後兼容性：傳統Excel中的動態數組

當您在舊版Excel中打開包含動態數組公式的工作簿時，它會自動轉換為用大括號{}括起來的常規數組公式。當您在新版Excel中再次打開工作表時，大括號將被刪除。

在傳統Excel中，新的動態數組函數和溢出區域引用會在前面加上_xlfn，以指示不支持此功能。溢出區域引用符號（#）將被ANCHORARRAY函數替換。

例如，以下是Excel 2013中UNIQUE公式的顯示方式：

大多數動態數組公式（但並非全部！）都會在傳統Excel中繼續顯示其結果，直到您對它們進行任何更改。編輯公式會立即破壞它並顯示一個或多個#NAME？錯誤值。

Excel動態數組公式無效

根據函數的不同，如果使用不正確的語法或無效的參數，可能會發生不同的錯誤。以下是使用任何動態數組公式時可能遇到的三個最常見的錯誤。

#SPILL! 錯誤

當動態數組返回多個結果，但某些內容阻塞了溢出區域時，會發生#SPILL！錯誤。

要修復此錯誤，您只需清除或刪除溢出區域中任何不完全為空的單元格。要快速找到所有妨礙的單元格，請單擊錯誤指示器，然後單擊選擇阻塞單元格。

除了非空的溢出區域外，此錯誤還可能由其他一些原因引起。有關更多信息，請參閱：

• Excel #SPILL 錯誤- 原因和解決方法
• 如何修復VLOOKUP、INDEX MATCH、SUMIF中的#SPILL！錯誤

#REF! 錯誤

由於工作簿之間對外部引用的支持有限，動態數組需要同時打開兩個文件。如果源工作簿已關閉，則會顯示#REF！錯誤。

#NAME? 錯誤

如果您嘗試在舊版Excel中使用動態數組函數，則會發生#NAME？錯誤。請記住，新函數僅在Excel 365和Excel 2021中可用。

如果在受支持的Excel版本中出現此錯誤，請仔細檢查有問題的單元格中的函數名稱。可能是它被錯誤輸入了:)

這就是如何在Excel中使用動態數組的方法。希望您會喜歡這個很棒的新功能！無論如何，感謝您的閱讀，希望下週能在我們的博客上見到您！

以上是Excel動態陣列，功能和公式的詳細內容。更多資訊請關注PHP中文網其他相關文章！