Komplexný vyskakovací zoznam v Exceli. Vytvorte rozbaľovací zoznam v Exceli

Microsoft Excel je skvelý tabuľkový procesor. S ním môžete rýchlo vytvoriť vhodnú tabuľku a vyplniť ju údajmi. Excel zároveň zjednodušuje nielen prácu s údajmi v tabuľke, ale aj proces vypĺňania tabuľky.

V tomto materiáli sa zameriame na vyplnenie tabuľky. Tu sa dozviete, ako vytvoriť rozbaľovací zoznam v Exceli, ktorý vám umožní oveľa rýchlejšie vyplniť tabuľku údajmi. Pokyn bude relevantný pre Excel 2007, 2010, 2013 a 2016.

Najjednoduchší spôsob, ako vytvoriť rozbaľovací zoznam v Exceli, je použiť funkciu overenia údajov. Ak chcete vytvoriť tento rozbaľovací zoznam, musíte najskôr vytvoriť bežný zoznam s údajmi a umiestniť ho do dokumentu programu Excel. Takýto zoznam môže byť umiestnený na rovnakom hárku dokumentu, kde bude umiestnený rozbaľovací zoznam, alebo na akomkoľvek inom hárku.

Najprv teda vytvoríme zoznam údajov, ktoré by mali byť v rozbaľovacom zozname, a umiestnime ho na akékoľvek vhodné miesto v dokumente programu Excel. Takýto zoznam môžete umiestniť napríklad za oblasť tlače alebo na iný hárok dokumentu programu Excel.

Potom vyberte bunku, v ktorej chcete vytvoriť rozbaľovací zoznam. Vyberte túto bunku myšou a kliknite na tlačidlo "Overenie údajov" na karte "Údaje".

Tým sa otvorí okno Overiť vstupné hodnoty. V tomto okne musíte najskôr otvoriť rozbaľovací zoznam "Typ údajov" a vybrať tam možnosť "Zoznam".

Potom musíte vyplniť riadok "Zdroj".

Ak to chcete urobiť, nastavte kurzor na riadok "Zdroj" a potom vyberte zoznam údajov pomocou myši, ktorý by mal byť vo vytvorenom rozbaľovacom zozname.

Po zadaní zdroja zatvorte okno „Skontrolovať vstupné hodnoty“ kliknutím na tlačidlo „Ok“. Rozbaľovací zoznam v Exceli je vytvorený a je možné ho skontrolovať.

Teraz však do tejto bunky môžete zadať iba údaje, ktoré sa nachádzajú v rozbaľovacom zozname. A ak sa pokúsite zadať nesprávnu hodnotu, zobrazí sa chybové hlásenie.

Ak chcete ponechať možnosť zadávať údaje do bunky, ktorá nie je obsiahnutá v rozbaľovacom zozname, musíte vybrať bunku s rozbaľovacím zoznamom a kliknúť na tlačidlo "Overenie údajov". Ďalej v okne, ktoré sa otvorí, musíte prejsť na kartu "Chybové hlásenie" a zrušiť začiarknutie políčka vedľa funkcie "Zobraziť chybové hlásenie".

Po uložení týchto nastavení môžete do bunky zadať nielen údaje, ktoré sú dostupné v rozbaľovacom zozname, ale aj akékoľvek ďalšie údaje, ktoré potrebujete.

Ako vytvoriť rozbaľovací zoznam s pridanými údajmi

Vyššie opísaná možnosť rozbaľovacieho zoznamu je celkom pohodlná. Ak však doň chcete pravidelne pridávať nové údaje, táto možnosť nebude fungovať, pretože po každom pridaní údajov budete musieť zmeniť rozsah, ktorý je uvedený v poli „Zdroj“. Tento problém môžete vyriešiť pomocou funkcie Smart Tables, ktorá bola predstavená v programe Microsoft Excel 2007.

Ak to chcete urobiť, musíte vytvoriť zoznam s údajmi, ako je popísané vyššie. Jediný rozdiel je v tom, že zoznam by teraz mal mať názov.

Po vytvorení zoznamu ho musíte vybrať a pomocou tlačidla „Formátovať ako tabuľku“ na karte „Domov“ na zoznam použijete ľubovoľný štýl.

Po výbere štýlu sa zobrazí okno Formátovať tabuľku. Tu musíte začiarknuť políčko vedľa funkcie "Tabuľka s hlavičkou" a kliknúť na "OK".

V dôsledku toho by ste mali dostať tabuľku s údajmi, niečo ako na obrázku nižšie.

Teraz musíte vybrať bunku, v ktorej chcete vytvoriť rozbaľovací zoznam, a kliknúť na tlačidlo "Overenie údajov" na karte "Údaje". V okne, ktoré sa otvorí, vyberte "Typ údajov - Zoznam" a potom umiestnite kurzor do riadku "Zdroj" a pomocou myši vyberte zoznam s údajmi, ktoré sa majú použiť pre rozbaľovací zoznam (nepotrebujete vyberte hlavičku zoznamu).

Tento spôsob vytvárania rozbaľovacích zoznamov s možnosťou pridávania nových riadkov bol testovaný v Exceli 2010, no mal by fungovať aj v Exceli 2007. Rovnako ako v modernejších verziách Excelu, ako sú Excel 2013 a Excel 2016.

Rozbaľovací zoznam v bunke umožňuje používateľovi vybrať pre vstup iba zadané hodnoty. To je užitočné najmä pri práci so súbormi štruktúrovanými ako databáza, keď zadanie nevhodnej hodnoty do poľa môže viesť k nežiaducim výsledkom.

Na vytvorenie rozbaľovacieho zoznamu teda potrebujete:

1. Vytvorte zoznam hodnôt, ktoré budú poskytnuté používateľovi na výber (v našom príklade ide o rozsah M1:M3), potom vyberte bunku, v ktorej bude rozbaľovací zoznam (v našom príklade je to bunka K1), potom prejdite na kartu " Údaje", skupina" Práca s údajmi", tlačidlo " Overovanie dát"

2. Vyberte si " Dátový typ" -"Zoznam“ a zadajte rozsah zoznamu

3. Ak chcete upozorniť používateľa na jeho akcie, prejdite na kartu " Zadajte správu“ a vyplňte názov a text správy

ktorý sa zobrazí pri výbere bunky s rozbaľovacím zoznamom

4. Môžete tiež voliteľne vytvoriť správu, ktorá sa zobrazí, keď sa pokúsite zadať nesprávne údaje.

Ak neurobíte kroky 3 a 4, potom overovanie dát bude fungovať, ale po aktivácii bunky sa používateľovi nezobrazí hlásenie o jeho zamýšľaných akciách a namiesto chybového hlásenia s vaším textom sa zobrazí štandardné hlásenie.

5. Ak sa zoznam hodnôt nachádza na inom hárku, potom nebude fungovať vytvorenie rozbaľovacieho zoznamu spôsobom opísaným vyššie (pred Excelom 2010). Ak to chcete urobiť, musíte zoznamu pomenovať. Dá sa to urobiť niekoľkými spôsobmi. Prvý: vyberte zoznam a kliknite pravým tlačidlom myši, v kontextovej ponuke vyberte " Priraďte meno"

Pre verzie Excelu nižšie ako 2007 vyzerajú rovnaké kroky takto:

Po druhé: použitie menný manažér(Verzie programu Excel staršie ako 2003 - karta " Vzorce" - Skupina " Určité mená"), ktorý sa v akejkoľvek verzii Excelu volá klávesovou skratkou Ctrl+F3.
Nech už si vyberiete ktorýkoľvek spôsob, nakoniec budete musieť zadať názov (pomenoval som rozsah pomocou zoznamu zoznam) a adresu samotného rozsahu (v našom príklade je to toto "2"! $A$1:$A$3)

6. Teraz v bunke s rozbaľovacím zoznamom v poli "Zdroj" zadajte názov rozsahu

7. Pripravený!

Na dokončenie obrazu dodám, že zoznam hodnôt je možné zadať aj priamo do kontroly údajov bez toho, aby ste sa museli uchýliť k vloženiu hodnôt na hárok (umožní vám to tiež pracovať so zoznamom na ľubovoľnom hárku ). Robí sa to takto:

Teda ručne ; (bodkočiarka) zadajte zoznam do poľa " Zdroj“, v poradí, v akom ho chceme vidieť (hodnoty zadané zľava doprava sa zobrazia v bunke zhora nadol).

So všetkými svojimi plusmi má rozbaľovací zoznam vytvorený vyššie uvedeným spôsobom jedno, ale veľmi „tučné“ mínus: overenie údajov funguje iba pri zadávaní hodnôt priamo z klávesnice. Ak sa pokúsite vložiť do bunky s overenie údajov hodnoty zo schránky, t.j. predtým skopírované akýmkoľvek spôsobom, potom budete úspešní. Okrem toho prilepená hodnota z vyrovnávacej pamäte ODSTRÁNI VALIDÁCIU ÚDAJOV A ROZŤAHOVACÍ ZOZNAM Z BUNKY, do ktorej bola vložená predtým skopírovaná hodnota. Tomu sa nemožno vyhnúť pomocou štandardných nástrojov Excelu.

Mnohí používatelia si ani neuvedomujú, že známy tabuľkový editor Excel má také funkcie a nástroje, ktoré ďaleko presahujú hlavný účel použitia programu – úpravu tabuliek. Tento článok bude hovoriť o možnosti vybrať z. Inými slovami, povieme vám, ako vytvoriť rozbaľovacie zoznamy v bunkách tabuľky.

Metóda 1: vytvorte ďalší zoznam

Ak chcete vytvoriť výberový zoznam v bunke Excelu, najjednoduchším spôsobom je použiť túto metódu, ktorá zahŕňa jednoduché vytvorenie rozbaľovacieho zoznamu. Mimochodom, budeme hovoriť o dvoch jeho variáciách, takže čítajte až do konca, aby ste všetko pochopili.

Krok 1: pripravte údaje

Najprv musíte vytvoriť tabuľku v samostatnom rozsahu buniek s údajmi, ktoré budú v rozbaľovacom zozname v budúcnosti. Poďme analyzovať všetko na príklade produktov. Máme teda zoznam siedmich produktov, presnejšie produktov. Túto tabuľku vytvoríme trochu napravo od hlavnej tabuľky, v rámci ktorej sa budú vytvárať rozbaľovacie zoznamy.

Ak nechcete, aby bola dátová tabuľka na rovnakom hárku ako hlavná, môžete ju vytvoriť na samostatnom hárku. Nebude to hrať rolu.

Krok 2: zadajte názov rozsahu

Ak chcete použiť možnosť výberu zo zoznamu v Exceli, musíte najprv zadať názov rozsahu s údajmi pre budúci zoznam. Robí sa to celkom jednoducho:

1. Vyberte bunky, ktoré v tomto prípade obsahujú názvy tovaru.
2. Kliknite pravým tlačidlom myši (RMB) na výber.
3. Z ponuky vyberte možnosť „Dať meno“.
4. V zobrazenom okne do poľa „Názov“ zadajte názov rozsahu. Môže to byť úplne čokoľvek.
5. Kliknite na tlačidlo OK.

Druhý krok bol dokončený. Rozsah buniek, ktoré sme práve vytvorili, uľahčí vytváranie zoznamu v budúcnosti.

Krok 3: Vytvorte rozbaľovací zoznam

Teraz môžete prejsť priamo na pomocou možnosti vybrať zo zoznamu v Exceli. Toto sa robí nasledovne:

1. Vyberte požadovaný rozsah buniek, v ktorých sa budú nachádzať rozbaľovacie zoznamy.
2. Prejdite na kartu Údaje.
3. V skupine Nástroje údajov kliknite na tlačidlo Overenie údajov.
4. V zobrazenom okne na karte "Parametre" vyberte z rozbaľovacieho zoznamu "Typ údajov" položku "Zoznam".
5. Zadajte názov predtým vytvoreného rozsahu buniek do poľa "Zdroj" po uvedení znamienka rovnosti. V našom prípade "=Produkty".
6. Kliknite na tlačidlo OK.

Hneď potom sa vo vybratých bunkách objavia rozbaľovacie zoznamy. Toto bol prvý spôsob, ako ho vytvoriť, prejdime k druhému.

Metóda 2: Vytvorenie rozbaľovacieho zoznamu prostredníctvom ponuky vývojára

Je celkom možné, že sa vám predchádzajúci pokyn zdal nezrozumiteľný a pri vytváraní prvku výberu hodnoty zo zoznamu v bunke tabuľky v Exceli ste narazili na ťažkosti. Druhý spôsob implementácie môže byť dôstojnou alternatívou.

Ako vytvoriť rozbaľovací zoznam v bunke listu pomocou ponuky „Vývojár“? Rovnako ako v predchádzajúcom období, pre lepšie pochopenie budú všetky akcie rozdelené do etáp.

Krok 1: Aktivujte ponuku vývojára

Najprv teda musíte aktivovať ponuku „Vývojár“, pretože v predvolenom nastavení nie je medzi ostatnými kartami.

1. Kliknite na tlačidlo "Súbor".
2. Kliknite na tlačidlo "Možnosti".
3. V okne s rovnakým názvom, ktoré sa zobrazí, prejdite do časti „Prispôsobiť pásku“.
4. V oblasti Hlavné karty začiarknite políčko vedľa položky Vývojár.
5. Kliknite na tlačidlo OK.

Požadovaný panel nástrojov je aktivovaný, teraz môžete začať vytvárať zoznam.

Krok 2: Vložte rozbaľovací zoznam

Musíte vytvoriť samotný prvok rozbaľovacieho zoznamu. Pre to:

1. Prejdite na pridanú kartu Vývojár.
2. Na pracovnom hárku vytvorte zoznam produktov, ktoré sa použijú na vytvorenie rozbaľovacieho zoznamu.
3. Kliknite na tlačidlo "Vložiť" a v dodatočnej ponuke vyberte položku "Combo Box".
4. Kliknite na bunku, kde sa bude nachádzať zoznam.

Už v tejto fáze sa objaví požadovaný prvok, ale ak naň kliknete, otvorí sa prázdny zoznam. Podľa toho je potrebné k nemu pridávať produkty.

Krok 3: nastavte potrebné parametre

Ak chcete pridať položky do rozbaľovacieho zoznamu:

1. Kliknite na tlačidlo "Design Mode" na paneli nástrojov.
2. Potom kliknite na tlačidlo Control Properties vedľa neho.
3. V zobrazenom okne vlastností zadajte do stĺpca ListFillRange rozsah buniek, v ktorých sa nachádzajú položky budúceho rozbaľovacieho zoznamu.
4. Teraz kliknite pravým tlačidlom myši na rozbaľovací zoznam a z ponuky az podponuky Upraviť vyberte položku „Objekt ComboBox“.

Hneď potom sa do rozbaľovacieho zoznamu pridajú zadané položky. Takto jednoducho sa dá vybrať zo zoznamu v Exceli pomocou druhého spôsobu.

Metóda 3: Vytvorte prepojený zoznam

Na výber viacerých hodnôt je najvhodnejší rozbaľovací zoznam v Exceli, niekedy je však potrebné prepojiť viacero takýchto zoznamov. Našťastie vám to program umožňuje a budú poskytnuté ďalšie podrobné pokyny krok za krokom s podrobným popisom všetkých akcií.

Krok 1: vytvorte ďalší zoznam

Prvým krokom je vytvorenie hlavného rozbaľovacieho zoznamu. Nebudeme sa tým dlho zaoberať, pretože dizajn je úplne podobný dizajnu opísanému v prvej metóde. Povieme len, že názov produktu si spojíme s jeho hmotnosťou. Odporúča sa vytvoriť názov sortimentu s mierami produktu (g, kg, ml, l).

Krok 2: Priraďte prvý zoznam k druhému

Teraz prejdime priamo k hlavnej veci - k vytvoreniu druhého prvku "Vybrať zo zoznamu" v Exceli, ktorý bude spojený s prvým.

1. Umiestnite kurzor do bunky, kde sa bude nachádzať druhý zoznam.
2. Otvorte okno Overenie vstupu kliknutím na tlačidlo Overenie údajov na karte Údaje.
3. V okne, ktoré sa zobrazí, na karte "Parametre" vyberte položku "Zoznam" z rozbaľovacieho zoznamu "Typ údajov".
4. Do vstupného poľa Zdroj zadajte NEPRIAME vzorec odkazujúci na prvý zoznam. V tomto prípade to bude vyzerať takto: "=PRIAME($B3)".
5. Kliknite na tlačidlo OK.

Druhý zoznam bol vytvorený. Je viazaný na prvý, čo znamená, že výberom hodnoty v tomto prípade produktu budete musieť vybrať aj jeho mieru. Aby sa nevytvárali rovnaké zoznamy v iných bunkách, vyberte už pridané a potiahnite pravý dolný roh výberu nadol, čím vyplníte všetky požadované bunky.

Záver

Možnosť výberu zo zoznamu v Exceli je celkom užitočná, dalo by sa to pochopiť zo všetkého vyššie uvedeného. Čo je však dôležitejšie, na jeho vytvorenie nepotrebujete mať hlboké znalosti v používaní tabuľkového procesora. Okrem toho existujú dokonca tri spôsoby, ako túto funkciu implementovať a s pomocou opísaných pokynov by ste pri ich vykonávaní nemali mať žiadne zvláštne problémy.

Ak chcete používateľom uľahčiť interakciu s pracovným hárkom, pridajte do buniek rozbaľovacie zoznamy, ktoré im umožnia vybrať požadovanú položku.

Na novom hárku zadajte údaje, ktoré sa majú zobraziť v rozbaľovacom zozname. Položky zoznamu by mali byť prednostne obsiahnuté v tabuľke programu Excel. Ak to tak nie je, zoznam možno rýchlo previesť na tabuľku výberom ľubovoľnej bunky v rozsahu a stlačením kláves CTRL+T.



Poznámky:

1. Vyberte bunku v hárku, do ktorej chcete umiestniť rozbaľovací zoznam.

   Na páse s nástrojmi otvorte kartu Údaje a stlačte tlačidlo Overovanie dát.

   Poznámka: Ak je tlačidlo Overovanie dát nedostupný, hárok môže byť chránený alebo zdieľaný. Odomknite konkrétne oblasti chráneného zošita alebo zrušte zdieľanie hárka a potom zopakujte krok 3.

   Na karte možnosti v teréne Dátový typ vybrať položku Zoznam.

   Kliknite na políčko Zdroj a zvýraznite rozsah zoznamu. V príklade sú údaje na hárku "Mestá" v rozsahu A2:A9. Všimnite si, že riadok hlavičky nie je v rozsahu, pretože to nie je jedna z možností dostupných na výber.

   

   Ak môžete bunku nechať prázdnu, začiarknite políčko Ignorovať prázdne bunky.

   Zaškrtávacie políčko Zoznam platných hodnôt

   Otvorte kartu Zadajte správu.

   
   

2. Otvorte kartu Chybná správa.

   
   

3. Neviem, ktorú možnosť vybrať v poli vyhliadka?

Práca s rozbaľovacím zoznamom

Po vytvorení rozbaľovacej ponuky sa uistite, že funguje tak, ako chcete. Môžete napríklad skontrolovať, či je potrebné zmeniť šírku stĺpcov a výšku riadkov, aby sa zobrazili všetky záznamy.

Stiahnite si príklady

Odporúčame stiahnuť si vzorovú knihu s niekoľkými príkladmi overenia údajov podobnými príkladu v tomto článku. Môžete ich použiť alebo si vytvoriť vlastné skripty na overenie údajov. Stiahnite si príklady overenia údajov v Exceli

Zadávanie údajov bude rýchlejšie a presnejšie, ak obmedzíte hodnoty v bunke na možnosti z rozbaľovacieho zoznamu.

Najprv vytvorte zoznam platných prvkov na pracovnom hárku a potom ich zoraďte alebo usporiadajte v požadovanom poradí. V budúcnosti môžu tieto prvky slúžiť ako zdroj pre rozbaľovací zoznam údajov. Ak je zoznam malý, možno naň jednoducho odkazovať a zadať ho priamo do validátora údajov.

Rozbaľovacie zoznamy v programe Microsoft Excel sú nevyhnutné pri vytváraní veľkých tabuliek a pri práci s databázami. Aké je vlastne pohodlie tohto nástroja?
Ak sa pri vypĺňaní tabuľky niektoré údaje periodicky opakujú, nie je potrebné zakaždým ručne zadávať konštantnú hodnotu – napríklad názov produktu, mesiac, celé meno zamestnanca. Stačí raz opraviť opakujúci sa parameter v zozname.
Bunky zoznamu sú chránené pred zavedením cudzích hodnôt, čo znižuje pravdepodobnosť chyby v práci.
Takto navrhnutý stôl vyzerá elegantne.
V článku poviem ako vytvoriť rozbaľovací zoznam v bunkách v programe Excel a ako s tým pracovať.

Vytvorenie rozbaľovacieho zoznamu

Jeden z najbežnejších príkladov používania rozbaľovacích zoznamov je v internetových obchodoch, kde sú všetky produkty kategorizované – táto štruktúra používateľom uľahčuje vyhľadávanie na stránke.
Pozrime sa na to vizuálne:

Všetky produkty uvedené v tabuľke musia byť zaradené do kategórie „Oblečenie“. Ak chcete vytvoriť rozbaľovací zoznam pre tento zoznam, musíte urobiť nasledovné:
Vyberte ľubovoľnú bunku, v ktorej sa vytvorí zoznam.
Prejdite na kartu „Údaje“ v časti „Overenie údajov“.
V okne, ktoré sa otvorí, vyberte kartu "Parametre" a v zozname "Typ údajov" je možnosť "Zoznam".
V zobrazenom riadku musíte zadať všetky dostupné názvy zoznamu. Môžete to urobiť dvoma spôsobmi: vyberte rozsah údajov v tabuľke pomocou myši (v príklade bunky A1-A7) alebo ručne zadajte názvy oddelené bodkočiarkami.
Označte všetky bunky s požadovanými hodnotami a kliknutím pravým tlačidlom myši vyberte v kontextovej ponuke položku „Priradiť názov“.
V riadku "Názov" uveďte názov zoznamu - v tomto prípade "Oblečenie".
Vyberte bunku, v ktorej bol zoznam vytvorený, a vytvorený názov zadajte do riadku "Zdroj" so znakom "=" na začiatku.
Konečný výsledok vyzerá takto. Toto je najjednoduchšia možnosť rozbaľovacej ponuky. V závislosti od verzie programu Excel môže existovať viac alebo menej akcií, ale vo všeobecnosti je inštrukcia univerzálna pre akýkoľvek program.

Ako pridať hodnoty do zoznamu

Niekedy je potrebné doplniť už existujúci zoznam. V rozbaľovacom zozname sa po pridaní automaticky zobrazia všetky nové položky. Ak však chcete priradiť rozsah buniek k novo pridanému prvku, zoznam musí byť naformátovaný ako tabuľka. Ak to chcete urobiť, musíte vybrať rozsah hodnôt, nájsť položku "Formátovať ako tabuľku" na karte "Domov" a vybrať napríklad ľubovoľný štýl, ktorý sa vám páči.