数据有效性做联想输入

在工作中，很多朋友一直想做这个功能：通过输入一个关键字，然后出现一个下拉菜单来选择内容。好，今天我们来做一个通过输入首字来联想输入的案例，并在后面附上原理解析，大家一起交流交流。如下图，是一个“学生姓名”表和“成绩表”表：

（姓名已经排序）

1、在【公式】选项卡下的【名称管理器】中建立一个如下的名称：

引用位置的公式为：=OFFSET(学生姓名!$A$1,MATCH(LEFT(成绩表!$A2) & "*",学生姓名!$A:$A,0)-1,,COUNTIF(学生姓名!$A:$A,LEFT(成绩表!$A2) & "*"))

2、选定“成绩表”里的A2:A8单元格区域，打开【数据有效性】窗口，并按如下设置：

（取消勾选“输入无效数据时显示出错警告”）

3、此时就大工告成了，效果如下：

原理解析：

本案例是利用名称作为数据有效性序列的来源，其中定义的名称公式中的“MATCH(LEFT(成绩表!$A2) & "*",学生姓名!$A:$A,0)”是用来定位满足以首字为开头的第一个内容出现的位置，例如“张”，满足以“张”字开头的第一个内容出现的位置是8。“COUNTIF(学生姓名!$A:$A,LEFT(成绩表!$A2) & "*")”用来统计以“张”字开头的内容在“成绩表”中有多少个，这也就是为什么一开始要对姓名排训了。是为了把同姓的人放在一起便于OFFSET函数引用。这个案例还要注意一点的是我们是通过输入首字来筛选的，故公式中用了“LEFT(成绩表!$A2)”，我们可以看到这个LEFT函数只有一个参数，在这种情况下，它表示只引用输入字符串最左边的第一个字，那么如果你想通过获取所输入字符的最前面两个字符来筛选，则只需要变通一下（LEFT(成绩表!$A2,2)）即可。

快捷查看