检查ID号的正确性

检查ID号的正确性= IF（LEN（A2）= 18，MID（“ 10X98765432”，MOD（SUMPRODUCT（MID（A2，ROW（INDIRECT（“ 1: 1 7”））），1）* 2 ^（18-ROW（INDIRECT（“ 1:17”））））），11​​）+1,1）= RIGHT（A2），IF（LEN（A2）= 15，ISNUMBER（-TEXT（19＆MID（A2， 7,6），“#-00-00”）））））如何提取ID卡的出生日期，性别，并在EXCEL中检查ID号的正确性. 中国居民号是一组功能组合代码号码检测，原本为15位，现在已升级为18位，其编码规则为: 15位: 永久居民户口所在县市的6位行政区划代码6位数字的出生日期代码，3位数字的顺序代码. 18位数字: 永久性户口所在地所在县或市的6位行政区划代码，8位出生日期代码，3位单词序列代码和1位校验码. 其中，三位数序号是针对同一年，同一天，同一地址出生的人编制的序号. 偶数是女性，奇数是男性. 1.提取原产地的行政区划代码（A2是ID号，下同）15位和18位数字是通用的: = LEFT（A2,6）如果在代码和省份之间存在比较表，则可以使用VLOOKUP函数提取区域信息.

2. 提取出生日期信息的15位数字: =-TEXT（19＆MID（A2,7,6），“#-00-00”）18位数字: =-TEXT（MID（A2,7,8），“#- 00-00“）15和18位是通用的: =-TEXT（IF（LEN（A2）= 15,19，”“）＆MID（A2,7,6 + IF（LEN（A2）= 18，2， 0）），“#-00-00”）简化公式: =-TEXT（（LEN（A2）= 15）* 19＆MID（A2,7,6 +（LEN（A2）= 18）* 2），“ #-00-00“）（请将输入公式的单元格格式设置为日期格式）. 3.提取15位性别信息: = IF（MOD（RIGHT（A2），2）= 1，” Male“，”女性”）18位: = IF（MOD（MID（A2），17,1）= 1，“男性”，“女性”）15位和18位通用: = IF（MOD（MID（A2，IF（LEN（ A2）= 15,15,17），1），2）= 1，“ Male”，“ Female”）简化公式: = IF（MOD（RIGHT（LEFT（A2,17）），2），“ Male” ，“女性”）4.检查号码的正确性. 18位ID卡号的最后一位是校验码，是根据规则根据ID卡的前17位数字计算得出的，其值为0-9或X.

通常，只要输入不正确的数字，就不会按照规则计算出第18位数字. 当然，不排除用错误数字进行的计算与校验码完全匹配，但是这种情况的可能性很小. 因此号码检测，验证18位数字使用以下公式: = MID（“ 10X98765432”，MOD（SUMPRODUCT（MID（A2，ROW（INDIRECT（“ 1:17”）），1）* 2 ^（18- R OW（INDIRECT（“ 1:17”）））），11​​）+1,1）= RIGHT（A2,1）对于15位ID卡，由于没有校验码，我们只能简单地判断是否出生日期代码是一个有效日期，请避免输入不存在的日期，例如“ 731302”或“ 980230”. = ISNUMBER（-TEXT（19＆MID（A2,7,6），“#-00-00”））组合15和18位后的通用公式为: = IF（LEN（A2）= 18，MID（“ 10X98765432 “，MOD（SUMPRODUCT（MID（A2，ROW（INDIRECT（” 1: 1 7“））），1）* 2 ^（18-ROW（INDIRECT（” 1:17“）））），11​​）+1， 1）= RIGHT（A2），IF（LEN（A2）= 15，ISNUMBER（-TEXT（19＆MID（A2,7,6），“#-00-00”）））））作为当前的15位ID号很少. 如果不需要进一步判断15位数字，可以将公式简化为: = IF（LEN（A2）= 18，MID（“ 10X98765432”，MOD（SUMPRODUCT（MID（A2，ROW（）INDIRECT（“ 1: 1 7“）），1）* 2 ^（18-ROW（INDIRECT（” 1:17“）））），11​​）+1,1）= RIGHT（A2），LEN（A2）= 15）将上面的公式放在单元格B2中，如果结果为TRUE，则ID编号正确，结果为FALSE是错误的.

您也可以在数据有效性中输入上述公式，以防止输入错误的ID号. 操作方法: 选择需要输入的所有单元格区域，例如A2: A10，单击菜单“数据”-“有效性”，在“允许”下拉框中选择“自定义”，然后输入上面的“公式”的15位和18位通用公式可以在确认后确定. 注意: 公式中的“ A2”是您刚选择输入ID卡的单元格区域中的第一个单元格. 如果要在C3: C20中输入ID卡号，请在公式为“ C3”中更改“ A2”. 此外，您还可以先设置单个单元格的数据有效性（此时将公式的A2更改为所选单元格），然后使用格式笔将格式刷到需要相同设置的其他单元格上. 5.将15位提高到18位= IF（LEN（A2）= 15，REPLACE（A2,7，，19）＆MID（“ 10X98765432”，MOD（SUMPRODUCT（MID（RE PLACE（A2,7，，19） ），ROW（INDIRECT（“ 1:17”）），1）* 2 ^（18-ROW（INDIRECT（“ 1:17”）））），11​​）+1,1），A2）6. 18位转换为15位= IF（LEN（A2）= 18，LEFT（REPLACE（A2,7,2，），15），A2）7. 示例表中的公式: B2 = IF（LEN（A2） = 18，MID（“ 10X98765432”，MOD（SUMPRODUCT（MID（A2，ROW（INDIRECT（“ 1: 1 7”））），1）* 2 ^（18-ROW（INDIRECT（“ 1:17”）））） ），11）+1，1）= RIGHT（A2），IF（LEN（A2）= 15，ISNUMBER（-TEXT（19＆MID（A2,7,6），“#-00-00”）））））C2 = IF（A2“”，TEXT（（LEN（A2）= 15）* 19＆MID（A2,7,6 +（LEN（A2）= 18）* 2），“#-00-00”）+ 0，） D2 = IF（A2“”，IF（MOD（RIGHT（LEFT（A2,17）），2），“男性”，“女性”），）E2 = IF（A2“”，DATEDIF（TEXT（（LEN（ A2）= 15）* 19＆MID（A2,7,6 +（LEN（A2）= 18）* 2），“#-00-00”），TODAY（），“ y”），）F2 = IF（A2 “”，VLOOKUP（LEFT（A2,2），Area table！A: D，2，），）H2 = IF（LEN（A2）= 15，REPLACE（A2,7，，19）＆MID（“ 10X98765432”， MOD（SUMPRODUCT（MID（RE PLACE（A2,7，，19），ROW（INDIRECT（“ 1:17”）），1）* 2 ^（18-ROW（INDIRECT（“ 1:17”））））） ，11）+1，1），A2）I2 = IF（LEN（A2）= 18，LEFT（REPLACE（A2,7,2，），15），A2）输入的号码是18位数字，前6位数字是区号. 第七到第十四位数字是出生日期的代码，第十五和第十六位数字是乡镇的代码，第十七位是性别识别码. 男子单打和女子双打在同一地区，并且出生在同一地区. 日期，同性别识别码，18位尾号是前17位的识别码，这是判断ID号是否正确的重要依据.

设置尾号的特殊规则是: 使用前17位数字并将其乘以7、9、10、5、8、4、2、1、6、3、7、9、10、5 8、4、2，然后将所得的加权总和除以11，其余部分从0到10，依次对应于尾数: 1、0，X，9、8、7、6、5、4 ，3、2. 现在，让我们设置相关的公式和函数. 在EXCEL表的四列B，C，D和E中，分别设置了四个项目: ID号，尾号，参数和检查结果. 步骤1: 在ID卡号列中，将整个列设置为文本格式，然后在ID卡号项名称下的单元格（例如，单元格B3）中输入一个18位的第二代ID卡号. 步骤: 在参数项下的同一单元格（D3单元格）中，输入以下函数公式: = 12-（MID（B3,1,1）* 7 + MID（B3,2,1）* 9 + MID（B3 ，3,1）* 10 + MID（B3,4,1）* 5 + MID（B3,5,1）* 8 + MID（B3,6,1）* 4 + MID（B3,7,1）* 2 + MID（B3,8,1）* 1 + MID（B3,9,1）* 6 + MID（B3,10,1）* 3 + MID（B3,11,1）* 7 + MID（B3， 12,1）* 9 + MID（B3,13,1）* 10 + MID（B3,14,1）* 5 + MID（B3,15,1）* 8 + MID（B3,16,1）* 4 + MI D（B3,17,1）* 2）+ INT（（MID（B3,1,1）* 7 + MID（B3,2,1）* 9 + MID（B3,3,1）* 10 + MID（B3,4,1）* 5 + MID（B3,5,1）* 8 + MID（B3,6,1）* 4 + MID（B3,7,1）* 2 + MID（B3,8， 1）* 1 + MID（B3,9,1）* 6 + MID（B3,10,1）* 3 + MID（B3,11,1）* 7 + MID（B3,12,1）* 9 + MID （B3,13,1）* 10 + MID（B3,14,1）* 5 + MID（B3,15,1）* 8 + MID（B3,16,1）* 4 + MID（B3,17，1 ）* 2）/ 11）* 11，目的是计算单元格B3的ID数的加权和，然后将其除以11得到余数，再减去12得到参数；步骤3: 在最后一个数字项列中，在同一单元格（C3单元格）中输入以下公式函数: = IF（D3 = 12,1，IF（D3 = 11,0，IF（D3 = 10，“ X”， D3））），目的是遵循特殊的尾号编码规则，第二步将D3单元格参数计算结果转换为正确的尾号；步骤4: 在测试项目下的单元格（E3单元格）中输入公式函数: = IF（MID（B3,18,1）-D3 = 0，“ correct”，“ error”），目的是比较B3 ID卡的结束号码，并计算出正确的结束号码以指示其是否正确. 第五步: 批量检查. 输入或复制ID卡批次到B3下方的B列中，选择C3: E3，然后将光标置于右下角. 当显示黑色十字时，按住鼠标左键并双击您输入的ID号. 在接下来的三列中，所需的所有结果将立即显示.

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