...
Eroare Excel · #REF!, referințe relative și referințe circulare

#REF! în OFFSET (deplasarea iese în afara foii)

Pe scurt

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

Greșit
EXCEL
C2: =AVERAGE(OFFSET(B2,-2,0,3,1))   → #REF!
Corect
EXCEL
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

Greșit
EXCEL
=AVERAGE(OFFSET(B2,-2,0,3,1))
Corect
EXCEL
=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

Greșit
EXCEL
=SUM(OFFSET(B1,1,0,COUNT(B2:B13),1))   (fără date: înălțime 0)
Corect
EXCEL
=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ă tu

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?

Alte forme ale mesajului

  • #REF!
  • #REF! în OFFSET
  • OFFSET 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

← Toate erorile Excel

Tudor Andrei Dumitrescu
Cine a explicat eroarea

Tudor Andrei Dumitrescu

Senior Software Developer · fondatorul Academiei Programatorilor

LinkedIn
  1. 2019 Junior JavaCognizant Softvision
  2. 2021 Mid JavaCognizant Softvision
  3. 2022 Team LeaderEndava
  4. 2023 Senior Software DeveloperUniCredit
  5. Acum Senior Software DeveloperHCLTech
Certificat de absolvire Instructor / Preparator formare, Tudor Andrei Dumitrescu
Certificat de formator autorizatMinisterul Muncii și Ministerul Educației · 2022
Certificat de absolvire Instructor / Preparator formare, Tudor Andrei Dumitrescu

Certificat de formator autorizat · Ministerul Muncii și Ministerul Educației · 2022

Din 2020 construiește Academia Programatorilor, iar din 2026 și JoburiIT.ro. Fiecare eroare de aici a fost reprodusă în editorul nostru: codul greșit chiar dă mesajul din titlu, iar reparația chiar funcționează.

Î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.