Funcția Excel OFFSET (Exemplu + Video)
Când se utilizează funcția Excel OFFSET
Funcția Excel OFFSET poate fi utilizată atunci când doriți să obțineți o referință care compensează numărul specificat de rânduri și coloane de la punctul de pornire.
Ce returnează
Returnează referința către care indică funcția OFFSET.
Sintaxă
= OFFSET (referință, rânduri, coluri, [înălțime], [lățime])
Argumente de intrare
- referință - Referința de la care doriți să compensați. Aceasta ar putea fi o referință de celulă sau o gamă de celule adiacente.
- rânduri - numărul de rânduri de compensat. Dacă utilizați un număr pozitiv, acesta se compensează cu rândurile de mai jos, iar dacă se utilizează un număr negativ, atunci se compensează cu rândurile de mai sus.
- cols - numărul de coloane de compensat. Dacă utilizați un număr pozitiv, acesta se compensează cu coloanele din dreapta și, dacă se utilizează un număr negativ, atunci se compensează cu coloanele din stânga.
- [înălțime] - acesta este un număr care reprezintă numărul de rânduri din referința returnată.
- [lățime] - acesta este un număr care reprezintă numărul de coloane din referința returnată.
Înțelegerea bazelor funcției OFFSET Excel
Funcția Excel OFFSET este probabil una dintre cele mai confuze funcții din Excel.
Să luăm un exemplu simplu de joc de șah. Există o piesă în șah numită Rook (cunoscută și sub numele de turn, marchiz sau rector).
[Amabilitatea imaginii: Wikipedia minunată]
Acum, ca toate celelalte piese de șah, un Rook are o cale fixă pe care se poate deplasa pe tablă. Poate merge drept (spre dreapta / stânga sau sus / jos pe tablă), dar nu poate merge în diagonală.
De exemplu, să presupunem că avem o tablă de șah în Excel (așa cum se arată mai jos), într-o casetă dată (D5 în acest caz), Rook poate merge doar în cele patru direcții evidențiate.
Acum, dacă îți cer să iei Rook-ul din poziția actuală pe cea evidențiată în galben, ce îi vei spune Rook-ului?
Îi vei cere să facă doi pași în jos și doi pași spre dreapta … nu-i așa?
Și aceasta este o apropiere apropiată a modului în care funcționează funcția OFFSET.
Acum să vedem ce înseamnă acest lucru în Excel. Vreau să încep cu celula D5 (care este locul Rook) și apoi să merg două rânduri în jos și două coloane la dreapta și să aduc valoarea de la celula de acolo.
Formula ar fi:
= OFFSET (de unde să înceapă, câte rânduri în jos, câte coloane la dreapta)
După cum puteți vedea, formula din exemplul de mai sus din celula J1 este = OFFSET (D5,2,2).
A început la D5, apoi a mers două rânduri în jos și două coloane la dreapta și a ajuns la celula F7. Apoi a returnat valoarea din celula F7.
În exemplul de mai sus, am analizat funcția OFFSET cu 3 argumente. Dar există încă două argumente opționale care pot fi folosite.
Să vedem un exemplu simplu aici:
Să presupunem că doriți să utilizați referința la celula A1 (în galben) și doriți să faceți referire la întregul interval evidențiat în albastru (C2: E4) într-o formulă.
Cum ați face asta folosind o tastatură? Mai întâi ați merge la celula C2, apoi ați selecta toate celulele din C2: E4.
Acum să vedem cum se face acest lucru folosind formula OFFSET:
= OFFSET (A1,1,2,3,3)
Dacă utilizați această formulă într-o celulă, aceasta va returna un #VALUE! eroare, dar dacă intrați în modul de editare și selectați formula și apăsați F9, veți vedea că returnează toate valorile care sunt evidențiate în albastru.
Acum să vedem cum funcționează această formulă:
= OFFSET (A1,1,2,3,3)
- Primul argument este celula de unde ar trebui să înceapă.
- Al doilea argument este 1, care spune Excel să returneze o referință care a fost OFFSET cu 1 rând.
- Al treilea argument este 2, care spune Excel să returneze o referință care a fost OFFSET cu 2 coloane.
- Al patrulea argument este 3. Aceasta specifică faptul că referința ar trebui să acopere 3 rânduri. Aceasta se numește argumentul înălțimii.
- Al cincilea argument este 3. Aceasta specifică faptul că referința ar trebui să acopere 3 coloane. Acesta se numește argumentul lățimii.
Acum că aveți referința la o gamă de celule (C2: E4), o puteți folosi în cadrul altor funcții (cum ar fi SUM, COUNT, MAX, MEDIE).
Sperăm că aveți o bună înțelegere de bază despre utilizarea funcției Excel OFFSET. Acum să aruncăm o privire la câteva exemple practice de utilizare a funcției OFFSET.
Funcția Excel OFFSET - Exemple
Iată două exemple de utilizare a funcției Excel OFFSET.
Exemplul 1 - Găsirea ultimei celule umplute în coloană
Să presupunem că aveți câteva date într-o coloană, așa cum se arată mai jos:
Pentru a găsi ultima celulă din coloană, utilizați următoarea formulă:
= OFFSET (A1, COUNT (A: A) -1,0)
Această formulă presupune că nu există valori în afară de cele afișate și aceste celule nu au o celulă goală în ea. Funcționează prin numărarea numărului total de celule care sunt umplute și compensează celula A1 în consecință.
De exemplu, în acest caz, există 8 valori, deci COUNT (A: A) returnează 8. Compensăm celula A1 cu 7 pentru a obține ultima valoare.
Exemplul 2 - Crearea unui drop down dinamic
Puteți extinde prezentările conceptuale din Exemplul 1 pentru a crea intervale denumite dinamice. Acestea ar putea fi utile dacă utilizați intervalele numite în formule sau în crearea de meniuri derulante și este probabil să adăugați / ștergeți date din referința care face intervalul numit.
Iată un exemplu:
Rețineți că meniul derulant se ajustează automat la adăugarea sau eliminarea anului.
Acest lucru se întâmplă deoarece formula care este utilizată pentru a crea meniul derulant este dinamică și identifică orice adăugare sau ștergere și ajustează intervalul în consecință.
Iată cum puteți face acest lucru:
- Selectați celula în care doriți să introduceți meniul derulant.
- Accesați Date -> Instrumente de date -> Validare date.
- În caseta de dialog Validare date, în fila Setări selectați Listă din meniul derulant.
- În sursă, introduceți următoarea formulă: = OFFSET (A1,0,0, COUNT (A: A), 1)
- Faceți clic pe OK.
Să vedem cum funcționează această formulă:
- Primele trei argumente ale funcției OFFSET sunt A1, 0 și 0. Aceasta înseamnă în esență că ar returna aceeași celulă de referință (care este A1).
- Al patrulea argument este pentru înălțime și aici funcția COUNT returnează numărul total de articole din listă. Se presupune că nu există spații goale în listă.
- Al cincilea argument este 1, care indică faptul că lățimea coloanei ar trebui să fie una.
Note Aditionale:
- OFFSET este o funcție volatilă (utilizați cu precauție).
- Se recalculează ori de câte ori registrul de lucru Excel este deschis sau ori de câte ori este declanșat un calcul în foaia de lucru. Acest lucru s-ar putea adăuga la timpul de procesare și vă poate încetini registrul de lucru.
- Dacă valoarea înălțimii sau lățimii este omisă, aceasta este luată ca referință.
- Dacă rânduri și cols sunt numere negative, atunci direcția de compensare este inversată.
Alternative de funcții Excel OFFSET
Datorită unora dintre limitările funcției Excel OFFSET, mulți doriți să luați în considerare alternative la aceasta:
- Funcția INDEX: funcția INDEX poate fi utilizată și pentru a returna o referință de celulă. Faceți clic aici pentru a vedea un exemplu despre cum să creați o gamă dinamică denumită folosind funcția INDEX.
- Tabel Excel: Dacă utilizați referințe structurate în Tabel Excel, nu trebuie să vă faceți griji cu privire la adăugarea de noi date și la necesitatea de a ajusta formulele.
Funcția Excel OFFSET - Tutorial video
- Funcția Excel VLOOKUP.
- Funcția Excel HLOOKUP.
- Funcția Excel INDEX.
- Funcția Excel INDIRECT.
- Funcția Excel MATCH.
