Excel数据有效性技巧：利用CELL函数制作模糊查询动态下拉菜单

众多亲戚都懂得在Excel里利用数据有效性来设置下拉菜单，今天我们要分享一种更为高级的技巧。这便是运用CELL函数来打造一个带有模糊搜索功能的动态下拉菜单。虽然操作起来有些繁琐，但它的实用价值非常高。

设定目标

我们要保证，在A列的特定位置输入关键词后，数据有效性选择项会展示含有该关键词的相关信息。比如，若要在众多商品名中挑选出带有“苹果”的，只需输入“苹果”，选择项就会自动列出所有包含“苹果”的商品。总之，我们的目标是让Excel的搜索功能更智能、更方便。

输入公式

在E2单元格中输入一个数组公式，然后将此公式应用到E10单元格区域。虽然这个公式看起来有些复杂，但只要你熟悉之前提到的INDEX、SMALL和IF函数组合的用法，理解起来就很容易了。比如，在一份包含多种手机型号数据的工作表中，你可以用这个公式来挑选出特定型号的手机。

CELL函数作用

使用CELL函数时，若不填第二个参数，它会展示最新编辑过的单元格数值。在Excel里，若你不断输入数据，CELL函数便能精确捕捉到你所最后修改的单元格信息，就像一位细致的侦探，精准锁定目标。

IF(ISNUMBER(FIND(CELL("contents"),D$2:D$10)),ROW($2:$10),4^8)

FIND函数判断

利用FIND函数对CELL函数的输出进行检索，核实该数值是否处在D2到D10的单元格区间内。若数值在指定范围内，将给出具体位置；若不在，将显示错误提示。此外，此操作还会生成一个临时内存数组。以处理学生成绩为例，若要快速定位特定分数段的学生，FIND函数能高效实现这一目标。

IF函数筛选

=INDEX(D:D,SMALL(IF(ISNUMBER(FIND(CELL("contents"),D$2:D$10)),ROW($2:$10),4^8),ROW(A1)))&""

FIND函数输出的若是数字，IF函数便会显示为真，并显示该数字所在的行号；若输出的不是数字，则会显示65536。在整理员工出勤记录时，这个功能能帮助我们筛选出符合要求的员工行号。

完整公式解读

SMALL函数能从IF函数的结果中挑选数字，并按顺序排列。然后，公式会从上到下一行行地填充，不断选取最小的数值，以此来找出符合条件的单元格所在的行号。INDEX函数会依据SMALL给出的索引，给出最终结果。这个过程就像在众多书籍中，通过关键词一个接一个地查找相关书籍信息。SMALL函数算出4的8次方，说明所有符合条件的行号都已选完。这时，INDEX函数可能给出空单元格。为了防止它显示零，我们用&“”符号处理，保证它显示为空。

数据验证设置

选取A2至A9的单元格区域，点击【数据】标签页中的【数据验证】按钮（旧版称为【数据有效性】）。在数据验证窗口中，切换到【设计】标签页，在【允许】输入框侧，点击右侧按钮，选择【序列】选项。随后，点击【来源】旁边的选取按钮，选取E2至E10的数据区域。取消“出错警告”标签页中“输入错误数据时显示错误提示（S）”的勾选，这样在输入不规范数据时，就不会弹出干扰操作的警告对话框了。

克服难点

今天的内容对大家来说有点难懂，特别是那个函数很复杂。但INDEX、SMALL和IF这三个函数放在一起用，特别有用，在Excel里处理数据时经常用到。比如，在处理销售和库存数据时，如果会用这些函数，工作效率就能大大提高。

学完动态下拉菜单的制作方法，大家觉得哪个步骤最难？欢迎在评论区分享你的看法。觉得教程有用，别忘了点赞和分享给更多人！