Excel中制作自动缩小选择范围的下拉列表

在《Excel中制作下拉列表》文章中，介绍了利用Excel数据有效性当中的序列功能，制作下拉列表的方法，包括如何制作多级级联的下拉列表。有了下拉列表，可以从下拉列表中选择项目，这样可以提高输入的准确性和便利性。当选择项非常多时，下拉列表会非常长，选择浏览时也会非常费劲。基于此，可以考虑对下拉列表进行智能化改造，改造后的下拉列表可以根据单元格里输入的字符，在下拉列表中自动筛选出仅包含以这些字符开头的项，缩小下拉列表中的选择范围，使选择更精准、更方便。

如何制作这种可根据输入内容自动缩小选择范围的下拉列表呢？理解透下拉列表制作原理，熟练掌握Excel公式与函数的使用，其实也非常简单，基本思路如下：

[1] 在某列中输入下拉列表数据源原始数据；

[2] 将数据源原始数据按升序或降序排序；

[3] 利用offset、match和countif等函数配合，生成可根据输入内容自动缩小范围的数据源，并设置为下拉列表的数据源；

接下来就具体介绍制作方法：

[1] 原始数据源准备

输入上图所示的数据，按照B列的员工编号按升序排序，排序方法：首先，选中上述数据区域，利用“数据→排序”菜单或是“工具”栏上的升序按钮进行排序。排序以后，相同字符开头的字符串将分布在连续单元格中，便于创建数据有效性的引用序列。

[2] 利用数据有效性生成下拉列表

选中需要设置下拉菜单的单元格G2，选择“数据→有效性”，在弹出的数据有效性对话框中，选择“设置”选项卡，在“有效性条件”的“允许”处选择“序列”，在“来源”中使用以下公式：

=Offset($B$1,Match(G2&'*',$B:$B,0)-1,,Countif($B:$B,G2&'*'))

其中B列是之前排过序的数据源所在列

数据源公式说明：

• Match函数的作用：返回G2单元格输入字符串开头的第一个选择项行号6，其中“*”是通配符可以表示一个或是多个字符；

• Countif函数的作用：返回G2单元格输入字符串开头的选择项个数3；

• 数据源公式的计算：=Offset($B$1,6-1,,3)，相当于以B1单元格向下偏移5行(B6单元格)，以此单元格起始的3行单元格区域为引用范围；

• 数据源公式的作用：在B列数据源中提取以G2单元格当中字符开始的所有选项。以提取的这个区域作为数据有效性序列的引用源，就形成了一个可动态变化、自动适应单元格输入内容的下拉列表。
  

[3] 去除有效性验证的错误提示

为了在单元格当中输入不完整的字符串时，系统不会因为数据有效性的错误警告而阻止用户的输入。在“数据有效性”设置窗口选中“出错警告”选项卡，取消勾选“输入无效数据时显示出错警告”选项。

[4] 实际操作

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

上述是以输入内容开头进行选择范围的自动缩小，如果希望是选项中包含输入内容进行选择范围的自动缩小，应该怎么做呢？

基本思路大致如下：

[1] 增加辅助列，只存放包含输入内容的所有选项；

[2] 以辅助列数据作为下拉列表的数据源；

接下来继续介绍制作方法：

[1] 增加辅助列

G2单元格设置选择“部门”的下拉列表，在I列增加辅助列，存放包含G2中输入内容的部门名称(如部门名称中含“务”字)。

在I2单元格输入公式：

=Iferror(INDEX($A$2:$A$8,Small(IF(Isnumber(Find($G$2,$A$2:$A$8)),Row($1:$7),4^8),Row(1:1))),'')

按Ctrl Shift Enter变成数组公式，并从I2向I8拓展公式

[2] 用辅助列做下拉列表的数据源

G2有效性的“来源”使用以下公式：=Offset($I$2,,,Countif($I2:$I8,'>'''''))

[3] 去除有效性验证的错误提示

[4] 实际操作