【CELL函数】制作可以搜索关键字的下拉菜单！

-01-

效果展示

下图A列是数据源，记录了一些姓名。D列是做好的可以搜索关键字的下拉列表。可以看到，当输入关键字时，下拉列表中只会出现包含关键字的姓名。

这样相当于做了一个筛选，当下拉列表的内容比较多时，通过关键字搜索，会更轻松地找到我们要选择的对象。

-02-

制作过程

下面来分享下实现这种效果的操作步骤。

1）在F列加个辅助列，在F2单元格输入下图的公式，按CTRL+SHIFT+ENTER，这时EXCEL会提示产生循环引用，没关系，点确定，向下填充。

这个公式产生循环引用的原因在于cell函数，CELL("contents")这部分，没有写第2参数，表示获取光标所在单元格（活动单元格）的内容。不清楚这个函数的用法，可以看下我的视频。

在F1单元格输入公式，F1就是光标所在的单元格，也就是F1单元格引用它自身单元格的值，所以形成循环引用。

当在D2单元格输入关键字"王"时，光标所在的单元格就是D2，此时cell函数获取的是D2单元格的内容"王"，然后用search函数在A列的每个姓名中查找"王"字，最后通过一系列的运算，把包含"王"的姓名提取到F列中。

2）选中D列的区域，点【数据】-【数据验证】，在【数据验证】的【允许】中选【序列】，来源中输入公式=OFFSET(F$1,1,,COUNTIF(F:F,"><")-1)，再点击【出错警告】，取消勾选【输入无效数据时显示出错警告】，点【确定】。

在单元格输入关键字后，点下列按钮，就只出现包含关键字的姓名了。

=OFFSET(F$1,1,,COUNTIF(F:F,"><")-1)这个公式，就是获取F列中包含姓名的区域。把这个区域当作序列的来源。

COUNTIF(F:F,"><")这个公式，是统计F列中包含汉字的单元格个数，它会把公式产生的空文本("")排除掉。因为F列的公式会产生空文本。

这个公式本来的意思是统计F列中大于"<"的有几个单元格。因为汉字大于"<"，而空文本("")小于"<"，所以COUNTIF(F:F,"><")只统计汉字的个数。再用这个个数减去1个表头的个数，就是姓名的个数。

PS：总结一下思路：

首先，通过辅助列把包含关键字的姓名筛选出来；然后，把辅助列中姓名的区域作为序列的来源。

这里获取关键字时需要用到cell函数。也就是用cell函数把所选单元格中的关键字作为条件传回到辅助列的公式中。

之所以可以用数据验证实现，就是在数据验证中写公式不会产生循环引用。想要实现这个效果，其实里面的细节有很多，大家可以体验下。

文件链接：

https://pan.baidu.com/s/1f6a6ejsGfxmNattggzXlwg

提取码：hany

有很多同学有excel函数问题会私下向我请教，由于我时间和精力有限，不能一一回复，请见谅。如果你工作中确实有很多函数问题需要咨询，可以报名我的答疑课程，包含《函数基础课程》视频，课件和一年答疑，只要99元。