Kako ispraviti #REF! pogreška

Pogreška #REF! prikazuje kada se formula odnosi na ćeliju koja nije valjana. To se najčešće događa kad se ćelije na koje se formula referencira izbrišu ili se preko njih zalijepi drugi sadržaj.

U sljedećem se primjeru koristi formula =SUM(B2,C2,D2) u stupcu E.

Formula koja koristi eksplicitne reference ćelija poput =SUM(B2,C2,D2) može uzrokovati pogrešku #REF! ako se stupac izbriše.

Ako izbrišete stupac B, C ili D, to bi izazvalo #REF! pogreška. U tom ćemo slučaju izbrisati stupac C (2007 Prodaja), a formula će sada pročitati =SUM(B2,#REF!,C2). Kada koristite eksplicitne reference na ćelije kao što je ova (pri čemu svaku ćeliju referencirate pojedinačno, odvojene zarezom) i izbrišete referenciran redak ili stupac, Excel ih ne može razriješiti, pa vraća #REF! pogreška. To je primarni razlog zašto se ne preporučuje korištenje eksplicitnih referenci ćelija u funkcijama.

Primjer pogreške #REF! uzrokovane brisanjem stupca.

Rješenje

Ako slučajno izbrišete retke ili stupce, možete odmah na alatnoj traci za brzi pristup kliknuti gumb Poništi (ili pritisnuti CTRL + Z) da biste ih oporavili.
Prilagodite formulu tako da koristi referencu raspona umjesto pojedinačnih ćelija, kao što je =SUM(B2:D2). Sada možete izbrisati bilo koji stupac unutar raspona zbroja, a Excel će automatski prilagoditi formulu. Za zbroj redaka možete koristiti i =SUM(B2:B5 ).

U sljedećem primjeru =VLOOKUP(A8,A2:D5,5,FALSE) će vratiti pogrešku #REF! jer traži vrijednost koju će vratiti iz stupca 5, ali raspon referenci je A:D, što je samo 4 stupca.

Primjer formule VLOOKUP s netočnim rasponom. Formula je =VLOOKU(A8;A2:D5;5;FALSE). U rasponu VLOOKUP nema petog stupca, pa 5 uzrokuje #REF! pogreška.

Rješenje

Prilagodite raspon tako da bude veći ili smanjite vrijednost traženja stupaca tako da odgovara rasponu referenci. =VLOOKUP(A8,A2:E5,5,FALSE) bio bi valjani raspon referenci, kao i =VLOOKUP(A8,A2:D5,4,FALSE).

U ovom primjeru formula =INDEX(B2:E5;5;5) vraća #REF! jer je raspon INDEX 4 retka sa 4 stupca, ali formula traži da vrati ono što se nalazi u petom retku i petom stupcu.

Primjer formule INDEX s referencom raspona koja nije valjana. Formula je =INDEX(B2:E5,5,5), ali raspon je samo 4 retka puta 4 stupca.

Rješenje

Prilagodite reference redaka ili stupaca tako da su unutar raspona traženja funkcije INDEX. =INDEX(B2:E5,4,4) će vratiti valjani rezultat.

U sljedećem primjeru funkcija INDIRECT pokušava referencira radnu knjigu koja je zatvorena, što uzrokuje #REF! pogreška.

Primjer pogreške #REF! uzrokovane formulom INDIRECT koja referencira zatvorenu radnu knjigu.

Rješenje

Otvorite referenciranu radnu knjigu. Naići ćete na istu pogrešku ako se pozivate na zatvorenu radnu knjigu s dinamičkom funkcijom polja.

Strukturirane reference na nazive tablica i stupaca u povezanim radnim knjigama nisu podržane.

Izračunate reference na povezane radne knjige nisu podržane.

Premještanje ili brisanje ćelija uzrokovalo je referencu ćelije koja nije valjana ili funkcija vraća pogrešku reference.

Ako ste koristili vezu Povezivanje i ugrađivanje objekata (OLE) koja vraća #REF! , a zatim pokrenite program koji veza poziva.

Napomena: OLE je tehnologija koja se koristi za zajedničko korištenje informacija između programa.

Ako ste koristili temu Dynamic Data Exchange (DDE) koja vraća #REF! da biste provjerili referencirate li odgovarajuću temu. Ako i dalje primate poruku #REF! provjerite postavke centra za pouzdanost za vanjski sadržaj kao što je navedeno u članku Blokiranje ili deblokiranje vanjskog sadržaja u dokumentima okruženja Microsoft 365.

Napomena: Dynamic Data Exchange (DDE) jeuspostavljeni protokol za razmjenu podataka između programa utemeljenih na sustavu Microsoft Windows.

Problemi s makronaredbama

Ako makronaredba na radni list unese funkciju koja se odnosi na ćeliju iznad funkcije, a ćelija koja sadrži funkciju nalazi se u prvom retku, funkcija će vratiti #REF! jer iznad prvog retka nema ćelija. Provjerite funkciju da biste vidjeli referencira li se argument na ćeliju ili raspon ćelija koji nije valjan. Da bi se ta situacija uzela u obzir, možda će trebati urediti makronaredbu u alatu Visual Basic Editor (VBE).