|
通过这篇文章来简单的总结一下与之相关的一些函数公式用法。 目前身份证的类型简单来讲有一代和二代之分，一代是15位号码，二代是18位号码，与一代的不同之处在于补足了四位年份日期并且在末尾添加了一个校验字符。二代身份证自2005年开始换发，一代可以同时通用。但根据规定，在2013年1月1日起将全面停止使用一代身份证。 身份证的号段含义如下： 15位身份证：前2位表示所属省份代码，3~6位表示所属城市和区县代码，7～12表示出生日期，格式为YYMMDD，13～15位是个人顺序码，其中第15位可以标识性别，为奇数表示男性，为偶数表示女性。 18位身份证：前2位表示所属省份代码，3~6位表示所属城市和区县代码，7～14表示出生日期，格式为YYYYMMDD，15～17位是个人顺序码，其中第17位可以标识性别，为奇数表示男性，为偶数表示女性。第18位是校验位，由前17位通过计算求得。
【正确输入】 在Excel当中使用身份证号码时，首先需要注意的就是正确输入方法。由于Excel单元格只支持15位有效数字，当输入的整数数值超过15位时后面输入的内容在回车确认以后都会自动转化成0，这样会造成18位身份证号码的输入错误。这是一个经常容易踏入的错误陷阱，如果不注意，会导致大量的错误数据产生。 要避免这样的错误，可以使用文本方式来输入号码，因为对于文本类型的数据不存在上述15位的限制问题。可以在输入号码之前，先添加一个半角的单引号再输入其他数字，这样完成输入的结果就是一个文本型数据。或者也可以在输入之前事先将单元格格式设置为【文本】再行输入。但是如果在输入完成以后再更改单元格格式就不会有效果。
【自动验证输入】 如果你的表格是要下发给其他人由他们来填写信息，为了限定单元格内必须输入身份证并且验证其输入的正确性，可以通过【数据有效性】功能来加以设定。 要限定单元格中输入的内容只能是身份证号码，可以根据下面这几个条件来判断： 条件1：输入长度为15位或18位 由此可以得到公式：
条件2：前17位必须都是数字
其中对于15位的号码来说，LEFT(A1,17)仍然可以准确的获取其15位字符，不会有额外的占位。 这个公式从严格来讲并未完全满足条件，对于包含小数等数学形式它也会包容。如果需要更严谨的话可以使用下面这个公式来判断：
条件3：如果不全都是数字，那么它只能是18位，并且末尾字符是字母“X”
上述三个条件全部满足（条件1 And 条件2 And 条件3）的情况下，可以保证A1当中输入的是身份证号码类型的数据（在这里没有验证它的具体内容是否正确，也没有验证它是否是文本型输入，如有必要，可以在条件中继续增加），得到下面的数据有效性判断公式：
假定要在A1单元格内进行设定，可以选中A1单元格，然后在菜单栏上点击【数据】——【有效性】（2007以上版本是在【数据】选项卡中点击【数据有效性】），打开【数据有效性】对话框，在【设置】选项卡的【允许】下拉框中选择【自定义】，然后在下方【公式】文本框中输入上面的公式。
【提取生日】 可以通过公式将15位号码中第7位开始的6位出生日期数字转换成真实日期值（可以在Excel当中进行日期运算的数值），由于这个6位的格式是YYMMDD，因此在公式处理中要在前面添加“19”补全四位年份代码，否则当YY小于30的时候（1930年之前出生），Excel自动识别的功能会将其转换为20YY年。 对于18位身份证，需要提取的是其中第7位开始的8位数字，这8位数字的格式是YYYYMMDD，可以很方便转化成真实日期数据。 这个公式可以分以下几个步骤来设计： 步骤1：提取6位或8位生日数字
如果愿意，还可以简化成这样：
步骤2：对于15位号码，需要补足前面两位“19”数字
步骤3：将上面得到的8位数字转换成真实日期数值
步骤3的公式就是最终可以从A1单元格中的身份证号码取得其出生日期的公式。如果通过这个公式得到的结果是一个5位数的数值，不要惊讶，这就是Excel当中日期值的本来面目，把单元格格式设置为【日期】就可以看到实际的日期显示。
|
