跳到主要内容

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

在 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 一样。