最新

Excel 怎么用数据验证限制输入内容

共享出去的表格,最怕别人把日期写成文字、把数量填成负数。数据验证能把单元格限定成指定类型、范围或下拉选项,不合规的内容直接拒绝录入。

共享出去的表格,最怕别人把日期写成文字、把数量填成负数,或者在同一列里混进好几种写法。数据验证(部分版本叫数据有效性)能提前把规则写进单元格,不符合的内容直接拒绝录入。这篇讲入口、步骤和几处最容易踩的坑。

入口在哪

入口在「数据」选项卡的「数据验证」按钮里,先选好单元格再点开。旧版 Excel 和部分 WPS 里这个功能叫「数据有效性」,位置一样,名称差别只是版本问题。它只作用于当前选中的区域,不会自动管到整列,这一点要特别注意。

具体步骤

  1. 先选中要限制的区域。整列点列标更省事,但表头也会被限制,所以一般从第一个数据格选到最后一个数据格。
  2. 点「数据验证」,在「设置」里选「允许」的类型。整数、小数、日期、序列、文本长度这几种最常用。
  3. 选「序列」时,来源可以写用逗号分隔的选项,也可以引用另一列写好的选项。选项多、以后要改,一律引用单元格区域,改一处全表跟着变。
  4. 切到「出错警告」页,写清提示语,例如「只能填 1 到 100 的整数」。保持默认的「停止」,填错就录不进去。
  5. 想提醒但不强制,把样式改成「警告」或「信息」,用户仍能强行录入。
  6. 规则写好以后复制一个格子,用选择性粘贴挑「验证」,比重新设一遍快得多。

几个坑

  • 粘贴会覆盖规则。从别处复制数据直接粘进来,验证规则可能被清掉,看起来设过了其实没有。
  • 序列来源引用整列时,空单元格会在下拉里留出空行,最好把来源范围限定到有内容的行。
  • 引用另一个文件里的区域,对方文件关掉或改名,下拉就会失效。
  • 数据验证只管新录入,不管已有数据。设置完不会回头检查以前填错的内容。
  • 它挡不住程序写入。导入、公式、宏写入的内容都不受验证约束。

怎么验证

挑一个格子,故意填一个不符合规则的内容,看是否弹出提示并被拒绝。再确认下拉列表的选项完整、末尾没有空行。最后在区域中间插入一行,检查新行有没有继承规则。

常见问题

下拉选项想允许手填怎么办

数据验证里取消下拉箭头只是隐藏箭头,并不能放开手填。想让用户既能选又能填新值,得改用组合框控件,或者把出错警告设成「信息」放行。

为什么复制粘贴后规则消失了

普通粘贴会连格式一起覆盖,源单元格没有规则就跟着没了。改用选择性粘贴里的「验证」,或者粘贴完再设一次。

能不能限制重复值

可以,用自定义公式配合计数函数,判断该值在本区域出现的次数是否大于一。公式要按相对引用写,否则整片区域会一起报错。

一句话总结:数据验证是录入前的门禁,选好区域、把选项来源引到单元格、出错警告写清楚,再记住粘贴和旧数据这两个缺口。