标签：输入

下图中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群下载文件和视频教材，从而方便练习，同时验证本案例中的技巧与操作结果。

在单元格中输入身份证号码时，单元格会将身份证号码显示为科学记数法，如6.543E+17，同时还会丢失后面几位数值。例如，在单元格中输入 654300199103073299 后自动变成654300199103073000，效果如图3.59所示：

图3.59 消失后几位数字的身份证号码

要如何才能让身份证号码显示完整呢？

解题步骤

Excel会在单元格中的数值长度大于等于12位时显示为科学记数法，从而减少数值的长度。身份证号码包含15位和18位两种形式，不管哪种形式都会显示为科学记数法。

Excel的数值计算精度是15位，显示精度也是15位，当数值超过15位时，Excel不能正确显示15位以后的值，而是以0占位，因此在图3.59中输入654300199103073299后会自动变成654300199103073000，右边3位数不管原来是什么都会变成0。

解决以上两个问题可采用同一个方法——输入身份证号码之前先录入半角状态的单引号“＇”。当输入“＇654300199103073299”后单元格会显示为图3.60所示的效果。

图3.60 完整显示的身份证号码

知识扩展

1.身份证号码是不需要参与数值运算的，因此以文本形式输入单元格中并不会带来负面影响，但可以解决本疑难中的两个问题。

2.将身份证号码以文本形式输入单元格后，单元格的左上角会显示一个绿色的倒三角符号，如果想关闭该符号，可以选中单元格后单击单元格左方的箭头，然后从弹出的下拉菜单中选择“忽略错误”。

3.对于身份证号码丢失最后3位数值的问题，只能前期预防，无法后期补救，对于已经输入单元格且已经丢失3位数值的身份证号码，没有任何办法找回丢失的数值，只能重新输入。

4.半角状态和全角状态的单引号有着本质区别，并非仅仅粗细不同。半角符号可将数值转换成文本，但是符号本身不会显示出来；全角撇号也可以将数值转换成文本，但是符号本身会显示在单元格中，而且会影响此单元格的数据参与运算。图3.61中是两种形式的撇号对比。

图3.61 半角撇号与全角撇号的区别

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

下图中的生产表中需要输入大量的姓名，而且每天都输入相同的一批姓名。但是经常会在输入姓名时产生手误，如将五笔编码FNU误按成FNY，“张志全”录成“张专全”，从而导致资料出错。有什么办法既能让输入速度加快又不出错，确保每天输入同一个人的姓名时都保持一致呢？

解题步骤

创建一个辅助工作表，然后将所有需要用到的姓名存放在辅助表中，最后对需要输入姓名的区域设置数据验证，通过数据验证调用辅助表中的所有姓名，显示在下拉列表中供用户使用，从而用选输录入的方式替代手工打字。具体步骤如下。

1.单击工作表标签右方的“新工作表”按钮（图标为），然后将新建的工作表移到最左端并命名为“职工表”。

2.将所有需要用到的姓名输入到职工表的A列中，效果如图3.39.所示。

图3.39 创建辅助表

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

4.从“允许”下拉列表中将默认值“任何值”修改为“序列”。

5.将“来源”设置为“=职工表！$A$1：$A$20”，操作界面如图3.40所示。

图3.40 设置数据验证的条件与数据来源

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

7.单击A2：A20区域中的任意单元格，Excel会弹出如图3.41所示的列表供用户选择。

图3.41 从下拉列表中选择目标数据

知识扩展

1.从下拉列表中选择目标数据的方式输入字符串有可能比手工打字的速度更快，也有可能更慢，视打字速度而定。但是从准确性上讲，通过选择输入数据的方式一定占据上风。同一个姓名，一年输入300次很难确保300次都输入正确，但是通过选择的方式输入则会准确得多。

2.数据验证的数据源不能有重复值，否则下拉列表会变得很长，列表越长从列表中查找目标就越费时。

3.本例的数据源可以放在“1号生产表”中，但由于后面还有“2号生产表”、“3号生产表”都需要调用，因此统一放在辅助工作表中更利于维护、更新。

4.为了提升搜索速度，应该将辅助工作表中的姓名按拼音顺序排序。例如，“鲍文秀”应排在前面，而“赵文祥”则排在后面。在打开下拉列表之前就能够预知目标的大概位置，找到目标的速度就会快得多。

5.数据验证产生的下拉列表中的文字不允许修改字体大小。

6.设置了数据验证后，可以选择式输入也允许手工输入，但是输入错别字时将被 Excel 阻止，提示“输入值非法”。数据验证的存在价值正在于此。

7.下拉列表框的默认项目数量是8个，要查看其他项目只能拉滚动条查看。

8.选中单元格后可以按组合键＜Alt+下箭头键＞打开下拉列表，然后通过下箭头键选择目标。

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