在Excel中設置數據驗證，可以規範數據的錄入，提高數據錄入效率。今天就和大家分享函數公式在數據驗證中應用的一個示例：在單元格中輸入一個字母，就可以出現以該字母作為首字母的單詞列表。這種設置可以縮小數據驗證的列表範圍，避免從很長的列表中尋找需要錄入的數據。

1

效果展示

如下圖所示，在A2單元格輸入“a”，下拉列表出現以“a”開頭的英文單詞。

輸入“b”，下拉列表出現以“b”開頭的英文單詞。

如果什麼都不輸入，下拉列表為數據源中的全部單詞。

2

操作步驟

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單元格區域。将該函數組合返回的結果作為數據驗證對話框中“序列”的數據來源，當輸入字母後，下拉列表就會出現首字母為該字母的英文單詞。

更多精彩资讯请关注tft每日頭條，我们将持续为您更新最新资讯!