【原】輸入首字母，自動出現對應的下拉列表

1、如下圖所示，“數據源”工作表的A2:A12為制作下拉列表的數據源。其中，首字母相同的單詞必須排列在一起。

2、選中“制作下拉菜單”工作表中需要設置搜索式下拉菜單的單元格，單擊【數據】-【數據驗證】，打開數據驗證對話框。

（1）【允許】選擇“序列”；

（2）【來源】輸入公式：=OFFSET(數據源!$A$1,MATCH($A2&"*",數據源!$A$2:$A$12,0),0,COUNTIF(數據源!$A$2:$A$12,$A2&"*"))

（3）【出錯警告】選項卡中取消勾選“輸入無效數據時顯示出錯警告”。如果不取消勾選此項，當輸入首字母后，單擊單元格右小角的三角符號，Excel會出現錯誤提示。

單擊確定，即可完成操作。

公式解析：

（1）OFFSET函數可以返回一個引用。比如，OFFSET($A$1,4,0,1)指以A1單元格為起點，向下移動4行，來到A5單元格；像左移動0列，仍然在A5單元格；返回1行1列的單元格，仍然是A5單元格。所以該公式最終返回的結果是A5單元格的“back”。

（2）MATCH函數作為OFFSET函數的第二個參數，指從“數據源”工作表的A1單元格向下移動多少行。如果在“制作下拉菜單”工作表的A2單元格輸入字母“b”，MATCH($A2&"*",數據源!$A$2:$A$12,0)返回“4”，也就是A2:A12中首字母為“b”的單詞第一次出現的位置。

（3）COUNTIF函數作為OFFSET函數的第4個參數，指返回的單元格區域共有幾行。如果在“制作下拉菜單”工作表的A2單元格輸入字母“b”，則COUNTIF(數據源!$A$2:$A$12,$A2&"*")返回“2”，即A2:A12中首字母為“b”的單詞數量為2。

（4）綜上，當在“制作下拉菜單”工作表的A2單元格輸入字母“b”，則OFFSET+MATCH＋COUNTIF函數組合返回的結果是A5:A6單元格區域。將該函數組合返回的結果作為數據驗證對話框中“序列”的數據來源，當輸入字母后，下拉列表就會出現首字母為該字母的英文單詞。

OFFSET函數使用示例：

【Excel教程】OFFSET函數介紹及實例

點個在看你最好看