Excel下拉+搜索功能 [英] Excel Drop Down + search function

问题描述

我有一个下拉列表，里面有很多列表. 只是需要很长时间才能搜索/向下滚动列表.

I have a drop down with a lot of list in it. Its taking a long time just to search / scrolling down through the list.

有什么方法可以使它更容易?例如:我可以延长滚动范围(目前仅8个项目)，或者通过键入项目名称来扩展某种自动完成功能.

is there any way to make it easier? For example: I can extend the scroll range longer (which is currently 8 items only), or maybe some sort of AutoComplete by typing the item name.

基本上，从下拉列表中搜索内容更加容易. 谢谢！

Its just basically to search the item easier in the drop down list. Thanks!

推荐答案

您可以使用:

1. 级联下拉列表(例如基于枢轴的xls级联查找表格样式数据源?和 http://www.contextures.com/xlDataVal02.html)
2. 在命名范围"中使用公式进行列表验证，该公式将模拟自动完成

1. Cascading dropdown (such as xls cascading lookup based on pivot table style datasource? and http://www.contextures.com/xlDataVal02.html)
2. Use a formula in the Named Range for your List Validation that will simulate an autocomplete

1. 使用这种公式创建命名范围Test: =OFFSET($A$2,MATCH($C$1&"*",$A:$A,0)-2,0,COUNT($A:$A))
2. 其中:
   • 您的数据列表在A列中(值应按字母顺序排序)
   • 应用验证的当前单元格为C1

1. Create a named range Test with this kind of formula : =OFFSET($A$2,MATCH($C$1&"*",$A:$A,0)-2,0,COUNT($A:$A))
2. Where:
   • your list of data is in column A (values should be sorted alphabetically)
   • the current cell where you are applying the validation on is C1

• 开始在单元格A1中键入所需的文本，例如Wash
• 然后单击下拉列表
• 下拉列表将从Whashington开始，依此类推

• start typing the text you want in cell A1, for instance Wash
• then click on the drop-down list
• the dropdown list will begin at Whashington and so on

我刚刚从一本旧工作簿中翻译了它，所以请告诉我是否有任何问题.

I've just translated it from one of my old workbook so tell me if anything doesn't work.

只需进行一次快速测试就可以使它起作用(您还应该限制列表的结尾以获取公式的美感)

Just a quick test seem to make it work (you should also restrain the end of the list for the beauty of the formula)

这篇关于Excel下拉+搜索功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！