​​Excel技巧，输入首字母，自动出现对应的下拉列表!

在Excel中设置数据验证，可以规范数据的录入，提高数据录入效率。今天就和大家分享函数公式在数据验证中应用的一个示例：在单元格中输入一个字母，就可以出现以该字母作为首字母的单词列表。这种设置可以缩小数据验证的列表范围，避免从很长的列表中寻找需要录入的数据。
1

效果展示

如下图所示，在A2单元格输入“a”，下拉列表出现以“a”开头的英文单词。

输入“b”，下拉列表出现以“b”开头的英文单词。

如果什么都不输入，下拉列表为数据源中的全部单词。

操作步骤
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单元格区域。将该函数组合返回的结果作为数据验证对话框中“序列”的数据来源，当输入字母后，下拉列表就会出现首字母为该字母的英文单词。

以上就是今天要分享的技巧，你学会了吗？若有什么问题，欢迎在下方留言。

学习资料见知识星球。

以上就是今天要分享的技巧，你学会了吗？若有什么问题，欢迎在下方留言。

快来试试吧，小琥 my21ke007。获取 1000个免费 Excel模板福利​​​​！

更多技巧， www.excelbook.cn

欢迎 加入 零售创新 知识星球，知识星球主要以数据分析、报告分享、数据工具讨论为主；