輸入關鍵字智能創建下拉菜單

馬震安

客服人員每天要接到許多業務咨詢信息，需要對客戶名稱、咨詢問題等逐一進行記錄，在Excel工作表中已經存放了一些客戶全稱的情況下，如果想在記錄時輸入客戶的部分名稱后，能夠在形成的下拉菜單中顯示出包含該部分名稱的所有客戶的全稱供選擇，同時還要支持下拉菜單動態更新（即當在存有客戶全稱表中添加新客戶名稱時，下拉菜單也隨之更新）（圖1），具有這樣功能的工作表如何才能實現？下面是Excel 2016中的具體實現方法。

1. 實現下拉菜單動態擴展

在Excel工作表中，一般情況下制作下拉菜單需要選定相應單元格后，點擊“數據”選項卡中的“數據驗證”，在彈出窗口的“設置”選項卡中，“驗證條件→允許”處選擇“序列”，來源處選擇相應單元格內容或輸入所要顯示的列表條目（列表條目用英文狀態下的逗號隔開），這樣制作的下拉菜單，當在所選單元格下增加新內容時，下拉列表并不會增加，這在實際使用中很不理想。要想使下拉菜單根據新增內容進行動態擴展，可以使用函數配合數據驗證。

首先，點擊“公式”選項卡中的“定義名稱”，在彈出窗口的名稱處輸入“客戶全稱”，在引用位置處輸入“=offset（客戶名稱！$A$2，，，counta（客戶名稱！$A：$A）-1）”；然后，再選定相應單元格（如D2：D27），點擊“數據驗證”，在“驗證條件→允許”處選擇“序列”，將光標點到來源處，按F3鍵，在彈出窗口選擇“客戶全稱”。這樣，當在A列增加或刪除客戶名稱時，下拉列表也跟著動態更新（圖2）。

2. 智能獲取當前輸入內容

一般情況下，在記錄客戶名稱時，不輸入客戶全稱，這就需要工作表能夠及時捕捉到輸入的內容是什么。接下來說明工作表是如何智能獲取當前輸入內容的。在工作表的F1單元格輸入“=CELL（"contents"）”，回車后會出現警告提示，直接確定，F1單元格的值就會變成0。這時，在任意單元輸入內容，F1單元格就會捕獲到輸入的內容（圖4）。

3. 動態篩選提取數據

新建一個名稱為“客戶列表”的工作表，點擊“公式”選項卡中的“名稱管理器”，在彈出的窗口中點擊“新建”，在新彈出窗口的名稱處輸入“客戶列表”，引用位置處輸入“=offset（客戶列表！$A$2，，，countif（客戶全稱，"*"&cell（"contents"）&"*"））”；選定“客戶列表”這個工作表的A2單元格，在公式編輯欄輸入“=INDEX（客戶名稱！A：A，SMALL（IF（ISNUMBE R（FIND（CELL（"contents"），客戶全稱）），ROW（客戶全稱） ， 4 ^ 8） ， R O W （ A1））） &" "”，同時按下Crtl+Shift+Enter，當彈出警告提示時，直接確定即可；將數組公式填充到A80（可根據客戶名稱中的記錄估算篩選結果進行填充），這樣就能將根據輸入關鍵字在“客戶名稱”工作表中篩選出的客戶全稱添加到“客戶列表”的A2單元格及以下單元格中（圖5）。

函數解釋：

⑴ “=offset（客戶列表！$A$2，，，countif（客戶全稱，"*"&cell（"contents"）&"*"））”，countif根據輸入的內容在客戶全稱中統計符合包含輸入內容的記錄個數，offset是從客戶列表中篩選出符合條件的記錄；

⑵ “=INDEX（客戶名稱！A：A，SMALL（IF（ ISNUMBER（FIND（CELL（"contents"），客戶全稱）），ROW（客戶全稱），4^8），ROW（A1）））&""”，FIND是根據輸入內容在客戶全稱中進行查找，如果找到就返回相應的位置，找不到就返回一個錯誤，為了兼容這個錯誤需要用ISNUMBER，即如果找到就返回TRUE，找不到就返回FALSE；ROW就是如果找到就返回到該記錄相應的行；SMALL能夠逐一提取找到的記錄。

最后，在“來電記錄”工作表中，選中“客戶全稱”這列，點擊“數據”選項卡中的“數據驗證”，在彈出窗口的“設置”選項卡中，“驗證條件→允許”處選擇“序列”，將光標點到來源處，按下F3鍵，在彈出的粘貼名稱窗口中選擇“客戶列表”；在“出錯警告”窗口中，去掉“輸入無效數據時顯示出錯警告”前面的勾選（圖6）。如此，就完成了整個智能化表格的制作。