Funcția Excel OFFSET (cuprins)

  • OFFSET în Excel
  • Formula OFFSET în Excel
  • Cum se utilizează funcția OFFSET în Excel?

OFFSET în Excel

Funcția OFFSET în Excel ajută utilizatorul să returneze o celulă sau o gamă de celule, care este un număr specificat. de rânduri și coloane dintr-o celulă. Putem specifica nr. de rânduri și nr. de coloane care trebuie returnate.

Mai jos este Formula OFFSET în Excel:

Funcția OFFSET în Excel constă din următoarele argumente:

  • Referință: este argumentul pe care dorim să bazăm compensarea. Ar putea fi o referință de celulă sau o gamă de celule adiacente, dacă nu Offset returnează #VALUE! Valoarea erorii.
  • Rânduri: Este nr. de rânduri de compensat. Dacă folosim un număr pozitiv, acesta compensează rândurile de mai jos și dacă este folosit un număr negativ, atunci compensează rândurile de mai sus.
  • Coloane: Este pentru nr. de coloane de compensat. Deci, este același concept ca rândurile, dacă folosim un număr pozitiv, atunci acesta compensează pe coloanele din dreapta și dacă este folosit un număr negativ, atunci compensările sale pe coloanele din stânga.
  • Înălțime: Acest argument este opțional. Ar trebui să fie un număr pozitiv. Este un număr care reprezintă numărul de rânduri din referința returnată.
  • Lățime: Acesta este, de asemenea, un argument opțional. Trebuie să fie un număr pozitiv. Este un număr care reprezintă numărul de coloane din referința returnată.

Note:

  • Funcția OFFSET din Excel returnează #REF! valoarea de eroare, dacă rândurile și coloanele compensează referința peste marginea foii de lucru.
  • În funcția OFFSET, se presupune că este aceeași înălțime sau lățime ca referință dacă înălțimea sau lățimea sunt omise.
  • OFFSET returnează o referință, nu mișcă de fapt nici o celulă sau o gamă de celule și nici nu schimbă selecția. Poate fi utilizat cu orice funcție care așteaptă un argument de referință.

Cum se utilizează funcția OFFSET în Excel?

Funcția OFFSET în Excel este foarte simplă și ușor de utilizat. Vă rugăm să înțelegeți funcția OFFSET în Excel printr-un exemplu de formulă OFFSET.

Puteți descărca acest șablon Excel funcție OFFSET - Șablon Excel funcție OFFSET

Exemplul # 1

Formula Offset returnează o referință de celulă bazată pe un punct de plecare, rânduri și coloane pe care le specificăm. O putem vedea în exemplul de mai jos:

= OFFSET (A1, 3, 1)

Formula spune Excel să ia în considerare celula A1 pentru punctul de pornire (referință), apoi mutați 3 rânduri în jos (rânduri) și 1 coloană spre stânga (argumentul coloanelor). După aceasta, formula OFFSET returnează valoarea în celula B4.

Imaginea din stânga arată că traseul funcției și captură de ecran din dreapta demonstrează modul în care putem folosi formula OFFSET în baza datelor în timp real. Diferența dintre cele două formule este că a doua (din dreapta) include o referință a celulei (D1) în argumentul rândului. Dar, deoarece celula D1 conține numărul 1 și exact același număr apare în argumentul rândurilor primei formule, ambele ar întoarce un rezultat identic - valoarea în B2.

Exemplul # 2 Utilizarea funcției OFFSET pentru sumă

După cum știm că funcția OFFSET poate fi folosită și cu cealaltă funcție Excel, prin urmare, în acest exemplu vom vedea cu funcția SUM.

În imaginea de mai sus, să presupunem că trebuie să aflăm suma mărcilor, atunci putem folosi și funcția OFFSET.

Formula este mai jos:

Deci, în formula de mai sus D5 este prima celulă în care datele încep, care este o referință ca celulă inițială. După aceea, valoarea rândurilor și a coloanelor este 0, deci îl putem pune ca 0, 0. Apoi, există 4 care înseamnă 4 rânduri sub referința pentru care Excel trebuie să calculeze suma și apoi, în sfârșit, este 1, înseamnă că există 1 coloană ca lățime.

Ne putem referi la poza de mai sus pentru a corela exemplul explicat.

Și mai jos este rezultatul:

Trebuie doar să apăsăm Enter după introducerea unei formule în celula necesară și apoi avem rezultatul.

Lucruri de reținut despre funcția OFFSET în Excel

  • Funcția OFFSET în Excel doar returnează o referință, nu mișcă nicio celulă sau o gamă de celule în realitate.
  • Dacă Formula OFFSET returnează o gamă de celule, rândurile și coloanele se referă întotdeauna la celula din stânga sus din intervalul returnat.
  • Referința ar trebui să includă o celulă sau o gamă de celule, altfel formula va returna eroarea #VALUE.
  • Dacă rândurile și coloanele specificate pe marginea foii de calcul, formula Offset în Excel va returna #REF! eroare.
  • Funcția OFFSET poate fi utilizată în orice altă funcție Excel care acceptă o celulă sau o gamă de referință în argumentele sale.

De exemplu, dacă folosim formula = OFFSET (A1, 3, 1, 1, 3) pe cont propriu, va oferi un #VALUE! Eroare, deoarece un interval de returnare (1 rând, 3 coloane) nu se încadrează într-o celulă. Cu toate acestea, dacă o asociem în funcția SUM, ca mai jos:

= SUMĂ (OFFSET (A1, 3, 1, 1, 3))

Formula Offset returnează suma valorilor într-un rând de un interval de 3 coloane care este cu 3 rânduri mai jos și o coloană din dreapta celulei A1, adică totalul valorilor din celulele B4: D4.

După cum se poate observa, totalul de 36 + 63 + 82 din Mar evidențiat este egal cu 181, care a rezultat ca G2 după aplicarea formulei sumă și Offset. (marcat cu galben)

Utilizarea funcției OFFSET în Excel

După cum am văzut că ceea ce funcția OFFSET face de fapt cu ajutorul exemplului de mai sus, este posibil să avem întrebări pe care de ce să deranjeze formula OFFSET cântată care este un pic complexă, de ce să nu folosești pur și simplu o referință directă ca suma sau B4: D4?

Funcția OFFSET în Excel este foarte bună pentru mai jos:

  • Obținerea intervalului din celula de pornire:

Uneori, nu știm adresa exactă sau reală a intervalului, știm doar de unde pornește de la o anumită celulă. În acest caz, putem folosi funcția OFFSET, care este ușor de parcurs.

  • Crearea unui interval dinamic:

Referințele precum exemplul de mai sus B1: C4 sunt statice, înseamnă că se referă întotdeauna la un interval dat sau fix. Dar, în unele cazuri, sarcinile sunt mai ușor de efectuat cu intervale dinamice. Ne ajută mai ales când lucrăm cu schimbarea datelor. De exemplu, am putea avea o foaie de calcul în care este introdus sau adăugat un nou rând sau coloană în fiecare zi / săptămână / lună, în acest caz, funcția OFFSET vă va ajuta.

Limitări și alternative ale funcției OFFSET în Excel

Deoarece fiecare formulă în Excel are propriile limitări și alternative, am văzut cum și când să utilizăm funcția OFFSET în Excel.

Mai jos sunt limitările critice ale funcției OFFSET în Excel:

  • Funcție înfometată cu resurse:

Înseamnă că, de fiecare dată când există o modificare a datelor sursă, formula va fi recalculată pentru întregul set, care menține Excel ocupat mai mult timp. Dacă există o foaie de calcul mică, atunci aceasta nu va afecta atât de mult, dar dacă există un număr de foi de calcul, Excel poate dura timp pentru recalculare.

  • Dificultate de revizuire:

După cum știm, referințele returnate de funcția Offset sunt dinamice sau în schimbare și pot conține o formulă mare, care poate fi dificil de corectat sau de editat conform cerințelor.

Alternative ale funcției OFFSET în Excel:

  • Funcția INDEX:

Funcția INDEX în Excel poate fi de asemenea folosită pentru a crea o gamă dinamică de referințe. Nu este exact același lucru ca OFFSET. Dar, la fel ca OFFSET, funcția INDEX nu este atât de volatilă, deci nu va încetini Excelul, ceea ce reprezintă o limitare pentru OFFSET.

  • Funcția INDIRECT:

De asemenea, putem folosi funcția INDIRECT pentru a crea o gamă dinamică de referințe din mai multe surse, cum ar fi valorile celulelor , textul și intervalele denumite.

Articole recomandate

Acesta a fost un ghid pentru OFFSET în Excel. Aici discutăm Formula OFFSET în Excel și Cum să utilizăm funcția OFFSET în Excel împreună cu exemple practice și șablon Excel descărcabil. De asemenea, puteți parcurge și alte articole sugerate -

  1. MS Excel: Funcție LOOKUP
  2. Cel mai bun exemplu de funcție Excel XIRR
  3. Ghid uimitor pentru funcția NU în Excel
  4. IPMT în Excel cu exemple

Categorie:

Sfaturi Excel