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

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

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

• 動態(tài)數(shù)組可用性
• 動態(tài)數(shù)組函數(shù)
• 動態(tài)數(shù)組公式示例
• 溢出區(qū)域- 一個公式，多個單元格
• 溢出區(qū)域引用（# 符號）
• 隱式交集和@字符
• 動態(tài)數(shù)組的優(yōu)勢
• 動態(tài)數(shù)組的局限性
• 動態(tài)數(shù)組與傳統(tǒng)的CSE數(shù)組公式
• 向後兼容性
• Excel動態(tài)數(shù)組公式無效

Excel動態(tài)數(shù)組

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

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

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

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

=A3:A5*B2:D2

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

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

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

Excel動態(tài)數(shù)組可用性

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

以下版本支持動態(tài)數(shù)組：

• Windows版Excel 365
• Mac版Excel 365
• Excel 2021
• Mac版Excel 2021
• iPad版Excel
• iPhone版Excel
• Android平板電腦版Excel
• Android手機版Excel
• 網(wǎng)頁版Excel

Excel動態(tài)數(shù)組函數(shù)

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

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

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

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

此外，還有兩個流行的Excel函數(shù)的現(xiàn)代替代品，它們並非正式屬於該組，但可以利用動態(tài)數(shù)組的所有優(yōu)勢：

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

Excel動態(tài)數(shù)組公式

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

下面的例子展示了動態(tài)數(shù)組公式的實際應(yīng)用，以及動態(tài)數(shù)組對現(xiàn)有函數(shù)的影響。

示例1. 新的動態(tài)數(shù)組函數(shù)

此示例演示了使用Excel動態(tài)數(shù)組函數(shù)可以實現(xiàn)多麼快速簡單的解決方案。

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

=UNIQUE(B2:B10)

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

示例2. 在一個公式中組合多個動態(tài)數(shù)組函數(shù)

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

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

其中A2:C13是源數(shù)據(jù)，B2:B13是要檢查的值，F(xiàn)1是條件。

示例3. 將新的動態(tài)數(shù)組函數(shù)與現(xiàn)有函數(shù)一起使用

由於Excel 365中實現(xiàn)的新計算引擎可以輕鬆地將傳統(tǒng)公式轉(zhuǎn)換為數(shù)組，因此沒有什麼可以阻止您將新舊函數(shù)組合在一起。

例如，要計算某個區(qū)域中共有多少唯一值，可以將動態(tài)數(shù)組UNIQUE函數(shù)嵌套在舊的COUNTA函數(shù)中：

=COUNTA(UNIQUE(B2:B10))

示例4. 現(xiàn)有函數(shù)支持動態(tài)數(shù)組

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

=TRIM(A2:A6)

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

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

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

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

示例6. 簡化的TRANSPOSE公式

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

在動態(tài)Excel中，您只需在輸出區(qū)域的最左單元格中輸入公式並按Enter鍵：

=TRANSPOSE(A1:B6)

搞定！

溢出區(qū)域- 一個公式，多個單元格

溢出區(qū)域是包含動態(tài)數(shù)組公式返回的值的單元格區(qū)域。

當(dāng)選擇溢出區(qū)域中的任何單元格時，會出現(xiàn)藍色邊框，以顯示其內(nèi)部的所有內(nèi)容都是由左上角單元格中的公式計算的。如果您刪除第一個單元格中的公式，所有結(jié)果都將消失。

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

注意。如果其他數(shù)據(jù)阻塞了溢出區(qū)域，則會發(fā)生#SPILL錯誤。一旦移除阻塞數(shù)據(jù)，錯誤就會消失。

有關(guān)更多信息，請參閱Excel溢出區(qū)域。

溢出區(qū)域引用（# 符號）

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

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

=COUNTA(A2#)

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

=SUM(A2#)

提示：

• 要快速引用溢出區(qū)域，只需使用鼠標選擇藍色框內(nèi)的所有單元格，Excel就會為您創(chuàng)建溢出引用。
• 與常規(guī)區(qū)域引用不同，溢出區(qū)域引用是動態(tài)的，會自動響應(yīng)區(qū)域大小調(diào)整。 有關(guān)更多詳細信息，請參閱溢出區(qū)域運算符。

隱式交集和@字符

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

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

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

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

在C2中，有一個動態(tài)數(shù)組公式，它將結(jié)果溢出到許多單元格中：

=UNIQUE(A2:A9)

在E2中，函數(shù)前綴為@字符，該字符調(diào)用隱式交集。結(jié)果，只返回第一個唯一值：

=@UNIQUE(A2:A9)

有關(guān)更多信息，請參閱Excel中的隱式交集。

Excel動態(tài)數(shù)組的優(yōu)勢

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

簡單而強大

動態(tài)數(shù)組使創(chuàng)建更強大的公式變得更加簡單。以下是一些示例：

• 提取唯一值：傳統(tǒng)公式| 動態(tài)數(shù)組函數(shù)
• 計數(shù)唯一值和不同值：傳統(tǒng)公式| 動態(tài)數(shù)組函數(shù)
• 按字母順序?qū)α羞M行排序：傳統(tǒng)公式| 動態(tài)數(shù)組函數(shù)

所有公式的原生支持

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

嵌套動態(tài)數(shù)組函數(shù)

要解決更複雜的任務(wù)的解決方案，您可以自由地組合新的Excel動態(tài)數(shù)組函數(shù)，或?qū)⑺鼈兣c舊函數(shù)一起使用，如此處和此處所示。

相對和絕對引用不太重要

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

動態(tài)數(shù)組的局限性

新的動態(tài)數(shù)組很棒，但與任何新功能一樣，也有一些注意事項。

結(jié)果無法以通常的方式排序

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

無法刪除溢出區(qū)域中的任何值

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

不受Excel表格支持

此功能（或錯誤？）相當(dāng)出乎意料。動態(tài)數(shù)組公式不適用於Excel表格中，僅適用於常規(guī)區(qū)域。如果您嘗試將溢出區(qū)域轉(zhuǎn)換為表格，Excel會這樣做。但是，您只會看到#SPILL！錯誤，而不是結(jié)果。

不適用於Excel Power Query

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

動態(tài)數(shù)組與傳統(tǒng)的CSE數(shù)組公式

隨著動態(tài)數(shù)組的引入，我們可以討論兩種類型的Excel：

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

不用說，動態(tài)數(shù)組在各個方面都優(yōu)於CSE數(shù)組公式。儘管出於兼容性原因保留了傳統(tǒng)的數(shù)組公式，但從現(xiàn)在開始，建議使用新的數(shù)組公式。

以下是最重要的區(qū)別：

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

向後兼容性：傳統(tǒng)Excel中的動態(tài)數(shù)組

當(dāng)您在舊版Excel中打開包含動態(tài)數(shù)組公式的工作簿時，它會自動轉(zhuǎn)換為用大括號{}括起來的常規(guī)數(shù)組公式。當(dāng)您在新版Excel中再次打開工作表時，大括號將被刪除。

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

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

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

Excel動態(tài)數(shù)組公式無效

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

#SPILL! 錯誤

當(dāng)動態(tài)數(shù)組返回多個結(jié)果，但某些內(nèi)容阻塞了溢出區(qū)域時，會發(fā)生#SPILL！錯誤。

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

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

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

#REF! 錯誤

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

#NAME? 錯誤

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

如果在受支持的Excel版本中出現(xiàn)此錯誤，請仔細檢查有問題的單元格中的函數(shù)名稱?？赡苁撬诲e誤輸入了:)

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

以上是Excel動態(tài)陣列，功能和公式的詳細內(nèi)容。更多資訊請關(guān)注PHP中文網(wǎng)其他相關(guān)文章！