excel 表格做下拉选项主要依靠数据验证功能实现,也就是旧版本里的数据有效性,设置完成之后单元格就会出现下拉箭头,点击箭头可以直接选择预设好的内容,不用手动打字录入,能够统一录入规范,减少错别字和重复录入,在登记台账 信息表 出入库单据 人员登记表这类表格里面非常常用,下拉选项支持固定手动输入内容,也支持引用表格单元格区域作为选项来源,还能搭配公式实现联动下拉,也就是二级下拉三级下拉,适合选项会跟着上一级选择内容动态变化的场景,不同 Excel 版本核心功能名称有区别,老版本叫数据有效性,新版本统一叫数据验证,操作逻辑基本一致,设置的时候要重点留意来源区域 空格 绝对引用 报错提示这些细节,很多新手设置完点不开下拉,基本都是来源填写错误或者单元格格式异常导致。
基础版 手动输入选项制作下拉选项
这个方法适合选项数量不多,选项内容基本不会变动的场景,优点是不需要单独准备选项列表,直接在设置窗口录入内容,缺点是后续想要新增修改选项,需要重新打开数据验证窗口编辑来源内容。
操作步骤
第一步 选中需要添加下拉选项的单元格或者整列单元格区域。
第二步 顶部菜单栏切换到数据选项卡,找到数据验证按钮点击打开设置窗口。
第三步 在允许下拉菜单里面选择序列。
第四步 在来源输入框里面,用英文逗号把所有选项内容隔开,例如男 女 未知,输入的时候逗号必须是英文逗号,中文逗号会识别失败。
第五步 勾选提供下拉箭头,这个选项一定要勾选,不勾选单元格就不会显示下拉箭头。
第六步 点击确定,选中单元格之后就可以看到下拉箭头,点击就能选择预设内容。
进阶版 引用单元格区域制作下拉选项
这个是日常最推荐的方式,适合选项比较多,后续还会新增修改选项的表格,选项单独放在工作表某一列,下拉选项直接引用这一列单元格,以后新增选项只需要在列表追加内容,不需要反复打开数据验证窗口修改来源。
操作步骤
第一步 在空白区域提前录入全部下拉选项内容,单独做成选项清单,不要和正式录入数据放在同一区域。
第二步 选中需要添加下拉选项的目标单元格区域。
第三步 打开数据验证,允许选择序列。
第四步 在来源框直接鼠标点选刚才做好的选项单元格区域,比如 A2 A15。
第五步 如果后续要向下填充更多单元格,建议给区域加上绝对引用,也就是美元符号锁定区域,防止填充的时候引用区域偏移。
第六步 点击确定完成设置,后续在选项清单追加新内容,部分版本需要重新打开数据验证确认区域范围,新版 365 可以搭配表格结构化引用实现自动扩展。
高级版 二级联动下拉选项
二级联动下拉选项,就是第一个单元格选择大类之后,第二个单元格的下拉选项只显示这个大类对应的子项,适合省市选择 分类产品 部门分类这类业务表格,核心原理是根据第一个单元格选中的值,用公式动态匹配对应的子选项区域。
操作步骤
第一步 把大类和对应的子项,按列分开整理,每一个大类单独一列作为子选项区域。
第二步 选中第一个单元格,按照基础序列方式设置大类下拉选项。
第三步 选中第二个单元格,打开数据验证,允许选择序列。
第四步 来源输入公式,使用 INDIRECT 函数引用第一个单元格内容对应的区域名称,提前给每一列子选项定义名称,名称和大类文字保持一致。
第五步 确认公式没有报错,点击确定,测试选择不同大类,第二个下拉选项是否同步切换子项。
补充设置 出错警告与输入信息
| 设置项目 | 作用 | 注意事项 |
|---|---|---|
| 输入信息 | 选中单元格弹出提示文字,告诉使用者这个单元格要做下拉选项选择 | 属于提示,不会限制手动输入 |
| 出错警告 | 当手动输入不在下拉选项内的内容时弹出报错拦截 | 可以选择停止 警告 信息三种级别,停止级别会禁止录入非法内容 |
| 提供下拉箭头 | 控制单元格是否显示下拉三角 | 取消勾选之后虽然还是序列,但是看不到下拉箭头 |
补充要点,如果原始选项里面存在空单元格,下拉选项里面会出现空白项,建议提前清理选项区域的空白单元格。如果复制已经做好下拉选项的单元格,粘贴到其他位置,下拉选项会跟着一起复制过去。如果想要清除下拉选项,选中单元格,打开数据验证窗口,点击全部清除,就可以移除这个下拉选项。
总结
excel 表格做下拉选项优先使用数据验证里面的序列功能,少量选项可以直接手动录入英文逗号分隔内容,选项多并且后续要维护,就单独建立选项清单引用单元格区域,业务需要分类联动就使用 INDIRECT 函数做二级联动下拉,设置过程重点区分中英文逗号,记得勾选提供下拉箭头,做好之后测试下拉选择,检查是否可以正常弹出选项列表。
【常见问题】
问题 1 excel 表格做下拉选项点不开下拉箭头是什么原因
回答 1 excel 表格做下拉选项点不开下拉箭头,常见原因是没有勾选提供下拉箭头,或者允许选项没有选择序列,还有可能单元格处于保护工作表锁定状态,解除工作表保护,重新打开数据验证检查序列设置,就可以恢复 excel 表格做下拉选项的下拉箭头。
问题 2 excel 表格做下拉选项能不能修改已经预设好的选项内容
回答 2 excel 表格做下拉选项之后可以修改预设选项,如果是手动录入来源的下拉选项,重新打开数据验证编辑来源文字,如果是引用单元格区域的 excel 表格做下拉选项,直接修改选项清单单元格内容即可,新增选项记得更新引用范围。
问题 3 excel 表格做下拉选项可以允许手动输入不在列表里面的内容吗
回答 3 excel 表格做下拉选项默认如果出错警告设置为停止,就不能手动输入列表外内容,想要允许额外手动录入,打开数据验证,把出错警告级别改成警告或者信息,或者取消出错警告,就可以在 excel 表格做下拉选项的单元格输入列表以外的文字。
【免责申明】
本文教程基于常规 Excel 版本编写,不同版本菜单名称和函数支持情况存在差异,操作前建议备份原始表格,二级联动下拉涉及定义名称和函数,公式错误会导致下拉失效,因操作失误造成表格数据异常,由使用者自行承担,本文内容仅供学习参考。
