周一：手機(jī)技巧； 周二：電腦技巧； 周三：技能知識(shí)； 周四：神器分享； 周五：精品推薦； 我國(guó)公民身份證為18位數(shù)字構(gòu)成，前6位為代表行政區(qū)域，從7到14為出生日期，15、16為派出所順序碼，第17位也就是倒數(shù)第二位代表性別，最后一位檢校碼，在我們使用Excel的時(shí)候，18位的身份證是我們經(jīng)常出現(xiàn)的需要輸入并編輯的一種數(shù)據(jù)，今天尋老師帶大家分享Excel中身份證的知識(shí)，這里面絕對(duì)有你不知道的！ 1 Excel如何完整顯示身份證號(hào) 最開(kāi)始使用Excel輸入身份證的時(shí)候，一般都會(huì)出現(xiàn)如下圖中C、D兩列的錯(cuò)誤，要么數(shù)字顯示為科學(xué)計(jì)數(shù)法、要么身份證后三位全部變?yōu)?。
這是為什么呢，其實(shí)這跟Excel的數(shù)據(jù)類(lèi)型有關(guān)，默認(rèn)情況下，Excel的數(shù)字是可以進(jìn)行計(jì)算的，因此，超過(guò)10位的數(shù)字Excel會(huì)將其自動(dòng)更新為科學(xué)計(jì)算法，而一旦超過(guò)15位，從第16開(kāi)始后面的數(shù)字全部變?yōu)?。
此時(shí)，我們只需把Excel這列的數(shù)據(jù)類(lèi)型改為文本即可。所謂文本，不管單元格里輸入什么信息都會(huì)完整顯示。需要注意的是，如果在輸入數(shù)據(jù)之前設(shè)置文本，再輸入數(shù)據(jù)會(huì)正確顯示，如果已經(jīng)輸入了數(shù)據(jù)，再設(shè)置文本，則需要再輸入數(shù)據(jù)。
2 根據(jù)身份證自動(dòng)顯示出生年月日.
=MID(F7,7,4)&'年'&MID(F7,11,2)&'月'&MID(F7,13,2)&'日'
函數(shù)解釋?zhuān)篎7是身份證號(hào)，函數(shù)在G7 單元格輸入，mid(F7,7,4)表示的是從F7這個(gè)單元格的第7位開(kāi)始取4位，& 這個(gè)符號(hào)是連接符，將數(shù)字和文本鏈接在一起，年月日在漢字，在函數(shù)里漢字一般都加雙引號(hào)，這個(gè)結(jié)果出來(lái)后，直接填充即可。
3 根據(jù)身份證自動(dòng)顯示年齡
=year(today())-year(G7)
函數(shù)解釋?zhuān)簩?duì)于年齡的求法有很多種，這里我們以一種最容易理解的方法計(jì)算：當(dāng)前年減去出生那年。Year()的意思是求括號(hào)里的日期的年齡，today()是顯示今天的日期，year(today())是先用today求出當(dāng)前天，再求當(dāng)前年。因?yàn)橐呀?jīng)在G7里求出出生日期，year(G7)就是顯示出生日期的年份。
4 根據(jù)身份證自動(dòng)判斷性別
=if(mod(mid(F7,17,1),2)=0,”女”,”男”)
函數(shù)解釋?zhuān)哼@是個(gè)三個(gè)基礎(chǔ)函數(shù)嵌套的復(fù)合函數(shù)。首先我們知道，身份證倒數(shù)第二位判斷性別，奇男，偶女，最外側(cè)if函數(shù)，if(邏輯條件,”女”,”男”)，如果邏輯條件成立顯示女，否則顯示男。什么邏輯條件呢？身份證倒數(shù)第二位是偶數(shù)，是偶數(shù)也就是身份證倒數(shù)第二位整除2 的余數(shù)為0 ，mid(F7,17,1)取身份證倒數(shù)第二位，mod(A,2) 求A整除2余數(shù)，如果A是偶數(shù)，則顯示0，A是奇數(shù)，則顯示1。
5 根據(jù)身份證自動(dòng)判定屬相
=CHOOSE(MOD(MID(F7,7,4)-1900,12)+1,'鼠','牛','虎','兔','龍','蛇','馬','羊','猴','雞','狗','豬')
函數(shù)解釋?zhuān)篊hoose函數(shù)根據(jù)參數(shù)索引顯示數(shù)值，因?yàn)閷傧酁?2個(gè)，所以用出生年減去減去1900，除以12的余數(shù)加上1，第一個(gè)值即對(duì)應(yīng)鼠，第二次值對(duì)應(yīng)牛，以此類(lèi)推。
6 根據(jù)身份證自動(dòng)判定星座
=VLOOKUP(--TEXT(MID(F7,11,4),'2015-00-00'),{0,'摩羯';42024,'水瓶';42054,'雙魚(yú)';42084,'白羊';42114,'金牛';42145,'雙子';42177,'巨蟹';42208,'獅子';42239,'處女';42270,'天秤';42301,'天蝎';42330,'射手';42360,'摩羯'},2)
函數(shù)解釋?zhuān)禾珡?fù)雜懶得解釋了，要用的話(huà)直接套用吧。
可以看到函數(shù)Excel的函數(shù)非常強(qiáng)大，這里面運(yùn)用的函數(shù)可以直接復(fù)制引用，不過(guò)一定要注意單元格的不同，比如這里F列是身份證，F(xiàn)7是出現(xiàn)的第一個(gè)身份證號(hào)，所以如果你在引用的時(shí)候，身份證在其他單元格，這里的F7就得換了。另外還需要注意的是，這里的所有標(biāo)點(diǎn)均需為英文狀態(tài)下的標(biāo)點(diǎn)。
原創(chuàng)干貨，好玩好學(xué)，我是尋老師，這是本周的技能知識(shí)，我們明天約神器分享。
聯(lián)系客服
