如何在EXCEL中批量将15位身份证号码变更为18位身份证号码?求解
首先需要知道身份证号中各个数字的含义,以18位身份证号主讲,
前边6个数字,每2个数字一组,分别是省(自治区、直辖市)、市(盟、自治州)、县(县级市、区)的代码,
接下来的8位数字(15位身份证是6位数字)是出生年月日(15位身份证号的年是两位数,省略了19),比如1975年6月5日出生的,这数字就是19750605(15位身份证中就是750605),
再往后的3个数是顺序码,这3个数的最后一个数,奇数为男,偶数为女。
最后1位是效验码(15位身份证号没有,就是为了防止身份证重号,而增设的)
总结:15位身份证号与18位身份证号的区别:
区别一:年份少个“19”,
区别二:最后少一个验证码。加“19”很容易,关键就在最后一个效验码的计算。
效验码是根据ISO 7604:1983.MOD11-2计算的,具体的计算规则看下图
至此可以写出身份证号升级公式了=REPLACE(A2,7,,19)&MID("10X98765432",MOD(SUM(MID(REPLACE(A2,7,,19),ROW($1:$17),1)*2^(18-ROW($1:$17))),11)+1,1)
上边公式中没有直接引用“加权因子”,而是使用了“2^(18-ROW($1:$17))”,用这个去乘身份证的每一个数,再求余数,结果是一样的,自己可以验证下。
18位身份证最后一位是校验位, 不那么简单.
15位的老身份证号码插入年份即可升到17位:
例如 B2 单元格中是这个17位的号码:
=IF(LEN(A2)=15,MID(A2,1,6)&"19"&MID(A2,7,9))
那么在 C2 中输入数组公式(公式输入后须同时按下 Ctrl+Shift+Enter 组合键方可完成):
=B2&LOOKUP(MOD(SUM(MID(B2,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})
即可得到18位(上述公式为函数大师 阵军 的作品).
参考资料:
公民身份号码是特征组合码,由十七位数字本体码和一位校验码组成。排列顺序从左至右依次为:六位数字地址码,八位数字出生日期码,三位数字顺序码和一位数字校验码。
校验方法:
(1)十七位数字本体码加权求和公式
S = Sum(Ai * Wi), i = 0, … , 16 ,先对前17位数字的权求和
Ai:表示第i位置上的身份证号码数字值
Wi:表示第i位置上的加权因子
Wi: 7 9 10 5 8 4 2 1 6 3 7 9 10 5 8 4 2
(2)计算模
Y = mod(S, 11)
(3)通过模得到对应的校验码
Y: 0 1 2 3 4 5 6 7 8 9 10
校验码: 1 0 X 9 8 7 6 5 4 3 2
例如 B2 单元格中是这个17位的号码:
=IF(LEN(A2)=15,MID(A2,1,6)&19&MID(A2,7,9))
那么在 C2 中输入数组公式(公式输入后须同时按下 Ctrl+Shift+Enter 组合键方可完成):
=B2&LOOKUP(MOD(SUM(MID(B2,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})
即可得到18位(上述公式为函数大师 阵军 的作品).
参考资料:公民身份号码是特征组合码,由十七位数字本体码和一位校验码组成。排列顺序从左至右依次为:六位数字地址码,八位数字出生日期码,三位数字顺序码和一位数字校验码。
校验方法:(1)十七位数字本体码加权求和公式
S = Sum(Ai * Wi), i = 0, … , 16 ,先对前17位数字的权求和
Ai:表示第i位置上的身份证号码数字值
Wi:表示第i位置上的加权因子
Wi: 7 9 10 5 8 4 2 1 6 3 7 9 10 5 8 4 2
(2)计算模
Y = mod(S, 11)(3)通过模得到对应的校验码
Excel作业,哪个帮帮忙~~~``
1>简述Excel中文件,工作簿,工作表,单元格之间的关系. 2>进行求和计算最简便的方法是什么? 3>Excel对单元格引用时默认采用的是相对引用还是绝对引用?两者有何区别?在行,列坐标的表示方法上两者有何差别?"Sheet3! $A$3:D$8 "表示什么意思? 展开 我来答 ...
excel中的GROUPBY函数怎么使用?
通过新建分析步骤,轻松进行多条件分类、求和、排序和筛选。对比GROUPBY,九数云的界面友好且功能全面,对于大量数据处理,无疑更具优势。总的来说,GROUPBY是Excel的新力量,适合基本的分类汇总需求,但对于大数据场景,九数云等BI工具更为适合。掌握GROUPBY的使用技巧,你将能在数据分析中游刃有余。
怎样操作可以使Excel表格中有相同的内容的行靠在一起?
1、首先选中你所要编辑的单元格。2、然后点击菜单栏中的“数据”。3、其次点击“筛选”。4、然后在文本编辑栏中点击旁边的小三角符号。5、其次点击“文本筛选”。6、然后点击“包涵”选项。7、在文本框内输入想要筛选的相同内容。(这里以学生姓名张三为例)。8、然后点击“确定”。9、这样就得到了...
在excel中绝对引用的快捷键是什么?
在Excel中,绝对引用的快捷键是F4键。以下是 在Excel中使用绝对引用时,通常涉及单元格地址的引用方式。绝对引用是指无论复制公式到何处,引用的单元格地址始终保持不变。当你在公式中使用绝对引用时,可以通过按下F4键来实现这一功能。当你选中要引用的单元格后,输入一个引用地址,并在地址的列标签或...
如何在Excel中用公式统计单列不重复记录数
如何在Excel中用公式统计单列不重复记录数 假设待统计的资料为A1:A10,则不重复的个数为: =SUMPRODUCT(1\/COUNTIF(A1:A10,A1:A10))比如A2:A9,(注意不能有空单元格) =SUMPRODUCT(1\/COUNTIF(A2:A9,A2:A9))如何在Excel中删除重复记录或建立不重复记录的列表 绍 如 何 创 建 不 重 ...
如何设置excel中的单元格才能显示成图片中所示?
1、点击插入——图片,插入图片到excel中。2、调整图片的大小,使其填满一个单元格。3、在图片上右键——大小和属性。在属性命令组勾选——大小和位置随单元格而变。这样在改变单元格宽度和高度时,图片也跟着变化,右键将该单元格所在的行隐藏,图片也能被隐藏。
在EXCEL中,以固定间隔显示时间用什么函数,请列出格式和例子,谢谢._百度...
可以用序列填充,也可以用公式。补充:在第一个单元格输入 8:30 第二个单元格输入 10:00 将两个单元格一起选中,下拉填充 或者用函数 =time(8,30,0)+90\/60\/24*(row(a1)-1)下拉填充 然后,将单元格格式设置为时间格式即可。
如何用电脑制作表格 初学
还可以在隔壁的【样式】中选择边框的样式,这样就得到一个简单的表格;5、表头可以选中一行之后,点击上方菜单栏的快捷键选择【合并后居中】按钮,就可以将这一行合并,还可以选中一列同样操作后就可以合并为一列;6、Excel的公式比较多,简单的公式可以直接在菜单栏的【公式】标签页中选择,只需要选中...
如何在Excel2013中数据选项卡下添加记录单命令
工具:Excel2013 步骤:打开Excel2013,点击Excel表格左上角的‘文件’按钮;在左侧菜单栏中找到并打开‘选项’对话框;在Excel选项对话框中,左侧边栏调整到‘自定义功能区’,在‘数据’选项卡下新建组;为新建组重命名,比如此处就将新建组命名为‘记录单’;选中‘记录单组’,将选择命令位置调整为‘...
在Excel中如何将学生的姓名和理论课成绩用簇状柱形图表示出来?
确保姓名在最前一行或最左一列。选中这些数据,插入,图表,柱形图,完成。 因为图形的主要作用是用来直观展示的,同一数据指标在一个图中几种不同的展示更让人觉得是不同指标; 如果是不同指标,只需要先插入 堆积柱形图,然后右击你想要簇状柱形图的指标,选择“更改系列图表类型”,选择簇状柱形图,然后调整即可。 举报...
方柴青尔: 把身份证号码列设置为“文本”格式.
南郑县19440324556: 在excel表格里面,有一列身份证号,里面有18位的 也有15位的 怎么把15位的查询出来 - ?
方柴青尔: 如果你的身份证号在A列,那么在B列输入公式 =if(len(a1)=15,a1,"") 公式下拉
南郑县19440324556: 在EXCEL表中有以下几项姓名、性别、身份证号,身份证号为15位或18们,请问要怎么样才能把15位或者18位身份证号的记录筛选出来呢? - ?
方柴青尔: 直接用数据筛选可以的~~按我的方法做: 选中身份证号所在的那一列~点击筛选~然后有个“等于”~~在等于的方框中输入15个“?”号(即???????????????)然后点击确定~~马上就可以筛选出来15位身份证号码~~~ 同理~要筛选18位的输入18个“?”即可~~ 以上方法叫做运用通配符查找法~~
南郑县19440324556: EXCEL表的单元格中怎样将18位身份证号码缩成15位的. - ?
方柴青尔: 假设数据在A列从A2开始,则在B2输入:=if(len(A2)=15,A2,left(left(A2,6)&right(A2,10),15))
南郑县19440324556: excel 如何把身份证号筛选出来,由于数据量比较大,有15位的、也有18位的最主要的是有些身份证号码错误?
方柴青尔: 设身份证号在a列,在b2输入: =if(or(len(a2)=15,len(a2)=18),"","该行对应的身份证号码错误") 然后下拉. 之后对b列进行排序或筛选,删除错误的行数据就好了.
南郑县19440324556: 如何在Excel表格中完整显示出身份证号码的操作方法 - ?
方柴青尔: 步骤如下: 一、打开需要使用的表格.二、在身份证号码直接输入身份号码,就出现以下图的情况. 三、是在前面加一个撇号,如图.操作之后这个撇号会会消失,直接显示文本数值.但这种方法每次都需要先添加撇号,对于需要输入大...
南郑县19440324556: excel中怎样把身份证号码 - ?
方柴青尔: Excel默认对超出11位的数字以科学计数法表示,对于15位以上的数字,超出部分以0计. 1、在Excel表格中完整显示出身份证号码的操作方法是将单元格设置成文本格式,选中单元格,点击文本,再输入身份证号码即可. 2、选择数字格式为“文本” 输入身份证号,并将列宽调宽让其完整显示出来.
南郑县19440324556: 在excel中怎么把身份证号码分组 - ?
方柴青尔: 直接用排列吧,最低级的分组功能
南郑县19440324556: EXCEL 如何把身份证号的生日月份提取出来,要15位和18位都能用的 - ?
方柴青尔: 提取身份证上的出生月份公式如下,假如身份证号在A列 结果为文本型的 =MID(A1,9+(LEN(A1)=18)*2,2) =MID(A1,11-(LEN(A1)=15)*2,2) 结果为数值型的 =--MID(A1,9+(LEN(A1)=18)*2,2) =--MID(A1,11-(LEN(A1)=15)*2,2)
南郑县19440324556: 电子表格怎么打15位以上的数字我只能打15位的数字,想输入一个新 ?
方柴青尔: EXCEL中只能用文本方式输入18位身份证的号码,最快捷的办法是在输入的身份证号码前加一个'号(英文半角下),EXCEL会自动识别为文本.