用vlookup+indirect动态引用数据，表格联动不求人-嘻道妙招

锛堟澶勬彃鍏ュ皝闈㈠浘锛欵xcel琛ㄦ牸鏁版嵁鑱斿姩鐨勫姩鎬佹紨绀猴級

锛堟暡榛戞澘锛侊級鍏堟潵鐐瑰疄鍦ㄧ殑锛?/p>

鈥?strong>鈥媀LOOKUP鈥?/strong>鈥嬶細灏卞儚涓櫤鑳芥湜杩滈暅锛岃兘鍦ㄨ尗鑼暟鎹捣閲屽府浣犵簿鍑嗘崬楸?/li>
鈥?strong>鈥婭NDIRECT鈥?/strong>鈥嬶細鐩稿綋浜庝竾鑳介仴鎺у櫒锛屾寚鍝墦鍝殑鍔ㄦ€佸湴鍧€鐢熸垚鍣?/li>
鈥?strong>鈥嬪悎浣撴晥鏋溾€?/strong>鈥嬶細姣斿鎶?鍖椾含鍒嗗叕鍙?A1:C10"鍙樻垚鍙殢鏃跺垏鎹㈢殑娲诲湴鍧€锛岃窡鐜╂父鎴忔崲瑁呭浼肩殑

涓句釜鏍楀瓙馃尠锛氫綘鍋氫簡12涓湀浠界殑閿€鍞〃锛岀幇鍦ㄨ鍦ㄦ眹鎬昏〃閲屾兂鐪嬪摢涓湀灏辨樉绀哄摢涓湀鐨勬暟鎹紝涓嶇敤鏀瑰叕寮忥紒杩欏氨鏄繖鍝ヤ咯鐨勭湅瀹舵湰浜嬨€?/p>

=鈥?strong>鈥媀LOOKUP鈥?/strong>鈥?鎵惧暐, 鍘诲摢鎵? 甯﹀洖鏉ョ鍑犲垪鐨勮揣, 鏄笉鏄簿纭壘)

excel澶嶅埗=VLOOKUP(A2, 浜у搧娓呭崟!A:D, 3, FALSE)

鈥?strong>鈥嬮噸鐐规彁閱掆€?/strong>鈥嬶細

=鈥?strong>鈥婭NDIRECT鈥?/strong>鈥?"鍐欏湪鍗曞厓鏍奸噷鐨勫湴鍧€")

excel澶嶅埗=INDIRECT(B2&"!C5")

鍋囪B2鍐欑潃"3鏈?锛岃繖鍏紡灏辩瓑浜?'3鏈?!C5
鈥?strong>鈥嬮獨鎿嶄綔鈥?/strong>鈥嬶細鎶婂伐浣滆〃鍚嶅啓鍦ㄥ崟鍏冩牸閲岋紝鍏紡鑷姩璺熺潃鍙橈紝姣斿瓩鎮熺┖鐨勪竷鍗佷簩鍙樿繕鏂逛究锛?/p>

涓夈€佸姩鎬佽仈鍔ㄥ疄鎴樻暀瀛?/h3>

鈥?strong>鈥嬬粓鏋佺洰鏍団€?/strong>鈥嬶細鍋氫釜浼氳嚜宸卞彉鑴哥殑鏅鸿兘鎶ヨ〃
鈥?strong>鈥嬪噯澶囨潗鏂欌€?/strong>鈥嬶細

鈥?strong>鈥嬪悎浣撳叕寮忊€?/strong>鈥嬶細

excel澶嶅埗=VLOOKUP(A2, INDIRECT(B2&"!A:D"), 3, FALSE)

鈥?strong>鈥嬬炕璇戞垚浜鸿瘽鈥?/strong>鈥嬶細鏍规嵁B2閫夌殑鍒嗗簵鍚嶏紝鑷姩鍘诲搴旇〃鏍肩殑A鍒癉鍒楁壘鏁版嵁

锛堟澶勬彃鍏ュ姩鎬佹紨绀哄浘锛氬垏鎹㈠垎搴楀悕绉版椂鏁版嵁鑷姩鍙樺寲锛?/p>

鍥涖€佸父瑙佺炕杞︾幇鍦烘姠鏁戞寚鍗?/h3>

鈥?strong>鈥嬭娉暀璁€?/strong>鈥嬶細宸ヤ綔琛ㄥ悕閲屾湁绌烘牸鎴栫壒娈婄鍙凤紵璁板緱鍔犲崟寮曞彿锛佹瘮濡傦細

excel澶嶅埗=INDIRECT("'"&B2&"'!C5")

鈥?strong>鈥婹锛氫负浠€涔堥潪瑕佷粬淇╂惌閰嶏紵鈥?/strong>鈥?br/> A锛歏LOOKUP鑷繁鍙兘鍥哄畾鏌ヤ竴涓〃锛孖NDIRECT鑳借瀹冨浼?鐬Щ"锛岃繖鎵嶆槸鍔ㄦ€佸姙鍏殑绮鹃珦锛?/p>

鈥?strong>鈥婹锛氳繖缁勫悎鑳藉共鍟ユ缁忎簨锛熲€?/strong>鈥?/p>

用vlookup+indirect动态引用数据，表格联动不求人