Excel OFFSET funkcija (Sadržaj)

  • OFFSET u Excelu
  • OFFSET Formula u Excelu
  • Kako se koristi OFFSET funkcija u Excelu?

OFFSET u Excelu

OFFSET funkcija u Excelu pomaže korisniku da vrati ćeliju ili raspon ćelija koji su zadani ne. redaka i stupaca iz ćelije. Možemo odrediti br. redova i br. stupca koji se vraćaju.

Ispod je OFFSET formula u Excelu:

OFFSET funkcija u Excelu sastoji se od sljedećih argumenata:

  • Referenca: To je argument na kojem želimo temeljiti odstupanje. To bi mogla biti referentna stanica ili niz susjednih ćelija, ako ne, Offset vraća #VALUE! Vrijednost pogreške
  • Redci: To je ne. redaka za nadoknadu. Ako koristimo pozitivan broj, tada se on pomakne u retke ispod, a ako se koristi negativni broj, tada se nadoknađuju redovi iznad.
  • Stupci: To je za br. stupaca za nadoknadu. Dakle, to je isti koncept kao i Redovi, ako koristimo pozitivan broj, tada se pomakne u stupce s desne strane, a ako se koristi negativni broj, tada se pomakne na stupce s lijeve strane.
  • Visina: Ovaj argument nije obavezan. To bi trebao biti pozitivan broj. To je broj koji predstavlja broj redaka u vraćenoj referenci.
  • Širina: Ovo je također izborni argument. To mora biti pozitivan broj. To je broj koji predstavlja broj stupaca u vraćenoj referenci.

Bilješke:

  • OFFSET funkcija u Excelu vraća #REF! vrijednost pogreške, ako se redovi i stupci pomaknu preko ruba radnog lista.
  • U OFFSET funkciji treba biti iste visine ili širine kao referentna ako su visina ili širina izostavljene.
  • OFFSET vraća referencu, ona zapravo ne pomiče nijednu ćeliju ili raspon ćelija niti mijenja odabir. Može se koristiti s bilo kojom funkcijom koja očekuje referentni argument.

Kako se koristi OFFSET funkcija u Excelu?

OFFSET Funkcija u Excelu je vrlo jednostavna i laka za upotrebu. Neka razumije funkcioniranje OFFSET funkcije u Excelu nekim primjerom OFFSET Formule.

Ovdje možete preuzeti ovaj OFFSET predložak funkcije Excel - OFFSET Predložak Excela

Primjer 1

Formula Offset vraća referencu na ćeliju na temelju početne točke, redaka i stupaca koje smo odredili. To možemo vidjeti u niže navedenom primjeru:

= OFFSET (A1, 3, 1)

Formula kaže Excelu da razmotri ćeliju A1 za početnu točku (referencu), a zatim pomakne 3 retka prema dolje (redovi) i 1 stupac ulijevo (argument stupaca). Nakon toga, OFFSET formula vraća vrijednost u ćeliji B4.

Slika s lijeve strane pokazuje da put funkcije i snimka zaslona s desne strane pokazuju kako možemo koristiti OFFSET formulu na osnovu podataka u stvarnom vremenu. Razlika između dvije formule je da druga (s desne strane) uključuje referencu ćelije (D1) u argumentu retka. Ali budući da ćelija D1 sadrži broj 1, a potpuno isti broj se pojavljuje u argumentu redaka prve formule, obje bi vratile identičan rezultat - vrijednost u B2.

Primjer # 2 Upotreba OFFSET funkcije za zbroj

Kao što znamo da se OFFSET funkcija može koristiti i s drugom funkcijom Excela, stoga ćemo u ovom primjeru vidjeti SUM funkciju.

Pretpostavimo da na gornjoj slici moramo pronaći zbroj marki i tada možemo koristiti OFFSET funkciju.

Formula je u nastavku:

Dakle, u gornjoj formuli D5 je prva ćelija u kojoj počinju podaci, što je referenca kao početna ćelija. Nakon toga vrijednost redaka i stupaca iznosi 0, pa možemo to staviti kao 0, 0. Zatim je 4 što znači 4 reda ispod reference za koji Excel treba izračunati zbroj, a zatim, na kraju, to je 1, to znači da postoji 1 stupac kao širina.

Gornju sliku možemo uputiti kako bismo povezali objasnjeni primjer.

A ispod je rezultat:

Samo moramo pritisnuti Enter nakon što unesete formulu u željenu ćeliju i tada imamo rezultat.

Ne zaboravite o OFFSET funkciji u Excelu

  • OFFSET funkcija u Excelu samo vraća referencu, ona ne pomiče nijednu ćeliju niti raspon ćelija.
  • Ako OFFSET Formula vrati raspon ćelija, redovi i stupci uvijek se odnose na gornju lijevu ćeliju u vraćenom rasponu.
  • Upućivanje treba sadržavati ćeliju ili raspon ćelija, u protivnom će formula vratiti grešku #VALUE.
  • Ako su redovi i stupci navedeni preko ruba proračunske tablice, Offset formula u Excelu će vratiti #REF! greška.
  • OFFSET funkcija može se koristiti unutar bilo koje druge Excel funkcije koja u svojim argumentima prihvaća referencu ćelije ili raspona.

Na primjer, ako koristimo formula = OFFSET (A1, 3, 1, 1, 3) samostalno, dat će #VALUE! Pogreška, jer raspon povrata (1 red, 3 stupca) ne stane u ćeliju. Međutim, ako to usporedimo u funkciju SUM kao što je niže:

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

Formula Offset vraća zbroj vrijednosti u 1 retku rasponom od 3 stupca koji je 3 reda ispod i 1 stupac desno od ćelije A1, tj. Ukupne vrijednosti u ćelijama B4: D4.

Kao što možemo vidjeti ukupno 36 + 63 + 82 istaknutog Mar je jednako 181, što je nastalo kao G2 nakon primjene zbroja i offset formule. (istaknuto žutom bojom)

Upotreba OFFSET funkcije u Excelu

Kao što smo vidjeli da ono što OFFSET funkcija zapravo čini uz pomoć gornjeg primjera, možemo imati pitanja koja Zašto gnjaviti otpjevanu OFFSET formulu koja je pomalo složena, zašto jednostavno ne koristiti izravnu referencu poput zbroja ili B4: D4?

OFFSET funkcija u Excelu je vrlo dobra za dolje:

  • Dobivanje raspona od početne ćelije:

Ponekad ne znamo točnu ili stvarnu adresu raspona, znamo samo odakle počinje iz određene ćelije. U ovom slučaju možemo upotrijebiti funkciju OFFSET, što je lako ići.

  • Stvaranje dinamičkog raspona:

Reference poput gornjeg primjera B1: C4 su statičke, to znači da se uvijek odnose na određeni ili fiksni raspon. Ali u nekim slučajevima se zadaci lakše izvode s dinamičkim rasponima. Posebno pomaže kada radimo s promjenom podataka. Na primjer, mogli bismo imati proračunsku tablicu u koju se ubacuje ili dodaje novi redak ili stupac svaki dan / tjedan / mjesec, u ovom će slučaju funkcija OFFSET pomoći.

Ograničenja i alternative funkcije OFFSET u Excelu

Kako svaka formula u Excelu ima svoja ograničenja i alternative, vidjeli smo kako i kada koristiti OFFSET funkciju u Excelu.

Ispod su kritična ograničenja OFFSET funkcije u Excelu:

  • Funkcija gladna za resursima:

To znači da kad god dođe do promjene izvornih podataka formula će se preračunati za cijeli skup, što Excel zadržava duže vrijeme. Ako postoji mala proračunska tablica, to neće utjecati toliko, ali ako postoji niz proračunskih tablica, Excel će možda trebati vremena za ponovni izračun.

  • Poteškoće u pregledu:

Kao što znamo reference vraćene u funkciji Offset su dinamične ili se mijenjaju i mogu sadržavati veliku formulu, koja može nakostriješiti ispravljanje ili uređivanje prema zahtjevu.

Alternativa OFFSET funkciji u Excelu:

  • Funkcija INDEX:

Funkcija INDEX u Excelu se također može koristiti za stvaranje dinamičkog raspona referenci. Nije potpuno isto kao OFFSET. Ali poput OFFSET-a, funkcija INDEX nije toliko volatilna, pa stoga neće usporiti Excel što je ograničenje za OFFSET.

  • Indirektna funkcija:

Također možemo koristiti funkciju INDIRECT za stvaranje dinamičkog raspona referenci iz mnogih izvora poput vrijednosti ćelija , teksta i imenovanih raspona.

Preporučeni članci

Ovo je vodič za OFFSET u Excelu. Ovdje smo raspravljali o OFFSET Formuli u Excelu i kako koristiti OFFSET funkciju u Excelu zajedno s praktičnim primjerima i download-ovim Excel predloška. Možete i pregledati naše druge predložene članke -

  1. MS Excel: LOOKUP funkcija
  2. Najbolji primjer Excel XIRR funkcije
  3. Iznenađujući vodič o NE funkciji u Excelu
  4. IPMT u Excelu s primjerima

Kategorija:

Excel savjeti