468在Excel中製作具有聯想能力的下拉清單的方法 @ 錦子老師

468	在Excel中製作具有聯想能力的下拉清單的方法
示範檔	無	範例檔	468.XLSX	結果檔	無

利用Excel的資料驗證功能，能夠建立下拉清單，方便使用者快速輸入一些常用資料。當可能輸入的資料很多時，從清單中找到需要的資料就不那麼容易了。實際上，可以通過設置獲得類似於輸入法的相關連功能，當在儲存格中輸入需要資料的前幾個字元後，在下拉清單中將只顯示以這幾個字元開頭的選項。本單元將介紹在Excel中製作具有聯想能力的下拉清單的方法。

啟動Excel並打開468.XLSX活頁簿。

點取「工作表2」工作表，選擇A1:B14儲存格範圍。

按Ctrl + L鍵開啟「建立表格」對話方塊，點取「請問表格的資料來源」欄位，會發現已經輸入了框選的儲存格範圍。

點取「有標題的表格」核取方塊，使其打勾，如下圖所示。

點取「確定」鈕，建立一個表格，如下圖所示。

點取A欄任一有資料儲存格，再點取「常用 > 編輯 > 排序與篩選 > 從A到Z排序」項目，對該欄位按昇冪排序。

點取「工作表2」工作表，框選A3:A30儲存格範圍。

點取「資料 > 資料工具 > 資料驗證」鈕，開啟「資料驗證」對話方塊。

點取「設定」標籤，點取「儲存格內允許」下拉清單，選擇「清單」項目，點取「來源」欄位，輸入公式：「=OFFSET(工作表1!$A$2,MATCH(A3&"*",工作表2!$A:$A,0)-2,,COUNTIF(工作表2!$A:$A,A3&"*"))」，如下圖所示。

點取「錯誤提醒」標籤中，點取「輸入的資料不正確時顯示警訊」核取方塊，使其空白，如下圖所示。

完成設置後，點取「確定」鈕關閉對話方塊。

提示

這裡在公式中使用MATCH()函數在「工作表2」工作表的A欄中定位「工作表1」工作表中A3儲存格字元開頭記錄所在的位置，同時去掉A1:A2這兩個儲存格的記錄，獲得的值作為OFFSET()函數偏移的行數。使用COUNTIF()函數統計「工作表2」工作表A欄中名稱和「工作表1」工作表A3儲存格字元開頭相符的資料的個數，將獲得值作為OFFSET()函數傳回的高度。另外要注意，這裡必須取消「輸入的資料不正確時顯示警訊」核取方塊的勾選，否則在輸入部分字元後會報錯將無法使用下拉清單選擇。