Otkrivanje grešaka u formulama u programu Excel

Formule ponekad mogu da dovedu, pored vraćanja neželjenih rezultata, i do vrednosti grešaka. Slede neke alatke koje možete da koristite da biste pronašli i istražili uzroke ovih grešaka i utvrdili rešenja.

Saznajte kako da unesete jednostavnu formulu

Formule predstavljaju jednačine koje vrše izračunavanja nad vrednostima u radnom listu. Formula počinje znakom jednakosti (=). Na primer, sledeća formula sabira 3 i 1.

=3+1

Formula može da sadrži bilo koju od sledećih stavki ili sve njih: funkcije, reference, operatore i konstante.

Delovi formule

1. Funkcije: uključene u Excel, funkcije su projektovane formule koje vrše određena izračunavanja. Na primer, funkcija PI() daje vrednost pi: 3,142...

2. Reference: upućuju na pojedinačne ćelije ili opsege ćelija. A2 vraća vrednost iz ćelije A2.

3. Konstante: brojevi ili tekstualne vrednosti unete direktno u formulu, kao što je 2.

4. Operatori: Operator ^ (znak „karet“) podiže broj na stepen, a operator * (zvezdica) množi. Koristeći + i – sabirate i oduzimate vrednosti, a pomoću znaka / delite.

   Napomena

   Neke funkcije zahtevaju ono što se naziva argumentima. Argumenti su vrednosti koje određene funkcije koriste za izračunavanja. Kada je to potrebno, argumenti se postavljaju između zagrada funkcije (). Funkcija PI ne zahteva nijedan argument, zato su zagrade prazne. Neke funkcije zahtevaju jedan ili više argumenata i mogu da ostave prostora za dodatne argumente. Morate koristiti zarez da biste razdvojili argumente ili tačku i zarez (;), u zavisnosti od postavki lokacije.

Na primer, funkcija SUM zahteva samo jedan argument, ali može da sadrži ukupno 255 argumenata.

=SUM(A1:A10) je primer za jedan argument.

=SUM(A1:A10, C1:C10) je primer za više argumenata.

Ispravljanje uobičajenih grešaka pri unosu formula

Sledeća tabela rezimira neke od uobičajenih grešaka koje korisnik može da napravi pri unosu formule i objašnjava kako se rešavaju te greške.

Obezbedite sledeće:	Dodatne informacije
Započnite svaku funkciju znakom jednakosti (=)	Ako izostavite znak jednakosti, ono što otkucate može biti prikazano kao tekst ili kao datum. Na primer, ako otkucate SUM(A1:A10), Excel prikazuje tekstualnu nisku SUM(A1:A10) i ne izvršava izračunavanje. Ako otkucate 11/2, Excel prikazuje datum 2. nov (pod pretpostavkom da je format ćelije "Opšti") umesto da 11 podeli sa 2.
Uparite sve otvorene i zatvorene zagrade	Uverite se da svaka zagrada ima odgovarajući par (otvorenu i zatvorenu zagradu). Kad u formuli koristite funkciju, važno je da svaka zagrada bude na tačnoj poziciji da bi funkcija radila ispravno. Na primer, formula =IF(B5<0),"Nije važeće",B5*1,05) neće raditi zato što postoje dve zatvorene zagrade i samo jedna otvorena zagrada, a treba da postoji samo jedna otvorena i jedna zatvorena zagrada. Formula treba da izgleda ovako: =IF(B5<0,"Nije važeće",B5*1,05).
Koristite dvotačku za označavanje opsega	Kada upućujete na opseg ćelija, koristite dvotačku (:) da biste razdvojili referencu na prvu ćeliju u opsegu i referencu na poslednju ćeliju u opsegu. Na primer, =SUM(A1:A5), ne =SUM(A1 A5), što će vratiti #NULL! Greška.
Unesite sve potrebne argumente	Neke funkcije imaju potrebne argumente. Takođe, uverite se da niste uneli previše argumenata.
Unesite ispravan tip argumenata	Neke funkcije, kao što je SUM, zahtevaju numeričke argumente. Druge funkcije, kao što je REPLACE, zahtevaju tekstualnu vrednost za najmanje jedan od argumenata. Ako koristite pogrešan tip podataka kao argument, Excel može da vrati neočekivane rezultate ili prikaže grešku.
Ne ugnežđujte više od 64 funkcije	Ne možete da unesete ili ugnezdite više od 64 nivoa funkcija unutar funkcije.
Stavite imena drugih listova pod jednostruke navodnike	Ako se formula odnosi na vrednosti ili ćelije u drugim radnim listovima ili radnim sveskama, a ime druge radne sveske ili radnog lista sadrži razmake ili znakove koji nisu abecedni, to ime morate staviti pod jednostruke navodnike ( ' ), na primer ='Kvartalni podaci'! D3, ili ='123'! O1.
Postavite znak uzvika (!) iza imena radnog lista kada se u formuli upućuje na njega	Na primer, da biste vratili vrednost iz ćelije D3 u radnom listu pod imenom „Kvartalni podaci“ u istoj radnoj svesci, koristite ovu formulu: ='Kvartalni podaci'!D3.
Uključite putanju do spoljašnjih radnih svezaka	Uverite se da svaka spoljašnja radna sveska sadrži ime radne sveske i putanju do radne sveske. Referenca na radnu svesku uključuje ime radne sveske i mora stajati u uglastim zagradama([Imeradne sveske.xlsx]). Referenca takođe mora sadržati ime radnog lista u radnoj svesci. Ako radna sveska na koju želite da uputite nije otvorena u programu Excel, referencu na nju i dalje može uključiti u formulu. Obezbedite potpunu putanju do datoteke kao u sledećem primeru: =ROWS('C:\Moji dokumenti\[Q2 operacije.xlsx]Prodaja'!A1:A8). Ova formula vraća broj redova u opsegu koji obuhvata ćelije od A1 do A8 u drugoj radnoj svesci (8). Napomena: Ako potpuna putanja sadrži znakove za razmak, kao što je slučaj u prethodnom primeru, morate da stavite putanju u jednostruke navodnike (na početku putanje i iza imena radne sveske, ispred znaka uzvika).
Unesite brojeve bez oblikovanja	Nemojte da oblikujete brojeve pri unosu u formule. Na primer, ako je vrednost koju želite da unesete 1000 RSD, u formulu unesite 1000. Ako unesete zarez kao deo broja, Excel ga tretira kao znak za razdvajanje. Ako želite da se brojevi prikazuju tako da pokazuju znakove za razdvajanje hiljada ili miliona ili simbole valuta, oblikujte ćelije nakon što unesete brojeve. Na primer, ako želite da dodate 3100 vrednosti u ćeliji A3 i unesete formulu =SUM(3,100,A3), Excel sabira brojeve 3 i 100, a zatim njihov zbir dodaje vrednosti u ćeliji A3, umesto da doda 3100 u ćeliju A3, što bi bilo =SUM(3100,A3). Ili, ako unesete formulu =ABS(-2,134), Excel prikazuje grešku jer funkcija ABS prihvata samo jedan argument: =ABS(-2134).

Ispravljanje uobičajenih problema u formulama

Možete da primenite određena pravila za proveru grešaka u formulama. Ova pravila ne garantuju da vaš radni list ne sadrži greške, ali mogu da izvrše značajnu potragu za uobičajenim greškama. Pravila možete pojedinačno da uključite ili isključite.

Greške mogu da se označe i isprave na dva načina: jedna po jedna greška (kao što je kontrolor pravopisa) ili odmah kada se pojave na radnom listu dok unosite podatke.

Grešku možete da rešite pomoću opcija koje Excel prikazuje ili možete da je zanemarite tako što ćete izabrati stavku "Zanemari grešku". Ako zanemarite grešku u određenoj ćeliji, greška u toj ćeliji se neće pojaviti u daljim proverama grešaka. Međutim, možete da poništite sve prethodno zanemarene greške da bi one mogle ponovo da se pojavljuju.

Uključivanje ili isključivanje pravila za proveru grešaka

1. Za Excel u operativnom sistemu Windows, idite nastavke "Opcije>datoteke>","Formule" ili
   U programu Excel na Mac računaru izaberite stavku "Proveravanje grešaka" u meniju > "Željene postavke>".

2. U okviru Provera grešaka potvrdite izbor u polju za potvrdu Omogući otkrivanje grešaka u pozadini. Sve pronađene greške označene su trouglom u gornjem levom uglu ćelije.

   

3. Da biste promenili boju trougla koji označava mesto pojavljivanja neke greške, u polju Obeleži greške ovom bojom izaberite željenu boju.

4. U okviru stavke Excel pravila provere grešaka potvrdite ili opozovite izbor u poljima za potvrdu bilo kog od sledećih pravila:

   • Ćelije koje sadrže formule koje kao rezultat daju grešku: Formula ne koristi očekivanu sintaksu, argumente ili tipove podataka. Vrednosti grešaka obuhvataju #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF! i #VALUE!. Svaka vrednost greške ima drugačije uzroke i rešava se na drugi način.

     Napomena

     Ako vrednost greške unesete direktno u ćeliju, ona se skladišti kao vrednost greške, ali se ne označava kao greška. Međutim, ako formula u drugoj ćeliji upućuje na tu ćeliju, formula vraća vrednost greške iz te ćelije.

   • Nedosledno izračunata formula kolone u tabelama: Izračunata kolona može da uključuje pojedinačne formule koje se razlikuju od glavne formule kolone što pravi izuzetak. Izuzeci izračunate kolone prave se kada uradite nešto od sledećeg:

     • U ćeliji izračunate kolone ste otkucali podatke koji se razlikuju od formule.
     • Otkucajte formulu u ćeliji izračunate kolone, a zatim pritisnite kombinaciju tastera Ctrl +Z ili izaberite dugme "Opozovi radnju" na priručnoj traci.
     • Novu formulu ste otkucali u izračunatoj koloni koja već sadrži neke izuzetke.
     • Kopirajte podatke u izračunatu kolonu koja se ne podudara sa formulom izračunate kolone. Ako kopirani podaci sadrže formulu, ona zamenjuje podatke u izračunatoj koloni.
     • Premestite ili izbrišite ćeliju koja se nalazi u nekoj drugoj oblasti radnog lista, a na koju ukazuje neki red iz izračunate kolone.

   • Ćelije koje sadrže godine predstavljene sa 2 cifre: Ćelija sadrži tekstualni datum koji može biti pogrešno protumačen kao pogrešan vek kad se koristi u formulama. Na primer, datum u formuli =YEAR("1/1/31") može da bude 1931. ili 2031. Ovo pravilo upotrebite za proveru nejasnih tekstualnih datuma.

   • Brojevi oblikovani kao tekst ili brojevi kojima prethodi apostrof: Ćelija sadrži brojeve koji su uskladišteni kao tekst. Do ovoga obično dolazi kada se podaci uvoze iz drugih izvora. Brojevi uskladišteni kao tekst mogu da dovedu do neočekivanih rezultata pri sortiranju, tako da je najbolje pretvoriti ih u brojeve. ‘=SUM(A1:A10) se vidi kao tekst.

   • Formule koje nisu dosledne drugim formulama u oblasti: Formula se ne podudara sa obrascem drugih formula u blizini. U većini slučajeva, formule pored drugih formula razlikuju se samo po korišćenim referencama. U sledećem primeru sa četiri susedne formule, Excel prikazuje grešku pored formule =SUM(A10:C10) u ćeliji D4 zato što se susedne formule povećavaju za po jedan red, a ta se povećava za 8 redova – Excel očekuje formulu =SUM(A4:C4).

     

     Ako reference upotrebljene u formuli nisu konzistentne sa onima iz susednih formula, Excel prikazuje grešku.

   • Formule koje izostavljaju ćelije u oblasti: Moguće je da formula automatski ne uključuje reference za podatke koje umećete između originalnog opsega podataka i ćelije koja sadrži formulu. Ovo pravilo upoređuje referencu u formuli sa stvarnim opsegom ćelija susednim u odnosu na ćeliju koja sadrži formulu. Ako susedne ćelije sadrže dodatne vrednosti i nisu prazne, Excel prikazuje grešku pored formule.
     Na primer, kada je ovo pravilo primenjeno, Excel umeće grešku pored formule =SUM(D2:D4) zato što su ćelije D5, D6 i D7 susedne u odnosu na ćelije na koje upućuje formula i na ćeliju koja sadrži formulu (D8), a te ćelije sadrže podatke na koje je trebalo da upućuje formula.

     

   • Otključane ćelije koje sadrže formule: Formula nije zaključana radi zaštite. Sve ćelije na radnom listu su podrazumevano zaključane tako da ih nije moguće promeniti kada je radni list zaštićen. Ovo može da vam pomogne da izbegnete nenamerne greške kao što je slučajno brisanje ili izmena formula. Ova greška ukazuje na to da je ćelija podešena da bude otključana, ali list nije zaštićen. Proverite da biste bili sigurni da ne želite da ćelija bude zaključana.

   • Formule koje upućuju na prazne ćelije: Formula sadrži referencu na praznu ćeliju. Ovo može da prouzrokuje neželjeni rezultat, kao što je prikazano u sledećem primeru.
     Pretpostavimo da želite da izračunate prosek brojeva u sledećoj koloni ćelija. Ako je treća ćelija prazna, nije uključena u izračunavanje i rezultat je 22,75. Ako treća ćelija sadrži 0, rezultat je 18,2.

     

   • Podaci uneti u tabelu su nevažeći: U tabeli postoji greška pri validaciji. Proverite postavku validacije za ćeliju tako što ćete otići na karticu Podaci, > grupa "Alatke za podatke", validacija> podataka.

Pojedinačno ispravljanje uobičajenih grešaka u formuli

1. Izaberite radni list u kome želite da proverite da li ima grešaka.

2. Ukoliko se radni list ručno računa, pritisnite taster F9 za ponovno izračunavanje.
   Ako dijalog "Provera grešaka" nije prikazan, izaberite stavku "Formule>""Nadzor> formula"Provera grešaka.

3. Ako ste prethodno zanemarili greške, možete kasnije ponovo da ih proverite tako što ćete uraditi sledeće:idite do stavke "Opcije>datoteke>" Formule. Za Excel na Mac računaru, izaberite stavku "Proveravanje grešaka" u meniju > "Željene postavke>".
   U odeljku "Provera grešaka " izaberite stavku " Poništi zanemarivanje grešaka>U redu".

   

   Napomena

   Poništavanje zanemarenih grešaka dovodi do poništavanja zanemarivanja svih grešaka u svim listovima u aktivnoj radnoj svesci.

   Savet

   Može biti korisno ako postavite dijalog Provera grešaka neposredno ispod polja za formulu.

   

4. Izaberite jedno od radnih dugmadi sa desne strane dijaloga. Dostupne radnje se razlikuju za svaki tip greške.

5. Izaberite stavku Dalje.

Pojedinačno ispravljanje uobičajenih grešaka u formuli

1. Pored ćelije, izaberite ikonu "Proveravanje grešaka" , a zatim izaberite željenu opciju. Dostupne komande se razlikuju za svaki tip greške, a u prvoj stavci nalazi se opis te greške.
   Ako izaberete opciju "Zanemari grešku", greška će biti označena kako bi pri svakoj sledećoj proveri bila zanemarena.

   

Ispravljanje vrednosti greške #

Ako formula ne može ispravno da izračuna rezultat, Excel prikazuje vrednost greške kao što su #####, #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, i #VALUE!. Svaki tip greške ima drugačije uzroke i različita rešenja.

Sledeća tabela sadrži veze ka člancima koji detaljno opisuju ove greške i kratak opis kako da počnete sa radom.

Tema	Opis
Ispravljanje greške ####	Excel prikazuje ovu grešku kada kolona nije dovoljno široka za prikaz svih znakova u ćeliji ili kada ćelija sadrži negativne vrednosti datuma ili vremena. Na primer, formula koja oduzima datum u budućnosti iz datuma u prošlosti, kao što je = 06/15/2008-07/01/2008, dovodi do negativne vrednosti datuma. Savet: Probajte automatski da prilagodite ćeliju tako što ćete kliknuti dvaput između zaglavlja kolona. Ako je greška ### prikazana zato što Excel ne može da prikaže sve znakove, ovo je ispravlja.
Ispravljanje greške #DIV/0! greška	Excel prikazuje ovu grešku kada je broj podeljen nulom (0) ili ćelijom koja ne sadrži vrednost. Savet: Dodajte rukovalac greškama kao u sledećem primeru, što je =IF(C2,B2/C2,0)
Ispravljanje greške #N/A	Excel prikazuje ovu grešku kada vrednost nije dostupna funkciji ili formuli. Ako koristite funkciju kao što je VLOOKUP, da li ono što pokušavate da pronađete ima podudarnost u opsegu pretrage? Najčešće nema. Probajte da koristite IFERROR da biste sprečili pojavljivanje greške #N/A. U ovom slučaju možete da koristite: =IFERROR(VLOOKUP(D2,$D$6:$E$8,2,TRUE),0) greška
Ispravljanje greške #NAME?	Ova greška se prikazuje kada Excel ne prepoznaje tekst u formuli. Na primer, ime opsega ili ime funkcije može biti napisano pogrešno. Napomena: Ako koristite funkciju, uverite se da je ime funkcije ispravno napisano. U ovom slučaju SUM je napisano pogrešno. Uklonite "e" i Excel će ga ispraviti.
Ispravljanje greške #NULL! greška	Excel prikazuje ovu grešku kada navedete presek dve oblasti koje se ne ukrštaju (unakrsno). Operator za presek je znak za razmak koji razdvaja reference u formuli. Napomena: Uverite se da su opsezi ispravno razdvojeni – oblasti C2: C3 i E4:E6 se ne ukrštaju, tako da unos formule = SUM (C2: C3 E4:E6) vraća #NULL! grešku. Postavljanje zareza između opsega C i E to ispravlja =SUM(C2:C3,E4:E6) greška
Ispravljanje greške #NUM! greška	Excel prikazuje ovu grešku kada formula ili funkcija sadrže nevažeće numeričke vrednosti. Koristite funkciju koja vrši ponavljanje, na primer IRR ili RATE? Ako je tako, greška #NUM! je verovatno zbog toga što funkcija ne može da pronađe rezultat. Pogledajte temu pomoći za korake rešenja.
Ispravljanje greške #REF! greška	Excel prikazuje ovu grešku kada referenca za ćeliju nije važeća. Na primer, možda ste obrisali ćelije na koje su upućivale druge formule ili ste možda nalepili ćelije koje ste premestili iznad ćelija na koje su upućivale druge formule. Da li ste slučajno izbrisali neki red ili kolonu? Izbrisali smo kolonu B u ovoj formuli = SUM(A2,B2,C2) i pogledajte šta se desilo. Koristite "Opozovi radnju" (Ctrl+Z) da biste opozvali brisanje, ponovo napravite formulu ili koristite referencu neprekidnog opsega kao što je: =SUM(A2:C2) koja će se automatski ažurirati kada izbrišete kolonu B.
Ispravljanje greške #VALUE! greška	Excel može da prikaže ovu grešku ako formula uključuje ćelije koje sadrže različite tipove podataka. Koristite matematičke operatore (+, -, *, /, ^) sa različitim tipovima podataka? Ako je tako, probajte da koristite funkciju. U ovom slučaju, funkcija =SUM(F2:F5) bi rešila problem.

Pregledanje formule i njenog rezultata pomoću prozora za praćenje

Kada ćelije nisu vidljive na radnom listu, možete da pogledate te ćelije i njihove formule na traci sa alatkama "Prozor za praćenje". Prozor za praćenje olakšava proveru, nadzor ili potvrdu izračunavanja i rezultata formula u velikim radnim listovima. Pomoću prozora za praćenje ne morate neprestano da se pomerate ili idete u različite delove radnog lista.

Ova traka sa alatkama može da se premesti ili usidri kao i ostale trake sa alatkama. Na primer, možete da je usidrite na dnu prozora. Traka sa alatkama prati sledeća svojstva ćelije: 1) Radna sveska, 2) List, 3) Ime (ako ćelija ima odgovarajući imenovani opseg) 4) Adrese ćelije, 5) Vrednost i 6) Formula.

Dodavanje ćelija u prozor za praćenje

1. Izaberite ćelije koje želite da pratite.
   Da biste izabrali sve ćelije na radnom listu sa formulama, idite na Početna>Uređivanje> , izaberite Pronađi & Izaberite (ili možete da koristite Ctrl+G ili Control+G na Mac računaru)> Idi na specijalne>formule.

   

2. Idite u stavku "Formule",> "Nadzor formula",> izaberite prozor za praćenje.

3. Izaberite stavku "Dodaj praćenje".

   

4. Potvrdite da ste izabrali sve ćelije koje želite da pratite i izaberite "Dodaj".

   

5. Da biste promenili širinu kolone prozora za praćenje, prevucite granicu sa desne strane naslova kolone.

6. Da biste prikazali ćeliju na koju upućuje unos na traci sa alatkama „Prozor za praćenje“, kliknite dvaput na unos.

   Napomena

   Ćelije koje imaju spoljne reference ka drugim radnim sveskama prikazuju se na traci sa alatkama „Prozor za praćenje“ samo kada su druge radne sveske otvorene.

Uklanjanje ćelija iz prozora za praćenje

1. Ako traka sa alatkama "Prozor za praćenje" nije prikazana, idite na stavke "Nadzor>formula" i izaberite stavku ">Prozor za praćenje".

2. Izaberite ćelije koje želite da uklonite.
   Da biste izabrali više ćelija, pritisnite taster CTRL i zatim izaberite ćelije.

3. Izaberite stavku "Izbriši praćenje".

   

Procena ugnežđene formule korak po korak

Ponekad je teško razumeti kako ugnežđena formula izračunava krajnji rezultat, jer postoji nekoliko posrednih izračunavanja i logičkih testova. Međutim, pomoću alatke za procenu formule u programu Excel za Windows možete da vidite različite delove ugnežđene formule procenjene po redosledu izračunavanja formule. Na primer, formulu =IF(AVERAGE(D2:D5)>,50,SUM(E2:E5),0) je lakše razumeti kada možete da vidite sledeće posredne rezultate:

U dijalogu „Provera formule“	Opis
=IF(AVERAGE(D2:D5)>,50,SUM(E2:E5),0)	Ugnežđene formule se prvobitno prikazuju. Funkcije AVERAGE i SUM su ugnežđene unutar funkcije IF. Opseg ćelija D2:D5 sadrži vrednosti 55, 35, 45 i 25, pa je rezultat funkcije AVERAGE(D2:D5) 40.
=IF(40>, 50,SUM(E2:E5),0)	Opseg ćelija D2:D5 sadrži vrednosti 55, 35, 45 i 25, pa je rezultat funkcije AVERAGE(D2:D5) 40.
=IF(False,SUM(E2:E5),0)	Pošto 40 nije veće od 50, izraz u prvom argumentu funkcije IF (argumentu logički_test) ima vrednost False. Funkcija IF vraća vrednost trećeg argumenta (argumenta vrednost_ako_je_netačno). Funkcija SUM se ne procenjuje jer je to drugi argument funkcije IF (argument vrednost_ako_je_tačno) i vraća se samo kad je vrednost izraza True.

1. U programu Excel za Windows izaberite formulu koju želite da procenite. Može da se procenjuje samo jedna ćelija odjednom.
2. Idite na stavke "Nadzor formula"> "Procena>formule".
3. Izaberite stavku Proceni da biste ispitali vrednost podvučenog upućivanja. Rezultat provere se prikazuje kurzivom.
   Ako podvučeni deo formule upućuje na drugu formulu, izaberite stavku Uđi da biste drugu formulu prikazali u polju " Procena ". Izaberite dugme Izađi da biste se vratili na prethodnu ćeliju i formulu.
   Dugme Uđi nije dostupno kada se upućivanje drugi put pojavi u formuli ili ako formula upućuje na ćeliju iz zasebne radne sveske.
4. Nastavite sa izborom opcije "Proveri" dok se ne proveri svaki deo formule.
5. Da biste ponovo videli procenu, izaberite opciju "Ponovo pokreni".
6. Da biste završili procenu, izaberite opciju "Zatvori".

Potrebna vam je dodatna pomoć?

Možete uvek da postavite pitanje stručnjaku u Excel Tech zajednici ili da potražite pomoć u zajednicama.

Takođe pogledajte

Prikazivanje odnosa između formula i ćelija

Način izbegavanja oštećenih formula