当前位置：首页 > news >正文

Excel中的“点选输入”——次级下拉列表创建

news 2025/7/9 21:33:32

在Excel中，用“数据验证”功能可以设置下拉列表，二级下拉列表需要设置公式。

(笔记模板由python脚本于2024年06月16日 18:36:37创建，本篇笔记适合经常使用Excel处理数据的coder翻阅)

【学习的细节是欢悦的历程】

Python 官网：https://www.python.org/
Free：大咖免费“圣经”教程《 python 完全自学教程》，不仅仅是基础那么简单……
地址：https://lqpybook.readthedocs.io/

自学并不是什么神秘的东西，一个人一辈子自学的时间总是比在学校学习的时间长，没有老师的时候总是比有老师的时候多。
—— 华罗庚

My CSDN主页、My HOT博、My Python 学习个人备忘录
好文力荐、老齐教室

Excel的“数据验证”功能 单元格下拉列表 (设置下拉列表，次级下拉需要公式)

本文质量分：

【 $96$ 】
本文地址： https://blog.csdn.net/m0_57158496/

CSDN质量分查询入口：http://www.csdn.net/qc

目录

◆ 单元格下拉列表
- 1、下拉列表制作
- - 1.1 下拉列表数据准备
  - 1.2 实景试炼
- 2、二级下拉列表
- 3、公式版本优劣
- - 3.1 OFFSET、MATCH函数组合
  - 3.2 INDEX、MATCH函数组合

◆ 单元格下拉列表

1、下拉列表制作

在Excel中创建单元格的下拉列表，可以提供一系列预设选项供用户选择，这不仅可以提高数据输入的准确性和效率，还可以保证数据的一致性。

以下是在Excel中创建下拉列表的步骤：

1.1 下拉列表数据准备

准备数据列表：
- 首先，在某个工作表中，你需要准备一个包含所有可能选项的列表。
- 例如，假设你想要在A列的单元格中创建下拉列表，你可以在工作表的某个区域（比如E1:E10）输入这些选项。
  下拉列表截屏图片
设置数据验证：
- 选中你想要设置下拉列表的单元格或单元格范围（在A列的假设中）。
- 在“数据”菜单中，找到“数据验证”选项(在Excel 2003及更早版本中，这一功能在“工具”菜单下的“数据验证”。我的Excel版本是“数据有效性”按钮)，点击按钮打开数据验证对话框。
  我的版本截屏图片
- 在弹出的“数据验证”对话框中，选择“设置”标签页。
- 在“允许”下拉列表中选择“列表”。
- 在“来源”框中，输入你的选项列表所在的范围引用，例如E1:E10。

回页目录

1.2 实景试炼

实景试炼截屏图片
可以实时编辑下拉列表

自定义下拉列表：
- 在“数据验证”对话框中，你还可以在“输入信息”和“错误警告”标签页中自定义提示信息和错误警告信息。
- 如果你想让用户在单元格中输入时能够看到所有选项，可以在“数据验证”对话框的“输入法模式”区域中勾选“下拉列表”。
应用并测试下拉列表：
- 点击“确定”后，选定的单元格或单元格范围就会添加下拉列表。
- 你可以尝试在不同的单元格中点击下拉箭头，看看是否能够选择预设的选项。
保护工作表（可选）：
- 如果你不希望用户在设置了下拉列表的单元格中输入列表外的值，可以通过“审阅”菜单中的“保护工作表”功能来锁定这些单元格。
- 在保护工作表时，确保选中“允许所有用户进行”列表中的“选择锁定单元格”。

完成以上步骤后，你就在Excel中成功创建了一个单元格下拉列表。如果需要在其他单元格或工作表中复制这个下拉列表，请确保引用的选项列表地址是正确的，或者将数据验证的设置通过复制粘贴应用到其他单元格。

回页目录

2、二级下拉列表

在Excel中创建二级下拉列表，也称为依赖性下拉列表，可以让第二个下拉列表的内容依赖于第一个下拉列表的选择。例如，如果你有一个下拉列表包含不同的国家，第二个下拉列表可以根据选择的国家显示该国家的城市。

以下是创建二级下拉列表的步骤：

准备数据：
- 在工作表中，准备好两个列表。例如，一个列表是不同国家的名称（A列），另一个列表是每个国家的城市名称（B列），且每个国家的城市都连续排列。
设置一级下拉列表：
- 选中你想要设置一级下拉列表的单元格或单元格范围。
- 打开“数据验证”对话框，选择“列表”，并在“来源”中引用包含国家名称的范围。
使用公式创建二级下拉列表：
- 选中你想要设置二级下拉列表的单元格或单元格范围。
- 打开“数据验证”对话框，选择“列表”，然后在“来源”中输入一个公式，该公式将基于一级列表的选择动态变化。例如，如果国家列表在A2:A10，城市列表在B2:C10，公式可能如下：
```
=INDIRECT(ADDRESS(ROW(), COLUMN()-1))
```
- 这个公式会根据当前行的左侧单元格（国家单元格）的位置来返回相应的城市列表范围。
复制二级下拉列表：
- 将含有二级下拉列表的单元格向下复制到需要下拉列表的每个单元格。
保护工作表（可选）：
- 如果你希望用户只能从下拉列表中选择，不能手动输入，可以选择“审阅”菜单中的“保护工作表”，并确保在保护工作表时勾选了“选择锁定单元格”。
测试二级下拉列表：
- 在一级下拉列表中选择不同的选项，确保二级下拉列表的内容会相应地变化。

请注意：使用INDIRECT函数创建的二级下拉列表可能需要确保数据格式正确，并且避免创建可能引用错误单元格的公式。此外，如果数据量很大或者需要频繁更改，可能需要考虑使用VBA宏来实现更复杂的依赖性下拉列表。

回页目录

3、公式版本优劣

公式有两个版本，都可以用来创建二级下拉列表，但它们的适用场景和优缺点略有不同。

3.1 OFFSET、MATCH函数组合

使用OFFSET和MATCH函数的公式：
- 公式：
```
     
=OFFSET($B$1, MATCH(C1, $A$1:$A$10, 0), 0, COUNTIF($A$1:$A$10, C1), 1)
```
- 这个公式的优点是它可以动态地计算出城市列表的范围，不需要预先知道每个国家城市的具体数量。
- 缺点是OFFSET函数在某些情况下可能会受到Excel计算模型的影响，导致公式计算不准确。此外，如果数据量很大，这个公式的计算可能会变慢。

回页目录

3.2 INDEX、MATCH函数组合

使用INDEX和MATCH函数的公式：
- 公式：
```
=INDEX($B$2:$B$30, MATCH(C2, $A$2:$A$10, 0), 0)
```
- 这个公式的优点是它更加稳定，不容易受到Excel计算模型的影响，且计算速度通常比OFFSET函数快。
- 缺点是它假设每个国家的城市数量是固定的，并且你需要预先知道第一个城市的起始位置。如果城市数量不固定，这个公式可能不适用。

在实际应用中，如果你的数据结构比较简单，每个国家的城市数量固定，使用INDEX和MATCH函数的公式会更加可靠。如果你的数据结构比较复杂，城市数量不固定，那么使用OFFSET和MATCH函数的公式可能更合适。

建议根据你的具体需求和数据结构来选择合适的公式。如果可能的话，可以先在一个小规模的数据集上测试两个公式，看看哪个更适合你的情况。

回页首

上一篇：功能强大的偷懒神器：数据透视表_{(数控透视表，用于总结、分析、探索和呈现数据。允许用户重新组织、汇总和简化大量数据)}
下一篇：

我的HOT博：

本次共计收集 311 篇博文笔记信息，总阅读量43.82w。数据于2024年03月22日 00:50:22完成采集，用时6分2.71秒。阅读量不小于6.00k的有 $7$ 篇。

001
标题：让QQ群昵称色变的神奇代码
_{(浏览阅读 5.9w )}
地址：https://blog.csdn.net/m0_57158496/article/details/122566500
点赞：25 收藏：86 评论：17
摘要：让QQ昵称色变的神奇代码。
首发：2022-01-18 19:15:08
最后编辑：2022-01-20 07:56:47
002
标题：Python列表(list)反序(降序)的7种实现方式
_{(浏览阅读 1.1w )}
地址：https://blog.csdn.net/m0_57158496/article/details/128271700
点赞：8 收藏：35 评论：8
摘要：Python列表(list)反序(降序)的实现方式：原址反序，list.reverse()、list.sort()；遍历，全数组遍历、1/2数组遍历；新生成列表，resersed()、sorted()、负步长切片[::-1]。
首发：2022-12-11 23:54:15
最后编辑：2023-03-20 18:13:55
003
标题：pandas 数据类型之 DataFrame
_{(浏览阅读 9.7k )}
地址：https://blog.csdn.net/m0_57158496/article/details/124525814
点赞：7 收藏：36
摘要：pandas 数据类型之 DataFrame_panda dataframe。
首发：2022-05-01 13:20:17
最后编辑：2022-05-08 08:46:13
004
标题：个人信息提取(字符串)
_{(浏览阅读 8.2k )}
地址：https://blog.csdn.net/m0_57158496/article/details/124244618
点赞：2 收藏：15
摘要：个人信息提取(字符串)_个人信息提取python。
首发：2022-04-18 11:07:12
最后编辑：2022-04-20 13:17:54
005
标题：Python字符串居中显示
_{(浏览阅读 7.6k )}
地址：https://blog.csdn.net/m0_57158496/article/details/122163023
评论：1
006
标题：罗马数字转换器|罗马数字生成器
_{(浏览阅读 7.5k )}
地址：https://blog.csdn.net/m0_57158496/article/details/122592047
摘要：罗马数字转换器|生成器。
首发：2022-01-19 23:26:42
最后编辑：2022-01-21 18:37:46
007
标题：回车符、换行符和回车换行符
_{(浏览阅读 6.0k )}
地址：https://blog.csdn.net/m0_57158496/article/details/123109488
点赞：2 收藏：3
摘要：回车符、换行符和回车换行符_命令行回车符。
首发：2022-02-24 13:10:02
最后编辑：2022-02-25 20:07:40