OFFSET
Vraća referencu na raspon koji je udaljen navedeni broj
redaka i stupaca od ćelije ili raspona ćelija. Vraćena
referenca može biti jedna ćelija ili raspon ćelija. Možete
navesti broj redaka i broj stupaca koji će biti vraćeni.
OFFSET funkcija u Excelu pomjera referencu (pravi pomak)
se od selektirane (određene) ćelije po redovima i
stupcima.
Taj pomak može ići u MINUS od selektirane ćelije
(Gore i Lijevo) ili u PLUS od selektirane ćelije
(Dolje i Desno)
Sintaksa
OFFSET(reference;rows;cols;height;width)
Reference je referenca na kojoj želite temeljiti
pomak. Referenca mora upućivati prema ćeliji ili rasponu
susjednih ćelija; inače će OFFSET prikazati pogrešku #VRIJ!.
Rows je broj redaka, nagore ili nadolje, na koje
želite da se odnosi gornja-lijeva ćelija. Argument rows s
vrijednošću 5 navodi da je gornja-lijeva ćelija u referenci
pet redaka ispod reference. Vrijednost argumenta rows može
biti pozitivna (što znači ispod početne reference) ili
negativna (što znači iznad početne reference). U ARRAY
formuli ovaj argument ne može biti "NIZ" jer Offset ne
prihvaća "niz" kao argumet. (vidi dodatak pojašnjenja na dnu
ove stranice)
Cols je broj stupaca, nalijevo ili nadesno, za koliko
želite da se odnosi gornja-lijeva ćelija rezultata.
Vrijednost 5 za argument cols navodi da je gornja-lijeva
ćelija u referenci pet stupaca desno od reference. Cols može
biti pozitivan (što znači desno od početne reference) ili
negativan (što znači lijevo od početne reference).
Height je željena visina vraćene reference, u broju
redaka. Visina mora biti pozitivan broj.
Width je željena širina vraćene reference, u broju
stupaca. Širina mora biti pozitivan broj.
Napomene
- Ako rows i cols pomaknu referencu preko ruba radnog
lista, OFFSET vraća pogrešku #REF!.
- Ako su height ili width ispušteni, pretpostavlja se
da su iste visine ili širine kao referenca.
- OFFSET ne pomiče ćelije niti mijenja označeno; on
samo vraća referencu. OFFSET može biti korišten sa bilo
kojom funkcijom koja za argument očekuje referencu. Na
primjer, formula SUM(OFFSET(C2;1;2;3;1)) izračunava
ukupnu vrijednost raspona od 3 retka i 1 stupca, koji je
1 redak ispod i 2 stupca desno od ćelije C2.
Primjer
Primjer OFFSET funkcije gdje tražimo naslov stupca uz uvjet
koji se nalazi u redu
|
Na slici lijevo
uočite gornju tablicu u kojoj se nalaze naši podaci.
Gradovi su raspoređeni po zonama. Mi želimo u nekom
daljnjem proračunu uz uvjet nekog grada znati kojoj
zoni pripada. Ovaj problem
možemo rješavati na dva načina. Pomoću funkcije
OFFSET ili pomoću funkcije
INDIRECT.
U dotične funkcije ugradit ćemo još neke dodatne
funkcije poput MAX, SUMPRODUCT, COLUMN.
Za početak
imenovat
ćemo range A2:F3 sa nazivom "gradovi" što je isto
kao i $A$2:$F$3 |
Pomoću funkcije INDIRECT (koju
neću opisivati) za rezultate u stupcu B5:B9 postavit ćemo
formulu polja
(matričnu ili array) koju nakon upisa završavamo sa
Ctrl+Shift+Enter da bi dobili vitičaste zagrade na
kraju.
=INDIRECT("R1C"&MAX(IF(gradovi=A5;COLUMN(gradovi);0));FALSE)
=> + Ctrl+Shift+Enter
Ako koristimo funkciju OFFSET tada će nam formula
u stupcu C5:C9 izgledati ovako
=OFFSET($A$1;0;SUMPRODUCT((gradovi=A5)*COLUMN(gradovi))-1)
Ova funkcija u sebi sadrži
ugniježđenu funkciju SUMPRODUCT i COLUMN.
Ove dvije funkcije =SUMPRODUCT((gradovi=A5)*COLUMN(gradovi))
kao rezultat vraćaju broj stupca u kojem se traži naš
podatak iz A5 (to je Rijeka). Dakle ako bi koristili samo
Sumproduct i Column tada bi rezultat bio broj 5 a to
je stupac u kojem se nalazi pojam "Rijeka".
Naša funkcija OFFSET ima
sintaksu =OFFSET(A1;0;broj_stupca-1).
Dakle umjesto "broj_stupca" mi
ćemo ugraditi formulu Sumproduct*Column. A1 u formuli
podrazumijeva prvu ćeliju cjelokupnog raspona podataka.
Pogledajte još jedan primjer
gdje možemo koristiti OFFSET funkciju.
Traženje sjecišta reda i
stupca u tablici Excela
DODATAK
pojašnjenja za argument "rows"
U slučaju da
funkciju OFFSET koristite u sastavu
ARRAY
formule trebate obratiti pažnju na
drugi argument funkcije Offset a to je "rows".
Uzmimo za primjer
ove dvije formule (boldana slova u formuli su argument "rows"
funkcije Offset)
{=TRANSPOSE(OFFSET($A$1;ROW(A1)*3-3;0;3;1))}
=> VRAĆA GREŠKU #VALUE!
{=TRANSPOSE(OFFSET($A$1;CELL("row";A1)*3-3;0;3;1))}
=> ISPRAVNA FORMULA
Prva formula koja kao argument "rows"
koristi funkciju "ROW" vratit će grešku #VALUE! jer
funkcija Offset ne prihvaća "NIZ" kao argument koji je u
ovom slučaju {1}. To
možemo pogledati kroz "evaluate" formule i uočit ćemo u
prvom koraku ovaj slijed formule a ona izgleda ovako:
=TRANSPOSE(OFFSET($A$1;{1}*3-3;0;3;1))
U drugom slučaju funkcija OFFSET
kao argument "rows" koristi funkciju CELL koja vraća broj
1 koji nije "NIZ" pa će
formula ispravno raditi. To možemo pogledati kroz "evaluate"
formule i uočit ćemo u prvom koraku ovaj slijed formule a
ona izgleda ovako: =TRANSPOSE(OFFSET($A$1;1*3-3;0;3;1))
|
|
|