Kako povezati padajući popis u Excelu

Želite li naučiti kako povezati padajući popis u Excelu ? Padajući popis u Excelu korisna je značajka prilikom izrade obrazaca za unos podataka ili Excel nadzornih ploča.

Prikazujete popis stavki kao padajući izbornik u ćeliji, a korisnik može napraviti odabir s padajućeg izbornika. Ovo bi moglo biti korisno kada imate popis imena, proizvoda ili regija koje često morate unijeti u skup ćelija.

Primjer padajuće liste u Excelu

Evo primjera Excel padajućeg popisa:

Ovdje možete pročitati o: Kako kopirati Excel list u drugu radnu knjigu – Vodič

U primjeru prikazanom na slici, elementi u A2: A6 korišteni su za stvaranje padajućeg izbornika u C3. Međutim, ponekad ćete možda htjeti koristiti više od jednog padajućeg popisa u Excelu tako da stavke dostupne na drugom padajućem popisu ovise o odabiru na prvom padajućem popisu.

Oni se u Excelu nazivaju ovisni padajući popisi.

Ispod je primjer onoga što želimo objasniti s padajućim popisom ovisnosti u Excelu:

Možete vidjeti da opcije u padajućem izborniku 2 ovise o odabiru u padajućem izborniku 1.

Ako u padajućem izborniku 1 odaberete 'Voće' , prikazat će se nazivi voća, ali ako u padajućem izborniku 1 odaberete 'Povrće' , tada će se nazivi povrća prikazati u padajućem izborniku 2. To se u Excelu naziva uvjetni ili ovisni padajući popis.

Stvorite zavisni padajući popis u Excelu

Evo koraka za stvaranje ovisnog padajućeg popisa u Excelu:

• korak 1: Odaberite ćeliju u kojoj želite prvi (glavni) padajući popis.
• korak 2: ići Podaci -> Provjera valjanosti podataka. Ovo će otvoriti dijaloški okvir za provjeru valjanosti podataka.

• korak 3: U dijaloškom okviru za provjeru valjanosti podataka, unutar kartice konfiguracije, odaberite opciju popis.

• korak 4: U prirodi Izvor, navodi raspon koji sadrži stavke za prikaz na prvom padajućem popisu.

• korak 5: Kliknite prihvatiti. Ovo će stvoriti padajući izbornik 1.

• korak 6: Odaberite cijeli skup podataka (A1:B6 u ovom primjeru).

• korak 7: Ići Formule -> Definirana imena -> Stvori iz odabira (ili možete koristiti tipkovni prečac Control + Shift + F3).

• korak 8: U dijaloškom okviru 'Stvorite ime iz odabira', označite opciju Gornji red i isključite sve ostale. Time se stvaraju 2 raspona naziva ('Voće' i 'Povrće'). Raspon Named Fruit odnosi se na svo voće na popisu, a raspon Named Vegetables odnosi se na sve povrće na popisu.

• korak 9: Kliknite prihvatiti.
• korak 10: Odaberite ćeliju u kojoj želite padajući popis Ovisno/Uvjetno (E3 u ovom primjeru).
• korak 11: Ići Podaci -> Provjera valjanosti podataka.

• korak 12: U dijaloškom okviru Provjera valjanosti podataka, unutar kartice Konfiguracije, Pobrinite se za to popis je odabran.

• korak 13: U Izvorno polje, unesite formulu = NEIZRAVNO (D3). Ovdje je D3 ćelija koja sadrži glavni padajući izbornik.

• korak 14: Kliknite prihvatiti.

Sada, kada odaberete u padajućem izborniku 1, opcije navedene u padajućem izborniku 2 ažurirat će se automatski.

Kako se to radi?

Kako ovo funkcionira? – Uvjetni padajući popis u Excelu (u ćeliji E3) odnosi se na =INDIRECT(D3). To znači da kada odaberete ' Voće ' u ćeliji D3, padajući popis u E3 odnosi se na raspon pod nazivom 'Voće' (putem funkcije INDIRECT ) i stoga navodi sve stavke u toj kategoriji.

• Važna napomena:ako je roditeljska kategorija više od jedne riječi (npr. 'Sezonsko voće'radije'Voće'), tada morate koristiti formula = INDIREKTNO (ZAMJENA (D3,”“,”_”)), umjesto jednostavne INDIRECT funkcije prikazane gore.

Razlog tome je što Excel ne dopušta razmake u imenovanim rasponima. Dakle, kada stvorite imenovani raspon koristeći više od jedne riječi, Excel automatski umeće podvlaku između riječi.

Na primjer : kada stvorite imenovani raspon s 'Sezonsko voće' , on će se u pozadini zvati Sezonsko_voće . Korištenje funkcije SUBSTITUTE unutar funkcije INDIRECT osigurava da se razmaci pretvaraju u podvlake.

Automatsko poništavanje/brisanje ovisnog sadržaja padajućeg popisa

Kada ste odabrali i zatim promijenili nadređeni padajući izbornik, ovisni padajući izbornik se neće promijeniti i stoga će biti netočan unos.

• Na primjer: Ako odaberete 'Voće' lajkajte kategoriju, a zatim odaberite jabuka kao stavku, a zatim se vratite i promijenite kategoriju u 'Povrće', zavisni padajući izbornik nastavit će se prikazivati jabuka kao element.

Možete upotrijebiti VBA kako biste osigurali da se sadržaj ovisnog padajućeg popisa poništi kad god se promijeni nadređeni padajući popis. Evo VBA koda za brisanje sadržaja ovisnog padajućeg popisa:

Privatni podradni list_Promjena (ByVal Target As Range)

U slučaju pogreške nastavite dalje

Ako je Target.Column = 4 Onda

Ako je Target.Validation.Type = 3 Zatim

Application.EnableEvents = False

Target.Offset(0, 1).ClearContents

Završit će ako

Završit će ako

ExitHandler:

Application.EnableEvents = True

Izlaz iz podv

End Sub

Kako se to radi?

Evo kako učiniti da ovaj kod radi:

• korak 1: Kopirajte VBA kod.
• korak 2: U Excel radnoj knjizi gdje imate zavisni padajući popis, idite na Kartica za programere, i unutar grupe 'Kodirati'klik Visual Basic (možete koristiti i tipkovni prečac – ALT + F11).

• korak 3: U prozoru VB editora, s lijeve strane u pregledniku projekta, vidjet ćete sve nazive radnih listova. Dvaput kliknite onu s padajućeg popisa.

• korak 4: Zalijepite kod u prozor koda s desne strane.

• korak 5: Zatvorite VB editor.

Sada, svaki put kada promijenite roditeljski padajući popis, VBA kod će se pokrenuti i sadržaj zavisnog padajućeg popisa će se izbrisati (kao što je prikazano u nastavku).

Ako niste stručnjak za VBA, također možete koristiti jednostavan trik uvjetnog oblikovanja koji će istaknuti ćeliju kad god postoji neslaganje. To vam može pomoći da vidite i vizualno ispravite neusklađenost (kao što je prikazano u nastavku).

Evo koraka za isticanje odstupanja u ovisnim padajućim popisima:

• korak 1: Odaberite ćeliju koja ima zavisne padajuće popise.
• korak 2: Ići Početna -> Uvjetno oblikovanje -> Novo pravilo.

• korak 3: U dijaloškom okviru Novo pravilo format, Odaberite 'Pomoću formule odredite koje ćelije format'.

• korak 4: U polje formule unesite sljedeću formulu:=ESERROR(VLOOKUP(E3,INDEX($A$2:$B$6,,MATCH(D3,$A$1:$B$1)), 1,0))

• korak 5: Postavite format.
• korak 6: Kliknite OK.

Možda će vas zanimati i: Kako grupirati pivot tablicu po mjesecima u Excelu

Formula koristi funkciju VLOOKUP kako bi provjerila je li stavka na ovisnom padajućem popisu ona u nadređenoj kategoriji. Ako nije, formula vraća pogrešku. Funkcija ISERROR koristi to za vraćanje vrijednosti TRUE, što uvjetnom oblikovanju govori da označi ćeliju.

Kao što vidite, ovo je ispravan način povezivanja padajućeg popisa u Excelu. Kad god je to moguće, koristite ovaj kratki vodič za vježbu kako biste naučili koristiti ovu korisnu značajku. Nadamo se da vam je ovo bilo korisno.