logotip web stranice ic.ims.hr
Home - počtna stranica  FORUM ACCESS 2003 EXCEL 2003 WORD 2003  
1 Pokretanje i zatvaranje Microsoft Excel-a 2013
2 Izgled prozora Microsoft Excela 2013
3 Alatne trake u MS Excelu 2013
4 Podešavanje Excela
5 Otvaranje nove radne knjige i radnog lista
6 Spremanje i zatvaranje Workbook u Excelu
7 Pregled radnog lista prije ispisa u Excelu 2013
8 Određivanje margina Radnog lista
9 Kreiranje Zaglavlja i Podnožja Radnog lista
10 Ispis radnog lista ili tablice ili područja u Excelu 2013
11 Kreiranje mapa u Excelu
12 Kretanje radnom tablicom u Excelu 2013
13 Označavanje ili selektiranje u Excelu
14 Operatori u Excelu
15 Kreiranje formule u Excelu
16 Uvjetno oblikovanje u Excelu 2013
17 Kako izraditi padajuću listu u Excelu - Data Validation
18 Imenovanje ćelija ili raspona podataka u excelu 2013
19 Poravnanje teksta
20 Promjena podataka i brisanje sadržaja ćelije u Excelu 2013
21 Spajanje i dijeljenje ćelija u Excelu 2013
22 Automatska ispuna - brzi unos podataka
23 Popunjavanje ćelija specijalnim znakovima i simbolima
24 Crtanje u Excelu 2013
25 Dodavanje komentara na ćelije u Excelu 2013
26 Naredbe poništi i ponovi u excelu 2013
27 Premještanje, izrezivanje i kopiranje sadržaja ćelije u Excelu 2013
28 Umetanje i brisanje Ćelija, Stupaca i Redova
29 Promjena veličine Ćelija, Stupaca i Redova u Excelu 2013
30 Umetanje slika u Excelu
31 Radnje vezane uz Radni List u Excelu 2013
32 Apsolutna i relativna adresa ćelija u Excelu
33 Izrada Grafikona u Excelu 2013
34 Oblikovanje radnih tablica u Excelu 2013
35 Sortiranje podataka u Excelu 2013
36 Filtriranje podataka u Excelu 2013
37 Kreiranje Pivot Table
38 Zamrzavanje dijela prozora radnog lista u Excelu 2013
39 Oblikovanje Datuma u Excelu 2013
40 Zaključavanje ćelija i radnog lista u Excelu 2013
41 Zaštita radne knjige u Excelu 2013
42 Greške u Excelu 2013
43 Popis svih funkcija u Excelu 2013
44 Tipkovni prečaci u Excelu (hot key, shortcut)
45 Ograničenja u Excelu 2013
46 Makronaredbe u Excelu
47 Razni primjeri u Excelu
   
1 Excel 2003 primjeri
1 Excel 2007 primjeri
1 VBA U EXCELU
   
 

MICROSOFT EXCEL 2013 tutoriali -Kako usporediti i povući podatke pod uvjetom iz druge radne knjige Excela, Uspoređivanje i kopiranje podataka sa kreiterijem iz druge radne knjige Excela

Kako usporediti i povući ili kopirati podatke pod uvjetom iz druge radne knjige Excela




sponzor



Uspoređivanje i kopiranje podataka sa kreiterijem iz druge radne knjige Excela

U koliko imate potrebu kopirati usporediti neke podatke pod uvjetom ili zadatim kriterijem i povući ili kopirati određeni podatak iz druge Excel radne knjige tada iskoristite Excel Vlookup funkciju. Ovdje želim odmah napomenuti da je prilikom korištenja Vlookup funkcije poželjno da imenujete izvor raspona podataka.

Za ovaj primjer simulirat ću dvije Excel radne knjige. (obje radne knjige moraju biti otvorene prilikom rada)

  1. izvorna-datoteka.xls (baza)
  2. radna-datoteka.xls

Pogledajmo za primjer da prvu radnu knjigu 'radna-datoteka.xls' koristite tako da u nju unosite određene vrijednosti ili tekst.
Drugu radnu knjigu koristite kao izvor podataka iz koje radna-datoteka.xls kopira određeni podatak na osnovu kriterija koji se nalazi u radnoj knjizi 'radna-dartoteka.xls'

Dakle polazna radna knjiga nam je 'izvorna-datoteka.xls' u kojoj se nalazi naša baza podataka, iz koje koristeći formulu kopiramo određeni podatak, zavisno o uvjetu ili kriteriju.

Ona izgleda ovako kao na slici 1 ispod

Slika 1.

Izvorna datoteka kao baza podataka za kopiranje u Excelu

Druga radna knjiga (radna-datoteka.xls) je Workbook u kojoj postavljamo kriterij i unosimo potrebne podatke u 'stupcu A'. Ona izgleda ovako kao na slici ispod. Kriterij se nalazi u 'stupcu A' a mi trebamo rezultate u stupcima C i D u kojima postavljamo formule. Ovi podaci u C i D stupcima kopiraju se iz radne knjige 'izvorna-datoteka.xls' (koja nam služi kao baza)

Slika 2.

radna datoteka u Excelu

Smisao ovog tutorijala sa primjerima radnih knjiga je slijedeći:

U radnoj knjizi 'izvorna-datoteka.xls' imamo bazu podataka. Dijelove ove baze podataka želimo kopirati u radnu knjigu pod nazivom 'radna-datoteka.xls', ali samo one dijelove podataka koji se vežu za kriterij ili uvjet koji postavimo u radnoj datoteci.

U radnoj knjigi 'radna-datoteka.xls' imamo tablicu koju popunjavamo podacima i postavljamo uvjet ili kriterij na osnovu kojega će Excel povući ili kopirati određeni podatak iz bazne radne knjige 'izvorna-datoteka.xls'

Uočite na slici 2 iznad 'stupac A' u kojem se nalazi klasa tj. broj formatiran kao tekst. Iz tog broja/tekst mi trebamo izdvojiti samo posljednji broj (iza zadnjeg minusa) jer ćemo njega koristiti kao kriterij/uvjet. Ovaj uvjet je ključ baze u izvornoj radnoj knjizi. Na osnovu njega (ovog kriterija) želimo da nam Excel kopira iz izvorne radne knjige (baze) ostale pripadajuće podatke u stupce C i D.

Izdvajanje zadnje znamenke iz broja koji je formatiran kao tekst.

Dakle, u stupcu A nalazi se broj formatiran kao tekst. Mi trebamo iz ovog teksta izdvojiti zadnje znamenke iza posljednjeg minusa (crtice). Ovo izdvajanje znamenki možemo uraditi koristeći ovu formulu: VALUE(RIGHT($A2;LEN($A2)-18)). Ova formula izdvojit će zadnje znakove (u ovom slučaju brojeve) koji se nalaze iza posljednjeg minus znaka (crtice) i koristeći ovu formulu izdvajamo naš kriterij/uvjet. Funkcije RIGHT i LEN ugniježđene su u funkciju VALUE da bi rezultat bio broj. Dakle uvjeti ili kriteriji su zadnji brojevi klase u 'stupcu A'.

sponzor


Povlačenje ili kopiranje podataka iz druge radne knjige koji zadovoljavaju uvjet

U 'stupcu C' koristeći formulu Excel će na osnovu kriterija kopirati podatak iz druge radne knjige 'izvorna-datoteka.xls' (baze).

Formula je slijedeća (ove formule kopiramo prema dolje):

Excel ćelija C2
=IFERROR(VLOOKUP(VALUE(RIGHT($A2;LEN($A2)-18));'[izvorna-datoteka.xls]Sheet1'!$A$2:$C$100;2;FALSE);"")

ovu formulu kopiramo u desnu stranu da dobijemo rezultat za 'stupac C' a broj '2' promijenimo u broj '3'

Excel ćelija D2
=IFERROR(VLOOKUP(VALUE(RIGHT($A2;LEN($A2)-18));'[izvorna-datoteka.xls]Sheet1'!$A$2:$C$100;3;FALSE);"")

Ovdje želim napomenuti da je Excel funkcija IFERROR dostupna u Excelu 2007 i novijem. U formuli iznad uočite pravokutne zagrade ili uglate zagrade ([izvorna-datoteka.xls]). One označavaju da je dotična datoteka linkana kao izvor podataka. Ovaj link se dobiva tako što prilikom kreiranja formule kliknete na ćeliju u drugoj radnoj knjizi.

Ove formule prikazane iznad, u ćelijama C2 i D2 mogu se pisati i na drugačiji način:

Excel ćelija C2: argument (col_index_num) je broj 2
1.=> =IFERROR(VLOOKUP(VALUE(RIGHT($A2;LEN($A2)-18));'[izvorna-datoteka.xls]Sheet1'!$A$2:$C$100;COLUMN(B1);FALSE);"")
2.=> =IFERROR(VLOOKUP(VALUE(RIGHT($A2;LEN($A2)-18));'[izvorna-datoteka.xls]Sheet1'!$A$2:$C$100;COLUMN(B:B);FALSE);"")
3.=> =IFERROR(VLOOKUP(VALUE(RIGHT($A2;LEN($A2)-18));'[izvorna-datoteka.xls]Sheet1'!$A$2:$C$100;COLUMN()-1;FALSE);"")

1. => COLUMN(B1) => rezultat je stupac broj 2
2. => COLUMN(B:B) => rezultat je stupac broj 2
3. => COLUMN()-1 => zbog toga što se formula nalazi u stupcu C koji je treći (3) po redu => 3-1=2

Excel ćelija D2: argument (col_index_num) je broj 3
1. => =IFERROR(VLOOKUP(VALUE(RIGHT($A2;LEN($A2)-18));'[izvorna-datoteka.xls]Sheet1'!$A$2:$C$100;COLUMN(C1);FALSE);"")
2. => =IFERROR(VLOOKUP(VALUE(RIGHT($A2;LEN($A2)-18));'[izvorna-datoteka.xls]Sheet1'!$A$2:$C$100;COLUMN(C:C);FALSE);"")
3. => =IFERROR(VLOOKUP(VALUE(RIGHT($A2;LEN($A2)-18));'[izvorna-datoteka.xls]Sheet1'!$A$2:$C$100;COLUMN()-1;FALSE);"")

1. => COLUMN(C1) => rezultat je stupac broj 3
2. => COLUMN(C:C) => rezultat je stupac broj 3
3. => COLUMN()-1 => zbog toga što se formula nalazi u stupcu D koji je četvrti (4) po redu=> 4-1=3

Uočite u formulama iznad argument Excel funkcije VLOOKUP (col_index_num). Ovaj argument treba biti broj. Ovaj broj označava iz kojeg stupca po redu Excel treba kopirati izvorne podatke. Umjesto ovog broja možemo koristiti Excel funkciju COLUMN koja također vraća broj stupca. Koristeći 'Excel funkciju Column' lakše nam je formulu kopirati u desnu stranu bez dodatnog prepravljanja formule.

Ako koristimo starije verzije Excela 97-2003, tada ne možemo koristiti funkciju IFERROR, tada moramo kombinirati funkcije IF i ISERROR. Naravno i ovdje možemo koristiti Excel funkciju COLUMN koju ugradimo u formulu.

Formule za Excel 97-2003
=IF(ISERROR(VLOOKUP(VALUE(RIGHT($A2;LEN($A2)-18));'[izvorna-datoteka.xls]Sheet1'!$A$2:$C$100;2;FALSE))=FALSE;VLOOKUP(VALUE(RIGHT($A2;LEN($A2)-18));'[izvorna-datoteka.xls]Sheet1'!$A$2:$C$100;2;FALSE);"")

=IF(ISERROR(VLOOKUP(VALUE(RIGHT($A2;LEN($A2)-18));'[izvorna-datoteka.xls]Sheet1'!$A$2:$C$100;3;FALSE))=FALSE;VLOOKUP(VALUE(RIGHT($A2;LEN($A2)-18));'[izvorna-datoteka.xls]Sheet1'!$A$2:$C$100;3;FALSE);"")

I na kraju da rezimiram sve iznad navedeno. Raspon podataka mora biti pisan sa apsolutnim adresama ili imenovan (define name)

  • U ćeliji A2, uvjet je zadnji broj 1
  • Formula postavljena u Excel ćeliju C2 vraća tekst 'Troja' koji se nalazi u drugom stupcu raspona podataka '[izvorna-datoteka.xls]Sheet1'!$A$2:$C$100
  • Formula postavljena u Excel ćeliju D2 vraća tekst 'KAD' koji se nalazi u trećem stupcu raspona podataka '[izvorna-datoteka.xls]Sheet1'!$A$2:$C$100

Također pogledajte link tutorijal - Kako u VLOOKUP funkciju ugraditi ili umetnuti drugu funkciju




     

sponzor



COPYRIGHT © - 2005 - 2019 IvanC - Sva prava pridržana - Ova web domena/web stranica koristi cookies.
Prema međunarodnom zakonu o autorskom djelu zabranjeno je kopiranje, reproduciranje, prosljeđivanje i publiciranje cijelog sadržaja ili bilo kojeg njegova dijela