excel中如何让a列某行的“汉字目录”自动在e列每行填满 EXCEL 如何在A列中排除某列（E列）中包含关键词的所有行...
EXCEL\u8868\u683cA\u5217\u7684\u6bcf\u884c\u5185\u5bb9\u4e0d\u540c\u540e\u9762\u586b\u52a0\u540c\u4e00\u4e2a\u5b57\u5728EXCEL\u8868\u683c\u67d0\u5217\u7684\u540e\u9762\u586b\u52a0\u540c\u4e00\u4e2a\u5b57\uff0c\u4f7f\u7528\u516c\u5f0f\u5728\u6bcf\u4e2a\u5355\u5143\u683c\u540e\u9762\u52a0\u4e0a\u5c31\u53ef\u4ee5\u4e86\u3002
\u65b9\u6cd5\u6b65\u9aa4\u5982\u4e0b\uff1a
1\u3001\u6253\u5f00\u9700\u8981\u64cd\u4f5c\u7684EXCEL\u8868\u683c\uff0c\u5728\u9700\u8981\u6dfb\u52a0\u5b57\u7684\u5355\u5143\u683c\u6240\u5728\u5217\u540e\u9762\u63d2\u5165\u4e00\u7a7a\u767d\u5217\uff0c\u7528\u4e8e\u8f93\u5165\u516c\u5f0f\uff0c\u5728\u6dfb\u52a0\u7684\u5217\u4e2d\u7b2c\u4e00\u4e2a\u5355\u5143\u683c\u8f93\u5165\u201c=\u201d\u3002
2\u3001\u70b9\u51fb\u9700\u8981\u6dfb\u52a0\u5b57\u7684\u5355\u5143\u683c\u3002
3\u3001\u7ee7\u7eed\u8f93\u5165\u3010&"\u8f66"\u3011\uff08\u6ce8\u610f""\u9700\u5728\u82f1\u6587\u72b6\u6001\u4e0b\u8f93\u5165\uff09\uff0c\u7136\u540e\u6309\u952e\u76d8\u7684\u201cENTER\u201d\u952e\u3002
4\u3001\u9f20\u6807\u653e\u5728\u5df2\u8f93\u5165\u516c\u5f0f\u7684\u5355\u5143\u683c\u53f3\u4e0b\u89d2\uff0c\u53d8\u6210\u201c+\u201d\u65f6\uff0c\u5f80\u4e0b\u62c9\u586b\u5145\u5355\u5143\u683c\u516c\u5f0f\u3002
5\u3001\u590d\u5236\u586b\u5145\u5b8c\u516c\u5f0f\u7684\u5355\u5143\u683c\uff0c\u5728\u201c\u5f00\u59cb\u201d\u9009\u9879\u5361\u627e\u5230\u201c\u526a\u8d34\u677f\u201d\uff0c\u70b9\u51fb\u201c\u7c98\u8d34\u201d\u4e0b\u9762\u7684\u4e0b\u62c9\u83dc\u5355\u6309\u94ae\uff0c\u9009\u62e9\u201c\u7c98\u8d34\u6570\u503c\u201d\u3002
6\u3001\u8fd4\u56deEXCEL\u8868\u683c\uff0c\u53d1\u73b0B\u5217\u5df2\u6210\u529f\u5728A\u5217\u6587\u672c\u7684\u57fa\u7840\u4e0a\u672b\u5c3e\u6dfb\u52a0\u4e86\u201c\u8f66\u201d\u5b57\u3002
\u4e0a\u56fe\u4f8b\u5b50\uff0cA3\u4e3a\u67e5\u627e\u7684\u5173\u952e\u8bcd\uff0c\u5728B8\uff1aD18\u533a\u57df\u67e5\u627e\uff0c\u53ea\u8981\u5305\u542b\u8fd9\u4e2a\u5173\u952e\u8bcd\u7684\u884c\uff0c\u63d0\u53d6\u81f3G8\uff1aI18\u4e2d\uff0cG8\u516c\u5f0f=INDEX(B:B,SMALL(IF(MMULT(1-ISERR(FIND($A$3,$B$8:$D$18)),{1;1;1}),ROW($8:$18),99),ROW(A1)))&"" \u6570\u7ec4\u516c\u5f0f\uff0c\u4e09\u952e\u7ed3\u675f\uff0c\u7136\u540e\u6a2a\u62c9\uff0c\u7ad6\u62c9\u516c\u5f0f\u5373\u53ef\u3002
e2=if(a2="","",ifcountif(a2,"?*"),a2,e1)),下拉
E2=IF(A2="","",LOOKUP("座",A$2:A2)) 下拉
绛旓細鈥濇暟鎹-鈥濇暟鎹湁鏁堟р-鈥濇暟鎹湁鏁堟р滐紝鍦ㄢ濆厑璁糕滀笅闈㈤夋嫨鈥濆簭鍒椻滐紝鍦ㄢ濇潵婧愨滀笅闈㈣緭鍏ュ叕寮"=INDIRECT($D$2)", 鐐瑰嚮纭畾銆傜涔濇锛屾楠屽垰鎵嶇殑鎴愭灉锛氭伃鍠滀綘锛屽ぇ鍔熷憡鎴愶紒琛ュ厖涓鍙ワ紝濡傛灉浣犵殑鍘熷鏁版嵁鏀惧湪Sheet1, 鑰屾煡瀵诲嵈鍦⊿heet2, 绗竷姝ョ殑鍏紡灏辨敼鎴愨=Sheet1!$A$1:$B$1"灏辫浜嗐
绛旓細鍦╡xcel琛ㄦ牸涓緭鍏ヤ竴涓紪鍙峰悗锛屽彟涓鎺掓牴鎹鐓ц〃鑷姩鏄剧ず璇ョ紪鍙峰搴旂殑鍐呭锛屽彲浣跨敤VLOOKUP鍑芥暟瀹炵幇銆傛柟娉曟楠ゅ涓嬶細1銆佹墦寮闇瑕佹搷浣鐨凟XCEL琛ㄦ牸锛屽湪鐩爣鍗曞厓鏍间腑杈撳叆鍏紡=IFERROR(VLOOKUP(A2,$D$3:$E$6,2,0),"")銆傘愬嚱鏁拌鏄庯紝鍏朵腑$D$3:$E$6鏄渶瑕佹煡鎵剧殑琛ㄦ牸瀵圭収鍖哄煙缁濆寮曠敤锛2琛ㄧず杩斿洖D鍒楀悗...
绛旓細=IF(COUNTBLANK(A:A)>0, 0, "OK")
绛旓細灏嗗師a鍒鐩存帴淇敼鏄剧ず缁撴灉锛屽彧鑳戒娇鐢╲ba锛屽叕寮忛渶瑕佽繘琛屾搷浣溿俤1杈撳叆 =vlookup(a1,b:c,2,0)涓嬫媺濉厖锛岀劧鍚庯紝澶嶅埗d鍒楋紝鍙抽敭锛岄夋嫨鎬х矘璐达紝鍕鹃夛紝鏁板硷紝纭畾銆傝繖鏍穌鍒楀氨鏄綘瑕佺殑鍐呭
绛旓細=IFERROR(IF(AND(ISERROR(VLOOKUP(D1,INDIRECT("D"&ROW(D1)+1):D100,1,0)),ISERROR(VLOOKUP(E1,INDIRECT("E"&ROW(E1)+1):E100,1,0))),"",VLOOKUP(E1,INDIRECT("E"&ROW(E1)+1):F100,2,0))+VLOOKUP(E1,INDIRECT("E"&ROW(E1)+1):G100,3,0)+IF(ROW(D1)=1,D1,0),0)...
绛旓細璁A鍒鍊兼槸A1:A100 B1 =IF(ROW(A1)>COUNTIF(A:A,100),"",SMALL(IF($A$1:$A$100=100,ROW($A$1:$A$100),4^8),ROW(A1)))鏁扮粍鍏紡锛屼笉鑳藉洖杞︼紝闇瑕佹寜涓媍trl+shift+鍥炶溅锛岀湅鍒板叕寮忓墠鍚庢湁{}鐨勪骇鐢燂紝灏卞浜嗭紝涓嬫媺濉厖
绛旓細浠excel2011缂栬緫涓轰緥锛1銆佸厛鎵撳紑excel琛紝濡傚浘鎵绀猴紝鐒跺悗浣跨敤excel琛ㄨ幏鍙栦俊鎭2銆佸鍥炬墍绀猴紝杈撳叆鍚庨変腑B鍒楃殑鎵鏈夌殑鍗曞厓鏍间俊鎭3銆佺劧鍚庡崟鍑婚〉闈㈤《閮ㄧ殑鑿滃崟椤规潵閫夋嫨鏁版嵁鍒嗗垪閫夐」銆4銆佺劧鍚庡湪寮瑰嚭椤典腑閫夋嫨鈥滃浐瀹氬搴︹濓紝鐒跺悗鍗曞嚮涓嬩竴姝ャ5銆佺劧鍚庣敤榧犳爣鍏夋爣鐐瑰嚮鍚嶇О鍜岄珮搴﹀垎寮鐨勪綅缃紝鐒跺悗鐐瑰嚮瀹屾垚銆6銆佸崟鍑诲悗...
绛旓細鐢ㄦ暟缁勫叕寮廔NDEX 鍥剧墖鐪嬩笉娓呮 鍋囪鍦A:F鍒楋紝鎻愬彇姣忚鏈鍚1涓暟鎹 =INDEX(A1:F1,SMALL(IF(A1:F1<>"",COLUMN($A$1:$F$1),256),COUNTA(A1:F1)))鍚屾椂鎸塁TRL SHIFT 鍥炶溅閿紝鍑虹幇缁撴灉锛屼笅鎷夊叕寮
绛旓細鍙互浣跨敤Excel鐨VLOOKUP鍑芥暟鏉ュ疄鐜拌繖涓渶姹傘俈LOOKUP鍑芥暟鍙互鏌ユ壘鎸囧畾鐨勫煎湪鏌愪竴鍒椾腑鐨勪綅缃紝骞舵牴鎹綅缃繑鍥炶琛岀殑鍏朵粬鏁版嵁銆傚叿浣撴潵璇达紝浣犲彲浠ュ湪O鍒椾腑杈撳叆浠ヤ笅鍏紡锛=VLOOKUP(M1,A:J,2,FALSE)杩欎釜鍏紡鐨勬剰鎬濇槸锛歏LOOKUP鍑芥暟鐨勭涓涓弬鏁版槸鏌ユ壘鐨勫硷紝鍦ㄨ繖閲屼负M1鍗曞厓鏍间腑鐨勫笺俈LOOKUP鍑芥暟鐨勭浜屼釜鍙傛暟鏄...
绛旓細鍦℉1涓緭鍏ユ垨澶嶅埗绮樿创涓嬪垪鍏紡 =IFERROR(INDEX(A:A,SMALL(IF($A$1:$A$8=$G$1,ROW($1:$8)+1),ROW(A1))),"")鎸変笁閿瓹TRL+SHIFT+ENTER缁撴潫鍏紡杈撳叆 涓嬫媺濉厖锛屽彸鎷夊～鍏 姹A鍒鈥滆摑鑹测濈殑涓暟锛屽彲鐢ㄥ涓嬪叕寮 =COUNTIF(A:A,"钃濊壊")...
