|
之前有介绍了数据有效性工具（Excel技巧应用篇：数据有效性工具），今日的内容可以说是上一篇文章的延续，也是与数据有效性相关，但因为思路稍稍复杂一点且涉及函数较多，特意单独拿出来介绍。 所谓“根据输入的关键字动态更新下拉框内容”，即在下拉框单元格输入关键字，下拉框便自动筛选，只显示包含关键字的选项供选择。 譬如“产品”下拉框中原本有众多产品名称选项，需要滚动条浏览选择。
在单元格输入“产品5”之后，下拉框中便只显示包含有“产品5”的产品名称供选择。
接下来分步骤演示如何实现。 第一步：整理产品名称列表，并创建一个叫做“产品名称”的名称。点击“公式”选项卡，点击“名称管理器”按钮，在弹出的对话框中点击“新建”按钮。
在“新建名称”对话框中，名称处键入“产品名称”，引用位置处键入“=产品列表!$A$2:$A$26”，注意没有包括第一行的标题。 第一个名称便建立好了（关于名称，请参阅Excel中越用越香的“名称”工具）。
第二步：建立辅助列，根据用户的录入动态生成一个产品列表。在产品名称旁新建“动态产品名称”列，在单元格B2键入公式“=INDEX(A:A,SMALL(IF(ISNUMBER(FIND(CELL("contents"),产品名称)),ROW(产品名称),4^8),ROW(A1)))&""”并下拉复制。编辑栏的公式两侧有一对大括号是数组公式的标记。
CELL("contents")返回引用单元格的格式、位置或者内容等信息，第一个参数表示返回何种信息，第二个参数指定引用地址，如果第二个参数缺省则返回工作薄中最后更新的单元格的相关信息。"contents"表示返回单元格的内容信息，我们用此函数返回用户录入的关键字。 FIND函数是在第二个参数字符串中查找第一个参数字符串，如果能找到返回第一个参数字符串在第二个参数字符串的起始位置即一个数值，否则返回错误值。本公式中，第二个参数“产品名称”是刚刚建立的名称，是一个数组，第一个参数会逐一与数组中的每一项匹配，然后返回一个包含了数值和错误值的数组。 ISNUMBER函数将FIND函数返回的数组转化为一个逻辑值数组，即能找到关键字的产品名称为TRUE，否则为FALSE。 IF函数根据ISNUMBER函数返回的逻辑值执行不同操作，TRUE返回ROW(产品名称)，即返回能找到关键字的产品名称在工作表中的行号，否则返回一个极大值“4^8（4的8次方）”，极大值处一般不会有数据录入，因此不会影响结果。最终IF函数返回一个关键字产品名称单元格行号和极大值组成的数组。 SMALL函数返回数组中第k大的值，第一个参数表示数组，公式中即IF函数返回的数组，第二个参数表示第k大，ROW(A1)返回引用位置的行号，因为是相对引用公式向下复制的时候，行号会递增，这样就能提取所有关键字产品名称的行号。注意ROW(A1)，参数必须是第一行的单元格，这样才能保证从“1”开始提取满足条件的数据。 INDEX函数返回区域内第k行第k列交叉单元格的内容，如果是单列数组譬如本例子，则返回第k行的内容。公式中的第一个参数为“A:A”，这里不能用名称“产品名称”代替。主要原因：SMALL函数返回的是单元格的行号，譬如“产品1aaaaaaa”的行号是“2”，而INDEX函数返回数组等于序号的值，如果用名称“产品名称”作为INDEX的第一个参数，SMALL函数返回值作为第二个参数，“2”返回的是“产品2aaaaaaa”。 “&""”是为了容错。 最后特别提醒一下，因为是数组公式，需要CTRL+SHIFT和Enter三键确认（关于数组公式，请参阅知道这些Excel数组概念和运算规则，数组公式就豁然开朗了）。 这样辅助列就建好了，在旁边录入一个“产品1”测试一下。
第三步：创建名称“产品列表”，方便设置数据有效性。引用位置键入公式“=OFFSET(产品列表!$B$2,,,COUNTIF(产品名称,"*"&CELL("CONTENTS")&"*"))”
OFFSET函数是一个十分有用的引用函数（关于OFFSET函数，请参阅掌握Offset()函数，轻松实现Excel动态图表），它按照参数的偏移值返回一个新的引用。这里，OFFSET函数的第二和第三个参数为空，只用“，”分开。 CELL函数的功能同上。 COUNTIF函数返回第一个参数中满足第二个参数条件的单元格数目，第一个参数是名称“产品名称”，第二参数中用了通配符“*”，因此只要包含CELL("CONTENTS")即关键字的产品名称都是符合条件的。COUNTIF函数的返回值是OFFSET函数的第三个参数，即定义引用位置的行数。 最后一步：设置数据有效性。选中单元格G2或者G列，点击“数据”选项卡，点击“数据有效性”，在“数据有效性”对话框中，“允许”处选择“序列”，来源处键入“=产品列表”，产品列表即刚刚创建的名称。
这样，动态下拉框便完成了。 |
|
|
