IZBORNIK HOME FORUM ACCESS EXCEL WORD KAZALO
 

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 for dummy - za početnike - Vlookup kopiranje, How to copy the VLOOKUP function to the right in a row

FUNKCIJE ZA PRETRAŽIVANJE I REFERENCE
U  MS EXCEL-u 2003
dinamično kopiranje formule VLOOKUP

Search This Web Site




Kako kopirati VLOOKUP funkciju u desno u jednom redu da bi dobili ispravne rezultate iz tablice koja sadrži preko 10-tak ili 100 stupaca

Osnove funkcije Vlookup pogledajte na linku VLOOKUP funkcija u Excelu

Koristeći funkciju VLOOKUP u nekom tabličnom proračunu može doći do jednog banalnog problema prilikom kopiranja u desno u jednom redu kada trebamo dobiti više desetaka rezultata za neku ogromnu tablicu.

Kada kopirate formulu koja sadrži osnovnu funkciju Vlookup tada prilikom kopiranja u desnu stranu na ostale ćelije dolazi do promjene parametara funkcije Vlookup. Naravno neke parametre možemo ograničiti upotrebom znaka dolar "$" ili apsolutne adrese. No što ako trebamo promjenu određenog parametra u samoj funkciji a koji neće da se promijeni kada kopiramo formulu.

Uzmimo za primjer da na Sheetu1 imamo formulu u ćeliji D2
Ova formula vraća rezultat iz raspona podataka C3:DU101362 sa Sheeta3 iz stupca 2 uz uvjet u ćeliji A2 dotičnog Sheeta1

=VLOOKUP(A2;Sheet3!$C$3:$DU$10162;2;0)

ili

=VLOOKUP(A2;Sheet3!$C$3:$DU$10162;2;FALSE)

Mi trebamo nastaviti dalje kopirati ovu formulu u desno jer nam trebaju rezultati iz ostalih stupaca sa Sheeta3 i tako 100 stupaca.

Znači u ćelijama trebamo imati ove formule

D2 => =VLOOKUP(A2;Sheet3!$C$3:$DU$10162;2;0)
E2 => =VLOOKUP(A2;Sheet3!$C$3:$DU$10162;3;0)
F2 => =VLOOKUP(A2;Sheet3!$C$3:$DU$10162;4;0)
G2 => =VLOOKUP(A2;Sheet3!$C$3:$DU$10162;5;0) itd... do 100-te formule

Znači mi možemo kopirati prvo kreiranu formulu iz D2 u desno do DU2. Sada nastaje problem jer nam se prvi argument A2 u formuli preobrazio u B2, C2 itd... a sve ostalo je ostalo isto.

Ako dodamo znak dolara "$" ispred prvog argumenta (uvjeta) i kopiramo u desno imat ćemo slijedeće formule koja nam blokira promjenu uvjeta ali ne mijenja važan predzadnji parametar koji  treba vratiti rezultat iz određenog stupca tablice.

=VLOOKUP($A2;Sheet3!$C$3:$DU$10162;2;0)
=VLOOKUP($A2;Sheet3!$C$3:$DU$10162;
2;0)
=VLOOKUP($A2;Sheet3!$C$3:$DU$10162;
2;0)
=VLOOKUP($A2;Sheet3!$C$3:$DU$10162;
2;0) itd... do 100-te formule, što rezultira problemima.

Dakle imamo problem kada kopiramo formulu u desno a trebamo 100-tinjak formula u jednom redu. Možemo mi kopirati ovu formulu pa prepravljati ali pojedinačno prepravljanje predzadnjeg argumenta u samoj formuli traje dugo a možda i besmisleno.

Dakle kako riješiti problem?

Možemo se poslužiti trikom tj. funkcijama CONCATENATE i TRANSPOSE.
Koristeći funkciju Concatenate pa kopranjem preko Transpose možemo u nekoliko koraka brzo dobiti 100-tinjak ispravnih Vlookup formula.

U nekoj Workbook (Book1) rastavimo formulu na dijelove i funkcijom Concatenate opet je spojimo u jednoj ćeliji.

Concatenate funkcija - spajanje dijelova formule i funkcije Vlookup

Što sam uradio?
U prvom redu ćelije od A1 do D1 imamo dijelove formule, dok u E1 imamo formulu =CONCATENATE(A1;B1;C1;D1)
Funkcijom Concatenate dobili smo formulu u tekstnom obliku koja ne funkcionira.

Sve to kopiramo prema dolje do određenog reda (što ovisi koliko nam formula treba)
Uočite da je na slici iznad PLAVOM bojom označen predzadnji parametar koji se kopiranjem promijenio (uvećavao za jedan). Isto tako uočite da sam u stupcu E (označeno crvenom bojom) dobio formule koje imaju različit predzadnji argument.
Znači ja sada imam ispravne formule ali u tekstualnom obliku koje nisu funkcionalne.
Slijedi selektiranje crvenih formula E1:E123 pa potom preko desne tipke miša => COPY.

TRANSPOSE svih formula (kopiranje ili prebacivanje iz stupca u jedan red)

Prebacujem se na moju Workbook (Book2) u kojoj trebam postaviti rezultate i selektiram prvu ćeliju u kojoj treba biti rezultat funkcije Vlookup.

Dakle dok mi je još aktivna radnja "Copy" i kopiran se nalazi u Clipboardu, selektiram prvu ćeliju i idem na klik desna tipka miša pa na Paste Special => selektiram opciju Value i Transpose.
Sada su mi sve formule u jednom redu u obliku teksta i nisu funkcionalne.

Kako konvertirati formulu koja je oblikovana kao tekst u pravu formulu

Da bi sve formule bile funkcionalne trebamo ih konvertirati u "FORMULU". Mislim da je najbrže preko Find/Replace opcije.

Dakle kliknemo na Find/Replace pa potom u

polje "Find what" upišemo => ;0) ili ;FALSE)
polje "Replace with" upišemo => ;0) ili ;FALSE)

potom kliknemo na Replace All. Sada su sve text-formule konvertirane u prave formule i imamo rezultate sa našeg Sheet3 za svaki stupac uz uvjet u stupcu A.

Nadam se da ste shvatili ovaj trik. DOWNLOAD datoteka iz primjera.

No sve je ovo nepotrebno ako koristimo dodatnu funkciju COLUMN koja vraća redni broj stupca. Tada bi formula koju možemo kopirati u desnu stranu i prema dolje izgledala ovako. Počinjemo sa stupcem "D" jer se u njemu nalazi prvi podatak koji trebamo kao rezultat.

=VLOOKUP($A2;Sheet3!$C$3:$DU$10162;COLUMN(D1)-2;0).

ili

=VLOOKUP($A2;Sheet3!$C$3:$DU$10162;COLUMN();0) => ako se formula nalazi u stupcu "B"

nakon kopiranja u desnu stranu u istom redu imamo ovakav niz formula

D2 => =VLOOKUP($A2;Sheet3!$C$3:$DU$10162;COLUMN(D1)-2;0)
E2 => =VLOOKUP($A2;Sheet3!$C$3:$DU$10162;COLUMN(E1)-2;0)
F2 => =VLOOKUP($A2;Sheet3!$C$3:$DU$10162;COLUMN(F1)-2;0)
G2 => =VLOOKUP($A2;Sheet3!$C$3:$DU$10162;COLUMN(G1)-2;0) itd... do 100-te formule

Da bi vam bilo jasnije funkcioniranje funkcije COLUMN u ovoj formuli, upotrijebite EVALUATE FORMULA korak po korak. Uočite da je stupac D četvrti po redu i za njega je vezan broj 4, također uočite da je u stvari broj stupca D-2=2 => 4-2=2 a nama treba rezultat iz stupca "dva" (2) u prvoj formuli pa tako slijedi u drugoj formuli E-2=3 => 5-2=3 
pa dalje slijedi F-2=3 => 6-2=4 itd itd....(pogledajte formule pri samom početku ovog tutorijala).


Dinamično kopiranje formule kojoj je osnova funkcija Vlookup (Dynamic formula with function Vlookup)

Ovo je još jedan primjer kako možemo kreirati promjenjivu formulu u kojoj koristimo excelovu funkciju VLOOKUP za pretraživanje izvornih podataka. Kao što znate problem je jedan od argumenata koji se treba mijenjati kada kopiramo formulu u desnu stranu u jednom redu (sve zavisi o izvornim podacima)

Na slici ispod u Sheet1 imamo izvorne podatke koje trebamo pretraživati koristeći funkciju Vlookup a ona sadrži više stupaca iz kojih moramo vratiti rezultate. Uočite da je raspon baznih podataka Sheet1!$C$2:$I$8 koji je imenovan nazivom "podaci" radi lakšeg rada sa formulom.

Na slici niže ispod teksta na Sheet2 imamo nekakvu tablicu u kojoj želimo imati rezultate a koje želimo dobiti pretraživanjem bazne tablice podataka sa Sheet1.
Kao što znamo sintaksa funkcije VLOOKUP glasi

=VLOOKUP(lookup_value;table_array;col_index_num;[range_lookup])

Uočite argument crvene boje "col_index_num" koji je važan za formulu sa Vlookup funkcijom, Ovaj argument traži redni broj stupca da bi znao gdje će tražiti podatke (a njega postavljamo upisom broja 2 pa nadalje). Standardna formula glasila bi ovako

D2: =VLOOKUP($C2;Sheet1!$C$2":$I$8;2;FALSE)

Ako ovu formulu kopiramo u desno u istom redu, argument koji bi se trebao mijenjati kopiranjem (broj 2) neće se promijeniti pa mi moramo ručno mijenjati ovaj argument. Što bi izgledalo ovako

E2: =VLOOKUP($C2;Sheet1!$C$2":$I$8;3;FALSE)
F2: =VLOOKUP($C2;
Sheet1!$C$2":$I$8;4;FALSE)
itd...

Da bi si olakšali posao i pojednostavnili formulu idemo izvršiti neke promjene u formuli. Kao prvo idemo imenovati raspon baznih podataka na Sheet1. Ovaj naziv range možemo koristiti u funkciji kao argument

Adsense sponzor



table_array: Sheet1!$C$2":$I$8 => imenovani raspon ćelija nazivom "podaci"

Sada imamo ovakvu formulu (koju opet kopiranjem u desnu stranu ne možemo dobiti željene rezultate) pa moramo ručno mijenjati argument stupca

D2: =VLOOKUP($C2;podaci;2;FALSE)
E2: =VLOOKUP($C2;podaci;3;FALSE)
F2: =VLOOKUP($C2;podaci;4;FALSE)
itd...

Sada dolazimo do najvažnijeg dijela tutorijala. Naš problematičan broj (argument "col_index_num") možemo zamijeniti sa drugim izrazom ili funkcijom koja će biti promjenjiva ili dinamična i mijenjati se rednim brojem stupaca kako mi kopiramo formulu u desnu stranu).
Ovaj argument možemo zamijeniti sa funkcijom COLUMN. Prikazat ću  dva načina.

Argument col_index_num možemo pisati kao:

COLUMNS($C2:C2)+1

ili

COLUMN()-2

Odmah da razjasnim zašto možemo pisati ova dva načina. Dakle formula ide u stupac "D" pa se baziramo na njega. Pogledajte link COLUMN pa će vam ovo biti jasnije. Ako u bilo koju ćeliju u stupcu D upišemo jednu od ovih dviju formula (sa znakom jednakosti ispred) imat ćemo rezultat broj 2. To je dakle isto kao da smo u standardnu formulu upisali broj 2 (vidi prvu formulu ovog tutorijala)

Pa tako slijede dvije vrste formula:

D2: =VLOOKUP($C2;podaci;COLUMN()-2;FALSE)
D2: =VLOOKUP($C2;
podaci;COLUMNS($C2:C2)+1;FALSE)

Ovo je startna formula na dva načina koje je dinamična i kopiranjem vraća ispravan rezultat pretraživanja. Kada ove formule upišemo u ćeliju D2 i kopiramo u desnu stranu do I2 pa potom cijeli red 2 prema dolje, formula će ispravno pretražiti baznu izvornu tablicu i vratiti ispravne rezultate pretraživanja.

Excel formula Vlookupza dinamično pretraživanje i kopiranje po stupcima

Adsense sponzor



Adsense sponzor



Tako bi imali nakon kopiranja slijedeće formule: Uočite dijelove koji se mijenjaju kopiranjem samo u drugoj formuli

Ova formula ispod se ne mijenja već ostaje ista ali vraća iste rezultate kao i druga formula

D2: =VLOOKUP($C2;podaci;COLUMN()-2;FALSE)

U ovoj formuli se mijenjaju dijelovi argumenta "col_index_num"

D2: =VLOOKUP($C2;podaci;COLUMNS($C2:C2)+1;FALSE)
E2: =VLOOKUP($C2;
podaci;COLUMNS($C2:D2)+1;FALSE)
F2: =VLOOKUP($C2;
podaci;COLUMNS($C2:E2)+1
;FALSE)




Ova web stranica koristi COOKIES - COPYRIGHT 2008 - 2018 - IvanC - ic.ims.hr