下拉列表的功能都非常實(shí)用，輕輕一點(diǎn)，選擇一下，就完成了輸入，但是遇到下面這種情況，怎么辦，一個(gè)一個(gè)去找嗎？

像上圖這種情況，還不如自己手工輸入。

那么這種效果如何？

你只需要輸入簡(jiǎn)單的幾個(gè)字，就可以把范圍大幅縮小。方便多了。

下面我們看如何制作。

1、 將數(shù)據(jù)源的右邊放置一個(gè)輔助列，輔助列的作用就是讓我們?cè)谌我粏卧褫斎胛淖?，將回篩選出包含的內(nèi)容。

在G1單元格輸入公式：=INDEX(A:A,SMALL(IF(ISNUMBER(SEARCH(CELL('contents'),$A$1:$A$180)),ROW($1:$180),4^8),ROW(A1)))&''

注意：三鍵結(jié)束（ctrl+shift+回車），把公式往下拉。

現(xiàn)在我們?cè)囼?yàn)一下，隨便找一個(gè)單元格，輸入內(nèi)容，我們的輔助列是不是在變化，這就是根據(jù)你輸入的內(nèi)容進(jìn)行了篩選。

公式解讀：

SEARCH(CELL('contents'),$A$1:$A$180)，在A1：A180單元格里面查找當(dāng)前單元格的內(nèi)容，并返回你要查找內(nèi)容的位置。

IF(ISNUMBER(SEARCH(CELL('contents'),$A$1:$A$180)),ROW($1:$180),4^8)，使用if函數(shù)，將查找到的內(nèi)容返回對(duì)應(yīng)的ROW($1:$180)序號(hào)，如果沒查找到內(nèi)容，就反回4^8。這樣的結(jié)果會(huì)形成一個(gè)數(shù)據(jù)，凡是符合你輸入的條件的，就是規(guī)則的序號(hào)，不符合條件的，就顯示4^8。

SMALL(IF(ISNUMBER(SEARCH(CELL('contents'),$A$1:$A$180)),ROW($1:$180),4^8),ROW(A1))，利用SMALL函數(shù)，求出第一個(gè)最小值，其實(shí)這個(gè)最小值就是符合你條件的數(shù)據(jù)源位置。

最后，用INDEX函數(shù)根據(jù)位置引出內(nèi)容。

我們將這個(gè)公式往下拉。

即然在任一地方輸入數(shù)字都可以實(shí)現(xiàn)篩選，那我們只需要把這個(gè)篩選的內(nèi)容放入到下拉列表就可以了。

2、 下拉列表數(shù)據(jù)源設(shè)

在你需要使用下拉列表的地方設(shè)置數(shù)據(jù)驗(yàn)證-序列，在數(shù)據(jù)源位置輸入以下公式：

OFFSET(數(shù)據(jù)!$G$1,,,COUNTA(數(shù)據(jù)!$G$1:$G$50)-COUNTBLANK(數(shù)據(jù)!$G$1:$G$50))

注意：出錯(cuò)警告選項(xiàng)卡的小勾要去掉。

然后就可以應(yīng)用了，如果你有多個(gè)單元格需要這個(gè)使用，不用重復(fù)設(shè)置，只需要設(shè)置一個(gè)，復(fù)制到其它單元格就行了。

最后想說一句，這種方法，可以實(shí)現(xiàn)動(dòng)態(tài)篩選輸入，但還不太方便，如果要更方便，那就是VBA制作，下圖是VBA制作的模型，制作方法，我們下回分解。

我是EXCEL共享局，關(guān)注我，每天提升工作效率。