Excel 中數組公式的使用：完整指南和實用範例

該 Excel 中帶有數組的公式 它們就像「黑魔法」一樣，初次見到時讓人覺得不可思議，但當你掌握了它們之後，它們就能讓你用一個公式完成以前需要幾十個甚至幾百個輔助單元格才能完成的事情。

雖然他們最初可能贏得一些尊重， 矩陣和矩陣公式 它們是 Excel 最強大的數據分析、建模、金融、工程資源之一，或者只是為了讓你的電子表格更清晰、更快速。

Excel中的陣列和陣列公式分別是什麼？

在 Excel 中，矩陣就是一個 值集合 它們被視為一個集合：它們可以位於同一行、同一列，或形成由幾行和幾列組成的區塊。

例如，一個非常典型的矩陣可能包含以下內容： 一年中的幾個月 將它們上下排列或並排排列，Excel 會將該群組資料視為單一資料物件進行處理。

一 矩陣公式 這是一個公式，它不是對單一值進行操作，而是同時處理一個完整的元素陣列：它可以一次執行多個計算，並傳回單一結果或結果陣列。

關鍵在於數組公式使 Excel 更加實用。 批量處理多種物品內部評估所有值，如有必要，也可以一次在不同的儲存格中傳回多個結果。

例如，假設 B 列是售出的商品數量，C 列是每件商品的價格。使用數組公式，例如 =SUM(B2:B11*C2:C11)Excel 將每一行（單位數乘以價格）相乘，然後將所有這些乘積相加，無需中間列。

如何輸入並識別經典矩陣公式

在 Excel 中，傳統的陣列公式需要使用特殊的快捷鍵組合輸入： Ctrl + Shift + Enter （Ctrl + Shift + Enter）。僅僅按 Enter 鍵是不夠的。

當您編寫陣列公式並確認該組合時，Excel 會在公式欄中顯示該公式，並用括號將其括起來。 鍵 { }這些鍵不是手動輸入的：Excel 會在偵測到是陣列公式時自動新增它們。

如果您嘗試手動輸入花括號，Excel 將不會將其識別為逗號。 數組公式但由於這是一個普通公式，因此必須使用組合鍵才能使其生效。

每次編輯陣列公式時，花括號都會暫時消失：您需要再次按下[對應的按鍵]。 Ctrl + Shift + Enter 編輯完成後，公式將不再基於矩陣，而只會根據範圍的第一個元素進行計算。

還有一點很重要：如果您忘記使用組合鍵而直接按 Enter 鍵，公式的行為將與預期不符。 標準公式只取每個範圍內的第一個值可能會導致錯誤的結果，而你可能沒有意識到。

矩陣公式的類型：單一結果或多重結果

我們可以區分兩種主要類型 Excel 中帶有數組的公式：傳回單一值的函數和傳回分佈在多個儲存格中的一組結果的函數。

第一種情況下，公式接受一個資料數組，執行計算，並傳回一個結果。 一個單元格中只有一個結果 （例如，總和、平均值、計數、最小值或最大值）。

在第二種類型中，公式本身會產生一個 輸出矩陣 佔據兩個或多個單元格。在這種情況下，所有結果都屬於同一個陣列公式，該公式同時存在於多個儲存格中。

特點如 SUMA的, 平均的, 關於 MAX o MIN （及其變體， MINIFS 和 MAXIFS) 可以處理數組，前提是數組以數組公式的形式輸入到單一單元格中，而其他函數，例如 ) 移調, 騰達 o 佛牌它們被設計用來傳回多個單元格的陣列。

這一切最強大的地方在於，一個公式就能 替換許多輔助柱，維持 你更乾淨、更輕的刀片 而且複製或更新錯誤的風險更小。

經典矩陣公式的進階範例

矩陣公式使我們能夠解決一些用標準公式難以處理甚至根本無法解決的問題。以下列舉幾個例子。 例子 典型的和高級的基於範圍的，名稱如 Data、Sales、MyData 或 YourData。

包含誤差的總和範圍

當範圍包含諸如以下錯誤時 ＃N / A使用 SUM 函數進行普通求和會失敗。使用陣列公式可以忽略這些錯誤，只對有效值求和：

=SUM(IF(ISERROR(Data),"",Data))

功能 發行人錯誤 偵測資料範圍內的錯誤儲存格及其功能 SI 建立一個新數組，其中用空字串代替錯誤訊息。 “”並且，沒有錯誤的單元格保持其原始值。

在這個乾淨的矩陣上，函數 SUMA的 它在計算總數時忽略空元素，因此即使原始範圍有缺陷的單元格，您也可以獲得總和。

統計一定範圍內的錯誤數量

如果您需要的是 計算錯誤 你可以不用將它們相加，而是使用類似的變體：

=SUM(IF(ISERROR(Data),1,0))

這個公式建立了一個矩陣，其中每個包含錯誤的單元格都會被轉換為一個 1 每個單元格均無錯誤 0因此，所有這些 1 和 0 的總和就是錯誤總數。

該公式可以 簡化 移除 IF 函數的第三個參數，因為當條件為假時，它會傳回 FALSE，而 SUM 函數將 FALSE 解釋為 0：

=SUM(IF(ISERROR(Data),1))

而且，還可以透過直接將布林運算結果乘以 1 來進一步縮短表達式，利用了以下事實： TRUE*1=1 y 錯誤*1=0:

=SUM(IF(ISERROR(Data)*1))

手動新增滿足條件的值（AND 和 OR）

使用陣列公式，您可以僅對滿足特定條件的值求和，而無需始終使用 SUMIFS 函數。例如，要僅對以下值求和： 積極 銷售範圍：

=SUM(IF(銷售額>0,銷售額))

這是該函數 SI 它會產生一個數組，其中值大於 0 的單元格保留其值，其餘單元格變為 FALSE；該函數 SUMA的 忽略 FALSE 值，只加上正數。

您也可以使用乘法（等價於）組合多個條件。 並且合乎邏輯）或總和（相當於一個 或者邏輯例如，要將大於 0 且小於等於 5 的值相加：

=SUM((銷售量>0)*(銷售額<=5)*(銷售))

在這種情況下，邏輯表達式傳回 TRUE/FALSE 數組，這些數組相乘後結果為 1 或 0，並作為 過濾器 銷售額。

如果你需要的是O型行為，你可以使用 邏輯條件之和 在國際單位制 (SI) 中，例如以下公式，它將小於 5 或大於 15 的值相加：

=SUM(IF((銷售額<5)+(銷售額>15);銷售))

功能介紹 Y y O 它們會傳回單一 TRUE 或 FALSE，因此不能直接與多個陣列一起使用；解決方法是像前面的範例一樣，用乘法和加法來模擬它們。

計算均值（不包括零值）

如果你想獲得一個 不考慮零值的平均值您可以將 AVERAGE 函數與銷售範圍的陣列條件結合使用：

=AVERAGE(IF(銷售額<>0;銷售))

最終得到的SI矩陣只包含非0值，這些值就是最終要使用的值。 平均的 計算平均值。

計算兩個範圍之間的差異

假設你有兩個大小和形狀相同的資料區域，分別稱為 MyData 和 YourData，你想知道 它們在多少個細胞中存在差異用矩陣公式可以這樣求解：

=SUM(IF(MyData=YourData,0,1))

IF 函數會產生一個數組，其中每個匹配項記為 0，每個不匹配項記為 1。對數組求和即可得到結果。 數數 來自不同的細胞。

這裡還有一個更簡潔的版本，它直接使用不等式比較（<>乘以 1：

=SUM(1*(MyData<>YourData))

再一次， 把戲 當 TRUE 乘以某個數等於 1，FALSE 乘以某個數等於 0 時，數就等於 TRUE 等於 1，FALSE 等於 0。

找出最大值及其在範圍內的位置。

利用矩陣公式，你不僅可以找出什麼 最大值 不僅指一組元素，還指它在圖紙上的確切位置；你也可以使用 排名功能 排序和定位職位。

找到 行號 若要尋找名為「Data」的欄位中某個範圍內的最大值，可以使用下列方法：

=MIN(IF(Data=MAX(Data),ROW(Data),»»))

此公式建立一個數組，其中包含最大值的單元格儲存其行號，其餘單元格則留空。 MIN 求出該矩陣中與最大值所在的行相對應的最小數。

如果你想要的是得到 細胞參考 為了達到最大精度，您可以將先前的計算用 ADDRESS 和 COLUMN 包裝：

=ADDRESS(MIN(IF(Data=MAX(Data),ROW(Data),"")),COLUMN(Data))

逐步建立多單元格數組公式

在一本樣本中 賣家桌車輛類型、銷售量和價格可以說明同時佔據多個單元格的矩陣公式是如何運作的。

假設您複製了一個從儲存格 A1 開始的表格，其中包含以下欄位： 賣家、車輛類型、售出數量、單價和總銷售額並且，在 E 列中，您希望使用陣列公式計算每一行的銷售額。

C2:C11區域儲存的是銷售數量，D2:D11區域儲存的是價格；目標是將E2:E11區域填滿為… 每行的乘積 無需逐一編寫公式。

若要使用陣列公式跨多個儲存格執行此操作，請先選擇整個範圍。 E2：E11在公式欄中輸入：

=C2:C11*D2:D11

並確認 Ctrl + Shift + EnterExcel 會一次將 E2 到 E11 的所有儲存格填入與每一行對應的結果。

同一範例中的單單元格數組公式

從同一張表格出發，可以得到 總銷售額 例如，在單一儲存格中使用單一陣列公式。 B13.

無需逐行編寫公式，只需在 B13 儲存格中輸入以下內容：

=SUM(C2:C11*D2:D11)

並確認 Ctrl + Shift + EnterExcel 內部會計算每對儲存格 Cx*Dx 的乘積，然後將所有這些乘積相加，以得出總數。

如何調試和理解複雜的矩陣公式

當公式很長且有點“晦澀難懂”時，能夠理解它就至關重要了。 查看每個部分正在計算的內容Excel 讓您可以使用 F9 鍵計算公式片段。

訣竅在於選擇公式欄中的特定部分（例如，僅選擇部分）。 B2:B11*C2:C11然後按 F9 因此，Excel 會暫時以中間結果取代該片段。

透過在數組公式中執行此操作，您將看到 所得矩陣及其所有組成部分，極大地幫助理解公式的行為並找到可能的錯誤。

檢查完選定的部件後，您可以按下 ESC 若要退出而不儲存更改，或者如果您不小心確認了操作並想撤銷操作，請按 Ctrl + Z。

Excel 中的常數數組：如何建立和使用它們

除了使用儲存格區域外，Excel 還允許您使用 矩陣常數這些固定值是直接寫在公式中的一組固定值，當公式被複製或移動時，這些固定值不會改變。

矩陣常數可以包含 數字、文字、邏輯值（真/假）或錯誤但它不能包含單元格引用、已定義名稱、日期、函數或其他陣列。

存在水平方向的一維常數（單行）、垂直方向的一維常數（單列和多列）。 QR 圖 （由行和列組成的區塊），並透過元素之間使用的分隔符號來區分。

在西班牙地區語境中，垂直陣列通常用…隔開。 分號（;）而在水平數組中，可以使用其他分隔符號（在某些配置中是反斜線）或逗號，這取決於系統配置。

例如，一個 垂直矩陣 如果用月份來表示，可以這樣寫：

={"一月";"二月";"三月";"四月";"五月";"六月";"七月";"八月";"九月";"十月";"十一月";"十二月"}

將數組常數賦名

為了使使用大型常數更易於管理，您可以 給它起個名字 使用 Excel 的名稱管理器，您可以重複使用該名稱，而無需再次輸入。

過程很簡單：轉到“公式”選項卡，使用以下選項： 定義名稱或指派名稱您輸入所需的名稱，然後在「引用」方塊中直接輸入矩陣常數。

例如，您可以建立一個名為“月份”的名稱， 指向常數:

={"一月"\"二月"\"三月"\"四月"\"五月"\"六月"\"七月"\"八月"\"九月"\"十月"\"十一月"\"十二月"}

然後，只需選擇與矩陣元素個數相同的儲存格，並輸入名稱即可。 =月 並將其確認為陣列公式，以便顯示工作表中的所有值。

如果常量導致了問題，最好檢查一下。 使用的分隔符 並且在按 Ctrl + Shift + Enter 輸入公式之前，已選擇適合矩陣大小的範圍。

矩陣常數的應用範例

常量可讓您用極少的空間建立強大的公式。例如，要添加… 一系列數值中最高的三個值 您可以結合使用 LARGE 和定義所需順序的常數。

類似地，也可以將 N 個最小值相加。 K.ESIMO.MINOR只需更改函數，但保留要求和的位置數組即可。

另一個典型例子是統計評估者（例如 Pedro）給出分數的次數。 幾個具體數值 無需在 COUNTIFS 中一再重複條件。

在一個 評估範圍 你可以使用類似這樣的公式：

=SUMA(CONTAR.SI.CONJUNTO(A2:A28;»Pedro»;C2:C28;{3\4\5}))

這裡，常數 {3\4\5} 它收集了可接受的分數（3、4 和 5），使公式更緊湊、更易於維護，儘管如有必要，您可以添加更多值，但始終要遵守公式的最大字元限制。

Excel 365 和 2021 中的動態陣列公式

使用現代版的 Excel（微軟365 （以及 Excel 2021）引入了一個非常重要的變化： 動態矩陣公式這樣在大多數情況下就不需要使用 Ctrl + Shift + Enter 了。

這些新公式與以下公式原生相容： 範圍和矩陣 它們能夠自動「溢出」到相鄰單元格，佔據顯示所有結果所需的行和列數。

主要區別在於，您只需像往常一樣在儲存格中輸入公式並按 Enter 鍵；Excel 會自動填入所需的輸出範圍，並以特殊邊框標記該範圍，表示這是一個輸出結果。 溢出範圍.

此外，還出現了一些專門用於處理動態數組的新功能，例如： 篩選, 命令, 獨特的, 順序, 排序方式 o 隨機矩陣，除其他。

這些功能可讓您進行篩選、排序、 產生順序列表 或隨機數，並傳回結果集，而無需事先定義目標範圍的大小。

經典矩陣公式與動態矩陣公式的主要差異

經典矩陣公式要求 Ctrl + Shift + Enter它們可能比較難讀，而且在很多情況下，它們自動產生結果的能力有限，不像現代功能那樣… VLOOKUP 和 XLOOKUP.

動態矩陣公式可寫成如下形式 普通公式只需按 Enter 鍵即可確認，結果會自動顯示，無需選擇先前的範圍或使用繼承的陣列公式。

另一個重要的差異在於動態函數 明確返回矩陣 這是意料之中的；如果一本舊書使用了一個函數，則函數會傳回一個陣列到多個儲存格，Excel 可以套用一個靜默的隱式交集。

使用動態陣列時，Excel 會用運算子標記那些舊的情況。 @這表示隱式交集發生的位置，目的是為了保持先前的行為並避免意外結果。

另外值得注意的是，動態​​矩陣公式僅在以下情況下可用： Excel 365 與 Excel 2021在早期版本中，這些公式無法正常運作，如果這些工作簿在不支援動態陣列的電腦上打開，則可能會顯示為舊式陣列公式。

設定和使用動態數組公式

要使用動態公式，只需選擇即可。 起始細胞輸入包含 FILTER 或 SORT 等函數的公式，然後按 Enter 鍵。 Excel 會自動將結果向下向右排列。

確保有以下情況很重要： 周圍有自由空間 從起始儲存格開始，因為如果陣列應該轉儲的儲存格已經包含數據，Excel 將顯示溢出錯誤（#OVERFLOW 或類似錯誤）。

公式建立完成後，溢出範圍即生效。 像塊如果修改主儲存格中的公式，所有結果都會更新；如果要刪除所有內容，只需從該儲存格中刪除公式即可。

您也可以參考 溢出範圍 與其他使用溢出運算子的公式（例如 =SUM(F2#)）不同，如果儲存格大小增加或減少，使用該儲存格的公式將自動調整。

在環境中 程序設計諸如此類的庫 Aspose.Cells 它們允許您透過程式碼設定和重新計算動態數組公式，使用特定方法將其指派給儲存格並在執行常規公式計算之前刷新它們。

矩陣的高階應用：線性代數與金融

在 Excel 中使用陣列不僅限於對區域求和或篩選值：它還可以用於解決涉及以下方面的問題： 線性代數 以及最佳化，例如矩陣求逆、求解方程組或建立投資組合。

在 Excel 中，可以使用下列函數求方陣 A 的逆矩陣： MINVERSA這需要一個矩陣（或動態，取決於版本）公式，其範圍與原始矩陣的大小相同。

要獲得位於 B3:D5 中的 3×3 矩陣的逆矩陣，您需要選擇一個空的 3×3 區塊，然後輸入 =MINVERSE(B3:D5) 然後，您可以將其確認為矩陣公式，從而得到矩陣 A。^-1.

如果將原矩陣乘以其逆矩陣，則 MMULT 在適當的範圍內，你會得到 單位矩陣類似於將一個數乘以它的倒數，結果總是 1。

類似地，你可以建立一個矩陣形式的線性方程組，其中 A 為係數矩陣，K 為未知數向量，P 為獨立項向量，並利用下列關係式解： K = A^-1 · 普 使用 MINVERSA 和 MMULT。

在金融領域，這種矩陣方法被應用於例如以下問題： 投資組合最佳化其中使用了協方差矩陣 σ。_ij 證券之間的預期收益率μ_j 並考慮獲利能力限制，以找到在特定獲利目標下風險最低的資產組合。

從拉格朗日函數和一階條件的推導發，我們再次得到形如 A·X = P 的矩陣系統，解為 X = A^-1·P 給我們 最佳權重 投資組合中每項資產的權重​​。

以三隻股票 A、B 和 C 為例，給定它們的協方差和預期收益率，可以確定一個投資向量，使得投資組合獲得 4% 的預期收益率。 最小方差由此形成的組合利用多元化來降低風險，相較於投資單一證券而言。

所有這些都是使用相同的基本工具實現的： MINVERSA、MMULT 和矩陣公式這進一步證實了，當掌握了矩陣的使用方法後，Excel 可以成為一個非常強大的數值分析平台。

在涵蓋了從經典矩陣公式的基礎知識、矩陣常數和動態矩陣到線性代數和金融領域的高級應用等所有內容之後，很明顯： 學習如何在Excel中有效率地處理矩陣 這是一項投資，它能讓你工作得更乾淨、更快捷，並能提供傳統方法難以實現的解決方案。