为了更轻松地输入 Excel 电子表格,请使用适合您的数据的简单或动态下拉列表。您将避免错误,并且您的数据将更加一致。
您想避免一直在 Excel 中输入相同的数据吗?防止错别字?标准化标签录入,让积累更轻松?使用下拉列表将数据输入 Microsoft 电子表格有一千零一个充分的理由!在电子表格中单击两次,您所要做的就是从列表中选择正确的值。
下拉列表的原理很简单:它包括在“下拉菜单”中提供现成的选择,准备各种选项并设置一些规则。对于输入以及数据和标题的一致性而言,它是最简单且最有效的解决方案。
Excel 充满了好主意,输入有助于让您的生活更轻松。这里有几种方法,非常简单或更复杂,用于显示下拉列表并验证您或电子表格用户输入的数据。好消息:这些技术几乎适用于 Windows 和 Mac 上的所有 Excel 版本,甚至是最旧的版本,有时会存在一些我们指定的细微差别。
您是否拥有 Office 的月度/年度订阅版本(称为 Microsoft 365)中包含的最新版本的电子表格?我们告诉您,一切对您来说都变得更加简单Excel 365 特有的方法。但如果必须由不一定拥有最新版本应用程序的人打开 Excel 文件,请坚持使用建议的其他方法。
另一个重要的说明:如果您愿意,请参阅另一个实用表,也称为依赖下拉列表或链接下拉列表。使用级联下拉列表,您可以根据第一个下拉列表中的选择调整下拉列表的内容:例如,第一个列表允许您选择一个国家/地区,然后第二个列表仅显示以下城市所选国家/地区,然后第三个列表显示所选城市的旅游景点。
让我们从最简单的方法开始,即 Excel 处理所有事情的方法。毫无疑问,它在 Windows 和 Mac 上已经对您有用了;您只需了解一个键盘快捷键即可确保充分利用它。
- 在已包含元素的列的单元格中键入几个字母:Excel 建议已在同一列中输入一个或多个值。这是自动完成。

- 如果列表未出现,请键入键盘快捷键Alt+向下键(四个方向键的箭头)。此两键组合使 Excel 显示列表,以便您可以选择已输入的值。

- 您也可以右键单击单元格来选择选择下拉列表在上下文菜单中,它将显示列表。

- 如果列中列表上方出现一个或多个空单元格,Excel 将显示一个空或不完整的列表。您当然可以在这些单元格中键入一个空格,以便它们不再为空,但该技巧可能会影响您的计算,例如,如果您使用 COUNT 函数来找出您的列有多少个项目。由你决定…
- 此下拉列表仅适用于文本,不适用于数值或日期,并且仅适用于数据位于列中的情况。 Excel 在向您呈现列表之前会删除重复项并按字母顺序对值进行排序。
- 禁用自动填充 Windows 版 Excel,请转至文件 > 选项 > 高级选项(在左栏中),以及该部分下编辑选项,取消选中该框自动完成单元格值。
- 要配置 Excel for Mac,请转至Excel 菜单 > 首选项 > 自动完成。
借助数据验证功能,Excel 可以检查用户在单元格中输入的内容,并且不允许这样做;例如,仅数字,或仅两个限制之间的日期,或标签(最多 15 个字符),甚至是从您指定的列表中提取的值:这正是我们感兴趣的。
通过数据验证,用户保留在单元格中键入值的权利,而不是从下拉列表中选择值。但您可以拒绝输入任何意外的值,或者在最终接受输入的值之前显示警告。这是创建下拉列表的原理。
- 选择用户必须滚动列表才能更轻松地输入数据的单元格(这里B3:B12)。
- 在 Excel 功能区中,单击选项卡数据然后数据验证。
- 在该地区授权, 选择列表。
- 保留复选框的选中状态如果为空则跳过等单元格中的下拉列表。
- 单击框中来源然后在电子表格中选择包含要显示的列表的单元格范围(此处的单元格范围A2:A9我们称之为叶子的设置)。
- 按好的并通过单击应显示此列表的单元格之一来测试您的下拉列表。

- 在电子表格中,从视觉上看,没有任何迹象表明提供了用于输入的下拉列表。仅当您选择单元格时,右侧才会出现一个小图标,您必须单击该图标才能展开列表。
- 要滚动列表,您还可以按两个键的组合Alt+向下键。
- 我们为您提供进一步一些建议准备要显示的项目列表。
- 当您选择应显示下拉列表的单元格或单元格范围时,如有必要,可以通过单击网格中的字母来选择整个列,例如单击 C 以选择整个列 C(很容易排除然后仅某些单元格,例如 C1 和 C2,通过按钮全部清除对话框的数据验证)。
该区来源– 指示要显示的元素列表所在的位置 – 接受不同类型的信息:文本、单元格范围、命名区域、公式……我们将发现此源区域的所有微妙之处。
Excel for Windows 中的提示:当您单击该框时来源如果您想移至内部修改其内容,请按(一次)该键F2使用键盘上的向左/向右箭头键之前。
如果列表只有少数值且不太可能更改,则此方法适用。
- 选择后数据>数据验证>授权>列表,在该地区来源, 类型用分号分隔的值(如果您使用英文版 Excel,则使用逗号),值之间没有空格,例如三个选项:是;否;不知道
- 请注意,对于这种类型的源,Excel 会区分大小写(我们说它考虑“大小写”)。所以如果用户输入奥伊在单元格中而不是选择奥伊在下拉列表中,Excel 将返回错误消息(进一步查看错误警报)。下面解释的其他方法不区分大小写。
如果您使用的是最新版本的 Excel(Microsoft 365 订阅中包含的版本)或Excel 网页版,定期更新。这是创建清除重复项并可选择排序的下拉列表以及向列表添加和删除项目的最简单的解决方案。
如果您的电子表格必须使用 Excel 2021 之前的版本或其他编辑器设计的与 Excel 兼容的电子表格打开或重新处理,请首选本实用表中介绍的其他方法。如果您有 Excel 2007、2010、2013、2016 或 2019 版本,请优先使用以下方法Excel 表格。
在这里您不仅可以从 Excel 表格中受益,还可以从新功能中受益特里尔,独特等过滤(与 Excel 2021 一起出现)和反向范围运算符(签名#) d'Excel for Microsoft 365。
在这里,我们向您展示如何创建单个下拉列表。如果您愿意,请参阅其他实用表。
如何知道您是否拥有适用于 Windows 365 的 Excel?
在 PC 上,当您打开应用程序时,屏幕中央会短暂出现一个绿色的“Excel for Microsoft 365”窗口,启动 Excel 后,即可通过选项卡查看“Microsoft 365 订阅”信息文件 > 帐户。
在 Mac 上,“Microsoft 365 订阅”信息可见于关于微软Excel。
在 Excel for Microsoft 365 中创建包含或不包含重复项的下拉列表
下面的示例中,我们的起始列表包含重复项,并且文章未排序。 Microsoft 正在解决重复项问题...当您阅读这些行时,Excel 365 可能负责自动删除重复项数据验证形式为列表。如果某个值在种子列表中多次出现,那么您不必担心下拉列表的问题。我们已经验证过,如果您选择通过以下方式参与 Beta 测试计划,Excel Windows 中就已经出现这种情况文件 > 帐户 > Office 预览体验成员。否则你将使用新功能独特的Excel 365/2021。以下是这两种情况下的操作方法...
使用 Excel for Microsoft 365 对下拉列表中的数据进行排序
显示忽略空单元格的下拉列表
当列表可以包含许多条目并且它不发展或很少发展时,此方法很有趣。在这里您将学习如何创建单个列表。如果您愿意,请参阅其他实用表。
- 选择后数据>数据验证>授权>列表,单击该区域来源然后在工作表中单击,使用鼠标或键盘选择行或列中的一系列单元格。
- 当然,您也可以自己在框中输入单元格范围来源。
- 然后,当您键入值而不是从下拉列表中选择值时,Excel 不会考虑大小写。如果值露西在您的列表中,用户将能够键入露西,路西或 LuCie 在单元中未收到错误消息。

这种方法有几个优点:列表可以很长;名称总是使公式更容易理解,并且通过使用公式定义命名单元格范围的范围,下拉列表将自动适应添加或删除项目时的更改。

在这里您将学习如何创建单个列表。如果您愿意,请参阅其他实用表。
- 要在 Excel 中命名一系列单元格,请首先选择这些单元格,然后在框中单击名义,就在 A 列上方,然后键入名称:在我们的示例中,我们选择单元格区域 A4:A10 并键入作者,这成为海滩的名称。名称只能以字母 A 到 Z 或符号 _ 开头,并且不得包含空格(请参阅选项卡公式>姓名经理对于其他选项)。
- 当该范围的第一个单元格已经有标题时,另一种更简单的方法来命名范围:选择具有此列标题的单元格,这里我们的标题是作者,因此选择单元格 A3:A10,然后在选项卡中公式, 点击从选择中。检查盒子是否顶线(并且只有它)被选中并按好的。范围 A4:A10(因此没有第一个标题单元格)以第一个选定单元格(此处为 A3)的内容命名,因此此处称为范围 A4:A10作者。如果单元格 A3 包含文本法国作家,名字是作者_法语因为在命名范围中,名称中的空格和连字符被替换为标志_通过 Excel。
- 选择应显示下拉列表的单元格,然后单击选项卡数据 > 数据验证 > 授权 > 列表。
- 橱窗里数据验证,对于来源在列表中,键入 =(等于)号,后跟命名范围的名称,因此=作者在我们的例子中。
- 在编辑源区域的内容时,您也可以按 键,而不是键入曲目名称F3在 Excel for Windows 中:小窗口粘贴一个名字弹出窗口,它列出了工作簿中的所有命名区域。点击此处您感兴趣的名字作者, Excel 添加公式=作者在该地区来源。

- 这是我们的下拉列表,您只需从中选择一位作者即可。

需要加深您对 Excel 的了解吗?
请关注我们在 CCM Benchmark Institute 上的培训!
在 CCM Benchmark Institut 上了解 Excel 培训
使用此方法,当您在源列表中添加或删除数据时,下拉列表会自动适应以反映更改。其他方法允许这样做,这个方法具有优雅和清晰的优点,特别是因为您工作的所有单元格范围都已命名。 Excel 的“表格”还有许多其他优点。
在这里您将学习如何创建单个列表。如果您愿意,请参阅其他实用表。
- 单击源数据单元格之一或选择整个单元格区域及其列名称。您的表可以有一个或多个列,下拉列表在任何情况下都将仅使用您稍后将通过其名称指定的单个列。
- 在选项卡下插入, 点击画面。
- 如果您没有选择整个表格,Excel 会选择它认为应该属于表格一部分的数据,包括列标题(标题)。检查选择是否适合您。
- 勾选复选框我的表格有标题并按好的。在我们的示例中,我们的标头是水生哺乳动物等尺寸。

- Excel 名称表格1在工作簿中创建的第一个表(忽略它添加到标题右侧的下拉列表)。通过单击框中左上角的 来检查表名称名义。单击名称可选择整个表:表格1不考虑标题(因此在我们的示例中它涵盖了 B3:C10),但 Excel 小心地将它们保留在内存中......
- 现在选择应显示输入下拉列表的单元格。
- 单击选项卡数据 > 数据验证 > 授权 > 列表。
- 在该地区来源, 类型=INDIRECT("表名[列标题]")也就是说=INDIRECT("表1[水生哺乳动物]")在我们的例子中。
- 测试你的下拉菜单...

- 在源列表末尾的空单元格中添加一项或多项。下面我们输入两个新元素,座头鲸等中华海豚、在列表的末尾...

- ......自动地,我们的表格1跨越 B3:C10,现在覆盖单元格范围 B3:C12。并且无需执行任何操作,下拉列表就会考虑到两个添加的元素。

- 当您从列表中选择一种动物时,您想显示动物的大小吗?在输入表中,在下拉列表上方输入标题 (鲸类等尺寸,在我们的示例中)然后选择这个迷你表(标题和第一行数据)并单击插入,画面这次创建一个表格2。
- 在G3中,输入公式=VLOOKUP([@Cetacean];表1;2;FALSE)
- 下拉列表左侧所选动物的大小会自动显示...

- 当您位于此表格末尾的单元格中时G3, 按 键选项卡从 PC 或 Mac 键盘向表格添加行表格2。细胞内F4,下拉列表被复制并可供您选择动物,从列表中选择一个,其大小将自动显示在单元格中G4,因为公式写成G3也被自动复制到G4。

- 调整我们的公式=VLOOKUP([@Cetacean];表1;2;FALSE)对于您的数据,这里有一些解释:
► 功能查找表搜索表格左侧第一列中的值(此处为表格1),并返回表同一行但来自另一列的值
►@鲸类是我们在 F2 中输入的文本,此处成为表格的标题表格2,它指向您要查找的文本表格1(此处使用下拉列表在单元格中输入动物的名称)
►表格1是我们进行搜索的表
► 值“2英寸对应必须返回的值所在的列号(这里是第二列)表格1,其中包括动物的大小) - 价值仿品很重要,不要省略它,它在这里指定您想要精确搜索动物的名称,而不是最接近的值(否则指示值真的,但在这种情况下,动物名称列表必须按升序排序)。
- 最后,需要了解一些管理表的命令...如果您希望删除源表标题中出现的小下拉列表:选项卡主页 > 排序和筛选 > 筛选。
- 要重命名表格,请单击其单元格之一,然后单击选项卡创建表它出现在功能区的最右侧(在“文件”、“主页”、“插入”、“布局”等之后),然后重命名屏幕左上角的表格(此处表格1被重命名塔布洛鲸类)。要删除表,请单击转换为范围(细胞的内容被保留)。
使用此方法,如果您将项目添加到源列表,它们会自动显示在下拉列表中。我们在这里提出一个相对“简单”的公式,但是 Excel 的所有功能都可供您使用来定义列表的源元素。而且,正如我们将看到的,名称还可以与公式而不是单元格范围相关联......
在这里您将学习如何创建单个列表。如果您愿意,请参阅其他实用表。
- 在列中输入数据后,选择应显示下拉列表的单元格,然后单击选项卡数据 > 数据验证 > 授权 > 列表。
- 在该地区来源,根据上面示例中的数据,输入公式=DECALER($A$5;0;0;NBVAL($A:$A)-1;1)
- 这是我们的清单……

- 如果您在源列表的末尾添加新项目(在奶酪乳蛋饼下方),它会自动添加到下拉列表中。

- 这个公式=DECALER($A$5;0;0;NBVAL($A:$A)-1;1)将很容易适应您自己的数据:
► OFFSET 函数返回对单元格范围的引用(解释在这里) 其中包含要显示的列表;
►5澳元指我们的源列表的第一个单元格;
► 离开 0;0;不应用水平或垂直偏移;
►$A:$A对应源列表所在的(A)列;
► NBVAL 统计该列中非空单元格的数量(此处结果为 11);
► 值 -1:我们从 NBVAL 返回的数字(在我们的示例中为 11)中减去 1,因为我们的单元格之一包含列表头(当日菜肴),不应考虑在内;
► 最后1对应于OFFSET返回的引用的列数(保留值1)。 - 您可以很好地将这个公式与一个名称联系起来。首先让我们看看如何在 Windows 中执行此操作...

- 在 Windows 中,无论当前选择哪个单元格:单击选项卡公式然后姓名经理,然后按按钮新风格。
- 例如,为您的列表指定一个新名称每日广场(没有空格,第一个字母必须是 A-Z 或下划线 _ 符号)。
- 在该地区指的是,复制粘贴上面看到的公式,也就是说=DECALER($A$5;0;0;NBVAL($A:$A)-1;1)
- 按确定按钮并关闭数据管理器窗口。
现在让我们看看如何进行相同的操作在苹果电脑上。
- 要将公式与名称相关联,无论选定的单元格如何,请单击选项卡公式然后设置名称。
- 在此输入新名称每日广场(禁止使用空格,名称必须以 A 到 Z 的字母或下划线 _ 符号开头)。
- 在该地区选择单元格范围,复制并粘贴公式=DECALER($A$5;0;0;NBVAL($A:$A)-1;1)
- 按下按钮好的。
- 在 Windows 或 macOS 中,现在选择应显示下拉列表的单元格。单击选项卡数据然后数据验证。
- 选择允许 > 列表。在该地区来源, 类型=(等号)后跟您刚刚创建的名称,所以=PlatsDuJour并按下按钮好的。
- 下拉列表显示所有项目。如果您修改、添加或删除源列表中的项目,它将自动更新。

要准备要在下拉列表中显示的数据,最好(但不是必需)将列表的源元素存储在 Excel 工作簿的单独工作表中。
- 单击窗口底部的签名+在 Excel 工作簿中创建一个新工作表。然后双击选项卡弗伊尔2刚刚添加的内容,以便通过调用它来重命名该工作表,例如,设置。

- 然后就可以隐藏您的工作表设置用鼠标右键单击它,选择隐藏。要再次显示它,请右键单击任何其他工作表名称(例如 Sheet1)以选择展示…并查看隐藏工作表的列表。

- 通过右键单击工作表名称,您还可以保护板材,包括密码。
- 如果您不太习惯管理多个工作表并且更喜欢使用单个电子表格,则可以输入项目,然后隐藏该列。右键单击工作表顶部的列字母(下例中的字母 A),然后选择隐藏在上下文菜单中。要重新显示隐藏的列,请选择它周围的列,右键单击它们以显示上下文菜单,然后选择展示。

- 将出现在列表中的标签数据验证可以根据需要出现在列或行中,但单元格必须相邻,因此彼此跟随。
- 下拉列表中的数据将按此处显示的顺序显示,不会自动按字母顺序排序。因此,如果可以更轻松地输入,请自行对它们进行排序:选择列表并单击选项卡主页 > 排序和过滤 > 从 A 到 Z 排序。

- 如果初始列表有重复项需要删除,请选择此列表并单击选项卡数据 > 删除重复项。
我们已经看到,在某些情况下,Excel 表格特别是,只需在源列表末尾键入一个新值,以便将其添加到下拉列表中。否则,根据下拉列表引用源单元格列表的方式,以下是如何添加项目而无需更改数据验证选项...
将项目添加到从单元格区域定义的下拉列表中
- 要稍后将项目添加到下拉列表中,请在列表内右键单击,然后选择插入…然后单元格下移并在添加的单元格中输入新元素。如有必要,请删除您的列表。


- 其他方法:区内来源的数据验证,如果您最初选择比所需更大的单元格范围,然后在列表末尾添加空单元格,这将允许您扩展列表,而无需重新定义引用它的数据验证下拉列表。但…
- ……我们发现,如果线包含空单元格(上例中的第 17 行到第 21 行)没有绝不内容或数据或格式(粗体、斜体、框架等),Excel 不会在下拉列表中显示空选项,这是完美的。否则,它会在下拉列表的末尾添加空项目:该列表仍然可用,但它不是很优雅,如下所示......
将项目添加到从命名范围定义的下拉列表中
- 要将项目添加到显示命名范围内容的下拉列表中,请执行与单元格范围相同的操作 (见上文):右键单击指定区域中的一个单元格(但不是第一个),选择插入> 单元格下移然后在空单元格中键入新项目。如有必要,请重新整理清单。
- 在 Excel for Windows 中,如果要重新定义命名单元格区域的范围,请单击功能区上的选项卡公式,然后在下面姓名经理。单击范围名称,然后单击其下方的框中指的是在工作表中选择一个新范围。最后点击车见你验证并按下按钮关闭。请注意,该窗口还用于修饰符(例如添加评论)或删除命名范围。

- 在 Excel for Mac 中,要重新定义命名范围的范围,请单击选项卡公式然后设置名称(或在菜单中插入 > 名称 > 设置名称)。单击名称,重新定义单元格范围,然后按好的。这+ 和 – 按钮允许您创建或删除名称。

为了避免必须通过“数据验证”对话框重新配置下拉列表,请执行以下操作。
- 转到初始列表所在的工作表。
- 右键单击要从源列表中删除的单元格。
- 点击删除在上下文菜单中。

- 在小对话框中删除, 选中该框将单元格向上移动并按下按钮好的。如果数据排列在行而不是列中,请选中该框将单元格向左移动。
当然,完全可以删除电子表格中不再使用的下拉列表,而无需删除填充列表的单元格的内容。
- 至少选择一个显示下拉列表的单元格或仅显示相关单元格。
- 单击选项卡数据 > 数据验证。

- 在选项卡下工具,如果您希望具有相同数据验证选项的所有单元格都受到影响,因此显示此下拉列表的所有单元格:选中该框将这些更改应用到具有相同设置的单元格。
- 单击按钮全部清除。
- 下拉列表将不再出现,但已填充单元格的内容不会被删除。
- 如果您不确定包含数据验证的单元格位于何处,请在选项卡下欢迎,单击功能区最右侧的搜索并选择,然后数据验证。

- 在点击之前搜索并选择,如果您选择的单元格包含下拉列表,请单击选择单元格 > 数据验证 > 相同:Excel 选择显示相同下拉列表的所有单元格。您所要做的就是转到选项卡数据 > 数据验证 >布顿清除一切。

有时它很有用——甚至是令人向往的! – 以气泡形式显示消息,帮助工作表用户正确使用下拉列表。
- 橱窗里数据验证,选项卡输入信息, 在地区蒂特雷(最多 30 个字符)和输入信息(250 个字符),指示将以工具提示形式显示的信息。
- 取消选中该框当单元格被选中时暂时或永久停用此选项。
为了帮助工作表的用户,强烈建议检查输入并在检测到错误时显示警报消息。
- 如果您不想检查单元格中输入的值,请打开选项卡错误警报并取消选中该框当输入无效数据时。

- 相反,如果您希望验证该条目,请打开选项卡错误警报并选中该框当输入无效数据时。
- 在列表中风格, 选择停止(或者停止,在旧版本的 Excel 中)显示错误消息并拒绝任何错误输入的值。

- 在列表中风格, 选择警告显示错误消息,但允许用户仍然接受输入的值,即使该值不在列表中。这就是我们在字段中指出的内容蒂特雷等错误信息。

- 在列表中风格, 选择资讯和选择很相似警告:它显示一条消息,这次提供一个按钮好的验证列表中未提供的值。因此,我们调整了消息以显示在字段中蒂特雷等错误信息。

发现我们在 Excel 上的其他实用表格
Excel 有问题吗?在我们的论坛中提出您的问题