跳到主要内容

Excel 中可搜索下拉列表的终极指南

添加一名作者 最后修改时间:2024-03-26

在 Excel 中创建下拉列表可简化数据输入并最大限度地减少错误。 但对于较大的数据集,滚动浏览冗长的列表会变得很麻烦。 只需键入并快速找到您的项目不是更容易吗? A ”可搜索的下拉列表”提供了这种便利。本指南将引导您完成在 Excel 中设置此类列表的四种方法。


视频


Excel 365 中的可搜索下拉列表

Excel 365 在其数据验证下拉列表中引入了一项备受期待的功能:在列表中搜索的能力。 借助可搜索功能,用户可以更有效地快速定位和选择项目。 像往常一样插入下拉列表后,只需单击带有下拉列表的单元格并开始输入。 该列表将立即过滤以匹配键入的文本。

在这种情况下,我输入 在单元格中,下拉列表会过滤掉以搜索词开头的城市 ,如 旧金山San Diego。 然后您可以使用鼠标选择结果或使用箭头键并按 Enter 键。

:
  • 我们推荐使用 搜索从每个单词的第一个字母开始 在下拉列表中。 如果您输入的字符与任何单词的起始字符都不匹配,列表将不会显示匹配的项目。
  • 此功能仅在最新版本的 Excel 365 中可用。
  • 如果您的Excel版本不支持此功能,这里我们推荐 可搜索的下拉列表 的特点 Kutools for Excel。 没有Excel版本限制,一旦启用,您只需键入相关文本即可在下拉列表中轻松搜索所需的项目。 查看详细步骤.

创建可搜索下拉列表(适用于 Excel 2019 及更高版本)

如果您使用的是Excel 2019或更高版本,也可以使用本节中的方法使下拉列表在Excel中可搜索。

假设您已使用 Sheet2(左图)的 A2:A2 范围内的数据在 Sheet8(右图)的单元格 A1 中创建了一个下拉列表,请按照以下步骤使该列表可搜索。

步骤 1. 创建一个列出搜索项的帮助列

这里我们需要一个辅助列来列出与源数据匹配的项目。 在本例中,我将在中创建辅助列 D列 of Sheet1.

  1. 选择第一个单元格 D1 在 D 列中输入列标题,例如“搜索结果” 在这种情况下。
  2. 在单元格 D2 中输入以下公式,然后按 输入.
    =FILTER(A2:A8,ISNUMBER(SEARCH(Sheet2!A2,A2:A8)),"Not Found")
:
  • 在这个公式中, A2:A8 是源数据范围。 表 2!A2 是下拉列表的位置,表示下拉列表位于Sheet2的A2处。 请根据您自己的数据进行更改。
  • 如果没有从 Sheet2 A2 的下拉列表中选择任何项目,则公式将显示源数据中的所有项目,如上图所示。 相反,如果选择了某个项目,D2 将将该项目显示为公式的结果。
步骤 2:重新配置下拉列表
  1. 选择下拉列表单元格(本例中我选择Sheet2的A2单元格),然后去选择 时间 > 数据验证 > 数据验证.
  2. 数据验证 对话框中,您需要进行如下配置。
    1. 在下面 个人设置 标签,点击 按钮在 来源 框。
    2. 我们推荐使用 数据验证 对话框将重定向到 Sheet1,选择包含步骤 2 中的公式的单元格(例如 D1),添加 # 符号,然后单击 关闭 按钮。
    3. 错误警报 标签,取消选中 输入无效数据后显示错误警报 复选框,最后单击 OK 按钮保存更改。
结果

Sheet2 单元格 A2 中的下拉列表现在可搜索。 在单元格中键入文本,单击下拉箭头展开下拉列表,您将看到列表立即经过筛选以匹配键入的文本。

:
  • 此方法仅适用于Excel 2019及更高版本。
  • 此方法一次仅适用于一个下拉列表单元格。 要使 Sheet3 中的单元格 A8 到 A2 中的下拉列表可搜索,必须对每个单元格重复上述步骤。
  • 当您在下拉列表单元格中键入文本时,下拉列表不会自动展开,您需要单击下拉箭头手动展开。

轻松创建可搜索的下拉列表(适用于所有 Excel 版本)

鉴于上述方法的各种局限性,这里有一个对您来说非常有效的工具 - Kutools for Excel's 使下拉列表可搜索、自动弹出特征。所有版本的 Excel 中均提供此功能,您可以通过简单的设置轻松地在下拉列表中搜索所需的项目。

下载并安装 Kutools for Excel, 选择 库工具 > 下拉列表 > 使下拉列表可搜索、自动弹出 启用此功能。 在里面 使下拉列表可搜索 对话框,您需要:

  1. 选择包含需要设置为可搜索下拉列表的下拉列表的范围。
  2. 点击 OK 完成设置。
结果

单击指定范围内的下拉列表单元格时,右侧会出现一个列表框。输入文本以立即过滤列表,然后选择一个项目或使用箭头键并点击 输入 将其添加到单元格中。

:
  • 该功能支持 从单词内的任意位置进行搜索。 这意味着即使您输入位于单词中间或末尾的字符,仍然会找到并显示匹配的项目,从而提供更全面和用户友好的搜索体验。
  • 要了解有关此功能的更多信息,请 访问此页.
  • 要应用此功能,请 下载并安装 Kutools for Excel 第一。

使用组合框和 VBA 创建可搜索下拉列表(更复杂)

如果您只想创建可搜索的下拉列表而不指定特定的下拉列表类型。 本节提供了另一种方法:使用带有 VBA 代码的组合框来完成任务。

假设您在 A 列中有一个国家名称列表,如下面的屏幕截图所示,现在您想将它们用作搜索下拉列表的源数据,您可以执行以下操作来完成它。

您需要在工作表中插入一个组合框而不是数据验证下拉列表。

  1. 如果 开发商 选项卡不显示在功能区上,您可以启用 开发商 标签如下。
    1. 在 Excel 2010 或更高版本中,单击 文件 > 附加选项. 而在 Excel 选项 对话框,单击 自定义功能区 在左侧窗格中。 转到自定义功能区列表框,选中 开发商 框,然后单击 OK 按钮。 看截图:
    2. 在Excel 2007中,单击 办公 按钮> Excel选项。 在 Excel选项 对话框,单击 热门服务 在左窗格中,检查 在功能区中显示“开发人员”选项卡 框,最后单击 OK 按钮。
  2. 显示后 开发商 标签,点击 开发商 > 插页 > 组合框.
  3. 在工作表中绘制一个组合框,右键单击它,然后选择 查看房源 从右键单击菜单中。
  4. 查看房源 对话框,您需要:
    1. 选择 ,在 自动选词 领域;
    2. 指定一个单元格 链接单元 领域。 在这种情况下,我们输入A12。
    3. 选择 2-fmMatchEntryNone ,在 匹配项 领域;
    4. Type 下拉列表列表填充范围 领域;
    5. 关上 查看房源 对话框。 看截图:
  5. 现在通过单击关闭设计模式 开发商 > 设计模式.
  6. 选择一个空白单元格,例如C2,输入下面的公式,然后按 输入. 他们将其自动填充句柄向下拖动到单元格 C9 以使用相同的公式自动填充单元格。 看截图:
    =--ISNUMBER(IFERROR(SEARCH($A$12,A2,1),""))
    :
    1. 12澳元 是您将其指定为的单元格 链接单元 在步骤 4 中;
    2. 完成上述步骤后,现在可以测试:在组合框中输入字母C,然后可以看到引用包含字符C的单元格的公式单元格填充了数字1。
  7. 选择单元格D2,输入下面的公式,然后按 输入. 然后将其自动填充句柄向下拖动到单元格 D9。
    =IF(C2=1,COUNTIF($C$2:C2,1),"")
  8. 选择单元格E2,输入下面的公式,然后按 输入. 然后将其自动填充手柄向下拖动到 E9 以应用相同的公式。
    =IFERROR(INDEX($A$2:$A$9,MATCH(ROWS($D$2:D2),$D$2:$D$9,0)),"")
  9. 现在您需要创建一个名称范围。 请点击 公式 > 定义名称.
  10. 新名字 对话框中输入 下拉列表 ,在 名称 框,在下面的公式中输入 框,然后单击 OK 按钮。
    =$E$2:INDEX($E$2:$E$9,MAX($D$2:$D$9),1)
    
  11. 现在,通过单击打开设计模式 开发商 > 设计模式. 然后双击组合框打开 Microsoft Visual Basic应用程序 窗口。
  12. 将下面的 VBA 代码复制并粘贴到代码编辑器中。
    VBA代码:使下拉列表可搜索
    Private Sub ComboBox1_GotFocus()
    	ComboBox1.ListFillRange = "DropDownList"
    	Me.ComboBox1.DropDown
    End Sub
  13. 其他 + Q 关闭键 Microsoft Visual Basic应用程序 窗口。

从现在开始,当在组合框中输入一个字符时,它会进行模糊搜索,然后在列表中列出相关值。

备注:您需要将此工作簿保存为 Excel 启用宏的工作簿文件,以便保留 VBA 代码以供将来使用。

最佳办公效率工具

Kutools for Excel-帮助您从人群中脱颖而出

🤖 Kutools 人工智能助手:基于以下内容彻底改变数据分析: 智能执行   |  生成代码  |  创建自定义公式  |  分析数据并生成图表  |  调用 Kutools 函数...
热门特色: 查找、突出显示或识别重复项  |  删除空白行  |  合并列或单元格而不丢失数据  |  不使用公式进行四舍五入 ...
超级VLookup: 多重标准  |  多重价值  |  跨多页  |  模糊查询...
副词。 下拉列表: 简易下拉列表  |  依赖下拉列表  |  多选下拉列表...
列管理器: 添加特定数量的列  |  移动列  |  切换隐藏列的可见性状态  将列与 选择相同和不同的单元格 ...
特色功能: 网格焦点  |  设计图  |  大方程式酒吧  |  工作簿和工作表管理器 | 资源库 (自动文本)  |  日期选择器  |  合并工作表  |  加密/解密单元格  |  按列表发送电子邮件  |  超级筛选  |  特殊过滤器 (过滤粗体/斜体/删除线...)...
前 15 个工具集12 文本 工具 (添加文本, 删除字符 ...)  |  50+ 图表 类型 (甘特图 ...)  |  40+ 实用 公式 (根据生日计算年龄 ...)  |  19 插入 工具 (插入二维码, 从路径插入图片 ...)  |  12 转化 工具 (小写金额转大写, 货币兑换 ...)  |  7 合并与拆分 工具 (高级组合行, 拆分 Excel 单元格 ...)  |  ... 和更多

Kutools for Excel 拥有超过 300 个功能, 确保只需点击一下即可获得您所需要的...

描述


Office 选项卡 - 在 Microsoft Office(包括 Excel)中启用选项卡式阅读和编辑

  • 一秒钟即可在数十个打开的文档之间切换!
  • 每天为您减少数百次鼠标单击,告别鼠标手。
  • 查看和编辑多个文档时,可将您的工作效率提高50%。
  • 为 Office(包括 Excel)带来高效的选项卡,就像 Chrome、Edge 和 Firefox 一样。
Comments (67)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Perfect idea for me. But I have a problem with the "ROWS" formula.
I mean point 8.
When I use your formula (in the drop-down list I have nothing entered, as you can see in point 8) in the first cell is "INDIA".
And pick up the cells with the "spilled" error. What I need to change for the formula to work properly.

E1 - India
E2 - #SPILL!
E3 -#SPILL!
E4 - #SPILL!
E5 - #SPILL!
E6 -#SPILL!
E7 - #SPILL!
E8 - India
E9 - Brazil
E10 - Italy
E11 - Japan
E12 - United State
E13 - Francy
E14 - Germany

You also see that there are more poems appearing than yours.
This comment was minimized by the moderator on the site
Hi Przamek PL,
Sory, I cannot reproduce the problem you mentioned. Can you provide us with your data for tesing? If you don't mind, upload your sample file here.
This comment was minimized by the moderator on the site
Thank you for your message.
I was able to run your example correctly.
I have a reflection now ...
How to apply your solution to the UseForm form?

I would like to select a person from the list in the form, then I would have information about the age of this person elsewhere in the form. Such a simple example. Difficult?
This comment was minimized by the moderator on the site
Hi Przemek PF,
This method does not work in UserForm. Sorry for the inconvenience.
This comment was minimized by the moderator on the site
Somehow excel will not let me fill in the ListFillRange with ANYTHING. so also not the DropDownList. I did all the steps but am not able to get a flashing cursor and when I type no drop down list appears. any solutions?
This comment was minimized by the moderator on the site
Hi Marloes, This problem can't be solved yet. Make sure the ListFillRange is on the same sheet as your list box. 
This comment was minimized by the moderator on the site
I've just purchased kutools to use this function. Is it possible to have two or more different searchable drop down lists (i.e. referncing different lists of valid entries) on the same sheet?
This comment was minimized by the moderator on the site
Hi Marc,The feature does not support two or more different searchable drop down lists on the same sheet. Sorry for the inconvenience.
This comment was minimized by the moderator on the site
how to use this dropdown in vba form any konw please reply
This comment was minimized by the moderator on the site
Hi, I made an action list for internal use with automatic email reminders in Excel, based on macro and vba. in a cell you select which person to send the reminder to, in a next cell you select which person to CC etc. Is it a good idea to copy this dropdownlist a few 100 times to every possible entry that I supply ? And is it possible to add a rule: Per row a particular person can only be selected once?
There are no comments posted here yet
Load More
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations