怎样在同一输入框中实现自动分行（自动出现对应的下拉列表）

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