Excel formule ne rade: mogući razlozi i kako to riješiti!

Ako radite s formulama u Excelu, prije ili kasnije naići ćete na problem u kojem Excel formule uopće ne rade (ili daju pogrešan rezultat).

Iako bi bilo sjajno da je bilo samo nekoliko mogućih razloga za neispravne formule. Nažalost, postoji previše stvari koje mogu poći po zlu (a često i rade).

No budući da živimo u svijetu koji slijedi Paretovo načelo, ako provjerite ima li uobičajenih problema, vjerojatno će riješiti 80% (ili možda čak 90% ili 95% problema u kojima formule ne rade u Excelu).

I u ovom ću članku istaknuti ona uobičajena pitanja koja su vjerojatno uzrok vaših Excel formule ne rade.

Pa krenimo!

Netočna sintaksa funkcije

Dopustite mi da počnem isticanjem očitog.

Svaka funkcija u Excelu ima određenu sintaksu - poput broja argumenata koje može uzeti ili vrste argumenata koje može prihvatiti.

U mnogim slučajevima razlog zašto vaše Excel formule ne rade ili daje pogrešan rezultat mogao bi biti netočan argument (ili nedostajući argumenti).

Na primjer, funkcija VLOOKUP uzima tri obavezna argumenta i jedan izborni argument.

Ako navedete pogrešan argument ili ne navedete izborni argument (gdje je to potrebno da bi formula radila), dobit ćete pogrešan rezultat.

Na primjer, pretpostavimo da imate skup podataka kao što je prikazano u nastavku gdje trebate znati ocjenu ocjene na ispitu 2 (u ćeliji F2).

Ako koristim donju formulu, dobit ću pogrešan rezultat jer koristim pogrešnu vrijednost u trećem argumentu (onaj koji traži broj indeksa stupca).

= VLOOKUP (A2, A2: C6,2, FALSE)

U ovom slučaju formula izračunava (jer vraća vrijednost), ali rezultat nije točan (umjesto ocjene na ispitu 2, daje ocjenu na ispitu 1).

Drugi primjer gdje morate biti oprezni u vezi s argumentima je kada koristite VLOOKUP s približnim podudaranjem.

Budući da morate upotrijebiti neobavezni argument da biste odredili gdje želite da VLOOKUP izvrši točno podudaranje ili približno podudaranje, ne specificiranje ovoga (ili korištenje pogrešnog argumenta) može uzrokovati probleme.

Ispod je primjer gdje imam podatke o ocjenama za neke studente i želim izvući ocjene na ispitu 1 za studente u tablici s desne strane.

Kad koristim donju formulu VLOOKUP, to mi daje pogrešku za neka imena.

= VLOOKUP (E2, $ A $ 2: $ C $ 6,2)

To se događa jer nisam naveo zadnji argument (koji se koristi za određivanje hoću li napraviti točno ili približno podudaranje). Kad ne navedete posljednji argument, on automatski zadaje približno podudaranje.

Budući da smo u ovom slučaju morali napraviti točno podudaranje, formula vraća pogrešku za neka imena.

Iako sam uzeo primjer funkcije VLOOKUP, u ovom slučaju to je nešto što se može primijeniti na mnoge Excel formule koje imaju i izborne argumente.

Također pročitajte: Identificirajte pogreške pomoću programa Excel za ispravljanje pogrešaka

Dodatni prostori uzrokuju neočekivane rezultate

Teško je pronaći vodeće i zadnje razmake i mogu uzrokovati probleme kada koristite ćeliju koja to ima u formulama.

Na primjer, u donjem primjeru, ako pokušam koristiti VLOOKUP za dohvaćanje ocjene za Marka, to mi daje grešku #N/A (pogreška nije dostupna).

Iako vidim da je formula točna i da je naziv 'Označi' jasno da postoji popis, ono što ne vidim je da u ćeliji postoji zadnji prostor s imenom (u ćeliji D2).

Excel ne smatra sadržaj tih dviju ćelija istim pa stoga smatra da se ne podudara pri dohvaćanju vrijednosti pomoću VLOOKUP -a (ili bi to mogla biti bilo koja druga formula za pretraživanje).

Da biste riješili ovaj problem, morate ukloniti ove dodatne razmake.

To možete učiniti na bilo koji od sljedećih načina:

1. Očistite ćeliju i uklonite sve početne/zadnje razmake prije nego što je upotrijebite u formulama
2. Upotrijebite funkciju TRIM unutar formule kako biste bili sigurni da su svi vodeći/krajnji/dvostruki razmaci zanemareni.

U gornjem primjeru umjesto toga možete upotrijebiti donju formulu kako biste bili sigurni da radi.

= VLOOKUP (TRIM (D2), $ A $ 2: $ B $ 6,2,0)

Iako sam uzeo primjer VLOOKUP -a, ovo je također čest problem pri radu s TEXT funkcijama.

Na primjer, ako koristim funkciju LEN za brojanje ukupnog broja znakova u ćeliji, ako postoje vodeći ili krajnji razmaci, oni bi se također prebrojali i dali pogrešan rezultat.

Odnijeti / Kako popraviti: Ako je moguće, očistite svoje podatke uklanjanjem vodećih/zadnjih ili dvostrukih razmaka prije nego što ih upotrebite u formulama. Ako ne možete promijeniti izvorne podatke, upotrijebite funkciju TRIM u formulama kako biste to riješili.

Korištenje ručnog izračuna umjesto automatskog

Ova jedna postavka može vas izludjeti (ako ne znate da je to uzrok svih problema).

Excel ima dva načina izračuna - Automatski i Priručnik.

Prema zadanim postavkama, automatski način rada je omogućen, što znači da u slučaju da koristim formulu ili unesem bilo kakve promjene u postojeće formule, on automatski (i odmah) vrši izračun i daje mi rezultat.

Ovo je postavka na koju smo svi navikli.

U automatskim postavkama, kad god napravite bilo kakvu promjenu na radnom listu (kao što je unos nove formule za čak i neki tekst u ćeliji), Excel automatski ponovno izračunava sve (da, sve).

No, u nekim slučajevima ljudi omogućuju postavku ručnog izračuna.

To se uglavnom radi kada imate tešku Excel datoteku s puno podataka i formula. U takvim slučajevima možda nećete htjeti da Excel ponovno izračuna sve kada napravite male izmjene (jer može proći nekoliko sekundi ili čak minuta) da se ovo ponovno izračunavanje dovrši.

Ako omogućite ručni izračun, Excel neće izračunati ako ga ne prisilite.

Zbog toga možete pomisliti da se vaša formula ne računa.

Sve što trebate učiniti u ovom slučaju je ili vratiti izračun na automatsko ili prisiliti ponovno izračunavanje pritiskom na tipku F9.

U nastavku su navedeni koraci za promjenu izračuna iz ručnog u automatski:

1. Kliknite karticu Formula
2. Kliknite na Opcije izračuna
3. Odaberite Automatski

Važno: U slučaju da promijenite izračun iz ručnog u automatski, dobra je ideja stvoriti sigurnosnu kopiju radne knjige (samo u slučaju da zbog toga vaša radna knjiga visi ili Excel padne)

Odnijeti / Kako popraviti: Ako primijetite da vaše formule ne daju očekivani rezultat, pokušajte nešto jednostavno u bilo kojoj ćeliji (poput dodavanja 1 postojećoj formuli. Nakon što identificirate problem kao onaj u kojem je potrebno promijeniti način izračuna, izvršite izračun sile pomoću F9.

Brisanje redaka/stupca/ćelija koje vode do #REF! Pogreška

Jedna od stvari koja može imati poguban učinak na vaše postojeće formule u Excelu je kada izbrišete bilo koji redak/stupac koji je korišten u izračunima.

Kad se to ponekad dogodi, Excel sam prilagođava referencu i osigurava da formule rade dobro.

I ponekad… ne može.

Srećom, jedan jasan pokazatelj koji dobijete kada se formule razbiju pri brisanju ćelija/redaka/stupaca je #REF! greška u ćelijama. Ovo je referentna pogreška koja vam govori da postoji problem s referencama u formuli.

Dopustite mi da vam na primjeru pokažem što mislim.

U nastavku sam upotrijebio formulu SUM za dodavanje ćelija A2: A6.

Sada, ako izbrišem bilo koju od ovih ćelija/redaka, formula SUM vratit će #REF! pogreška. To se događa jer kada sam izbrisao redak, formula ne zna na što se sada poziva.

Možete vidjeti da je treći argument u formuli postao #REF! (što se ranije odnosilo na ćeliju koju smo izbrisali).

Odnijeti / Kako popraviti: Prije nego što izbrišete sve podatke koji se koriste u formulama, provjerite da nema brisanja nakon brisanja. Također se preporučuje da redovito izrađujete sigurnosnu kopiju svog rada kako biste bili sigurni da uvijek imate na čemu da se oslonite.

Neispravno postavljanje zagrada (BODMAS)

Kako vaše formule postaju sve veće i složenije, dobro je upotrijebiti zagrade kako biste bili potpuno jasni koji dio pripada zajedno.

U nekim slučajevima zagrade se mogu nalaziti na pogrešnom mjestu, što vam može dati pogrešan rezultat ili pogrešku.

U nekim slučajevima, preporučuje se korištenje zagrada kako biste bili sigurni da formula razumije što je potrebno prvo grupirati i izračunati.

Na primjer, pretpostavimo da imate sljedeću formulu:

=5+10*50

U gornjoj formuli rezultat je 505 jer Excel prvo vrši množenje, a zatim zbrajanje (jer postoji red prvenstva kada su operatori u pitanju).

Ako želite da prvo napravi zbrajanje, a zatim množenje, morate upotrijebiti donju formulu:

=(5+10)*50

U nekim vam slučajevima redoslijed prvenstva može pomoći, ali ipak se preporučuje da upotrijebite zagrade kako biste izbjegli zabunu.

Također, ako vas zanima, dolje je redoslijed prioriteta za različite operatore koji se često koriste u formulama:

Operater	Opis	Red prvenstva
: (dvotočka)	Domet	1
(jedan razmak)	Križanje	2
, (zarez)	unija	3
-	Negacija (kao u -1)	4
%	Postotak	5
^	Eksponentiranje	6
* i /	Množenje i dijeljenje	7
+ i -	Zbrajanje i oduzimanje	8
&	Konkatenacija	9
= =	Usporedba	10

Odnijeti / Kako popraviti: Uvijek koristite zagrade kako biste izbjegli zabunu, čak i ako znate redoslijed prvenstva i ispravno ga koristite. Zagrađivanje olakšava reviziju formula.

Neispravna upotreba apsolutnih/relativnih referenci stanica

Kada kopirate i zalijepite formule u Excel, on automatski prilagođava reference. Ponekad želite upravo to (uglavnom kada kopirate formule u stupac), a ponekad ne želite da se to dogodi.

Apsolutna referenca je kada popravite referencu ćelije (ili referencu raspona) tako da se ne promijeni kada kopirate i zalijepite formule, a relativna referenca je ona koja se mijenja.

Ovdje možete pročitati više o apsolutnim, relativnim i mješovitim referencama.

Možda ćete dobiti netočan rezultat u slučaju da zaboravite promijeniti referencu na apsolutnu (ili obrnuto). To mi se često događa kada koristim formule za pretraživanje.

Dopustite mi da vam pokažem primjer.

U nastavku imam skup podataka u kojem želim dohvatiti rezultat na ispitu 1 za imena u stupcu E (jednostavan slučaj upotrebe VLOOKUP -a)

Ispod je formula koju koristim u ćeliji F2, a zatim kopiram u sve ćelije ispod nje:

= VLOOKUP (E2, A2: B6,2,0)

Kao što vidite da ova formula u nekim slučajevima daje pogrešku.

To se događa jer nisam zaključao argument polja tablice - to je A2: B6 u ćeliji F2, dok je to trebalo biti $ A $ 2: $ B $ 6

Budući da su ti znakovi dolara ispred broja retka i abecede stupca u referenci ćelije, primoravam Excel da te reference ćelija zadrži fiksnim. Dakle, čak i kad prepišem ovu formulu, tablični niz će se i dalje odnositi na A2: B6

Profesionalni savjet: Da biste relativnu referencu pretvorili u apsolutnu, odaberite tu referencu ćelije unutar ćelije i pritisnite tipku F4. Primijetili biste da se mijenja dodavanjem znakova dolara. Možete nastaviti pritiskati F4 dok ne dobijete željenu referencu.

Neispravno upućivanje na nazive listova / radne sveske

Kada se u formuli pozivate na druge listove ili radne knjige, morate slijediti određeni format. U slučaju da je format netočan, dobit ćete pogrešku.

Na primjer, ako se želim referirati na ćeliju A1 u Sheet2, referenca bi bila = List2! A1 (gdje se iza naziva lista nalazi uskličnik)

A u slučaju da u nazivu lista postoji više riječi (recimo da je to primjer podataka), referenca bi bila = 'Primjeri podataka'! A1 (gdje je ime zatvoreno pod navodnicima iza kojih slijedi uskličnik).

U slučaju da se pozivate na vanjsku radnu knjigu (recimo da se pozivate na ćeliju A1 u "Primjer lista" u radnoj knjizi pod nazivom "Primjer radne knjige"), referenca će biti prikazana u nastavku:

= '[Primjer radne knjige.xlsx] Primjer lista'! ​​$ A $ 1

A u slučaju da zatvorite radnu knjigu, referenca bi se promijenila tako da uključuje čitavu putanju radne knjige (kao što je prikazano u nastavku):

= 'C: \ Users \ sumit \ Desktop \ [Example Workbook.xlsx] Primjer lista'! ​​$ A $ 1

U slučaju da promijenite naziv radne knjige ili radnog lista na koji se formula odnosi, dobit ćete #REF! pogreška.

Također pročitajte: Kako pronaći vanjske veze i reference u Excelu

Kružne reference

Kružna referenca je kada upućujete (izravno ili neizravno) na istu ćeliju u kojoj se izračunava formula.

Dolje je jednostavan primjer, gdje koristim formulu SUM u ćeliji A4 dok je koristim u samom izračunu.

= ZBIR (A1: A4)

Iako vam Excel prikazuje upit koji vas obavještava o kružnoj referenci, to neće učiniti za svaku instancu. To vam može dati pogrešan rezultat (bez ikakvog upozorenja).

U slučaju da sumnjate da je kružna referenca u igri, možete provjeriti koje ćelije to imaju.

Da biste to učinili, kliknite karticu Formula i u grupi ‘Revizija formule’ kliknite padajuću ikonu Provjera pogrešaka (mala strelica usmjerena prema dolje).

Zadržite pokazivač miša iznad opcije Kružna referenca i ona će vam pokazati ćeliju koja ima problem s kružnom referencom.

Ćelije oblikovane kao tekst

Ako se nađete u situaciji da čim upišete formulu kao hit enter, umjesto vrijednosti vidite formulu, to je jasan slučaj da se ćelija formatira kao tekst.

Kad se ćelija formatira kao tekst, ona formulu smatra tekstualnim nizom i prikazuje je takvu kakva jest.

Ne prisiljava ga na izračunavanje i davanje rezultata.

I ima lako rješenje.

1. Promijenite format u "Općenito" iz "Tekst" (nalazi se na kartici Početna u grupi Brojevi)
2. Idite na ćeliju koja ima formulu, uđite u način uređivanja (upotrijebite F2 ili dvaput kliknite na ćeliju) i pritisnite Enter

U slučaju da gornji koraci ne riješe problem, potrebno je provjeriti ima li ćelija apostrof na početku. Mnogi ljudi dodaju apostrof za pretvaranje formula i brojeva u tekst.

Ako postoji apostrof, možete ga jednostavno ukloniti.

Automatski se tekst pretvara u datume

Excel ima tu lošu naviku pretvaranja koje izgleda kao datum u stvarni datum. Na primjer, ako unesete 1/1, Excel bi to pretvorio u 01. siječnja tekuće godine.

U nekim slučajevima to može biti upravo ono što želite, a u nekim slučajevima to može djelovati protiv vas.

Budući da Excel pohranjuje vrijednosti datuma i vremena kao brojeve, čim unesete 1/1, pretvara ih u broj koji predstavlja 1. siječnja tekuće godine. U mom slučaju, kad to učinim, pretvara se u broj 43831 (za 01-01-2020).

To bi moglo poremetiti vaše formule ako ove ćelije koristite kao argument u formuli.

Kako to popraviti?

Opet, ne želimo da nam Excel automatski odabere format, pa moramo sami jasno odrediti format.

U nastavku su navedeni koraci za promjenu formata u tekst tako da se tekst ne pretvara automatski u datume:

1. Odaberite ćelije/raspon u kojem želite promijeniti format
2. Kliknite na karticu Početna
3. U grupi Broj kliknite padajući izbornik Oblikovanje
4. Kliknite na Tekst

Sada, kad god unesete bilo što u odabrane ćelije, to će se smatrati tekstom, a ne mijenjati automatski.

Napomena: Gore navedeni koraci funkcionirali bi samo za podatke unesene nakon promjene oblikovanja. Neće promijeniti tekst koji je pretvoren u datum prije nego što ste izvršili ovu promjenu oblikovanja.

Drugi primjer gdje ovo može biti jako frustrirajuće je kada unesete tekst/broj koji ima vodeće nule. Excel automatski uklanja te vodeće nule jer ih smatra beskorisnima. Na primjer, ako unesete 0001 u ćeliju, Excel će je promijeniti u 1. U slučaju da želite zadržati ove vodeće nule, koristite gore navedene korake.

Skriveni redovi/stupci mogu dati neočekivane rezultate

Ovo nije slučaj da formula daje pogrešan rezultat, već da koristite pogrešnu formulu.

Na primjer, pretpostavimo da imate skup podataka kao što je prikazano u nastavku i želim dobiti zbroj svih vidljivih ćelija u stupcu C.

U ćeliji C12 koristio sam funkciju SUM kako bih dobio ukupnu vrijednost prodaje za sve navedene zapise.

Zasada je dobro!

Sada primjenjujem filtar na stupac stavke kako bih prikazao samo zapise o prodaji pisača.

I tu je problem - formula u ćeliji C12 i dalje pokazuje isti rezultat - tj. Zbroj svih zapisa.

Kao što sam rekao, formula ne daje pogrešan rezultat. Zapravo, funkcija SUM radi sasvim dobro.

Problem je u tome što smo ovdje upotrijebili pogrešnu formulu.

SUM funkcija ne može uzeti u obzir filtrirane podatke i dati vam rezultat za sve ćelije (skrivene ili vidljive). Ako želite dobiti samo zbroj/broj/prosjek vidljivih ćelija, upotrijebite funkcije SUBTOTAL ili AGGREGATE.

Ključno za poneti - Shvatite ispravnu uporabu i ograničenja funkcije u Excelu.

Ovo su neki od uobičajenih uzroka koje sam vidio gdje ste Excel formule možda neće raditi ili dati neočekivane ili pogrešne rezultate. Siguran sam da postoji mnogo više razloga zašto formula u Excelu ne radi ili se ažurira.

Ako znate neki drugi razlog (možda nešto što vas često nervira), javite mi u odjeljku za komentare.

Nadam se da vam je ovaj vodič bio koristan!