excel中如果一列输入身份证号,另一列自动生成生日?

excel啊,很强大,最近才体验到,过去一直处于无知者阶段,因为自己也用不到,最近研究了一下,那么如何在一列中输入身份证号,另一列中自动生成生日呢?说起来简单,实际上不那么简单,现在就简单的讲一讲吧。

一、要用到的函数len,mid,text

说到你们头晕了吧,头晕的就省略,直接看以下函数,叫你先看没有叫你做什么哈

=IF(LEN(B1)=15,TEXT("19"&MID(B1,7,6),"0000-00-00"),IF(LEN(B1)=18,TEXT(MID(A1,7,8),"0000-00-00"),"身份证位数不合法"))

上面的话先留到

二、开始操作

(一)打开excel把一列设置为以文本存储数字,为什么这么讲(避免默认的科学计数法给你省略几位身份证号码)。选中你要输入身份证号码的那一列,然后右键-->设置单元格格式……-->数字-->文本,然后确认。本文中,选中的B1(为啥叫B1,看excel上面是ABC排列,下面123排行,B和1相交的那格就叫B1,说得够详细吧),如图。

设置为以文本存储数字

(二)在A1栏中输入第一步提到的公式

=IF(LEN(B1)=15,TEXT("19"&MID(B1,7,6),"0000-00-00"),IF(LEN(B1)=18,TEXT(MID(A1,7,8),"0000-00-00"),"身份证位数不合法"))

注意哈,前面有个 “=”,如果你用的不是B1,请根据你的实际情况,修改成实际的格子,例如A1

(三)完成了第一个,如果每行都有,不是每一行都要这样输入一个函数,还得改b1,b2,b3……这样一想,还不如手动来的快呢?这些其实都想到了,看吧,选中A1,拖动即可。(把鼠标放在A1的右下角,待鼠标变成了实心的十字架的时候,往下拉吧拉吧)如图

向下拖动单元格

三、几点思考

输出性别代码:=IF(B1="","",IF(LEN(B1)=18,IF(MOD(MID(B1,17,1)+2,2)=1,"男","女"),IF(MOD(RIGHT(B1,1)+2,2)=1,"男","女")))

输出年龄代码:=YEAR(TODAY())-MID(B1,7,4)
       验证身份证号合法与否
=IF(LOOKUP((LEFT(B1,1)*7+MID(B1,2,1)*9+MID(B1,3,1)*10+MID(B1,4,1)*5+MID(B1,5,1)*8+MID(B1,6,1)*4+MID(B1,7,1)*2+MID(B1,8,1)+MID(B1,9,1)*6+MID(B1,10,1)*3+MID(B1,11,1)*7+MID(B1,12,1)*9+MID(B1,13,1)*10+MID(B1,14,1)*5+MID(B1,15,1)*8+MID(B1,16,1)*4+MID(B1,17,1)*2)-ROUNDDOWN((LEFT(B1,1)*7+MID(B1,2,1)*9+MID(B1,3,1)*10+MID(B1,4,1)*5+MID(B1,5,1)*8+MID(B1,6,1)*4+MID(B1,7,1)*2+MID(B1,8,1)+MID(B1,9,1)*6+MID(B1,10,1)*3+MID(B1,11,1)*7+MID(B1,12,1)*9+MID(B1,13,1)*10+MID(B1,14,1)*5+MID(B1,15,1)*8+MID(B1,16,1)*4+MID(B1,17,1)*2)/11,0)*11,{0,1,2,3,4,5,6,7,8,9,10},{"1","0","x","9","8","7","6","5","4","3","2"})=RIGHT(B1,1),"号码合法","号码不合法")

未命名-3.jpg

可能你对以下的内容也感兴趣:

Excel表格的25招必学秘技 电子表格常用技巧大全

解决打开excel后打开双份的和excel/word图表存为矢量图的问题

excel拖动单元格使得数字递增(按照顺序增加)

excel如何设置固定表头及打印保持表头每页都有

火热榜

发表评论

    微笑 大笑 拽 大哭 奸笑 流汗 喷血 生气 囧 不爽 晕 示爱 卖萌 吃惊 迷离 爱你 吓死了 呵呵