根據(jù)身份證號(hào)碼提取個(gè)人信息課件

根據(jù)身份證號(hào)碼

提取個(gè)人信息牟定縣職業(yè)高級(jí)中學(xué)計(jì)算機(jī)專(zhuān)業(yè)組陳彥宏公式函數(shù)－專(zhuān)題5一、新課導(dǎo)入學(xué)校要上報(bào)學(xué)生基本信息材料，這份材料是用Excel做的匯總表，其中必須輸入每位學(xué)生的姓名、性別、身份證號(hào)碼、出生年月、年齡等相關(guān)信息，但是在錄入容易出錯(cuò)，那么有沒(méi)有簡(jiǎn)單的辦法使身份證號(hào)碼、性別、出生年月、年齡等信息統(tǒng)一呢？二、邏輯思維訓(xùn)練那大家先來(lái)看一個(gè)小學(xué)生能做，你卻可能不會(huì)做的問(wèn)題——

？？？？？……用excel公式和函數(shù)來(lái)編輯公式，非常強(qiáng)調(diào)邏輯思維，我們需要經(jīng)常進(jìn)行訓(xùn)練這是一張正在運(yùn)動(dòng)的抽象的公交車(chē)圖，請(qǐng)你根據(jù)上圖判斷車(chē)是由A地開(kāi)往B地，還是由B地開(kāi)往A。AB邏輯思維訓(xùn)練題1二、邏輯思維訓(xùn)練這個(gè)問(wèn)題看似很難，無(wú)從下手，其實(shí)這個(gè)問(wèn)題的關(guān)鍵是你有沒(méi)有仔細(xì)觀察，能否找到問(wèn)題切入的關(guān)鍵點(diǎn)，別急，好好想想…….告訴你答案：要注意車(chē)門(mén)，我們看到的一邊沒(méi)車(chē)門(mén)，那車(chē)門(mén)就在看不到另一邊，公交車(chē)都是從右邊上車(chē)，因此可以斷定車(chē)是由B→A二、邏輯思維訓(xùn)練寄語(yǔ)：其實(shí)遇到什么問(wèn)題，關(guān)鍵要認(rèn)清事物的規(guī)律，善于思索，找到切入點(diǎn)，一定能找到解決問(wèn)題的方法。我們今天提出來(lái)的問(wèn)題，其實(shí)通過(guò)excel的公式和函數(shù)可以很輕松地解決這個(gè)問(wèn)題！三、預(yù)備知識(shí)分析身份證號(hào)碼身份證號(hào)碼與一個(gè)人的性別、出生年月、籍貫等信息緊密相連，無(wú)論是15位還是18位的身份證號(hào)碼，其中都保存了相關(guān)的個(gè)人信息。18位身份證號(hào)碼：第7-10位為出生年份(四位數(shù))，第11、12位為出生月份，第13、14位為出生日期，第17位代表性別，奇數(shù)為男，偶數(shù)為女。如某學(xué)生的身份證號(hào)碼（18位）那么表示1988年9月16日出生，性別為女。如果能想辦法從這些身份證號(hào)碼中將上述個(gè)人信息提取出來(lái)，不僅快速簡(jiǎn)便，而且不容易出錯(cuò)，核對(duì)時(shí)也只需要對(duì)身份證號(hào)碼進(jìn)行檢查，可大大提高工作效率。四、函數(shù)學(xué)習(xí)現(xiàn)在，我們需要使用MID、MOD、IF、DATE、year、today等函數(shù)從身份證號(hào)碼中提取個(gè)人信息。1、mid函數(shù)MID返回文本字符串中從指定位置開(kāi)始的特定數(shù)目的字符，該數(shù)目由用戶(hù)指定。如下圖A4單元格數(shù)值為，我們?cè)贐4單元格輸入公式“＝mid(A4,7,4)”，該公式是求出A4單元格從第7個(gè)字符開(kāi)始，提4個(gè)字符的結(jié)果，結(jié)果為1996。＝mid(A4,7,4)1996進(jìn)入實(shí)例四、函數(shù)學(xué)習(xí)2、MOD函數(shù)返回兩數(shù)相除的余數(shù)。如果B4單元格數(shù)值為7，在C4輸入公式“＝mod(B4,2)”，該公式的含義是求B4單元格數(shù)值除于2最終得到的余數(shù)，本實(shí)例為7除于2最終得到的余數(shù)，結(jié)果為1＝mod(B4,2)1進(jìn)入實(shí)例四、函數(shù)學(xué)習(xí)3、DATE函數(shù)返回代表特定日期的序列號(hào)。如果在輸入函數(shù)前，單元格格式為“常規(guī)”，則應(yīng)將其設(shè)為日期格式，使用哪種格式自己選定。語(yǔ)法DATE(year,month,day)如下圖A4單元格數(shù)值為1998，B4單元格數(shù)值為12，C4單元為數(shù)值8，在D4輸入公式“＝date(A4,B4,C4)”,運(yùn)行結(jié)果為日期：1998－12－8＝date(A4,B4,C4)1998－12－8進(jìn)入實(shí)例四、函數(shù)學(xué)習(xí)4、today函數(shù)返回當(dāng)前日期。如果在輸入函數(shù)前，單元格的格式為“常規(guī)”，則結(jié)果將設(shè)為日期格式，該函數(shù)無(wú)引用區(qū)域。如今天是2012年11月28日，在E2單元格輸入公式“＝today()”，則結(jié)果為今天的日期2012－11－285、year函數(shù)返回某日期對(duì)應(yīng)的年份。如下圖，在F4輸入公式“＝y(tǒng)ear(d4)”,則顯示結(jié)果為：1995＝today()2012－11－28＝y(tǒng)ear(d4)1995進(jìn)入實(shí)例五、學(xué)習(xí)案例1、提取出生年月日信息

由于上交報(bào)表時(shí)要填寫(xiě)出生年月日，因此這里我們只需根據(jù)身份證號(hào)碼的利用函數(shù)即可計(jì)算出，并將其設(shè)置為日期格式。在D4單元格中輸入公式“=date(MID(A4,7,4),mid(A4,11,2),MID(A4,13,2))，其中MID(A42,7,4）表示從A4單元格中字符串的第7位開(kāi)始提取4位數(shù)字。分別提取出年、月、日，再利用DATE函數(shù)轉(zhuǎn)換為日期格式。這里得到結(jié)果：1995-4-23=date(mid(A4,7,4),mid(A4,11,2),mid(A4,13,2))1995-4-23進(jìn)入實(shí)例五、學(xué)習(xí)案例2、提取性別信息

由于學(xué)生信息按學(xué)號(hào)有固定順序，如果一個(gè)一個(gè)手工輸入性別信息的話，既麻煩又容易出錯(cuò)。

本實(shí)例性別信息統(tǒng)一在C列填寫(xiě)，可以在C4單元格中輸入公式“=IF(MOD(MID(A4,17,1),2)=1,"男","女")”，其中：

MID(A4,17,1)：提取第17位的數(shù)字。

MOD(MID(A4,17,1),2)：用于得到給出數(shù)字除以指定數(shù)字后的余數(shù)，本例表示對(duì)提出來(lái)的數(shù)值除以2以后所得到的余數(shù)。

=IF(MOD(MID(A4,17,1),2)=1,“男”,“女”)”：如果除以2以后的余數(shù)是1，那么C４單元格顯示為“男”，否則顯示為“女”。回車(chē)確認(rèn)后，即可在C4單元格顯示正確的性別信息，接下來(lái)就是選中填充柄直接拖曳即可！=IF(MOD(MID(A4,17,1),2)=1,"男","女")男進(jìn)入實(shí)例五、學(xué)習(xí)案例3、計(jì)算年齡

要自動(dòng)計(jì)算出學(xué)生年齡，用現(xiàn)在的年減去出生的年即可得到年齡，本實(shí)例可在E4單元格輸入如下公式：“＝2012-year(d4)”,如果E4顯示不為數(shù)字，注意要把E4單元格設(shè)為常規(guī)。這樣計(jì)算出來(lái)的年齡不會(huì)隨著日期的變化而變更，如本例E4結(jié)果為17，明年還為17，能否讓年齡隨著日期的變化而自動(dòng)更新，這里我們可以使用today函數(shù)來(lái)實(shí)現(xiàn)本實(shí)例可在E4單元格輸入如下公式：“＝y(tǒng)ear(today())-year(d4)”,運(yùn)算的結(jié)果一樣，到2013年，E4結(jié)果則為18，年齡隨著日期的變化而自動(dòng)更新。＝y(tǒng)ear(today())-year(d4)17進(jìn)入實(shí)例六、小結(jié)通過(guò)以上函數(shù)的學(xué)習(xí)，我們能快速地提取出身份證中諸如性別，出生年月日、年齡等基本信息，以后當(dāng)我們填寫(xiě)這些信息時(shí)，只需要輸入身份證號(hào)碼，其他的項(xiàng)目不用自己輸入，利用公式復(fù)制后就能自動(dòng)計(jì)算出來(lái)，而且不會(huì)出錯(cuò)，這就是excel公式與函數(shù)的妙用。在求日期時(shí)可以設(shè)置不同的日期格式，在求年齡時(shí)要把其設(shè)置為常規(guī)或數(shù)值，否則出來(lái)的結(jié)果格式不對(duì)七、上機(jī)作業(yè)我們重新來(lái)做一遍這個(gè)實(shí)例作業(yè)，打開(kāi)“提取身份證信息-作業(yè)5.xls”,重新完成作業(yè)。最終做出的效果如下：開(kāi)始作業(yè)八、總結(jié)反思

今后不管遇到