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

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

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

• 動態(tài)數(shù)組可用性
• 動態(tài)數(shù)組函數(shù)
• 動態(tài)數(shù)組公式示例
• 溢出區(qū)域- 一個(gè)公式，多個(gè)單元格
• 溢出區(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ù)組，它們會自動計(jì)算並將值返回到多個(gè)單元格中，而這些值都基於單個(gè)單元格中輸入的公式。

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

讓我們用一個(gè)非?；镜睦觼碚f明這個(gè)概念。假設(shè)您需要將兩組數(shù)字相乘，例如，計(jì)算不同的百分比。

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

=A3:A5*B2:D2

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

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

需要注意的是，最近的更新不僅僅是處理Excel數(shù)組的一種新方法。事實(shí)上，這是對整個(gè)計(jì)算引擎的突破性改變。有了動態(tài)數(shù)組，Excel函數(shù)庫中添加了一系列新函數(shù)，現(xiàn)有函數(shù)也開始運(yùn)行得更快、更高效。最終，新的動態(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手機(jī)版Excel
• 網(wǎng)頁版Excel

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

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

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

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

• UNIQUE - 從單元格區(qū)域中提取唯一項(xiàng)。
• FILTER - 根據(jù)您定義的條件過濾數(shù)據(jù)。
• SORT - 按指定的列對單元格區(qū)域進(jìn)行排序。
• SORTBY - 按另一個(gè)區(qū)域或數(shù)組對單元格區(qū)域進(jìn)行排序。
• RANDARRAY - 生成一個(gè)隨機(jī)數(shù)數(shù)組。
• SEQUENCE - 生成一個(gè)序列號列表。
• 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ù)組擴(kuò)展到指定數(shù)量的行和列。
• CHOOSECOLS - 從數(shù)組中返回指定的列。
• CHOOSEROWS - 從數(shù)組中提取指定的行。
• GROUPBY - 基於一列或多列中的值按行對數(shù)據(jù)進(jìn)行分組和聚合。
• PIVOTBY - 按行和列對數(shù)據(jù)進(jìn)行分組，並聚合關(guān)聯(lián)的值。

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

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

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

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

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

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

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

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

=UNIQUE(B2:B10)

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

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

如果沒有辦法用一個(gè)函數(shù)完成任務(wù)，可以將幾個(gè)函數(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中實(shí)現(xiàn)的新計(jì)算引擎可以輕鬆地將傳統(tǒng)公式轉(zhuǎn)換為數(shù)組，因此沒有什麼可以阻止您將新舊函數(shù)組合在一起。

例如，要計(jì)算某個(gè)區(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ū)域，它將為第一個(gè)單元格返回單個(gè)結(jié)果：

=TRIM(A2:A6)

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

示例5. 返回多個(gè)值的VLOOKUP公式

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

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

示例6. 簡化的TRANSPOSE公式

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

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

=TRANSPOSE(A1:B6)

搞定！

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

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

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

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

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

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

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

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

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

=COUNTA(A2#)

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

=SUM(A2#)

提示：

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

隱式交集和@字符

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

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

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

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

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

=UNIQUE(A2:A9)

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

=@UNIQUE(A2:A9)

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

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

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

簡單而強(qiáng)大

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

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

所有公式的原生支持

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

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

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

相對和絕對引用不太重要

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

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

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

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

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

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

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

不受Excel表格支持

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

不適用於Excel Power Query

動態(tài)數(shù)組公式的結(jié)果無法加載到Power Query中。例如，如果您嘗試使用Power Query將兩個(gè)或多個(gè)溢出區(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ù)組在各個(gè)方面都優(yōu)於CSE數(shù)組公式。儘管出於兼容性原因保留了傳統(tǒng)的數(shù)組公式，但從現(xiàn)在開始，建議使用新的數(shù)組公式。

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

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

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

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

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

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

大多數(shù)動態(tài)數(shù)組公式（但並非全部?。┒紩趥鹘y(tǒng)Excel中繼續(xù)顯示其結(jié)果，直到您對它們進(jìn)行任何更改。編輯公式會立即破壞它並顯示一個(gè)或多個(gè)#NAME？錯(cuò)誤值。

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

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

#SPILL! 錯(cuò)誤

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

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

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

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

#REF! 錯(cuò)誤

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

#NAME? 錯(cuò)誤

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

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

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

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