原标题：根据关键字动态设置数据有效性下拉列表

使用数据有效性设置的下拉列表中的数据选项，除了使用固定的选项内容，也可以使用动态数据选项。当选项较多时， 如果下拉菜单能够根据输入的关键字来筛选出相关的项显示在下拉列表中 ，将更加便于数据输入。

如下图所示，为某公司往来账目登记簿。有两个工作表组成，一个是账目表，另一个是参数表，其中参数表的A列罗列了往来公司的名称。

现在要求按照“参数表”往来公司的名称，在账目表的B列，通过“数据有效性”制作下拉列表， 并根据用户输入的关键字，挑选相关公司记录作为下拉列表的内容 。例如在账目表的B2单元格输入“石油”，则可以将所有包含“石油”的往来公司的名称挑选出来，供数据输入者选择。

把“参数表”的B列作为辅助列，B1单元格输入标题名“辅助列”；在B2单元格输入以下数组公式，按<Ctrl+Shift+Enter>，并复制填充到B20单元格。

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

CELL("contents")部分，CELL函数省略了第二参数，获得最后活动单元格的值。

FIND(CELL("contents"),A$2:A$100)部分，FIND函数查询CELL函数的结果，是否在A2:A100单元格区域存在，如果存在则返回一个位置数值，相反则返回错误值，构成一个由数值和错误值构成的内存数组。

IF(ISNUMBER(FIND(CELL("contents"),A$2:A$100)),ROW($2:$100),4^8)部分，ISNUMBER函数判断FIND函数的结果是否为数值，如为数值，则IF函数判断为真，返回相应的行号，相反则返回4^8，即65536。

SMALL函数对IF函数的计算结果依次从小到大取数，随着公式的向下填充，依次提取第1、2、3、4……n个最小值，INDEX函数根据SMALL函数的结果进行取值，即得出包含最后活动单元格内容的往来公司名称。

选中“账目表”的B2:B10单元格区域，打开【数据有效性】设置对话框，使用【序列】作为允许条件，在【来源】编辑框中输入以下公式。

=OFFSET(参数表!$B$2,,,COUNTIF(参数表!$B:$B,">"""))

OFFSET函数以“参数表!$B$2”为基点，偏移行数和偏移列数的参数值省略，表示偏移量为0，也就是在基点位置上，行和列都不偏移。

新引用的行数是COUNTIF(参数表!$B:$B,">""")的计算结果。COUNTIF函数的作用是计算参数表B列区域中大于假空（""）的个数，即实际有多少条公司名称记录，OFFSET函数就引用多少行。

继续在【数据有效性】对话框中，单击【出错警告】选项卡，取消勾选【输入无效数据时显示出错警告】命令，最后单击【确定】按钮。

设置完成后，在“账目表”的往来客户列，例如B2单元格输入关键字“石油”，单击单元格右侧的下拉箭头，在弹出的下拉列表中会出现“往来公司”中所有包含“石油”的公司名称，选择需要的公司名称即可完成往来客户录入操作。如下图所示。

欢迎朋友们分享办公技巧 返回搜狐，查看更多