|
每個人都有一張身份證，身份證號碼中不僅包含了出生年月，還有性別等信息……如果給你幾千條或幾萬條人員信息，你能快速的從身份證號碼中提取出生年月，性別等信息嗎？ 一、從身份證號碼中提取出生年月。 方法： 在目標單元格中輸入公式：=TEXT(MID(C3,7,8),'00-00-00')。 解讀： 1、MId函數(shù)的作用為：從文本字符傳中指定的起始位置返回指定長度的字符串。語法結構為：=Mid(字符串，起始位置，長度)。 2、Text函數(shù)的作用為：根據(jù)制定的數(shù)值格式將數(shù)值轉(zhuǎn)換為文本。 3、公式：=TEXT(MID(C3,7,8),'00-00-00')中，首先用Mid函數(shù)提取C3單元格中從第7位開始，長度為8的字符，然后用Text函數(shù)設置成格式為“00-00-00”（暨年月日）的文本。 二、從身份證號碼中提取性別。 方法： 在目標單元格中輸入公式：=IF(MOD(MID(C3,17,1),2),'男','女')。 解讀： 1、Mod函數(shù)的作用為：返回兩數(shù)相除的余數(shù)。語法結構為：=Mod(被除數(shù)，除數(shù))，例如=Mod(6,2)，返回值為0；=Mod(3,2)，返回值為1。 2、If函數(shù)的作用為：判斷條件是否成立，如果成立返回一個值，否則返回另外一個值。 3、公式：=IF(MOD(MID(C3,17,1),2),'男','女')中，首先利用Mid函數(shù)提取C3單元格中字符串的第17位，然后除以2，返回余數(shù)（暨Mod函數(shù)的作用，其返回的結構只有0和1兩種），最后用If函數(shù)判斷余數(shù)，如果為“1”，返回“男”，如果為“0”，返回“女”。 三、從身份證號碼中提取年齡。 方法： 在目標單元格中輸入公式：=DATEDIF(D3,TODAY(),'y')。 解讀： 1、Datedif函數(shù)是系統(tǒng)隱藏函數(shù)，其作用為按照指定的類型計算兩個日期之間的差值。語法結構為：=Datedif(開始日期，結束日期，統(tǒng)計方式)，常見的“統(tǒng)計方式”有：“Y”、“M”、“D”；分別為“年”、“月”、“日”。 2、Today()函數(shù)的作用為獲取當天的日期。 四、用身份證號碼計算退休年齡。 方法： 在目標單元格中輸入公式：=EDATE(D3,MOD(MID(C3,17,1),2)*120+600)。 解讀： 1、Edate函數(shù)的作用為返回指定日期之前或之后X月的日期。語法結構為：=Edate(指定日期，月份)。 2、公式：=EDATE(D3,MOD(MID(C3,17,1),2)*120+600)中，首先用判斷性別，如果為“男”性，則公式為：=Edate(D3，1*120+600)，暨從出生日期算起之后的720個月（暨60年）的日期；如果為“女性”，則公式為：=Edate(D3，0*120+600)，暨從出生日期算起之后的600個月（暨50年）的日期。 五、判斷身份證號是否重復。 1、常規(guī)操作（錯誤辦法）。 方法： 在目標單元格中輸入公式：=IF(COUNTIF($C$3:$C$9,C3)>1,'重復','')。 解讀： 1、Countif函數(shù)是單條件計數(shù)函數(shù)，其語法結構為：=Countif(條件范圍，條件)。 2、利用Countif函數(shù)統(tǒng)計出當前值在C3:C9范圍內(nèi)數(shù)量，緊接著和1進行比較，并用If函數(shù)來判斷，如果比較的結果成立，則返回“重復”，否則返回空值。 3、從單元格值中可以看出，兩個身份證號并不重復，但是返回的結果為“重復”，Why？因為在Excel中的數(shù)字的有效位數(shù)為15位，超過15位的全部按“0”處理。而上述的兩個號碼中只有最后兩位不同，故被Excel作為“0”處理，因此判斷的結果為“重復”。 2、正確做法。 方法： 在目標單元格中輸入公式：=IF(COUNTIF(C$3:C$9,C3&'*')>1,'重復','')。 解讀： 1、此公式和“常規(guī)操作”的公式比較，只是在C3后添加了&'*'，但能得到正確的結果，Why？ 2、“*”在Excel中被稱為通配符，起作用就是將當前的數(shù)字強制轉(zhuǎn)換為文本，然后進行比較，從而得到了正確的結果。 六、防止身份證號重復。 方法： 1、選定目標單元格。 2、【數(shù)據(jù)】-【數(shù)據(jù)驗證】，選擇【允許】中的【自定義】，并在【公式】中輸入：=COUNTIF(C$3:C$9,C3)=1。 3、完善提示信息并【確定】。 |
|
|
