EXcel_VBA身份证号码验证

利用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中检测输⼊的⾝份证号码是否合法函数校验⽅法⼤揭秘什么时候需要校验⾝份证号码合法性呢？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位。

'身份证号码验证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（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`函数用于计算校验码。

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

本算法只能发现输入错误的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验证结果如下图：。

校验居民身份证号码函数 在日常工作中，我们经常用电子表格输入身份证号码，往往出现输入错误，校对麻烦。

由于身份证中第十八位数字的计算方法较为繁琐，下面提供了一个自定义函数，很方便。

=jysfz(身份证号码 字符型)示例：340524************←号码#NAME?=jysfz(A7)#NAME?=jysfz("340524************") 340524************#NAME? 340524************#NAME?340524************#NAME?340524************#NAME? 340524************#NAME? 340524************#NAME? 3405241952060122188#NAME?34052419710702233#NAME? 3405241964100622#NAME? 340524************#NAME? 34052419641006225想#NAME?#NAME?这样也有错！#NAME?　身份证的号码是按照国家的标准编制的，由18位组成：前六位为行政区划代码，第七至第十四位为出生日期码，第15至17位为顺序码，第15、16位为乡镇代码（一个乡镇可能有多个代码），第17位代表性别（奇数为男，偶数为女），第18位为校验码。

作为尾号的校验码，是由号码编制单位按统一的公式计算出来的，如果某人的尾号是0-9，都不会出现X，但如果尾号是10，那么就得用X来代替，因为如果用10做尾号，那么此人的身份证就变成了19位，而19位的号码违反了国家标准，并且我国的计算机应用系统也不承认19位的身份证号码。

Ⅹ是罗马数字的10，用Ⅹ来代替10，可以保证公民的身份证符合国家标准。

但是我国的居民身份证在升位后，一些人的尾号变成了X，这部分人在工作生活中，例如去银行存取钱、去汽车公司租赁汽车或者报名参加考试等等过程中，往往不被检验者理解，认为是假身份证，这样的误会给很多人的生活带来不便。

如何在VBA中处理Excel的数据验证VBA（Visual Basic for Applications）是一种用于处理Microsoft Office应用程序的编程语言。

在Excel中，VBA允许用户编写自定义的宏，用于自动执行各种任务。

其中一个常见的需求是处理Excel的数据验证。

数据验证可以确保Excel中的数据符合特定的规则和限制。

本文将介绍如何在VBA中处理Excel的数据验证。

数据验证是一种在单元格中强制执行特定规则的功能，以确保输入的数据符合预期。

数据验证可以定义以下条件：1. 允许的值范围：设置一个数值范围，只有在此范围内的值才能被输入到单元格中。

2. 列表选项：限制输入的选项为预定义的列表中的值。

3. 文本长度限制：限制输入文本的最大长度。

4. 自定义公式：使用自定义的公式来验证输入数据。

下面将介绍如何使用VBA在Excel中处理这些数据验证需求。

1. 允许的值范围：使用VBA，可以通过以下代码在Excel中设置允许的值范围：```vbaSub SetRangeValidation()With Range("A1").Validation.Delete.Add Type:=xlValidateWholeNumber, _AlertStyle:=xlValidAlertStop, _Operator:=xlBetween, _Formula1:="1", _Formula2:="10".InputTitle = "输入错误".ErrorTitle = "超出范围".InputMessage = "请输入一个介于1到10之间的整数。

".ErrorMessage = "输入的值超出了允许的范围，请重新输入。

"End WithEnd Sub```上述代码将在单元格A1中设置了一个整数的范围验证，限制输入的值必须介于1到10之间。

VBA实现Excel的数据校验与验证Excel是一款功能强大的电子表格软件，而VBA（Visual Basic for Applications）是一种编程语言，可用于扩展Excel的功能。

在本文中，我们将探讨如何使用VBA实现Excel的数据校验与验证。

数据校验与验证是确保数据的准确性和完整性的关键步骤。

通过实施适当的校验和验证规则，可以防止用户输入错误或不完整的数据，并提高数据处理的效率。

下面，我们将介绍几个常见的数据校验与验证技术，并通过VBA代码实现它们。

一、数据格式校验数据格式校验是确保数据按照指定格式输入的过程。

例如，日期应该按照"yyyy-mm-dd"的格式输入，货币值应该带有货币符号和正确的小数位数。

VBA提供了Format函数，可以轻松地实现数据格式的校验。

下面是一个示例：Sub CheckDateFormat()Dim rng As RangeDim cell As RangeSet rng = Range("A1:A10")For Each cell In rngIf Not IsDate(cell.Value) Thencell.Value = Format(cell.Value, "yyyy-mm-dd")End IfNext cellEnd Sub上述代码将检查A1:A10范围内的所有单元格，如果单元格的值不是日期格式，则将其转换为"yyyy-mm-dd"的格式。

二、数据范围校验数据范围校验是确保数据落在指定范围内的过程。

例如，年龄应该在18到65之间，成绩应该在0到100之间。

VBA 提供了Range对象的Validation属性，可以设置数据范围校验规则。

下面是一个示例：Sub CheckDataRange()Dim rng As RangeSet rng = Range("A1:A10")With rng.Validation.Delete.Add Type:=xlValidateWholeNumber, _AlertStyle:=xlValidAlertStop, _Operator:=xlBetween, _Formula1:="18", Formula2:="65"End WithEnd Sub上述代码将检查A1:A10范围内的所有单元格，如果单元格的值不是在18到65之间，则会显示验证错误。

Excel中进行验证身份证号数据有效性的操作技巧
设置单元格的数据有效性，使得输入的身份证号码不出错。

具体该怎么去进行操作的呢?今天，店铺就教大家在Excel中进行验证身份证号数据有效性的操作技巧。

Excel中进行验证身份证号数据有效性的操作步骤
1、先建立下面的表格，要设置蓝色框区域的单元格的数据有效性，使得输入的身份证号码不出错。

2、选中G3-G11的单元格。

3、如下图，1处单击“数据”，2处单击“数据有效性”，出现下列对话框。

4、现在可以设置：允许的地方先“文本长度”;数据选“等于”;长度选“18”，因为身份证号码是18位。

5、点下图1处“出错警告”;就有下列选项，在2处选“停止”;3处输入出错提示的标题”输入错误“;4处输入错误提示信息”亲，身份证数字位数不对。

6=现在我试一下，在蓝色框处填上10位，按回车就有如下提示，成功了。

7、如果是设性别可以参考下图。

8、如果是设出生年月日可以参考下图。

9、如果是设身高可以参翻考下图。

注意事项：
第七步，输入男女中间的逗号是英文状态下。

VBA操作Excel宏实现数据校验与转换数据校验与转换是在日常工作中经常遇到的任务之一。

通过使用VBA编程语言和Excel宏，我们可以轻松地实现数据校验与转换的功能，提高工作效率和准确性。

本文将介绍如何使用VBA操作Excel宏来实现数据校验与转换。

一、数据校验在处理大量数据时，我们经常需要确保数据的准确性。

VBA提供了强大的数据校验功能，以下是一个示例来说明如何使用VBA实现数据校验。

首先，打开一个包含数据的Excel文件，我们假设要校验的数据位于Sheet1的A列。

我们需要确保这些数据都是大于等于0的整数。

在Excel中按下Alt+F11组合键打开VBA编辑器，然后在左侧的项目浏览器中找到Sheet1（Sheet1是我们要校验数据所在的工作表）。

双击Sheet1打开代码编辑窗口。

在代码编辑窗口中输入以下代码：```Private Sub Worksheet_Change(ByVal Target As Range)Dim cell As RangeOn Error GoTo ErrorHandlerApplication.EnableEvents = FalseFor Each cell In TargetIf cell.Column = 1 And cell.Value <> "" Then '校验A列 If Not IsNumeric(cell.Value) Then '判断是否为数字 MsgBox "请输入数字！", vbExclamationcell.ClearContents '清除错误数据ElseIf cell.Value < 0 Then '判断是否小于0MsgBox "请输入大于等于0的数字！", vbExclamationcell.ClearContentsElseIf cell.Value <> Int(cell.Value) Then '判断是否为整数MsgBox "请输入整数！", vbExclamationcell.ClearContentsEnd IfEnd IfNext cellErrorHandler:Application.EnableEvents = TrueEnd Sub```这段代码的功能是在数据被修改时触发，判断A列的数据是否满足要求。

竭诚为您提供优质文档/双击可除excel表格在同一张表格里如何检查身份证号码和名字对照是否一致篇一：如何用excel来判断身份证号码是否正确如何用excel来判断身份证号码是否正确？没有参照系，只要格式、位数没错，就难以辨其是非了；而给出参照系，即可判别：★按补充题意，用性别判别对与错：1、如果以c列输入性别为准，在b列输入公式“=iF(iF(mod(mid(a1,17,1),2)=0,"女","男")=c1,"第17位正确","第17位错")”，向下复制到需要处如b3，即可判别a列号码第17位数码之对错，如黄色区域所示。

2、如果以a列输入号码为准，在d列输入公式“=iF(iF(mod(mid(a1,17,1),2)=0,"女","男")=c1,"c列正确","c列填错")”，向下复制到需要处如d3，即可判别c列填写之对与错，如绿色区域所示。

3、若以c列为准，还可以用红色标示a列数码以示其17位错误，以与b列相呼应。

请选中a1:a3在“格式/条件格式”中查看公式和格式具体设置。

附件：辨别.xls篇二：excel中判断身份证号的几个方法excel中判断身份证号是否正确的方法探讨身份证有15位和18位两种，身份证位数是否正确，我们可以用len函数判断。

但身份证上的日期是否合法：月份是否在1-12之间，日期是否在1-31之间，并且2月份只有28或29天，其他月份30或31天，都不能超过范围。

另外一般规定6岁以上才可以办理身份证，也就是年份也有一个超范围的可能性。

综合起来看，有三类错误：“身份证位数不对”、“月日错误”、“年份错误”。

假定身份证号码在b1单元格，下面的公式可以综合判断以上三种错误：=iF(oR(len(b1)=18,len(b1)=15),iF(len(b1)=18,iF(oR(m onth(date(1*(mid(b1,7,4)),1*(mid(b1,11,2)),1*(mid(b 1,13,2))))1*(mid(b1,11,2)),day(date(1*(mid(b1,7,4)) ,1*(mid(b1,11,2)),1*(mid(b1,13,2))))1*(mid(b1,13,2) )),"月日错误",""),iF(len(b1)=15,iF(oR(month(date(1*(mid(b1,7,2))+1900,1*(mid(b1,9,2)),1*(mid(b1,11,2))))1*(mid(b1, 9,2)),day(date(1*(mid(b1,7,2))+1900,1*(mid(b1,9,2)) ,1*(mid(b1,11,2))))1*(mid(b1,11,2))),"月日错误",""))),"身份证位数不对")&iF(and(len(b1)=18,1*mid(b1,7,4)>yeaR(today())-6) ,"年份错误","")公式首先对身份证长度进行判断，是否等于15位或18位，然后对18位和15位身份证分别判断月日是否合法，最后判断18位身份证的年份是否合法。

如何使用VBA实现Excel中的数据验证VBA是Visual Basic for Applications的缩写，是一种宏语言，用于在Microsoft Office应用程序中自动化任务和定制功能。

在Excel中，VBA可以帮助我们实现各种数据验证的功能，提高数据的准确性和完整性。

下面将介绍如何使用VBA实现Excel中的数据验证。

首先，打开Excel并选择要进行数据验证的单元格或区域。

然后按下Alt+F11，在Microsoft Visual Basic for Applications编辑器中打开一个新的模块。

在模块中，我们可以使用VBA代码来定义数据验证的规则。

例如，下面是一个简单的例子，用于限制单元格只能输入数字：```Sub DataValidation_example()Dim rng As Range'设置要进行数据验证的单元格范围Set rng = Range("A1:A10")'设置数据验证规则为只允许输入数字With rng.Validation.Delete.Add Type:=xlValidateWholeNumber, _AlertStyle:=xlValidAlertStop, _Operator:=xlBetween, _Formula1:="0", Formula2:="100".IgnoreBlank = True.InCellDropdown = TrueEnd WithEnd Sub```在上述代码中，我们使用了Range对象来指定要进行数据验证的单元格范围。

然后，通过使用Validation对象，我们可以添加各种数据验证规则。

在这个例子中，我们使用了xlValidateWholeNumber常量来限制单元格只能输入整数。

Formula1和Formula2参数分别指定了最小值和最大值。

EXCEL 身份证查对公式EXCEL 中你输入的公民身份号码正确吗?目录 :1、输入错误自动红色显示提示2、15 位身份证号码升位为18 位公民身份号码3、自动生成出诞辰期和性别4、中华人民共和国国家标准GB 11643-1999公民身份号码一、输入错误自动红色显示提示在公司报送的 EXCEL 电子表格中 ,常常碰到公民身份号码输入错误而给工作带来不用要的麻烦 ,降低了工作效率。

有没有方法在公民身份号码录入错误时进行自动提示呢?其实【中华人民共和国国家标准码第 18 位校验码已作了详尽的规定而已。

GB 11643-1999 】对公民身份号,不过我们在电算工作中极少用到可用以下方法在EXCEL 中录入公民身份号码错误时自动红字显示:选择需要录入公民身份号码的单元格 (如 A1), 选择菜单 :格式 /条件格式。

①点击条件 1 左侧的下拉箭头 ,选择公式 ,在右边的公式栏中输入以下公式 :=AND(LEN(D4)<>0,LEN(D4)<>15,LEN(D4)<>18)接着单击格式 ,点击字体 ,颜色选择红色 ,确立。

②在条件格式中点增添,点击条件 2 左侧的下拉箭头 ,选择公式 ,在右边的公式栏中输入以下公式:=MID("10X98765432",MOD(SUMPRODUCT(MID(D4,ROW($1:$ 17),1)*MOD(2^(18-ROW($1:$17)),11) ),11)+1,1)<>MID(D4,18,1)接着单击条件 2 中的格式 ,点击字体 ,颜色选择红色 ,确立 ,确立。

右键按住 A1 单元格右下角的小黑点 ,向下拖至所需要录入公民身份号码的全部单元格 ,松开右键 ,选择仅填补格式。

在接着的录入中 ,凡是输入不是 15 位或 18 位 ,或许输入 18 位错误的公民身份号码都会红色显示。

身份证重复显蓝色的公式:COUNTIF($D$4:$D$65536,D4)>1二、15 位身份证号码升位为18 位公民身份号码要使单元格中 A1 中的 15 位身份证号码升位为 18 位公民身份号码 , 只需在单元格 A2 中输入以下公式即可 :=REPLACE(A1,7,,19)&MID("10X98765432",MOD(SUMPRODUCT(MID(REPLACE(A1,7,,19),ROW($1:$17),1)*MOD(2^(18-ROW($1:$17)),11)),11)+1,1)或=REPLACE(A1,7,,19)&LOOKUP(MOD(SUMPRODUCT(MID(RE PLACE(A1,7,,19),ROW($1:$17),1)*MOD(2^(18-ROW($1:$17)),11)),11),{0,1,2,3,4,5,6,7,8,9,10},{1,0,"X",9,8,7,6,5,4,3,2})三、自动生成出诞辰期和性别假如单元格 A1 为身份证号码或公民身份号码 ,单元格 A2 为性别 , 单元格 A3 为出诞辰期 ,则操作以下 :在单元格 A2 中输入以下公式 ,则会自动生成性别:=IF(LEN(A1)=18,IF(INT(MID(A1,17,1)/2)=MID(A1,17,1)/2,"女 "," 男"),IF(LEN(A1)=15,IF(INT(MID(A1,15,1)/2)=MID(A1,15,1)/2,"女","男"),""))在单元格 A3 中输入以下公式 ,则会自动生成出诞辰期:=IF(LEN(A1)=18,DATE(MID(A1,7,4),MID(A1,11,2),MID(A1,13,2) ),IF(LEN(A1)=15,DATE(MID(A1,7,2),MID(A1,9,2),MID(A1,11,2)),""))[顶] EXCEL 中多条件乞降、计数的 4 种方法EXCEL 中多条件乞降、计数的方法大概可概括为 4 种:⒈自动挑选法⒉归并条件法⒊数组公式法⒋调用函数法先翻开上边的工作表,分别用这 4 种方法对同时知足“A2:A15地区为A,B2:B15 地区为 10,C2:C15 地区为Ⅰ”条件的 E2:E15 地区进行乞降、计数。

该函数用于验证身份证号码的有效性，包括长度检查、字符检查和校验位检查。

Function Peng(IDCard As String) As String' 定义变量Dim Weights As VariantDim CheckDigits As StringDim Sum As IntegerDim Remainder As IntegerDim CalculatedCheckDigit As StringDim i As Integer' 校验身份证号码的长度是否为18位If Len(IDCard) <> 18 ThenPeng = "错误：长度不正确" ' 返回错误信息Exit FunctionEnd If' 校验前17位是否全为数字For i = 1 To 17If Not IsNumeric(Mid(IDCard, i, 1)) ThenPeng = "错误：包含非数字字符" ' 如果有非数字字符，返回错误信息Exit FunctionEnd IfNext i' 校验最后一位字符是否为数字或字母XIf Not (Mid(IDCard, 18, 1) Like "[0-9X]") ThenPeng = "错误：最后一位字符无效" ' 返回错误信息Exit FunctionEnd If' 定义权重系数数组Weights = Array(7, 9, 10, 5, 8, 4, 2, 1, 6, 3, 7, 9, 10, 5, 8, 4, 2, 1)' 计算前17位数字的加权和Sum = 0For i = 1 To 17Sum = Sum + CInt(Mid(IDCard, i, 1)) * Weights(i - 1) ' 将每位数字乘以对应的权重Next i' 计算校验码Remainder = Sum Mod 11 ' 计算加权和的模11CheckDigits = "10X98765432" ' 定义校验码对应的字符串CalculatedCheckDigit = Mid(CheckDigits, Remainder + 1, 1) ' 获取计算出的校验位' 校验最后一位字符If UCase(Mid(IDCard, 18, 1)) = CalculatedCheckDigit ThenPeng = "正确" ' 如果校验位正确，返回“正确”ElsePeng = "错误" ' 如果校验位不正确，返回“错误”End IfEnd Function使用方法1.打开Excel:启动Microsoft Excel并打开你要使用的工作簿。

提取身份证的出生年月日的VBA方法一：使用VBA建立自定义函数在EXCEL中建立模块，输入下列代码，则在单元格中就可以使用自定义函数XB()，则就可以根据身份份号码得出性别，使用自定义函数CS()，则就可以根据身份份号码得出出生日期。

'根据身份证号码求出生日期与性别Function xb(Number)If Len(Number) = 15 Thense = Val(Right(Number, 1))Select Case seCase 0, 2, 4, 6, 8xb = "女"Case 1, 3, 5, 7, 9xb = "男"End SelectEnd IfIf Len(Number) = 18 Thense = Val(Mid(Number, 17, 1))Select Case seCase 0, 2, 4, 6, 8xb = "女"Case 1, 3, 5, 7, 9xb = "男"End SelectEnd IfEnd FunctionFunction cs(Number) As DateIf Len(Number) = 15 Then cs = "19" + Mid(Number, 7, 2) + "-" + Mid(Number, 9, 2) + "-" + Mid(Number, 11, 2)If Len(Number) = 18 Then cs = Mid(Number, 7, 4) + "-" + Mid(Number, 11, 2) + "-" + Mid(Number, 13, 2)End Function方法二：使用函数=IF(LEN(C2)=15,concatenate("19",MID(C2,7,2),"-",MID(C2,9,2),"-",MID(C 2,11,2)),if(len(c2)<>18,"身份证错误",concatenate(MID(C2,7,4),"-",MID(C2,11,2),"-",MID(C2,13,2)))或=IF(LEN(C2)=15,"19"&MID(C2,7,2)&"-"&MID(C2,9,2)&"-"&MID(C2,11,2),I F(LEN(C2)<>18,"身份证错误",MID(C2,7,4)&"-"&MID(C2,11,2)&"-"&MID(C2,13,2)))“性别”的方法与出生年月大同小异。

VBA使用EXCEL读取身份证阅读器获得证件信息本代码实现了在EXCEL中调用精伦身份证阅读器控件（可在网上百度精伦二次开发包，里面有）读取身份证信息，可根据实际情况修改相关代码,源文件可在EXCELhome网站搜索下载。

Private Declare Function InitComm Lib "Sdtapi.dll" (ByVal iPort As Integer) As IntegerPrivate Declare Function CloseComm Lib "Sdtapi.dll" () As IntegerPrivate Declare Function Authenticate Lib "Sdtapi.dll" () As IntegerPrivate Declare Function ReadBaseInfos Lib "Sdtapi.dll" (ByVal iname As String, ByVal isex As String, ByVal folk As String, ByVal birthday As String, ByVal code As String, ByVal addr As String, ByVal agency As String, ByVal startdate As String, ByVal enddate As String) As IntegerSub 启动_Click()'On Error Resume NextDim n, ret, nLenDim iname As String * 31Dim isex As String * 3Dim folk As String * 10Dim code As String * 19Dim addr As String * 71Dim birthday As String * 9Dim startdate As String * 9Dim enddate As String * 9Dim agency As String * 31Dim Msg As String * 300Dim Msg1 As String * 256Dim IINSNDN As String * 64Dim SAMID As String * 36Dim LenT As Integerret = InitComm("1001")If Err ThenErr.ClearMsgBox "端口错误", vbOKOnly, "提示" Exit SubEnd Ifret = Authenticate()If (ret) Then '' MsgBox "找到卡，正在读卡..."' ret = MsgBox("找到卡，正在读卡...", vbOKOnly + vbInformation, "提示")ret = ReadBaseInfos(iname, isex, folk, birthday, code, addr, agency, startdate, enddate)If (ret) Then' MsgBox "读卡成功！"'姓名Range("a1") = Trim(iname)'性别Range("a2") = Trim(isex)'民族Range("a3") = Trim(folk)'出生年Range("a4") = Left(Trim(birthday), 4) + "年" + Mid(Trim(birthday), 5, 2) + "月" + Mid(Trim(birthday), 7, 2) + "日"Range("b4") = Trim(birthday)'住址Range("a5") = Trim(addr)'公民身份号码Range("a6") = Trim(code)'签发机关Range("a7") = Trim(agency)'有效期限Range("a8") = Trim(enddate)If Trim(enddate) = "长期" ThenRange("b8") = Left(Trim(startdate), 4) + "." + Mid(Trim(startdate), 5, 2) + "." + Mid(Trim(startdate), 7, 2) + "长期"ElseRange("b8") = Left(Trim(startdate), 4) + "." + Mid(Trim(startdate), 5, 2) + "." + Mid(Trim(startdate), 7, 2) + "" + Left(Trim(enddate), 4) + "." + Mid(Trim(enddate), 5, 2) + "." + Mid(Trim(enddate), 7, 2)End If'显示照片'Pic.Picture = LoadPicture(App.Path + "\photo.bmp") ElseMsgBox "读卡不成功！请重新开始读卡。