辦公小技巧：制作模糊查詢效果Excel下拉菜單

溫暖南方 2018-12-20

展開全文

在Excel單元格中輸入特定范圍的內(nèi)容時，一般會利用數(shù)據(jù)有效性生成下拉列表的方式進行，但如果源列表的內(nèi)容太多（比方說有數(shù)百個），這時再通過下拉列表查找需要的值就很不方便了。

如果采用智能感知的模糊查詢下拉列表菜單輸入，隨著回車之前輸入字符的增加，列表中可供選擇的項目在逐漸減少，這樣就更便于選擇。那么這樣的效果如何實現(xiàn)呢？其實，利用輔助列、CELL函數(shù)，再結(jié)合數(shù)據(jù)有效性，這樣的模糊查詢效果下拉菜單就很容易實現(xiàn)（圖1）。

首先，在名單所在工作表中插入一個輔助列，在輔助列的第一個單元格中輸入“=INDEX(B:B,SMALL(IF(ISNUMBER(FIND(CELL('contents'),B$2:B$13)),ROW($2:$13),4^8),ROW(A1)))&''”，然后，同時按下Ctrl、Shift、Enter，使得輸入的內(nèi)容變成數(shù)組公式，拖動鼠標向下填充（圖2）。

小提示：

公式中CELL('contents')省略了第二參數(shù)，直接獲得最后更改單元格的值；FIND(CELL('contents'),B$2:B$13)是查詢CELL('contents')的結(jié)果是否在B2:B13單元格區(qū)域存在，如果存在則返回一個位置數(shù)值，如果不存在則返回錯誤值；IF(ISNUMBER(FIND(CELL('contents'),B$2:B$13)),ROW($2:$13),4^8)中，ISNUMBER函數(shù)判斷FIND函數(shù)的結(jié)果是否為數(shù)值，如果為數(shù)值，則返回相關值所對應的行號，如果不是數(shù)值，則返回值4^8；SMALL函數(shù)對IF函數(shù)的結(jié)果進行從小到大取數(shù)，隨著公式的向下填充，依次提取第1、2、3、4、5……N個最小值，依次得到包含最后更改單元格值的單元格的行號；INDEX函數(shù)根據(jù)SMALL函數(shù)返回的索引值，得出結(jié)果。

接下來，選取需要設置下拉菜單的單元格區(qū)域，比如E2:E10；選擇“數(shù)據(jù)”選項卡，點擊“數(shù)據(jù)驗證數(shù)據(jù)驗證”，在彈出窗口“設置”選項卡中，驗證條件下“允許”處選擇“序列”，“來源”處選擇C2:C13（即輔助列下的單元格區(qū)域）；再選擇“出錯警告”選項卡，將“輸入無效數(shù)據(jù)時顯示出錯警告”的勾選去掉（圖3）。