[教學] Excel 簡易快速搜尋下拉選單

大家都習慣搜尋引擎（Google , Bing , Yahoo ...etc）在輸入關鍵字時會自動列出相關的字串吧？

而在 Excel 當中，大家也已經知道使用 資料驗證 功能來產生下拉式選單了。
但是要如何使用快速搜尋這樣的功能呢？那就要來學一下啦！

※ 本文使用 Microsoft Office Excel 2013 進行講解。

首先，你必須準備３個工作表 (Sheet)。

工作表一：資料來源的工作表　　　　　／本文訂定該工作表名為：資料
工作表二：需要下拉選單的工作表　　　／本文訂定該工作表名為：應用
工作表三：輔助篩選功能的工作表　　　／本文訂定該工作表名為：篩選

以下正式進入使用教學　↓

步驟一：
如果你要搜尋的是繁體中文字，請將下列公式放到 篩選!A1：

=LOOKUP(REPT("龤",255),應用!B:B)

如果你要搜尋的是簡體中文字，請將下列公式放到 篩選!A1：

=LOOKUP(REPT("龠",255),應用!B:B)

如果你要搜尋的是英文字，請將下列公式放到 篩選!A1：

=LOOKUP(REPT("z",255),應用!B:B)

※ 本文使用搜尋英文字的功能。

其中有不同的字，代表各語系文字中提取的最大值。

※ 函數說明請參閱：http://gb.twbts.com/index.php?topic=2064.0

步驟二：
將下列公式複製並貼到 篩選!B1 ，然後記得按下 Ctrl + Shift + Enter 產生陣列公式 （頭尾增加 {}的符號）

=IF(SUM(--ISNUMBER(SEARCH(A$1,資料!B$1:B$100)))<ROW(),"",INDEX(資料!B:B,SMALL(IF(ISNUMBER(SEARCH(A$1,資料!$B$1:$B$100)),ROW($A$1:$B$100)),ROW())))

公式內容中的 100 可以依據你的資料大小來進行調整。
（如果你的資料大於100筆，則可以將其調整更大的數字）



步驟三：
進行表格的複製填滿，就是滑鼠移到儲存格右下方變成十字，然後按住左鍵下拉至一定格數。

※ 本文下拉至 101 格。

步驟四：
複製下列公式。

=OFFSET(INDIRECT("篩選!B1"),,,SUMPRODUCT(--(INDIRECT("篩選!B1:B100")<>"")))

步驟五：
進入 應用 工作表，然後把整個 B 欄 選擇起來（點選 B 左鍵）

步驟六：
進入上方工具列資料的頁籤，看到資料工具內的資料驗證功能，按一下。

步驟七：
儲存格內允許選擇 清單
將方才步驟五的公式貼入 來源 內

跳到錯誤提醒的頁籤中，把打勾取消（輸入的資料不正確時顯示警訊）

然後按下確定退出畫面。



步驟八：

這時後回到 應用 的工作表之中，你會看到 B 欄位可以進行下拉動作了。
但是這時必須輸入關鍵字 然後按 下拉按鈕，才能幫你進行篩選出相關的內容。

以上，這樣就大功告成囉！

附上範例檔案供大家參考。

※ 註記：本文功能僅蒐尋最下方的值，因此回頭修改上面的值不會有效果。

→ 點我下載 ←

※ 本文僅講述功能應用，若要了解更深入，請參閱下列網址。

http://forum.twbts.com/viewthread.php?tid=3542

下拉選單 Plus！

若是你使用以上功能時，出現下列狀況：

狀況一：我想要沒輸入關鍵字的時候看到原始的清單。

狀況二：我的欄位有一些需要保持空白，但是最後輸入的關鍵字就無法使用下拉選項。

狀況三：我想要修改之前寫過的資料但是卻無法進行下拉選單功能。

...

以上這些狀況就需要 Excel 的巨集功能才能解決。

解決方式如下：

步驟Ａ：

將下列公式替代上面的 步驟四 的公式內容，然後依序執行步驟五～步驟七。

=IF(INDIRECT("篩選!A1")="",OFFSET(INDIRECT("資料!B2"),,,SUMPRODUCT(--(INDIRECT("資料!B1:B100")<>""))-1),OFFSET(INDIRECT("篩選!B1"),,,SUMPRODUCT(--(INDIRECT("篩選!B1:B100")<>""))))

步驟Ｂ：

滑鼠對著 應用 工作表按下右鍵，然後點選 檢視程式碼 。

步驟Ｃ：

將下列程式碼複製並貼上 程式碼 ，然後關掉視窗。

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 2 And Selection.Cells.Count = 1 Then '此處 Column=2 的 2 是假設在B欄輸入,若輸入區為C欄請改=3....依此類推
Sheets("篩選").Range("A1").Formula = "=" & ActiveSheet.Name & "!" & Target.Address & "&"""""
End If
End Sub

接下來就是回到 步驟八，開始使用囉！

附上 Plus 版本的範例下載

  → 點我下載 ←

@Neo Chen