在用Excel錄入個(gè)人信息的時(shí)候，經(jīng)常會(huì)遇到身份證號碼輸錯(cuò)情況，有些身份證號碼位數(shù)輸多了，有些身份證號碼位數(shù)輸少，導(dǎo)致最后校對非常的麻煩。

Microsoft Office 2024 LTSC v17932.20162 中文專業(yè)增強(qiáng)版(附安裝教程+密鑰) 32位/64位

• 類型：辦公軟件
• 大?。?/span>16.7GB
• 語言：簡體中文
• 時(shí)間：2024-11-14

查看詳情

遇到這種情況，如果信息量少，直接一個(gè)一個(gè)的重新核對一遍即可；如果遇到大量的數(shù)據(jù)，處理起來就非常麻煩，重新核對，不僅浪費(fèi)大量時(shí)間，工作效率也很低。

小編就給大家分享兩種方法，在輸入身份證的時(shí)候如何驗(yàn)證身份證號碼是不是18位，用檢驗(yàn)的方式批量處理身份證號碼的輸入是否規(guī)范。

方法一：數(shù)據(jù)驗(yàn)證方式

選中要輸入身份證號碼的數(shù)據(jù)列，點(diǎn)擊【數(shù)據(jù)】再點(diǎn)擊【數(shù)據(jù)驗(yàn)證】在彈出框中分別設(shè)置如下數(shù)據(jù)：

設(shè)置好輸入身份證號碼的數(shù)據(jù)列之后，點(diǎn)擊這一列中任意單元格時(shí)，會(huì)有提示信息。

當(dāng)輸入身份證號碼時(shí)，如果輸入的位數(shù)大于18位或者小于18位時(shí)，會(huì)彈出框，提示數(shù)據(jù)輸入錯(cuò)誤，只有輸入位數(shù)等于18位時(shí)，才不會(huì)有彈框。

方法二：公式驗(yàn)證方式

選中要輸入身份證號碼的數(shù)據(jù)列，點(diǎn)擊【開始】再點(diǎn)擊【條件格式】再點(diǎn)擊【新建規(guī)則】然后點(diǎn)擊【使用公式確定要設(shè)置格式的單元格】在文本框中輸入公式“=LEN(A1)<>18”，然后再點(diǎn)擊【格式】設(shè)置文本樣式。

公式的參數(shù)說明：

• LEN:len是英文單詞leng的縮寫，意思是“長度”。
• A1：表示要設(shè)置的數(shù)據(jù)列。(如：要設(shè)置B列，這里的參數(shù)就是B1)
• 18:表示身份證的位數(shù)18位。(文本長度)

當(dāng)輸入身份證號碼時(shí)，如果輸入的位數(shù)大于18位或者小于18位時(shí)，單元格的內(nèi)容是紅色的(小編設(shè)置的文本樣式是紅色)，只有輸入位數(shù)等于18位時(shí)，才不會(huì)顯示紅色。

補(bǔ)充：

1、查找重復(fù)身份證號碼：

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

要點(diǎn)：

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

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

2、用出生年月來計(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ù)身份證號計(jì)算出生年月：

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

要點(diǎn)：

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

4、根據(jù)身份證號碼提取性別：

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

要點(diǎn)：

• 身份證號碼中的第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ù)身份證號計(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)過指定月份后的日期，也就是以出生年月為準(zhǔn)，男性要經(jīng)過720個(gè)月（60年），女性要經(jīng)過600個(gè)月（50年）就是退休年齡了

以上就是小編給大家分享的在Excel中使用條件格式檢測身份證號碼是否18位，自動(dòng)檢測是否合法，不用一個(gè)一個(gè)核對，大大提高工作效率，您學(xué)會(huì)了嗎？

最新評論