1. excel鎬庝箞闅愯棌鍏寮忕殑閿欒鍊
1銆佹瘮濡傦紝鏍規嵁琛2鐨勫伐鍙鳳紝鎶婂瑰簲鍐呭規彁鍙栧埌琛3涓錛屽湪琛3鐨凚3杈撳叆鍏寮=VLOOKUP$A2錛孲heet2錛$A$2錛$C$5001錛孋OLUMN)錛0)錛岀劧鍚庡彸鎷変笅鎷夊嶅埗鍏寮忋
2銆佸叕寮忚繑鍥炵粨鏋滄湁寰堝氶敊璇鍊#N/A錛岃繖鏄鍥犱負鍘熷嬫暟鎹娌℃湁瀵瑰簲宸ュ彿鐨勫師鏁呫
3銆佸湪涓嶄慨鏀瑰叕寮忕殑鍩虹涓婏紝鎶婇敊璇鍊奸殣鈃忚搗鏉ワ紝鍙浠ュ埄鐢ㄦ潯浠舵牸寮忚劇疆錛岄変腑鍏寮忕粨鏋滄墍鍦ㄥ尯鍩燂紝鍦ㄥ姛鑳藉尯鐐瑰嚮鏍煎紡--鏉′歡鏍煎紡--鏂板緩瑙勫垯銆
4銆侀夋嫨浣跨敤鍏寮忚劇疆鏍煎紡錛岃緭鍏=ISNAB2)錛岀偣鍑繪牸寮忥紝鎶婂瓧浣撻滆壊璁劇疆鎴愮櫧鑹詫紝鐐瑰嚮紜瀹氥
5銆佹渶鍚庡叕寮忕粨鏋滀粛鐒跺瓨鍦錛屽彧鏄鎶婇敊璇鍊肩殑瀛椾綋棰滆壊璁劇疆鎴愮櫧鑹查殣鈃忎簡璧鋒潵銆
2. Excel闅愯棌淇℃伅鐨勬柟娉曟湁鍝浜涳紵
鎴戜滑鍦ㄤ嬌鐢‥xcel琛ㄦ牸榪涜屾暣鐞嗘暟鎹鐨勬椂鍊欙紝閫氬父浼氭湁寰堝氱殑鏁版嵁鏁板兼槸涓嶉渶瑕佹樉紺哄嚭鏉ョ殑錛屼絾鏄鍒犻櫎涔熶笉琛岋紝閭ｄ箞搴旇ユ庝箞澶勭悊鍛錛熷叾瀹炶繖鏃跺欏氨鍙浠ヤ嬌鐢ㄩ殣鈃忓姛鑳借繘琛屽皢鏁版嵁榪涜岄殣鈃忚搗鏉ヤ簡錛佽屽湪Excel涓闅愯棌鐨勬柟娉曟妧宸ч兘鏈夊摢浜涘憿錛熶綘鎯寵佺煡閬撶殑璇濓紝浣犱笉濡ㄧ戶緇寰涓嬫祻瑙堝唴瀹瑰惂錛佷笅闈㈠氨鍋氫簡璇︾粏鐨勫垎鐐硅存槑錛屼竴璧鋒潵鐪嬬湅~Excel闅愯棌淇℃伅鐨勬柟娉曞垎浜錛01.Excel闅愯棌鑹插甫鎻愮ず濡傛灉浣犱笉灝忓績闅愯棌浜嗗姛鑳藉尯鍩熸庝箞鍔?榪欓噷鏈変笁縐嶈В鍐蟲柟娉曘1.鎸夊揩鎹烽敭Ctrl+F1;2.鐢ㄩ紶鏍囧崟鍑葷晫闈㈠彸涓婅掔殑銆愬姛鑳藉尯鏄劇ず閫夐」銆3.鐢ㄩ紶鏍囧弻鍑誨姛鑳藉尯涓鐨勪換鎰忛夐」鍗02.闅愯棌Excel紿楀彛濡傛灉浣犻渶瑕佸湪宸ヤ綔涓闅愯棌褰撳墠Excel紿楀彛錛屼綘鍙浠ュ湪鈥滆嗗浘鈥濋夐」鍗′腑闅愯棌瀹冦傚傛灉鍍忎笅闈㈣繖鏍風殑紿楀彛琚闅愯棌錛屼笉瑕佹儕鎱岋紝鎵撳紑瑙嗗浘閫夐」鍗″苟鍙栨秷銆03.闅愯棌褰撳墠宸ヤ綔琛ㄥ唴瀹濡傛灉鎮ㄤ復鏃墮渶瑕侀殣鈃忓綋鍓嶅伐浣滆〃鐨勫唴瀹癸紝璇ユ庝箞鍔?鐐瑰嚮褰撳墠宸ヤ綔琛ㄥ乏涓婅掔殑鈥滃掍笁瑙掆濇寜閽錛岀劧鍚庢寜蹇鎹烽敭鈥淐trl+9鈥;濡傛灉闇瑕侀噸鏂版樉紺猴紝璇鋒寜Ctrl+Shift+9銆04.闅愯棌宸ヤ綔琛ㄧ殑甯哥敤鏂規硶闅愯棌宸ヤ綔琛ㄦ渶甯哥敤鐨勬柟娉曟槸鐢ㄩ紶鏍囬変腑瑕侀殣鈃忕殑宸ヤ綔琛錛屽崟鍑婚紶鏍囧彸閿錛岃劇疆闅愯棌;褰撻渶瑕侀殣鈃忓氫釜琛ㄦ牸鏃訛紝鎸変綇閿鐩樹笂鐨勨淪hift鈥濋敭錛屼緷嬈￠夋嫨涓涓宸ヤ綔琛ㄨ繘琛岄殣鈃忋05.娣卞害闅愯棌宸ヤ綔琛ㄧ殑鏂規硶宸ヤ綔琛ㄩ珮綰ч殣鈃忔柟娉:鐒跺悗鎸堿lt+F11鎵撳紑VBA緙栬緫紿楀彛銆傞夋嫨瑕侀殣鈃忕殑宸ヤ綔琛錛屽苟灝哣isible灞炴ц劇疆涓-2錛屽叾涓-2琛ㄧず娣卞害闅愯棌銆傚傛灉闇瑕佹樉紺猴紝鍒欒劇疆涓-1銆傚間負0琛ㄧず姝ｅ父闅愯棌銆娣卞害闅愯棌鐨勭壒鐐規槸:鍦ㄦ櫘閫氶〉闈涓錛岀偣鍑婚紶鏍囧彸閿鍚庯紝鐪嬩笉鍒伴殣鈃忕殑宸ヤ綔琛ㄦ彁紺恆傛偍鍙鑳介氳繃榪涘叆VBA緙栬緫鍣ㄦ潵鏌ョ湅瀹冦06.闅愯棌Excel宸ヤ綔綈鐜板湪錛屽備綍闅愯棌宸ヤ綔綈挎枃浠?涓嬮潰婕旂ず浣跨敤Windows10緋葷粺錛岀洿鎺ュ湪鈥滄煡鐪嬧濅腑璁劇疆鈥滈殣鈃忔墍閫夐」鈥濓紝鍕鵑夆滈殣鈃忛」鈥濆嵆鏄劇ず闅愯棌鏂囦歡錛屼笉鍕鵑夊垯涓嶆樉紺洪殣鈃忔枃浠躲07.闅愯棌鍗曞厓鏍0鐨勬柟娉鎴戠粡甯稿湪琛ㄦ牸涓閬囧埌寰堝氶浂銆傝嫢瑕佸皢榪欎簺鍗曞厓鏍肩殑闆惰劇疆涓轟笉鏄劇ず錛岃鋒墦寮鈥淓xcel閫夐」鈥濆硅瘽妗嗭紝鍦ㄢ滈珮綰р濅腑鎵懼埌鈥滄樉紺哄間負闆剁殑鍗曞厓鏍間腑鐨勯浂鈥濓紝鍙栨秷鍕鵑夛紝鍗曞嚮鈥滅『瀹氣濅笉鏄劇ず銆08.鍦ㄥ矓閮ㄥ崟鍏冩牸涓闅愯棌闆剁殑鏂規硶閭ｄ箞錛屼綘濡備綍璁劇疆灞閮ㄥ崟鍏冩牸涓嶆樉紺0錛岃屼粛鐒跺湪鍏朵粬鍦版柟鏄劇ず瀹?棣栧厛錛岄夋嫨瑕侀殣鈃忛浂鍊肩殑鏁版嵁鍖哄煙錛屽皢榧犳爣縐誨姩鍒拌ユ暟鎹鍖哄煙涓婏紝鍗曞嚮榧犳爣鍙抽敭錛岄夋嫨鈥滆劇疆鍗曞厓鏍兼牸寮忊濄傚湪鈥淐ustom鈥濈殑鈥淭ype鈥濅腑錛屾坊鍔燵=0]鈥溾!09.涓縐嶉殣鈃忓崟鍏冩牸涓鎵鏈夊唴瀹圭殑鏂規硶濡傛灉鎯抽殣鈃忓崟鍏冩牸鐨勬墍鏈夊唴瀹癸紝鍙浠ュ湪鈥淭ype鈥濆瓧孌典腑杈撳叆涓変釜鍒嗗彿(;;;)鏉ュ畾鍒跺崟鍏冩牸鏍煎紡銆傚崟鍑燴滅『瀹氣濄10.闅愯棌緗戞牸綰匡紝緙栬緫鏍忥紝鏍囬鍦╗瑙嗗浘]閫夐」鍗′腑錛屾偍鍙浠ラ殣鈃忕綉鏍肩嚎銆佺紪杈戞爮鍜屾爣棰樸傚綋閬囧埌闅愯棌琛ㄦ椂錛屼篃鍙浠ュ仛鍚屾牱鐨勪簨鎯呫11.Excel闅愯棌鍏寮忕殑鏂規硶浣犲彲浠ラ殣鈃忓拰淇濇姢浣犺緵杈涜嫤鑻﹀啓鍑烘潵鐨勫叕寮忋傞栧厛錛岄夋嫨鏁版嵁鍖哄煙錛屾寜Ctrl+G錛岄夋嫨LocationConditions鍦ㄢ滃畾浣嶆潯浠垛濆硅瘽妗嗕腑錛岄夋嫨鈥滃叕寮忊濄傜劧鍚庡皢榧犳爣縐誨姩鍒伴夋嫨鍖猴紝鍙抽敭鍗曞嚮錛岄夋嫨鈥滄牸寮忓寲鍗曞厓鏍尖濓紝閫夋嫨鈥滈殣鈃忎繚鎶も濄傛渶鍚庯紝鍗曞嚮鈥滃℃煡鈥濋夐」鍗″苟閫夋嫨鈥滀繚鎶よ〃鈥濄12.闅愯棌琛屽拰鍒楃殑鏂規硶闅愯棌宸ヤ綔琛ㄤ腑鐨勮屽拰鍒楃殑閫氬父鏂規硶鏄鐢ㄩ紶鏍囬夋嫨鎵闇鐨勮岋紝騫墮氳繃鍙抽敭鍗曞嚮鎵閫夊唴瀹規潵璁劇疆闅愯棌銆傜劧鑰岋紝鏈変簺浜烘洿鍠滄浣跨敤蹇鎹烽敭鏉ュ仛鍒拌繖涓鐐廣傞殣鈃忚岀殑蹇鎹烽敭涓衡淐trl+9鈥濓紝鏄劇ず闅愯棌琛岀殑蹇鎹烽敭涓衡淐trl+Shift+9鈥濓紝闅愯棌鍒楀揩鎹烽敭涓衡淐trl+0鈥濄13.闅愯棌鍏寮忛敊璇鍊浠諱綍浣跨敤榪嘨LOOKUP鐨勪漢閮界啛鎮#N/A閿欒鍊箋傚湪鏌ユ壘鐩鏍囧兼椂錛屽傛灉鐩存帴鏄劇ず榪斿洖#N/A錛屽垯浼氬獎鍝嶆暣涓琛ㄧ殑澶栬傘傚洜姝わ紝鎮ㄥ彲浠ュ湪鍏寮忓懆鍥村寘瑁呬竴涓狪FERROR鍑芥暟鏉ラ殣鈃忛敊璇鍊箋浠ヤ笂榪13鐐規柟娉曟妧宸у氨鏄鍏充簬鈥淓xcel闅愯棌淇℃伅鐨勬柟娉曟湁鍝浜涳紵鈥濈殑鍒嗕韓浜嗭紝甯屾湜瀵逛綘鐨凟xcel瀛︿範浠ュ強鎿嶄綔閮芥湁閲嶈佺殑甯鍔╀綔鐢ㄥ摝錛佹兂瑕佺啛緇冧嬌鐢‥xcel琛ㄦ牸鐨勫皬浼欎即錛屾渶濂藉氨鏄灝嗕互涓婅繖浜涙柟娉曠偣閮界粰鐗㈢墷璁頒綇錛屽疄闄呰繍鐢ㄥ埌鑷宸辯殑Excel琛ㄦ牸鏁寸悊涓鍝~
