#REF! în OFFSET (deplasarea iese în afara foii)
OFFSET dă #REF! când deplasarea pornește de la o celulă și ajunge deasupra rândului 1 sau la stânga coloanei A. Apare des la medii mobile copiate pe primele rânduri. Pornește formula de la rândul care are destul istoric sau verifică poziția cu IF.
Explicat de Tudor Andrei Dumitrescu, Senior Software Developer, fondatorul Academiei ProgramatorilorActualizat la
Ce înseamnă
OFFSET(referință, rânduri, coloane, [înălțime], [lățime]) întoarce un interval aflat la o distanță dată față de celula de plecare. Valorile negative înseamnă „în sus” și „la stânga”. Dacă intervalul rezultat ar începe deasupra rândului 1 sau înaintea coloanei A, nu există în foaie, iar OFFSET întoarce #REF!.
Cazul tipic: o medie mobilă pe 3 luni, =AVERAGE(OFFSET(B2,-2,0,3,1)), adică „luna curentă și cele două dinainte”. Pe rândul 2, două rânduri mai sus înseamnă rândul 0, care nu există. Pe rândul 3 nu dă eroare, dar include antetul și calculează o medie pe doar două luni, ceea ce poate fi la fel de înșelător.
Aceeași eroare apare când înălțimea sau lățimea e 0 sau negativă, ori când deplasarea trece de ultimul rând al foii.
De ce apare: cauzele frecvente
1Medie mobilă pornită prea sus
C2: =AVERAGE(OFFSET(B2,-2,0,3,1)) → #REF!C4: =AVERAGE(OFFSET(B4,-2,0,3,1)) (C2:C3 goale)De la rândul 4, cele trei luni (rândurile 2–4) există toate.
2Formula trebuie să rămână pe toate rândurile
=AVERAGE(OFFSET(B2,-2,0,3,1))=IF(ROW()<4,"",AVERAGE(OFFSET(B2,-2,0,3,1)))IF evaluează OFFSET doar când există trei luni de istoric.
3Înălțime 0 calculată dintr-o numărare
=SUM(OFFSET(B1,1,0,COUNT(B2:B13),1)) (fără date: înălțime 0)=IF(COUNT(B2:B13)=0,0,SUM(OFFSET(B1,1,0,COUNT(B2:B13),1)))OFFSET nu acceptă un interval cu 0 rânduri.
Repară eroarea, direct în pagină
În C2:C7, media mobilă pe 3 luni se calculează cu =AVERAGE(OFFSET(B2,-2,0,3,1)), copiată pe toate rândurile. C2 afișează #REF!, iar C3 face media pe doar două luni. Repară foaia ca media mobilă să apară doar de la martie (C4), iar C2 și C3 să rămână goale.
Indiciu
Ianuarie și februarie nu au încă trei luni de istoric: ce faci cu celulele lor?
Reparația
Golește C2 și C3 (selectează-le și apasă Delete). Formulele din C4:C7 rămân:
=AVERAGE(OFFSET(B4,-2,0,3,1))C4 afișează 110.
De ce merge
Pe rândul 2, OFFSET urca două rânduri, deasupra rândului 1, într-un loc care nu există, așa că întorcea #REF!. Pe rândul 3 lua și antetul, deci media se făcea pe două luni. Din martie (rândul 4) există trei luni complete: (100 + 120 + 110) / 3 = 110. Golind C2 și C3, rămân doar mediile calculate corect.
Alte forme ale mesajului
#REF!#REF! în OFFSETOFFSET returns #REF!#REF! în DEPLASARE
Cum o eviți pe viitor
- La formulele cu deplasare negativă, începe calculul de pe primul rând care are destul istoric.
- Când OFFSET primește înălțimea dintr-o numărare, tratează cazul 0.
- Pentru intervale dinamice simple,
INDEX(B2:INDEX(B:B,COUNTA(B:B))) e mai ușor de urmărit și nu se recalculează la orice modificare, cum face OFFSET.
Întrebări frecvente
De ce rândul 3 nu dă eroare, dar rezultatul e greșit?
Pe rândul 3, OFFSET ia rândurile 1–3. Rândul 1 e antetul (text), pe care AVERAGE îl ignoră, deci media se face doar pe două luni, fără niciun semnal.
OFFSET încetinește registrul?
OFFSET e o funcție volatilă: se recalculează la orice modificare din registru. La câteva sute de formule nu se simte; la zeci de mii, da.
Erori înrudite
#REF! în INDIRECT #REF! în INDIRECT înseamnă că textul primit nu este o referință validă: lipsește semnul !, numele foii e scris greșit sau foaia nu există. Construiește textul exact ca o referință scrisă de mână, de exemplu =INDIRECT("'"&A2&"'!B5"). #REF! după ștergerea unui rând sau a unei coloane #REF! înseamnă că formula trimite la o celulă care nu mai există, de obicei pentru că ai șters rândul, coloana sau foaia la care se referea. Rescrie referința (de preferat cu un interval, =SUM(B2:B6)) sau dă Undo imediat după ștergere. #REF! în VLOOKUP #REF! în VLOOKUP înseamnă că al treilea argument, col_index_num, cere o coloană care nu există în interval: de exemplu 4 pentru un interval de 3 coloane. Micșorează numărul sau lărgește intervalul. Învață Excel de la zero, cu exerciții la fiecare lecție
Curs Excel: lecții scurte, fiecare cu un exercițiu verificat automat, direct în browser. Primele 20 lecții sunt gratuite.
