[使用Excel处理中的“号码”的研究] excel输入的号码

摘要根据Excel数据信息，本文针对输入后ID号码不能正常显示，不可避免地输入错误以及通过ID号码减少数据输入量的问题，介绍了相应的解决方案. 关键词数据信息号码数据有效性设置研究

CLC编号: G71文件标识代码: A商品编号: 1002-7661（2012）07-0006-02

目前，ID卡号在数据信息中随处可见，并且存在处理不当的许多问题. 例如，小张单位负责收集数据，每年9月至少招收两三千名新生，并将其上传到网络系统；每个班主任在输入时都会不可避免地犯错误. 如果号如果输入错误，则不能正常上传. 您必须返回并请班主任再次检查，然后将其退回并再次上传. 如此反复往复，费时费力，主要影响工作的进展，无助于领导. 那么有什么办法可以避免这种错误并提高工作效率呢？您可能需要让每位班级老师仔细输入数据并输入，可以，但是如果您不注意该错误，则会出现该错误. 不要说输入了班上数十个人的信息. 有时我们会写自己的手机号码. 否则ID号可能是错误的. 但是，如果您可以在Excel表中输入ID号时添加其他提醒功能，那么我相信出错的机会会更少，同时，您可以找到错误并及时纠正. 只需在Excel表中添加一些功能，您就可以轻松确定输入的ID号是否存在输入错误（例如: 输入的ID号的数目或多或少）或是否合法，然后检查ID号有效性和合法性.

首先，使用Excel进行设置以验证号码的有效性和合法性

使用Excel公式检查ID号输入的准确性，以确保ID号输入的准确性；您可以检查15位ID号是否为15位和18位ID号（目前绝对是第二代ID卡，即18位数字），最后一位是验证码，它是根据规则根据ID号的前17位数字（此处未说明）计算得出的，其值是0〜9或X. 通常，只要有一位数字输入错误，它将根据规则计算后与第18位数字不匹配. 当然，也不排除基于错误编号的计算与验证码一致，但是这种情况的可能性很小. 因此，使用下面介绍的设置功能，除了可以检测输入18位ID号时输入的正确或错误的数字位数，还可以验证是否合法并在输入时及时提醒参赛者，从而大大提高了ID数字输入. 准确性.

（1）使用Excel数据有效性设置自动弹出提示框提醒

在输入ID号之前，首先将要输入ID号的列设置为文本格式，否则在Excel单元格中输入的位数将超过11位，这将成为科学的计数形式，例如输入“ 123456789012345678”（不要在外面输入双引号），则18位将显示为“ 1.23457E + 17”，双击以查看数字变为“ 123456789012345000”，而三位都变为“ 000”是错误的，如图1所示.

图1

1. 将ID编号所在的列的单元格格式设置为“文本”格式

选择ID编号所在的“列标题”列（本文的C列）. 如图2所示，然后单击“鼠标右键”→选择“设置单元格格式”选项→“单元格格式”窗口→选择“数字”选项卡→在“类别”列中→选择“文本”选项→然后单击“确定”按钮. 这样，该列的所有单元格格式都设置为文本格式，然后输入ID号，就不会出现错误（参见图1）.

图2

2. 设置要输入ID编号的列（C列）的有效性

设置要在其中输入ID编号的列（C列）的有效性，以便在ID编号不正确时可以迅速提示.

步骤1: 选择“列C”列标题，菜单: 数据→有效性. （图3）

图3

步骤2: 打开“数据有效性窗口”时，选择“设置”选项卡→允许→选择“自定义”. 在公式的引用位置输入以下公式（或复制公式）: （图4）

= OR（LEN（C1）= 15，IF（LEN（C1）= 18，MID（“ 10X98765432”，MOD（SUM（MID（C1，ROW（INDIRECT（“ 1:17”））），1） * 2 ^（18-ROW（INDIRECT（“ 1:17”）））），11​​）+1，1）= RIGHT（C1）））

图4

步骤3: 输入公式后，选择“错误警告”选项卡→样式→选择“信息”；在“标题”下的内容中，输入提醒文本，例如“注释”；在“错误信息”下的内容: 输入“请再次确认，您刚才输入的号码是否正确！”和其他类型的文本，最后单击“确定”（图5），数据有效性设置完成. 此时，如果输入的号有误，将立即弹出提示对话框，提醒输入人员再次检查，避免出现号错误，未知的情况.

图5

（2）在ID编号所在的列旁边，添加一列用于设置文字提醒

如果在D列中进行了设置，请在单元格D1的标题中输入“号码是对还是错”，然后在单元格D2中输入以下公式:

= IF（IF（LEN（C2）= 18，MID（“ 10X98765432”，MOD（SUMPRODUCT（MID（C2，ROW（INDIRECT（“ 1:17”）），1）* 2 ^（18-ROW （INDIRECT（“ 1:17”））））），11​​）+1、1）= RIGHT（C2），IF（LEN（C2）= 15，ISNUMBER（-TEXT（19＆MID（C2excel号码性别，7，6）， “#-00-00”）））），“正确”，“错误”）

输入后，按“ Enter（Enter）”，然后向下填写公式. 这样，如果正确输入了ID号，则右侧相应的D列将显示“正确”，如果不正确，则将显示“错误”.

为了使“错误”更加明显，您还可以在单​​元格D2中输入公式后，在单元格D2中设置“条件格式”，然后向下填充公式. 通过“条件格式”单元格，当显示“错误”时，字体变为红色，并添加阴影颜色以更好地区分.

设置方法（步骤）: 在单元格D2中输入上述公式后，选择单元格“ D2”→选择菜单“格式”→选择“条件格式”选项→“条件1”选择“单元格值”→选择“等于”→输入“ =错误”→选择“格式”按钮（在其中，选择“字体”选项卡→颜色选择“红色”→然后选择“图案”选项卡→单元格底纹→选择一种底纹颜色“灰色”→单击“确定”按钮退出）→单击“条件格式”窗口上的“确定”按钮以完成设置. （图6）

图6

如果再次使用单元格D2，则即使使用“条件格式”，向下填充公式也会将其向下填充. 如果输入的ID号错误，则D列中的相应单元格将以红色字体显示单词“错误”，并且单元格的效果带有“灰色”底纹，这一点更加明显. （图7）

图7

但是，输入所有ID号后，如果不再需要D列，则可以将其隐藏或直接删除.

通过设置以上两种方法，您可以立即提醒输入人员在输入错误的ID号时重新检查ID号，从而避免了输入错误的ID号和未知的情况. 这两种方法可以单独使用或一起使用. 至于使用哪种方法，您可以根据自己的需求和习惯来决定.

第二，使用ID号提取相应的内容以减少输入的信息量

正确和合法的号码是每个公民的唯一标识码，其中包含公民的出生日期，性别以及号码最初被编译的区域（当前通常是出生地）或地方. 起源. 面积等信息. 除了姓名和号码，输入信息时，我们通常还会提供出生日期（或年龄），性别和其他相关信息. 如果输入的数据量很大（人数众多），则输入这些信息不仅费时费力，而且错误不可避免. 但是，如果仅输入名称并确保通过上述方法正确输入了ID号，然后使用Excel函数通过其ID号提取出生日期（或年龄）和性别，则可以大大减少输入的信息量和出生日期和日期输入不正确.

中国居民号由一组功能组合代码组成. 最初的ID卡号为15位，现在大多数已升级为第二位ID卡号为18位. 编码规则为:

15位ID号: 前6位数字是位置代码，第7至12位数字是出生日期代码，第13至15位数字是顺序代码. 18位ID号: 前6位数字是位置代码，第7位至第14位数字是出生日期代码，第15位至第17位数字是顺序代码，第18位数字是检验代码.

其中的三位数序列号是为在同一原籍地（代码）在同一年excel号码性别，月，日出生的人编制的序列号（例如，双胞胎）和三位数顺序码的最后一位数字（女性为15位，倒数第二位为18位）对于男性而言偶数为偶数. 在本文中，“ ID号”列为C列，是从单元格C2输入的. 下式中提到的C2就是这个意思.

1. 从ID号中提取出生日期或年龄

在本文中，提取“出生日期”并将其放在E列中，从单元格E2开始，然后必须首先将E2单元格设置为所需的“日期”格式（按照图2中类似的设置方法进行操作2），然后在E2单元格中输入以下公式，并向下填写公式以获取相应的出生日期. （图8）

图8

= IF（C2“”，TEXT（（LEN（C2）= 15）* 19＆MID（C2，7，6+（LEN（C2）= 18）* 2），“#-00-00”）+ 0，）

简化公式: =-TEXT（（LEN（C2）= 15）* 19＆MID（C2，7，6+（LEN（C2）= 18）* 2），“#-00-00”）

如果使用提取的出生日期格式（例如: 19950809）公式:

= IF（LEN（C2）= 15,19＆MID（C2,7,6），“”）＆IF（LEN（C2）= 18，MID（C2,7,8），“”）

简化公式为: = IF（LEN（C2）= 18，MID（C2，7，8），19＆MID（C2，7，6））

2. 从ID号中提取性别

在本文中，提取“性别”并将其放在F列中，从单元格F2开始，然后必须首先将单元格F2设置为所需的“常规”格式（遵循图2中的类似设置方法），然后放入将公式输入到单元格F2中，然后向下填充公式以获取相应的性别. （图9）

图9

= IF（C2“”，IF（MOD（MID（C2，IF（LEN（C2）= 15，15，17），1），2）= 1，“男性”，“女性”），）

简化公式为: = IF（MOD（MID（C2，IF（LEN（C2）= 15，15，17），1），2）= 1，“ Male”，“ Female”）

3. 从ID号中提取年龄

公式:

= DATEDIF（TEXT（（LEN（C2）= 15）* 19＆MID（C2,7,6 +（LEN（C2）= 18）* 2），“#-00-00”），TODAY（）， “ y”）

类似地，无论是15位还是18位ID号，前6位都是原产地，如果有代码和省区比较表，也可以使用Excel函数公式提取其对应的原点区域.

简而言之，通过上面介绍的方法进行设置后，不仅可以减少输入中ID号的错误率，而且可以大大减少信息输入量. 使用Excel本身的功能不仅可以解决许多问题，而且可以大大提高工作效率.

本文来自电脑杂谈，转载请注明本文网址：
http://www.pc-fly.com/a/jisuanjixue/article-260906-1.html