根據關鍵字動態設置數據有效性下拉列表

07-02

使用數據有效性設置的下拉列表中的數據選項，除了使用固定的選項內容，也可以使用動態數據選項。當選項較多時，如果下拉菜單能夠根據輸入的關鍵字來篩選出相關的項顯示在下拉列表中，將更加便於數據輸入。

如下圖所示，為某公司往來賬目登記簿。有兩個工作表組成，一個是賬目表，另一個是參數表，其中參數表的A列羅列了往來公司的名稱。

現在要求按照「參數表」往來公司的名稱，在賬目表的B列，通過「數據有效性」製作下拉列表，並根據用戶輸入的關鍵字，挑選相關公司記錄作為下拉列表的內容。例如在賬目表的B2單元格輸入「石油」，則可以將所有包含「石油」的往來公司的名稱挑選出來，供數據輸入者選擇。

步驟1

把「參數表」的B列作為輔助列，B1單元格輸入標題名「輔助列」；在B2單元格輸入以下數組公式，按<Ctrl Shift Enter>，並複製填充到B20單元格。

=INDEX(A:A,SMALL(IF(ISNUMBER(FIND(CELL("contents"),A$2:A$100)),ROW($2:$100),4^8),ROW(A1)))&""

CELL("contents")部分，CELL函數省略了第二參數，獲得最後活動單元格的值。

FIND(CELL("contents"),A$2:A$100)部分，FIND函數查詢CELL函數的結果，是否在A2:A100單元格區域存在，如果存在則返回一個位置數值，相反則返回錯誤值，構成一個由數值和錯誤值構成的內存數組。

IF(ISNUMBER(FIND(CELL("contents"),A$2:A$100)),ROW($2:$100),4^8)部分，ISNUMBER函數判斷FIND函數的結果是否為數值，如為數值，則IF函數判斷為真，返回相應的行號，相反則返回4^8，即65536。

SMALL函數對IF函數的計算結果依次從小到大取數，隨著公式的向下填充，依次提取第1、2、3、4……n個最小值，INDEX函數根據SMALL函數的結果進行取值，即得出包含最後活動單元格內容的往來公司名稱。

步驟2

選中「賬目表」的B2:B10單元格區域，打開【數據有效性】設置對話框，使用【序列】作為允許條件，在【來源】編輯框中輸入以下公式。

=OFFSET(參數表!$B$2,,,COUNTIF(參數表!$B:$B,">"""))

OFFSET函數以「參數表!$B$2」為基點，偏移行數和偏移列數的參數值省略，表示偏移量為0，也就是在基點位置上，行和列都不偏移。

新引用的行數是COUNTIF(參數表!$B:$B,">""")的計算結果。COUNTIF函數的作用是計算參數表B列區域中大於假空（""）的個數，即實際有多少條公司名稱記錄，OFFSET函數就引用多少行。

步驟3

繼續在【數據有效性】對話框中，單擊【出錯警告】選項卡，取消勾選【輸入無效數據時顯示出錯警告】命令，最後單擊【確定】按鈕。

設置完成後，在「賬目表」的往來客戶列，例如B2單元格輸入關鍵字「石油」，單擊單元格右側的下拉箭頭，在彈出的下拉列表中會出現「往來公司」中所有包含「石油」的公司名稱，選擇需要的公司名稱即可完成往來客戶錄入操作。如下圖所示。