如何在Excel中创建下拉列表(您需要的唯一指南)

下拉列表是为用户提供从预定义列表中选择的选项的绝佳方式。

可以在获取用户填充表单的同时使用,或者在创建交互式时使用Excel仪表板

在网站/应用程序上下拉列表非常常见,对用户非常直观。

观看视频 - 在Excel中创建下拉列表

在本教程中,您将学习如何在Excel中创建一个下拉列表(只需要几秒钟来执行此操作)以及您可以与之完成的所有令人敬畏的事物。

如何在Excel中创建下拉列表

在本节中,您将学习创建Excel下拉列表的确切步骤:

  1. 使用小区中的数据。
  2. 手动输入数据。
  3. 使用偏移公式。

#1使用来自单元格的数据

假设您有一个如下所示的项目列表:

需要创建下拉列表的项目列表

以下是创建Excel下拉列表的步骤:

  1. 选择要在其中创建下拉列表的单元格。
  2. 转到数据 - >数据工具 - >数据验证。功能区中的数据验证选项的图像
  3. 在“数据验证”对话框的“设置”选项卡中,选择“列表”作为验证条件。
    • 选择列表后,将显示源字段。在Excel中创建下拉列表的数据验证设置
  4. 在源字段中,输入= $ 2美元:$ 6,或者只需单击源字段并使用鼠标选择单元格,然后单击“确定”。这将在单元C2中插入下拉列表。
    • 确保选中单元内下拉选项(默认检查)。如果此选项未选中,则单元格不会显示下拉,但是,您可以手动输入列表中的值。选择要创建下拉菜单的范围

笔记:如果要在One Go中在多个单元格中创建下拉列表,请选择要创建它的所有单元格,然后按照上述步骤操作。确保小区参考是绝对的(例如$ 2),而非相对(例如A2,或2美元或$ A2)。

#2手动输入数据

在上面的示例中,在源字段中使用单元格引用。您还可以通过手动在源字段中输入它来添加项目。

例如,假设您想要显示两个选项,是和否,在小区中的下降中。以下是如何在数据验证源字段中直接输入它:

  • 选择要创建下拉列表的单元格(本示例中的单元格C2)。
  • 转到数据 - >数据工具 - >数据验证。功能区中的数据验证选项
  • 在“数据验证”对话框的“设置”选项卡中,选择“列表”作为验证条件。
    • 选择列表后,将显示源字段。Excel下拉菜单 - 数据验证中的设置
  • 在源字段中,输入是,没有
    • 确保选中“单元格拉目”中的选项。
  • 单击确定。

这将在所选单元格中创建一个下拉列表。由逗号分隔的源字段中列出的所有项目都在下拉菜单中的不同行中列出。

在符号分隔的源字段中输入的所有项目都以下拉列表中的不同行显示。

Excel下拉菜单 -  Manual Entry Demo

笔记:如果要在One Go中在多个单元格中创建下拉列表,请选择要创建它的所有单元格,然后按照上述步骤操作。

#3使用Excel Formulas

除了从单元格中选择和手动输入数据,还可以使用源字段中的公式来创建Excel下拉列表。

返回值列表的任何公式都可用于在Excel中创建一个下拉列表。

例如,假设您具有如下所示的数据集:

需要创建下拉列表的项目列表

以下是使用excel下拉列表的步骤使用偏移功能

  • 选择要创建下拉列表的单元格(本示例中的单元格C2)。
  • 转到数据 - >数据工具 - >数据验证。功能区中的数据验证选项
  • 在“数据验证”对话框的“设置”选项卡中,选择“列表”作为验证条件。
    • 选择列表后,将显示源字段。创建Excel下拉列表的设置
  • 在“源”字段中,输入以下公式:=偏移($ 2,0,0,5)
    • 确保选中“单元格拉目”中的选项。
  • 单击确定。

这将创建一个下拉列表,列出所有水果名(如下所示)。

使用偏移功能来创建Excel下拉列表笔记如果要在ONE GOT中创建一个下拉列表,请选择要创建的所有单元格,然后按照上述步骤操作。确保小区参考是绝对的(例如$ 2),而非相对(例如A2,或2美元或$ A2)。

该公式如何运作??

在上面的情况下,我们使用偏移函数来创建下拉列表。它返回来自RA的项目列表

它返回来自A2:A6范围的项目列表。

以下是偏移功能的语法:=偏移量(参考,行,COL,[高度],[宽度])

它需要五个参数,在那里我们将引用指定为A2(列表的起点)。Rows / Cols指定为0,因为我们不希望抵消参考单元格。高度指定为5,因为列表中有五个元素。

现在,当您使用此公式时,它会返回一个包含A2:A6中五个果实列表的数组。请注意,如果在单元格中输入公式,请选择它并按F9,您会看到它返回水果名称数组。

偏移函数返回一组项目

在Excel中创建动态下拉列表(使用偏移量)

可以扩展使用公式创建下拉列表的上述技术,以创建动态下拉列表。如果使用偏移函数,如上所示,即使您将更多项目添加到列表中,下降也不会自动更新。每次更改列表时都必须手动更新它。

这是一种使其动态的方法(并且只不过是公式中的小调整):

  • 选择要创建下拉列表的单元格(本示例中的单元格C2)。
  • 转到数据 - >数据工具 - >数据验证。
  • 在“数据验证”对话框的“设置”选项卡中,选择“列表”作为验证条件。选择列表后,将显示源字段。
  • 在“源”字段中,输入以下公式:=偏移量($ 2,0,0,COUNTIF($ 2 $ 2:$ 100,“<>”))
  • 确保选中单元格下拉下降选项。
  • 单击确定。

在这个公式中,我用countif取代了参数5($ 2:$ 100,“<>”)。

Countif函数计数A2:A100范围内的非空白单元。因此,偏移函数调整为包括所有非空白单元。

一种动态下拉列表如何在Excel中工作的演示

笔记:

  • 为此工作,填充的细胞之间不能没有任何空白单元。
  • 如果要在ONE GOT中创建一个下拉列表,请选择要创建的所有单元格,然后按照上述步骤操作。确保小区参考是绝对的(例如$ 2),而非相对(例如A2,或2美元或$ A2)。

在Excel中复制粘贴下拉列表

您可以将带有数据验证的单元格复制到其他单元格,并且它也将复制数据验证。

例如,如果您在单元C2中有一个下拉列表,并且您希望将其应用于C3:C6,只需将单元格C2复制并粘贴在C3:C6中。这将复制下拉列表并使其在C3:C6中可用(随着下拉目,它也将复制格式化)。

如果您只想复制下拉液滴而不是格式化,则以下是步骤:

  • 复制下拉下降的单元格。
  • 选择要复制下拉的单元格。
  • 回到家 - >粘贴 - >粘贴特别。粘贴特殊选择
  • 在粘贴特殊对话框中,选择粘贴选项中的验证。选择粘贴特殊验证选项
  • 单击确定。

这将只复制下拉块而不是复制单元格的格式。

在使用Excel下拉列表时小心

当您在Excel中使用下拉列表时,您需要小心。

在包含包含下拉列表的单元格上复制单元格(不包含下拉列表)时,丢失下拉列表丢失。

最糟糕的一部分是Excel不会显示任何警报或提示,让用户知道将被覆盖下降。

如何选择其中下拉列表的所有单元格

有时,很难知道哪个单元格包含下拉列表。

因此,通过使其成为一个不同的边界或背景颜色来标记这些细胞是有意义的。

而不是手动检查所有单元格,有一种快速的方法来选择具有其下拉列表(或任何数据验证规则)的所有单元格。

  • 回到家 - >查找和选择 - >转到特殊。转到特别选择其中下拉列表中的所有单元格
  • 在转到特殊对话框中,选择数据验证
    • 数据验证有两个选项:全部和相同。所有人都将选择应用于它的数据验证规则的所有单元格。同样将仅选择具有与活动小区具有相同数据验证规则的那些单元格。如何在Excel中创建下拉列表 -  Goto DataVal
  • 单击确定。

这将立即选择应用于它的数据验证规则的所有单元格(这也包括下拉列表)。

现在,您可以简单地格式化单元格(给出边框或背景颜色),以便在视觉上可见,您不会意外地复制另一个单元格。

这是另一种技术通过Jon Acampora,您可以使用始终保持下拉箭头图标可见。您还可以看到某种方式来执行此操作视频由Excel先生

创建依赖/条件Excel下拉列表

以下是如何在Excel中创建依赖下拉列表的视频。

如果您更喜欢阅读视频,请继续阅读。

有时,您可能有多个下拉列表,并且希望在第二个下拉下显示的项目依赖于第一次下拉列表中选择的用户。

这些称为依赖或条件下拉列表。

以下是条件/依赖下拉列表的示例:

在Excel中取决于依赖下拉列表的演示

在上面的示例中,当“下拉2”中列出的项目取决于“下拉1”中所做的选择时。

现在让我们看看如何创建这个。

以下是在Excel中创建依赖/条件下拉列表的步骤:

  • 选择要第一个(main)下拉列表的单元格。
  • 转到数据 - >数据验证。这将打开“数据验证”对话框。功能区中的数据验证选项
  • 在“数据验证”对话框中,在“设置”选项卡中,选择列表。数据验证设置以在Excel中创建依赖下拉列表
  • 在“源”字段中,指定包含要在第一个下拉列表中显示的项目的范围。使用标题以创建第一个下拉列表
  • 单击确定。这将创建下降1。Excel下拉列表 - 条件 -  DD1演示
  • 选择整个数据集(在此示例中为A1:B6)。Excel下拉列表 - 条件 - 选择整个范围
  • 转到公式 - >定义的名称 - >从选择创建(或者您可以使用键盘快捷控制+ Shift + F3)。从选择创建命名范围
  • 在“创建”对话框中,选中“顶行”选项并取消选中所有其他选项。这样做创造了2个名称范围('果实'和'蔬菜')。评为范围的水果是指名单和蔬菜中的所有果实都指的是列表中的所有蔬菜。从选择的顶行创建名为范围
  • 单击确定。
  • 选择要依赖/条件下拉列表的单元格(此示例中的E3)。
  • 转到数据 - >数据验证。功能区中的数据验证选项
  • 在“数据验证”对话框中,在“设置”选项卡中,请确保在所选中列出。“数据验证”对话框中的“设置”列表
  • 在“源”字段中,输入公式=间接(D3)。这里,D3是包含主下拉的小区。使用间接函数在Excel中创建条件下拉列表
  • 单击确定。

现在,当您在下降1中进行选择时,下拉列表2中列出的选项将自动更新。

下载示例文件

这是如何运作的?- 条件下拉列表(在单元格E3中)是指=间接(D3)。这意味着当您在单元格D3中选择“果实”时,E3中的下拉列表是指的命名范围'果实'(通过间接功能)因此列出了该类别中的所有项目。

重要说明,同时使用Excel中的条件下拉列表:

  • 当您进行选择时,然后更改父级下拉时,依赖下拉下降不会改变,因此,是错误的条目。例如,如果你选择美国作为国家,然后选择佛罗里达作为国家,然后回去并将国家改为印度,国家将留下佛罗里达州。这里有一个黛布拉的伟大教程在选择更改时,在清除依赖(条件)下拉列表中删除列表。
  • If the main category is more than one word (for example, ‘Seasonal Fruits’ instead of ‘Fruits’), then you need to use the formula =INDIRECT(SUBSTITUTE(D3,” “,”_”)), instead of the simple INDIRECT function shown above. The reason for this is that Excel does not allow spaces in named ranges. So when you create a named range using more than one word, Excel automatically inserts an underscore in between words. So ‘Seasonal Fruits’ named range would be ‘Seasonal_Fruits’. Using the替代功能在间接功能中确保空格转换为下划线。

您也可能喜欢以下Excel教程:

Excel电子书订阅

免费Excel书籍

获得51个Excel提示电子书来飙升您的生产力,并更快地完成工作

39关于“如何在Excel中创建下拉列表(所需的指南)的”如何在excel中创建下拉列表“的想法”

  1. 当我尝试在数据验证中使用源字段中的偏移公式时,即使在手机本身时,我会收到错误消息(“此公式有问题......”)。任何想法如何发生这种情况?

  2. 您如何使用日期创建下拉列表?例如,11月1日,2020年11月1日,2021年,11月1日,2022年等等。

  3. 是否有任何方法可以在Excel中制作关系数据库,其中我可以保留进入单元格不同并将它们链接到另一组条目。例如:一个表中的类别链接到另一个表中提供的产品。如果有请回答?

    • 有可能的。我看到了两个或选项:(a)fomularyly,很大程度上使用引用函数,例如索引,查找等,可能使用Excel VBA以编程方式或(b)编写逻辑函数。

      使用Excel VBA使用VBA等价物使用电子表格函数或在Excel VBA中不存在的位置来支持程序化解决方案,使用Excel VBA“Application.spreadsheetfunction ...”方法来访问电子表格函数。有条件分支的知识,例如“如果”,“选择案例”和循环,for / next,“do to”构造是非常有用的,如果不是在实现方案解决方案方面是必要的。此外,您可以使用VBA用户表单进行编辑记录,或创建基于电子表格的形式,该表单写入并从其他数据表中读取记录数据项。如果使用VBA用户表单或电子表格模板显示记录,则需要伴随用户输入文本字段,按钮,下拉列表等(VBA中的控制)或字段的单元格,按钮和电子表格下拉的形状(数据验证)单元格。VBA按钮和电子表格将分配给它们的VBA代码,当单击剪切时将被执行。

  4. 我跟随你的视频来做一些作业。我做得非常成功,我的主管非常幸福。感谢您通过放置所有步骤完成的良好工作。

  5. 嗨,我正在研究一个偏移的下拉列表,以便每个项目都列出一次。例如,我有10项来选择可能的20行。我注意到,在项目中填充10行,下拉目没有显示任何用于选择的项目(如计划),但我能够在其他空白行中包含任何自由文本,有效地绕过下拉目验证。我究竟做错了什么?

    谢谢!

  6. 您好,我想知道如何使用动态下拉列表功能,以跨不同的工作表?为了保留我的电子表格清洁,我将用于填充一张纸上的各种下拉列表但在另一张上使用它们。谢谢

  7. 嗨sumit,
    您的视频非常乐于助人,您使其非常容易明白了解。
    保持伟大的工作,谢谢你让Excel看起来并不像我认为这将是令人生畏的

  8. 我想创建一个下拉列表,其中我有全名,但是当我选择其中一个时,而不是单元格显示全名,它只显示第一个字母。例如,下降显示javier,但是当我选择该单元只显示J.我该怎么做?谢谢你。

  9. 我有一个具有大量具有动态下拉列表的小区的电子表格,并且它正在痛苦地运行,我认为是由于在数千次细胞的验证中间接引起的。是否有一种方法可以获得不使用间接或偏移的动态下拉列表?我尝试过index():索引()公式,但它只是抛出错误。

  10. 问题,
    在我们的Petanque俱乐部中,我们有俱乐部比赛A和B对阵成员X和Y.
    为结果创建表格,我可以使用输入的下拉列表,与所有成员(200+)一起,但通常我知道名字,但姓氏是什么?

    有没有办法在列表中开始键入,以便它开始过滤列表中的所有John或Mary?

    会帮助我很多,谢谢

  11. 本教程非常全面,非常有用。做更多视频,很想订阅你的教程。做得好!

  12. 我有一个查询,我有下拉(预先发布作为输入)现在我希望当我选择“pre”时,我应该得到值“x”,当我选择POST时,我应该将值作为空白(如总为空字段),你能建议任何方法吗?

  13. 这很有帮助。问题:如果我希望从列表中添加2个项目的选项,我该如何做到?我使用单独的表格使用单独的表格来使用该列表来更新我的公式。我需要分别查看POS(销售点)数字和APO(经过销售点之后),两者都加在一起。我该怎么做呢?

  14. 嗨,我真的很喜欢教学视频,并创建了自己的动态下拉列表。我有两个问题:
    1.有没有办法将下拉列表与键盘一起使用?(每次按下下按按钮,我的Excel工作簿关闭。)
    2.以我想查看的名称键入后,我可以选择滚动下来吗?
    我非常欣赏帮助!

  15. 我正在使用间接函数来创建依赖下拉列表。为什么我需要在我的一些下降项目前面放置一个下划线“_”,其中一些下拉项目有相似但不是确切的名称?例如,DV,DV0和DV30。Excel正在强迫我使用_dv0和_dv30,因为我的下落项目而不是dv0和dv30。

    • “DV30”可以是一个小区参考(列DV,第30行),您无法提供一个也可以是小区参考的名称。

  16. 它以非常简单明了的方式解释。赞赏它。谢谢你的视频

  17. 不起作用。选择果实时下载的示例不做任何事情。公式与此页面上的示例不同,以便使用间接

  18. 我理解上面写的所有关于独立的下拉列表,工作很大。我所拥有的问题完全相同,但使用不同的工作簿。我使用了两个工作簿一下,它没有问题。我还在同一个工作簿中尝试并独立下降,并且还可以正常工作,但一旦我尝试使用间接进行独立列表,在两个不同的工作簿中它不起作用。我花了这么多时间试图让它工作......我的最新尝试是我的目标文件中有两次下降,但它们不是独立的......你帮忙!

  19. 嗨,我正在创建一个ROTA,其中选择了不同的任务/分配,我想使用下拉列表来选择名称。问题是,因为它基于技能水平,我只想要那些拥有这些技能的人在每个相关的下拉列表中显示。
    我要使用的来源是一个带有名称和复选框的表格,可以勾选,以便他们有相关的技能/ s。
    我不知道如何将名称勾选框 - 下拉列表链接在一起。

  20. 亲爱的兄弟,我非常感谢你。确实非常乐于助人!

  21. 这非常有用!非常感谢!您是否知道是否有一种方法可以从原始列表中保留格式化,以便在创建它们时将这些格式填充到列表中?一个例子是,当在列表中选择一个选项时,该单元格将被格式化为红色,其中其他单元格(选项)是不同的或无颜色。我似乎无法弄清楚这一点。谢谢!


  22. 你能使更大且可读的下降。就像我真的很长的供应商名称列表和它相当小,但想知道任何想法要使下降更大。
    瑟瑞娜

  23. 如何从下拉列表选择中删除条目?我希望能够再次选择相同的条目来删除。基本上再次选择应该加入条目。

  24. 您如何使用依赖下拉列表,其中第一个条件是一系列值?例如,如果单元格A2 = 100,D,E,F将显示在下拉列表中?

  25. 谢谢...优秀的解释。但是如何提供一个下拉列表,该列表提供了完成原始列表中不在原始列表中的响应(例如的名称)的选项,而且更好地静止......这个名称是否自动包含在数据库中以供将来下拉下降?
    考虑一个改变的员工列表,员工进入,出门,能够在表格中添加名称而不是搜索列表必须是其他人所寻求的优势

评论被关闭。