vba Excel下拉+搜索功能
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18605402/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel Drop Down + search function
提问by user2629807
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.
我有一个下拉列表，里面有很多列表。只是搜索/向下滚动列表需要很长时间。
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.
有没有办法让它更容易？例如：我可以延长滚动范围（目前只有 8 个项目），或者通过键入项目名称来实现某种自动完成。
Its just basically to search the item easier in the drop down list. Thanks!
它只是基本上可以更轻松地在下拉列表中搜索项目。谢谢！
回答by JMax
You can use:
您可以使用：
- Cascading dropdown (such as xls cascading lookup based on pivot table style datasource?and http://www.contextures.com/xlDataVal02.html)
- Use a formula in the Named Range for your List Validation that will simulatean autocomplete
- Create a named range
Testwith this kind of formula :=OFFSET($A$2,MATCH($C$1&"*",$A:$A,0)-2,0,COUNT($A:$A)) - 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
- Then you can:
- 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
Whashingtonand so on
- start typing the text you want in cell A1, for instance
- Create a named range
- 级联下拉列表（例如基于数据透视表样式数据源的 xls 级联查找？和http://www.contextures.com/xlDataVal02.html）
- 使用命名范围中的公式进行列表验证，以模拟自动完成
Test使用这种公式创建一个命名范围：=OFFSET($A$2,MATCH($C$1&"*",$A:$A,0)-2,0,COUNT($A:$A))- 在哪里：
- 您的数据列表在 A 列中（值应按字母顺序排序）
- 您正在应用验证的当前单元格是 C1
- 然后你可以：
- 例如，开始在单元格 A1 中键入所需的文本
Wash - 然后点击下拉列表
- 下拉列表将从
Whashington等等开始
- 例如，开始在单元格 A1 中键入所需的文本
I've just translated it from one of my old workbook so tell me if anything doesn't work.
我刚刚从我的一本旧工作簿中翻译了它，所以如果有任何不起作用，请告诉我。
[EDIT] Just a quick test seem to make it work (you should also restrain the end of the list for the beauty of the formula)
[编辑] 只是一个快速测试似乎使它起作用（为了公式的美，您还应该限制列表的末尾）
