EXCEL怎么检查身份证号码是否正确

EXCEL怎么检查身份证号码是否正确

在日常工作中，我们常常需要在Excel中处理和验证大量的身份证号码。如何确保这些号码的正确性？本文将详细介绍三种检查方法：格式检查、校验码验证和逻辑验证，并提供具体的Excel公式实现方法。

检查身份证号码是否正确的主要方法有：格式检查、校验码验证、逻辑验证。其中，校验码验证是最为重要和复杂的一步，因为它涉及到一系列的数学计算和规则。下面将详细介绍如何在Excel中实现这些检查方法。

一、格式检查

格式检查是第一步，也是最基础的一步。中国的身份证号码分为15位和18位两种，其中18位身份证号码是当前的标准格式。18位身份证号码的格式为：前6位表示地区代码，接下来8位表示出生日期（YYYYMMDD），第15到17位表示顺序码，最后一位是校验码（可能是数字或字母X）。

1.1 检查长度

首先，我们需要确保身份证号码的长度是18位。可以使用Excel中的
LEN
函数来实现：

  
=LEN(A1)=18
  

1.2 检查字符类型

其次，身份证号码的前17位应该都是数字，最后一位可以是数字或字母X。可以使用Excel中的
ISNUMBER
函数和
MID
函数来检查：

  
=AND(ISNUMBER(VALUE(LEFT(A1, 17))), OR(ISNUMBER(VALUE(RIGHT(A1, 1))), RIGHT(A1, 1)="X"))
  

二、校验码验证

校验码是身份证号码的最后一位，用于验证前17位的正确性。校验码的计算涉及到一系列的权重和模11的操作。具体步骤如下：

2.1 权重设置

身份证号码的前17位各自对应的权重如下：7、9、10、5、8、4、2、1、6、3、7、9、10、5、8、4、2。

2.2 计算加权和

将前17位数字分别乘以对应的权重，然后求和。可以使用Excel中的
SUMPRODUCT
函数来实现：

  
=SUMPRODUCT(--MID(A1, {1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17}, 1), {7, 9, 10, 5, 8, 4, 2, 1, 6, 3, 7, 9, 10, 5, 8, 4, 2})
  

2.3 计算校验码

将加权和除以11，取余数。余数对应的校验码如下：0对应1，1对应0，2对应X，3对应9，4对应8，5对应7，6对应6，7对应5，8对应4，9对应3，10对应2。
可以使用
CHOOSE
函数来实现：

  
=CHOOSE(MOD(SUMPRODUCT(--MID(A1, {1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17}, 1), {7, 9, 10, 5, 8, 4, 2, 1, 6, 3, 7, 9, 10, 5, 8, 4, 2}), 11) + 1, "1", "0", "X", "9", "8", "7", "6", "5", "4", "3", "2")
  

2.4 比较校验码

最后，将计算得到的校验码与身份证号码的第18位进行比较：

  
=RIGHT(A1, 1) = CHOOSE(MOD(SUMPRODUCT(--MID(A1, {1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17}, 1), {7, 9, 10, 5, 8, 4, 2, 1, 6, 3, 7, 9, 10, 5, 8, 4, 2}), 11) + 1, "1", "0", "X", "9", "8", "7", "6", "5", "4", "3", "2")
  

三、逻辑验证

逻辑验证是指验证身份证号码中包含的出生日期和地区代码是否合法。

3.1 验证出生日期

可以使用
DATE
函数来验证身份证号码中包含的出生日期是否合法：

  
=AND(ISNUMBER(DATE(MID(A1, 7, 4), MID(A1, 11, 2), MID(A1, 13, 2))))
  

3.2 验证地区代码

中国的地区代码是有一定规律的，可以通过查找表格来验证地区代码的合法性。可以在Excel中建立一个地区代码的查找表，然后使用
VLOOKUP
函数来验证：

  
=NOT(ISNA(VLOOKUP(LEFT(A1, 6), 地区代码表, 1, FALSE)))
  

综合验证公式

最终，我们可以将以上检查步骤综合起来，形成一个综合验证公式：

  
=AND(LEN(A1)=18, ISNUMBER(VALUE(LEFT(A1, 17))), OR(ISNUMBER(VALUE(RIGHT(A1, 1))), RIGHT(A1, 1)="X"), RIGHT(A1, 1) = CHOOSE(MOD(SUMPRODUCT(--MID(A1, {1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17}, 1), {7, 9, 10, 5, 8, 4, 2, 1, 6, 3, 7, 9, 10, 5, 8, 4, 2}), 11) + 1, "1", "0", "X", "9", "8", "7", "6", "5", "4", "3", "2"), AND(ISNUMBER(DATE(MID(A1, 7, 4), MID(A1, 11, 2), MID(A1, 13, 2)))), NOT(ISNA(VLOOKUP(LEFT(A1, 6), 地区代码表, 1, FALSE))))
  

这个公式可以帮助我们在Excel中全面检查身份证号码的正确性。

结语

通过上述方法，我们可以在Excel中实现对身份证号码的全面检查，包括格式检查、校验码验证和逻辑验证。这不仅提高了数据的准确性，还能有效避免因身份证号码错误导致的各种问题。希望这篇文章能对你有所帮助，让你在Excel中更好地处理身份证号码的验证工作。

相关问答FAQs：

1. 如何在Excel中检查身份证号码的正确性？

在Excel中，您可以使用公式来检查身份证号码的正确性。首先，将身份证号码输入到单元格中，然后使用以下公式来检查：

=IF(AND(LEN(A1)=18, ISNUMBER(--LEFT(A1, 17)), OR(MOD(SUMPRODUCT(VALUE(MID(A1, ROW(INDIRECT("1:17")), 1)) * {7;9;10;5;8;4;2;1;6;3;7;9;10;5;8;4;2}), 11)=2, AND(MID("10X98765432", MOD(SUMPRODUCT(VALUE(MID(A1, ROW(INDIRECT("1:17")), 1)) * {7;9;10;5;8;4;2;1;6;3;7;9;10;5;8;4;2}), 11)+1, 1) = RIGHT(A1, 1)))), "身份证号码正确", "身份证号码错误")

这个公式将会在检查结果列中显示“身份证号码正确”或“身份证号码错误”。

2. 如何批量检查Excel表格中的身份证号码是否正确？

如果您有一个包含多个身份证号码的Excel表格，并且想要批量检查它们的正确性，可以使用以下步骤：

2. 在新的列中，输入上述公式来检查身份证号码的正确性。

3. 将公式应用到整个身份证号码列。

4. Excel将会自动检查每个身份证号码，并在相邻列中显示“身份证号码正确”或“身份证号码错误”。

3. 如果身份证号码检查结果显示错误，我应该怎么处理？

如果Excel表格中的身份证号码检查结果显示为“身份证号码错误”，您可以考虑进行以下操作：

• 检查身份证号码是否输入正确。确保没有输入错误的字符或错位的数字。

• 检查身份证号码的长度是否正确。中国的身份证号码通常为18位。

• 检查身份证号码的校验位是否正确。校验位是身份证号码中的最后一位，用于验证身份证号码的正确性。

• 如果您认为身份证号码是正确的，但仍然显示错误，可能是因为公式中的错误。您可以检查公式是否正确，并尝试重新应用它。

请注意，Excel只能检查身份证号码的格式是否正确，但无法验证身份证号码的真实性。如有需要，建议向相关机构查询身份证号码的有效性。