Ustvarjanje odvisnega spustnega seznama v Excelu (Vadnica po korakih)

Oglejte si video - Ustvarjanje odvisnega spustnega seznama v Excelu

Spustni seznam Excel je uporabna funkcija pri ustvarjanju obrazcev za vnos podatkov ali Excelovih nadzornih plošč.

Seznam elementov prikazuje kot spustni meni v celici, uporabnik pa lahko izbere iz spustnega menija. To je lahko koristno, če imate seznam imen, izdelkov ali regij, ki jih morate pogosto vnesti v niz celic.

Spodaj je primer spustnega seznama Excel:

V zgornjem primeru sem uporabil postavke v A2: A6 za ustvarjanje spustnega menija v C3.

Preberite: Tukaj je podroben vodnik o tem, kako ustvariti spustni seznam v Excelu.

Včasih pa boste morda želeli uporabiti več kot en spustni seznam v Excelu, tako da so elementi, ki so na voljo na drugem spustnem seznamu, odvisni od izbire na prvem spustnem seznamu.

Ti se v Excelu imenujejo odvisni spustni seznami.

Spodaj je primer, kaj mislim s odvisnim spustnim seznamom v Excelu:

Vidite lahko, da so možnosti v spustnem meniju 2 odvisne od izbire v spustnem meniju 1. Če v spustnem seznamu 1 izberem 'Sadje', se mi prikažejo imena sadja, če pa v spustnem seznamu 1 izberem zelenjavo, potem v spustnem meniju 2 so prikazana imena zelenjave.

To se v Excelu imenuje pogojni ali odvisni spustni seznam.

Ustvarjanje odvisnega spustnega seznama v Excelu

Tu so koraki za ustvarjanje odvisnega spustnega seznama v Excelu:

• Izberite celico, v kateri želite prvi (glavni) spustni seznam.
• Pojdite na Podatki -> Preverjanje podatkov. S tem se odpre pogovorno okno za preverjanje podatkov.
• V pogovornem oknu za preverjanje podatkov na zavihku z nastavitvami izberite Seznam.
• V polju Vir navedite obseg, ki vsebuje postavke, ki naj bodo prikazane na prvem spustnem seznamu.
• Kliknite V redu. To bo ustvarilo spustni meni 1.
• Izberite celoten nabor podatkov (A1: B6 v tem primeru).
• Pojdite na Formule -> Določena imena -> Ustvari iz izbora (ali pa uporabite bližnjico na tipkovnici Control + Shift + F3).
• V pogovornem oknu »Ustvari ime iz izbora« potrdite možnost Vrstna vrstica in počistite vse ostale. S tem nastaneta 2 obsega imen ("Sadje" in "Zelenjava"). Območje poimenovano sadje se nanaša na vse sadje na seznamu, imenovano območje zelenjava pa se nanaša na vso zelenjavo na seznamu.
• Kliknite V redu.
• Izberite celico, v kateri želite spustni seznam odvisnih/pogojno (E3 v tem primeru).
• Pojdite na Podatki -> Preverjanje podatkov.
• V pogovornem oknu Preverjanje podatkov na zavihku z nastavitvami preverite Seznam v izbranem.
• V polje Vir vnesite formulo = NEPOSREDNO (D3). Tu je D3 celica, ki vsebuje glavni spustni meni.
• Kliknite V redu.

Ko se odločite na spustnem seznamu 1, se bodo možnosti, navedene na spustnem seznamu 2, samodejno posodobile.

Prenesite datoteko z vzorcem

Kako to deluje? - Pogojni spustni seznam (v celici E3) se nanaša na = INDIRECT (D3). To pomeni, da ko v celici D3 izberete „Sadje“, se spustni seznam v E3 nanaša na imenovano območje „Sadje“ (s funkcijo INDIRECT) in zato našteje vse postavke v tej kategoriji.

Pomembna opomba: Če je glavna kategorija več kot ena beseda (na primer „Sezonsko sadje“ namesto „Sadje“), morate uporabiti formulo = NEPOSREDNO (NAMESTNIK (D3, ”„, ”_”)), namesto preprosta zgoraj prikazana funkcija INDIRECT.

• Razlog za to je, da Excel ne dovoljuje presledkov v imenovanih obsegih. Ko torej ustvarite poimenovano območje z uporabo več besed, Excel samodejno vstavi podčrtaj med besedami. Ko na primer ustvarite poimenovano območje z 'Sezonsko sadje', bo v zaledju poimenovano Sezona_Fruits. Uporaba funkcije SUBSTITUTE v funkciji INDIRECT poskrbi za presledke so pretvori v podčrtaje.

Samodejno ponastavi/počisti vsebino odvisnega spustnega seznama

Ko ste izbrali in nato spremenite nadrejeni spustni meni, se odvisni spustni seznam ne bi spremenil in bi bil zato napačen vnos.

Če na primer za kategorijo izberete »Sadje« in nato za izdelek izberete Apple, nato pa se vrnete nazaj in kategorijo spremenite v »Zelenjava«, bo odvisni spustni meni še naprej prikazal Apple kot element.

Z VBA lahko preverite, ali se vsebina odvisnega spustnega seznama ponastavi, kadar koli spremenite glavni spustni seznam.

Tu je koda VBA za brisanje vsebine odvisnega spustnega seznama:

Private Sub Worksheet_Change (ByVal Target As Range) Napaka Nadaljuj Naprej Če je Target.Column = 4 Potem Če Target.Validation.Type = 3 Potem Application.EnableEvents = False Target.Offset (0, 1) .ClearContents End If End Če exitHandler: Application.EnableEvents = Podrejen podkončni podvig z resničnim izhodom

Zasluga za to kodo je v tej vadnici Debra pri brisanju odvisnih spustnih seznamov v Excelu, ko se izbira spremeni.

Takole lahko naredite to kodo:

• Kopirajte kodo VBA.
• V Excelovem delovnem zvezku, kjer imate odvisen spustni seznam, pojdite na zavihek Razvijalec in v skupini »Koda« kliknite Visual Basic (lahko uporabite tudi bližnjico na tipkovnici - ALT + F11).
• V oknu urejevalnika VB na levi strani raziskovalca projektov bi videli vsa imena delovnih listov. Dvokliknite tisto, ki ima spustni seznam.
• Kodo prilepite v okno za kodo na desni.
• Zaprite urejevalnik VB.

Zdaj, ko spremenite glavni spustni seznam, bi se sprožila koda VBA in počistila bi vsebino odvisnega spustnega seznama (kot je prikazano spodaj).

Če niste ljubitelj VBA, lahko uporabite tudi preprost trik s pogojnim oblikovanjem, ki bo celico označil, kadar pride do neskladja. To vam lahko pomaga vizualno videti in popraviti neusklajenost (kot je prikazano spodaj).

Tu so koraki t0, ki označujejo neusklajenost na odvisnih spustnih seznamih:

• Izberite celico, ki ima odvisne spustne sezname.
• Pojdite na dom -> Pogojno oblikovanje -> Novo pravilo.
• V pogovornem oknu Novo pravilo oblikovanja izberite »Uporabi formulo za določitev celic, ki jih želite oblikovati«.
• V polje formule vnesite naslednjo formulo: = NAPAKA (VLOOKUP (E3, INDEX ($ A $ 2: $ B $ 6,, MATCH (D3, $ A $ 1: $ B $ 1)), 1,0))
• Nastavite obliko.
• Kliknite V redu.

Formula uporablja funkcijo VLOOKUP za preverjanje, ali je element na odvisnem spustnem seznamu tisti iz glavne kategorije ali ne. Če ni, formula vrne napako. To funkcija ISERROR uporablja za vrnitev TRUE, ki pove pogojnemu oblikovanju, da označi celico.

Morda vam bodo všeč tudi naslednje vaje za Excel:

• Izvlecite podatke na podlagi izbire spustnega seznama.
• Ustvarjanje spustnega seznama s predlogi za iskanje.
• Na spustnem seznamu izberite več elementov.
• Ustvarite več spustnih seznamov brez ponavljanja.
• Prihranite čas z obrazci za vnos podatkov v Excelu.