Excel 動態陣列函數：演進史上最強大的革新

如果你使用的是 Microsoft 365 或 Excel 2021 以後的版本，你可能會發現有些公式輸入後，居然會自動「溢位」(Spill) 到旁邊的格子，一次填滿好幾行。這就是所謂的 「動態陣列」(Dynamic Arrays)。

這不只是新功能，它徹底改寫了我們使用 Excel 的邏輯。以前你需要寫一個公式後拼命往下拖曳，現在你只需要在一個儲存格寫公式，結果就會自動長出來。

UNIQUE：提取不重複清單

以前想從 5000 筆訂單中找出「總共有哪些客戶」，你需要用「移除重複項」功能。現在，一個公式搞定。

  • 語法：=UNIQUE(範圍)
  • 效果：它會自動列出該範圍內所有出現過、不重複的項目。當原始資料新增時，這份清單也會自動更新。

UNIQUE()

FILTER：自動化的篩選器

這可能是最強大的新函數，它能根據條件，將符合的資料整列抓出來，放到另一個區域。

語法結構

=FILTER(要抓取的範圍, 判斷條件, [找不到時顯示什麼])

FILTER()

實戰應用

你想在 Sheet2 自動列出所有「未交貨」的訂單明細：

  • 公式：=FILTER(A:D, B:B="未交貨", "全數已送達")
  • 這讓你不必手動點選篩選器，就能建立一個隨時自動更新的「異常清單報表」。

SORT：公式化的排序

如果你想讓剛才用 UNIQUE 抓出來的客戶清單，自動按筆劃排序。

語法：=SORT(範圍, [依第幾欄排], [遞增或遞減])

參數說明：

  1. [依第幾欄排] (sort_index)：選填。預設為 1。如果範圍有多欄，你可以填寫數字來指定要按哪一欄排序。
  2. [遞增或遞減] (sort_order)：選填。
    • 1：遞增排序（由小到大、A 到 Z）。（預設）
    • -1：遞減排序（由大到小、Z 到 A）。

SORT()

實戰範例：按業績排行

假設 A 欄是姓名，B 欄是業績。你想一次列出按業績「由高往低」排行的名單：

  • 公式：=SORT(A2:B10, 2, -1) （這代表：選取 A2 到 B10 的範圍，按第 2 欄排序，且排序方式為 -1 遞減。）

  • 組合大招：=SORT(UNIQUE(A:A)) （這會先找出所有不重複的姓名，然後再依照筆劃自動排好。這在以前需要極其複雜的公式才能達成。）

認識「#」引用的威力

當一個公式產生了動態陣列（譬如從 A1 開始溢位），這個 A1 儲存格就變成了一個「母體」。如果你想在其他地方引用「這整串結果」，你不需要知道它到底有幾列，只需要在位址後面加個 # 號。

你可以把 # 想像成一個自動隨身包：不論母體公式後續變長還是變短，# 都會自動幫你抓到最精確的範圍。

實戰應用範例

母體公式 (A1)引用方式說明
=UNIQUE(客戶名單)=COUNTA(A1#)動態計算項目數：當名單增加時，個數會自動同步更新。
=FILTER(業績, 條件)=SUM(A1#)動態加總：只計算被篩選出來的這組數字總和。

超強大招：自動增長的下拉選單

這是 # 最常用的神級技巧。在設定「資料驗證」的來源時：

  1. 先在某處（如 $G$1）寫好 =SORT(UNIQUE(員工名單))。
  2. 在設定資料驗證的「來源」框中輸入 =$G$1#。
  3. 效果：未來只要員工名單有新人加入，你的下拉選單會「自動出現」新名字，且自動排好序，完全不需要手動調整範圍！

常見錯誤：#SPILL! (溢位錯誤)

如果你輸入公式後看到 #SPILL!，代表 Excel 想要把結果填進去，但那些格子上已經有其他資料擋住了路。 解決方法：把擋路（顯示錯誤訊息範圍內）的其他文字刪除，結果就會瞬間彈出來。

為什麼要學動態陣列函數？

動態陣列函數能顯著減少「複製貼上」與「向下拖曳公式」的動作，進而降低人為疏失。它是邁向「Excel 自動化」最簡單也最有效的路徑。