1.4 设置有效性对输入数据审核
在Excel中进行数据输入时，有些输入内容是需要进行审核的，不能什么都可以输入。为此，Excel提供了“数据有效性”工具，利用这个工具，操作者可以有针对性地输入数据，在输入错误的数据后，系统会出现错误信息框，提醒用户进行修改。
如图1-23所示学生信息表，该数据表要满足以下输入条件：
图1-23 需要设置“数据有效性”的人员信息表
（1）“编号”下面的内容输入都必须以“DY”开头；
（2）“姓名”下面都应该是汉字，所以最好能实现中文输入法自动切换；
（3）“性别”应该可以从“男”、“女”组成的下拉列表中选择输入；
（4）“身份证号”要求必须为18位（假设已经全部为第二代号码）；
（5）“入学日期”输入时应在新生注册“2012年10月1日”之前；
（6）“成绩”按照学校的管理政策，应该是0至100之间的整数。
下面就通过“数据有效性”设置来对输入的数据按照上述要求进行审核。
1.利用有效性限制数值的输入范围
在图1-23表格中，根据前面对成绩的限制，成绩应为0至100之间的整数。为了防止错误地输入成绩数据（如错误地把80输成了800），需要为其设置输入有效范围。下面以该项设置为例，详细介绍“数据有效性”的整体操作步骤：
（1）选择需要输入成绩数据的区域（本例选取F2:F11，根据实际人数需要，后者中行号可适当扩大，作为教学用例，此处假设输入人数只有9人，下面的一样处理）。
说明：数据有效性设置之前，必须首先选取所需单元格或区域；另外，数据有效性应该在输入之前设置，否则不会自动起作用。
（2）运行“数据”菜单栏下“数据工具”选项中的“数据有效性”命令，打开“数据有效性”对话框。
（3）选择“设置”选项卡，在“允许”下拉列表中选择“整数”，在“数据”下拉列表中选择“介于”，在“最小值”和“最大值”中分别输入“0”和“100”，如图1-24所示。
图1-24 设置有效性条件
说明：在“允许”下拉列表框中，还有“小数”“序列”“时间”“日期”“文本长度”“自定义”等类型可选，选择的类型不同，其有效检验的数据类型就不同，特别是在选择“自定义”后，用户可以自己输入有效性公式。
（4）选择“输入信息”选项卡，在“标题”文本框中输入“请输入成绩”，在“输入信息”下的文本框中输入“请输入该学生的成绩（0-100之间的整数）”，“选定单元格时显示输入信息”复选框保持默认的选中状态，如图1-25所示。
图1-25 设置输入提示信息
（5）选择“出错警告”选项卡，在“样式”下拉列表框中选择“停止”，在“标题”文本框中输入“数据错误”，在“错误信息”文本框中输入“成绩数据超过可能范围，请核对！”，如图1-26所示。
说明：
① 在图1-26所示“样式”下共有3个选项：停止、警告、信息，分别对应不同标志，具有不同的含义，可以根据不同情况设置，一般选取审核条件最严格的“停止”。
图1-26 设置出错时提示信息
② 如在数据输入中有小数点的输入时，一般会在“输入法模式”选项卡中的“模式”下拉列表框中选择“关闭（英文模式）”，如图1-27所示。这个设置是在输入数字时自动关闭中文输入方法，切换到英文输入状态，以免在输入小数点时输入汉字的句号。
图1-27 设置输入法模式
（6）单击“确定”按钮，关闭“数据有效性”对话框。
经过以上操作，指定单元格区域的数据有效性就设置完毕。单击该区域内任意一个单元格，就会在其旁边显示一条输入提示信息，如图1-28所示。根据该提示，可以输入正确的数字，当输入的数字超过规定的范围时，就会弹出如图1-29所示的错误警告信息。
图1-28 在单元格旁边出现的输入提示信息
图1-29 输入超出范围数字之后出现的错误警告信息
说明：如果不再需要有效设置条件，也可将其删除。操作时，先选定相应单元格，然后打开“数据有效性”对话框，单击“全部清除”按钮，最后单击“确定”按钮即可。
2.利用有效性限制输入数据的格式
限制输入数据的格式就是限定单元格录入数据为特定格式，如日期、整数、文本等。
限定数据的格式有助于提高输入的正确率。上面图1-23所示的表格例子中，“入学日期”一列应该必须输入日期型数据，根据题目要求，输入的日期必须在学生注册日期“2012年10月1日”之前。
以上有效性限制只要按照图1-30所示样式设置有效条件即可，其余选项不再说明。
图1-30 对“入学日期”的限制
3.利用有效性限制文本的录入长度
限制文本的录入长度就是在单元格中录取数字和文本的长度不满足条件时，能够阻止其录入。前面图1-23所示的表格例子中，“身份证号”要求必须为18位。
该有效性审核只要按照图1-31所示样式设置有效性条件即可，其余选项卡不再说明。
图1-31 对“身份证号”输入位数的限制
4.利用有效性确保必须输入特定内容
有时候，要求输入的内容必须包含特定的内容（比如必须以指定字母开头）。对于这种限制条件，也可以在“数据有效性”对话框中通过输入有效性公式进行设置。
前面图1-23所示的表格例子中，“编号”下面的内容输入都必须以“DY”开头。该有效性条件只要按照图1-32所示样式设置有效性条件即可，其余选项卡不再说明。
图1-32 利用有效性确保必须输入特定内容
说明：上面的有效公式“=COUNTIF(A3，"DY*")=1”中，“*”为通配符，能够用来代替任何字符，如果输入的单元格是以“DY”开头的，该公式为真，则允许输入数据，否则数据输入将会被阻止。需要注意图中绝对单元格和相对单元格的不同使用。如对COUNTIF函数或者对相对单元格和绝对单元格的区别的理解有困难，可以先跳过本例的介绍，等学过公式和函数的相关知识后再进行解释。其实，在有效性中通过自定义公式来设置条件是其应用的高级阶段，也是其灵活性和技巧性所在。
5.利用有效性设置汉字字段自动切换汉字输入法
在数据表中，往往具有英文字符和汉字字段，又有汉字文本字段，在其中输入数据时，难免需要在中文输入法状态之间切换。其实，利用Excel的“数据有效性”工具可以设置汉字字段自动切换中文输入法状态的有效性设置。如图1-23所示的表格例子中，当光标移动到“姓名”字段下面，就应该自动切换为中文输入法。
以上面“姓名”字段为例，在“数据有效性”对话框中只能按照图1-33样式设置输入法模式为“打开”状态即可，其余选项卡不再说明。
图1-33 对汉字字段自动切换成中文输入法的有效性设置
6.利用有效性设置只能接受不超过总数的数值
下面再看一个有效性设置实例，在实例设置后，单元格只能接受不超过总数的数值。
图1-34所示为一个简单项目预算表，在区域C2:C6中输入各个预算项目的金额，计划总预算在G8单元格中。以下“数据有效性”公式设置“=SUM($C$2:$C$6)<=$G$8”保证了各个预算项目的总和不能超过计划总预算。当用户尝试在C6中输入2000时，弹出事先设置的错误提示信息，如图1-34所示。
图1-34 利用“数据有效性”确保区域总数不超过特定数值
除以上各种设置之外，利用有效性设置，在“允许”列表框选择“序列”数据后，还可以构成下拉列表输入，例如，想对“性别”字段设置有效性，使性别输入时可以从“男”“女”中选择，需在“允许”列表框中选择“序列”，并在随之出现的来源文本框中输入男女自定义序列（注意：序列中各项中间的逗号必须为英文状态符号）。