身份證號還能這麼玩?利用Excel把身份證號玩出花

2017-01-18 15:59  出處:PConline原創  作者:唐山居人   責任編輯:censi 

  【PConline 技巧】身份證號中蘊含著很多資訊,比方說大家都知道的生日,便是其中一個最經典的案例。其實除此之外,還有一些數據也是日常經常會用到的。將這些資訊快速提取出來,不但能讓工作更加高效,還能大大減少輸入出錯的機率。


把身份證號碼玩出花的Excel小技巧

  1. 提取生日

  函數:MID(從指定單元格中提取指定數量的字元)

  身份證號中第7位~14位數字就是每個人的出生日期,而MID函數的最終原理,就是將這8位數字提取出來,再透過對應的連字元連接起來。

  用法:MID(B2,7,4)&"-"&MID(B2,11,2)&"-"&MID(B2,13,2)

  * MID(B2,7,4)提取的是出生年份(從B2單元格中的第7位數字開始,向後提取4位)

  * MID(B2,11,2)提取的是出生月份(從B2單元格的第11位數字開始,向後提取2位)

  * MID(B2,13,2)提取的是出生日期(從B2單元格的第13位數字開始,向後提取2位)

  * &”-”&是將提取到的數字用“-”連接起來,而非參與計算,最終可以得到“出生年-出生月-出生日”這樣一種我們需要的格式


提取生日示例

  2. 提取性別

  函數:MOD(判斷兩個單元格作除法運算後的餘數)

  除了出生日期外,身份證號中的第17位數字是性別標識符。其中奇數為男性、偶數為女性。和生日提取不同,想要實現性別提取,僅靠單一函數是無法實現的,需要與IF函數搭配使用。

  用法:IF(MOD(MID(B2,15,3),2),"男","女")

  * MID(B2,15,3)提取的是身份證號中第15-17三位數字,即性別標識位

  * MOD(MID(B2,15,3),2)是將提取出來的數字與2取餘,這樣可計算出MID函數提取出來的數字到底是奇數還是偶數

  * IF(MOD(MID(B2,15,3),2),”男”,”女”))根據MOD函數得出的結果,奇數顯示“男”,偶數顯示“女”


提取性別示例

  3. 計算年齡

  函數:YEAR(提取日期數據中年份部分)

  年齡計算其實應該算身份證計算的衍生公式了,同樣無法藉助單一函數實現。一般操作前,先利用MID函數將生日提取出來,然後藉助YEAR函數得到目前年份與生日年份之間的差值即可。

  用法:YEAR(NOW())-YEAR(MID(B2,7,4))

  * NOW()提取目前日期

  * YEAR(NOW())將目前日期中年份資訊提取出來,比如”1970/01/01”提取出來後就是”1970”

  * YEAR(MID(B2,7,4)):首先藉助MID函數提取身份證號中的出生年份,然後再用YEAR函數提取目前年份,最後將兩個年份相減,結果即為該員工目前年齡


計算年齡

  注:最後再來個小貼士吧,有些網友可能會遇到這種情況,輸入18位身份證號時出現“x.xxxxxE+17”這樣的提示。這主要是因為單元格格式設定有問題,解決的方法是提前將單元格格式改為“文本”,然後再次輸入數值即可。


這種情況曾經讓很多新入手Excel的網友頭疼


其實解決的方法,只要一步就可以!簡單吧

相關閱讀:

Excel表格怎麼求差?Excel求差函公式使用方法

//pcedu.pconline.com.cn/875/8755800.html

Excel教程:Excel文件如何快速轉換為Word文檔?

//pcedu.pconline.com.cn/875/8755520.html

如何將excel文本轉換為數字?這些技巧請收下

//pcedu.pconline.com.cn/873/8736900.html

超簡單一招!用Excel十秒快速制作工資條

//pcedu.pconline.com.cn/817/8170320.html

 

excel如何篩選
BOSS刮目相看!讓你效率增10倍的“神鍵”
BOSS刮目相看!讓你效率增10倍的“神鍵”

很多朋友工作上都會用到Excel,不過Excel的一些使用技巧,並不人人都知道。掌握一些小技巧,甚至可以讓效率提升10倍!今天,一起來看看Excel那些能讓效率暴增的小技巧吧。...

唐山居人 2016-05-25 評論: 5 標簽: excel合併單元格   excel怎麼合併單元格  

你所不知道的!Excel 2013辦公小技巧三則
你所不知道的!Excel 2013辦公小技巧三則

相信不少人經常使用Office excel 2013軟體辦公,通暢在處理excel數據的時候都是非常麻煩的。但是如果你懂得一些excel技巧的話,會很讓你的辦公效率提高很多。...

唐山居人 2013-08-30 評論: 4 標簽: office2003破解版   office相容包  

Office免費?MAC用戶不必費勁去找破解版
Office免費?MAC用戶不必費勁去找破解版

微軟校園先鋒在近期搞一個免費贈送價值1999元的Office Mac 2011的促銷活動。只要以微軟校園先鋒註冊用戶的身份登錄Apple在線教育商店購買Apple商品,最高可省2300元,同時還有機會免...

xiaohu 2013-03-29 評論: 33 標簽: Mac   excel合併單元格  

微軟新規:Office 2013裝設授權可轉移
微軟新規:Office 2013裝設授權可轉移

自微軟正式發佈Office 2013後,就有不少用戶對微軟限制Office 2013的裝設、轉移授權問題表示不滿。近日,微軟在其官方博客表示,稱根據用戶的反饋,微軟決定修改Office 2013的零售許...

盛夏Veya 2013-03-07 評論: 19 標簽: excel合併單元格   excel如何篩選  

更新網站結構 微軟或將發佈iOS版Office
更新網站結構 微軟或將發佈iOS版Office

據外媒近日報道,微軟支援網站上包含了Excel for iPad、PowerPoint for iPad和Office Mobile for iPhone等各種非常明顯的標簽。這意味著微軟正在研發iOS平台的Office軟體。...

盛夏Veya 2012-12-11 評論: 23 標簽: excel合併單元格   excel如何篩選  

卡巴斯基:警惕Excel宏病毒感染電腦
卡巴斯基:警惕Excel宏病毒感染電腦

Excel文檔可以說是今天人們不可缺少的一種辦公應用,由於其豐富的功能和方便的數據處理能力,受到大家的青睞。而也正是因為這些出色的能力和廣泛的使用度,使其也成為了各種病毒、惡...

PConline 2012-10-26 評論: 0 標簽: excel合併單元格   xls文件怎麼打開  

加入HTML5支援 Excel網頁版詳細內容曝光
加入HTML5支援 Excel網頁版詳細內容曝光

微軟在官方博客中展露了大量關於網頁版Excel的消息和截圖,網頁版Excel依然充滿著Metro元素,添加了HTML5支援。微軟稱用戶可以在不同的設備上體驗一樣的Excel。...

Aimo 2012-07-24 評論: 6 標簽: excel合併單元格   xls文件怎麼打開