|
每個人都有身份證號碼。作為疾控人的你，知道從身份證號碼上可以提取多少信息嗎？那么打開你的excel，手把手的交給你如何批量提取有效信息。相關(guān)理論基礎(chǔ)詳見《如何10秒校對1萬個身份證號有無錯誤？》 民政部官網(wǎng)上有2020年12月中華人民共和國縣以上行政區(qū)劃代碼，上面的六位代碼就是我們身份證號的前六位，可以通過mid函數(shù)+vlookup函數(shù)+數(shù)組實現(xiàn)自動提取
=VLOOKUP(MID(B2,1,2)*1,{11,'北京市';12,'天津市';13,'河北省';14,'山西省';15,'內(nèi)蒙古自治區(qū)';21,'遼寧省';22,'吉林省';23,'黑龍江省';31,'上海市';32,'江蘇省';33,'浙江省';34,'安徽省';35,'福建省';36,'江西省';37,'山東省';41,'河南省';42,'湖北省';43,'湖南省';44,'廣東省';45,'廣西壯族自治區(qū)';46,'海南省';50,'重慶市';51,'四川省';52,'貴州省';53,'云南省';54,'西藏自治區(qū)';61,'陜西省';62,'甘肅省';63,'青海省';64,'寧夏回族自治區(qū)';65,'新疆維吾爾自治區(qū)';71,'臺灣省';81,'香港特別行政區(qū)';82,'澳門特別行政區(qū)'},2,0)
其中MID(B2,1,2)*1是因為MID(B2,1,2)提取的是文本值，乘1之后可以轉(zhuǎn)為數(shù)值。=vlookup(MID(B2,1,4)*1,{1301,'石家莊市';1302,'唐山市';1303,'秦皇島市';1304,'邯鄲市';1305,'邢臺市';1306,'保定市';1307,'張家口市';1308,'承德市';1309,'滄州市';1310,'廊坊市';1311,'衡水市';1401,'太原市';1402,'大同市';1403,'陽泉市';1404,'長治市';1405,'晉城市';1406,'朔州市';1407,'晉中市';1408,'運城市';1409,'忻州市';1410,'臨汾市';1411,'呂梁市';1501,'呼和浩特市';1502,'包頭市';1503,'烏海市';1504,'赤峰市';1505,'通遼市';1506,'鄂爾多斯市';1507,'呼倫貝爾市';1508,'巴彥淖爾市';1509,'烏蘭察布市';1522,'興安盟';1525,'錫林郭勒盟';1529,'阿拉善盟';2101,'沈陽市';2102,'大連市';2103,'鞍山市';2104,'撫順市';2105,'本溪市';2106,'丹東市';2107,'錦州市';2108,'營口市';2109,'阜新市';2110,'遼陽市';2111,'盤錦市';2112,'鐵嶺市';2113,'朝陽市';2114,'葫蘆島市';2201,'長春市';2202,'吉林市';2203,'四平市';2204,'遼源市';2205,'通化市';2206,'白山市';2207,'松原市';2208,'白城市';2224,'延邊朝鮮族自治州';2301,'哈爾濱市';2302,'齊齊哈爾市';2303,'雞西市';2304,'鶴崗市';2305,'雙鴨山市';2306,'大慶市';2307,'伊春市';2308,'佳木斯市';2309,'七臺河市';2310,'牡丹江市';2311,'黑河市';2312,'綏化市';2327,'大興安嶺地區(qū)';3201,'南京市';3202,'無錫市';3203,'徐州市';3204,'常州市';3205,'蘇州市';3206,'南通市';3207,'連云港市';3208,'淮安市';3209,'鹽城市';3210,'揚州市';3211,'鎮(zhèn)江市';3212,'泰州市';3213,'宿遷市';3301,'杭州市';3302,'寧波市';3303,'溫州市';3304,'嘉興市';3305,'湖州市';3306,'紹興市';3307,'金華市';3308,'衢州市';3309,'舟山市';3310,'臺州市';3311,'麗水市';3401,'合肥市';3402,'蕪湖市';3403,'蚌埠市';3404,'淮南市';3405,'馬鞍山市';3406,'淮北市';3407,'銅陵市';3408,'安慶市';3410,'黃山市';3411,'滁州市';3412,'阜陽市';3413,'宿州市';3415,'六安市';3416,'亳州市';3417,'池州市';3418,'宣城市';3501,'福州市';3502,'廈門市';3503,'莆田市';3504,'三明市';3505,'泉州市';3506,'漳州市';3507,'南平市';3508,'龍巖市';3509,'寧德市';3601,'南昌市';3602,'景德鎮(zhèn)市';3603,'萍鄉(xiāng)市';3604,'九江市';3605,'新余市';3606,'鷹潭市';3607,'贛州市';3608,'吉安市';3609,'宜春市';3610,'撫州市';3611,'上饒市';3701,'濟南市';3702,'青島市';3703,'淄博市';3704,'棗莊市';3705,'東營市';3706,'煙臺市';3707,'濰坊市';3708,'濟寧市';3709,'泰安市';3710,'威海市';3711,'日照市';3713,'臨沂市';3714,'德州市';3715,'聊城市';3716,'濱州市';3717,'菏澤市';4101,'鄭州市';4102,'開封市';4103,'洛陽市';4104,'平頂山市';4105,'安陽市';4106,'鶴壁市';4107,'新鄉(xiāng)市';4108,'焦作市';4109,'濮陽市';4110,'許昌市';4111,'漯河市';4112,'三門峽市';4113,'南陽市';4114,'商丘市';4115,'信陽市';4116,'周口市';4117,'駐馬店市';4201,'武漢市';4202,'黃石市';4203,'十堰市';4205,'宜昌市';4206,'襄陽市';4207,'鄂州市';4208,'荊門市';4209,'孝感市';4210,'荊州市';4211,'黃岡市';4212,'咸寧市';4213,'隨州市';4228,'恩施土家族苗族自治州';4301,'長沙市';4302,'株洲市';4303,'湘潭市';4304,'衡陽市';4305,'邵陽市';4306,'岳陽市';4307,'常德市';4308,'張家界市';4309,'益陽市';4310,'郴州市';4311,'永州市';4312,'懷化市';4313,'婁底市';4331,'湘西土家族苗族自治州';4401,'廣州市';4402,'韶關(guān)市';4403,'深圳市';4404,'珠海市';4405,'汕頭市';4406,'佛山市';4407,'江門市';4408,'湛江市';4409,'茂名市';4412,'肇慶市';4413,'惠州市';4414,'梅州市';4415,'汕尾市';4416,'河源市';4417,'陽江市';4418,'清遠市';4419,'東莞市';4420,'中山市';4451,'潮州市';4452,'揭陽市';4453,'云浮市';4501,'南寧市';4502,'柳州市';4503,'桂林市';4504,'梧州市';4505,'北海市';4506,'防城港市';4507,'欽州市';4508,'貴港市';4509,'玉林市';4510,'百色市';4511,'賀州市';4512,'河池市';4513,'來賓市';4514,'崇左市';4601,'?？谑?;4602,'三亞市';4603,'三沙市';4604,'儋州市';5101,'成都市';5103,'自貢市';5104,'攀枝花市';5105,'瀘州市';5106,'德陽市';5107,'綿陽市';5108,'廣元市';5109,'遂寧市';5110,'內(nèi)江市';5111,'樂山市';5113,'南充市';5114,'眉山市';5115,'宜賓市';5116,'廣安市';5117,'達州市';5118,'雅安市';5119,'巴中市';5120,'資陽市';5132,'阿壩藏族羌族自治州';5133,'甘孜藏族自治州';5134,'涼山彝族自治州';5201,'貴陽市';5202,'六盤水市';5203,'遵義市';5204,'安順市';5205,'畢節(jié)市';5206,'銅仁市';5223,'黔西南布依族苗族自治州';5226,'黔東南苗族侗族自治州';5227,'黔南布依族苗族自治州';5301,'昆明市';5303,'曲靖市';5304,'玉溪市';5305,'保山市';5306,'昭通市';5307,'麗江市';5308,'普洱市';5309,'臨滄市';5323,'楚雄彝族自治州';5325,'紅河哈尼族彝族自治州';5326,'文山壯族苗族自治州';5328,'西雙版納傣族自治州';5329,'大理白族自治州';5331,'德宏傣族景頗族自治州';5333,'怒江傈僳族自治州';5334,'迪慶藏族自治州';5401,'拉薩市';5402,'日喀則市';5403,'昌都市';5404,'林芝市';5405,'山南市';5406,'那曲市';5425,'阿里地區(qū)';6101,'西安市';6102,'銅川市';6103,'寶雞市';6104,'咸陽市';6105,'渭南市';6106,'延安市';6107,'漢中市';6108,'榆林市';6109,'安康市';6110,'商洛市';6201,'蘭州市';6202,'嘉峪關(guān)市';6203,'金昌市';6204,'白銀市';6205,'天水市';6206,'武威市';6207,'張掖市';6208,'平?jīng)鍪?;6209,'酒泉市';6210,'慶陽市';6211,'定西市';6212,'隴南市';6229,'臨夏回族自治州';6230,'甘南藏族自治州';6301,'西寧市';6302,'海東市';6322,'海北藏族自治州';6323,'黃南藏族自治州';6325,'海南藏族自治州';6326,'果洛藏族自治州';6327,'玉樹藏族自治州';6328,'海西蒙古族藏族自治州';6401,'銀川市';6402,'石嘴山市';6403,'吳忠市';6404,'固原市';6405,'中衛(wèi)市';6501,'烏魯木齊市';6502,'克拉瑪依市';6504,'吐魯番市';6505,'哈密市';6523,'昌吉回族自治州';6527,'博爾塔拉蒙古自治州';6528,'巴音郭楞蒙古自治州';6529,'阿克蘇地區(qū)';6530,'克孜勒蘇柯爾克孜自治州';6531,'喀什地區(qū)';6532,'和田地區(qū)';6540,'伊犁哈薩克自治州';6542,'塔城地區(qū)';6543,'阿勒泰地區(qū)'},2,0)
出生區(qū)縣查詢的話公式太長了，超過excel的允許，需要用到輔助列，
=VLOOKUP(MID(B2,1,6)*1,輔助列!$A$1:$B$2844,2,0)
身份證號第17位代表性別，奇數(shù)為男，偶數(shù)為女。利用if函數(shù)+mod函數(shù)+mid函數(shù)構(gòu)建符合函數(shù)=IF(MOD(MID(B2,17,1),2),'男','女')。
身份證號第17位代表性別，奇數(shù)為男，偶數(shù)為女。利用if函數(shù)+mod函數(shù)+mid函數(shù)構(gòu)建符合函數(shù)=TEXT(MID(B2,7,8),'0000-00-00')
=DATEDIF(TEXT(MID(B2,7,8),'00-00-00'),TODAY(),'y')DATEDIF函數(shù)用于計算兩個日期之間的間隔。=DATEDIF(開始日期,結(jié)束日期,指定要返回的類型),第三參數(shù)是Y，表示年，是M，就表示月，TODAY函數(shù)返回系統(tǒng)當前的日期。
=MID('鼠?；⑼谬埳唏R羊猴雞狗豬',MOD(TEXT((TEXT(MID(B2,7,8),'0000-00-00')),'[$-130000]e')-4,12)+1,1)其中 TEXT(D2,'[$-130000]e')的作用就是將日期轉(zhuǎn)換成陰歷年份，然后在十二屬相之間輪回。
=LOOKUP(TEXT(TEXT(MID(B3,7,8),'0000-00-00'),'mdd')*1,{0,'摩羯座';121,'水瓶座';220,'雙魚座';321,'白羊座';421,'金牛座';522,'雙子座';622,'巨蟹座';724,'獅子座';824,'處女座';924,'天秤座';1024,'天蝎座';1123,'射手座';1223,'摩羯座'})主要思路是先提取出生月日，然后用vlookup函數(shù)匹配。
計算退休時間（以男性60歲，女性55歲退休為例），在K2中輸入公式=EDATE(TEXT(MID(B2,7,8),'0!/00!/00'),MOD(MID(B2,15,3),2)*120+660)EDATE(start_date,months) 其中start_date表示起始日期的日期，months：表示start_date 之前或之后的月份數(shù)。男性要經(jīng)過720個月（60年），女性要經(jīng)過660個月（55年）就是退休年齡了。其基本思路是先用TEXT(MID(B2,7,8),'0!/00!/00')，計算出出生年月信息。再使用MOD(MID(B2,15,3),2)計算性別碼和2相除的余數(shù)，結(jié)果是1或是0。如果性別碼和2相除的余數(shù)是1，則為奇數(shù)，判定為男性，MOD(MID(B2,15,3),2)*120計算結(jié)果為1，部分就相當于1*120+660，結(jié)果是720，及720月，合60年。如果是女性，性別碼和2相除的余數(shù)是0，則是0*120+660，結(jié)果是660，合55年。
直接拿來用，所有的函數(shù)都已經(jīng)設(shè)置好了
|
