Ako vytvoriť rozbaľovací zoznam v programe Excel (jediný sprievodca, ktorý potrebujete)

Rozbaľovací zoznam je vynikajúcim spôsobom, ako poskytnúť používateľovi možnosť vybrať si z vopred definovaného zoznamu.

Dá sa použiť pri prinútení používateľa vyplniť formulár alebo pri vytváraní interaktívnych dashboardov Excelu.

Rozbaľovacie zoznamy sú na webových stránkach/aplikáciách pomerne bežné a sú pre používateľa veľmi intuitívne.

Pozrite si video - Vytvorenie rozbaľovacieho zoznamu v programe Excel

V tomto tutoriále sa naučíte, ako vytvoriť rozbaľovací zoznam v programe Excel (jeho vykonanie trvá iba niekoľko sekúnd) spolu so všetkým úžasným, čo s ním môžete robiť.

Ako vytvoriť rozbaľovací zoznam v programe Excel

V tejto časti sa naučíte presné kroky na vytvorenie rozbaľovacieho zoznamu programu Excel:

1. Použitie údajov z buniek.
2. Ručné zadávanie údajov.
3. Pomocou vzorca OFFSET.

#1 Používanie údajov z buniek

Povedzme, že máte zoznam položiek, ako je uvedené nižšie:

Tu je postup, ako vytvoriť rozbaľovací zoznam programu Excel:

1. Vyberte bunku, v ktorej chcete vytvoriť rozbaľovací zoznam.
2. Prejdite na položku Údaje -> Nástroje údajov -> Overenie údajov.
3. V dialógovom okne Overenie údajov na karte Nastavenia vyberte ako kritériá overenia zoznam.
   • Hneď ako vyberiete Zoznam, zobrazí sa zdrojové pole.
4. Do poľa zdroja zadajte = $ A $ 2: $ A $ 6 alebo jednoducho kliknite do poľa Zdroj a vyberte bunky pomocou myši a kliknite na tlačidlo OK. Tým sa do bunky C2 vloží rozbaľovací zoznam.
   • Uistite sa, že je začiarknutá možnosť Rozbaľovacia ponuka v bunke (ktorá je v predvolenom nastavení začiarknutá). Ak nie je táto možnosť začiarknutá, bunka nezobrazuje rozbaľovaciu ponuku, hodnoty však môžete do zoznamu zadať ručne.

Poznámka: Ak chcete vytvoriť rozbaľovacie zoznamy vo viacerých bunkách naraz, vyberte všetky bunky, v ktorých ich chcete vytvoriť, a potom postupujte podľa vyššie uvedených krokov. Uistite sa, že odkazy na bunky sú absolútne (napríklad $ A $ 2) a nie relatívne (napríklad A2 alebo A $ 2 alebo $ A2).

#2 Ručným zadaním údajov

Vo vyššie uvedenom príklade sa odkazy na bunky používajú v poli Zdroj. Položky môžete pridávať aj priamo tak, že ich zadáte ručne do poľa zdroja.

Povedzme napríklad, že chcete v rozbaľovacom zozname v bunke zobraziť dve možnosti, Áno a Nie. Takto ho môžete priamo zadať do poľa zdroja overenia údajov:

• Vyberte bunku, v ktorej chcete vytvoriť rozbaľovací zoznam (v tomto prípade bunka C2).
• Prejdite na položku Údaje -> Nástroje údajov -> Overenie údajov.
• V dialógovom okne Overenie údajov na karte Nastavenia vyberte ako kritériá overenia zoznam.
  • Hneď ako vyberiete Zoznam, zobrazí sa zdrojové pole.
• Do poľa zdroja zadajte Áno, Nie
  • Uistite sa, že je začiarknutá možnosť Rozbaľovacia ponuka v bunke.
• Kliknite na tlačidlo OK.

Vo vybratej bunke sa tak vytvorí rozbaľovací zoznam. Všetky položky uvedené v zdrojovom poli oddelené čiarkou sú v rozbaľovacej ponuke uvedené v rôznych riadkoch.

Všetky položky zadané do zdrojového poľa oddelené čiarkou sa v rozbaľovacom zozname zobrazujú v rôznych riadkoch.

Poznámka: Ak chcete vytvoriť rozbaľovacie zoznamy vo viacerých bunkách naraz, vyberte všetky bunky, v ktorých ich chcete vytvoriť, a potom postupujte podľa vyššie uvedených krokov.

#3 Používanie vzorcov programu Excel

Okrem výberu z buniek a ručného zadávania údajov môžete na vytvorenie rozbaľovacieho zoznamu programu Excel použiť aj vzorec v zdrojovom poli.

Na vytvorenie rozbaľovacieho zoznamu v programe Excel je možné použiť akýkoľvek vzorec, ktorý vracia zoznam hodnôt.

Predpokladajme napríklad, že máte množinu údajov, ako je uvedené nižšie:

Tu je postup, ako vytvoriť rozbaľovací zoznam programu Excel pomocou funkcie OFFSET:

• Vyberte bunku, v ktorej chcete vytvoriť rozbaľovací zoznam (v tomto prípade bunka C2).
• Prejdite na položku Údaje -> Nástroje údajov -> Overenie údajov.
• V dialógovom okne Overenie údajov na karte Nastavenia vyberte ako overovacie kritériá položku Zoznam.
  • Hneď ako vyberiete položku Zoznam, zobrazí sa zdrojové pole.
• Do poľa Zdroj zadajte nasledujúci vzorec: = OFFSET ($ A $ 2,0,0,5)
  • Uistite sa, že je začiarknutá možnosť Rozbaľovacia ponuka v bunke.
• Kliknite na tlačidlo OK.

Vytvorí sa rozbaľovací zoznam, v ktorom sú uvedené všetky názvy plodov (ako je uvedené nižšie).

Poznámka: Ak chcete vytvoriť rozbaľovací zoznam vo viacerých bunkách naraz, vyberte všetky bunky, v ktorých ho chcete vytvoriť, a potom postupujte podľa vyššie uvedených krokov. Uistite sa, že odkazy na bunky sú absolútne (napríklad $ A $ 2) a nie relatívne (napríklad A2 alebo A $ 2 alebo $ A2).

Ako tento vzorec funguje ??

Vo vyššie uvedenom prípade sme na vytvorenie rozbaľovacieho zoznamu použili funkciu OFFSET. Vráti zoznam položiek z ra

Vráti zoznam položiek z rozsahu A2: A6.

Tu je syntax funkcie OFFSET: = OFFSET (odkaz, riadky, stĺpce, [výška], [šírka])

Trvá päť argumentov, kde sme špecifikovali referenciu ako A2 (počiatočný bod zoznamu). Riadky/stĺpce sú uvedené ako 0, pretože nechceme odsadiť referenčnú bunku. Výška je uvedená ako 5, pretože v zozname je päť prvkov.

Keď teraz použijete tento vzorec, vráti pole, ktoré má zoznam piatich plodov v A2: A6. Všimnite si toho, že ak zadáte vzorec do bunky, vyberte ho a stlačte F9, uvidíte, že vráti pole názvov plodov.

Vytvorenie dynamického rozbaľovacieho zoznamu v programe Excel (pomocou OFFSET)

Vyššie uvedenú techniku ​​použitia vzorca na vytvorenie rozbaľovacieho zoznamu je možné rozšíriť aj o dynamický rozbaľovací zoznam. Ak použijete funkciu OFFSET, ako je uvedené vyššie, rozbaľovacia ponuka sa neaktualizuje automaticky, aj keď do zoznamu pridáte ďalšie položky. Budete ho musieť aktualizovať ručne vždy, keď zmeníte zoznam.

Toto je spôsob, ako to urobiť dynamickým (a nie je to nič iné ako malé vylepšenie vzorca):

• Vyberte bunku, v ktorej chcete vytvoriť rozbaľovací zoznam (v tomto prípade bunka C2).
• Prejdite na položku Údaje -> Nástroje údajov -> Overenie údajov.
• V dialógovom okne Overenie údajov na karte Nastavenia vyberte ako kritériá overenia zoznam. Hneď ako vyberiete položku Zoznam, zobrazí sa zdrojové pole.
• Do poľa zdroja zadajte nasledujúci vzorec: = OFFSET ($ A $ 2,0,0, COUNTIF ($ A $ 2: $ A $ 100, ””))
• Uistite sa, že je začiarknutá možnosť Rozbaľovacia ponuka v bunke.
• Kliknite na tlačidlo OK.

V tomto vzorci som nahradil argument 5 za COUNTIF ($ A $ 2: $ A $ 100, ””).

Funkcia COUNTIF počíta neprázdne bunky v rozsahu A2: A100. Preto sa funkcia OFFSET sama upraví tak, aby zahŕňala všetky prázdne bunky.

Poznámka:

• Aby to fungovalo, NESMÚ byť medzi vyplnenými bunkami žiadne prázdne bunky.
• Ak chcete vytvoriť rozbaľovací zoznam vo viacerých bunkách naraz, vyberte všetky bunky, v ktorých ho chcete vytvoriť, a potom postupujte podľa vyššie uvedených krokov. Uistite sa, že odkazy na bunky sú absolútne (napríklad $ A $ 2) a nie relatívne (napríklad A2 alebo A $ 2 alebo $ A2).

Skopírujte prilepovacie rozbaľovacie zoznamy v programe Excel

Bunky s overením údajov môžete skopírovať do iných buniek a skopíruje sa aj overenie údajov.

Ak máte napríklad rozbaľovací zoznam v bunke C2 a chcete ho použiť aj na C3: C6, jednoducho skopírujte bunku C2 a vložte ju do C3: C6. Skopíruje sa rozbaľovací zoznam a sprístupní sa v C3: C6 (spolu s rozbaľovacou ponukou sa skopíruje aj formátovanie).

Ak chcete skopírovať iba rozbaľovaciu ponuku, nie formátovanie, postupujte takto:

• Skopírujte bunku, ktorá má rozbaľovaciu ponuku.
• V rozbaľovacej ponuke vyberte bunky, do ktorých chcete skopírovať.
• Prejdite na položku Domov -> Prilepiť -> Prilepiť špeciálne.
• V dialógovom okne Prilepiť špeciálne vyberte položku Možnosti overenia v možnostiach Vložiť.
• Kliknite na tlačidlo OK.

Skopíruje sa iba rozbaľovací zoznam a nie formátovanie skopírovanej bunky.

Pri práci s rozbaľovacím zoznamom programu Excel buďte opatrní

Pri práci s rozbaľovacími zoznamami v programe Excel musíte byť opatrní.

Keď skopírujete bunku (ktorá neobsahuje rozbaľovací zoznam) cez bunku, ktorá obsahuje rozbaľovací zoznam, rozbaľovací zoznam sa stratí.

Najhoršie na tom je, že Excel nezobrazí žiadne upozornenie ani výzvu, aby oznámil používateľovi, že rozbaľovací zoznam bude prepísaný.

Ako vybrať všetky bunky, v ktorých je rozbaľovací zoznam

Niekedy je ťažké vedieť, ktoré bunky obsahujú rozbaľovací zoznam.

Preto má zmysel tieto bunky označiť buď výrazným orámovaním alebo farbou pozadia.

Namiesto ručnej kontroly všetkých buniek existuje rýchly spôsob, ako vybrať všetky bunky, v ktorých sú rozbaľovacie zoznamy (alebo akékoľvek pravidlo overovania údajov).

• Prejdite na položku Domov -> Nájsť a vybrať -> Prejsť na špeciálne.
• V dialógovom okne Prejsť na špeciálne vyberte položku Overenie údajov
  • Overenie údajov má dve možnosti: Všetky a rovnaké. Všetky by vybrali všetky bunky, na ktoré je aplikované pravidlo overovania údajov. Rovnaké by vybrali iba tie bunky, ktoré majú rovnaké pravidlo overovania údajov ako aktívna bunka.
• Kliknite na tlačidlo OK.

Tým by sa okamžite vybrali všetky bunky, na ktoré je aplikované pravidlo overovania údajov (to zahŕňa aj rozbaľovacie zoznamy).

Teraz môžete bunky jednoducho naformátovať (dať ohraničenie alebo farbu pozadia) tak, aby boli vizuálne viditeľné a neskopírovali ste do nich omylom inú bunku.

Tu je ďalšia technika od Jona Acamporu, pomocou ktorej môžete vždy vidieť ikonu rozbaľovacej šípky viditeľnú. Niektoré spôsoby, ako to urobiť, nájdete aj v tomto videu od pána Excela.

Vytvorenie závislého / podmieneného rozbaľovacieho zoznamu programu Excel

Tu je video o tom, ako vytvoriť závislý rozbaľovací zoznam v programe Excel.

Ak dávate prednosť čítaniu pred sledovaním videa, čítajte ďalej.

Niekedy môžete mať viac ako jeden rozbaľovací zoznam a chcete, aby položky zobrazené v druhom rozbaľovacom zozname záviseli od toho, čo používateľ vybral v prvom rozbaľovacom zozname.

Hovorí sa im závislé alebo podmienené rozbaľovacie zoznamy.

Nasleduje príklad podmieneného/závislého rozbaľovacieho zoznamu:

Vo vyššie uvedenom príklade, keď položky uvedené v „rozbaľovacom zozname 2“ závisia od výberu vykonaného v „rozbaľovacom zozname 1“.

Teraz sa pozrime, ako to vytvoriť.

Tu je postup, ako vytvoriť závislý / podmienený rozbaľovací zoznam v programe Excel:

• Vyberte bunku, v ktorej chcete prvý (hlavný) rozbaľovací zoznam.
• Prejdite na položku Údaje -> Overenie údajov. Otvorí sa dialógové okno na overenie údajov.
• V dialógovom okne overenia údajov na karte nastavení vyberte položku Zoznam.
• Do poľa Zdroj zadajte rozsah, ktorý obsahuje položky, ktoré sa majú zobraziť v prvom rozbaľovacom zozname.
• Kliknite na tlačidlo OK. Tým sa vytvorí rozbaľovací zoznam 1.
• Vyberte celý súbor údajov (v tomto prípade A1: B6).
• Prejdite na Vzorce -> Definované mená -> Vytvoriť z výberu (alebo môžete použiť klávesovú skratku Ctrl + Shift + F3).
• V dialógovom okne „Vytvoriť pomenované z výberu“ začiarknite možnosť Horný riadok a zrušte začiarknutie všetkých ostatných. Týmto sa vytvoria 2 rozsahy mien („Ovocie“ a „Zelenina“). Ovocie s názvom rozsah odkazuje na všetky druhy ovocia v zozname a zelenina s názvom rozsah označuje všetku zeleninu v zozname.
• Kliknite na tlačidlo OK.
• Vyberte bunku, kde chcete rozbaľovací zoznam Závislé/Podmienené (v tomto prípade E3).
• Prejdite na položku Údaje -> Overenie údajov.
• V dialógovom okne Overenie údajov na karte nastavení skontrolujte, či je vybratá možnosť Zoznam.
• Do poľa Zdroj zadajte vzorec = NEPRIAMY (D3). Tu je D3 bunka, ktorá obsahuje hlavnú rozbaľovaciu ponuku.
• Kliknite na tlačidlo OK.

Teraz, keď vyberiete položku v rozbaľovacom zozname 1, možnosti uvedené v rozbaľovacom zozname 2 sa automaticky aktualizujú.

Stiahnite si ukážkový súbor

Ako to funguje? - Podmienený rozbaľovací zoznam (v bunke E3) odkazuje na = NEPRIAMY (D3). To znamená, že keď v bunke D3 vyberiete „Ovocie“, rozbaľovací zoznam v E3 bude odkazovať na pomenovaný rozsah „Ovocie“ (prostredníctvom funkcie NEPRIAMY) a zobrazí sa zoznam všetkých položiek v tejto kategórii.

Dôležitá poznámka pri práci s podmienenými rozbaľovacími zoznamami v programe Excel:

• Keď vykonáte výber a potom zmeníte rozbaľovaciu ponuku rodiča, závislá rozbaľovacia ponuka sa nezmení, a preto by bolo nesprávnym zadaním. Ak napríklad vyberiete USA ako krajinu a potom vyberiete ako štát Floridu a potom sa vrátite späť a zmeníte krajinu na Indiu, štát zostane ako Florida. Tu je skvelý návod od Debry na vymazanie závislých (podmienených) rozbaľovacích zoznamov v programe Excel pri zmene výberu.
• Ak je hlavnou kategóriou viac ako jedno slovo (napríklad „sezónne ovocie“ namiesto „ovocie“), potom musíte namiesto jednoduchá NEPRIAMA funkcia zobrazená vyššie. Dôvodom je to, že Excel nepovoľuje medzery v pomenovaných rozsahoch. Keď teda vytvoríte pomenovaný rozsah pomocou viac ako jedného slova, Excel automaticky vloží medzi slová podčiarkovník. Pomenovaný sortiment pre „sezónne ovocie“ by teda bol „sezónne_ ovocie“. Použitím funkcie SUBSTITUTE v rámci funkcie INDIRECT sa zaistia medzery sú premenené na podčiarkovníky.