針對這個問題,我們首先得了解我國身份證號的組成結構,知道了身份證號不同位數所代表的的含義後,就可以針對性的設計出相對完善處理方案。
(1)目前我們現行使用的是18位的二代身份證號,其不同位數所代表意思如下圖所示
18位身份號代表信息
左數第1、2位表示省份代碼
左數第3、4位表示城市代碼
左數第5、6位表示區縣代碼
左數第7-14位表示出生年月代碼
左數第15-17位表示同一地區範圍內,對同年同月同日出生的人員編定的順序碼,其中第17位的奇數代表男性,偶數代表女性
左數第18位表示校驗碼(0-9,X)
雖然目前一代的15位身份證號碼已不再使用,但在一些情況下,部分表格和數據庫中還會留存著15位身份證號的信息。所以我們也需要了解下這類身份證號的組成結構
(2)15位身份證號不同位數所代表意思如下圖所示
15位身份號代表信息
左數第1、2位表示省份代碼
左數第3、4位表示城市代碼
左數第5、6位表示區縣代碼
左數第7-12位表示出生年月代碼
左數第13-15位表示同一地區範圍內,對同年同月同日出生的人員編定的順序碼,其中第15位的奇數代表男性,偶數代表女性
(3)18位身份證號和15位身份證號的區別
一:18位身份證號的出身年月代碼有8位,其中代表年份的有4位。15位身份證號的出身年月代碼有6位,其中代表年份有2位。
二:18位身份證號最後一位是校驗碼,15位身份證號沒有校驗碼
瞭解了身份證號的組成結構後,對於18位的身份證號,我們可以判斷第17位數的奇偶來識別是男性還是女性。對於15位的身份證號,可以判斷第15位數的奇偶來識別男性還是女性。
(4)我們先從18位身份證號開始設計函數,操作步驟如下圖所示
上圖函數公式:
=IF(MOD(MID(C2,17,1),2)=1,"男","女")
函數解析:MID(C2,17,1)
MID函數的作用是從原文本數據中截取指定位置,指定長度的字符串。
C2表示要截取數據的原文本
17表示從第17位開始截取
1表示截取長度為1的字符串
整個函數的作用是把18位身份證號中的第17位數提取出來
函數解析:
MOD(MID(C2,17,1),2)
MOD函數的作用是求餘
這裡MID(C2,17,1)是被除數
2表示除數
整個函數相當於(MID(C2,17,1))%2,數字奇偶數的判斷我們通常都是通過(被除數%2)這樣的思路來操作的。餘數為0視為偶,餘數為1視為奇。
函數解析:
=IF(MOD(MID(C2,17,1),2)=1,"男","女")
整個函數的思路就是
先用MID函數提取身份證號中能區分性別位數上的數字。
再利用MOD函數求餘判斷是奇數還是偶數。
最後利用IF條件函數判斷如果結果是奇數就是“男”,否則就是“女”。
(5)對於15位身份證號獲取性別的函數公式設計,如下圖所示
上圖函數公式:
=IF(MOD(MID(C3,15,1),2)=1,"男","女")
15位身份證號的性別獲取公式原理同18位身份證號,唯一區別是提取的位置一個是第17位,一個是第15位。這裡就不再做解析了。
(6)對於表格中既含有15位身份證號,又含有18位身份證號的情況。
函數公式設計思路如下圖所示
上圖函數公式:
=IF(LEN(C2)=18,IF(MOD(MID(C2,17,1),2)=1,"男","女"),IF(MOD(MID(C2,15,1),2)=1,"男","女"))
函數解析:LEN(C2)=18
LEN函數的作用是返回文本串的字符串數
這裡的作用是獲取C2單元格中的身份證號的位數並判斷是否等於18,其返回值為TRUE或是FALSE
函數解析:
IF(MOD(MID(C2,17,1),2)=1,"男","女")
18位身份證號的性別獲取公式
函數解析:
IF(MOD(MID(C2,15,1),2)=1,"男","女")
15位身份證號的性別獲取公式
函數解析:
=IF(LEN(C2)=18,IF(MOD(MID(C2,17,1),2)=1,"男","女"),IF(MOD(MID(C2,15,1),2)=1,"男","女"))
整個函數的設計思路是
利用IF函數先判斷LEN函數獲取的身份證號的位數是否是18位,如果是,則調用18位身份證號的性別獲取公式IF(MOD(MID(C2,17,1),2)=1,"男","女"),否則,調用15位身份證號的性別獲取公式。
(7)擴展:18位15位身份證號的性別獲取公式還有一個更簡潔的
=IF(MOD(MID(C2,(LEN(C2)=18)*2+15,1),2),"男","女")
這個公式對於Excel老鳥來說理解起來沒什麼難度,但對於剛接觸Excel函數的小夥伴來說還是有點挑戰的。這個函數整體的解析我就不做了,把其中的難點跟各位小夥伴分析一下。
函數解析:(LEN(C2)=18)*2+15
“(LEN(C2)=18)*2+15”這個公式,仔細觀察的小夥伴也不難發現其代表的是MID函數中第二個參數,用於指定截取數據的開始位置。
根據Excel中TRUE和FALSE乘以數字會對應變為1和0的特性,
如果LEN(C2)=18為TRUE,則(LEN(C2)=18)*2+15就變為“1*2+15”得出17的結果,代表18位身份證號中的性別獲取位置。
如果LEN(C2)=18為FALSE,則(LEN(C2)=18)*2+15就變為“0*2+15”得出15的結果,代表15位身份證號中的性別獲取位置。
知道了這個公式的含義,結合之前解釋的MID函數MOD函數以及IF函數,整個函數就很好理解了。
關注本頭條號並私信小編,回覆“教程”,即贈送Excel2010全套教學視頻!
往期Excel專題回顧
閱讀更多 CACA數據閒聊室 的文章