Pomereni opsezi polja u Excel-u
Šta da radimo kada imamo veliku tabelu s raznovrsnim podacima koje uredno prikupljamo, a oni nam u određenim situacijama zasmetaju. Recimo da imamo veće grupe polja iz kojih je potrebno vaditi samo pojedinačna polja i preslikavati ih u drugu, kondenzovanu tabelu. Još ako je potrebna i neka iole ozbiljnija transformacija, eto izazova. Sličan problem imao je u našoj redakciji Nenad Veljković dok je sređivao tabele evidencije rada zaposlenih, a u pomoć mu je priskočio naš knjigovođa Branislav Pavlović, ponudivši inspiraciju za rešenje preko funkcije Excel Offset.
Funkcija Offset sračunava vrednost reference nekog opsega polja, pri čemu taj opseg može imati više redova i kolona, a u najčešćim slučajevima to je samo jedno polje koje je po zadatom ključu izvučeno iz neke matrice. Upravo ono što je u ovoj situaciji bilo potrebno.
Sintaksa je Offset(referenca; redova; kolona; [visina]; [širina]). Referenca je obavezno referentno polje ili grupa polja u odnosu na koje krećemo. Ukoliko se radi o grupi polja, ova polja moraju da budu povezana u kompaktnu pravougaonu ili kvadratnu celinu. Naredna dva obavezna argumenta koja određuju za koliko se pomeramo nadole ili udesno u odnosu na referencu. Ukoliko se pomeramo nadole ili udesno, brojevi su pozitivni, a ako idemo nagore ili ulevo, negativni. Na kraju, možemo opciono da zadamo i visinu i širinu opsega koji će biti vraćen, tj. referenciran. Ukoliko ne zadamo opcione parametre, biće vraćene dimenzije početne reference, tj. jedno polje ili povezana referencirana grupa polja. Ako se slučajno parametri redova i kolona pomere van radnog lista, dobićemo poruku o greški.
Važno je imati u vidu da funkcija Offset ništa ne pomera, već samo vraća željene reference polja čije se vrednosti prikazuju na novim lokacijama, uz eventualnu obradu ako je naknadno izvršena nad rezultatom formule. Dobijene Offset reference koriste se kao argumenti drugih funkcija ili formula koje mogu da koriste polja ili opsege. Na primer, Sum(Offset(B2;1; 2; 3; 4)) sračunava zbir vrednosti grupe polja iz tri reda i četiri kolone koja se nalazi jedan red ispod i dve kolone udesno od početnog referentnog polja B2.
Kao parametri mogu da se koriste i vrednosti referenciranih polja, tako da veličina okvira i pomeraj mogu i da se menjaju dinamički, što daje dodatnu fleksibilnost u korišćenju, naravno uz oprez jer je lako pogrešiti. Tako će formula =Offset(A1; 3; 1; 1; 3) svakako izazvati grešku jer pokušavamo ceo opseg da smestimo u jedno polje, ali će zato funkcija =Sum(Offset(A1; 3; 1; 1; 3)) biti ispravno izvršena – funkcija Sum() zna da radi sa opsezima i jedinstven sračunat rezultat smešta u tekuće polje.
Funkcija Offset pomaže nam da pravimo dinamičke opsege. Reference tipa A1:D4 su statičke, što znači da se uvek odnose na jednom definisan opseg. Mnogi problemi rešavaju se lakše preko dinamičkih opsega, naročito kada radimo s podacima koji rastu i menjaju se tokom vremena. To je situacija kada se podaci dodaju svakodnevno ili u određenim vremenskim intervalima, upravo kao evidencija radnog vremena koja se menja tokom meseca, a uz to sadrži i brojne prateće podatke koje je potrebno ignorisati u konačnim obračunima, a ujedno podesiti ih jednom i ne baviti se stalno ispravkama statičkih referenci. Druga interesantna primena jeste dobijanje željenog opsega u odnosu na početno polje. Ponekad se dešava da ne znamo konkretnu adresu željenog opsega, ali znamo koje je početno polje.
Isprobajmo dinamičko sumiranje korišćenjem funkcije Sum spregnute sa funkcijom Offset. Kada naša tabela kontinuirano raste tokom vremena i dodaju se novi elementi, dobro je da podesimo funkciju Sum tako da automatski preuzima sve nove redove. Na primer, svakog dana dodaje se nov red iznad Sum formule. U ovoj situaciji možemo da radimo osvežavanje ručno ili da upotrebimo Offset kako bismo to automatizovali. U ovoj situaciji znamo fiksnu vrednost početnog, gornje‑levog polja, poznata je i vrednost kolone, ali je neizvesno koja je vrednost zadnjeg reda. S obzirom na to da znamo da je taj red neposredno iznad polja u kojem se nalazi krajnji rezultat, možemo da osmislimo odgovarajući Offset:
=SUM(C4:(OFFSET
(C13; ‑1; 0)))
U Offset‑u je C13 adresa sume, paramtar ‑1 pokazuje na red iznad, a poslednja 0 govori da se referenca kolone ne pomera. Ovako se formula sama ažurira sa svakim dodatim redom.
U narednom primeru pozabavićemo se situacijom kada želimo da sumiramo samo poslednjih X redova u tabeli. Recimo da nas zanima ukupna isplata za poslednja tri meseca, a ne ukupna suma svih isplata u tabeli. Podrazumeva se da je potrebno automatsko ažuriranje proračuna, pri čemu se svakog meseca u zbir dodaje poslednja isplata, a izbacuje ona od pre četiri meseca. Koristićemo malo složeniju bateriju Sum‑Offset‑Count. Moguća su dva slična rešenja koja koriste funkcije Count ili Counta.
=SUM(OFFSET(C1;
COUNT(C:C)‑E1+1;
0; E1;1))
ili
=SUM(OFFSET(C1;
COUNTA(C:C)‑E1;
0; E1;1))
Referenca C1 je zaglavlje kolone koju sabiramo, a Counta(C:C)‑E1 parametar prebrojava polja kolone C koja sadrže brojeve umanjen za sadržaj E1. O označava da ostajemo u istoj koloni, sledeće E1 govori da uzimamo tri vrste, a 1 da je u pitanju jedna kolona.
Razlika između Count i Counta u tome je što Counta prebrojava sva polja koja nisu prazna, uključujući i zaglavlje, a Count prebrojava samo brojčane vrednosti, pa je u datoj situaciji razlika upravo 1, a to je ono 1 koje se dodaje u drugom argumentu funkcije Offset u Count varijanti.
Uređuje Ladislav Struharik
