Excel如何自动提取身份证数据
身份证是一组有规律的号码，具体分割应该是：XXX XXX XXXX XX XX XX X X的格式。这里解释一下，第一组和第二组共六个数字代表的是户籍所在地代码，第三组数据是出生年份，第四组两位数代表出生月份，第五组两位数代表出生日期，倒数第二位则是性别判断数字(偶数为女性，奇数为男性)，在统计信息的时候，可能只登记了姓名和身份证号码，如何能够快速的将生日、性别、年龄一一提取计算出来，这是很多人都想知道的。而这些，都可以通过Excel公式来实现快速自动提取计算。首先新建一个Excel表格，右键单击要填写身份证号码的列(或者Ctrl+A整个表格)，选择“设置单元格格式”。
在弹出对话框内，点击“数字”中的“文本”确定。注意这个操作一定要在复制身份证数字前操作。
如表格中所示，在姓名和身份证后的同行单元格内，输入公式：“=MID(B2,7,4)&"年"&MID(B2,11,2)&"月"&MID(B2,13,2)&"日"”，注意，公式不包括中文半角双引号，手动输入时也要留意，所有符号全部为英文半角字符。另外，公式中“B2”为身份证所在单元格，具体到自己的表格中要根据情况变换。这里要解释一下，公式中的MID函数是Excel提供的一个“从字符串中提取部分字符”的函数命令，具体使用格式在EXCEL中输入MID后会出现提示。这个公式的大概意思是，提取B2单元格第7位后4个数字标记为年，第11位后2个数字标记为月，第13位后2个数字为日，由此组成出生日期。
判断男女则是根据身份证号的第17位数字决定，所以我们在后面的单元格中单击输入公式：“=IF(MID(B2,17,1)/2=TRUNC(MID(B2,17,1)/2),"女","男")”，大意为根据指定的条件(本例中即B2单元格第17位)来判断其“真”(TRUE)、“假”(FALSE)(注：男、女)，这里根据奇数、偶数来判断，所谓单数即男性，偶数为女性。
最后是年龄，一般使用公式：“=YEAR(NOW())-YEAR(C2)”(本文中C2单元格为出生日期)，但是这个公式第一要建立在之前我们提取出生日期的单元格数据上，而且这个公式也仅仅是以年份相减得出的年龄，说起来有点像我们常说的“虚岁”，这个在人事工作中是不科学的。最精确的公式应为：“=DATEDIF(TEXT(MID(B2,7,6+2*(LEN(B2)=18)),"#-00-00"),NOW(),"y")”，DATEDIF函数其实是Excel隐藏函数，在帮助和插入公式里面没有。公式主要作用是返回两个日期之间的年\月日间隔数，我们也正是借此来精确计算实际年龄。
如此一来，通过三组不同的函数，我们可以依靠着身份证信息快速提取出生日期和性别，并能精确计算出当事人年龄，为统计录入节约大量时间。
绛旓細瑕佷粠Excel涓彁鍙栬韩浠借瘉鍙风爜鐨勫悗鍑犱綅鏁板瓧锛岄鍏堟墦寮鍖呭惈韬唤璇佸彿鐮佺殑鏂囦欢銆傛壘鍒伴渶瑕佸鐞嗙殑鍗曞厓鏍间綅缃傛帴涓嬫潵锛岀偣鍑诲崟鍏冩牸涓婃柟鐨勫嚱鏁板浘鏍囷紝閫氬父浠モ渇x鈥濇爣璇嗐傝繖灏嗘墦寮涓涓嚱鏁伴夋嫨鐣岄潰銆傚湪寮瑰嚭鐨勫璇濇涓紝閫夋嫨鈥淢ID鈥濆嚱鏁板苟鐐瑰嚮纭銆傝繖涓嚱鏁扮敤浜庢彁鍙栧瓧绗︿覆涓殑鐗瑰畾閮ㄥ垎銆傚湪MID鍑芥暟鍙傛暟涓紝鈥渢ext鈥濆瓧娈靛簲...
绛旓細姝ラ涓锛屾墦寮鍚湁韬唤璇鍙风爜鍦板潃淇℃伅鐨Excel琛ㄦ牸锛屽亣璁惧湪A鍒椾腑銆傚湪B1鍗曞厓鏍间腑锛岃緭鍏ュ叕寮=MID(A1,1, Locate("鐪", A1)), 杩欏皢瀹氫綅鍒板湴鍧涓殑"鐪"瀛楃锛屼粠鑰鎻愬彇鍑虹渷鐨勪唬鐮侀儴鍒嗐傛帴涓嬫潵锛屽湪C1鍗曞厓鏍间腑锛屼娇鐢ㄥ叕寮=MID(A1, Locate("鐪", A1)+1, Locate("甯", A1) - Locate("鐪", A1))...
绛旓細1锛杞崲韬唤璇佸彿鐮佹牸寮 鍏堝皢韬唤璇佸彿瀹屾暣鍦拌緭鍏ュ埌Excel琛ㄦ牸涓紝杩欐椂榛樿涓衡滄暟瀛椻濇牸寮(鍗曞厓鏍煎唴鏄剧ず鐨勬槸绉戝璁版暟娉曠殑鏍煎紡)锛岄渶瑕佹洿鏀逛竴涓嬫暟瀛楁牸寮忋傞変腑璇ュ垪涓殑鎵鏈夎韩浠借瘉鍙峰悗锛屽彸鍑婚紶鏍囷紝閫夋嫨鈥滆缃崟鍏冩牸鏍煎紡鈥濄傚湪寮瑰嚭瀵硅瘽妗嗕腑鈥滄暟瀛椻濇爣绛惧唴鐨勨滃垎绫烩濊涓衡滄枃鏈濓紝鐒跺悗鐐瑰嚮纭畾銆2锛屸滄彁鍙栧嚭...
绛旓細2銆侀鍏堟墦寮Excel鐢靛瓙琛ㄦ牸锛岀劧鍚庡湪琛ㄦ牸涓緭鍏ョ敤浜庢紨绀烘墍闅忔満鐢熸垚鐨勪竴缁勮韩浠借瘉鍙锛屼富瑕侊紝婕旂ず鎵鐢ㄨ韩浠借瘉鍙峰潎涓洪殢鏈虹敓鎴愶紝涓嶅叿澶囦换浣曞疄闄呮剰涔夈3銆佺劧鍚庢垜浠夋嫨B2鍗曞厓鏍硷紝骞惰緭鍏モ=SUBSTITUTE(B2,RIGHT(B2,4),"***")鈥濓紝璇ュ鍚堝嚱鏁拌〃绀轰粠B2鍗曞厓鏍间腑鑾峰彇鏁版嵁锛屽苟灏嗗悗鍥涗綅鏇挎崲鎴愨*鈥濆彿銆4銆佽緭鍏ュ畬鎴愬悗...
绛旓細瑕佸湪Excel涓彁鍙栬韩浠借瘉鍙风殑鍓嶅叚浣嶆暟瀛楋紝鍙互浣跨敤LEFT鍑芥暟銆1銆侀鍏堬紝纭繚浣犵殑Excel鐗堟湰鏄2010鎴栦互涓婏紝鐒跺悗閫変腑鍖呭惈韬唤璇佸彿鐨勫崟鍏冩牸锛屾瘮濡侫2锛2銆佸湪鐩搁偦鐨勫崟鍏冩牸B2涓緭鍏ュ叕寮廯=LEFT(A2,6)`锛岀劧鍚庢寜Enter閿紱3銆佸皢榧犳爣鎸囬拡鏀惧湪B2鍗曞厓鏍肩殑鍙充笅瑙掞紝褰撴寚閽堝彉鎴愰粦鑹插崄瀛楁灦鏃讹紝鍚戜笅鎷栧姩浠ュ～鍏呭叕寮忓埌鍏朵粬鍚湁...
绛旓細棣栧厛锛屾墦寮鍖呭惈韬唤璇淇℃伅鐨Excel琛ㄦ牸銆傚叾娆★紝娉ㄦ剰韬唤璇佸彿鐮佷腑鐨勫勾榫鏁板瓧浣嶄簬绗竷浣嶅埌绗崄浣嶃傜劧鍚庯紝閫夋嫨涓涓┖鐧藉崟鍏冩牸鏉ヨ緭鍏ュ叕寮忥紝鍋囪璇ュ崟鍏冩牸涓篋2銆傛帴鐫锛屽湪D2鍗曞厓鏍间腑杈撳叆浠ヤ笅鍏紡锛歚=YEAR(TODAY())-MID(C2,7,4)`銆傜‘淇濅娇鐢ㄨ嫳鏂囩鍙疯緭鍏ユ暣涓叕寮忋傜劧鍚庯紝鎸変笅鍥炶溅閿紝D2鍗曞厓鏍煎皢鏄剧ず鎻愬彇鐨...
绛旓細鎻愬彇鍑虹敓鏃ユ湡銆 鎵撳紑Excel锛屾垜杩欓噷鍑嗗浜嗕竴浜涘熀纭鏁版嵁锛屽湪闇瑕佹彁鍙栫殑鍒椾腑绗竴涓崟鍏冩牸鏁版嵁鍏紡 =TEXT(MID( A2,7,8),"0-00-00") 娉ㄦ剰锛氬叾涓瑼2涓轰綘闇瑕鎻愬彇韬唤璇鍙风爜鐨勫崟鍏冩牸锛屼竴瀹氫笉瑕佽緭閿欎簡 杈撳叆瀹屾垚鍚庯紝鎸変笅閿洏鐨勫洖杞﹂敭锛岃繖鏄崟鍏冩牸涓氨浼氭樉绀哄嚭璇ヨ韩浠借瘉鐨勫嚭鐢熷勾鏈堛 灏嗛紶鏍囨斁缃...
绛旓細鍦ㄢ滃鍒跺埌鈥濇枃鏈涓緭鍏ヤ竴涓┖鐧藉崟鍏冩牸鐨勪綅缃紝濡傗淎10鈥濓紝鐒跺悗鐐瑰嚮鈥滅‘瀹氣濓紱Excel浼鑷姩鏌ユ壘姣忎釜浜哄悕瀵瑰簲鐨韬唤璇鍙风爜锛屽苟灏嗙粨鏋滃鍒跺埌鎸囧畾鐨勫崟鍏冩牸涓傞渶瑕佹敞鎰忕殑鏄紝濡傛灉浜哄悕鍒椾腑瀛樺湪閲嶅悕鐨勬儏鍐碉紝鍙兘浼氬鑷存煡鎵剧粨鏋滀笉鍑嗙‘銆傛鏃跺彲浠ュ皾璇曞湪绛涢夌獥鍙ｄ腑杈撳叆鏇村淇℃伅锛屽鍑虹敓鏃ユ湡銆佸湴鍧绛夛紝浠ョ缉灏忔煡鎵...
绛旓細鑰岃繖浜涳紝閮藉彲浠ラ氳繃Excel鍏紡鏉ュ疄鐜板揩閫鑷姩鎻愬彇璁＄畻銆傞鍏堟柊寤轰竴涓Excel琛ㄦ牸锛屽彸閿崟鍑昏濉啓韬唤璇佸彿鐮佺殑鍒楋紙鎴栬匔trl+A鏁翠釜琛ㄦ牸锛夛紝閫夋嫨璁剧疆鍗曞厓鏍兼牸寮忋傚湪寮瑰嚭瀵硅瘽妗嗗唴锛岀偣鍑绘暟瀛椾腑鐨勬枃鏈‘瀹氥傛敞鎰忚繖涓搷浣滀竴瀹氳鍦ㄥ鍒韬唤璇佹暟瀛鍓嶆搷浣溿傚琛ㄦ牸涓墍绀猴紝鍦ㄥ鍚嶅拰韬唤璇佸悗鐨勫悓琛屽崟鍏冩牸鍐咃紝杈撳叆鍏紡锛=MID...
绛旓細鎯宠鍦excel涓彁鍙18浣韬唤璇鍙风殑鎬у埆锛屽彲浠ユ寜鐓т互涓嬫楠よ繘琛屾搷浣滐細1銆佹墦寮Excel锛屽湪涓鍒椾腑浠庝笂鍒颁笅渚濇杈撳叆闇瑕佹彁鍙栨у埆鐨18浣嶈韩浠借瘉鍙枫傛妸韬唤璇侀兘鏀惧湪鍚屼竴鍒楁槸涓轰簡鏂逛究鐢ㄥ叕寮忔彁鍙栨у埆銆2銆佸湪绗竴琛岃韩浠借瘉鍚庤緭鍏ヤ互涓嬪叕寮忥細=IF(MOD(MID(A1,17,1),2),"鐢","濂")锛屽叾涓殑鈥淎1鈥濊〃绀洪渶瑕佹彁鍙栫殑...
