Stvaranje ovisnog padajućeg popisa u Excelu (Korak po korak vodič)

Gledajte video - Stvaranje ovisnog padajućeg popisa u Excelu

Padajući popis programa Excel korisna je značajka pri izradi obrazaca za unos podataka ili Excelove nadzorne ploče.

Prikazuje popis stavki kao padajući izbornik u ćeliji, a korisnik može odabrati odabir s padajućeg izbornika. To bi moglo biti korisno ako imate popis naziva, proizvoda ili regija koje često trebate unijeti u skup ćelija.

Ispod je primjer padajućeg popisa programa Excel:

U gornjem primjeru koristio sam stavke u A2: A6 za stvaranje padajućeg izbornika u C3.

Čitati: Evo detaljnog vodiča o tome kako stvoriti Excel padajući popis.

Međutim, ponekad ćete možda htjeti upotrijebiti 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.

To se u Excelu naziva ovisnim padajućim popisima.

Ispod je primjer onoga što mislim pod ovisnim padajućim popisom u Excelu:

Možete vidjeti da opcije u padajućem izborniku 2 ovise o odabiru u padajućem izborniku 1. Ako odaberem 'Voće' u padajućem izborniku 1, prikazat će mi se nazivi voća, ali ako odaberem Povrće u padajućem izborniku 1, tada prikazani su mi nazivi povrća u padajućem izborniku 2.

To se naziva uvjetni ili ovisni padajući popis u Excelu.

Stvaranje ovisnog padajućeg popisa u Excelu

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

• Odaberite ćeliju u kojoj želite prvi (glavni) padajući popis.
• Idite na Podaci -> Validacija podataka. Ovo će otvoriti dijaloški okvir za provjeru valjanosti podataka.
• U dijaloškom okviru za provjeru valjanosti podataka na kartici postavki odaberite Popis.
• U polju Izvor navedite raspon koji sadrži stavke koje će se prikazati na prvom padajućem popisu.
• Pritisnite U redu. Ovo će stvoriti padajući izbornik 1.
• Odaberite cijeli skup podataka (A1: B6 u ovom primjeru).
• Idite na Formule -> Definirani nazivi -> Stvori iz odabira (ili možete koristiti tipkovnički prečac Control + Shift + F3).
• U dijaloškom okviru "Kreiraj ime iz odabira" označite opciju Gornji red i poništite sve ostale. Time se stvaraju 2 raspona naziva ("Voće" i "Povrće"). Asortiman voća odnosi se na svo voće na popisu, a naziv Povrće na svo povrće na popisu.
• Pritisnite U redu.
• Odaberite ćeliju u kojoj želite ovisni/uvjetni padajući popis (E3 u ovom primjeru).
• Idite na Podaci -> Validacija podataka.
• U dijaloškom okviru Provjera valjanosti podataka na kartici postavki provjerite je li odabran Popis u.
• U polje Izvor unesite formulu = INDIREKTNO (D3). Ovdje je D3 ćelija koja sadrži glavni padajući izbornik.
• Pritisnite U redu.

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

Preuzmite datoteku primjera

Kako ovo radi? - Uvjetni padajući popis (u ćeliji E3) odnosi se na = INDIREKTNO (D3). To znači da kada odaberete "Voće" u ćeliji D3, padajući popis u E3 odnosi se na imenovani raspon "Voće" (putem funkcije INDIREKT) i stoga navodi sve stavke u toj kategoriji.

Važna nota: Ako je glavna kategorija više od jedne riječi (na primjer, "Sezonsko voće" umjesto "Voće"), tada morate koristiti formulu = INDIREKTNO (ZAMJENA (D3, "", "_")), umjesto jednostavna INDIRECT funkcija prikazana gore.

• Razlog tome je što Excel ne dopušta razmake u imenovanim rasponima. Dakle, kada kreirate imenovani raspon koristeći više od jedne riječi, Excel automatski umetne podcrtavanje između riječi. Na primjer, kada stvorite imenovani raspon sa "Sezonsko voće", on će se u pozadini nazvati Sezona_Fruits. Korištenje funkcije SUBSTITUTE unutar funkcije INDIRECT osigurava razmake su pretvorene u podvlake.

Automatski poništi/obriši sadržaj ovisnog padajućeg popisa

Kada izvršite odabir, a zatim promijenite nadređeni padajući izbornik, ovisni padajući popis se ne bi promijenio i stoga bi bio pogrešan unos.

Na primjer, ako odaberete "Voće" kao kategoriju, a zatim kao stavku odaberete Apple, a zatim se vratite i promijenite kategoriju u "Povrće", ovisni padajući izbornik nastavit će prikazivati ​​Apple kao stavku.

Možete koristiti VBA kako biste bili sigurni da se sadržaj ovisnog padajućeg popisa poništava kad god se glavni padajući popis promijeni.

Evo VBA koda za brisanje sadržaja ovisnog padajućeg popisa:

Private Sub Worksheet_Change (ByVal Target As Range) On Error Nastavi dalje If Target.Column = 4 then If Target.Validation.Type = 3 Then Application.EnableEvents = False Target.Offset (0, 1) .ClearContents End If End If exitHandler: Application.EnableEvents = Potpuni podzavršetak istinskog izlaza

Zasluga za ovaj kôd pripada ovom Vodiču za brisanje ovisnih padajućih popisa u Excelu pri promjeni odabira.

Evo kako ovaj kôd funkcionirati:

• Kopirajte VBA kôd.
• U radnoj knjizi programa Excel gdje imate ovisni padajući popis, idite na karticu Razvojni programer, a unutar grupe ‘Kôd’ kliknite Visual Basic (možete koristiti i tipkovnički prečac - ALT + F11).
• U prozoru VB Editor, s lijeve strane u istraživaču projekata, vidjeli biste sve nazive radnih listova. Dvaput kliknite na onu s padajućim popisom.
• Zalijepite kôd u kôd prozor s desne strane.
• Zatvorite VB Editor.

Sada, kad god promijenite glavni padajući popis, VBA kôd bi se pokrenuo i izbrisao bi sadržaj ovisnog padajućeg popisa (kao što je prikazano u nastavku).

Ako niste ljubitelj VBA -e, možete upotrijebiti i jednostavan trik s uvjetnim oblikovanjem koji će istaknuti ćeliju kad god dođe do neslaganja. To vam može pomoći da vizualno vidite i ispravite neusklađenost (kao što je prikazano u nastavku).

Evo koraka t0 koji ističu neusklađenosti na ovisnim padajućim popisima:

• Odaberite ćeliju koja ima ovisne padajuće popise.
• Idite na Početna -> Uvjetno oblikovanje -> Novo pravilo.
• U dijaloškom okviru Novo pravilo oblikovanja odaberite "Koristi formulu za određivanje ćelija koje ćete oblikovati".
• U polje formule unesite sljedeću formulu: = POGREŠKA (VLOOKUP (E3, INDEKS ($ A $ 2: $ B $ 6,, MATCH (D3, $ A $ 1: $ B $ 1)), 1,0))
• Postavite format.
• Pritisnite U redu.

Formula koristi funkciju VLOOKUP za provjeru je li stavka na ovisnom padajućem popisu ona iz glavne kategorije ili nije. Ako nije, formula vraća pogrešku. To koristi funkcija ISERROR za vraćanje TRUE koja govori uvjetnom oblikovanju da označi ćeliju.

Možda će vam se svidjeti i sljedeći Excel vodiči:

• Izdvajanje podataka na temelju odabira padajućeg popisa.
• Izrada padajućeg popisa s prijedlozima pretraživanja.
• Odaberite više stavki s padajućeg popisa.
• Izradite više padajućih popisa bez ponavljanja.
• Uštedite vrijeme pomoću obrazaca za unos podataka u Excelu.