Kako ustvariti spustni seznam v Excelu (edini vodnik, ki ga potrebujete)

Spustni seznam je odličen način, da uporabniku omogočite izbiro na vnaprej določenem seznamu.

Uporablja se lahko med pridobivanjem uporabnika za izpolnjevanje obrazca ali pri ustvarjanju interaktivnih Excelovih nadzornih plošč.

Spustni seznami so na spletnih mestih/v aplikacijah precej pogosti in so za uporabnika zelo intuitivni.

Oglejte si video - Ustvarjanje spustnega seznama v Excelu

V tej vadnici se boste naučili ustvariti spustni seznam v Excelu (za to potrebujete le nekaj sekund) skupaj z vsemi čudovitimi stvarmi, ki jih lahko naredite z njim.

Kako ustvariti spustni seznam v Excelu

V tem razdelku boste izvedeli natančne korake za ustvarjanje spustnega seznama Excel:

1. Uporaba podatkov iz celic.
2. Ročni vnos podatkov.
3. Z uporabo formule OFFSET.

#1 Uporaba podatkov iz celic

Recimo, da imate seznam predmetov, kot je prikazano spodaj:

Za ustvarjanje spustnega seznama Excel:

1. Izberite celico, v kateri želite ustvariti spustni seznam.
2. Pojdite na Podatki -> Podatkovna orodja -> Preverjanje podatkov.
3. V pogovornem oknu Validacija podatkov na zavihku Nastavitve izberite Seznam kot merila preverjanja.
   • Takoj, ko izberete Seznam, se prikaže izvorno polje.
4. V izvorno polje vnesite = $ A $ 2: $ A $ 6 ali preprosto kliknite polje Vir in z miško izberite celice in kliknite V redu. S tem boste v celico C2 vstavili spustni seznam.
   • Prepričajte se, da je označena možnost Spustni meni v celici (ki je privzeto preverjena). Če ta možnost ni označena, celica ne prikaže spustnega menija, lahko pa ročno vnesete vrednosti na seznam.

Opomba: Če želite hkrati ustvariti spustne sezname v več celicah, izberite vse celice, kjer jih želite ustvariti, in sledite zgornjim korakom. Prepričajte se, da so sklice na celice absolutne (na primer $ A $ 2) in ne relativne (na primer A2 ali A $ 2 ali $ A2).

#2 Z ročnim vnosom podatkov

V zgornjem primeru se sklice na celice uporabljajo v polju Vir. Elemente lahko dodate tudi neposredno, tako da jih ročno vnesete v izvorno polje.

Recimo, da želite v spustnem meniju v celici prikazati dve možnosti, Da in Ne. Takole ga lahko vnesete neposredno v polje vira preverjanja veljavnosti podatkov:

• Izberite celico, v kateri želite ustvariti spustni seznam (v tem primeru celica C2).
• Pojdite na Podatki -> Podatkovna orodja -> Preverjanje podatkov.
• V pogovornem oknu Validacija podatkov na zavihku Nastavitve izberite Seznam kot merila preverjanja.
  • Takoj, ko izberete Seznam, se prikaže izvorno polje.
• V izvorno polje vnesite Da, Ne
  • Preverite, ali je spustna možnost V celici označena.
• Kliknite V redu.

To bo ustvarilo spustni seznam v izbrani celici. Vsi elementi, navedeni v izvornem polju, ločeni z vejico, so navedeni v različnih vrsticah v spustnem meniju.

Vsi elementi, vneseni v izvorno polje, ločeni z vejico, so prikazani v različnih vrsticah na spustnem seznamu.

Opomba: Če želite hkrati ustvariti spustne sezname v več celicah, izberite vse celice, kjer jih želite ustvariti, in sledite zgornjim korakom.

#3 Uporaba Excelovih formul

Poleg izbire iz celic in ročnega vnosa podatkov lahko uporabite tudi formulo v izvornem polju za ustvarjanje spustnega seznama Excel.

Za ustvarjanje spustnega seznama v Excelu lahko uporabite katero koli formulo, ki vrne seznam vrednosti.

Recimo, da imate nabor podatkov, kot je prikazano spodaj:

Spodaj so navedeni koraki za ustvarjanje spustnega seznama Excel s funkcijo OFFSET:

• Izberite celico, v kateri želite ustvariti spustni seznam (v tem primeru celica C2).
• Pojdite na Podatki -> Podatkovna orodja -> Preverjanje podatkov.
• V pogovornem oknu Validacija podatkov na zavihku Nastavitve izberite Seznam kot merila preverjanja.
  • Takoj, ko izberete Seznam, se prikaže izvorno polje.
• V polje Vir vnesite naslednjo formulo: = OFFSET ($ A $ 2,0,0,5)
  • Preverite, ali je spustna možnost V celici označena.
• Kliknite V redu.

To bo ustvarilo spustni seznam, ki navaja vsa imena sadja (kot je prikazano spodaj).

Opomba: Če želite hkrati ustvariti spustni seznam v več celicah, izberite vse celice, v katerih ga želite ustvariti, in sledite zgornjim korakom. Prepričajte se, da so sklice na celice absolutne (na primer $ A $ 2) in ne relativne (na primer A2 ali A $ 2 ali $ A2).

Kako deluje ta formula ??

V zgornjem primeru smo za ustvarjanje spustnega seznama uporabili funkcijo OFFSET. Vrne seznam elementov iz ra

Vrne seznam elementov iz območja A2: A6.

Tukaj je sintaksa funkcije OFFSET: = OFFSET (sklic, vrstice, stolpci, [višina], [širina])

Potrebuje pet argumentov, kjer smo referenco podali kot A2 (izhodišče seznama). Vrstice/polja so podane kot 0, ker ne želimo izravnati referenčne celice. Višina je določena kot 5, saj je na seznamu pet elementov.

Ko uporabite to formulo, vrne polje, ki vsebuje seznam petih plodov v A2: A6. Upoštevajte, da če formulo vnesete v celico, jo izberete in pritisnete F9, boste videli, da vrne niz imen sadja.

Ustvarjanje dinamičnega spustnega seznama v Excelu (z uporabo OFFSET)

Zgornjo tehniko uporabe formule za ustvarjanje spustnega seznama je mogoče razširiti, da ustvarite tudi dinamični spustni seznam. Če uporabljate funkcijo OFFSET, kot je prikazano zgoraj, tudi če na seznam dodate več elementov, se spustni meni ne bo samodejno posodobil. Vsakič, ko spremenite seznam, ga boste morali ročno posodobiti.

Tukaj je način, kako ga narediti dinamičnega (in to ni nič drugega kot manjši popravek v formuli):

• Izberite celico, v kateri želite ustvariti spustni seznam (v tem primeru celica C2).
• Pojdite na Podatki -> Podatkovna orodja -> Preverjanje podatkov.
• V pogovornem oknu Validacija podatkov na zavihku Nastavitve izberite Seznam kot merila preverjanja. Takoj, ko izberete Seznam, se prikaže izvorno polje.
• V izvorno polje vnesite naslednjo formulo: = OFFSET ($ 2,0,0 $, COUNTIF ($ A $ 2: $ A $ 100, ""))
• Preverite, ali je spustna možnost V celici označena.
• Kliknite V redu.

V tej formuli sem argument 5 zamenjal z COUNTIF ($ A $ 2: $ A $ 100, ””).

Funkcija COUNTIF šteje neprazne celice v območju A2: A100. Zato se funkcija OFFSET prilagodi tako, da vključuje vse celice, ki niso prazne.

Opomba:

• Da bi to delovalo, med celicami, ki so zapolnjene, NE sme biti nobenih praznih celic.
• Če želite naenkrat ustvariti spustni seznam v več celicah, izberite vse celice, v katerih ga želite ustvariti, in sledite zgornjim korakom. Prepričajte se, da so sklice na celice absolutne (na primer $ A $ 2) in ne relativne (na primer A2 ali A $ 2 ali $ A2).

Kopirajte spustne sezname lepljenja v Excelu

Celice z validacijo podatkov lahko kopirate v druge celice, prav tako pa bo kopiralo tudi preverjanje podatkov.

Na primer, če imate spustni seznam v celici C2 in ga želite uporabiti tudi za C3: C6, preprosto kopirajte celico C2 in jo prilepite v C3: C6. S tem boste kopirali spustni seznam in ga dali na voljo v C3: C6 (skupaj s spustnim menijem bo tudi kopiralo oblikovanje).

Če želite kopirati samo spustni meni in ne oblikovanja, so tukaj naslednji koraki:

• Kopirajte celico s spustnim menijem.
• V spustnem meniju izberite celice, kamor želite kopirati.
• Pojdite na dom -> Prilepi -> Posebno lepljenje.
• V pogovornem oknu Prilepi posebno izberite Preverjanje v možnostih lepljenja.
• Kliknite V redu.

To bo samo kopiralo spustni meni in ne oblikovanje kopirane celice.

Previdno pri delu s spustnim seznamom Excel

Pri delu s spustnimi seznami v Excelu morate biti previdni.

Ko kopirate celico (ki ne vsebuje spustnega seznama) nad celico, ki vsebuje spustni seznam, se spustni seznam izgubi.

Najslabši del tega je, da Excel ne prikaže nobenega opozorila ali poziva, ki uporabniku sporoči, da bo spustni meni prepisan.

Kako izbrati vse celice, ki imajo spustni seznam

Včasih je težko vedeti, katere celice vsebujejo spustni seznam.

Zato je smiselno te celice označiti tako, da jim damo posebno obrobo ali barvo ozadja.

Namesto ročnega preverjanja vseh celic obstaja hiter način, da izberete vse celice, v katerih so spustni seznami (ali katero koli pravilo preverjanja podatkov).

• Pojdite na domačo stran -> Najdi in izberite -> Pojdi na posebno.
• V pogovornem oknu Pojdi na posebno izberite Preverjanje podatkov
  • Preverjanje podatkov ima dve možnosti: vse in enako. Vsi bi izbrali vse celice, za katere velja pravilo preverjanja veljavnosti podatkov. Enako bi izbrali samo tiste celice, ki imajo enako pravilo preverjanja podatkov kot celica aktivne celice.
• Kliknite V redu.

Tako bi takoj izbrali vse celice, za katere velja pravilo preverjanja veljavnosti podatkov (to vključuje tudi spustne sezname).

Zdaj lahko preprosto formatirate celice (dajte obrobo ali barvo ozadja), tako da so vidno vidne in ne boste pomotoma kopirali druge celice.

Tukaj je še ena tehnika Jona Acampore, ki jo lahko uporabite, da je ikona puščice navzdol vedno vidna. Nekaj ​​načinov za to si lahko ogledate tudi v tem videoposnetku gospoda Excela.

Ustvarjanje odvisnega / pogojnega spustnega seznama Excel

Tukaj je video o tem, kako ustvariti odvisen spustni seznam v Excelu.

Če raje berete, kot gledate video, nadaljujte z branjem.

Včasih imate morda več kot en spustni seznam in želite, da so elementi, prikazani v drugem spustnem meniju, odvisni od tega, kaj je uporabnik izbral v prvem spustnem meniju.

Ti se imenujejo odvisni ali pogojni spustni seznami.

Spodaj je primer pogojnega/odvisnega spustnega seznama:

V zgornjem primeru, ko so elementi, navedeni v "spustnem seznamu 2", odvisni od izbire, izbrane v "spustnem seznamu 1".

Zdaj pa poglejmo, kako to ustvariti.

Tu so koraki za ustvarjanje odvisnega / pogojnega 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 pri delu s pogojnimi spustnimi seznami v Excelu:

• Ko ste izbrali, nato pa spremenite nadrejeni spustni meni, se odvisni spustni meni ne bi spremenil in bi bil zato napačen vnos. Če na primer za državo izberete ZDA in nato za državo izberete Florido, nato pa se vrnete in državo spremenite v Indijo, bo država ostala kot Florida. Tukaj je odlična vadba Debre o brisanju odvisnih (pogojnih) spustnih seznamov v Excelu, ko se izbira spremeni.
• Č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. Tako bi bilo poimenovano območje "Sezonsko sadje" "Sezonsko sadje". Uporaba funkcije SUBSTITUTE v funkciji INDIRECT poskrbi za presledke so pretvori v podčrtaje.