利用Excel的【數據有效性】功能製作下拉菜單，應是表哥表姐耳熟能詳的一個技能了。
當然，有些小夥伴可能還不會，不會的小夥伴估計是E界新人，是輕稚可愛的表弟表妹，是早晨八九點鐘的太陽，是祖國——咳咳，作為過來人，星光還是誠摯建議您翻閱下EH公眾號的往期文章，認真學習下【數據有效性】的相關知識——畢竟這部分知識是非常炫酷實用的。
咱們這期講的內容是也和數據有效性有關，是利用CELL函數，製作具有模糊查詢效果的動態下拉菜單。
這話到底啥意思呢，還是用動態圖說話吧。
目標
在A列某區域輸入某個關鍵詞，在數據有效性的下拉列表里，顯示出包含該關鍵詞的數據。
材料
一份名單表：
製作過程
E2單元格輸入數組公式，並向下填充到E10單元格區域：
（左右拖動查看完整公式）
這公式看起來很複雜的模樣，但倘若您學習並掌握了我們之前推送的INDEX+SMALL+IF函數套路的文章，理解起來就簡單多了。
CELL("contents")
CELL函數省略了第二參數，獲得最後更改單元格的值。
FIND(CELL("contents"),D$2:D$10)
FIND函數查詢CELL函數的結果，是否在D2:D10單元格區域存在，如存在則返回一個位置數值，相反則返回錯誤值，生成一個內存數組，比如：
{#VALUE!;4;4;2;#VALUE!;#VALUE!;……;#VALUE!}。
再來看下面這部分：
ISNUMBER函數判斷FIND函數的結果是否為數值，如為數值，則IF函數判斷為真，返回相關值所對應的行號，如否，則返回值4^8，即65536。
接下來再看一下完整公式：
SMALL函數對IF函數的結果進行從小到大取數，隨着公式的向下填充，依次提取第1、2、3、4……N個最小值，由此依次得到符合條件——包含最後更改單元格值的單元格的行號。
INDEX函數根據SMALL函數返回的索引值，得出結果。
由於CELL("contents")得到的是最後更改單元格的值，而編輯上述公式時的單元格即為最後更改內容的單元格，此時會造成循環引用，但不必理會。
公式填充至E10單元格後結果如下：
選取設置下拉菜單的單元格區域A2:A9，點擊【數據】選項卡中的【數據驗證】（13版本之前名為【數據有效性】）。
在彈出的數據驗證對話框中，單擊【設計】選項卡【允許】輸入框右側的按鈕，在下拉列表中選擇【序列】。
單擊【來源】右側的選取按鈕選擇工作表的數據區：$E$2:$E$10
單擊【出錯警告】選項卡，去掉【輸入無效數據時顯示出錯警告(S)】的勾選。
最後【確定】之，大功告成了。
結束語
撓頭……那個……對於大部分表弟表妹來說，今天分享的內容難度係數確實有些偏高，想必有不少人云里霧裡的，難點之處在於那條長長的裹腳布函數。
關於INDEX+SMALL+IF的函數套路，坊間裡戲稱萬金油套路，由此可見它作用之強大——作為早晨八九點鐘的太陽，E界未來的希望，怎能不掌握它呢？
——新的一天，兄弟姐妹們努力學習吧！
圖文作者：看見星光
留言列表