EP05. “Drop-down List 制作下拉列表”
🔒 登录后可标记已读- Data Validation 下拉列表的完整用法合集
- 涵盖：建立基础下拉列表、允许用户输入列表外的值、增删列表项目、做出会自动扩展的动态下拉列表，以及用 Excel 365/2021 的新函数做「表格式」下拉列表
- 前置知识：基本的 Data Validation 操作
- 是 EP06「相关下拉列表」的基础篇，学完能应付各种下拉列表的建立和维护需求
重点内容
适用版本
桌面版通用（Excel 365 / 2021 / 2019 等），部分进阶用法（UNIQUE 函数、# 溢出引用）需要 Excel 365 或 2021。
下拉列表范围怎么选
| 做法 | 适合场景 | 备注 |
|---|---|---|
| 固定范围（Sheet2!$A$1:$A$3） | 选项很少变动 | 最简单，但新增/删除项目要手动改范围 |
| OFFSET + COUNTA 动态范围 | 选项会常常增减，且用的是旧版本 Excel | 范围自动跟着清单长度调整，公式稍长 |
| Excel 表格（Table）+ UNIQUE | Excel 365/2021，且想连原始数据都自动去重 | 最省事，但需要新版本 Excel |
基础下拉列表
- 在第二个工作表（例如 Sheet2）输入下拉选项内容，例如 A1:A3
- 回到第一个工作表，选中要放下拉列表的单元格（例如 B1）
- Data 选项卡 → Data Tools 组 → Data Validation
- Allow 下拉菜单选择 List
- Source 输入框填入
Sheet2!$A$1:$A$3 - 点击 OK
[截图：Data Validation 对话框，Allow 选 List、Source 填入 Sheet2!$A$1:$A$3 的界面，以及单元格套用后下拉箭头展开选项的效果]
替代做法：不用引用其他工作表，直接在 Source 输入框手动打上选项文字，用逗号分隔（这种方式区分大小写）。
复制下拉列表规则到其他单元格：选中已设好规则的格子，Ctrl + C 复制，选中目标格子 Ctrl + V 粘贴。
允许输入列表以外的值
- 打开 Data Validation 对话框
- 切到 Error Alert 分页
- 取消勾选 "Show error alert after invalid data is entered"
- 点击 OK
[截图：Data Validation 对话框 Error Alert 分页，Show error alert after invalid data is entered 复选框取消勾选的界面] 这样使用者仍然会看到下拉箭头，但也可以手动输入列表之外的内容，不会被拒绝。
增加/删除列表项目
增加： 在原本的选项列表中，选中某一项，右键 → Insert，选择"整行下移"，在空出来的格子输入新项目。Excel 会自动把 Data Validation 的 Source 范围扩大（例如从 A1:A3 自动变成 A1:A4）。
删除： 右键选中要删除的项目 → Delete，选择"整行上移"。
动态下拉列表（自动扩展范围）
用 OFFSET 搭配 COUNTA，让 Source 范围随着清单增减自动调整，不用手动改范围：
=OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A:$A),1)
COUNTA 算出 A 列有多少个非空单元格，OFFSET 就以此动态决定范围要延伸到第几行。
相关下拉列表（简介）
第一个下拉列表选 "Pizza" 时，第二个下拉列表只显示披萨相关的项目；选 "Chinese" 时则显示中餐项目。完整做法见 EP06。
用「表格」做下拉列表（Excel 365/2021）
- 选中列表项目范围，Insert 选项卡 → Table，转成 Excel 表格（这样清单会自动随新增行扩展，不用像 OFFSET 那样另外写公式）
- 搭配结构化引用（Structured Reference）或 INDIRECT 函数取用表格数据
- Excel 365/2021 可以用 UNIQUE 函数从原始数据提取不重复清单：
=UNIQUE(数据范围)
- 用
F1#（溢出范围引用）指向 UNIQUE 算出来的整组结果，当作下拉列表的 Source
删除下拉列表
- 选中已设置下拉列表的单元格
- Data Validation
- 点击 Clear All
- 点击 OK
函数速查
| 函数 | 语法 | 用途 | 例子 |
|---|---|---|---|
| OFFSET | =OFFSET(reference, rows, cols, [height], [width]) | 从某点位移指定行列数，取出一个动态范围 | =OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A:$A),1) |
| COUNTA | =COUNTA(value1, [value2], ...) | 统计非空单元格数量 | =COUNTA(Sheet2!$A:$A) |
| UNIQUE | =UNIQUE(array) | 提取不重复值清单（Excel 365/2021） | =UNIQUE(A1:A20) |
学完你会
- ✅ 会用 Data Validation 建立基础下拉列表，也能设置成允许输入列表外的值
- ✅ 会判断该用固定范围、OFFSET 动态范围、还是 Excel 表格来管理下拉选项
- ✅ 明白相关下拉列表（EP06）需要先搞懂这篇的下拉列表基础
常见错误
- Source 引用其他工作表的范围时忘记加绝对引用符号，规则套用到其他单元格后范围跑掉
- 想允许输入列表外的值，却只是取消了下拉箭头本身，没有去 Error Alert 分页关闭错误提示，导致使用者还是被拒绝输入
- 用 OFFSET 动态范围时 COUNTA 统计错了列（比如统计到了标题行），导致范围多算或少算一行
Sources
Blog / Website：