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

1

效果展示

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

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

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

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

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

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

2

操作步骤

1、如下图所示,“数据源”工作表的A2:A12为制作下拉列表的数据源。其中,首字母相同的单词必须排列在一起。

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

2、选中“制作下拉菜单”工作表中需要设置搜索式下拉菜单的单元格,单击【数据】-【数据验证】,打开数据验证对话框。

(1)【允许】选择“序列”;

(2)【来源】输入公式:=OFFSET(数据源!$A$1,MATCH($A2&"*",数据源!$A$2:$A$12,0),0,COUNTIF(数据源!$A$2:$A$12,$A2&"*"))

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

(3)【出错警告】选项卡中取消勾选“输入无效数据时显示出错警告”。如果不取消勾选此项,当输入首字母后,单击单元格右小角的三角符号,Excel会出现错误提示。

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

单击确定,即可完成操作。

公式解析:

(1)OFFSET函数可以返回一个引用。比如,OFFSET($A$1,4,0,1)指以A1单元格为起点,向下移动4行,来到A5单元格;像左移动0列,仍然在A5单元格;返回1行1列的单元格,仍然是A5单元格。所以该公式最终返回的结果是A5单元格的“back”。

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

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

,