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 izraditi dva zavisna Drop Down izbornika na kojem se nalaze jedinstveni podaci bez praznih ćelija

Dvije zavisne Validation Liste u Excelu




sponzor



Dva uvjeta - Tri stupca - Kako izraditi tri dinamične Validation liste sa jedinstvenim podacima koje ne sadrže prazne ćelije

O 'Validation listama' ili padajućim izbornicima pisao sam u tutorialima koji se nalaze na kraju teksta ovog članka. U ovom tutorial-članku pokazat ću kako možemo kreirati tri padajuće liste od kojih su dva padajuća izbornika zavisna o prethodnom. Cilj ovog tutoriala je, u tri dinamične padajuće liste izabrati konačan rezultat koji zavisi od dva podatka izabrana u prethodne dvije ćelije ali tako da na popisu imamo samo jedinstvene podatke i da se ne prikazuju prazne ćelije. Uočite situaciju na slici ispod.

Upute za izradu dinamičnih zavisnih padajućih popisa

Na slici iznad uočite bazu podataka u 'A:C' stupcima. Mi želimo kreirati tri padajuća izbornika (tri Validation liste)

  • U 'E2' ćeliji želimo kreirati prvu padajuću listu koja će sadržavati jedinstvene podatke iz 'A' stupca ali tako da ne prikazuje prazne ćelije u padajućem popisu.
  • U 'F2' ćeliji želimo prikazati drugi padajući izbornik koji će zavisiti od podatka koji smo izabrali u 'E2' ćeliji. U padajućoj listi želimo prikazati jedinstvene podatke/vrijednosti koji se nalaze u 'B' stupcu a da se podudara uvjet iz 'E2' ćelije. Također na popisu ne želimo prikazati prazne ćelije (mi želimo da na ovaj popis bude dinamičan i promjenjiv, u zavisnosti od broja stavki na popisu)
  • U 'G2' ćeliji želimo kreirat drugu zavisnu padajuću listu u kojoj će se pojaviti podaci iz 'C' stupca a koji zavise o uvjetu koji se nalazi u 'F2' ćeliji a također na popisu ne želimo prikazati prazne ćelije (mi želimo da na ovaj popis bude dinamičan i promjenjiv, u zavisnosti od broja stavki na popisu).

Kreiranje dinamičnog popisa sa jedinstvenim podacima/vrijednostima za višestruke uvjete

Da bi si olakšali posao mi ćemo kreirati pomoćne stupce (helper columns) iz kojih ćemo povlačiti rezultate. Na slici ispod uočite kreirane ekstra kolone.

  • U 'J' stupcu postaviti ćemo formulu koja će nam kao rezultat vratiti sve jedinstvene podatke iz 'A' stupca. Ovdje nemamo uvjeta.
  • U 'K' stupcu postaviti ćemo formulu koja će nam kao rezultat vratiti sve jedinstvene podatke iz 'B' stupca ako se podudara uvjet u 'E2' ćeliji. Dakle imamo jedan uvjet.
  • U 'L' stupcu postaviti ćemo formulu koja će nam kao rezultat vratiti sve jedinstvene podatke iz 'C' stupca ako se podudaraju uvjeti u 'F2' i 'E2' ćelijama. Dakle imamo dva uvjeta.

Izrada pomoćnih stupaca za dinamične padajuće izbornike

Prva formula nalazi se u 'J2' ćeliji a ona izgleda ovako (kopirajte je do zadnjeg reda). Ova formula vraća sve jedinstvene podatke iz 'A' stupca.
=IF(IFERROR(INDEX($A$2:$A$100;MATCH(0;INDEX(COUNTIF($J$1:J1;$A$2:$A$100););0));"")=0;
"";IFERROR(INDEX($A$2:$A$100;MATCH(0;INDEX(COUNTIF($J$1:J1;$A$2:$A$100););0));""))

Druga ARRAY formula (CSE formula) u 'K2' ćeliji je slijedeća (kopirajte je do zadnjeg reda). Ova formula vraća sve jedinstvene podatke iz 'B' stupca iz istog reda u kojem se nalazi postavljeni uvjet u 'E2' ćeliji.
=IFERROR(INDEX(B$2:B$100;MATCH(0;IF(E$2=A$2:A$100;COUNTIF(K$1:K1;B$2:B$100);"");0));"")

Treća ARRAY formula u 'L2' ćeliji je slijedeća (kopirajte je do zadnjeg reda). Ova formula vraća sve jedinstvene podatke iz 'C' stupca iz istog reda u kojem se nalaze uvjeti iz 'E2' i 'F2' ćelija.
=IFERROR(INDEX($C$2:$C$100;MATCH(0;COUNTIF(L1:$L$1;$C$2:$C$100)+
IF($B$2:$B$100<>$F$2;1;0)+IF($A$2:$A$100<>$E$2;1;0);0));"")

Kreiranje zavisnih padajućih lista i postavljanje formule za Data Validation listu i padajuće izbornike

Prvi padajući izbornik

Na slici ispod uočite kako izgleda prvi padajući izbornik koji postavljamo preko 'Validation List'. Uočite da nema praznih stavki u padajućem zborniku.

Padajuća lista koja ne prikazuje prazne ćelije u Excelu

U 'E2' ćeliji postavljamo formulu za prvi padajući izbornik. Selektirajte ćeliju pa zatim kliknite na 'Data Validation'.
U polju 'Source' postavite formulu:
=OFFSET($J$2;;;COUNTIF($J$2:$J$17;"?*"))

Postavljanje Data Validation za Padajući izbornik

Drugi zavisni padajući izbornik (jedan uvjet)

Na slici ispod uočite kako izgleda drugi zavisni padajući izbornik koji postavljamo preko 'Validation List'. Stavke koje se nalaze na ovom popisu iz 'B' stupca, pripadaju uvjetu iz 'E2' ćelije koji se nalazi u 'A' stupcu. Dakle, u 'A' stupcu za svaku ćeliju u kojoj se nalazi uvjet "Truck", u istom redu u 'B' stupcu nalaze se podaci: "John", "Mike" i "Leo". Također uočite da nema praznih stavki u izborniku.

Zavisna padajuća lista bez praznih ćelija ako se podudara jedan uvjet

U 'F2' ćeliji postavljamo formulu za drugi padajući izbornik. Selektirajte ćeliju pa zatim kliknite na 'Data Validation'.
U polju 'Source' postavite formulu:
=OFFSET($K$2;;;COUNTIF($K$2:$K$11;"?*"))

Kreiranje padajućeg izbornika preko Data Validation List

Treći zavisni padajući izbornik (dva uvjeta)

Na slici ispod uočite kako izgleda treći zavisni padajući izbornik koji postavljamo preko 'Validation List'. Stavke koje se nalaze na ovom popisu, pripadaju uvjetima iz 'E2' i 'F2' ćelija a rezultat se nalazi u 'C' stupcu. Dakle, prikazani su samo oni podaci iz istog reda koji sadrže podatak "Truck" u 'A' stupcu i podatak "John" u 'B' stupcu. I ovdje uočite da nema praznih stavki u izborniku (dakle ne prikazuju se prazne ćelije iz 'L' stupca.

Padajući izbornik zavisi o dva uvjeta

U 'G2' ćeliji postavite formulu za treći padajući izbornik. Selektirajte ćeliju pa zatim kliknite na 'Data Validation'.
U polju 'Source' postavite formulu:
=OFFSET($K$2;;;COUNTIF($K$2:$K$11;"?*"))

U konačnici, mi smo kreirali tri padajuće liste od kojih su dva zavisna padajuća izbornika. I to izgleda kao na slici ispod.

Tri padajuća izbornika ali dvije zavisne padajuće liste sa jedinstvenim podacima bez praznih ćelija

Ako želite koristiti 'Uvjetno oblikovanje' da istaknete redove koji se podudaraju sa uvjetima (kao na slici iznad) tada upotrijebite ovu formulu za cijeli raspon ćelija 'A2:E23'.
=AND($E$2=$A2;$F$2=$B2)


Automatsko brisanje padajućeg izbornika ako promijenimo neki od podataka

Tijekom rada dolazi do potrebe promijeniti određene podatke. U ovom slučaju ako promijenite podatak u 'E2' ćeliji ostali podaci neće se promijeniti automatski. Pa tako u 'E2' ćeliji ako zamijenite 'Truck' sa novim izborom 'Car', drugi podaci u 'F2 i G2' ćelijama ostat će ne promijenjeni. Ovo je zabrinjavajuća situacija i nije dobro, jer možete tokom rada zaboraviti izmijeniti ove podatke. Ovdje nam može pomoći VBA makronaredba koja će automatski izbrisati podatke u 'F2 i G2' ćelijama ako promijenimo podatak u 'E2' ćeliji. Ovu VBA makronaredbu ispod kopirajte u 'Worksheet module' vaše radne knjige. Kliknite desnom tipkom miša na naziv radnog lista (Sheet Tab) pa zatim ovaj VBA code ispod kopirajte u 'Window Code'. Napomena, promijenite u izvornom kodu naziv vašeg radnog lista.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("E2")) Is Nothing Then
Sheets("Sheet1").Range("F2:G2").Value = ""
End If
If Not Intersect(Target, Range("F2")) Is Nothing Then
Sheets("Sheet1").Range("G2").Value = ""
End If
End Sub

Napomena: Ako koristite ovaj VBA kod, tada obavezno snimite vašu radnu knjigu u XLS, XLSM ili XLSB formatu.

Tutoriali vezani za padajući izbornik ili Validation List

Datoteku (u ZIP formatu) na kojoj sam radio ovaj tutorijal, možete preuzeti na vaše računalo na linku ispod
Download 'dva zavisna padajuća izbornika sa jedinstvenim podacima bez praznih ćelija'




     

sponzor



COPYRIGHT © - 2005 - 2020 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