2.2 为非序列设置数据验证
为透彻介绍Excel的数据验证功能，新建一个Excel文件，命名为“第2章 数据验证.xlsx”，随着后面章节的介绍，将增加一些工作表。
2.2.1 为数值型数据设置数据验证
2.2.1.1 为常量的数据验证设置逻辑条件
在设置数据验证时，在“设置”选项卡的“验证条件”组中，在“允许”下拉列表中，当选择“整数”“小数”“日期”“时间”“文本长度”等数值型数据时，需要在“数据”下拉列表中选择数据的逻辑条件，这些条件包括“介于”“未介于”“等于”“不等于”“大于”“小于”“大于或等于”“小于或等于”等，指示了数据的范围，如图2-12所示。
图2-12 数值型数据和逻辑条件
然后在相应的数值框中输入具体的数值。这些数值不太方便改变，要改变这些数值，只有重新设置数据验证，因而这些数值可以叫作常量。使用常量设置数据验证比较简单，这里不再赘述。
2.2.1.2 数据验证使用变量的两种情况
如果不在数值框中输入具体的数值，而是通过引用伸缩框引用单元格中的具体数值或由函数公式计算出来的数值，这就是使用变量。引用单元格时，会在所引用的单元格前面添加一个等号，这意味着引用的实质是构成一个公式。单元格中的数值可以很方便地改变，数据验证的数值会随之变化。
在文件“第2章 数据验证”中新建一张工作表，更名为“非序列”。在此工作表，建立一个示例表，如图2-13所示。
图2-13 “非序列”示例表（为数值型数据设置数据验证）
下面将介绍设置变量的两种情况。
1．使用单元格引用设置变量
表中，A3:A5区域的数据为语文成绩，必须大于等于0、小于等于满分。满分是一个变量，可以根据需要灵活调整。比如，小学语文满分为100分，中学语文满分为150分。满分分数放在G2单元格。如何对A3:A5区域设置数据验证呢？
打开“数据验证”对话框后，具体操作步骤如下：
1 选择“设置”选项卡。
2 在“允许”下拉列表中选择“小数”选项。
3 在“数据”下拉列表中选择“介于”选项。
4 在“最小值”框中输入“0”。
5 在“最大值”框中输入如下公式：
6 单击“确定”按钮，完成设置。
“设置”选项卡的设置过程如图2-14所示。
图2-14 设置使用变量
A3:A5区域设置好数据验证后，当G2单元格的满分值为“150”时，在A3:A5区域能够正常输入0～150的分数（包含小数）。如果分数超过这个范围，就会弹出警告。当G2单元格的满分值修改为“100”时，在A3:A5区域能够正常输入0～100的分数（包含小数）。如果分数超过这个范围，也会弹出警告。
2．使用函数公式设置变量
表中，B3:B5区域的数据为某项数量，必须介于最小值和最大值两个数值之间，最小值和最大值是变量，由H2:H5区域的数据产生。如何对B3:B5区域设置数据验证呢？
打开“数据验证”对话框后，具体操作步骤如下：
1 选择“设置”选项卡。
2 在“允许”下拉列表中选择“整数”选项。
3 在“数据”下拉列表中选择“介于”选项。
4 在“最小值”框中用MIN函数组成函数公式：
5 在“最大值”框中用MAX函数组成函数公式：
6 单击“确定”按钮，完成设置。
“设置”选项卡的设置过程如图2-15所示。
图2-15 数据验证使用极值函数
B3:B5区域设置好数据验证后，当H2:H5区域输入了数据，H2:H5区域的最小值“15”和最大值“40”就作为B3:B5区域数据验证的范围15～40。如果B3:B5区域的数据超过这个范围，就会弹出警告。
数据验证所用的函数公式可以与常量组合，一般会用到单元格引用，函数公式值会随着单元格值的变化而变化，因而是动态可变的，也是变量，有很大的拓展余地。
【函数公式解析】
在本例第一个公式中，MIN函数返回一组值中的最小值。具体语法为：
number1必需。
number2，…可选。最多有255个参数。
在本例第二个公式中，MAX函数返回一组值中的最大值。具体语法与MIN相同。
2.2.1.3 为动态日期设置数据验证
“明日复明日，明日何其多。”时间长河总是在悄无声息地缓慢流淌。现实生活中的时间包括日期。在Excel单元格格式中，日期和时间是有严格区分的，年月日为日期，时分秒为时间。这些日期和时间表示为英语单词的缩写:Y代表年（Year），M代表月（Month），D代表日（Day），H代表时（Hour），M代表分（Minute），S代表秒（Second）。
Excel将日期存储为可用于计算的序列号，在默认情况下，Excel使用的是1900年日期系统，1900年1月1日的序列号是“1”。Excel还将时间存储为小数，因为时间被看作天的一部分。该小数值的范围为0（零）到0.99999999，表示0:00:00（12:00:00AM）至23:59:59（11:59:59PM）的时间。在Excel中，可将日期和时间数据设置为日期或时间类型的单元格格式。
由于时间（日期）总是处于不断的变化中，现实工作中，有时会需要为动态日期设置数据验证。下面通过三个案例介绍动态日期的数据验证的设置。
例1：在“非序列”工作表，C3:C5区域的数据为完成日期，必须在从今日起的未来7～100天之间，应该如何设置数据验证呢？
打开“数据验证”对话框后，C3:C5区域的数据验证可以这样设置：
1 选择“设置”选项卡。
2 在“允许”下拉列表中选择“日期”选项。
3 在“数据”下拉列表中选择“介于”选项。
4 在“开始日期”框可用TODAY函数组成函数公式：
5 在“结束日期”框也可用TODAY函数组成函数公式：
6 单击“确定”按钮，完成设置。
“设置”选项卡的设置过程如图2-16所示。
图2-16 数据验证使用日期函数
C3:C5区域设置数据验证后，由于TODAY函数取得“今日”的序列号，而“今日”每天都在变化，因而C3:C5区域的数据始终在从“今日”起的未来7～100天。
【函数公式解析】
在例1的公式中，TODAY函数返回当前日期的序列号。具体语法为：
TODAY函数语法没有参数。如果在输入该函数之前单元格格式为“常规”，Excel会将单元格格式更改为“日期”。若要显示序列号，必须将单元格格式更改为“常规”或“数字”。
例2：在“非序列”工作表，D3:D5区域的数据为签到时间，必须早于此时此刻，应该如何设置数据验证呢？
打开“数据验证”对话框后，D3:D5区域的数据验证可以这样设置：
1 选择“设置”选项卡。
2 在“允许”下拉列表中选择“时间”选项。
3 在“数据”下拉列表中选择“小于或等于”选项。
4 在“结束时间”输入如下函数公式：
5 单击“确定”按钮，完成设置。
“设置”选项卡的设置过程如图2-17所示。
图2-17 数据验证使用时间函数
D3:D5区域设置数据验证后，其签到时间若晚于此时此刻，就无法输入。这样，可以有效杜绝作假情况的发生。
【函数公式解析】
在例2的公式中，用到多个时间函数，下面从内层到外层逐一介绍所用到的函数。
式中，NOW函数返回当前日期和时间的序列号。具体语法为：
NOW函数语法没有参数。序列号中，小数点右边的数字表示时间，左边的数字表示日期。如果在输入NOW函数前，单元格格式为“常规”，Excel会更改单元格格式，使其与区域设置的日期和时间格式匹配。可以在功能区“开始”选项卡上的“数字”组中使用命令来更改日期和时间格式。
式中，HOUR函数返回时间值的小时数。小时数是介于0（12:00 AM）到23（11:00 PM）之间的整数。具体语法为：
serial_number必需。一个时间值，其中包含要查找的小时数。时间值有多种输入方式：带引号的文本字符串（例如“6:45 PM”）、十进制数（例如0.78125表示6:45 PM）或其他公式或函数的结果（例如TIMEVALUE（“6:45 PM”））。
式中，MINUTE函数返回时间值中的分钟数，分钟数是一个介于0到59的整数。具体语法为：
serial_number必需。一个时间值，其中包含要查找的分钟。
式中，SECOND返回时间值的秒数，秒数是0到59范围内的整数。具体语法为：
serial_number必需。一个时间值，其中包含要查找的秒数。
式中，TIME函数返回特定时间的十进制数字。如果在输入该函数之前单元格格式为“常规”，则结果将使用日期格式。由TIME返回的十进制数字是一个范围为0（零）到0.99988426的值，表示0:00:00（12:00:00AM）～23:59:59（11:59:59PM）的时间。具体语法为：
hour（小时）必需。0（零）到32767之间的数字，代表小时。任何大于23的值都会除以24，余数将作为小时值。例如，TIME（27，0，0）=TIME（3，0，0）=0.125或3:00AM。
minute（分钟）必需。0到32767之间的数字，代表分钟。任何大于59的值将转换为小时和分钟。例如，TIME（0,750，0）=TIME（12，30，0）=0.520833或12:30PM。
second（秒）必需。0到32767之间的数字，代表秒。任何大于59的值将转换为小时、分钟和秒。例如，TIME（0，0，2000）=TIME（0，33，20）=0.023148或12:33:20AM。
本例，TIME函数合成当前时间。
例3：E3:E5区域的数据为进校日期，必须按升序输入，也就是按进校日期的先后顺序输入，应该如何设置数据验证呢？
打开“数据验证”对话框后，具体操作步骤如下：
1 选择“设置”选项卡。
2 在“允许”下拉列表中选择“日期”选项。
3 在“数据”下拉列表中选择“大于或等于”选项。
4 在“开始日期”框中输入如下函数公式：
5 单击“确定”按钮，完成设置。
“设置”选项卡的设置和相对引用的变化，如图2-18所示。
图2-18 数据验证使用相对引用实现日期升序
【函数公式解析】
在例3的公式中，MAX函数返回一组值中的最大值，其参数$E$3:$E3区域是绝对引用和相对引用的结合。后一个E3单元格是列绝对行相对引用，行号随活动单元格的变化而变化的。设置数据验证时，显示为E3单元格。当E5单元格成为当前活动单元格时，打开“数据验证”对话框，会发现公式的后一个E3单元格就变成了E5单元格，相对地址发生了变化。
2.2.2 使用公式自定义数据验证
设置数据验证时，在“设置”选项卡中，选择“验证条件”的“允许”框中的“自定义”选项，可以充分发挥思维能力，设置函数公式约束数据，满足特殊需要。函数公式的值为TRUE或不为0的数值，就启用验证条件，允许符合条件的数据“入室就座”；否则，就禁止“不懂礼数”的数据输入。
为便于介绍，在“非序列”工作表中建立一个示例表，如图2-19所示。
图2-19 “非序列”示例表（验证不重复值和字符位置）
2.2.2.1 验证不重复值
不重复值一般使用在名单、项目、品种等方面，用途广泛。表中，A9:A11区域的数据为教职工姓名，必须为唯一的名字，不能重复，应该如何设置数据验证呢？
打开“数据验证”对话框后，数据验证可以这样设置：
1 选择“设置”选项卡。
2 在“允许”下拉列表中选择“自定义”选项。
3 在“公式”框中输入如下函数公式：
4 单击“确定”按钮，完成设置。
“设置”选项卡的过程设置如图2-20所示。
图2-20 在自定义中使用函数公式
【函数公式解析】
在本例的公式中，COUNTIF是一个功能强大的统计函数，用于统计满足某个条件的单元格的数量。具体语法为：
range必需。要计算单元格区域。范围可以包含数字、数组、命名的区域或包含数字的引用，忽略空值和文本值。该参数不能是数组。
criteria必需。用于决定要统计哪些单元格的数量的数字、表达式、单元格引用或文本字符串。
COUNTIF函数不直接支持基于单元格背景或字体颜色计算单元格数量。COUNTIF仅使用一个条件。在本例中，COUNTIF函数统计A9:A11区域的某一名字的个数，“1”表示“唯一”。
由于COUNTIF函数第2个参数可以为数字、表达式、单元格引用或文本字符串，条件组合极为丰富，因此可以设置不同的条件进行单条件计数，具有广泛的用途，值得用户深入研究。
假若源数据在A1:A10区域，现将COUNTIF函数的使用方法汇总如表2-1所示。
表2-1 COUNTIF函数的使用方法
2.2.2.2 验证字符位置
在一些文本数据中，对字符可能有一些特殊的规定。比如，允许在什么位置输入什么字符，字符长度如何。这种规定可以通过设置数据验证对输入的数据予以控制。下面将用四个案例进行介绍。
例1：B9:B11区域的数据为教职工编号，第一位必须为字母“J”“Z”“G”，分别代表教师、职员、工勤类别，后面三位为数字编号，应该如何设置数据验证呢？
打开“数据验证”对话框后，数据验证可以这样设置：在“允许”下拉列表中选择“自定义”选项，在“公式”框中输入如下函数公式：
单击“确定”按钮，完成设置。
【函数公式解析】
在例1的公式中，用到多个文本函数和逻辑函数，下面由里到外逐一介绍相关函数。
式中，LEFT函数从文本字符串的第一个字符开始返回指定个数的字符。具体语法为：
text必需。包含要提取的字符的文本字符串。
num_chars可选。指定LEFT提取的字符的数量。num_chars必须大于或等于零。如果num_chars大于文本长度，则LEFT返回全部文本。如果省略num_chars，则假定其值为1。
式中，OR函数是一个逻辑函数，返回一个逻辑值。参数之间是“或”的逻辑关系，只要有一个参数满足条件即为TRUE。如果所有参数都为FALSE，则返回FALSE。具体语法为：
logical1必需。需要进行测试的条件，参数必须计算为逻辑值，测试结果可以为TRUE、FALSE或为包含逻辑值的数组或引用。
logical2，…可选。可以多达255个参数。
式中，LEN函数返回文本字符串中的字符个数。具体语法为：
text必需。要查找其长度的文本，空格将作为字符进行计数。
式中，AND函数也是一个逻辑函数，用于确定测试中的所有条件是否均为TRUE，参数之间是“和”的逻辑关系。所有参数的计算结果为TRUE时，返回TRUE。只要有一个参数的计算结果为FALSE，即返回FALSE。具体语法为：
logical1必需。要测试的第一个条件，测试结果可以为TRUE、FALSE或为包含逻辑值的数组或引用。
logical2，…可选。要测试的其他条件，其计算结果可以为TRUE或FALSE，最多可包含255个条件。
本例，LEFT函数截取B9单元格文本字符串左边第一位字符。然后用OR函数判断，只要这个字符与字母“J”“Z”“G”中的任意一个相符，即为TRUE。最后用AND函数再次判断，只要同时满足OR函数的判断和LEN函数返回的文本长度，即为TRUE。
例2：C9:C11区域的数据为教职工的另一种编号，要求数据第一位是S，第二位不限，第三位是4，总位数等于8位，应该如何设置数据验证呢？
打开“数据验证”对话框后，数据验证可以这样设置：在“允许”下拉列表中选择“自定义”选项，在“公式”框中输入如下函数公式：
单击“确定”按钮，完成设置。
【函数公式解析】
在例2的公式中，FIND函数用于在第二个文本串中定位第一个文本串，并返回第一个文本串的起始位置的值，该值从第二个文本串的第一个字符算起。具体语法为：
find_text必需。要查找的文本。
within_text必需。包含要查找文本的文本。
start_num可选。指定开始进行查找的字符。within_text中的首字符是编号为1的字符。如果省略start_num，则假定其值为1。
在例2的公式中，MID函数返回文本字符串中从指定位置开始的特定数目的字符，该数目由用户指定。具体语法为：
text必需。包含要提取字符的文本字符串。
start_num必需。文本中要提取的第一个字符的位置。文本中第一个字符的start_num为1，以此类推。
num_chars必需。指定希望MID从文本中返回字符的个数。
本例，FIND函数可用LEFT函数代替。“1*”表示将MID函数截取的字符串变成数字型数据。“1*MID（C9,3,1）=4”也可以改成“MID（C9,3,1）=“4””。
例3：D9:D11区域的数据为教职工跑步的成绩，要求格式为“×分×秒”，带“分”“秒”字样，应该如何设置数据验证呢？
打开“数据验证”对话框后，数据验证可以这样设置：在“允许”下拉列表中选择“自定义”选项，在“公式”框中输入如下公式：
单击“确定”按钮，完成设置。
例4：E9:E11区域的数据为教职工登山的成绩，要求格式为“××小时××分××秒”或“××分××秒”，带“小时”“分秒”字样，应该如何设置数据验证呢？
打开“数据验证”对话框后，数据验证可以这样设置：在“允许”下拉列表中选择“自定义”选项，在“公式”框中输入如下函数公式：
单击“确定”按钮，完成设置。
【函数公式解析】
在例4的公式中，OR函数这里有2个参数，前者允许“××分××秒”，后者允许“××小时××分××秒”，实现了兼容。
在例4的公式中，IFERROR函数可捕获和处理公式中的错误。如果公式的计算结果错误，则返回指定的值；否则返回公式的结果。具体语法为：
value（值）必需。检查是否存在错误的参数。
value_if_error必需。公式的计算结果错误时返回的值。计算的错误类型包括：#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME?或#NULL!。
本例，在找不到“小时”字样出错的情况下，IFERROR函数将错误值转变为“FALSE”，以实现容错的目标。
本小节数据验证的效果如图2-21所示。
图2-21 验证不重复值和定位字符的效果
2.2.2.3 验证文本长度
在上面的例子中，已涉及文本的长度。由于字符有双字符和单字符之分，为深入理解，下面再举两个例子作进一步介绍。为便于介绍，在“非序列”工作表建立一个示例表，如图2-22所示。
图2-22 “非序列”示例表（验证文本长度和数据范围）
例1：A15:A17区域的数据全部为汉字，要求文本长度为2～4个字符，应该如何设置数据验证呢？
打开“数据验证”对话框后，数据验证可以这样设置：在“允许”下拉列表中选择“自定义”选项，在“公式”框中输入如下函数公式：
单击“确定”按钮，完成设置。
【函数公式解析】
在例1的公式中，LEN函数面向使用单字节字符集（SBCS）的语言，而函数LENB面向使用双字节字符集（DBCS）的语言。无论计算机默认语言设置如何，函数LEN始终将每个字符（不管是单字节还是双字节）按1计数。当启用支持DBCS的语言的编辑语言并将其设置为默认语言时，函数LENB才会将每个双字节字符按2计数，否则，函数LENB会将每个字符按1计数。支持DBCS的语言包括日语、中文（简体）、中文（繁体）及朝鲜语。
启用DBCS语言的过程是：在功能区右击鼠标→在快捷菜单中选择“自定义功能区”选项→在打开的“Excel对话框”中，在左侧列表框中选择“语言”选项→在右侧的“编辑语言”列表框中，选择“中文（中国）”选项→单击“设为默认值”按钮→单击“确定”按钮，完成设置。
如果在启用DBCS语言之前使用了LENB函数，LENB函数仍会将双字节字符按1计数。遇到这种情况，要么删除原有的LENB函数公式，重新设置函数公式，要么双击包含有LENB函数的单元格以激活LENB函数。数据验证公式中的LENB函数会随着DBCS语言的启用而生效。如果不能生效，建议对“编辑语言”在中文和英文之间反复设置两次，并重新启动Excel。
大家知道，汉字是双字节字符。如果LEN函数的计算结果的2倍等于LENB函数的计算结果，那么，可以判断数据中的字符全部都是汉字。本例公式正是运用了这一原理作为验证条件之一设置了数据验证公式。
例2：B15:B17区域的数据不能包含汉字，要求文本长度为6个字符，应该如何设置数据验证呢？
打开“数据验证”对话框后，数据验证可以这样设置：在“允许”下拉列表中选择“自定义”选项，在“公式”框中输入如下函数公式：
单击“确定”按钮，完成设置。
2.2.2.4 验证数据范围
数据验证不仅可以验证文本长度，还可以验证数据范围，对文本或数值型数据都可以验证数据范围。下面通过三个例子进行介绍。样表如图2-22所示。
例1：C15:C17区域的数据为号牌，要求数据在aa0000～zz9999，应该如何设置数据验证呢？
打开“数据验证”对话框后，数据验证可以这样设置：在“允许”下拉列表中选择“自定义”选项，在“公式”框中输入如下函数公式：
单击“确定”按钮，完成设置。
【函数公式解析】
在例1的公式中，RIGHT函数根据所指定的字符数返回文本字符串中最后一个或多个字符，与LEFT函数语法相同，截取方向相反。此式中，“--”将RIGHT函数从文本字符串右侧取得的文本强制转换成常规数字，与“*1”的效果相同。
ISNUMBER函数判断是否为数值型数据。具体语法为：
value必需。指的是要测试的值。参数value可以是空白（空单元格）、错误值、逻辑值、文本、数字、引用值，或者引用要测试的以上任意值的名称。
例2：D15:D17区域的数据要求为工作日，即周一至周五，应该如何设置数据验证呢？
打开“数据验证”对话框后，数据验证可以这样设置：在“允许”下拉列表中选择“自定义”选项，在“公式”框中输入如下函数公式：
单击“确定”按钮，完成设置。
【函数公式解析】
在例2的公式中，WEEKDAY函数返回对应于某个日期的一周中的第几天。具体语法为：
serial_number必需。一个序列号，代表尝试查找的那一天的日期。应使用DATE函数输入日期，或者将日期作为其他公式或函数的结果输入。例如，使用函数DATE（2008,5,23）输入2008年5月23日。如果日期以文本形式输入，则会出现问题。
return_type可选。用于确定返回值类型的数字。
默认情况下（省略第2参数），其值为1（周日）到7（周六）。比如，日期2016/3/14、2016/3/15、2016/3/16、2016/3/17、2016/3/18、2016/3/19、2016/3/20，用WEEKDAY函数取得的值分别为2、3、4、5、6、7、1，分别代表周一、周二、周三、周四、周五、周六、周日。WEEKDAY函数值为“1”就是周日，这与西方利用礼拜天做礼拜的习俗有关。西方人把周六视作周末，把周日视作一周的开始。第2参数为“2”时，其值为数字1（周一）到7（周日）。
例3：E15:E17区域的数据为身份证号码，要求不能重复，出生年份在1960—2016年之间，应该如何设置数据验证呢？
打开“数据验证”对话框后，数据验证可以这样设置：在“允许”下拉列表中选择“自定义”选项，在“公式”框中输入如下公式：
单击“确定”按钮，完成设置。
验证文本长度和数据范围的效果如图2-23所示。
图2-23 验证文本长度和数据范围的效果
2.2.2.5 验证数据类型
Excel中字符型数据包括汉字、英文字母、空格等。Excel可以验证这些字符，禁止“非我族类”的数据输入。下面通过5个例子进行介绍。为便于介绍，在“非序列”工作表中建立一个示例表，如图2-24所示。
图2-24 “非序列”示例表（验证数据类型）
例1：A21:A23区域的数据类型要求为文本（数字除外）字符，应该如何设置数据验证呢？
打开“数据验证”对话框后，数据验证可以这样设置：在“允许”下拉列表中选择“自定义”选项，在“公式”框中输入下列函数公式之一：
单击“确定”按钮，完成设置。
【函数公式解析】
在例1的公式中，ISNUMBER函数判断数据是否为数值，ISTEXT判断数据是否为文本。
例2：B21:B23区域的数据类型要求为数字，应该如何设置数据验证呢？
打开“数据验证”对话框后，数据验证可以这样设置：在“允许”下拉列表中选择“自定义”选项，在“公式”框中输入如下函数公式：
单击“确定”按钮，完成设置。
例3：C21:C23区域的数据类型要求为字母和数字，应该如何设置数据验证呢？
打开“数据验证”对话框后，数据验证可以这样设置：在“允许”下拉列表中选择“自定义”选项，在“公式”框中输入如下函数公式：
单击“确定”按钮，完成设置。
例4：D21:D23区域的数据类型要求为限制字母和数字，应该如何设置数据验证呢？
打开“数据验证”对话框后，数据验证可以这样设置：在“允许”下拉列表中选择“自定义”选项，在“公式”框中输入下列函数公式之一：
单击“确定”按钮，完成设置。
注意
例4中的两个公式是等价的，但因为第一个公式使用了除法，所以当D21:D23区域有空白单元格时，LEN（D21）的计算结果为0，即除数为0，整个公式计算就会出错，完成数据验证设置时会弹出出错警告对话框。这时，单击“是”按钮即可，这并不影响数据验证的使用。出错警告对话框如图2-25所示。
图2-25 出错警告对话框
例5：E21:E23区域的数据禁止输有空格，应该如何设置数据验证呢？
打开“数据验证”对话框后，数据验证可以这样设置：在“允许”下拉列表中选择“自定义”选项，在“公式”框中输入下列函数公式之一：
单击“确定”按钮，完成设置。
【函数公式解析】
在例5的第一个公式中，用COUNTIF函数统计空格的数量，要注意两个星号“*”之间的空格，“*”表示任意字符。单元格有空格时统计结果为1，没有空格时统计结果为0。此结果再与“0”比较，进行逻辑判断，没有空格时逻辑判断结果为“TRUE”，有空格时逻辑判断结果则为“FALSE”。第二个公式用FIND函数查找空格的位置，没有查找到空格时结果为错误值，再用ISERROR对错误值进行判断，结果为“TRUE”。
如果希望第一位或最后一位不能输入空格，请分别使用下列公式：
COUNTIF函数的条件部分，要注意星号“*”与空格的位置。
本小节使用数据验证方法控制数据类型的示例，如图2-26所示。
图2-26 验证数据类型的效果
2.2.2.6 选择性判断
一个区域的数据要根据另一个区域的数据来决定是否输入，这就是选择性判断。下面通过两个例子进行介绍。为便于介绍，在“非序列”工作表建立一个示例表，如图2-27所示。
图2-27 “非序列”示例表（选择性判断）
例1：A27:A29区域为单据类型，单据类型包含入库单、出库单。此区域已设置了数据验证，其设置方法将在下一节中介绍。B27:B29区域填写入库数量，C27:C29区域填写出库数量，两类数量需要根据两类单据的类型进行选择性判断，以决定是否允许输入，这应该如何设置数据验证呢？
打开“数据验证”对话框后，B27:B29区域的数据验证可以这样设置：在“允许”下拉列表中选择“自定义”选项，在“公式”框中输入如下函数公式：
单击“确定”按钮，完成设置。
打开“数据验证”对话框后，C27:C29区域的数据验证可以这样设置：在“允许”下拉列表中选择“自定义”选项，在“公式”框中输入如下函数公式：
单击“确定”按钮，完成设置。
例2：D27:D29区域为证件类型，已设置了数据验证，其设置方法将在下一节中介绍。证件类型包含身份证、军官证、学生证，号码长度必须分别为18位、12位、9位。E27:E29区域填写证件号码，证件号码长度根据D27:D29区域的证件类型进行选择性判断，应该如何设置数据验证呢？
打开“数据验证”对话框后，E27:E29区域的数据验证可以这样设置：在“允许”下拉列表中选择“自定义”选项，在“公式”框中输入如下函数公式：
单击“确定”按钮，完成设置。
【函数公式解析】
在例2的公式中，多个IF函数构成嵌套函数，逐层进行判断，而且是从外层到里层逐层判断。首先看D27单元格是不是“身份证”，如果是，则数据长度与“18”作比较；如果不是，则执行下一层判断。其次看是不是“军官证”，如果是，则数据长度与“12”作比较；如果不是，则执行下一层判断。最后看是不是“学生证”，如果是，则数据长度与“9”作比较；如果不是，则值为“FALSE”。
读者需要注意，设置数字的长度来验证数据，只是降低了输入数据的错误率，远远不能保证数据的正确性。可以进一步增加数据验证的条件，以减少错误。还要结合其他方法确保数据的准确无误。
上述用数据验证方法进行选择性判断输入的示例，如图2-28所示。
图2-28 关于选择性判断的数据验证示例
2.2.2.7 身份证号码校验
前面介绍的用身份证号码长度、出生年月、唯一值来验证身份证号码的方法是比较粗浅的，验证通过的身份证号码还不能保证出生年月、地区代码等的正确性，因而不能保证验证通过的身份证号码是真实有效的。身份证号码校验有一套独特科学的方法。前17位数的每一位数都有一个权重，乘积之和除以11，看余数对应的校验码与身份证号码最后一位数是否相等。若相等，则身份证号码为真；否则，为假。
为便于介绍，在文件“第2章 数据验证”中插入一个工作表，更名为“身份证号码校验”。在此工作表，建立一个校验表和辅助表，如图2-29所示。
图2-29 身份证号码校验表和辅助表
表中，要求在B列输入18位身份证号码时，Excel会自动辨别真伪，将“不明身份”的人拒之门外，这应该如何设置数据验证呢？
打开“数据验证”对话框后，B3:B19区域的数据验证可以这样设置：在“允许”下拉列表中选择“自定义”选项，在“公式”框中输入如下函数公式：
输入公式后，要在“数据验证”对话框“出错警告”选项卡中勾选“输入无效数据时显示出错警告”复选框。
单击“确定”按钮，完成设置。
B3:B19区域设置数据验证后，在B4单元格中输入“510231200512131415”，敲击Enter键后，弹出如图2-30所示的警告框，表明这个身份证号码是错的。
图2-30 关于身份证号码校验的数据验证示例
【函数公式解析】
本例公式是一个数组公式，公式外层的花括号不是手动输入的，而是通过按“Ctrl+Shift+Enter”组合键自动产生的，以通知Excel执行数组运算，并锁定数组公式不被误改。
这里，从里到外对这个函数公式进行剖析。ROW函数获取行序为“{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17}”。MID函数从B3单元格的身份证号码依次取出“1”位数“{"4";"2";"0";"1";"8";"1";"1";"9";"9";"5";"1";"0";"0";"4";"0";"4";"3"}”。VALUE函数将这些文本数字转化为数值型数字“{4;2;0;1;8;1;1;9;9;5;1;0;0;4;0;4;3}”。SUMPRODUCT函数将这些数字与D3:D19区域的权重相乘并得到和值为“248”。MOD将此和值除以11得到余数“6”。VLOOKUP则从E3:F13查找此数对应的校验码，为“6”。再与身份证号码最后一位数“6”比较。比较结果为TRUE，则判断此身份证号码为真。
式中，VLOOKUP函数是一个纵向查找函数，在工作中有广泛的应用。具体语法为：
lookup_value必需。为需要在数据表第一列中进行查找的数值。lookup_value可以为数值、引用或文本字符串。当VLOOKUP函数第一参数省略查找值时，表示用0查找。
table_array必需。为需要在其中查找数据的数据表。使用对区域或区域名称的引用。col_index_num为table_array中查找数据的数据列序号。
col_index_num必需。为1时，返回table_array第一列的数值，col_index_num为2时，返回table_array第二列的数值，以此类推。如果col_index_num小于1，函数VLOOKUP返回错误值#VALUE!；如果col_index_num大于table_array的列数，函数VLOOKUP返回错误值#REF!。
range_lookup可选。为一逻辑值，指明函数VLOOKUP查找时是精确匹配，还是近似匹配。如果为FALSE或0，则返回精确匹配，如果找不到，则返回错误值#N/A。如果range_lookup为TRUE或1，函数VLOOKUP将查找近似匹配值，也就是说，如果找不到精确匹配值，则返回小于lookup_value的最大数值。如果range_lookup省略，则默认为近似匹配。
本例设置数据验证所使用的“自定义”公式还可以为下列公式之一：
这两个公式看起来挺吓人的，但只要弄清了函数的嵌套关系，就容易理解了。
第一个备用公式为普通公式。用MID函数截取出B3单元格身份证的每一位数为“4+2+0+1+8+1+1+9+9+5+1+0+0+4+0+4+3”，用SUM函数求和为“248”，用MOD函数取得该数除以“11”的余数为“6”，用CHOOSE函数查找“6”在校验码数组中的对应位置的数为“6”。“6”与RIGHT函数所截取到B3单元格的末位数“6”相等，即TRUE。
式中，CHOOSE函数返回数值参数列表中的数值。具体语法为：
index_num必需。用于指定所选定的数值参数。index_num必须是介于1到254之间的数字，或是包含1到254之间的数字的公式或单元格引用。
如果index_num为1，则CHOOSE返回value1；如果为2，则CHOOSE返回value2，以此类推。
如果index_num小于1或大于列表中最后一个值的索引号，则CHOOSE返回#VALUE!错误值。
如果index_num为小数，则在使用前将被截尾取整。
value1是必需的，value2及后续值是可选的。1到254个数值参数，CHOOSE将根据index_num从中选择一个数值或一项要执行的操作。参数可以是数字、单元格引用、定义的名称、公式、函数或文本。
第二个备用公式为数组公式。COLUMN函数获取A～Q列的列号为“{1,2,3,4,5,6,7,8, 9,10,11,12,13,14,15,16,17}”。MID函数截取出B3单元格身份证的每一位“{"4","2","0","1","8","1","1","9","9","5","1","0","0","4","0","4","3"}"，“0"与之并连成“{"04","02","00","01", "08","01","01","09","09","05","01","00","00","04","00","04","03"}”，再与身份证的每一位的权重相乘，结果为“{28,18,0,5,64,4,2,9,54,15,7,0,0,20,0,16,6}”，SUMPRODUCT得到此乘积之和为“248”，再用MOD函数取得该数除以“11”的余数为“6”，LOOKUP函数再查找“6”在“余数”列中的位置所对应的“校验码”为“6”。“6”与RIGHT函数所截取到B3单元格的末位数“6”相等，即为TRUE。
式中，COLUMN函数返回指定单元格引用的列号。具体语法为：
reference（引用）可选。要返回其列号的单元格或单元格范围。如果省略参数reference或该参数为一个单元格区域，并且COLUMN函数是以水平数组公式的形式输入的，则COLUMN函数将以水平数组的形式返回参数reference的列号，需要按“Ctrl+Shift+Enter”组合键形成数组。如果参数reference为一个单元格区域，并且COLUMN函数不是以水平数组公式的形式输入的，则COLUMN函数将返回最左侧列的列号。如果省略参数reference，则假定该参数为对COLUMN函数所在单元格的引用。参数reference不能引用多个区域。
式中，SUMPRODUCT函数在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。具体语法为：
array1必需。其相应元素需要进行相乘并求和的第一个数组参数。
array2,array3,…可选。2到255个数组参数，其相应元素需要进行相乘并求和。
式中，LOOKUP函数是一个高效率的查找函数。其向量形式是在单行区域或单列区域（称为“向量”）中查找值，然后返回第二个单行区域或单列区域中相同位置的值。具体语法为：
lookup_value必需。LOOKUP在第一个向量中搜索的值。lookup_value可以是数字、文本、逻辑值、名称或对值的引用。
lookup_vector必需。只包含一行或一列的区域。lookup_vector中的值可以是文本、数字或逻辑值。lookup_vector中的值必须按升序排列：…,-2,-1,0,1,2,…, A-Z,FALSE,TRUE；否则，LOOKUP可能无法返回正确的值。文本不区分大小写。
result_vector可选。只包含一行或一列的区域。result_vector参数必须与lookup_vector参数大小相同。