收藏 | 用好数据有效性，再也不怕别人给错数据了

摘要：身处大数据时代的我们，不管从事什么职业，或多或少都会接触各种各样的数据，必然也伴随着数据处理。据统计，数据处理的过程50%的精力都会花在脏数据的处理上，从数据录入源头进行控制，其实就是在简便我们未来的数据处理。

本文在介绍数据有效性的操作基础上，详细介绍了利用Excel数据有效性进行空格、指定日期区间、重复数据等输入限制。

（全文共1987字，读完全文预计6~8分钟）

一图看全文

“数据验证”在Excel 2013之前的版本称为“数据有效性”，通过对单元格设置“数据验证”，不仅可以提升数据录入的准确率，还可以借助其提示功能和预置选型提升录入体验与录入效率。此外，借助“数据验证”功能还可以在已输入的数据区域查看不符合要求的数据，即“数据验证”的“圈释无效数据”功能。其选项卡界面如下：

（一）常规输入

在“数据验证”所提供的【验证条件】中，Excel提供了任何值、整数、小数、序号、日期、时间、文本长度、自定义等8种验证条件，而我们习惯上将除序号、文本长度与自定义外的其他格式统称为常规格式。就此几种格式操作起来较为容易上手，不再一一进行解释。下面我们以【整数】条件为例进行演示。

同理，我们可以通过对验证条件设置为其他格式进行单元格输入限制。

（二）序列限制下拉菜单

有时候，我们为了提高数据准确性，同时提高数据录入效率会将待录数据进行序列限制，在录入过程中，直接根据所提供的下拉菜单进行点选，即可完成输入

就【验证条件】中【序号】的使用，我们提供了两种操作方法，即引用目标区域和直接录入的方法。但不管哪种办法，其优点在于直接，缺点在于无法动态适应，不利于后期维护。比如“基础表”中项目有增加时，就无法体现在数据验证中。至于如何进行动态适应录入限制，我们将在未来的文章中分别探讨。

直接引用目标区域的方法适用于多数据项目情况下，直接引用所需数据源，如下图所示。

直接录入法适用于数据项目较少的情况，即在来源中直接录入数据选项，项目之间需要用半角逗号“,”隔开，如下图所示

（三）自定义验证条件

我们在第一节学习了常规格式的限制，但这远远不能满足我们对数据准确性的要求。以【日期】条件为例，我们可以通过规范日期范围对录入数据进行限制，但一旦涉及到需要限制录入“本月/周日期”便无法简简单单通过常规格式限制完成。同样的我们限制了文本长度，却无法限制重复数据的录入与多余空格的录入。解决以上这些问题，我们提供的方法是——利用自定义进行限制。

3.1 限制输入空格

在输入两个字的姓名时，有些用户喜欢在姓与名之间插入空格，以达到三个字姓名对齐的目的，但这种方式会严重影响数据的正确性，为后面的数据查询匹配等后续处理产生不良影响，需要杜绝。

为实现此数据验证，仅需要在【自定义】的【公式】中输入如下公式

=LEN(B2)=LEN(SUBSTITUTE(B2,' ',))

其中LEN函数为文本函数，即出该单元格的字符数量；SUBSTITUTE函数为文本函数，即将单元格中内容所含空格” “替换为空值。具体函数讲解将在【函数】专题详细讲解。

如果是英文姓名，则允许单词之间有一个空格，但不允许在字符串首尾插入多余空格，可以用下面的公式代替上面的公式。

=B2=TRIM(B2)

其中TRIM函数为清除除单词间单个空格外的其余所有空格。

3.2 限制输入指定范围内的日期

为保证账期，特别是在财务数据的录入中，存在着各种对日期的限制，如上面所提到的限制输入本月日期，可通过在【日期】条件中输入动态规则，使之只能输入系统所在月份的日期。

【开始日期】编辑框公式如下：

=DATE(YEAR(TODAY()),MONTH(TODAY()),1)