下载掌阅APP，畅读海量书库
立即打开
在基础表单数据维护中，为了把错误数据消灭在萌芽之中，可以使用数据验证（又称数据有效性）来控制规范数据输入。
所谓数据验证，就是对单元格设置的一个规则，只有满足这个规则的数据才能输入到单元格，否则是不允许输入到单元格中的。数据验证就相当于在数据库中对字段的数据类型进行设置。
数据验证是一个非常有用并且效率极高的数据输入方法。利用数据验证，我们可以限制只能输入某种类型的数据、规定格式的数据、满足条件的数据，不能输入重复数等；还可以控制在工作表上输入数据的过程（例如不能空行输入，不能在几个单元格都输入数据，在几个单元格中只能输入某个单元格等）；当输入数据出现错误时，还可以提醒用户为什么出现了错误，应该如何去纠正。
灵活使用数据验证，并结合一些函数，可以设置非常复杂的限制输入数据条件，从而使数据的输入工作既快又准，工作效率也大大提高。
单击“数据”→“数据验证”按钮（如图2-1所示），打开“数据验证”对话框，如图2-2所示。从“允许”下拉列表中选择验证条件，就可以根据实际要求来设置各种数据验证了，如图2-3所示。
图2-1 “数据验证”按钮
图2-2 “数据验证”对话框
图2-3 选择验证条件
在“允许”下拉列表中选择“整数”，可展开关于整数的数据验证条件，其中包括，介于、未介于、等于、不等于、大于、小于、大于或等于、小于或等于6种，如图2-4所示。
例如，要限制在单元格中只能输入正整数（如销售商品的件数、套数等），就可以在“数据”下拉列表中选择“大于或等于”的条件，然后在“最小值”文本框中输入0，如图2-5所示。
图2-4 选择“整数”条件
图2-5 选择“大于或等于”，并输入0
为了更直观地得到数据输入提示信息，可以在“输入信息”选项卡中输入一段简短的说明文字，以提醒用户应该输入什么样的数据、怎么输入，如图2-6所示。
如果输入了不符合条件的数据，系统就会报错。此时，我们还可以在“出错警告”选项卡中进行设置，以警告错误的原因，如图2-7所示。
图2-6 输入提示信息文字
图2-7 输入警告信息文字
这样，只要单击设置了数据验证的单元格，就会出现提示文字“请输入正整数！”，如图2-8所示。如果输入错误，就会弹出警告对话框，如图2-9所示。
图2-8 输入提示信息，马上知道要输入什么样的数据
图2-9 输入的数据不满足条件，弹出警告对话框
例如，假设公司规定各个部门每天的日常费用开支不能超过1000元，可以是小数，但不能为负数，那么就可以进行如图2-10所示的数据验证设置。
（1）在“允许”下拉列表中选择“小数”。
（2）在“数据”下拉列表中选择“介于”。
（3）在“最小值”文本框中输入0。
（4）在“最大值”文本框中输入1000。
图2-10 只允许输入规定的正小数
大部分表格都要输入日期，但在有些情况下只允许输入规定的日期，比如只能输入2018年的日期、只能输入当天的日期、只能输入当月的日期等，此时，同样也可以使用数据验证并联合使用有关的函数来解决。
假如某个管理表格只允许输入2018年的日期，那么可以进行如图2-11所示的验证设置。
图2-11 只允许输入2018年的日期
（1）在“允许”下拉列表中选择“日期”。
（2）在“数据”下拉列表中选择“介于”。
（3）在“开始日期”文本框中输入2018-1-1。
（4）在“结束日期”文本框中输入2018-12-31。
假若某个管理表格只允许输入当天的日期，那么可以进行图2-12所示的验证设置。
（1）在“允许”下拉列表中选择“日期”。
（2）在“数据”下拉列表中选择“等于”。
（3）在“日期”文本框中输入公式“=TODAY（）”。
图2-12 只允许输入当天的日期
思考：为什么在“日期”文本框中必须输入“=TODAY（）”，而不能直接输入TODAY呢？因为TODAY是函数，在单独使用函数时，必须以公式的形式输入，也就是说，必须先输入等号（=）。
此外，TODAY函数没有参数，因此函数名后面的一对括号不能漏掉。
函数说明： TODAY
TODAY函数用于得到当天的日期。用法如下：
=TODAY（）
假若某个管理表格只允许输入当天以前的日期（含当日），那么可以进行如图2-13所示的验证设置。
（1）在“允许”下拉列表中选择“日期”。
（2）在“数据”下拉列表中选择“小于或等于”。
（3）在“结束日期”文本框中输入公式“=TODAY（）”。
图2-13 只允许输入当天（含）以前的日期
假若某个Excel表格只允许输入当天以前一周内的日期（不含当日），那么可以进行如图2-14所示的验证设置。
（1）在“允许”下拉列表中选择“日期”。
（2）在“数据”下拉列表中选择“介于”。
（3）在“开始日期”文本框中输入公式“=TODAY（）-7”。
（4）在“结束日期”文本框中输入公式“=TODAY（）-1”。
图2-14 只允许输入当天以前一周内的日期（不含当日）
假若在某个Excel表格只允许当天以前的本月日期，那么可以进行如图2-15所示的验证设置。
图2-15 只允许输入当天以前的本月日期
（1）在“允许”下拉列表中选择“日期”。
（2）在“数据”下拉列表中选择“介于”。
（3）在“开始日期”文本框中输入公式：
=EOMONTH(TODAY(),-1)+1
（4）在“结束日期”文本框中输入公式：
=TODAY()-1
公式“=EOMONTH（TODAY（），-1）+1”就是获取当月的第一天的日期，这里，利用了EOMONTH函数得到上个月最后一天日期，然后再加1就是本月第一天日期。
函数说明： EOMONTH
EOMONTH函数用于计算指定日期之前或之后几个月的月底日期。用法如下：
=EOMONTH（开始日期，月数）
例如，下面公式的结果是 2018-7-31：
=EOMONTH（'2018-4-15',3）
而下面的公式结果是 2018-1-31：
=EOMONTH（'2018-4-15',-3）
我们也可以使用验证限制时间的输入，其方法与限制日期输入是一样的。比如，员工的考勤表中，上班时间只能输入上午8点以前的时间，超过上午8点就算旷工；下班时间只能输入下午5点以后的时间，在下午5点以前就算早退。对此可以进行图2-16、图2-17所示的验证设置。
图2-16 只允许输入上午8点以前的时间
上班时间数据验证如下。
（1）在“允许”下拉列表中选择“时间”。
（2）在“数据”下拉列表中选择“小于或等于”。
（3）在“结束时间”文本框中输入“8：00：00”。
图2-17 只允许输入下午5点以后的时间
下班时间数据验证如下。
（1）在“允许”下拉列表中选择“时间”。
（2）在“数据”下拉列表中选择“大于或等于”。
（3）在“开始时间”文本框中输入“17：00：00”。
我们也可以对输入的文本进行控制，比如只能输入规定长度（或长度区间）的文本。例如输入邮政编码时限制文本的长度是6，那么可以进行图2-18所示的验证设置。
（1）在“允许”下拉列表中选择“文本长度”。
（2）在“数据”下拉列表中选择“等于”。
（3）在“长度”文本框中输入数字6。
那么，就在选定单元格区域内只能输入长度为6的字母、数值型文本或者纯数字，字符长度少于6或者多于6都是非法的。
但是，这种长度限制的数据验证，对数字和字母都是有效的。由于邮政编码是6位数字，为了更加精准控制，只能输入6位的数字编码，需要使用自定义条件来解决了。这个设置我们将在后面进行介绍。
图2-18 限制输入文本的长度
很多情况下，我们经常要重复输入一些固定数据。比如要在员工信息表的某列输入该员工所属部门名称，而这些部门名称就是那么几个固定的名称。此时利用数据验证，不仅可以实现部门名称的快速输入，也可以防止输入错误的、不规范的部门名称。
效果如图2-19所示，当单击B列数据区域的某个单元格时，就在该单元格的右侧出现一个下拉按钮，单击该按钮，就可以选择输入该序列的某个项目，或者人工输入序列中已存在的项目，输入不在序列中的其他数据都是非法的。
图2-19 从单元格下拉列表中选择输入数据
对要输入部门名称的表格列设置如下的数据验证，如图2-20所示。
（1）在“允许”下拉列表中选择“序列”。
（2）在“来源”文本框中输入部门名称序列，如“办公室，人力资源部，财务部，销售部，开发部，工程部”，注意该序列的各个项目之间用英文逗号隔开。
图2-20 只能输入指定的序列数据
在很多情况下，要输入的序列项目很多，或者每个项目都是很长的字符串，那么在“来源”文本框里输入这些序列名称便不太方便了。一种比较好的方法就是把这些序列数据保存在工作表的某列或者某行中，然后在“来源”文本框里引用这个单元格区域，如图2-21所示。
图2-21 将保存在工作表的某列（或某行）数据作为数据序列的来源
这种做法也有一个问题，对于基础表单来说，诸如部门名称、产品名称、客户名称等这样的基本资料数据，最好不要保存在当前源数据表单工作表中，而应该保存在另外一个专门保存基本资料数据的工作表中。换句话说，就是将日常不断输入数据、维护数据的表单工作表与基本资料工作表分开。此时，数据验证的设置方法与上面介绍的是一样的，直接引用基本资料工作表的数据即可，如图2-22所示。
图2-22 将保存在另外一个工作表的数据作为数据序列的来源
在设计如员工信息管理表格时，我们还会经常碰到这样的问题。比如要输入企业各个部门名称及其下属的员工姓名，如果将所有的员工姓名放在一个列表中，并利用此列表数据设置数据验证，那么很难判断某个员工是属于哪个部门的，容易造成张冠李戴的错误，如图2-23所示。
图2-23 无法确定某个员工是哪个部门的
我们能不能在A列输入部门名称后，在B列只能选择或输入该部门下的员工姓名，其他部门员工姓名不会出现在序列列表中呢？
使用多种限制的数据验证来制作二级下拉菜单，就可以解决这样的问题。
步骤 1 首先设计部门名称及其下属员工姓名列表，如图2-24所示。其中，第1行是部门名称，每个部门名称下面保存该部门的员工姓名。
图2-24 设计部门名称及其下属员工姓名列表
步骤 2 选择B列至I列含第1行部门名称及该部门下员工姓名在内的区域，单击“公式”→“定义的名称”→“根据所选内容创建”按钮，如图2-25所示。
图2-25 批量创建名称命令
步骤 3 打开“根据所选内容创建名称”对话框，选中“首行”复选框，然后单击“确定”按钮，将B列至I列的第2行开始往下的各列员工姓名区域分别定义名称，如图2-26所示。
步骤 4 再选择单元格区域B1：I1，单击名称框，输入名称“部门名称”，然后按Enter键，将这个区域定义为“部门名称”，如图2-27所示。
图2-26 先选取区域，再批量定义名称
图2-27 把第1行各个部门名称区域定义名称“部门名称”
单击“公式”→“定义的名称”→“名称管理器”按钮，打开“名称管理器”对话框，可以看到我们定义了很多名称，其中各个部门员工姓名区域的名称就是第1行的部门名称，如图2-28所示。
图2-28 定义的名称
步骤 5 选取单元格区域A2：A1000（或者到需要的行数），打开“数据验证”对话框，按图2-29所示进行设置。
● 在“允许”下拉列表中选择“序列”。
● 在“来源”文本框中输入公式“=部门名称”。
步骤 6 选取单元格区域B2：B1000（或者到需要的行数），打开“数据验证”对话框，按如图2-30所示进行设置。
● 在“允许”下拉列表中选择“序列”。
● 在“来源”文本框中输入公式“=INDIRECT（A2）”。
图2-29 为A列设置部门名称序列
图2-30 为B列设置某部门下员工姓名序列
这样，在A列的某个单元格中选择或输入部门名称，在该行B列的单元格内就只能选择或输入该部门下的员工姓名，如图2-31、图2-32所示。
图2-31 选择或输入“办公室”的员工姓名
图2-32 选择或输入“信息部”的员工姓名
函数说明： INDIRECT
INDIRECT函数用于把一个字符串表示的单元格地址转换为引用。用法如下：
=INDIRECT（字符串表示的单元格地址或名称，引用方式）
这里注意以下几点。
（1）INDIRECT转换的对象是一个文本字符串。
（2）这个文本字符串必须是能够表达为单元格地址或名称的，例如“C5”“M10”“预算！C5”。
（3）这个字符串是我们自己手工连接（&）起来的。
例如，若单元格D5保存的数据是A2，它是一个文本字符串，但这个A2恰好又是单元格A2的地址，那么公式“=INDIRECT（D5）”并不是引用单元格D5的数据，而是单元格A2的数据。
小知识：
名称就是给工作表中的对象命名的一个称谓，在公式或函数中，可以直接使用定义的名称进行计算，不必去理会这个名称所代表的对象在哪里。能够定义名称的对象如下。
（1）常量 。比如可以定义一个名称为“增值税”，它代表0.17。公式“=D2*增值税”中，这个名称“增值税”就是0.17。
（2）一个单元格 。比如把单元格A1定义名称为“年份”，若在公式中使用“年份”两字，就是引用单元格A1中指定的年份。
（3）单元格区域 。比如把B列定义名称为“年份”，D列定义名称为“销售量”，那么公式“=SUMIF（年份，2018，销售量）”就使用了2个名称，就是对B列进行条件判断，对D列求和，条件是2018。
（4）公式 。可以对创建的公式定义名称，以便更好地处理分析数据。比如把公式“=OFFSET（Sheet1！$A$1，，，Sheet1！$A：$A，Sheet1！$1：$1）”命名为data，就可以利用这个动态的名称制作基于动态数据源的数据透视表，而不必每次去更改数据源。
“老师，我设计了一个员工花名册，怎样才能控制在D列不能输入重复的员工身份证号码啊？而且还要限制身份证号码必须是18位。”
这样的问题，使用数据验证下的自定义规则即可解决，不过，在这种情况下，需要熟练使用逻辑函数、信息函数、查找函数、统计函数、条件表达式等。
对该同学的问题，可以设置这样的数据验证：在“允许”下拉列表里选择“自定义”，在“公式”文本框里输入下面的公式（如图2-33所示）：
=AND(COUNTIF($D$2:D2,D2)=1,LEN(D2)=18)
图2-33 只能输入18位不重复的身份证号码
这个公式中，有如下两个条件，它们必须都满足才能输入数据。
条件1：判断输入的数据是否唯一，使用COUNTIF函数进行统计，公式如下：
COUNTIF($D$2:D2,D2)=1
条件2：判断输入的数据长度是否为18位，使用LEN函数进行计算，公式如下：
LEN(D2)=18
最后使用AND函数将两个条件组合起来。
=AND（条件1，条件2）
在使用COUNTIF函数进行统计时，由于我们是统计刚刚输入的数据是否在前面已经输过了，因此统计区域是一个不断往下扩展的变动区域，但这个区域的第一个单元格永远是D2，因此统计区域的第一个单元格是绝对引用$D$2，最后一个单元格是相对引用D2，这样统计区域是$D$2：D2。
这样，当输入重复数据时，就会出现警告对话框，如图2-34所示。
图2-34 不允许输入重复数据
函数说明： COUNTIF
COUNTIF函数用于统计满足一个指定条件的单元格个数。用法如下：
=COUNTIF（统计区域，条件值）
例如，图2-35中的公式“=COUNTIF（B2：B10，'A'）”，结果为3，因为该区域有3个字母A。
图2-35 COUNTIF函数基本用法
函数说明： LEN
LEN函数用于计算字符串的长度，也就是字符串中字符的个数。用法如下：
=LEN（字符串）
例如，公式“=LEN（'Excel高效办公'）”，结果为9，因为有9个字符（5个字母和4个汉字）
函数说明： AND
AND函数用来组合几个与条件，也就是这几个条件必须同时满足。用法如下：
=AND（条件1，条件2，条件3，…）
假若在A列区域要输入这样的日期数据：下一个单元格的日期只能大于或等于上一个单元格的日期，不能小于上一个单元格的日期，那么同样可以利用数据验证进行控制。
数据验证的设置如图2-36所示，主要步骤如下。
步骤 1 选择A列单元格区域（从第2行往下选一定的行）。
步骤 2 打开“数据验证”对话框。
步骤 3 在“允许”下拉列表中选择“日期”。
步骤 4 在“数据”下拉列表中选择“大于”或“等于”。
步骤 5 在“日期”文本框中输入下面的公式：
=MAX($A$2:A2)
图2-36 设置只能输入大于或等于上一个单元格的日期
在这个公式中，利用MAX函数对输入的日期进行统计，得出前面已经输入的所有日期的最大值。注意，引用单元格区域的第1个单元格是绝对引用，而第2个单元格是相对引用。
这样，如果在下面的单元格输入了比前面小的日期，就被认为是非法日期，如图2-37所示。
图2-37 输入了非法的日期
大多数情况下数据表单的第1列是关键字段，不能是空白单元格，但其他列可以根据实际情况来输入数据或者不输入数据。如果在数据表单的第1列没有输入数据，那么其他各列的数据就无法判断是属于哪类的。比如是哪天的数据，哪个销售人员的数据，哪个城市的数据，哪个部门的数据等。
为了防止在数据表单的第1列没有输入数据时就在其他列输入数据，可以利用数据验证进行输入控制。
假设数据表单是工作表的A列至H列，A列为关键字段列，必须在A列的单元格输入数据后，才能在B列至H列对应行的单元格输入数据，我们可以进行如下的数据验证设置。
步骤 1 选择单元格区域B2：H1000（根据实际情况选择到一定的行）。
步骤 2 在“数据验证”对话框的“允许”下拉列表中选择“自定义”，在“公式”文本框中输入下面的公式，如图2-38所示。
=COUNTA($A2)>0
图2-38 设置数据验证，判断A列单元格是否输入了数据
这个公式是很容易理解的：对B列至D列的每个单元格，都会利用函数COUNTA来统计该行对应A列的单元格是否为空。如果不为空，那么函数COUNTA的返回值为1，公式“=COUNTA（$A2）>0”的返回值就是TRUE，因而数据是有效的；否则，如果对应A列的单元格为空，那么函数COUNTA的返回值为0，这样公式“=COUNTA（$A2）>0”的返回值就是FALSE，因而数据是无效的。
步骤 3 选择“出错警告”选项卡，在“错误信息”文本框中输入出错时的提示信息“A列对应单元格没有数据！”，如图2-39所示。
步骤 4 单击“确定”按钮，关闭“数据验证”对话框。
图2-39 设置错误提示信息
这样，如果A列的单元格没有数据，那么在B列至H列对应的任一单元格中都是不允许输入数据的，如图2-40所示。
图2-40 A列没有输入数据时，不允许在B列至H列对的任一应单元格中输入数据
函数说明： COUNTA
COUNTA函数用来统计单元格区域内不为空的单元格个数。用法如下：
=COUNTA（单元格区域）
在大多数的表单中，作为一个完整的数据记录清单，每一条记录的信息都应该是完整的，因此在输入数据时就要保证每行的每个单元格都输入了数据。如果在某行的单元格没有输全数据，那么在下一行就不能开始输入新的数据，除非上一行的所有单元格都输入了数据。利用数据验证，很容易就可以实现这个目的。
假设数据清单区域是A列至H列，并要求必须在上一行单元格都输入数据后才能在下一行输入数据，则设置数据验证的具体步骤如下。
步骤 1 选择A列至H列的单元格区域A2：H1000（根据实际情况选择到一定的行）。
步骤 2 打开“数据验证”对话框。
步骤 3 在“允许”下拉列表中选择“自定义”，在“公式”文本框中输入下面的公式，如图2-41所示。
=COUNTA($A2:$H2)=8
这个公式不难理解：当在本行的某个单元格中输入数据时，都会对上一行A列至H列的单元格利用函数COUNTA来统计不为空的单元格个数。如果上一行A列至H列不为空的单元格个数恰好等于8，表明上一行所有单元格都输入了数据，那么就可以在下一行开始输入新的数据，否则就被禁止。
图2-41 设置数据验证条件
步骤 4 选择“出错警告”选项卡，在“错误信息”文本框中输入出错时的提示信息“上一行还没有输全数据！”，如图2-42所示。
步骤 5 单击“确定”按钮，关闭“数据验证”对话框。
图2-42 设置错误提示信息
这样，如果在某行没有输全数据，那么就不能在下一行输入新的数据，如图2-43所示。
图2-43 第3行还没有输全数据，因此在第4行的任一单元格中均不能输入新的数据
在实际工作表中，一个标准的、完善的数据管理表单，应当是数据完整并且符合规则要求的。为了防止输入不规范的数据，或者输入的数据不完整，应当在输入数据时就进行控制。因此，实际管理表单在很多情况下需要设置很多条件，进行多种限制。
图2-44所示是一个简单的员工信息管理表单，A列保存工号，对A列的要求如下。
（1）只能输入4位工号。
（2）不允许重复。
（3）必须保证上一行所有数据都输入完毕，才能在下一行输入新的工号。
图2-44 一个简单的员工信息管理表单
这是一个多条件的数据有效性问题。对A列输入工号有效性进行设置的具体步骤如下。
步骤 1 选择A列从第2行开始的单元格区域A2：A1000（或到需要的行）。
步骤 2 打开“数据验证”对话框。
步骤 3 选择“设置”选项卡，在“允许”下拉列表中选择“自定义”，在“公式”文本框中输入如下公式：
=AND(LEN(A2)=4,COUNTIF($A$2:A2,A2)=1,COUNTA($A1:$E1)=5)
效果如图2-45所示。
这个公式有如下3个条件。
条件1：LEN（A2）=4，限制只能输入4位工号。
条件2：COUNTIF（$A$2：A2，A2）=1，限制不能输入重复工号。
条件3：COUNTA（$A1：$E1）=5，限制要保证上一行数据都已经全部输入。
图2-45 多个条件的数据验证设置
步骤 4 选择“出错警告”选项卡，在“错误信息”文本框中输入出错时的提示信息，如图2-46所示。
步骤 5 单击“确定”按钮，关闭“数据验证”对话框。
这样，在A列输入的工号，必须完全满足前面要求的几个基本条件，否则不允许在下一行输入新的工号，如图2-47所示。
图2-46 设置出错警告信息
图2-47 不能输入新的工号，因为不满足条件
清除数据验证是很简单的，先定位要清除的数据验证区域，然后打开“数据验证”对话框，单击“全部清除”按钮即可，如图2-48所示。
图2-48 清除数据验证
在使用数据验证时，要特别注意的是，数据验证只能控制纯手工输入入数据，无法控制填充数据、复制粘贴数据等，这种操作会破坏已设置的数据验证规则，使数据验证失去作用。
如果已经在单元格输入了数据，后来才设置的数据验证，那么对原来的数据是没有影响的，仅仅对以后输入新数据才有影响。