返回目录：excel表格制作
从事人事管理工作的人，对身份证信息的录入一点也不陌生，这是所有人事管理工作的起步开始阶段，可用微软Excel录入身份证信息，还是会经常出错，主要是15位以上的数字如何以文本形式存储，如何减少重复输入，以及如何减少输入出错的概率等问题，别看一个个问题不大，但加起来还是对大家产生了不小的困扰。
今天，我就来说说如何把握好输入的源头，提高输入的质量，减少错误率，以便后续的数据分析和人员管理工作。
要想提高输入质量，首先就要确保输入的每一个身份证号码都是唯一的，在确保唯一的基础上，还要确保输入的每一个身份证都是18位，不能低于18位（包括空格）。因为身份证的号码每个人都不同，而且二代身份证都是18位数字组成，具有唯一性，通过对身份证输入时的把握可以很好地减少重复输入的出现，避免垃圾信息。
有了思路，我们就可以到微软Excel中找到解决问题的办法。
一、用COUNTIF（range，criteria）函数来解决对输入的内容是否重复的判断。
参数:range 要计算其中非空单元格数目的区域
参数:criteria 以数字、表达式或文本形式定义的条件
COUNTIF 的最简形式为：=COUNTIF(要检查哪些区域?，要查找哪些内容?)
图一
例如我们的身份证输入都在B列，则要检查的区域就是整个B列（表示为B:B），而当前要输入单元格为B6（见图一），即要查找的内容就是B6单元格中输入的内容，则只要将B6单元格中的内容与B列已经输入的内容进行比较，相同就计数一次，我们只要设定总计数等于1，也就是只能有一个记录不能有第二个，这样就保证了录入的唯一性。
具体公式为：=COUNTIF（B:B，B6）=1
二、用LEN（Text）函数来控制输入的内容长度。
LEN（Text）就是返回文本字符串Text中的字符个数，设定B6单元格输入的字符串个数等于18，即可保证输入的数字不超过18位。
具体公式为：=LEN（B6）=18
三、用微软Excel的数据验证工具，实现我们的要求。
将以上两个公式合并成一个公式，用函数AND(logical1,logical2, ...)进行连接，表示要同时满足这两个条件。
具体公式为：=AND（COUNTIF（B:B，B6）=1，LEN（B6）=18）
图二
此时，用ALT+D+L快捷方式调出数据验证对话框，将设置中的验证条件-允许栏中的下拉菜单选择为自定义，以显示出公式输入栏（如图二）方便输入公式，同时将忽略空值的选择取消，取消后单元格不输入数据也不行，也就是单元格激活后要么输入18位数字，要么就不要激活，激活后什么不输入也不行！
再将合并后的公式填入其中，点确定即可。
这样我们就能对B6单元格所录内容进行唯一性和位数的控制了。
四、对以上不足进行改进，最终实现我们所想要的结果。
可当我们在B6单元格输入“310108195512081358”时，却发现系统提示输入非法值。如图三
图三
改了几次数字再输依然如此，明明B列中并没有重复的内容，为什么还报错？这是什么原因呢？
研究后发现，这个问题与身份证这种长度超过15位的数字文本有关。微软Excel在记录超过15位的数字时会自动变为科学计数法如 “ 3.10108E+17”，而编辑栏中的数字却是这样“310108195512081000”，也就是微软Excel只认前15位数，后三位数均视为0，如果输入的数字只改变后3位，在微软Excel看来就是没有变化，一律视为相同，所以报警；如果输入的数字在前15位内就有变化，就被认为两个数字不相同，也就不会报警！
看来必须克服科学计数法的问题才行，不过这是微软Excel默认的数字记录方法，只有告诉微软Excel你输入的数字是文本才行，才不会被当着数字用科学计数法记录。
只有对公式：=COUNTIF（B:B，B6）=1中的B6强行进行定义才可。
修改后的公式为：= COUNTIF（B:B，B6&“*”）=1，增加&“*”就是要将B6单元格中的数字强行转为文本。
公式最终改为：=AND（COUNTIF（B:B，B6&“*”）=1，LEN（B6）=18）
图四
再将公式重新输入刚才数据验证对话框中，点确认（如图四），这次会不会再出问题呢？
试了几次，可问题依旧，难道是公式不对？不会啊！公式肯定不应该再有问题！
最后发现，问题还是出在科学计数法上，我们只改了公式，公式只是控制要输入的数字，而表中单元格的现有格式依然是常规，只有改为文本格式才行（或者每次输入身份证数字前加一个单引号），这样与公式统一后，问题就解决啦！
至此，只需将B6单元格数字验证格式复制到B列所有单元格即可，完美实现我们的要求。
此方法也可运用于除人事管理工作以外，其他需要对录入数据有位数或唯一性方面要求的地方，如产品编码、护照号、税务号、学号等等，有了录入的准确性，之后的数据分析也会得心应手！
写文不易，欢迎点赞、留言探讨！