Excel中根據輸入的關鍵字動態更新下拉框內容

上一章介紹了資料有效性工具（Excel的資料有效性工具），今日的內容可以說是上一篇文章的延續，也是與資料有效性相關，但因為思路稍稍複雜一點且涉及函數較多，特意單獨拿出來介紹。

所謂「根據輸入的關鍵字動態更新下拉框內容」，即在下拉框單元格輸入關鍵字，下拉框便自動篩選，只顯示包含關鍵字的選項供選擇。

譬如「產品」下拉框中原本有眾多產品名稱選項，需要滾動條瀏覽選擇。

在單元格輸入「產品5」之後，下拉框中便只顯示包含有「產品5」的產品名稱供選擇。

接下來分步驟演示如何實現。

第一步：整理產品名稱列表，並創建一個叫做「產品名稱」的名稱。點選「公式」選項卡，點選「名稱管理器」按鈕，在彈出的對話方塊中點選「新建」按鈕。

在「新建名稱」對話方塊中，名稱處鍵入「產品名稱」，引用位置處鍵入「=產品列表!$A$2:$A$26」，注意沒有包括第一行的標題。

第一個名稱便建立好了（關於名稱，請參閱Excel中越用越香的「名稱」工具）。

第二步：建立輔助列，根據使用者的錄入動態生成一個產品列表。在產品名稱旁新建「動態產品名稱」列，在單元格B2鍵入公式「=INDEX(A:A,SMALL(IF(ISNUMBER(FIND(CELL("contents"),產品名稱)),ROW(產品名稱),4^8),ROW(A1)))&""」並下拉複製。編輯欄的公式兩側有一對大括號是陣列公式的標記。

CELL("contents")返回引用單元格的格式、位置或者內容等資訊，第一個參數表示返回何種資訊，第二個參數指定引用地址，如果第二個參數預設則返回工作薄中最後更新的單元格的相關資訊。"contents"表示返回單元格的內容資訊，我們用此函數返回使用者錄入的關鍵字。

FIND函數是在第二個參數字元串中查詢第一個參數字元串，如果能找到返回第一個參數字元串在第二個參數字元串的起始位置即一個數值，否則返回錯誤值。本公式中，第二個參數「產品名稱」是剛剛建立的名稱，是一個數組，第一個參數會逐一與陣列中的每一項匹配，然後返回一個包含了數值和錯誤值的陣列。

ISNUMBER函數將FIND函數返回的陣列轉化為一個邏輯值陣列，即能找到關鍵字的產品名稱為TRUE，否則為FALSE。

IF函數根據ISNUMBER函數返回的邏輯值執行不同操作，TRUE返回ROW(產品名稱)，即返回能找到關鍵字的產品名稱在工作表中的行號，否則返回一個極大值「4^8（4的8次方）」，極大值處一般不會有資料錄入，因此不會影響結果。最終IF函數返回一個關鍵字產品名稱單元格行號和極大值組成的陣列。

SMALL函數返回陣列中第k大的值，第一個參數表示陣列，公式中即IF函數返回的陣列，第二個參數表示第k大，ROW(A1)返回引用位置的行號，因為是相對引用公式向下複製的時候，行號會遞增，這樣就能提取所有關鍵字產品名稱的行號。注意ROW(A1)，參數必須是第一行的單元格，這樣才能保證從「1」開始提取滿足條件的資料。

INDEX函數返回區域內第k行第k列交叉單元格的內容，如果是單列陣列譬如本例子，則返回第k行的內容。公式中的第一個參數為「A:A」，這裡不能用名稱「產品名稱」代替。主要原因：SMALL函數返回的是單元格的行號，譬如「產品1aaaaaaa」的行號是「2」，而INDEX函數返回陣列等於序號的值，如果用名稱「產品名稱」作為INDEX的第一個參數，SMALL函數返回值作為第二個參數，「2」返回的是「產品2aaaaaaa」。

「&""」是為了容錯。

最後特別提醒一下，因為是陣列公式，需要CTRL+SHIFT和Enter三鍵確認（關於陣列公式，請參閱知道這些Excel陣列概念和運算規則，陣列公式就豁然開朗了）。

這樣輔助列就建好了，在旁邊錄入一個「產品1」測試一下。

第三步：創建名稱「產品列表」，方便設定資料有效性。引用位置鍵入公式「=OFFSET(產品列表!$B$2,,,COUNTIF(產品名稱,"*"&CELL("CONTENTS")&"*"))」

OFFSET函數是一個十分有用的引用函數（關於OFFSET函數，請參閱掌握Offset()函數，輕鬆實現Excel動態圖表），它按照參數的偏移值返回一個新的引用。這裡，OFFSET函數的第二和第三個參數為空，只用「，」分開。

CELL函數的功能同上。

COUNTIF函數返回第一個參數中滿足第二個參數條件的單元格數目，第一個參數是名稱「產品名稱」，第二參數中用了通配符「*」，因此只要包含CELL("CONTENTS")即關鍵字的產品名稱都是符合條件的。COUNTIF函數的返回值是OFFSET函數的第三個參數，即定義引用位置的行數。

最後一步：設定資料有效性。選中單元格G2或者G列，點選「資料」選項卡，點選「資料有效性」，在「資料有效性」對話方塊中，「允許」處選擇「序列」，來源處鍵入「=產品列表」，產品列表即剛剛創建的名稱。

這樣，動態下拉框便完成了。