輸入關(guān)鍵字智能創(chuàng)建下拉菜單

馬震安

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

1. 實(shí)現(xiàn)下拉菜單動態(tài)擴(kuò)展

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

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

2. 智能獲取當(dāng)前輸入內(nèi)容

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

3. 動態(tài)篩選提取數(shù)據(jù)

新建一個名稱為“客戶列表”的工作表，點(diǎn)擊“公式”選項(xiàng)卡中的“名稱管理器”，在彈出的窗口中點(diǎn)擊“新建”，在新彈出窗口的名稱處輸入“客戶列表”，引用位置處輸入“=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，當(dāng)彈出警告提示時，直接確定即可；將數(shù)組公式填充到A80（可根據(jù)客戶名稱中的記錄估算篩選結(jié)果進(jìn)行填充），這樣就能將根據(jù)輸入關(guān)鍵字在“客戶名稱”工作表中篩選出的客戶全稱添加到“客戶列表”的A2單元格及以下單元格中（圖5）。

函數(shù)解釋：

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

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

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