Formula Excel OFFSET (cuprins)

  • Care este OFFSET Formula în Excel?
  • Cum se utilizează formula OFFSET în Excel?

Care este OFFSET Formula în Excel?

OFFSET Formula în Excel oferă o celulă sau o gamă de celule dreptunghiulare dinamice ca o ieșire care este un subset al intervalului dat total cu un număr specificat de rânduri, coloane, înălțime și lățime.

Sintaxa formulei OFFSET în Excel

Explicația funcției OFFSET în Excel:

  • referință: punctul de plecare pentru OFFSET sau putem spune o serie de celule care pot fi considerate ca bază pentru OFFSET.
  • rânduri : numărul de rânduri până la OFFSET sub sau deasupra referinței de bază.
  • cols: numărul de coloane de dezactivat la dreapta sau la stânga referinței de bază.
  • înălțime: număr de rânduri în referință returnată.
  • lățime: număr de coloane în referință returnată.

Înălțimea și lățimea sunt parametrii opționali pentru această funcție și pot fi folosiți pentru a determina dimensiunea de referință creată.

Cum se utilizează formula OFFSET în Excel?

Vom învăța acum cum se scrie formula offset pentru a obține valoarea căutării. Să înțelegem formula de compensare cu câteva exemple în Excel.

Puteți descărca acest model OFFSET Formula Excel aici - OFFSET Formula Excel Model

Formula Excel OFFSET - Exemplul # 1

Să presupunem că avem date așa cum sunt date mai jos.

  • În celula E2, introduceți următoarea formulă:

= OFFSET (A1, 11, 1)

  • Odată terminat cu formula, apăsați Enter și vedeți ieșirea.

Ceea ce a făcut această formulă este, a luat referință din celula A1 (An) și s-a mutat cu 11 rânduri după A1 (adică rândul nr. 12) și apoi a obținut valoarea care este coordonata a 11- a rând după A1 și prima coloană ( coloana A = 0 coloana B = 1. în acest caz. După cum știm, putem merge atât la dreapta cât și la stânga). Care este 141, 27 milioane dolari.

Formula Excel OFFSET - Exemplul # 2

Acum vom vedea un alt exemplu de celulă de referință pentru un an anume. Puneți următoarea formulă în celula E3:

= OFFSET (A1, 11, 0)

  • Odată terminat cu formula, apăsați Enter și vedeți ieșirea.

Dacă ați văzut formula, este aceeași cu cea anterioară, singura modificare este în referința coloanei. Referința de coloană 0 (Zero) înseamnă că valoarea va fi capturată din coloana A pentru a 11- a rând după A1 (care este 2010).

Formula Excel OFFSET - Exemplul # 3

În acest exemplu, vom calcula suma ultimelor N rânduri folosind funcția Excel OFFSET. Formula generală pentru însumarea ultimelor N numere este:

= SUMĂ (OFFSET (A1, COUNT (A: A), 0, -N))

Pentru a rezuma ultimele N observații din datele dvs., puteți utiliza funcția SUM împreună cu funcția OFFSET și COUNT. În acest exemplu, vom rezuma ultimele 5 puncte de date. Vă rugăm să rețineți că atunci când spunem N, este o generalizare a oricărei valori întregi. Deci, puteți calcula pentru ultimele 3, ultimele 4, ultimele 5, etc., în funcție de setul dvs. de date, precum și de cerința dvs.

Să o facem pentru setul nostru de date:

  • Introduceți următoarea formulă în celula E2 a fișierului excel.

= SUMĂ (OFFSET (B1, COUNT (B: B), 0, -5))

  • Faceți clic pe Enter pentru a vedea rezultatul.

După cum am văzut deja, funcția Excel OFFSET produce valori dinamice dreptunghiulare atunci când este dată o referință de pornire.

  • În acest caz, referința de pornire este furnizată în termeni de B1 (ca prim argument pentru funcția OFFSET).
  • Am folosit funcția COUNT pentru că avem nevoie de suma observațiilor de la ultima valoare. Funcția COUNT ajută OFFSET să determine câte observații (rânduri) există în coloana B. Acesta numără toate valorile numerice din coloana B (19 este numărul).
  • Cu numărul 19, formula OFFSET devine:

= OFFSET (B1, 19, 0, -5)

  • Această formulă începe compensarea la B1 cu 19 rânduri și aceeași coloană (0). Apoi, parametrul înălțime -5 ajută această funcție să extindă intervalul în direcția înapoi cu 5 rânduri (vezi semnul negativ asociat cu 5).
  • Gama dinamică a ultimelor cinci observații este dată ca o contribuție la funcția de sumă. Ceea ce rezultă prin însumarea celor din coloana B.

Formula Excel OFFSET - Exemplul # 4

În acest exemplu, vom calcula vânzările mediane pentru ultimii 3 ani. Funcția OFFSET poate fi folosită ca argument pentru a calcula aceeași.

  • Puneți următoarea formulă în celula E2:

= MEDIAN (OFFSET (B1, COUNT (B: B), 0, -3))

  • Apăsați Enter pentru a vedea ieșirea.

Această formulă funcționează pe liniile similare cu cea anterioară.

  • OFFSET Creează o gamă dinamică de ultimele trei observații din coloana B cu ajutorul funcției COUNT (= OFFSET (A1, 19, 0, -3)).
  • Acest lucru poate fi dat ca o ieșire la funcția MEDIAN. Care apoi ordonează observațiile în ordine crescătoare sau descendentă și apoi selectează valoarea cea mai mijlocie așa cum vedeți la ieșire (212, 07 USD).

Formula Excel OFFSET - Exemplul # 5

Aici vom calcula anul median folosind funcția MEDIAN și OFFSET.

  • Utilizați următoarea formulă pentru aceeași în celula E3:

= MEDIAN (OFFSET (A1, COUNT (A: A), 0, -3))

  • Faceți clic pe Enter pentru a vedea rezultatul.

Același lucru s-a întâmplat aici. Formula a preluat o gamă dinamică creată de funcția OFFSET cu ajutorul lui COUNT. Și a folosit-o ca argument pentru o funcție MEDIANĂ, care apoi a sortat valorile anului în ordine crescătoare sau descendentă și apoi a dat ieșire ca 2017.

Utilizarea formulei OFFSET este crearea intervalelor dinamice. Este întotdeauna mai bine să lucrezi pe intervale dinamice în loc de cele statice. După cum s-ar putea să aveți date care se adaugă din când în când. De exemplu, Master Employee. Datele dvs. de bază ale angajaților vor fi mereu crescute din când în când, deoarece trebuie să se alăture unii angajați noi. De fiecare dată folosirea aceleiași formule nu are sens. În loc de asta, creați o gamă care este dinamică folosind funcția OFFSET și vedeți magia. Cu siguranță te va economisi un timp.

Lucruri de amintit

  • Funcția OFFSET returnează doar o referință la o anumită gamă de celule din Excel. Nu mișcă celulele din pozițiile inițiale.
  • În mod implicit, această funcție funcționează pe partea superioară (în ceea ce privește rândurile) și pe partea dreaptă (în ceea ce privește coloanele). Cu toate acestea, poate fi furnizat cu argumente negative pentru a inversa funcționalitatea implicită. Ca și-Argumentul rândului negativ ajută la compensarea datelor de jos în sus și argumentul col negativ ajută la compensarea datelor la stânga.
  • Formula OFFSET poate încetini funcționalitatea Excel, deoarece este o formulă care este recalculată de fiecare dată când există o modificare a foii de lucru.
  • Eroarea #REF va fi aruncată dacă intervalul dvs. se află în afara domeniului de lucru.
  • Orice altă funcție care se așteaptă la o referință este capabilă să utilizeze funcția OFFSET în cadrul acesteia.

Articole recomandate

Acesta este un ghid pentru OFFSET Formula în Excel. Aici vom discuta despre cum să folosiți funcția OFFSET în Excel, împreună cu exemple practice și șablon Excel descărcabil. De asemenea, puteți parcurge și alte articole sugerate -

  1. Cum să înfășurați textul în Excel?
  2. Ghid simplu de sortare în Excel
  3. Funcție LOOKUP în Excel
  4. Cum se utilizează funcția FIND în Excel?

Categorie:

Sfaturi Excel