	HOME
1	ADDRESS
2	AREAS
3	CHOOSE
4	COLUMN
5	COLUMNS
6	HLOOKUP
7	HYPERLINK
8	INDEX
9	INDIRECT
10	LOOKUP
11	MATCH
12	OFFSET
13	ROW
14	ROWS
15	RTD
16	TRANSPOSE
17	VLOOKUP
18	GETPIVOTDATA



.

MICROSOFT EXCEL

FUNKCIJE ZA PRETRAŽIVANJE I REFERENCE
U MS EXCEL-u 2003

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

Vraća referencu na raspon koji je udaljen navedeni broj redaka i stupaca od ćelije ili raspona ćelija

Offset funkcija -primjer kako funkcija izračunava koji red i stupac treba pomaknuti

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))

Evaulate formula