下图中B列只能输入身份证号码，禁止输入手机号码等其他数据，也禁止输入不符合规则的身份证号码。如何才能实现？

解题步骤

身份证号码都有固定的规则，不按规则输入的都禁止输入即可。使用公式计算B列的值是否符合规则，然后在数据验证中加以限制，具体操作步骤如下。

1.在I2输入以下公式：

2.将公式向下填充到I11，然后隐藏I列，避免意外删除公式。

3.选择B2：C11区域，然后单击功能区的“数据”→“数据验证”，弹出“数据验证”对话框。

4.在“数据验证”对话框中将“允许”下拉列表的默认值“任何值”修改为“自定义”，然后在公式框中输入公式“=i2”，操作界面如图3.74所示。

5.打开“出错警告”选项卡，并按图3.75的方式指定警告信息。

图3.74 设置数据验证的条件与公式

图3.75 设置出错警告

6.单击“确定”按钮保存设置，同时返回工作表界面。

7.在B2单元格输入身份证号码“＇330401197502177647”，当单击Enter键后Excel不会产生任何提示，表示输入的号码符合规则。

此处要注意身份证号码前面必须有半角状态下的撇号，否则身份证号码的最后3位数字有可能丢失。

之所以说有可能丢失而不是一定会丢失，是因为部分特殊身份证号码以X结尾，这种号码本身属于文本，和超长数值的性质不同。

8.在B3单元格输入身份证号码“＇750423199407146087”，当单击Enter键后Excel会弹出如图3.76所示的警告信息，因为身份证号码没有以75开头的，只有以15、35、45和65开头的。

图3.76 首位数字录入有误产生的警告

9.单击“重试”按钮，然后重新输入身份证号码“＇4504231994071460871”，当单击Enter键后Excel会弹出如图3.77所示的警告信息，因为此身份证号码尽管前12位没有问题，但是其长度为19位，超过了标准长度。

图3.77 字符长度超过18位产生的警告

10.单击“重试”按钮，然后重新输入身份证号码“＇4504231994077460871”，当单击Enter键后Excel会弹出如图3.78所示的警告信息，代表日期的8位数字“19940774”格式有误，一个月的天数上限是31，而此处输入的值是74。

图3.78 日期格式错误产生的警告

知识扩展

1.我国的身份证号码在10多年以前采用的15位数字，现在改成了18位，其中前6位用于说明身份证持有人所在地区的信息，第7位到第14位表示身份证号码持有人的出生年月日，第15位到第17位表示顺序码，即同一地区同一日期有多人出生时，使用顺序码来区分，第18位是校验码，校验前面的数字否是正确，有计算规律。

2.我国身份证号码的前两位按表3.1所示的方式编码。

从表3.1中可以看出四川省的身份证号码是以51开头的，吉林省的身份证号码是以22开头的，湖北的身份证号码则是以42开头。如果某身份证号码以7开头则说明该号码一定有误。

3.身份证号码的第7位到第14位用于说明持有人的出生日期，因此不可能小于“19010101”也不可能大于今天的日期。

4.本例的数据验证公式使用了AND函数限制4个条件必须同时满足，否则就禁止输入。

=AND(LEN(B2)=18,OR(LEFT(B2,2)={＂11＂,＂12＂,＂13＂,＂14＂,＂15＂,＂21＂,＂22＂,＂23＂,＂31＂,＂32＂,＂33＂,＂34＂,＂35＂,＂36＂,＂37＂,＂41＂,＂42＂,＂43＂,＂44＂,＂45＂,＂46＂,＂50＂,＂51＂,＂52＂,＂53＂,＂54＂,＂61＂,＂62＂,＂63＂,＂64＂,＂65＂,＂81＂,＂82＂}),AND(MID(B2,7,8)＞＂19010101＂,MID(B2,7,8)＜=TEXT(NOW(),＂yyyymmdd＂)),ISNUMBER(TEXT(MID(B2,7,8),＂0000-00-00＂)*1))

公式中的“LEN（B2）=18”表示身份证号码只允许长度为18位，否则不符合规则。公式中的“OR（LEFT（B2，2）={＂11＂，＂12＂，＂13＂，＂14＂，＂15＂，＂21＂，＂22＂，＂23＂，＂31＂，＂32＂，＂33＂，＂34＂，＂35＂，＂36＂，＂37＂，＂41＂，＂42＂，＂43＂，＂44＂，＂45＂，＂46＂，＂50＂，＂51＂，＂52＂，＂53＂，＂54＂，＂61＂，＂62＂，＂63＂，＂64＂，＂65＂，＂81＂，＂82＂}）”表示身份证号码的前两位必须是 11、12、13、14、15、21、22、23、31、32、33、34、35、36、37、41、42、43、44、45、46、50、51、52、53、54、61、62、63、64、65、81、82 中的一个，只要满足条件之一即可，这是OR函数的功能。

公式中的“AND（MID（B2，7，8）＞＂19010101＂，MID（B2，7，8）＜=TEXT（NOW（），＂yyyymmdd＂）））”表示从身份证号码的第7位开始、提取出来的8位数必须大于“19010101”，同时小于等于今天的日期，两个条件必须同时满足，这是AND函数的功能。其中NOW函数用于产生当前时间值，而TEXT函数则负责将时间值转换成与身份证号码中代表日期的数字同样的格式，便于比较。

公式中的“ISNUMBER（TEXT（MID（B2，7，8），＂0000-00-00＂）*1）”表示从身份证号码的第7位开始、提取出来的8位数必须是日期。判断一个8位数是否为日期的方法是：使用Text函数转换成“0000-00-00”格式，然后将它乘以 1，如果返回值是数值，表示它是日期，如果返回值是错误值，则表示不是日期。例如，公式“=ISNUMBER（TEXT（19870218，＂0000-00-00＂）*1）”的运算结果为True，而公式“=ISNUMBER（TEXT（19870248，＂0000-00-00＂）*1）”的运算结果为False，因为1987年2月只有28天，不存在48天，因此此数字不能代表日期。

当输入的身份证号码同时满足以上4个条件时，Excel将不会禁止用户输入当前号码。

5.数据验证的公式有所限制，禁止在公式中使用常量数组，因此本例没有在数据验证对话框中直接输入公式，而是将公式写在辅助区域中，然后在数据验证对话框中检查辅助区域是否等于True即可。

6.输入汉字、英文单词、标点符号、长度不等于18位等情况都会被阻止，但是粘贴单元格则不会被阻止，因此为了提升输入数据的准确性，使用了数据验证的区域应采用手工输入方式。

请在封面中缝指定的QQ群下载文件和视频教材，从而方便练习，同时验证本案例中的技巧与操作结果。