Excel 数据验证
Excel 数据验证完全指南 · 难度：进阶 · +15XP
Excel 数据验证完全指南
数据验证（也称数据有效性）用于限制用户在单元格中输入的内容类型和范围。通过设置验证规则，可以防止错误数据进入工作表，确保数据质量和一致性，是Excel数据管理的必备技能。
验证条件类型
| 允许条件 | 说明 | 示例 |
|---|---|---|
| 整数 | 限制为整数范围 | 年龄：0-150 |
| 小数 | 限制为小数范围 | 折扣：0.1-0.9 |
| 序列 | 下拉列表选择 | 部门：人事,财务,技术,市场 |
| 日期 | 限制日期范围 | 入职日期：2010-01-01至今 |
| 时间 | 限制时间范围 | 工作时间：08:00-18:00 |
| 文本长度 | 限制字符数 | 手机号：11位 |
| 自定义 | 使用公式判断 | =LEN(A1)=11 |
下拉列表（序列验证）
这是最常用的数据验证方式，让用户从预设列表中选择：
方法一：直接输入列表
数据 → 数据验证 → 允许：序列 → 来源：人事,财务,技术,市场
（注意逗号必须是英文逗号）
方法二：引用单元格区域
来源：=$F$1:$F$10
优点：修改F1:F10的内容会自动更新下拉选项
方法三：使用名称管理器
公式 → 名称管理器 → 新建名称"部门列表"→ 引用=$F$1:$F$10
来源：=部门列表
优点：名称可在整个工作簿中共享使用
方法四：动态下拉（使用表格自动扩展）
先创建表格(Ctrl+T)，来源引用表格列，新增数据自动纳入
级联下拉菜单：
要实现二级联动（选省份后城市列表自动变），需要INDIRECT函数配合
来源：=INDIRECT(A2) — 将A2中选中的省份名称作为名称引用
输入信息与出错警告
数据验证对话框的三个标签页：
设置：定义验证规则（上述内容）
输入信息（选填）：
标题：如"请输入年龄"
输入信息：如"请输入0-150之间的整数"
效果：选中单元格时自动显示提示框
出错警告：
样式：停止/警告/信息（三种级别）
停止：完全阻止输入（最严格）
警告：提示后允许选择是否继续
信息：仅提示，允许输入
标题：如"输入错误"
错误信息：如"年龄必须在0-150之间！" 自定义公式验证
高级验证公式示例：
1. 不允许重复：=COUNTIF($A$2:$A$100,A2)=1
2. 手机号格式：=AND(LEN(B2)=11,LEFT(B2)="1")
3. 身份证18位：=OR(LEN(C2)=15,LEN(C2)=18)
4. 大于等于另一列：=D2>=E2
5. 不允许空格：=A2=TRIM(A2)
6. 仅允许周一到周五：=WEEKDAY(B2,2)<=5圈释无效数据
数据 → 数据验证 → 圈释无效数据：将已输入但不符合当前验证规则的单元格用红色圆圈标记出来，方便审计已有数据。
实战练习
- 创建员工信息表，为"部门"设置下拉菜单（人事、财务、技术、市场）
- 为"年龄"列设置允许整数范围15-65
- 设置手机号列验证（必须11位且以1开头）
- 创建级联下拉：选省份后地级市自动更新
- 用圈释无效数据检查已有数据中的错误