所谓“自适应下拉菜单”,就是可以根据用户输入的起始字符,在下拉菜单的显示项目中自动筛选出与之匹配的项目,缩小选择范围,方便快速地选取所需项目。
录入数据时是不是方便、快速多了?下面介绍其设置方法。
使用[数据有效性]中的[序列]功能,可以在单元格内创建一个下拉菜单,在进行输入时可以在下拉菜单中选择项目。
步骤1:将需要作为选择项目的原始数据进行排序(升降序均可,本例中:标题在第3行,数据在B列、从第4行开始)
步骤2:选中需要设置下拉菜单的单元格,依次执行[数据有效性]-->[序列],在[来源]处输入以下公式:
=OFFSET(B3,MATCH(C2&"*",B:B,0)-3,,COUNTIF(B:B,C2&"*"))
该公式的原理如下:
(1) MATCH(C2&"*",B:B,0)-3 在B列中查找以C2中字符打头的项目,返回找到的第一个项目的行号
(2) COUNTIF(B:B,C2&"*") 在B列中统计C2中字符打头的项目个数
(3) 在下图的例子中:MATCH(C2&"*",B:B,0)-3=54(从B3以下偏移的行数)
COUNTIF(B:B,C2&"*")=19(符合要求的项目数)
(4) 整个公式等同于:
=OFFSET(B3,54,,19)
作用是引用一个范围,如下图
步骤3:选中[数据有效性]-->[出错警告]选项卡,取消勾选[输入无效数据时显示出错警告],点击确定。
该设置的目的是为了在单元格当中输入不完整的项目字符时,系统不会报错并阻止输入。
当然,输完部分字符后,需点击单元格右侧的倒三角形符号,系统是不会直接弹出下拉菜单的。
最终效果见文首第一张图片。
如果项目的原始数据在另一个工作表中,则需通过自定义名称的方式解决跨工作表引用的问题。比如,原始数据在sheet1工作表中,需要在sheet2工作表C2单元格中设置下拉菜单。这种情况下,上述步骤2需变动如下:
步骤2.1 选中sheet1工作表C2单元格,选择[公式]-->[定义名称],在[新建名称]--[名称]中输入“project”(或其它名称),在[引用位置]中输入以下公式: =OFFSET(Sheet1!B3,MATCH(Sheet2!C2&"*",Sheet1!B:B,0)-3,,COUNTIF(Sheet1!B:B,Sheet2!C2&"*"))
注意:此时每个单元格及单元格区域前均加上了工作表名称,C2前是“Sheet2!”,其它是“Sheet1!”
步骤2.2 选中sheet2工作表C2单元格,依次执行[数据有效性]-->[序列],在[来源]处输入以下公式:
=project
接着执行上述步骤3。效果如下
本站仅提供存储服务,所有内容均由用户发布,如发现有害或侵权内容,请
点击举报。