鍝庯紝浣犵殑Excel琛ㄦ牸閲屾槸涓嶆槸缁忓父鍐掑嚭鈥?strong>鈥嬩贡涓冨叓绯熺殑璐熸暟鈥?/strong>鈥嬶紝鎴栬€呯獊鐒跺嚭鐜扳€?strong>鈥?DIV/0!銆?N/A鈥?/strong>鈥嬭繖绉嶇湅涓嶆噦鐨勭鍙凤紵鍒厡锛佷粖澶╁挶浠笉鏁撮偅浜涘鏉傜殑鐞嗚锛岀洿鎺ヤ笂鈥?strong>鈥嬪皬鐧戒篃鑳界鎳傗€?/strong>鈥嬬殑瀹炴搷鎶€宸э紝鏁欎綘涓夋嫑鎶婃暟鎹敹鎷惧緱骞插共鍑€鍑€锛?/p>

涓€銆佺敤IF鍑芥暟锛氫竴閿妸璐熸暟鍙橀浂鐨勫偦鐡滄搷浣?/h3>

鈥?strong>鈥嬧€滃嚱鏁板惉璧锋潵寰堥珮绾э紵鍏跺疄姣斿墛鑻规灉杩樼畝鍗曪紒鈥濃€?/strong>鈥?br/> 鍒氬叆琛岀殑鍚屼簨灏忕帇锛屼笂鍛ㄥ洜涓烘妸閿€鍞暟鎹噷鐨勮礋鏁版墜鍔ㄦ敼鍒扮溂鍐掗噾鏄燂紝宸偣鎶婇敭鐩樼牳浜?..鍏跺疄鐪熶笉鐢ㄨ繖涔堢疮锛丒xcel鑷甫鐨処F鍑芥暟锛?0绉掑氨鑳芥悶瀹氳繖浜嬪効銆?/p>

鍏蜂綋鎿嶄綔锛?/p>

1. 鍦ㄧ┖鐧藉崟鍏冩牸杈撳叆 鈥?strong>鈥?IF(鍘熷崟鍏冩牸<0,0,鍘熷崟鍏冩牸)鈥?/strong>鈥?br/> 锛堟瘮濡傛暟鎹湪A1锛屽氨杈撳叆=IF(A1<0,0,A1)锛?/li>
2. 鎸夊洖杞﹀悗寰€涓嬫嫋鍔ㄥ～鍏咃紝鈥?strong>鈥嬫墍鏈夎礋鏁扮灛闂村彉闆垛€?/strong>鈥?/li>
3. 鏈€鍚庡鍒跺鐞嗗ソ鐨勬暟鎹紝鈥?strong>鈥嬪彸閿啋閫夋嫨鎬х矘璐粹啋鏁板€尖€?/strong>鈥嬶紝鎼炲畾锛?/li>

馃憠 鈥?strong>鈥嬫暡榛戞澘閲嶇偣鈥?/strong>鈥嬶細

• 杩欐嫑閫傚悎鈥?strong>鈥嬮渶瑕佷繚鐣欏師濮嬫暟鎹€?/strong>鈥嬬殑鎯呭喌
• 澶勭悊1000琛屾暟鎹彧瑕?绉掞紝姣斿枬鍙ｆ按杩樺揩
• 杩涢樁鐜╂硶锛氭妸0鏇挎崲鎴愨€滃紓甯糕€濈瓑鎻愮ず璇紝鏂逛究鍚庣画鎺掓煡锛堟瘮濡?IF(A1<0,"寮傚父",A1)锛?/li>

---

浜屻€佹潯浠舵牸寮忛殰鐪兼硶锛氫笉淇敼鍘熸暟鎹殑鍋锋噿绉樼睄

鈥?strong>鈥嬧€滈瀵奸潪瑕佺湅鍘熷琛ㄦ牸锛熻繖鎷涜兘楠楄繃鎵€鏈変汉鐨勭溂鐫涳紒鈥濃€?/strong>鈥?br/> 涓婃璐㈠姟閮ㄦ潕濮愭暀鎴戜釜楠氭搷浣溾€斺€旂敤鏉′欢鏍煎紡璁╄礋鏁扳€?strong>鈥嬪亣瑁呮秷澶扁€?/strong>鈥嬨€傛暟鎹湰韬病鍙橈紝浣嗘樉绀烘晥鏋滃叏鏄鏁帮紝鐗瑰埆閫傚悎搴斾粯妫€鏌ユ姤琛ㄧ殑鍦烘櫙銆?/p>

鎿嶄綔姝ラ鍒嗚В锛?/p>

1. 閫変腑鏁版嵁鍖哄煙 鈫?鐐广€愬紑濮嬨€戔啋銆愭潯浠舵牸寮忋€戔啋銆愭柊寤鸿鍒欍€?/li>
2. 閫夈€愪娇鐢ㄥ叕寮忕‘瀹氭牸寮忋€戔啋 杈撳叆 鈥?strong>鈥?A1<0鈥?/strong>鈥嬶紙鍋囪鏁版嵁浠嶢1寮€濮嬶級
3. 鐐广€愭牸寮忋€戔啋銆愭暟瀛椼€戔啋銆愯嚜瀹氫箟銆戯紝鍦ㄧ被鍨嬫閲岃緭 鈥?strong>鈥?;0;0鈥?/strong>鈥?/li>
4. 瑙佽瘉濂囪抗鐨勬椂鍒伙細鎵€鏈夎礋鏁板師鍦板彉韬樉绀轰负0锛屸€?strong>鈥嬪疄闄呮暟鍊艰繕鍦ㄥ悗鍙拌汉鐫€鍛⑩€?/strong>鈥?/li>

鈿狅笍 鈥?strong>鈥嬫敞鎰忓潙浣嶁€?/strong>鈥嬶細

• 杩欐柟娉曟墦鍗版椂浼氶湶棣咃紒瑕佸鍑烘暟鎹殑璇濊寰楃敤绗竴鎷?/li>
• 鏀寔鎵归噺淇敼瀛椾綋棰滆壊锛堟瘮濡傛妸璐熸暟0鏍囩孩锛夛紝鍋氶璀﹁秴濂界敤

涓夈€乂BA鏆村嚮娴侊細澶勭悊10涓囪鏁版嵁鐨勬牳姝﹀櫒

鈥?strong>鈥嬧€滃惉璇翠綘瑕佸鐞嗗ぉ鐚弻11鐨勯攢鍞暟鎹紵VBA瀹忔墠鏄湡鐖哥埜锛佲€濃€?/strong>鈥?br/> 涓婁釜鏈堝府鏈嬪弸澶勭悊鐩存挱甯﹁揣鏁版嵁锛?0涓囪閲屾湁3涓囧璐熸暟锛屾墜鍔ㄦ敼锛熸€曟槸鏀瑰埌閫€浼戦兘鎼炰笉瀹?..杩欐椂鍊欏氨寰楃キ鍑篤BA杩欎釜澶ф潃鍣ㄤ簡銆?/p>

浠ｇ爜灏忕櫧涓撶敤妯℃澘锛?/p>

vba澶嶅埗
Sub 璐熸暟娓呴浂()
For Each cell In Selection
    If cell.Value < 0 Then cell.Value = 0
Next cell
End Sub

鎿嶄綔鎸囧崡锛?/p>

1. 鎸堿lt+F11鎵撳紑VBA缂栬緫鍣?鈫?鍙抽敭鎻掑叆妯″潡
2. 绮樿创涓婇潰浠ｇ爜 鈫?鍏抽棴缂栬緫鍣?/li>
3. 閫変腑瑕佸鐞嗙殑鏁版嵁鍖哄煙 鈫?鎸堿lt+F8杩愯瀹?/li>

馃挕 鈥?strong>鈥嬩釜浜鸿娉粡楠屸€?/strong>鈥嬶細

• 澶勭悊鍓嶄竴瀹氾紒涓€瀹氾紒瑕佸浠藉師鏂囦欢
• 鑳藉悓鏃跺鐞嗛敊璇€硷細鎶婁唬鐮侀噷鐨勫垽鏂潯浠舵敼鎴?鈥?strong>鈥婭f cell.Value <0 Or IsError(cell.Value)鈥?/strong>鈥?/li>
• 杩涢樁鐜╁鍙互鍔犱釜杩涘害鏉★紝鐪嬬潃鏁版嵁鍝楀摋鍙橀浂鐗瑰埆瑙ｅ帇

鍥涖€侀敊璇€肩殑闅愯棌鍓湰锛欼FERROR鐨勪吉瑁呮湳

绐佺劧鎯宠捣鏉ワ紝浣犱滑鏄笉鏄繕琚悇绉?鍙烽敊璇悶鐤繃锛熲€?strong>鈥嬪伔鍋峰憡璇変綘浠釜涓囪兘鑶忚嵂鈥斺€擨FERROR鍑芥暟鈥?/strong>鈥嬨€?/p>

姣斿鍏紡缁忓父鎶ラ敊 #DIV/0!锛屾敼鎴愶細
鈥?strong>鈥?IFERROR(鍘熷叕寮?0)鈥?/strong>鈥?br/> 鐬棿涓栫晫娓呭噣锛佸氨鍍忕粰鍏紡绌夸簡闃插脊琛ｏ紝浠€涔堥敊璇€奸兘缁欎綘鎸″湪澶栭潰鏄剧ず鎴?銆?/p>

浜斻€佽閫夊摢鎷涳紵鐪嬪満鏅笅鑿滅

鏍规嵁鎴戣繖浜涘勾韪╁潙鎬荤粨鐨勭粡楠岋細

• 鈥?strong>鈥嬩复鏃舵鏌ョ敤绗簩鎷涒€?/strong>鈥嬶紙鏉′欢鏍煎紡闅滅溂娉曪級
• 鈥?strong>鈥嬫棩甯稿皬鎵归噺鐢ㄧ涓€鎷涒€?/strong>鈥嬶紙IF鍑芥暟锛?/li>
• 鈥?strong>鈥嬫湀搴?骞存湯澶ф壒閲忕敤绗笁鎷涒€?/strong>鈥嬶紙VBA瀹忥級
• 鈥?strong>鈥嬪叕寮忔姤閿欑粺涓€鐢ㄧ鍥涙嫑鈥?/strong>鈥嬶紙IFERROR澶ф硶锛?/li>

鏈€杩戝彂鐜颁釜鏂拌秼鍔匡細瓒婃潵瓒婂鐨?0鍚庡悓浜嬪紑濮嬬敤鈥?strong>鈥婸ower Query鈥?/strong>鈥嬪鐞嗘暟鎹€傝櫧鐒堕棬妲涢珮鐐癸紝浣嗚兘涓€娆℃€ц缃竻娲楄鍒欙紝涔嬪悗鐐瑰埛鏂板氨鑳借嚜鍔ㄥ鐞嗭紝閫傚悎闀挎湡閲嶅鎬у伐浣溿€備笉杩囧浜庢柊鎵嬶紝杩樻槸寤鸿鍏堟妸浠婂ぉ杩欎笁鏉挎枾缁冪啛鍐嶈锛?/p>

璇寸偣澶у疄璇?/h3>

寰堝浜鸿寰楁暟鎹竻娲楀氨鏄釜鑴忔椿绱椿锛屼絾浠ユ垜杩欎簲骞寸殑瀹炴搷缁忛獙鏉ョ湅锛屸€?strong>鈥嬩細娓呮礂鏁版嵁鐨勪汉鍗囪亴姣旇皝閮藉揩鈥?/strong>鈥嬶紒涓婃鍏徃绔炶仒鏁版嵁鍒嗘瀽宀楋紝灏卞洜涓烘垜鑳芥妸涔变竷鍏碂鐨勯攢鍞暟鎹?0鍒嗛挓娲楀共鍑€锛岀洿鎺ュ共鎺変簡涓€鍫嗗彧浼氬仛鍥捐〃鐨勮姳鏋跺瓙銆傝浣忓挴锛氣€?strong>鈥嬪共鍑€鐨勬暟鎹墠鏄垎鏋愮殑鍩虹煶鈥?/strong>鈥嬶紝鍒瓑鍒板仛姹囨姤鏃惰鑰佹澘闂緱鍝戝彛鏃犺█鍐嶅悗鎮旓紒

本文由嘻道妙招独家原创，未经允许，严禁转载