Probabil credeți că funcția OFFSET () este acrobatică în Excel ... Este de fapt simplă și sună foarte complicat. Voi încerca să explic cum funcționează și sper că, după citire, veți putea să vă impresionați colegii care nu au dat încă peste acest articol. 🙂

Cu OFFSET () puteți crea diagrame dinamice în Excel:

offset

… Sau conectați două tabele dacă într-unul datele sunt în rânduri separate și în celălalt în coloane:

Funcția OFFSET () din Excel returnează conținutul uneia sau mai multor celule. (perioadă!)

Rezultatul va fi similar cu tastarea = A2 în celula A1. Astfel, prima celulă va afișa conținutul celei de-a doua.

Cu toate acestea, diferența este că, cu OFFSET (), aveți mai multă flexibilitate și Excel poate îndeplini anumite condiții atunci când găsiți celula al cărei conținut doriți să îl utilizați.

Principalul avantaj al funcției OFFSET () este că nu conține o adresă de celulă, ci un „ghid” pentru câte rânduri în jos și câte coloane la dreapta este celula pe care o căutăm în raport cu un punct de plecare. Foarte ușor, numărul de rânduri și coloane poate fi înlocuit cu o formulă logică, iar Excel va da întotdeauna conținutul celulei care îndeplinește noua condiție logică.

În cea mai simplă formă a formulei, îi spunem Excel: „Dacă vedeți această celulă, coborâți 1 rând din ea, mergeți la o coloană din dreapta și dați-mi conținutul celulei care este acolo”.

Iată sintaxa cu care scriem funcția OFFSET ().

= OFFSET (referințe, rânduri, col,Înălțime lățime)

  • referinţă - adresa celulei care va servi drept punct de plecare pentru localizarea rezultatului căutat de formulă. De obicei, celula de sus, cea mai stângă a tabelului în care se află celula căutată este specificată pentru referință.
  • rânduri - un număr care indică câte rânduri în jos (+) sau în sus (-) de la punctul de referință se află celula pe care o căutăm.
  • cols - un număr care arată câte coloane la dreapta (+) sau la stânga (-) punctului de referință (referință) este celula pe care o căutăm.
  • înălțime - nu trebuie completat. Dacă este lăsat necompletat, Excel presupune că căutăm o zonă de celule cu „înălțime” 1 - adică. o zonă de celule care este într-un rând.
  • lățime - nu trebuie să fie umplută. Dacă este lăsat necompletat, Excel presupune că căutăm o zonă de celule cu o „lățime” de 1 - adică. o zonă de celule care se află într-o singură coloană.

Înălțimea și lățimea lăsate necompletate sau scrise cu o valoare de 1 înseamnă că rezultatul este 1 celulă (zona situată pe un singur rând și o singură coloană la un moment dat).

De exemplu, dacă în celula B2 vrem să înregistrăm aportul zilnic de calorii pentru 01/01/2013 folosind OFFSET () pentru a găsi celula cu această valoare dintr-un tabel suplimentar, iată cum ar arăta formula:

Scrierea $ G $ 1 (Data) pentru referință înseamnă că acesta va fi punctul de plecare de la care Excel va începe să găsească celula. În câmpul Rânduri scriem 1, ceea ce înseamnă că celula căutată este la un rând în jos față de celula de pornire, iar cea din câmpul Cols înseamnă că este, de asemenea, o coloană în dreapta celulei de pornire. Rezultatul în acest caz va fi H2 (1940).

La prima vedere, se pare că ne complicăm fără sens viața cu lucruri evidente - o linie în jos, o coloană la dreapta ... bla-bla. Lucrul cu adevărat util în acest caz este că, în loc să scriem un număr solid pentru un rând/coloană, putem înlocui numărul cu o formulă care dă un număr diferit în condiții diferite.
De exemplu IF (), al cărui rezultat, dacă condiția este îndeplinită să fie 1 și dacă nu este îndeplinită - 2. Și dacă scrieți această formulă IF () în loc de cols în OFFSET () - și veți avea o dinamică rezultat.

În loc de IF () puteți utiliza oricare dintre opțiunile „Comenzi de formular” - butoanele radio, bara de derulare și altele asemenea, care conectate la o celulă din Excel dau un număr în schimbare.

Iată un exemplu de realizare a unui grafic dinamic în care datele să fie derulate cu bara de derulare: