在Excel中制作自适应下拉菜单

开篇：润墨网以专业的文秘视角，为您筛选了一篇在Excel中制作自适应下拉菜单范文，如需获取更多写作素材，在线客服老师一对一协助。欢迎您的阅读与分享！

小王利用excel“数据有效性”当中的“序列”功能在单元格内创建了一个下拉菜单，在进行输入时可以在下拉菜单中选择项目。这样的下拉菜单可以提高输入时的准确性和便利性。

但是，由于项目非常多，制作的下拉菜单也非常长，选择浏览时费事、费时。于是，小王对他的菜单进行了智能化改造。

改造后的下拉菜单可以根据用户在单元格里输入的字符，在下拉菜单的显示项目中自动筛选出以这些字符开头的项目，缩小下拉菜单中的项目选择范围，使目标更精准，方便用户选取。小王的这种自适应下拉菜单是如何实现的呢？

对原始选择项目排序

这个过程主要是将需要作为选择项目的原始数据进行排序。首先，选中作为选择项目的数据区域，利用“数据排序”菜单或“工具”栏上的升序、降序按钮都可以进行排序。排序以后，以相同字符开头的字符串将分布在连续的单元格中，便于创建数据有效性的引用序列（图1）。

利用数据有效性生成下拉菜单

这个过程主要是利用数据的有效性来生成下拉菜单。首先，我们选中需要设置下拉菜单的单元格，然后选择“数据有效性”，在弹出的“数据有效性”对话框中，选择“设置”选项卡，在“有效性条件”的“允许”处选择“序列”，并且在“来源”中使用以下公式：

=OFFSET（$A$1，MATCH（C2&"*"，$A：$A，0）

-1，，COUNTIF（$A：$A，C2&"*"））

其中，A列是之前排过序的项目数据源所在列，C2 则是当前选中的单元格（图2）。

在上述公式中，MATCH函数的作用是返回在指定方式下与指定数值匹配的数组中元素的相应位置。MATCH（C2&"*"，$A：$A，0），这部分可以在A列中查找以C2当中字符打头的项目，返回其中找到的第一个项目的行号，其中“*”是通配符，可以表示一个或多个字符。

COUNTIF函数的作用是计算区域中满足给定条件的单元格的个数。 COUNTIF（$A：$A，C2&"*"），这部分公式在A列中统计以C2当中字符打头的项目的个数。

以图中的数据情况为例，如果我在C2单元格中输入“上”字，那么MATCH（C2&"*"，$A：$A，0）就等于5，而COUNTIF（$A：$A，C2&"*"）则等于2，所以整个公式就等效于：

=OFFSET（$A$1，5-1，，2）

而这个OFFSET函数公式的作用是形成一个引用区域，即以A1单元格向下偏移4行（A5单元格），以此单元格起始的2行单元格区域为引用范围。

由此不难看出，这个公式的整体作用就是在A列数据源中提取出以C2单元格当中字符开始的所有项目。以这个提取出来的区域作为数据有效性序列的引用源，就可以形成一个可以动态变化、自动适应单元格输入内容的下拉菜单。

去除错误提示

这个操作的目的是为了在单元格当中输入不完整的项目字符串时，系统不会因为数据有效性的错误警告而阻止用户的输入。

在“数据有效性”设置窗口选中“出错警告”选项卡，取消勾选“输入无效数据时显示出错警告”选项。

经过上面的设置，当C2单元格中输入“上”字后，点击下拉菜单，这时下拉菜单只有两个选项。选择范围缩小，这样操作更精准、更快捷（图3）。