Excel 進階教學：如何用陣列公式自動篩選，只顯示有資料的列？

在處理 Excel 資料時，我們經常會遇到一個需求：如何從一個包含許多空白列的資料表中，自動篩選出所有包含資料的列，並整理成一個新的、乾淨的表格？

很多人會使用「篩選」功能，然後手動複製貼上。但這種方法不僅繁瑣，而且當原始資料更新時，你還得再重做一次。今天，我們將介紹一個進階技巧，透過組合使用 Excel 的陣列公式，實現一個能動態更新的篩選結果。

核心問題

如下圖所示，左邊是我們的原始資料表，其中「數量」欄位有部分是空白的。我們的目標是在右邊建立一個新表格，它只會顯示左邊表格中「數量」欄位不是空白的那些列。而且當我們在左邊新增或刪除數量時，右邊的表格會自動更新。

使用的關鍵函數

要完成這個任務，我們主要會用到以下幾個函數的組合：

• IF：進行條件判斷。

• ROW：取得儲存格的列號。

• SMALL：找出陣列中第 N 小的數值。

• OFFSET：以指定的參照位址為起點，傳回位移指定列數與欄數後的儲存格內容。

• IFERROR：用於捕捉並處理公式中的錯誤。

步驟分解教學（輔助欄法）

為了方便理解，我們先用一個「輔助欄位」來一步步拆解公式的運作原理。

步驟 1：找出所有包含資料的「列號」

首先，我們要判斷 B 欄（數量）中哪些儲存格有資料，並取得它們的列號。

1. 在輔助欄位（例如 K2）中輸入以下公式：

   =IF($B$2:$B$21<>"",ROW($B$2:$B$21))

2. 這是一個陣列公式，輸入完畢後必須按下 Ctrl + Shift + Enter 組合鍵。你會看到公式前後自動加上大括號 {}。

這個公式的意思是：檢查 $B$2:$B$21 這個範圍，如果儲存格不等於空值（<>""），就傳回該儲存格的 ROW()（列號），否則就傳回 FALSE。

步驟 2：整理並排序有效的列號

上一步的結果會包含許多 FALSE。接下來我們用 SMALL 函數，將有效的列號從小到大依序排列出來。

1. 修改 K2 的公式如下：

   =SMALL(IF($B$2:$B$21<>"",ROW($B$2:$B$21)),ROW(A1))

2. 同樣，按下 Ctrl + Shift + Enter。

這裡 ROW(A1) 是一個計數器，當公式向下拖曳時，它會變成 ROW(A2)、ROW(A3)，依序回傳 1、2、3...。這等於是告訴 SMALL 函數，請依序找出陣列中第1小、第2小、第3小的列號。

步驟 3：使用 OFFSET 取得對應資料

現在我們有了排序好的列號清單，就可以用 OFFSET 函數來抓取原始資料了。

1. 在最終表格的第一格（例如 L2）輸入以下公式：

   =OFFSET($A$1, K2-1, 0)
   ```2.  這個公式的意思是：以 `$A$1` 為基準點，向下位移 `K2-1` 列（`K2` 是我們找到的第一個有效列號，減 1 是因為 `OFFSET` 是計算「位移量」），向右位移 `0` 欄，取得該儲存格的內容。

步驟 4：整合為單一終極公式

理解了輔助欄位的原理後，我們就可以將它們全部整合成一個公式，不再需要任何輔助欄位。

1. 在最終表格的第一格（例如 L2）輸入以下完整公式：

   =IFERROR(OFFSET($A$1, SMALL(IF($B$2:$B$21<>"",ROW($B$2:$B$21)),ROW(A1))-1, COLUMN(A1)-1), "")

2. 關鍵：再次按下 Ctrl + Shift + Enter 將其定義為陣列公式。

3. 將這個公式向右、再向下拖曳，填滿你的新表格。

公式解析：

• COLUMN(A1)-1 的作用和 ROW(A1) 類似，當公式向右拖曳時，會依序產生 0, 1, 2...，用來動態指定 OFFSET 的欄位移量。

• 最外層的 IFERROR(..., "") 則是為了處理當所有資料都抓完後，SMALL 函數找不到更多數值所產生的 #NUM! 錯誤，讓儲存格顯示為空白，使表格更美觀。

現在，只要你修改 A、B、C 欄的原始資料，右邊的表格就會立刻同步更新，完全實現了自動化篩選！