Si të rregulloni gabimet e zakonshme të formulës në Excel

  • Identifikoni gabimet më të zakonshme dhe mësoni të interpretoni mesazhet e tyre.
  • Zbuloni zgjidhje praktike për të korrigjuar çdo lloj gabimi në formulat e Excel-it.
  • Zbatoni këshilla kyçe për të parandaluar gabimet dhe për të optimizuar fletëllogaritëset tuaja.
Mayka Jimenez

Minuta 8

Si të rregulloni gabimet e formulës në Excel.

Puna me formula në Excel mund të jetë mjaft sfiduese., veçanërisht kur hasim mesazhe misterioze gabimi ose rezultate që thjesht nuk përputhen. Nëse keni hasur ndonjëherë simbole të çuditshme, numra ose paralajmërime të fshehta si #DIV/0!, #VALUE! ose #REF!, mos u shqetësoni: është shumë e zakonshme dhe ka një zgjidhje.

Ky artikull synon t'ju ofrojë një udhëzues të plotë dhe praktik. se si të zbulohen, të kuptohen dhe të korrigjohen gabimet më të zakonshme që shfaqen kur punohet me formula në Excel.

Gabimet kryesore të formulës në Excel dhe si t'i identifikoni ato

Kur shkruani formula në Excel, Është e rëndësishme t'i kushtohet vëmendje sintaksës dhe llojeve të të dhënave të përdorura., meqenëse shumica e gabimeve lindin nga mbikëqyrje të vogla ose mosnjohje e rregullave themelore të funksioneve.

Disa nga dështimet më të zakonshme Ato ndodhin kur shenja e barazimit nuk përdoret për të filluar formulën, kllapat lihen jashtë, dy pikat përdoren gabimisht për diapazonet e qelizave ose zgjidhen argumente të pasakta për një funksion, ndër të tjera.

  • Formulat gjithmonë fillojnë me '='Nëse nuk e bëni, Excel do ta interpretojë atë që shkruani si tekst ose edhe si datë. Për shembull, "SUM(A1:A10)" do të shfaqet siç është, dhe "11/2" mund të shfaqet si 2 nëntor.
  • Kontrolloni kllapatDuhet të hapni dhe mbyllni saktë çdo kllapë në funksione. Nëse humbisni një ose shkoni shumë larg, formula do të jetë e pavlefshme. Shembull i pasaktë: =IF(B5<0);"Invalid";B5*1,05)
  • Përdor dy pika (':') për diapazoneKjo është jetike që Excel të kuptojë diapazonin e qelizave. Shkruaj =SUM(A1:A5), kurrë =SUM(A1:A5), gjë që do të jepte një gabim #NULL!.
  • Sigurohuni që të shkruani të gjitha argumentet e nevojshmeÇdo funksion kërkon një numër të saktë argumentesh, as më shumë, as më pak, dhe të llojit të saktë (numër, tekst, etj.).

Përveç kësaj, ka detaje të vogla që shpesh kalojnë pa u vënë re., siç është kufizimi i vendosjes së folezave në 64 funksione, përdorimi i saktë i thonjëzave kur emërtoni fletët me hapësira ose karaktere speciale dhe përdorimi i pikëçuditëses (!) kur një formulë thërret një fletë tjetër. Nëse punoni me libra pune të jashtëm, shtegu dhe emri duhet të referencohen saktë dhe sa herë që shkruani numra, shmangni formatet (si presjet ose simbolet e euros), duke u kufizuar në vlerën e pastër numerike.

8 gabimet më të zakonshme dhe si t'i zgjidhni ato

Korrigjoni gabimet e formulës në Excel.

Excel shfaq kode të ndryshme gabimi Në varësi të problemit të hasur në një formulë. Çdo mesazh tregon një shkak të ndryshëm dhe ka zgjidhjen e vet. Më poshtë, shqyrtojmë më të zakonshmet dhe korrigjimet e tyre të mundshme.

# N / A

Kuptimi: I padisponueshem. Ndodh kur një funksion kërkimi nuk e gjen vlerën e kërkuarËshtë tipike në VLOOKUP, HLOOKUP, MATCH ose SEARCH kur vlera nuk ekziston në diapazonin e kërkimit.

Zgjidhja: Kontrolloni që diapazonet e referencuara janë të sakta, që informacioni është aty ku duhet të jetë dhe që nuk është fshirë. Nëse ky gabim duhet të shfaqet sepse të dhënat nuk ekzistojnë në të vërtetë, mund të përdorni një funksion për trajtimin e gabimeve si IFERROR për të shfaqur tekst më të dobishëm, për shembull: =SI.ERROR(BUSCARV(A10;A3:B5;2;0);»Valor no encontrado»).

#VALE!

Kuptimi: Vlerë e pavlefshme. Ky gabim tregon që formula po bashkëvepron me qeliza ose lloje të dhënash që nuk mbështeten.Shfaqet shpesh kur përdoret teksti ku kërkohen numra ose anasjelltas, ose kur ka qeliza bosh ose karaktere të papritura.

Zgjidhja: Kontrolloni që të gjitha argumentet të jenë të llojit të saktë (nëse pritet një numër ose tekst) dhe që të mos ketë hapësira ose karaktere të pazakonta në diapazon. Përdorni mjetin Gjurmimi i Precedentëve për të zbuluar se nga vjen gabimi.

#REF!

Kuptimi: Referencë e pavlefshme. Ndodh kur një formulë tregon një qelizë të fshirë ose një që është lënë jashtë fletës për shkak të prerjeve ose ngjitjeve aksidentale.

Zgjidhja: Idealisht, duhet ta anuloni veprimin që fshiu qelizën ose ta modifikoni formulën për të treguar në referenca të vlefshme. Gjithashtu, mund ta rregulloni diapazonin në funksionet e kërkimit që të përputhet me numrin e kolonave të disponueshme.

Për shembull, një formulë =VLOOKUP(A8;A2:D5,5;XNUMX;FALSE) do të japë një gabim, meqenëse diapazoni A2:D5 ka vetëm 4 kolona; duhet të jetë =VLOOKUP(A8;A2:E5,5;XNUMX;FALSE) ose korrigjoni indeksin e kolonës.

#EMRI?

Kuptimi: Excel nuk e njeh tekstin e përdorur në formulë si një emër funksioni të vlefshëm ose si një referencë të njohur qelize.

Shkaqet e zakonshme: Një gabim shtypi në emrin e funksionit ose një përkufizim i diapazonit të emërtuar mungon. Shembull: "=average(A1:A5)" në vend të "=average(A1:A5)".

Zgjidhja: Kontrolloni që funksioni të jetë shkruar saktë dhe përdorni opsionin e futjes së funksionit nga skeda e formulave për të shmangur gabimet e shtypjes.

#NDIV/0!

Kuptimi: Po përpiqeni të pjesëtoni një vlerë me zero, gjë që nuk është matematikisht e mundur.

Zgjidhja: Para pjesëtimit, përdorni një funksion SI për të kontrolluar nëse pjesëtuesi është zero. Për shembull: =IF(B3=0;»»;A3/B3)Kjo do të parandalojë shfaqjen e gabimit dhe do t'ju lejojë të shfaqni një rezultat alternativ ose një qelizë bosh.

#####

Gabime në Excel.

Kuptimi: Qeliza nuk është mjaftueshëm e gjerë për të shfaqur përmbajtjen e saj. Ky nuk është një gabim formule, por një gabim formatimi.

Zgjidhja: Rregulloni gjerësinë e kolonës duke klikuar dy herë në skajin e djathtë të kokës së faqes, ose zgjidhni butonin "Zgjidh të Gjitha" dhe ndryshoni madhësinë e të gjithë fletës nëse është e nevojshme.

#I PAVLEFSHËM!

Kuptimi: Është referuar një diapazon joekzistent ose i përcaktuar dobët, zakonisht sepse është përdorur një operator i gabuar.

Zgjidhja: Përdorni gjithmonë dy pika ':' për të treguar diapazone (për shembull, A2:C2) dhe pikëpresje ';' për të bashkuar diapazone të shumëfishta në një funksion, për shembull: =SUM(A2:A6;D2:D6).

#NUM!

Kuptimi: Numër i pavlefshëm, zakonisht për shkak të llogaritjeve të pamundura ose rezultateve jashtë kufijve që Excel mund të trajtojë.

shkakton: Llogaritni rrënjën katrore të një numri negativ ose kërkoni një rezultat aq të madh ose të vogël sa të jetë jashtë diapazonit të lejuar (midis -1×10)307 dhe 1×10307).

Zgjidhja: Rishikoni vlerat e përdorura në formulë, sigurohuni që nuk po operoni me të dhëna jologjike dhe nëse përdorni funksione iterative si IRR ose RATE, rregulloni opsionet e iteracionit në mjete të avancuara të analizës.

Si të shmangni gabimet kur shkruani formula komplekse

Shumë probleme mund të parandalohen duke ndjekur një sërë rekomandimesh kur krijoni formula në Excel:

  • Kontrolloni gjithmonë numrin e kllapave dhe që ato të përputhen saktë. Një metodë praktike është të numërohen numrat e hapjes dhe mbylljes përpara se të finalizohet formula.
  • Ruaj logjikën e ndërthurjesNdërthurja e më shumë se 64 funksioneve nuk duhet të lejohet, pasi Excel nuk do ta lejojë këtë dhe do të shkaktojë gabime që janë të vështira për t'u gjetur.
  • Referoni saktë qelizat midis fletëve dhe librave të punësNëse emri i një fletëje përmban hapësira ose karaktere jo-alfabetike, vendoseni gjithmonë në thonjëza të vetme (' '). Gjithashtu, kur referoheni librave të punës të jashtëm, përdorni formatin dhe shtoni shtegun nëse nuk është i hapur.
  • Mos përfshini formatimin në numraNëse doni të shfaqni simbolin e euros, aplikojeni atë në qelizë më pas, jo brenda formulës.

Mjete dhe truke në Excel për të rregulluar shpejt gabimet

Formulat me gabime në Excel: si t'i rregulloni ato.

Excel ka disa funksione dhe shërbime që ndihmojnë në zbulimin dhe korrigjimin e gabimeve në formula ose të dhëna në spreadsheet-e. Njohja e tyre mund t'ju kursejë shumë kohë dhe përmirësoni besueshmërinë e spreadsheet-eve tuaja.

  • Gabim gjatë kontrollitNga skeda "Formulat", mund të qaseni në këtë veçori, e cila analizon formulën dhe shpjegon gabimet e mundshme ose zgjidhjet për t'i zgjidhur ato. Është veçanërisht e dobishme për mesazhe si #EMRI?
  • Gjurmimi i precedentëve dhe personave në varësiKy mjet ju lejon të shfaqni me shigjeta se cilat qeliza janë pjesë e llogaritjes, duke ndihmuar në gjetjen e burimit të gabimit #¡VALUE! ose gabimeve të tjera të ngjashme.
  • Plotësimi automatik i formulësNëse ka mospërputhje midis qelizave, zgjidhni formulën problematike dhe, duke mbajtur të shtypur tastin SHIFT, zgjeroni përzgjedhjen dhe më pas korrigjojeni atë lart, poshtë, djathtas ose majtas sipas nevojës, duke përdorur shkurtoret e tastierës ose opsionet në menynë Plotëso.

Raste praktike dhe situata të zakonshme

Për të kuptuar më mirë gabimet e formulave, le të shohim disa shembuj dhe truke të dobishme që mund të aplikoni.

  • Korrigjoni gabimin #####: Kur futni një datë të gjatë ose një numër të madh në një qelizë të ngushtë, përmbajtja do të shfaqet me disa shenja hash. Zgjeroni kolonën dhe të dhënat do të jenë të dukshme.
  • #REF! për shkak të rrezes së pamjaftueshme: Nëse përdorni funksione si VLOOKUP dhe kërkoni një kolonë jashtë diapazonit, rregulloni diapazonin ose indeksin që të përputhet.
  • Trajtimi i #DIV/0!: Para pjesëtimit, përdorni një kusht për të kontrolluar nëse pjesëtuesi është zero. Në këtë mënyrë, shmangni gabimin dhe kontrolloni rezultatin e shfaqur.

Fundamentalshtë themelore që gabimet në Excel të korrigjohen në kohën e duhur, pasi ato mund të ndikojnë në llogaritjet e varura dhe të përhapin rezultate të pasakta në të gjithë spreadsheet-in.