Jak vytvořit seznam v jedné buňce Excel. Vytvářejte propojené rozevírací seznamy v Excelu – nejjednodušší způsob

Rozbalovací seznamy zjednodušují a umožňují zadávat data do tabulek. Stačí kliknout na šipku a vybrat možnost. Do buněk aplikace Excel můžete přidat rozevírací seznamy obsahující možnosti jako Ano a Ne, Muži a ženy nebo jakýkoli jiný vlastní seznam možností.

Přidat rozevírací seznam do buňky v Excelu dost jednoduché, ale proces není intuitivní. Rozbalovací seznamy se vytvářejí pomocí funkce ověřování dat. Vytvoříme rozbalovací seznam se sadou věkových skupin, abychom vám ukázali, jak na to.

Chcete-li začít, zadejte seznam věkových skupin do po sobě jdoucích buněk podle sloupce nebo řádku. Věkové rozsahy jsme zadali do buněk A9 až A13 na stejném listu, jak je uvedeno níže. Seznam možností můžete také přidat do jiného listu ve stejném sešitu.

Nyní pojmenujeme náš rozsah buněk, abychom je mohli snadněji přidat do rozevíracího seznamu. Chcete-li to provést, vyberte všechny buňky, které obsahují položky rozevíracího seznamu, a poté zadejte název rozsahu buněk do pole Název nad mřížkou. Pojmenovali jsme náš sortiment Stáří.

Nyní vyberte buňku, kam chcete přidat rozevírací seznam, a přejděte na kartu Data.

V sekci "Datové nástroje" na kartě Data klikněte na tlačítko Ověření dat.

Otevře se dialogové okno Ověření dat. Na kartě Možnosti vyberte v rozevíracím seznamu Typ dat položku Seznam.

Nyní použijeme název, který jsme přiřadili rozsahu buněk obsahujících naše rozevírací možnosti. Vstupte = Věk v poli "Zdroj" (pokud jste rozsah buněk pojmenovali nějak jinak, nahraďte "Věk" tímto názvem). Zkontrolujte zaškrtávací políčko Prázdné buňky ignorujte výrazný.

Můžete přidat vyskakovací zprávu, která se zobrazí, když je vybrána buňka obsažená v rozevíracím seznamu. Chcete-li to provést, klepněte na kartu Zadejte zprávu v dialogovém okně Ověření dat. Zkontrolujte zaškrtávací políčko Zobrazit popisek, pokud je buňka aktuální nainstalováno. Zadejte zprávu do polí Název a Zpráva a poté klikněte na tlačítko OK.

Když je vybrána buňka obsahující rozevírací seznam, uvidíte napravo od buňky tlačítko se šipkou dolů. Pokud jste přidali vstupní zprávu, zobrazí se pod buňkou. Tlačítko se šipkou dolů se zobrazí pouze v případě, že je vybrána buňka.

Stisknutím tlačítka se šipkou dolů zobrazte seznam možností a vyberte jednu z nich.

Pokud se rozhodnete odstranit rozevírací seznam z buňky, otevřete dialog Ověření dat, jak je popsáno výše, a klikněte na tlačítko Vymazat vše, které je dostupné bez ohledu na to, která karta je v dialogu vybrána.

Možnosti v dialogovém okně Ověření dat se obnoví na výchozí hodnoty. Klepnutím na tlačítko OK rozevírací seznam odeberete a obnovíte výchozí buňku.

Pokud byla vybrána možnost, pak po odstranění rozevíracího seznamu bude buňka vyplněna hodnotou této možnosti.

Podle tohoto postupu přidejte rozevírací seznamy do dalších buněk podle potřeby. Stejný seznam můžete použít pro více rozbalovacích nabídek.

Máte-li mnoho rozevíracích seznamů, které je třeba přidat do listu, doporučujeme umístit seznamy možností na samostatný list aplikace Excel a skrýt list, abyste zabránili změnám možností.

Související rozbalovací nabídky

Stáhněte si soubor použitý ve výukovém videu:

("Spodní lišta":("textstyle":"static","textpositionstatic":"bottom","textautohide":true,"textpositionmarginstatic":0,"textpositiondynamic":"bottomleft","textpositionmarginleft":24,"textpositionmarginright":24,"textpositionmargintop":24,"textpositionmargin,"textefase":" tCubic","texteffectduration":600,"texteffectslidedirection":"left","texteffectslidedistance":30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":"slide","texteffectslidedirection1","texteffectslidedirection1":"right","texteffect"texteffectslidedistance efektduration1":600,"texteffectdelay1":1000,"texteffect2":"slide","texteffectslidedirection2":"right","texteffectslidedistance2":120,"texteffecteasing2":"easeOutCubic","texteffectduration2":600"dding1"textplaycsdelay"; 12px; text-align:left;","textbgcss":"display:block; position:absolute; top:0px; left:0px; width:100%; height:100%; background-color:#333333; opacity:0.6; filter:alpha(opacity=60);","distitlecss";" poloha:relativní; font:bold 14px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff;","descriptioncss":"display:block; poloha:relativní; font:12px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; barva:#fff; margin-top:8px;","buttoncss":"display:block; poloha:relativní; margin-top:8px;","texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"font-size:12px;","descriptioncssresponsive":"display:none !important;","buttoncssresponsive":"","addperfontcents":false","addperfontcents":false

Chcete-li uživatelům usnadnit práci s listem, přidejte do buněk rozevírací seznamy, které jim umožní vybrat požadovanou položku.

Na novém listu zadejte data, která chcete zobrazit v rozevíracím seznamu. Položky seznamu by měly být pokud možno obsaženy v excelové tabulce. Pokud tomu tak není, lze seznam rychle převést na tabulku výběrem libovolné buňky v rozsahu a stisknutím kláves CTRL+T.



Poznámky:

1. Vyberte buňku v listu, kam chcete umístit rozevírací seznam.

   Na pásu karet otevřete kartu Data a stiskněte tlačítko Kontrola dat.

   Poznámka: Pokud tlačítko Kontrola dat není k dispozici, může být list chráněný nebo sdílený. Odemkněte konkrétní oblasti chráněného sešitu nebo zrušte obecný přístup na list a poté opakujte krok 3.

   Na kartě Možnosti v terénu Datový typ vybrat předmět Seznam.

   Klepněte na pole Zdroj a zvýrazněte rozsah seznamu. V příkladu jsou data na listu "Města" v rozsahu A2:A9. Všimněte si, že řádek záhlaví není v rozsahu, protože to není jedna z možností dostupných pro výběr.

   

   Pokud můžete nechat buňku prázdnou, zaškrtněte políčko Prázdné buňky ignorujte.

   Zaškrtávací políčko Seznam platných hodnot

   Otevřete kartu Zadejte zprávu.

   
   

2. Otevřete kartu Chybové hlášení.

   
   

3. Nevím, kterou možnost v poli vybrat Pohled?

Práce s rozevíracím seznamem

Po vytvoření rozevíracího seznamu se ujistěte, že funguje tak, jak chcete. Můžete například chtít zkontrolovat, zda je pro zobrazení všech záznamů nutné změnit šířku sloupců a výšku řádků.

Stáhněte si příklady

Doporučujeme stáhnout si vzorovou knihu s několika příklady ověření dat podobnými příkladu v tomto článku. Můžete je použít nebo si vytvořit vlastní skripty pro ověřování dat. Stáhněte si příklady ověření dat aplikace Excel

Zadávání dat bude rychlejší a přesnější, pokud omezíte hodnoty v buňce na možnosti z rozevíracího seznamu.

Nejprve vytvořte seznam platných prvků na listu a poté je seřaďte nebo uspořádejte v požadovaném pořadí. Tyto prvky mohou v budoucnu sloužit jako zdroj pro rozbalovací seznam dat. Pokud je seznam malý, lze na něj snadno odkazovat a zadat jej přímo do validátoru dat.

Prvek seznamu je nám známý z formulářů na stránkách. Je vhodné zvolit hotové hodnoty. Například měsíc nikdo nezadává ručně, bere se z takového seznamu. Pomocí různých nástrojů můžete v Excelu vyplnit rozevírací seznam. V článku se budeme zabývat každou z nich.

Jak vytvořit rozevírací seznam v aplikaci Excel

Jak vytvořit rozevírací seznam v aplikaci Excel 2010 nebo 2016 pomocí jednoho příkazu na panelu nástrojů? Na kartě Data v části Nástroje dat najděte tlačítko Ověření dat. Klikněte na něj a vyberte první položku.

Otevře se okno. Na kartě "Parametry" v rozbalovací části "Typ dat" vyberte "Seznam".


Ve spodní části se objeví řádek pro specifikaci zdrojů.


Informace můžete zadávat různými způsoby.

Nejprve přiřadíme jméno. Chcete-li to provést, vytvořte takovou tabulku na libovolném listu.

Vyberte jej a stiskněte pravé tlačítko myši. Klikněte na příkaz "Dejte jméno".

Do výše uvedeného řádku zadejte jméno.

Vyvolejte okno "Ověření dat" a v poli "Zdroj" zadejte název tak, že před něj dáte znak "=".


V kterémkoli ze tří případů uvidíte požadovaný prvek. Výběr hodnoty z rozbalovací nabídky Excelový seznam děje s myší. Klikněte na něj a zobrazí se seznam zadaných údajů.

Naučili jste se vytvořit rozevírací seznam v buňce aplikace Excel. Ale dá se udělat víc.

Dynamická substituce dat v Excelu

Pokud přidáte nějakou hodnotu do rozsahu dat, který je nahrazen do seznamu, nezmění se, dokud nebudou ručně zadány nové adresy. Chcete-li přiřadit rozsah a aktivní prvek, musíte první prvek naformátovat jako tabulku. Vytvořte takové pole.

Vyberte jej a na kartě Domů vyberte libovolný styl tabulky.


Nezapomeňte zaškrtnout políčko níže.

Obdržíte takový design.

Vytvořte aktivní prvek, jak je popsáno výše. Zadejte vzorec jako zdroj

Chcete-li zjistit název tabulky, přejděte na kartu "Návrhář" a podívejte se na ni. Název můžete změnit na jakýkoli jiný.


Funkce NEPŘÍMÉ vytvoří odkaz na buňku nebo oblast. Nyní je váš prvek v buňce svázán s datovým polem.

Zkusme zvýšit počet měst.


Opačný postup - nahrazení údajů z rozbalovacího seznamu v excelovská tabulka, funguje velmi jednoduše. Do buňky, kam chcete vložit vybranou hodnotu z tabulky, zadejte vzorec:

Adresa_buňky

Pokud je například seznam dat v buňce D1, pak do buňky, kde se zobrazí vybrané výsledky, zadejte vzorec

Jak odstranit (odstranit) rozevírací seznam v aplikaci Excel

Otevřete rozbalovací okno přizpůsobení a v části "Typ dat" vyberte "Libovolná hodnota".

Nežádoucí prvek zmizí.

Závislé prvky

Někdy je v Excelu potřeba vytvořit několik seznamů, když jeden závisí na druhém. Každé město má například několik adres. Při výběru v prvním bychom měli získat pouze adresy vybraného sídla.


V tomto případě pojmenujte každý sloupec. Vyberte bez první buňky (jména) a stiskněte pravé tlačítko myši. Vyberte Přiřadit název.

Toto bude jméno města.


Při pojmenování Petrohrad a Nižnij Novgorod se zobrazí chyba, protože název nemůže obsahovat mezery, podtržítka, speciální znaky atd.


Proto tato města přejmenujeme podtržením.


Vytvoří se první prvek v buňce A9 obvyklým způsobem.


A do druhého napište vzorec:

NEPŘÍMÉ (A9)

Nejprve se zobrazí chybová zpráva. Souhlasit.

Problém je, že neexistuje žádná vybraná hodnota. Jakmile je město vybráno v prvním seznamu, druhý bude fungovat.

Jak nastavit závislé rozevírací seznamy v Excelu s vyhledáváním

Může být použito dynamický rozsah data pro druhý prvek. To je výhodnější, pokud počet adres roste.
Vytvořme si rozevírací seznam měst. Jmenovaný rozsah je zvýrazněn oranžově.


Pro druhý seznam musíte zadat vzorec:

OFFSET($A$1,MATCH($E$6,$A:$A,0)-1,1,COUNTIF($A:$A,$E$6);1)

MATCH vrátí číslo buňky s městem vybraným v prvním seznamu (E6) v zadané oblasti SA:$A.
COUNTIF počítá počet shod v rozsahu s hodnotou v zadané buňce (E6).

Máme propojené rozevírací seznamy v Excelu s podmínkou shody a hledáním rozsahu.

Více násobný výběr

Často potřebujeme získat více hodnot z datové sady. Můžete si je vzít různé buňky, ale lze je spojit do jednoho. V každém případě potřebujete makro.
Klikněte na štítek listu v dolní části pravého tlačítka myši a vyberte příkaz "Zobrazit kód".


Otevře se okno vývojáře. Je nutné do něj vložit následující algoritmus.

Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Not Intersect(Target, Range("C2:F2")) Is Nothing And Target.Cells.Count = 1 Then Application.EnableEvents = False If Len(Target.Offset(1, 0)) = 0 Then Offset (Target) Offset =Enown. 0) = Target End If Target.ClearContents Application.EnableEvents = True End If End Sub

Všimněte si, že v řádku

If Not Intersect(Target, Range("E7")) Is Nothing And Target.Cells.Count = 1 Then

Měli byste uvést adresu buňky se seznamem. Budeme to mít E7.

Návrat do list Excelu a vytvořte seznam v buňce E7.

Po výběru se pod ním zobrazí hodnoty.

Následující kód vám umožní akumulovat hodnoty v buňce.

Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Not Intersect(Target, Range("E7")) Is Nothing And Target.Cells.Count = 1 Then Application.EnableEvents = False newVal = Target Application.Undo oldval = Target If Len(oldval)<>0 A oldval<>newVal Then Target = Target & "," & newVal Else Target = newVal End If Len(newVal) = 0 Then Target.ClearContents Application.EnableEvents = True End If End Sub

Jakmile přesunete ukazatel do jiné buňky, zobrazí se vám seznam vybraných měst. Pro přečtení tohoto článku.


Ukázali jsme, jak přidat a upravit rozevírací seznam v Excel buňka. Doufáme, že vám tyto informace pomohou.

Měj krásný zbytek dne!

V tomto tutoriálu vám ukážu, jak vytvořit rozevírací seznam v aplikaci Excel. V podstatě je vytvořen s jediným účelem – omezit možnost zadávání dat na konkrétní seznam. Například když je v tabulce speciální buňka, ve které označíte, do kterého oddělení ten či onen zaměstnanec patří. Tento seznam je vždy stejný. Je mnohem pohodlnější vybrat oddělení ze seznamu, než jej pokaždé zadávat ručně nebo kopírovat z jiných buněk.

Pro vytvoření rozevíracího seznamu v Excelu použijeme možnost Kontrola dat. Je to v záložce. Data.

Kliknutím na ikonu se otevře nové okno. Validace vstupních hodnot. Zde potřebujete v rozevíracím seznamu Datový typ Vybrat Seznam a co zadat do zobrazeného pole Zdroj teď na to přijdeme.

Seznam můžete vytvořit alespoň 3 způsoby.

Specifikace prvků přímo ve zdroji

Tato metoda je velmi jednoduchá a vhodná pro malé seznamy.

• Dostáváme se do buňky, kde je potřeba vytvořit seznam;
• Zadání Zkontrolujte data;
• V terénu Zdroj vyjmenujte prvky seznamu, které jsou odděleny středníkem.

Poté stiskněte klávesu OK a dostaneme hotový rozevírací seznam.

Tuto buňku lze bezpečně použít v celé tabulce. Stačí jej zkopírovat a vložit na správné místo.

Seznam položek na stejném listu

Tato metoda umožňuje použít připravený seznam oddělení, která jsou na listu.

• Stáváme se buňkou;
• Zadání Zkontrolujte data;
• Vcházíme na hřiště Zdroj a myší vyberte rozsah, což by měl být seznam. Rozsah musí být umístěn na stejném listu!.

Nyní lze tuto buňku jednoduše zkopírovat a vložit do všech řádků tabulky.

Použití pojmenovaného rozsahu

Tato metoda je vhodná, pokud jsou položky seznamu umístěny na jiném listu.

• Vytvořte seznam oddělení na jiném listu;
• Vytvořte pojmenovaný rozsah. Vyberte rozsah s položkami seznamu. Vlevo od řádku vzorců je nyní buňka, kde jste zahájili výběr. V mém případě - A2;
• Místo A2 dáváme název náš sortiment. Říkáme tomu například oddělení. Poté stiskněte klávesu Vstupte, Gratulujeme, vytvořili jsme Pojmenovaný rozsah.

Vrátíme se zpět k původnímu listu. Dostaneme se do buňky, kde vytvoříme seznam. Přejděte na "Data -> Kontrola dat". V terénu Zdroj, přes znamení = zadejte název rozsahu vytvořeného v předchozím kroku oddělení.

Výsledkem je seznam, jehož prvky jsou na jiném listu.

Gratulujeme, nyní víte, jak vytvořit rozevírací seznam v aplikaci Excel. Pokud máte nějaké dotazy, rád se je pokusím zodpovědět.

V této lekci vám řeknu, co to je speciální vložka v Excelu a jak jej používat.

V tomto tutoriálu vám ukážu, jak vložit znaménko plus nebo nula před číslo na začátku buňky v Excelu. Představme si, že do buňky potřebujete zadat telefonní číslo ve formátu „+7 987 ...“. V normální stav Excel jednoduše odstraní toto znaménko plus.