Kako se koristi Excel OFFSET funkcija -

Sažetak

Funkcija Excel OFFSET vraća referencu na raspon konstruiran s pet ulaza: (1) početna točka, (2) pomak retka, (3) pomak stupca, (4) visina u redovima, (5) širina u stupaca. OFFSET je zgodan u formulama koje zahtijevaju dinamički raspon.

Svrha

Stvorite referentni pomak od zadane početne točke

Povratna vrijednost

Referenca stanice.

Sintaksa

= OFFSET (referenca, redovi, stupci, (visina), (širina))

Argumenti

  • referenca - početna točka, isporučuje se kao referenca ili raspon ćelije.
  • redovi - broj redaka za pomak ispod početne reference.
  • Stupci - Broj stupaca za pomak na desno od početne reference.
  • visina - (nije obavezno) Visina u redovima vraćene reference.
  • širina - (nije obavezno) Širina u stupcima vraćene reference.

Verzija

Excel 2003

Napomene o upotrebi

Funkcija Excel OFFSET vraća dinamički raspon konstruiran s pet ulaza: (1) početna točka, (2) pomak retka, (3) pomak stupca, (4) visina u redovima, (5) širina u stupcima.

Polazna točka ( referentni argument) može biti jedna stanica ili niz stanica. Na reda i COLS argumenti su broj stanica za „pomak” od početne točke. Na visinu i širinu argumenti su opcionalni i odrediti veličinu područja koje je stvorio. Kada su visina i širina izostavljene, zadane vrijednosti su visina i širina reference .

Na primjer, na referencu C5 koja započinje od A1, referenca je A1, redovi su 4, a stupci 2:

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

Za referencu C1: C5 iz A1, referenca je A1, retci 0, stupci 2, visina 5 i širina 1:

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

Napomena: širina se može izostaviti jer će zadana vrijednost biti 1.

Uobičajeno je vidjeti OFFSET umotan u drugu funkciju koja očekuje domet. Na primjer, na SUM C1: C5, počevši od A1:

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

Glavna svrha OFFSET-a je omogućiti formulama da se dinamički prilagođavaju dostupnim podacima ili korisničkom unosu. Funkcija OFFSET može se koristiti za izgradnju dinamičkog imenovanog raspona za grafikone ili zaokretne tablice, kako bi se osiguralo da su izvorni podaci uvijek ažurni.

Napomena: Excel dokumentacija navodi da visina i širina ne mogu biti negativni, ali čini se da su negativne vrijednosti dobro funkcionirale od početka 1990-ih. Funkcija OFFSET u Google tablicama neće dopustiti negativnu vrijednost za argumente visine ili širine.

Primjeri

Primjeri u nastavku pokazuju kako se OFFSET može konfigurirati za vraćanje različitih vrsta raspona. Ti su zasloni snimljeni s programom Excel 365, pa OFFSET vraća dinamički niz kada je rezultat više od jedne ćelije. U starijim verzijama Excela možete koristiti tipku F9 da biste provjerili rezultate vraćene iz OFFSET.

Primjer # 1

Na donjem zaslonu koristimo OFFSET za vraćanje treće vrijednosti (ožujak) u drugom stupcu (zapad). Formula u H4 je:

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

Primjer # 2

Na donjem zaslonu koristimo OFFSET za vraćanje posljednje vrijednosti (lipanj) u trećem stupcu (sjever). Formula u H4 je:

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

Primjer # 3

Ispod koristimo OFFSET za vraćanje svih vrijednosti u trećem stupcu (sjever). Formula u H4 je:

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

Primjer # 4

Ispod koristimo OFFSET za vraćanje svih vrijednosti za svibanj (peti red). Formula u H4 je:

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

Primjer # 5

U nastavku koristimo OFFSET za vraćanje vrijednosti travnja, svibnja i lipnja za zapadnu regiju. Formula u H4 je:

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

Primjer # 6

U nastavku koristimo OFFSET za vraćanje vrijednosti travnja, svibnja i lipnja za zapad i sjever. Formula u H4 je:

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

Bilješke

  • OFFSET vraća samo referencu, ne premještaju se stanice.
  • I retci i stupci mogu se isporučiti kao negativni brojevi da se preokrene njihov normalan smjer pomaka - negativni stupci pomaknu se ulijevo, a negativni redovi pomaknu gore.
  • OFFSET je "hlapljiva funkcija" - preračunavat će se sa svakom promjenom radnog lista. Hlapljive funkcije mogu učiniti da veće i složenije radne knjige polako rade.
  • OFFSET će prikazati #REF! vrijednost pogreške ako je pomak izvan ruba radnog lista.
  • Kada su visina ili širina izostavljeni, koristi se visina i širina reference .
  • OFFSET se može koristiti s bilo kojom drugom funkcijom koja očekuje primanje reference.
  • Excel dokumentacija kaže da visina i širina ne mogu biti negativne, ali negativne vrijednosti djeluju.

Slični Videi

Kako stvoriti dinamički imenovani raspon pomoću OFFSET U ovom ćemo videozapisu pogledati kako stvoriti dinamički imenovani raspon pomoću funkcije OFFSET, najčešći način za stvaranje dinamičkog raspona imena pomoću formule.

Zanimljivi članci...

Preporučeno