Cum se utilizează funcția Excel OFFSET -

rezumat

Funcția Excel OFFSET returnează o referință la un interval construit cu cinci intrări: (1) un punct de pornire, (2) un offset de rând, (3) un offset de coloană, (4) o înălțime în rânduri, (5) o lățime în coloane. OFFSET este la îndemână în formule care necesită un interval dinamic.

Scop

Creați un decalaj de referință de la punctul de plecare dat

Valoare returnată

O referință de celulă.

Sintaxă

= OFFSET (referință, rânduri, coluri, (înălțime), (lățime))

Argumente

  • referință - Punctul de plecare, furnizat ca referință sau interval de celulă.
  • rânduri - Numărul de rânduri care trebuie compensate sub referința inițială.
  • cols - Numărul de coloane de compensat la dreapta referinței de pornire.
  • înălțime - (opțional) Înălțimea în rânduri a referinței returnate.
  • lățime - (opțional) Lățimea în coloane a referinței returnate.

Versiune

Excel 2003

Note de utilizare

Funcția Excel OFFSET returnează un interval dinamic construit cu cinci intrări: (1) un punct de plecare, (2) un offset de rând, (3) un offset de coloană, (4) o înălțime în rânduri, (5) o lățime în coloane.

Punctul de plecare ( argumentul de referință ) poate fi o celulă sau o gamă de celule. Cele rânduri și COLS argumentele sunt numărul de celule pentru a „compensa“ din punctul de pornire. Argumentele pentru înălțime și lățime sunt opționale și determină mărimea intervalului creat. Când înălțimea și lățimea sunt omise, acestea sunt implicite la înălțimea și lățimea de referință .

De exemplu, pentru referința C5 începând cu A1, referința este A1, rândurile sunt 4 și cols este 2:

=OFFSET(A1,4,2) // returns reference to C5

Pentru referință C1: C5 din A1, referința este A1, rândurile sunt 0, cols este 2, înălțimea este 5 și lățimea este 1:

=OFFSET(A1,0,2,5,1) // returns reference to C1:C5

Notă: lățimea poate fi omisă, deoarece va fi implicită la 1.

Este obișnuit să vezi OFFSET înfășurat într-o altă funcție care așteaptă un interval. De exemplu, la SUM C1: C5, începând cu A1:

=SUM(OFFSET(A1,0,2,5,1)) // SUM C1:C5

Scopul principal al OFFSET este de a permite formulelor să se adapteze dinamic la datele disponibile sau la intrarea utilizatorului. Funcția OFFSET poate fi utilizată pentru a construi un interval denumit dinamic pentru diagrame sau tabele pivot, pentru a vă asigura că datele sursă sunt întotdeauna actualizate.

Notă: documentația Excel afirmă că înălțimea și lățimea nu pot fi negative, dar valorile negative par să fi funcționat bine de la începutul anilor 1990. Funcția OFFSET din Foi de calcul Google nu va permite o valoare negativă pentru argumentele înălțime sau lățime.

Exemple

Exemplele de mai jos arată cum poate fi configurat OFFSET pentru a returna diferite tipuri de intervale. Aceste ecrane au fost realizate cu Excel 365, deci OFFSET returnează o matrice dinamică atunci când rezultatul este mai mult de o celulă. În versiunile mai vechi de Excel, puteți utiliza tasta F9 pentru a verifica rezultatele returnate de la OFFSET.

Exemplul nr. 1

În ecranul de mai jos, folosim OFFSET pentru a returna a treia valoare (martie) în a doua coloană (vest). Formula din H4 este:

=OFFSET(B3,3,2) // returns D6

Exemplul nr. 2

În ecranul de mai jos, folosim OFFSET pentru a returna ultima valoare (iunie) din a treia coloană (nord). Formula din H4 este:

=OFFSET(B3,6,3) // returns E9

Exemplul nr. 3

Mai jos, folosim OFFSET pentru a returna toate valorile din a treia coloană (Nord). Formula din H4 este:

=OFFSET(B3,1,3,6) // returns E4:E9

Exemplul # 4

Mai jos, folosim OFFSET pentru a returna toate valorile pentru mai (al cincilea rând). Formula din H4 este:

=OFFSET(B3,5,1,1,4) // returns C8:F8

Exemplul nr. 5

Mai jos, folosim OFFSET pentru a returna valorile din aprilie, mai și iunie pentru regiunea Vest. Formula din H4 este:

=OFFSET(B3,4,2,3,1) // returns D7:D9

Exemplul nr. 6

Mai jos, folosim OFFSET pentru a returna valorile din aprilie, mai și iunie pentru vest și nord. Formula din H4 este:

=OFFSET(B3,4,2,3,2) // returns D7:E9

Note

  • OFFSET returnează doar o referință, nu sunt mutate celule.
  • Atât rândurile, cât și colurile pot fi furnizate ca numere negative pentru a inversa direcția normală a decalajului - decolorarea negativă a colelor spre stânga și rândurile negative decalate deasupra.
  • OFFSET este o „funcție volatilă” - se va recalcula la fiecare modificare a foii de lucru. Funcțiile volatile pot face ca registrele de lucru mai mari și mai complexe să ruleze încet.
  • OFFSET va afișa #REF! valoare de eroare dacă offset-ul se află în afara marginii foii de lucru.
  • Când înălțimea sau lățimea sunt omise, se utilizează înălțimea și lățimea de referință .
  • OFFSET poate fi utilizat cu orice altă funcție care se așteaptă să primească o referință.
  • Documentația Excel spune că înălțimea și lățimea nu pot fi negative, dar valorile negative funcționează.

Videoclipuri asemănătoare

Cum să creați o gamă dinamică denumită cu OFFSET În acest videoclip, vom analiza cum să creați o gamă dinamică numită utilizând funcția OFFSET, cea mai obișnuită modalitate de a crea o gamă dinamică de nume cu o formulă.

Articole interesante...