和身份證有關(guān)的5個(gè)Excel函數(shù)公式
發(fā)布時(shí)間:2022-11-07 10:20 [ 我要自學(xué)網(wǎng)原創(chuàng) ] 發(fā)布人: 小劉2175

以下是一組和身份證有關(guān)的函數(shù)公式,如果你是財(cái)務(wù)人員或是一名HR,那就一起來(lái)看一下啦:

1、查找重復(fù)身份證號(hào)碼:

=IF(COUNTIF(A:A,A2&”*”)>1,”重復(fù)”,””)



要點(diǎn):

在COUNTIF函數(shù)的第二參數(shù)后加上一個(gè)星號(hào) &”*”。

COUNTIF函數(shù)在計(jì)算文本型數(shù)字時(shí),會(huì)默認(rèn)按數(shù)值型進(jìn)行處理,但是Excel中的數(shù)字精度只有15位,并且身份證號(hào)碼是18位,因此會(huì)把前15位相同的身份證全部識(shí)別為相同的內(nèi)容。在第二參數(shù)后加上一個(gè)星號(hào) &”*”,就是告訴Excel,要查找包含A2單元格內(nèi)容的文本,通過(guò)這樣變通的手段,COUTNIF函數(shù)就聽話啦。

2、用出生年月來(lái)計(jì)算年齡:

=DATEDIF(A4,TODAY(),”y”)



要點(diǎn):

DATEDIF函數(shù)用于計(jì)算兩個(gè)日期之間的間隔。用法是:
=DATEDIF(開始日期,結(jié)束日期,指定要返回的類型)
第三參數(shù)是Y,表示年,是M,就表示月。
TODAY函數(shù)返回系統(tǒng)當(dāng)前的日期。
DATEDIF函數(shù)是隱藏函數(shù),輸入時(shí)沒有屏幕提示,需要手工錄入哦。

3、根據(jù)身份證號(hào)計(jì)算出生年月:

=–TEXT(MID(A2,7,8),”0!/00!/00″)



要點(diǎn):

身份證號(hào)碼中的第8位到15位是出生年月信息。
首先使用MID函數(shù)從A2單元格的第7位開始,提取出8個(gè)字符,得到類似 19840727的結(jié)果。
再使用TEXT函數(shù)將這個(gè)字符串變成日期樣式的文本,得到類似1984/07/27的結(jié)果。
由于TEXT函數(shù)的結(jié)果是文本型的內(nèi)容,所以加上兩個(gè)小減號(hào) — 就是計(jì)算負(fù)數(shù)的負(fù)數(shù),通過(guò)四則運(yùn)算,變成真正的日期序列。
公式也可以使用=–TEXT(MID(A2,7,8),”0-00-00″),原理是一樣的。

4、根據(jù)身份證號(hào)碼提取性別:

=IF(MOD(MID(A2,15,3),2),”男”,”女”)



要點(diǎn):

身份證號(hào)碼中的第17位是性別信息,單數(shù)表示男,雙數(shù)表示女。
首先使用MID函數(shù)從A2單元格的第15位開始,提取出3個(gè)字符。
再使用MOD函數(shù)計(jì)算這個(gè)字符與2相除的余數(shù)。
最后使用IF函數(shù)判斷,如果余數(shù)大于0,返回男,如果余數(shù)是0,則返回女。
這里使用MID(A2,15,3),是為了兼容15位的舊身份證,這部分公式也可以使用MID(A2,17,1)。

5、根據(jù)身份證號(hào)計(jì)算退休時(shí)間:

=EDATE(TEXT(MID(A2,7,8),”0!/00!/00″),MOD(MID(A2,15,3),2)*120+600)



要點(diǎn):

先用TEXT(MID(A2,7,8),”0!/00!/00″),計(jì)算出出生年月信息。
再使用MOD(MID(A2,15,3),2)計(jì)算性別碼和2相除的余數(shù),結(jié)果是1或是0。
如果性別碼和2相除的余數(shù)是1,MOD(MID(A2,15,3),2)*120+600部分就相當(dāng)于1*120+600,結(jié)果是720。
否則就是0*120+600,結(jié)果是600。
EDATE函數(shù)返回一個(gè)日期經(jīng)過(guò)指定月份后的日期,也就是以出生年月為準(zhǔn),男性要經(jīng)過(guò)720個(gè)月(60年),女性要經(jīng)過(guò)600個(gè)月(50年)就是退休年齡了

Excel2019視頻教程
我要自學(xué)網(wǎng)商城 ¥80 元
進(jìn)入購(gòu)買
文章評(píng)論
0 條評(píng)論 按熱度排序 按時(shí)間排序 /350
添加表情
遵守中華人民共和國(guó)的各項(xiàng)道德法規(guī),
承擔(dān)因您的行為而導(dǎo)致的法律責(zé)任,
本站有權(quán)保留或刪除有爭(zhēng)議評(píng)論。
參與本評(píng)論即表明您已經(jīng)閱讀并接受
上述條款。
V
特惠充值
聯(lián)系客服
APP下載
官方微信
返回頂部
分類選擇:
電腦辦公 平面設(shè)計(jì) 室內(nèi)設(shè)計(jì) 室外設(shè)計(jì) 機(jī)械設(shè)計(jì) 工業(yè)自動(dòng)化 影視動(dòng)畫 程序開發(fā) 網(wǎng)頁(yè)設(shè)計(jì) 會(huì)計(jì)課程 興趣成長(zhǎng) AIGC