Excel OFFSET funkcija (primjer + video)
Kada koristiti Excel OFFSET funkciju
Excel OFFSET funkcija može se koristiti kada želite dobiti referencu koja pomakne određeni broj redaka i stupaca od početne točke.
Što vraća
Vraća referencu na koju pokazuje funkcija OFFSET.
Sintaksa
= OFFSET (referenca, redovi, stupci, [visina], [širina])
Ulazni argumenti
- referenca - Referenca od koje želite odstupiti. To može biti referenca ćelije ili niz susjednih ćelija.
- redovi - broj redaka za pomak. Ako koristite pozitivan broj, pomaknut će se u donje retke, a ako se koristi negativan broj, pomaknut će se u gornje retke.
- cols - broj stupaca za pomak. Ako koristite pozitivan broj, pomaknut će se prema stupcima s desne strane, a ako se koristi negativan broj, pomaknut će se prema stupcima s lijeve strane.
- [visina] - ovo je broj koji predstavlja broj redaka u vraćenoj referenci.
- [širina] - ovo je broj koji predstavlja broj stupaca u vraćenoj referenci.
Razumijevanje osnova Excel OFFSET funkcije
Excel OFFSET funkcija je vjerojatno jedna od najzbunjujućih funkcija u Excelu.
Uzmimo jednostavan primjer šahovske igre. U šahu postoji komad koji se zove Rook (također poznat kao toranj, markiz ili rektor).
[Ljubaznošću slike: Prekrasna Wikipedia]
Sada, kao i sve ostale šahovske figure, topa ima fiksnu putanju po kojoj se može kretati po ploči. Može ići ravno (desno/lijevo ili gore/dolje po ploči), ali ne može ići dijagonalno.
Na primjer, pretpostavimo da imamo šahovsku ploču u Excelu (kao što je prikazano u nastavku), u danom okviru (u ovom slučaju D5), topa može ići samo u četiri istaknuta smjera.
Ako vas zamolim da odvedete Rooka s trenutnog položaja na položaj označenog žutom bojom, što ćete reći topu?
Zamolit ćete ga da napravi dva koraka prema dolje i dva koraka udesno … zar ne?
I to je približan način funkcioniranja funkcije OFFSET.
Pogledajmo sada što to znači u Excelu. Želim početi s ćelijom D5 (gdje se nalazi Rook), a zatim otići dva retka prema dolje i dva stupca udesno i dohvatiti vrijednost iz tamošnje ćelije.
Formula bi bila sljedeća:
= OFFSET (odakle započeti, koliko redaka dolje, koliko stupaca desno)
Kao što vidite, formula u gornjem primjeru u ćeliji J1 je = OFFSET (D5,2,2).
Počelo je u D5, a zatim se spustilo dva reda dolje i dva stupca udesno i stiglo do ćelije F7. Zatim je vratila vrijednost u ćeliju F7.
U gornjem primjeru smo pogledali funkciju OFFSET s 3 argumenta. No, postoje još dva izborna argumenta koja se mogu koristiti.
Pogledajmo ovdje jednostavan primjer:
Pretpostavimo da želite koristiti referencu na ćeliju A1 (u žutoj boji) i želite se odnositi na cijeli raspon označen plavom bojom (C2: E4) u formuli.
Kako biste to učinili pomoću tipkovnice? Prvo biste otišli u ćeliju C2, a zatim odabrali sve ćelije u C2: E4.
Pogledajmo sada kako to učiniti pomoću OFFSET formule:
= OFFSET (A1,1,2,3,3)
Ako koristite ovu formulu u ćeliji, vratit će #VREDNOST! pogreška, ali ako uđete u način uređivanja, odaberete formulu i pritisnete F9, vidjet ćete da vraća sve vrijednosti označene plavom bojom.
Pogledajmo sada kako funkcionira ova formula:
= OFFSET (A1,1,2,3,3)
- Prvi argument je ćelija u kojoj bi trebala početi.
- Drugi argument je 1, koji govori Excelu da vrati referencu koja je OFFSET za 1 redak.
- Treći argument je 2, koji govori Excelu da vrati referencu koja je za 2 stupca bila OFFSET.
- Četvrti argument je 3. To određuje da referenca treba pokriti 3 retka. To se naziva argument visine.
- Peti argument je 3. To određuje da referenca treba pokriti 3 stupca. To se naziva argument širine.
Sada kada imate referencu na raspon ćelija (C2: E4), možete je koristiti u okviru drugih funkcija (kao što su SUM, COUNT, MAX, AVERAGE).
Nadajmo se da dobro razumijete korištenje funkcije Excel OFFSET. Pogledajmo sada neke praktične primjere korištenja funkcije OFFSET.
Excel OFFSET funkcija - primjeri
Evo dva primjera korištenja Excel OFFSET funkcije.
Primjer 1 - Pronalaženje posljednje ispunjene ćelije u stupcu
Pretpostavimo da imate neke podatke u stupcu kao što je prikazano u nastavku:
Da biste pronašli posljednju ćeliju u stupcu, upotrijebite sljedeću formulu:
= OFFSET (A1, COUNT (A: A) -1,0)
Ova formula pretpostavlja da nema vrijednosti osim prikazanih i da ove ćelije nemaju praznu ćeliju. Djeluje tako što broji ukupan broj ćelija koje su ispunjene i prema tome odmiče ćeliju A1.
Na primjer, u ovom slučaju postoji 8 vrijednosti, pa COUNT (A: A) vraća 8. Mi pomaknemo ćeliju A1 za 7 kako bismo dobili zadnju vrijednost.
Primjer 2 - Stvaranje dinamičkog padajućeg izbornika
Konceptne prikaze u primjeru 1 možete proširiti za stvaranje dinamičkih imenovanih raspona. To bi moglo biti korisno ako koristite imenovane raspone u formulama ili stvarate padajuće izbornike i vjerojatno ćete dodati/izbrisati podatke iz reference koja čini imenovani raspon.
Evo primjera:
Imajte na umu da se padajući izbornik automatski prilagođava kada se godina doda ili ukloni.
To se događa jer je formula koja se koristi za stvaranje padajućeg izbornika dinamična i identificira svako dodavanje ili brisanje te u skladu s tim prilagođava raspon.
Evo kako to učiniti:
- Odaberite ćeliju u koju želite umetnuti padajući izbornik.
- Idite na Podaci -> Alati za podatke -> Provjera podataka.
- U dijaloškom okviru Validacija podataka na kartici Postavke s padajućeg izbornika odaberite Popis.
- U izvor unesite sljedeću formulu: = OFFSET (A1,0,0, COUNT (A: A), 1)
- Pritisnite U redu.
Pogledajmo kako funkcionira ova formula:
- Prva tri argumenta funkcije OFFSET su A1, 0 i 0. To u biti znači da bi vratila istu referentnu ćeliju (koja je A1).
- Četvrti argument je za visinu i ovdje funkcija COUNT vraća ukupan broj stavki na popisu. Pretpostavlja se da na popisu nema praznina.
- Peti argument je 1, što znači da bi širina stupca trebala biti jedna.
Dodatne napomene:
- OFFSET je hlapljiva funkcija (koristite s oprezom).
- Ponovno se izračunava kad god je Excel radna knjiga otvorena ili kad god se na radnom listu pokrene izračun. To bi moglo produžiti vrijeme obrade i usporiti radnu knjigu.
- Ako je vrijednost visine ili širine izostavljena, uzima se kao vrijednost reference.
- Ako redove i cols su negativni brojevi, tada je smjer pomaka obrnut.
Alternative funkcije Excel OFFSET
Zbog nekih ograničenja funkcije Excel OFFSET, mnogi želite razmotriti njezine alternative:
- INDEX funkcija: INDEX funkcija se također može koristiti za vraćanje reference ćelije. Kliknite ovdje da biste vidjeli primjer kako stvoriti dinamički imenovani raspon pomoću funkcije INDEX.
- Excel tablica: Ako u Excel tablici koristite strukturirane reference, ne morate brinuti o dodavanju novih podataka i potrebi prilagodbe formula.
Excel OFFSET funkcija - video vodič
- Excel VLOOKUP funkcija.
- Excel HLOOKUP funkcija.
- Excel INDEX funkcija.
- Excel INDIREKTNA funkcija.
- Excel MATCH funkcija.
