通过关键字词制作动态下拉菜单

 

问题描述：输入关键词，在下拉菜单里只显示所有含有此关键字词的内容。

在这里但我们用公式，是可以达到目的。并且，公式中的函数，都是常用的函数。并且，这种输入关键字词可进行模糊查找，相当实用。

且看小哇用常用函数完成输入关键字的下拉菜单如何制作的？

下表是某县所有学校的名称(简称），现在我们通过输入关键字，含有关键字的所有学校都显示在下拉菜单里。如果不输入关键字，下拉菜单显示所有学校：

不输入关键字词，选择下拉时，估计都把你眼睛都看花了。如果输入关键字，是不是省事多了？

来看看小哇是如何一步一步由简到繁的完成的。

本例建2个工作表，下拉菜单工作表是完成下拉菜单的，在此工作表A2制作下拉菜单。源数据工作表是存放源数据与动态数据的，此表A列存放源数据学校名称，D列存放动态显示数据。

1、定义名称

定义一个“学校”名称。依次点击公式——定义名称，在“名称”后面输入“学校”，引用位置输入以下公式：=OFFSET(源数据!$A$1,1,0,COUNTA(源数据!$A:$A)-1)。这一步也可以不做，在以后步骤里直接用一个区域来代替。

这样当删减增加学校时，会自动显示刷新后的学校名称。这里定义名称的目的是便于以后引用。

2、完成关键字或词的动态显示

也就是说，当输入关键字或词时，所有含有这个关键字的学校都会在动态显示区域内显示。这是本例最难的地方。小哇一步一步的从简到繁来讲解。

第一步：在D2中输入公式：=FIND(下拉菜单!$A$2,学校)公式意思是在下拉菜单工作表的A2输入关键字，用函数找到在源数据里的位置。因为源数据是很多学校，所以此时显示肯定没有结果。

第二步：继续嵌套一个逻辑函数：=ISNUMBER(FIND(下拉菜单!$A$2,学校))。ISNUMBER函数是用来判断第一步的结果是否是数值。

第三步：继续在第二步基础上嵌套一个逻辑函数if：=IF(ISNUMBER(FIND(下拉菜单!$A$2,学校)),row(学校))。意思就是：如果是一个数值，则返回源数据对应的行号（row（学校））。

第四步：第三步返回的是通过FINd查找到的关键字的位置，它是一个数值，通过if返回到对应的行号。现在继续嵌套一个函数small找到这所有是数值的行号中最小的一个行号：=SMALL(IF(ISNUMBER(FIND(下拉菜单!$A$2,学校)),ROW(学校)),ROW(A1))。然后三键同时按下（Ctrl+Shift+Enter）可以查找到这个最小的行号，也就是含有关键字第一个学校所在行号：

因为小哇输入的关键字是“龙”，所以龙在数据源里显示的学校所在行号最小的是13行。

如果你下拉填充，可以找到所有含有“龙”字的学校所在的行号。

第五步，现在我们嵌套一个INDEX函数，就可以找到所有对应行号的学校名称了：=INDEX(A:A,SMALL(IF(ISNUMBER(FIND(下拉菜单!$A$2,学校)),ROW(学校)),ROW(A1)))。注意三键同时按下：Ctrl+Shift+Enter

可能含有关键字的学校很多，所以在“动态显示”的D列，公式下拉填充的行数，最好与源数据行数差不多。

现在可以试试再输入其它关键字，如“安”，动态显示列显示如下：

这个公式确实非常复杂。但公式总的框架是以前我们经常用到的index+small+if+row组合。其它的都是常用的函数，多想下思考下，还是好理解的。

3、定义下拉菜单的名称

通过输入关键字，可以找到所有含有关键字的学校。现在我们要把这个动态显示出来的数据，显示在下拉菜单里。

依次点击公式——定义名称，找到定义名称对话框。名称输入“显示”（名字可以随便取），引用位置输入公式：=IF(下拉菜单!$A$2="",学校,OFFSET(源数据!$D$1,1,0,COUNTIF(学校,"*"&下拉菜单!$A$2&"*")))。确定后返回即可。

怎么理解这个公式？OFFSET(源数据!$D$1,1,0,COUNTIF(学校,"*"&下拉菜单!$A$2&"*"))中，COUNTIF(学校,"*"&下拉菜单!$A$2&"*")是找到含有关键字的数据在源数据列表中的个数。星号*是通配符，表示所有。"*"&下拉菜单!$A$2&"*"表示所有含关键字的数据。OFFSET(源数据!$D$1,1,0,COUNTIF(学校,"*"&下拉菜单!$A$2&"*"))的意思就是以源数据表D1为起始点，向下移动1向右移动0后，以此为起点，向下移动的高度是COUNTIF(学校,"*"&下拉菜单!$A$2&"*")，向右的宽度是1，也就是动态显示的含有关键字所有学校。

为了在不输入关键字时，仍然可以通过下拉菜单选择学校。所以在公式前面嵌套一个IF函数，表示意思是：当下拉菜单工作表里A2为空时，显示“学校”（名称）。

4、制作下拉菜单

回到下拉菜单工作表，单击A2，依次单击数据——数据验证，打开数据验证对话框，在允许里选择“序列”，在来源里输入：=显示。这个步骤与第3步可以全为一处，也就是可以不用定义“显示”，直接将显示那个公式写入数据验证的“来源”下的方框内。

单击数据验证对话框的“出错警告”，将“输入无效数据时显示出错警告”前的勾去掉。

最后确定返回即可。一个输入关键字词的动态下拉菜单就制作好。

最后试试看。是不是只要输入关键词后，再单击下拉三角按钮，所有包含关键词的学校都出现了？

注：如果你有更好的方法，欢迎留言交流。

作者：小哇。欢迎留言。