在百度搜索网站中输入搜索关键词时,百度会逐步弹出相关的词条列表。例如,输入“三”会弹出包含“三国杀”、“三维度”之类的词条列表,而输入“三星”时则弹出包含“三星S6”、“三星手机官网”之类的词条列表。

Excel的单元格是否也可以在输入字符时逐步提示呢?以图3.42所示的订单表为例,在“客户名称”工作表的A1:A23中有23个客户的详细名称,其中多数客户名称的前几个字符都是相同的,现要求在订单表的A列输入客户名称时可以产生带联想功能的下拉菜单

Excel 如何设计带联想的二级下拉菜单?-Excel22

图3.42 订单表

解题步骤

在疑难39中,使用数据验证让单元格产生了下拉列表,若修改一下验证条件和数据来源,则可以让下拉列表匹配当前已输入的字符,从而实现“联想”功能,具体步骤如下。

1.选择订单表的A2:A1000区域,然后单击功能区的“数据”→“数据验证”,弹出“数据验证”对话框。

2.将“允许”下拉列表中的默认值“任何值”修改为“序列”,然后在“来源”文本框中输入以下公式:

Excel 如何设计带联想的二级下拉菜单?-Excel22

图3.43为数据验证的设置界面。

3.打开“出错警告”选项卡,取消选择“输入无效数据时显示出错警告”复选框,操作界面如图3.44所示。

Excel 如何设计带联想的二级下拉菜单?-Excel22

图3.43 设置数据验证的条件和来源

Excel 如何设计带联想的二级下拉菜单?-Excel22

图3.44 取消出错警告

4.单击“确定”按钮保存设置,同时返回工作表界面。

5.在A2单元格录入字符“东”,然后单击单元格右方的倒三角符号,Excel会弹出“东”字开头的所有客户名称,效果见图3.45。

6.继续输入“东莞万”,然后再次单击倒三角符号,Excel会弹出以“东莞万”3字开头的所有客户名称,效果如图3.46所示。

Excel 如何设计带联想的二级下拉菜单?-Excel22

图3.45 显示“东”开头的词条列表

Excel 如何设计带联想的二级下拉菜单?-Excel22

图3.46 显示“万莞万”开头的词条列表

7.在列表中选择第3项,单元格会自动产生“东莞万江税务局”。

知识扩展

1.在实现本例的功能前,客户名称工作表中的所有名称必须预先排序。不排序的前提下无法显示完整的词条列表。

2.OFFSET 函数的功能是以指定的单元格为参照系,通过上、下、左或右执行一定量的偏移后产生新的区域引用,区域的大小可以通过第3参数和第4参数控制。其中第3参数和第4参数的默认值是1,忽略参数的值则表示1行、1列。

=OFFSET(B2,2,3,3,1)——此公式表示相对于B2单元格向下偏移2行、向右偏移3列从而形成的3行1列的区域,即E4:E6区域。

MATCH函数的功能则是搜索一个字符串在一组字符串或一行、一列中的出现顺序,要搜索的字符串允许包含通配符“*”和“?”,其中“*”代表任何长度的任意字符,“?”代表长度为1的任意字符。例如公式:

=MATCH("成都",{"重庆","广州","北京","成都","昆明"},FALSE)——计算结果是4,因为成都二字排列在“{"重庆","广州","北京","成都","昆明"}”这个数组中的第4位。

=MATCH("长*",{"重庆市","广州市","长沙市","成都市","长葛市"},FALSE)——计算结果是3,参数“长*”表示以“长”开头的任意长度、任意字符结尾的字符串,数组“{"重庆市","广州市","长沙市","成都市","长葛市"}”的长沙市和长葛市都符合条件,但是此时MATCH函数只以第一个目标为准,即长沙市所在的位置。

COUNTIF 函数用于计算一个区域中有多少个符合条件的单元格。例如,在图 3.47 中COUNTIF函数用于计算A1:A5区域包含多少个以“长”字开头、任意长度的字符结尾的单元格数量,计算结果为2,其中A3和A5单元格符合条件。

Excel 如何设计带联想的二级下拉菜单?-Excel22

图3.47 COUNTIF函数功能示意图

本例公式的含义是:以 A1 为参照点,以活动单元格的值(A2 属于相对引用,代表活动单元格,而非仅指代 A2 单元格本身,在哪个单元格中输入,A2 就代表哪一个单元格)在客户名称工作表的A1:A23区域中的位置减1作为行偏移量,以0作为列偏移量,以“客户名称”工作表的A1:A23区域中包含活动单元格的值的数量作为行数从而形成的区域。

以上解释颇为复杂,通过图片可以更快捷地了解本例公式。

为了方便查看,特将公式写在客户名称工作表中,原来公式中的A2修改为C2。完整公式如下:

=OFFSET(客户名称!$A$1,MATCH(C2&"*",客户名称!$A$1:$A$23,0)-1,0,COUNTIF(客户名称!$A$1:$A$23,C2&"*"))

图3.48中包含OFFSET函数的参数解释,以及OFFSET的引用结果。

Excel 如何设计带联想的二级下拉菜单?-Excel22

图3.48 本例公式详解

3.设置了数据验证后,默认状态下输入的值不符合需求时会弹出错误警告,但本例需要在输入部分字符后调出下拉列表,因此需要禁用错误警告。将“输入无效数据时显示出错警告”复选框即表示禁用错误警告。

4.本例公式中采用“A2&*”作为MATCH函数的搜索条件和COUNTIF函数的计数条件,因此OFFSET引用的对象是以 A2的值开头、其他任意字符结尾的单元格。不能将本例公式理解为包含引用A2的值的所有单元格,“*&A2&*”才是包含关系。

5.数据验证无法做到输入过程中自动弹出下拉列表,只在单击倒三角箭头时才会弹出。

请在封面中缝指定的QQ群下载文件和视频教材,从而方便练习,同时验证本案例中的技巧与操作结果。