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.
Š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 |
|
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.
|
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)
…
|
|