EXCEL我有两个表,一个有姓名,电话,一个有姓名,身份证号码,怎么让信息合并 两个Excel表,一个表有姓名和卡号,另一个表有姓名和身份证...
excel{vlookup}\u6211\u6709\u4e24\u4e2a\u8868\u683c\uff0c\u4e00\u6709\u59d3\u540d\uff0c\u8eab\u4efd\u8bc1\u53f7\u7801\uff1b\u4e8c\u53c8\u59d3\u540d\u7535\u8bdd\u53f7\u7801\uff0c\u5982\u4f55\u5b89\u8eab\u4efd\u8bc1\u53f7\u7801\u628a\u4e8c\u5bfc\u5165\u8868\u683c\u4e00\u5982\u8868\u4e00A\u5217\u662f\u59d3\u540d\uff0cB\u5217\u662f\u8eab\u4efd\u8bc1\u53f7\u7801\uff1b\u8868\u4e8cA\u5217\u662f\u59d3\u540d\uff0cB\u5217\u662f\u7535\u8bdd\u53f7\u7801\uff1b
\u5728\u8868\u4e00C1\u5217\u4e2d\u8f93\u5165\uff1a=vlookup(a1,\u8868\u4e8c!A:B,2,0) \u7136\u540e\u5c06\u516c\u5f0f\u590d\u5236\u5230C2 C3 C4......\u5373\u53ef
\u5982\u679c\u6ca1\u6709\u540c\u540d\u540c\u59d3\u7684\u5c31\u597d\u529e
sheet2\u7684J2\u8f93\u5165\u516c\u5f0f
=INDEX(sheet1!A:A,MATCH(B2,sheet1!B:B,))
\u4e0b\u62c9\u586b\u5145\u516c\u5f0f\u5373\u53ef
希望能够帮到你!
SHET2 的A2中输入:
=INDEX(SHEET1!A:A,MATCH(B2,SHEET1!B:B,)) 下拉
SHET2 的C2中输入:
=VLOOKUP(B2,SHEET1!B:C),2,) 下拉
直接把身份证拖过去不就行了
绛旓細浣犲ソ锛岃繖涓彲浠ョ敤鈥滈珮绾х瓫閫夆濆姛鑳借繘琛屻傚涓嬪浘鎵绀恒傚畠鍗冲彲浠ュ湪鍚涓涓宸ヤ綔钖勪腑杩涜鎿嶄綔锛屼篃鍙互閽堝涓嶅悓鐨勫伐浣滆杽銆傛搷浣滄槸绫讳技鐨勩
绛旓細1銆侀鍏堬紝鎵撳紑鐢佃剳锛屽湪鐢佃剳涓娇鐢Excel杞欢鎵撳紑涓や釜琛ㄦ牸銆琛1鍖呭惈涓ゅ垪濮撳悕鍜屽鑸嶇紪鍙枫2銆琛2鍖呭惈涓ゅ垪锛氬鍚嶃佸勾绾у拰瀹胯垗鍙枫傛垜浠湪琛1鐨勮〃2涓鎵剧浉鍚岀殑鍚嶅瓧锛屽苟鍦ㄨ〃1鍜岃〃2涓樉绀哄搴旂浉鍚屽悕瀛楃殑瀹胯垗缂栧彿銆3銆侀夋嫨C2鍗曞厓鏍硷紝鍗曞嚮FX鏌ユ壘VLOOKUP鍑芥暟銆4銆佽繖閲岀殑鏌ユ壘鍊兼槸鍚嶇О锛岃繖鏄袱涓〃鐨勭浉鍚屼俊鎭5...
绛旓細1銆侀鍏堝湪琛ㄤ竴涓夋嫨涓涓绌虹櫧鍗曞厓鏍煎苟鐐瑰嚮鈥渇x鈥濆浘鏍囥2銆佺劧鍚庨夋嫨IF鍑芥暟锛屽苟鍦ㄥ叾涓緭鍏ュ嚱鏁板弬鏁帮紝瀹屾暣鍏紡涓猴細=IF(COUNTIF(Sheet2!$A$1:$A$7,A1)>0,"鏈","娌℃湁")銆3銆佺偣鍑诲洖杞︾敓鎴愭暟鎹苟鍚戜笅濉厖鍏紡锛屽彲浠ョ湅鍒板搴旂殑琛ㄦ牸浜屼腑濮撳悕濡傛灉鏈夌浉鍚屽氨浼氭樉绀衡滄湁鈥濓紝濡傛灉娌℃湁鐩稿悓鍒欎細鏄剧ず鈥滄病鏈夆濄...
绛旓細鍋囪琛ㄧ殑A鍒椾负濮撳悕锛孊鍒椾负韬唤璇佸彿锛屼袱涓〃鍒嗗埆鍦⊿HEET1鍜孲HEET2锛岀敤鍏紡锛=IF(VLOOKUP(B2,IF({1,0},sheet2!B:B,sheet2!A:A),2,)=A2,"瀵","閿")涓嬫媺銆
绛旓細鍋囪琛1鍜琛2濮撳悕銆佷汉鍛樼紪鐮佸湪A鍜孊鍒楋紝鍦ㄨ〃2鐨凚2鐢ㄥ叕寮忥細=VLOOKUP(A2,琛1!A:B,2,0)鎴 =VLOOKUP(A2,琛1!A:B,2,)鎴 =INDEX(琛1!B:B,MATCH(A2,琛1!A:A,))涓嬫媺濉厖
绛旓細濡傛灉娌℃湁鍚屽悕鍚屽鐨勫氨濂藉姙 sheet2鐨凧2杈撳叆鍏紡 =INDEX(sheet1!A:A,MATCH(B2,sheet1!B:B,))涓嬫媺濉厖鍏紡鍗冲彲
绛旓細excel琛ㄦ牸鍖归厤涓や釜琛ㄦ牸涓浉鍚屾暟鎹殑鏂规硶鐒跺悗鍒囨崲鑷砈heet4宸ヤ綔琛ㄤ腑锛岄夋嫨濮撳悕鍖哄煙锛屽苟鎸夊洖杞﹂敭銆傛墦寮涓涓猠xcel琛紝闇瑕佸湪鍙︿竴涓〃涓尮閰嶅嚭鐩稿簲鍚屽鐨勭彮绾т俊鎭傛垜浠妸鍏夋爣瀹氫綅鍦ㄩ渶瑕佸睍绀烘暟鎹殑鍗曞厓鏍间腑锛屽涓嬪浘鎵绀恒傚湪鍗曞厓鏍间腑杈撳叆鈥=vl鈥濓紝鐒跺悗浼氳嚜鍔ㄦ彁绀哄嚭VLOOKUP鍑芥暟锛屽弻鍑昏摑鑹茬殑鍑芥暟閮ㄥ垎銆excel涓鎬庝箞...
绛旓細鍋囪濮撳悕鍜屽湴鍧鏄〃sheet1锛A鍒楁槸濮撳悕锛孊鍒楁槸鍦板潃锛孋鍒楁槸瑕佹坊鍔犵殑鎵嬫満鍙凤紱鍋囪濮撳悕鍜屾墜鏈哄彿鏄〃sheet2锛A鍒楁槸濮撳悕锛孊鍒楁槸鎵嬫満鍙枫傚湪sheet1鐨凜2鍗曞厓鏍奸噷杈撳叆=VLOOKUP(A2,Sheet2!A:B,2,FALSE)锛屼笅鎷夊~鍏呭嵆鍙
绛旓細鍋囧畾绗涓涓琛ㄦ牸鐨勫鍚嶅湪sheet1鐨凙鍒楋紝绗浜屼釜琛ㄦ牸鐨勫鍚嶅湪sheet2鐨凙鍒椼佸嚭鐢熷湴鍦╯heet2鐨凚鍒 閭d箞鍦╯heet1鐨凚1閲岄潰杈撳叆 =vlookup锛圓:A,sheet2!A:B,2,0锛夊洖杞﹀悗鍏紡涓嬫媺鍗冲彲
绛旓細浣跨敤鍑芥暟VLOOKUP,A琛ㄧ洿鎺ョ敤濮撳悕鍒楀紩鐢˙琛ㄧ殑韬唤璇佸彿鐮佸嵆鍙 甯屾湜鑳藉甯埌浣!