您的位置：360文档中心› EXcel_VBA身份证号码验证

EXcel_VBA身份证号码验证

合集下载

利用ExcelVBA插件实现证件信息批量校验

利用ExcelVBA插件实现证件信息批量校验

利用ExcelVBA插件实现证件信息批量校验作者：宋义河来源：《计算机光盘软件与应用》2012年第24期摘要：使用Excel表格实现证件申请信息批量填报，并按要求对有关内容进行有效性校验。

通过分析JPG格式图片文件的结构，自行编写代码获取图片尺寸等信息。

利用ExcelVBA 插件，实现了证件提报信息批量校验。

关键词：ExcelVBA插件；JPG图片文件；有效性校验中图分类号：TP311 文献标识码：A 文章编号：1007-9599 （2012） 24-0004-031 引言举办重要会议、活动时，通常需要为与会人员制作发放专用证件。

因为与会人员数量较多，逐一录入效率很低，一般情况下采用批量提报的方式采集数据。

目前，我单位使用公安部第一研究所开发的证件管理软件制作证件，该软件使用Excel表格批量导入制证信息。

申请单位按要求将申请人姓名、身份证号码、单位等信息填入既定格式的Excel表格，与申请人照片保存在同一文件夹内打包提报。

照片文件为JPG格式，以申请人姓名加“-”再加出生日期命名，如“张三-19810213.JPG”。

为了保证照片质量，通常需要对照片尺寸和文件大小提出要求，如照片尺寸指定为413×579像素，文件大小不超过300K。

这种数据采集方式大大减轻了制证人员的工作量，提高了工作效率。

在实际应用中我们也遇到了一些问题：一是导入信息时缺少照片。

有些是因为确实漏报了照片，有些是因为照片不是JPG格式，更多的是因为照片文件命名错误，比如姓名、出生日期与表格内容不一致，文件名中有多余空格，以及因为扩展名被隐藏而在文件名中多输入了“.JPG”，把照片文件命名成类似“张三-19810213.JPG.JPG”的情况。

二是提交的照片不符合要求。

普遍存在尺寸不符的情况，并且很多照片的宽高比例不合适，制作证件时因为拉伸而严重变形。

三是普遍反映填报信息时效率不高。

特别是命名照片文件时，一一核对出生日期比较麻烦，很容易出现错误。

excel表格输入身份证正确性实时验证

excel表格输入身份证正确性实时验证
Excel 表格身份证输入 实时验证
一、示例使用的是 wps2019版 二、主要利用数据有效性 进行设计 三、身份证验证：
1、长度 2、X大写 3、逻辑验证
一、需要设置数据位置 如下图，主要位置在D2单元格
二、选中D2单元格，从菜单上方选择--数据--有效性--下拉-数据有效性
三、弹出对话框
数据有效-选择设置项 一、允许下拉框中选择为自定义 二、公式输入加入下面数据验证公式 注意：本公式只针对D2单元格，如有变 动请将下面的所有D2进行查找替换。
• 首先 公式计算获得验证结果，判断正确返回1，错误返回0 • 返回值 与 1 进行比较 结果=1 返回 True或 False • 数据有效性 根据返回的False 进行验证，False则弹出警告
三、确定即可，保留忽略空值。可以考虑在出 错警告里 添加提示
四、设置完成确定后D2单元格就可以自动验证； 然后将光标放到D2单元格右下角，变为实心十字后， 按住下拉覆盖要输入的单元格即可。
此处会自动累加D2 变为D3。
公式解释：
=IF(LEN(D2)=0,1,IF(LEN(D2)=15,1,IF(LEN(D2)<>18,0,IF(CHOOSE(MOD(SUM(MID(D 2,1,1)*7+MID(D2,2,1)*9+MID(D2,3,1)*10+MID(D2,4,1)*5+MID(D2,5,1)*8+MID(D 2,6,1)*4+MID(D2,7,1)*2+MID(D2,8,1)*1+MID(D2,9,1)*6+MID(D2,10,1)*3+MID(D 2,11,1)*7+MID(D2,12,1)*9+MID(D2,13,1)*10+MID(D2,14,1)*5+MID(D2,15,1)*8+ MID(D2,16,1)*4+MID(D2,17,1)*2),11)+1,1,0,"X",9,8,7,6,5,4,3,2)=IF(ISNUMBER(RI GHT(D2,1)*1),RIGHT(D2,1)*1,"X"),1,0))))=1

Excel中检测输入的身份证号码是否合法函数校验方法大揭秘

Excel中检测输入的身份证号码是否合法函数校验方法大揭秘

Excel中检测输⼊的⾝份证号码是否合法函数校验⽅法⼤揭秘什么时候需要校验⾝份证号码合法性呢？1.防⽌输⼊⼈员随⼿输⼊⼀个18位数字冒充⾝份证号码2.防⽌输⼊⼿误,有时候并⾮故意输错注意本⽅法⽆法检测⾝份证是否真实存在,只能从格式上检查号码是否合法⾝份证号码校验基本原理第1步：把号码的前17位分别乘以不同的系数,系数表如下第1步第2步：把17位数字和系数相乘的结果相加，加和值再除以11，取其余数。

⽤得到的余数与⾝份证第18位号码进⾏对照,余数与⾝份证号码第18位对应关系如下表第2步例如例如：某男⼠的⾝份证号码是622924************。

⾸先我们得出前17位与系数的乘积和是320，除以11的余数是1，对应第18位数是0。

所以这是⼀个不合法的⾝份证号码。

判断⾝份证号码是否合法的函数公式计算第18位数=IF(LEN($C2)=18,MID("10X98765432",MOD(SUMPRODUCT(VALUE(MID($C2,ROW($1:$17),1)),$B$2:$B$18),11)+1,1),"长度错误")判断是否合法=IF(LEN($C2)=18,IF(MID("10X98765432",MOD(SUMPRODUCT(VALUE(MID($C2,ROW($1:$17),1)),$B$2:$B$18),11)+1,1)=RIGHT($C2,1),"合法","不合法"),"长度错误")其中，C2为⾝份证号码，$B$2:$B$18为第1到17个系数。

这个⼀般存到另⼀个sheet⾥或者顶⼀个名称,与实际使⽤的表分开函数分步讲解VALUE(MID($C2,ROW($1:$17),1))ROW函数返回⼀个1到17的数组，配个mid函数依次取出C2号码中前17个数字，并将截取的⽂本⽤value转换为数值SUMPRODUCT(VALUE(MID($C2,ROW($1:$17),1)),$B$2:$B$18),11)⽤SUMPRODUCT函数返回MID($C2,ROW($1:$17),1)数组和$B$2:$B$18(校验表)区域对应相乘的和MOD(SUMPRODUCT(VALUE(MID($C2,ROW($1:$17),1)),$B$2:$B$18),11)⽤mod对上⼀步求乘积之取11的余数MID("10X98765432",MOD(SUMPRODUCT(VALUE(MID($C2,ROW($1:$17),1)),$B$2:$B$18),11)+1,1)⽤MID函数截取10X98765432中的1位数，即⾝份证号码的第18位。

EXcel_VBA身份证号码验证

EXcel_VBA身份证号码验证

'身份证号码验证Public Function identi_check(identitynum As String) '身份证号码验证Dim jyw As Longidenti = Replace(identitynum, " ", "")If (Len(identi) > 0 And Len(identi) < 15) Or Len(identi) > 18 Thenidenti = "不正确,非15、18位"ElseIf Len(identi) = 0 Thenidenti = "不正确，为空"ElseIf Len(identi) = 15 Thenj = 0For i = 1 To 15If Not Mid(identi, i, 1) Like "[0-9]" Thenj = j + 1End IfNext iIf j > 0 Thenidenti = "不正确，包含非数字"ElseIf Val(Mid(identi, 9, 2)) > 12 Thenidenti = "不正确，月份大于12"ElseIf Val(Mid(identi, 11, 2)) > 31 Thenidenti = "不正确，日期大于31"'MsgBox identiElseidenti = ""End IfElseIf Len(identi) = 18 Then'For i = 1 To 17jyw = (Mid(identi, 1, 1) * 7 + Mid(identi, 2, 1) * 9 + Mid(identi, 3, 1) * 10 _+ Mid(identi, 4, 1) * 5 + Mid(identi, 5, 1) * 8 + Mid(identi, 6, 1) * 4 _+ Mid(identi, 7, 1) * 2 + Mid(identi, 8, 1) * 1 + Mid(identi, 9, 1) * 6 + Mid(identi, 10, 1) * 3 _+ Mid(identi, 11, 1) * 7 + Mid(identi, 12, 1) * 9 + Mid(identi, 13, 1) * 10 + Mid(identi, 14, 1) * 5 _+ Mid(identi, 15, 1) * 8 + Mid(identi, 16, 1) * 4 + Mid(identi, 17, 1) * 2)jyw = jyw Mod 11yzm = Replace("1 0 X 9 8 7 6 5 4 3 2", " ", "")If Mid(yzm, jyw + 1, 1) <> Mid(identi, 18, 1) Thenidenti = "不正确"Elseidenti = ""End If'7 9 10 5 8 4'2 1 6 3 7 9 10 5'8 4 2'Next iEnd Ifidenti_check = identi'MsgBox "yyy" & identi 'Return (identitynum) End Function。

VBA快速生成随机身份证号与银行卡号的实用方法

VBA快速生成随机身份证号与银行卡号的实用方法

VBA快速生成随机身份证号与银行卡号的实用方法VBA（Visual Basic for Applications）是一种用于Microsoft Office软件中的宏语言，可以通过VBA编程来增强Office应用程序的功能。

在实际工作中，经常需要生成一些测试数据，其中包括随机的身份证号和银行卡号。

本文将介绍如何使用VBA快速生成随机身份证号与银行卡号的实用方法。

生成随机身份证号的方法：身份证号是一个含有18位字符的字符串，其中是包含了地区、生日、性别以及校验码等信息。

随机生成身份证号时，需要注意以下几个要点：1. 地区码：前6位是地区码，代表身份证持有人的所在地区。

2. 出生日期：紧接着地区码的6位数字代表出生年月日。

3. 顺序码：接下来的3位数字是顺序码，代表在该地区、出生日期、性别下的顺序号。

4. 校验码：最后一位是校验码，根据前面的17位数字计算得出，用于校验身份证号是否有效。

根据以上要点，可以编写以下VBA代码来生成随机身份证号：```vbaFunction GenerateRandomID() As StringDim id As String'随机生成地区码Dim region As Stringregion = "52xxxx"'随机生成出生日期Dim birthDate As StringDim year As IntegerDim month As IntegerDim day As Integeryear = Int((Date - 50) - (Date - 20) * Rnd) '20到50岁之间month = Int((12 - 1) * Rnd) + 1 '1到12月day = Int((31 - 1) * Rnd) + 1 '1到31日birthDate = Format(year, "0000") & Format(month, "00") & Format(day, "00")'随机生成顺序码Dim sequence As Stringsequence = Format(Int((999 - 1 + 1) * Rnd) + 1, "000")'计算校验码Dim checkCode As StringDim code As Stringcode = region & birthDate & sequence '拼接前17位数字 checkCode = GetCheckCode(code) '计算校验码id = code & checkCode '得到完整的身份证号GenerateRandomID = idEnd FunctionFunction GetCheckCode(code As String) As StringDim weights() As Integerweights = Array(7, 9, 10, 5, 8, 4, 2, 1, 6, 3, 7, 9, 10, 5, 8, 4, 2) Dim checkDigit As StringDim sum As IntegerDim i As Integersum = 0For i = 1 To 17sum = sum + Mid(code, i, 1) * weights(i - 1)Next icheckDigit = "10X98765432"GetCheckCode = Mid(checkDigit, (sum Mod 11) + 1, 1)End Function```上述VBA代码包含两个函数，`GenerateRandomID`函数用于生成随机的身份证号码，`GetCheckCode`函数用于计算校验码。

vba 判断excel本列身份证号是否正确

vba 判断excel本列身份证号是否正确

Function getYjm(num)'dd = "新身份证：" & Left(xian, 6) & "19" & Right(xian, 9) & CStr(getYjm(xian)) Dim ciDIf Len(num) = 15 ThenciD = Left(num, 6) & "19" & Right(num, 9)ElseIf Len(num) = 17 Or Len(num) = 18 ThenciD = Left(num, 17)ElsegetYjm = "f"Exit FunctionEnd IfEnd IfFor i = 1 To Len(ciD)If Not IsNumeric(Mid(ciD, i, 1)) ThengetYjm = "f"Exit FunctionEnd IfNext insum = Mid(ciD, 1, 1) * 7nsum = nsum + Mid(ciD, 2, 1) * 9nsum = nsum + Mid(ciD, 3, 1) * 10nsum = nsum + Mid(ciD, 4, 1) * 5nsum = nsum + Mid(ciD, 5, 1) * 8nsum = nsum + Mid(ciD, 6, 1) * 4nsum = nsum + Mid(ciD, 7, 1) * 2nsum = nsum + Mid(ciD, 8, 1) * 1nsum = nsum + Mid(ciD, 9, 1) * 6nsum = nsum + Mid(ciD, 10, 1) * 3nsum = nsum + Mid(ciD, 11, 1) * 7nsum = nsum + Mid(ciD, 12, 1) * 9nsum = nsum + Mid(ciD, 13, 1) * 10nsum = nsum + Mid(ciD, 14, 1) * 5nsum = nsum + Mid(ciD, 15, 1) * 8nsum = nsum + Mid(ciD, 16, 1) * 4nsum = nsum + Mid(ciD, 17, 1) * 2'*计算校验位check_number = 12 - nsum Mod 11If check_number = 10 Thencheck_number = "X"End IfIf check_number = 12 Thencheck_number = "1"End IfIf check_number = 11 Thencheck_number = "0"End IfgetYjm = check_numberEnd FunctionFunction getCsrc(num, flaG)lenx = Len(num)If lenx = 15 Thenyy = "19" & Mid(xian, 7, 2)mm = Mid(num, 9, 2)dd = Mid(num, 11, 2)End IfIf lenx = 18 Thenyy = Mid(num, 7, 4)mm = Mid(num, 11, 2)dd = Mid(num, 13, 2)End Ifss = yy & "/" & mm & "/" & ddIf flaG = 0 ThenIf IsDate(ss) ThengetCsrc = ssElsegetCsrc = "1900/02/02"End IfElseIf IsDate(ss) ThengetCsrc = ssElsegetCsrc = "f"End IfEnd IfEnd FunctionFunction getSbie(num, flaG)Dim yy, mm, dd As Stringlenx = Len(num)If lenx = 15 Thenaa = Mid(num, 15, 1) '15位身分证取第十五位,能被2整除为女性End IfIf lenx = 18 Thenaa = Mid(num, 17, 1) '18位身分证取第十七位,能被2整除为女性End IfIf flaG = 0 ThenIf (Not IsNumeric(aa)) Or aa = "" Thenaa = 1End IfIf aa Mod 2 = 0 Thenxb = "女"Elsexb = "男"End IfgetSbie = xbElseIf (Not IsNumeric(aa)) Or aa = "" ThengetSbie = "f"Exit FunctionEnd IfIf aa Mod 2 = 0 Thenxb = "女"Elsexb = "男"End IfgetSbie = xbEnd IfEnd FunctionFunction banduanShengfen(num) As Boolean Dim a, b, c, da = getYjm(num)b = getCsrc(num, 1)c = getSbie(num, 1)num = Replace(num, "x", "X")d = Right(num, 1)If Len(num) = 18 ThenIf StrComp(d, a) = 0 ThenElsea = "f"End IfEnd IfIf (Len(num) < 15) Or (Len(num) > 18) Thena = "f"End IfIf 15 < Len(num) And Len(num) < 18 Thena = "f"End IfIf a = "f" Or b = "f" Or c = "f" Then banduanShengfen = FalseElsebanduanShengfen = TrueEnd IfEnd FunctionSub yianzhengShengfen()Dim rng As RangeDim selCount, i, j, k, concelCount, conrowCount As IntegerDim selStr(100) As StringDim selTemp() As StringDim Tempstr As StringconcelCount = edRange.Columns.CountconrowCount = edRange.Rows.CountFor Each rng In SelectionDoEventsselTemp = Split(rng.Address, "$")Tempstr = selTemp(1)j = 0For i = 0 To selCountIf selTemp(1) = selStr(i) Thenj = 1End IfNext iIf j = 0 ThenselStr(selCount) = selTemp(1)selCount = selCount + 1End IfNextk = 0Tempstr = ""For j = 1 To conrowCountDoEventsIf Not banduanShengfen(Replace(Range(selStr(0) & j).V alue, " ", "")) ThenRange(selStr(0) & j).Interior.Color = RGB(255, 98, 98)Tempstr = Tempstr & j & ","k = k + 1End IfNext jIf Not Range(selStr(0) & "1").Comment Is Nothing ThenRange(selStr(0) & "1").ClearCommentsIf k > 0 ThenRange(selStr(0) & "1").AddComment "本列中共" & k & "个身份号不对,其中:" & Left(Tempstr, Len(Tempstr) - 1) & "行身份证号不对"ElseRange(selStr(0) & "1").AddComment "本列身份证号全对"End IfElseIf k > 0 ThenRange(selStr(0) & "1").AddComment "本列中共" & k & "个身份号不对,其中:" & Left(Tempstr, Len(Tempstr) - 1) & "行身份证号不对"ElseRange(selStr(0) & "1").AddComment "本列身份证号全对"End IfEnd IfEnd Sub。

excel身份证验证公式

excel身份证验证公式

excel身份证验证公式
Excel身份证验证公式
1.验证格式：数据校验公式可以验证身份证号码是否正确和合法。

Excel中可以使用“LEN()”函数来确定身份证号码字符串的长度，再与18位数对比来明确证书是否存在。

2.验证位数：只有18位数身份证号码才能使用来进行校验，15位号码已不再使用，可以通过旧省份前两位是否是“35”、“36”或“37”来判断。

3.验证省级代码：通过省份前两位码来验证省级代码，与身份证号码前两位相对应。

4.验证出生日期：可以通过对身份证号码出生日期的部分（第7到14位）与Excel 中“LEFT()”“MID()”等函数结合，来验证身份证号码的出生日期，验证准确。

5.验证顺序号：通过身份证号码的第16位和第17位数字代表性别，第15位和第18位数字代表性别，第15位数字用于最后一位校验码的算法，如果正确，身份证号码可以正确匹配！
6.验证位：计算机通过一组算法，通过身份证号码前17位数字，计算
出一个0-10之间的数字，如果运算结果与最后一位数字相等则表示验证通过！
此外，Excel中对于身份证号码的验证，还可以利用VBA编程语言进行。

其中可以使用“If（）Then（）Else（）”结构计算出一组结果，以决定身份证号码是否要验证成功。

用VB实现18位身份证输入错误的批量验证

用VB实现18位身份证输入错误的批量验证

用VB实现18位身份证输入错误的批量验证在学校里，建学生档案、中考、高考报名时通常都要班主任收集和输入学生的身份证号码，因为身份证数字位数多，既容易输错，逐一验证更是费工夫，如果能够用程序进行批量验证，把输错的号码找出来，只针对错误的号码进行验证就轻松得多了。

本算法只能发现输入错误的18位身份证号码，不涉及身份证信息的解读和真伪验证。

VB程序的界面很简单，最少只要一个TEXTBOX和一个命令按钮（下图）。

TEXTBOX只要把MultiLine（多行）属性设为True和ScrollBar(滚动条)属性设为2-Vertical(垂直)即可。

这样TEXTBOX就可以容纳多个身份证号码，每个一行。

在EXCEL 表格里输好然后统一复制过来即可。

18位身份证的验证算法是这样的：①.将前面的身份证号码17位数分别乘以不同的系数。

从第一位到第十七位的系数分别为：7 9 10 5 8 4 2 1 6 3 7 9 10 5 8 4 2②.将这17位数字和系数相乘的结果相加。

③.用加出来和除以11，看余数是多少？④.余数只可能有0 1 2 3 4 5 6 7 8 9 10这11个数字。

其分别对应的最后一位身份证的号码为1 0 X 9 8 7 6 5 4 3 2。

而程序的实现过程就是先把TEXTBOX内的身份证号码按行拆成字符数组，然后逐个对每个身份证号码的前17位进行计算，得出其校验码再与第18位号码进行对照，如果相同就是对的，如果不同则说明有输入错误。

然后在每个错误的身份证号码后加上相关信息，使用者可以根据错误信息，只针对有错误的身份证号码来核对证件上的身份证号码。

按钮的代码如下：Private Sub Command1_Click()REM 准备工作，前17位系数和对应检验码放入数组wi$ = "7 9 10 5 8 4 2 1 6 3 7 9 10 5 8 4 2"yzm$ = "1 0 X 9 8 7 6 5 4 3 2"Dim s1() As StringDim s2() As Strings1 = Split(wi$, " ")s2 = Split(yzm$, " ")REM 获取身份证号并验证Dim s() As Stringtemp$ = Text1.Texts = Split(temp$, vbNewLine)x = UBound(s) '检测数组的上标For i = 0 To xIf Len(s(i)) = 18 Thensu = 0For j = 1 To 17c$ = Mid$(s(i), j, 1)a = Val(c$)su = su + a * Val(s1(j - 1))Next jy = su Mod 11c$ = Mid(s(i), 18, 1)If UCase(c$) <> s2(y) Thens(i) = s(i) & " ←校验错误"End IfElses(i) = s(i) & " ←不是18位"End IfNextREM 清除TEXTBOX并把字符数组信息写入TEXTBOX Text1.Text = ""For i = 0 To xText1.Text = Text1.Text & s(i) & vbNewLineNextEnd Sub验证结果如下图：。

  1. 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
  2. 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
  3. 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。

'身份证号码验证
Public Function identi_check(identitynum As String) '身份证号码验证
Dim jyw As Long
identi = Replace(identitynum, " ", "")
If (Len(identi) > 0 And Len(identi) < 15) Or Len(identi) > 18 Then
identi = "不正确,非15、18位"
ElseIf Len(identi) = 0 Then
identi = "不正确，为空"
ElseIf Len(identi) = 15 Then
j = 0
For i = 1 To 15
If Not Mid(identi, i, 1) Like "[0-9]" Then
j = j + 1
End If
Next i
If j > 0 Then
identi = "不正确，包含非数字"
ElseIf Val(Mid(identi, 9, 2)) > 12 Then
identi = "不正确，月份大于12"
ElseIf Val(Mid(identi, 11, 2)) > 31 Then
identi = "不正确，日期大于31"
'MsgBox identi
Else
identi = ""
End If
ElseIf Len(identi) = 18 Then
'For i = 1 To 17
jyw = (Mid(identi, 1, 1) * 7 + Mid(identi, 2, 1) * 9 + Mid(identi, 3, 1) * 10 _
+ Mid(identi, 4, 1) * 5 + Mid(identi, 5, 1) * 8 + Mid(identi, 6, 1) * 4 _
+ Mid(identi, 7, 1) * 2 + Mid(identi, 8, 1) * 1 + Mid(identi, 9, 1) * 6 + Mid(identi, 10, 1) * 3 _
+ Mid(identi, 11, 1) * 7 + Mid(identi, 12, 1) * 9 + Mid(identi, 13, 1) * 10 + Mid(identi, 14, 1) * 5 _
+ Mid(identi, 15, 1) * 8 + Mid(identi, 16, 1) * 4 + Mid(identi, 17, 1) * 2)
jyw = jyw Mod 11
yzm = Replace("1 0 X 9 8 7 6 5 4 3 2", " ", "")
If Mid(yzm, jyw + 1, 1) <> Mid(identi, 18, 1) Then
identi = "不正确"
Else
identi = ""
End If
'7 9 10 5 8 4
'2 1 6 3 7 9 10 5
'8 4 2
'Next i
End If
identi_check = identi
'MsgBox "yyy" & identi 'Return (identitynum) End Function。

相关文档
最新文档