國慶期間，老徐整理了之前粉絲們的常見問題，其中發現很多人會對excel數字提取、文本挖掘有比較大的需求，今天特別將這幾種常見的案例一一復現並通過函數撰寫給出解決辦法。提示下有些函數組合寫的有點繞，如果對於部分用到的函數不夠熟悉可能需要一段時間來理解消化，有條件的朋友可以照著流程嘗試一遍，暫時沒有條件的朋友也可以收藏起來待需要時使用。

情況一 使用的函數：ROW,SMALL,OFFSET

如下圖，如何由原始清單中（如下圖左），挑出符合指定內容的清單？

【公式設計與解析】

如果不想大費周章的寫公式，篩選工具是你很好的選擇，而且很容易即可完成。本篇則是要以撰寫函數公式來完成這個工作。

單元格F4：{=IFERROR(OFFSET($D$1,SMALL(IF($B$2:$B$16=$F$2,

ROW($B$2:$B$16),""),ROW(1:1))-1,0),"")}

這是數組公式，輸入完成要按 Ctrl+Shift+Enter 鍵，Excel 會自動加上「｛｝」。

複製單元格F4，貼至單元格F4:F16。

(1) IF($B$2:$B$16=$F$2,ROW($B$2:$B$16),"")

在數組公式中判斷單元格F2的內容是否和單元格B2:B16中的每一個單元格的內容相符，若是，則傳回該單元格的列號；否則傳回空字元串。在數組公式中，ROW($B$2:$B$16)＝{2,3,4, ... , 14,15,16}。

(2) SMALL(第(1)式,ROW(1:1))

利用 SMALL 函數找出第(1)式傳回的列號之最小值。其中，ROW(1:1)向下複製時，ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→....。可以分別取出第 1,2,3,… 較小值。

(3) OFFSET($D$1,第(2)式-1,0)

將第(2)式依序取出的列號，置入 OFFSET 函數中，得到對應的單元格內容。

(4) IFERROR(第(3)式,"")

因為第(3)式當公式向下複製時，有可能傳回錯誤訊，所以利用 IFERROR 函數將錯誤訊息以空字元串顯示。

所以，當單元格F2改變時，可以顯示對應的列表。

情況二 使用的函數：ROW,COLUMN,INT,MOD,OFFSET

【公式設計與解析】

單元格B15：

{=IFERROR(OFFSET($B$1,SMALL(IF($A15=$A$2:$A$11,ROＷ($A$2:$A$11),

""),INT((COLUMN(A:A)+1)/2))-1,MOD(COLUMN(A:A)-1,2)),"")}

這是數組公式，輸入完成要按 Ctrl+Shift+Enter 鍵，Excel 會自動加上「｛｝」。

複製單元格F15，貼至單元格F15:I17。

公式解釋請參考第一個案例。

以下來解釋和上一個案例情況不同之處：

(1) INT((COLUMN(A:A)+1)/2)

公式向右複製時，INT((COLUMN(A:A)+1)/2)會產生：1, 1, 2, 2, 3, 3, …。

(2) MOD(COLUMN(A:A)-1,2)

公式向右複製時，MOD(COLUMN(A:A)-1,2)會產生：0, 1, 0, 1, 0, 1, ….。

公式細節請參考第一個案例的圖片。

情況三 使用的函數：ROW,SMALL,OFFSET,YEAR,MONTH

（如下圖） 分年／分月篩選數據

篩選指定年份

【公式設計與解析】

單元格F2：{=IFERROR(OFFSET(A$1,SMALL(IF(YEAR(日期)=$E$2,ROW(日期),

""),ROW(1:1))-1,0),"")}

這是數組公式，輸入完成要按 Ctrl+Shift+Enter 鍵，Excel 會自動加上「｛｝」。

複製單元格F2，貼至單元格F2:H16。

公式原理：

請參考上兩個案例。其中差異之處，在於條件的設定：YEAR(日期)=$E$2，利用 YEAR 函數篩選『年』。

2. 篩選指定年／指定月

【公式設計與解析】

單元格F2：{=IFERROR(OFFSET(A$1,SMALL(IF((YEAR(日期)=$E$2)*(MONTH(

日期)=$E$4),ROW(日期),""),ROW(1:1))-1,0),"")}

這是數組公式，輸入完成要按 Ctrl+Shift+Enter 鍵，Excel 會自動加上「｛｝」。

複製單元格F2，貼至單元格F2:H16。

公式原理同前面兩個案例情況。其中差異之處，在於條件的設定：(YEAR(日期)=$E$2)*(MONTH(日期)=$E$4)，利用 YEAR 函數篩選『年』和 MONTH 函數篩選『月』。其中的『*』運算符，相當於執行邏輯 AND 運算。