WiFi

Excel: Vyberte ze seznamu. Rozbalovací seznam v buňce listu

Jednou z nejužitečnějších funkcí při zadávání dat je možnost použití rozevíracího seznamu. Umožňuje vybrat hodnotu z předdefinovaného seznamu a umožňuje zadat pouze ty údaje, které splňují vaše požadavky. Nabízíme vám několik jednoduchých způsobů, jak vytvořit rozevírací seznamy v Excelu. Podíváme se také na složitější metody založené na dynamických rozsazích a využívající data z jiných tabulek.

Jak nám to může být užitečné?

Často se stává, že musíte zadat stejné opakující se hodnoty do jednoho ze sloupců vaší tabulky. Například jména zaměstnanců, názvy produktů. Co se může stát? Samozřejmě v první řadě dojde k překlepům. Lidský faktor nebyl zrušen. Co nám to hrozí? Když se například rozhodneme spočítat, kolik zakázek každý manažer dokončil, ukáže se, že existuje více jmen než zaměstnanců. Dále budete muset hledat chyby, opravit je a opakovat výpočet znovu.

A samozřejmě, že psát pořád ta samá slova rukou je jen zbytečná práce a ztráta času. Zde se hodí rozbalovací seznamy. Po kliknutí se zobrazí seznam předdefinovaných hodnot, z nichž je třeba zadat pouze jednu.

Důležité je, že je nyní nebudete zadávat, ale vybírat pomocí myši nebo klávesnice. To výrazně urychluje práci a zároveň zaručuje ochranu před náhodnými chybami. Kontrola toho, co jsme zadali do tabulky, již není nutná.

1 - Nejrychlejší způsob.

Jaký je nejjednodušší způsob přidání rozevíracího seznamu? Stačí jedno kliknutí pravým tlačítkem myši na prázdnou buňku pod sloupcem dat a poté příkaz kontextové nabídky „Vybrat z rozevíracího seznamu“. Nebo můžete jen stát na správném místě a stisknout kombinaci kláves Alt+šipka dolů. Zobrazí se seřazený seznam jedinečných dříve zadaných hodnot.
Metoda nefunguje, pokud je naše buňka a sloupec se záznamy odděleny alespoň jedním prázdným řádkem nebo chcete zadat něco, co výše ještě nebylo zadáno. To je jasně vidět na našem příkladu.

2 - Použijte nabídku.

Podívejme se na malý příklad, ve kterém potřebujeme do tabulky zadávat neustále stejné názvy produktů. Zapište si do sloupce údaje, které budeme používat (například názvy produktů). V našem příkladu - v rozsahu G2:G7.

Vyberte buňku tabulky (můžete jich mít několik najednou), ve které chcete použít vstup z předem určeného seznamu. Dále v hlavním menu vyberte záložku Data – Validace. Dále klikněte na Typ dat (Povolit) a vyberte možnost Seznam. Umístěte kurzor do pole Zdroj a zadejte do něj adresy s referenčními hodnotami prvků - v našem případě G2:G7. Zde je také doporučeno používat absolutní odkazy (nastavte je stisknutím F4).

Bonusem je zde možnost nastavit si nápovědu a chybové hlášení, pokud chcete automaticky vkládanou hodnotu ručně změnit. K tomu slouží karty Input Message a Error Alert.

Jako zdroj můžete také použít pojmenovaný rozsah.

Například rozsah I2:I13, který obsahuje názvy měsíců, lze pojmenovat „měsíce“. Název pak můžete zadat do pole Zdroj.

Kromě toho může být zdroj jako běžný rozsah buněk i pojmenovaný rozsah na jiných listech v sešitu.

Nemusíte ale používat rozsahy nebo odkazy, ale jednoduše definovat možné možnosti přímo v poli „Zdroj“. Například tam napište -

K oddělení hodnot použijte středník, čárku nebo jiný znak, který jste nastavili jako oddělovač prvků. (Vidět Ovládací panely - Hodiny a region - Formáty - Pokročilé - Čísla.)

3 - Vytvořte ovládací prvek.

Vložíme do listu nový objekt - ovládací prvek "Combo Box" a ten pak svážeme s daty na listu Excel. My ano:

Otevřete kartu Vývojář. Pokud není vidět, musíte v Excelu 2007 kliknout na tlačítko Office – Možnosti – zaškrtávací políčko Zobrazit kartu Vývojář na pásu karet (tlačítko Office – Možnosti – Zobrazit kartu Vývojář na pásu karet) nebo ve verzích 2010–2013 kliknout pravým tlačítkem na pásu karet vyberte příkaz Přizpůsobit pás karet a pomocí zaškrtávacího políčka povolte zobrazení karty Vývojář.
Najděte požadovanou ikonu mezi ovládacími prvky (viz obrázek níže).

Po vložení ovládacího prvku do listu na něj klikněte pravým tlačítkem myši a ze zobrazené nabídky vyberte „Formátovat objekt“. Dále označíme rozsah buněk, ve kterých jsou zaznamenány platné hodnoty pro zadání. V poli "Odkaz na buňku" uvedeme, kam přesně umístit výsledek. Je důležité vzít v úvahu, že tímto výsledkem nebude samotná hodnota z námi uvedeného rozsahu, ale pouze její sériové číslo.

Ale nepotřebujeme toto číslo, ale slovo, které mu odpovídá. Používáme funkci INDEX (anglicky INDEX). Umožňuje vám najít jednu z nich v seznamu hodnot podle jejího sériového čísla. Jako argumenty INDEX zadejte rozsah buněk (F5:F11) a adresu s výsledným pořadovým číslem (F2).

Vzorec zapíšeme do F3, jak je znázorněno na obrázku:

INDEX(F5:F11;F2)

Stejně jako v předchozí metodě jsou zde možné odkazy na další listy a pojmenované rozsahy.

Upozorňujeme také, že zde nejsme vázáni na žádné konkrétní místo v tabulce. Tento seznam je vhodné používat, protože jej lze libovolně „přetáhnout“ myší na jakékoli vhodné místo. Chcete-li to provést, musíte na kartě „Vývojář“ aktivovat režim návrhu.

4 - Prvek ActiveX

Postupujeme podobně jako u předchozího způsobu, ale vybereme ikonu hned níže - ze sekce „ActiveX Elements“.

Definujeme seznam přijatelných hodnot (1). Upozorňujeme, že zde můžete vybrat více sloupců pro zobrazení najednou. Poté vybereme adresu, na kterou se vloží požadovaná pozice ze seznamu (2), uvedeme počet sloupců, které budou použity jako zdrojová data (3), a číslo sloupce, ze kterého bude výběr proveden pro vložení do listu (4). Pokud uvedete sloupec číslo 2, pak se do A5 vloží nikoli příjmení, ale pozice. Můžete také určit počet řádků, které se v seznamu zobrazí. Výchozí hodnota je 8. Zbytek lze posouvat myší (5).

Tato metoda je složitější než předchozí, ale okamžitě vrací hodnotu, nikoli její číslo. Není tedy potřeba mezičlánku a jeho zpracování pomocí INDEXU. Myslím, že použití tohoto seznamu je mnohem pohodlnější.

5 - Seznam s automatickým doplňováním

Úkol: Vytvořte seznam, do kterého budou automaticky přidány hodnoty ze zadaného dynamického rozsahu. Pokud jsou provedeny změny v rozsahu, sada navrhovaných hodnot se okamžitě změní. Zde není třeba upravovat žádné vzorce ani nastavení.

V jednoduchém příkladu může automatické doplňování vypadat takto:

Metoda 1. Uveďte známý velký zdroj.

Nejjednodušší a nekomplikovaný trik. Nejprve se řídíme obvyklým algoritmem akcí: v nabídce vyberte na kartě Data – Validace. Ze seznamu Typ dat (Povolit) vyberte možnost Seznam. Umístěte kurzor do pole Zdroj. V seznamu si rezervujme sadu s velkou rezervou: například do 55. řádku, ačkoli máme obsazených pouze 7. Nezapomeňte zaškrtnout políčko „Ignorovat prázdné...“. Pak vás vaše „rezerva“ prázdných hodnot nebude obtěžovat.

Opravdu nejjednodušší způsob, ale ne příliš pohodlný. Rezervované místo totiž může dojít...

Samozřejmě můžete také zadat celý sloupec jako zdroj:

Ale zpracování tak velkého počtu buněk může výpočty poněkud zpomalit.

Metoda 2: Použijte pojmenovaný rozsah.

Pojmenovaný rozsah se liší od běžného rozsahu tím, že je mu přidělen konkrétní název. Práce s ním je mnohem jednodušší, protože nemusíte zadávat odkaz, ale stačí uvést jeho název. Podívejme se na malý příklad.

Sloupec A obsahuje jména zaměstnanců, které zadáme. Seznam může být libovolně dlouhý. Chceme, aby byla každá nová položka zahrnuta do rozevíracího seznamu bez jakékoli další akce z naší strany.

Dovolte nám zdůraznit seznam jmen, který máme k dispozici A2:A10. Poté jej pojmenujte vyplněním pole „Name“ umístěného nalevo od řádku vzorců. Vytvořme seznam hodnot v C2. Jako zdroj pro něj uvádíme výraz

Nevýhodou práce s takovým seznamem je, že nové hodnoty nelze jednoduše připojit na konec použitého seznamu – zůstanou mimo pojmenovaný rozsah. Budou muset být vloženy dovnitř pomocí vložení prázdného řádku.

Seznam lze také třídit, aby bylo používání snazší.

Hlavní nevýhodou použití takového seznamu je, že rozsah, který používáme, je statický. Jeho velikost nelze automaticky měnit. Souhlasíte, není to příliš pohodlná a technologicky vyspělá metoda. Příliš mnoho ručních kroků.

Metoda 3. Pomůže nám „chytrá“ tabulka.

Od roku 2007 již excelová tabulka není jen sadou řádků a sloupců. Pokud jednoduše uspořádáte ukazatele v obvyklé tabulkové formě, nebude je považovat za tabulku. Existuje speciální formátování, po kterém se řada začíná chovat jako jeden celek a získává řadu zajímavých vlastností. Zejména začíná sledovat svou vlastní velikost, která se dynamicky mění podle toho, jak jsou data upravována.

Tímto způsobem lze transformovat jakoukoli sadu hodnot v tabulce. Například A1:A8. Vyberte je myší. Poté pomocí nabídky převeďte na tabulku Domů - Formátovat jako tabulku. Označte, že první řádek obsahuje název sloupce. Toto bude záhlaví vaší tabulky. Vzhled může být jakýkoli: není to nic jiného než vnější design a nic jiného neovlivňuje.

Jak již bylo zmíněno výše, „chytrá“ tabulka je pro nás dobrá, protože dynamicky mění svou velikost, když jsou do ní přidávány informace. Pokud cokoli zadáte do řádku pod ním, okamžitě to k sobě připojí. Lze tak jednoduše přidávat nové hodnoty. Zadejte například slovo „coconut“ do A9 a tabulka se okamžitě rozšíří na 9 řádků.

V důsledku toho lze organizovat automatickou aktualizaci sady použitých informací v seznamu, pokud použijete obsah libovolného sloupce „inteligentní“ tabulky.

Nezbývá než jej označit za zdroj. Problém je, že program jako zdroj v seznamu nerozumí výrazu formuláře

Tabulka1[Sloupec1]

a nepovažuje to za formuli. I když v běžných výrazech na listu vašeho sešitu to bude fungovat docela dobře. Tato konstrukce označuje odkaz na první sloupec. Ale z nějakého důvodu je v poli "Zdroj" ignorován.

Pro použití „chytré tabulky“ jako zdroje budeme muset použít malý trik a použít funkci NEPŘÍMÉ. Tato funkce převede textovou proměnnou na běžný odkaz.

Vzorec bude nyní vypadat takto:

NEPŘÍMÉ("Tabulka5[Produkt]")

Tabulka5 je název automaticky přiřazený k "inteligentní tabulce". U vás to může být jinak. Na kartě nabídky Návrh můžete změnit standardní název na svůj vlastní (ale bez mezer!). Pomocí něj pak můžeme přistupovat k naší tabulce na libovolném listu knihy.

„Produkt“ je název našeho prvního a jediného sloupce přiřazeného jeho záhlavím.

Nezapomeňte také celý výraz uzavřít do uvozovek, abyste jej označili jako textovou proměnnou.

Nyní, když do A9 přidáte další ovoce (například kokos), automaticky se objeví v našem seznamu. Bude to stejné, pokud něco smažeme. Problém automatického zvýšení rozevíracího seznamu hodnot byl vyřešen.

Doufáme, že nyní můžete pomocí seznamů zadávat často se opakující data do tabulky bez chyb.

Zde je několik dalších užitečných informací pro vás:

- Jednou z nejužitečnějších funkcí ověřování dat je možnost vytvořit rozevírací seznam, který vám umožní vybrat hodnotu z předdefinovaného seznamu. Ale jakmile to začnete aplikovat na své tabulky...

Prvek seznamu je nám známý z formulářů na webových stránkách. Je vhodné vybrat hotové hodnoty. Například měsíc nikdo nezadává ručně, přebírá se z takového seznamu. V aplikaci Excel můžete pomocí různých nástrojů vyplnit rozevírací seznam. V tomto článku se podíváme na každý 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 „Práce s daty“ najděte tlačítko „Ověření dat“. Klikněte na něj a vyberte první položku.

Otevře se okno. Na kartě „Možnosti“ v rozbalovací části „Typ dat“ vyberte „Seznam“.

Ve spodní části se objeví čára označující zdroje.

Informace můžete poskytovat 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 klikněte pravým tlačítkem. Klikněte na příkaz „Přiřadit jméno“.

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

Vyvolejte okno „Kontrola dat“ a v poli „Zdroj“ zadejte název umístěním znaku „=“ před něj.

V kterémkoli ze tří případů uvidíte požadovaný prvek. Výběr hodnoty z rozevíracího seznamu aplikace Excel se provádí pomocí myši. Klikněte na něj a zobrazí se seznam zadaných údajů.

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

Dynamická náhrada dat v Excelu

Pokud přidáte nějakou hodnotu do rozsahu dat, který se vkládá do seznamu, nedojde v něm k žádným změnám, dokud nebudou ručně zadány nové adresy. Chcete-li propojit rozsah a aktivní prvek, musíte první 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.

Tento design obdržíte.

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

NEPŘÍMÉ("Tabulka1[Města]")

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í dat z rozbalovacího seznamu do excelové tabulky – 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 (smazat) rozevírací seznam v Excelu

Otevřete okno nastavení rozevíracího seznamu a v části "Typ dat" vyberte možnost "Jakákoli hodnota".

Nepotřebný prvek zmizí.

Závislé položky

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 prvního bychom měli obdržet pouze adresy vybrané lokality.

V tomto případě pojmenujte každý sloupec. Vyberte bez první buňky (nadpisu) a klikněte pravým tlačítkem. Vyberte "Jméno".

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 s podtržítkem.

První prvek v buňce A9 vytvoříme běžným způsobem.

A ve druhém napíšeme vzorec:

NEPŘÍMÉ (A9)

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

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

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

Pro druhý prvek můžete použít dynamický datový rozsah. 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).

Získali jsme 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 je zobrazit v různých buňkách, nebo je můžete spojit do jedné. V každém případě je potřeba makro.
Klikněte pravým tlačítkem na štítek listu v dolní části a vyberte Zobrazit kód.

Otevře se okno vývojáře. Musíte 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 Target.Offset(1, 0) = Target Else Target.End(xlDown).Offset(1, 0) = Target End If Target.ClearContents Application.EnableEvents = True End If End Sub

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

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

Měli byste zadat adresu buňky se seznamem. Pro nás to bude E7.

Vraťte se do listu aplikace Excel 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. Chcete-li si přečíst tento článek.

Ukázali jsme vám, jak přidat a změnit rozevírací seznam v buňce Excelu. Doufáme, že vám tyto informace pomohou.

Měj krásný zbytek dne!

Microsoft Excel má schopnost vytvořit rozevírací seznam - to znamená, aby jedna buňka obsahovala několik hodnot, mezi kterými může uživatel přepínat. Jedná se o velmi pohodlný nástroj pro práci s tabulkami, zejména při kontrole zadaných údajů. Pokud tedy nevíte, jak vytvořit rozevírací seznam v aplikaci Excel, pomohou vám pokyny uvedené v článku.

Nejprve se podívejme na nejjednodušší způsob, který vám pomůže vytvořit rozevírací seznam. Chcete-li jej implementovat, proveďte vše podle pokynů níže:

Krok 1. Přejděte na kartu "Data", který je umístěn na horním panelu, pak v bloku "Práce s daty" vyberte nástroj pro ověřování dat (snímek obrazovky ukazuje, jakou ikonu představuje).

Krok 2. Nyní otevřete úplně první kartu "Možnosti", a nainstalovat "Seznam" v seznamu datových typů.

Krok 3 Nyní v poli pro zadávání dat "Zdroj" můžete zadat hodnoty, které budou tvořit rozevírací seznam. Můžete to provést následujícími způsoby:

Na poznámku! Abyste rozsah buněk nezadávali ručně, stačí kliknout levým tlačítkem myši a vybrat první buňku s hodnotou, vložit dvojtečku a myší označit poslední buňku s hodnotou.

Po použití kterékoli z metod a zadání názvu rozsahu hodnot dostanete výsledek jako na obrázku níže.

Na poznámku! Existuje ještě jeden způsob, jak určit hodnotu ve zdroji - napište název rozsahu do vstupního pole. Tato metoda je nejrychlejší, ale než ji použijete, musíte vytvořit pojmenovaný rozsah. O tom, jak to udělat, si povíme později.

Video – Vytváření rozevíracích seznamů v Excelu

Rozbalovací seznam s nahrazováním dat

Pokud vaše tabulka podléhá neustálým změnám a nechcete, aby ovlivnila obsah vašeho rozevíracího seznamu, pomůže vám následující metoda. Použijte jej a poté se v rozevíracím seznamu automaticky zobrazí úprava dat buňky. Chcete-li to provést, postupujte takto:

Vyberte rozsah pro seznam levým tlačítkem myši (v tomto příkladu to bude seznam stromů), poté otevřete záložku "Domov" a vyberte nabídku "Formátovat jako tabulku".
Poté se otevře nabídka výběru stylu. Styl nehraje jinou roli než vizuální, takže si vyberte jakýkoli podle svého vkusu.
Dále se objeví potvrzovací okno, jehož účelem je ověřit správnost zadaného rozsahu. Je důležité zaškrtnout políčko vedle "Tabulka se záhlavími", protože přítomnost názvu v tomto případě hraje klíčovou roli.
Po dokončení postupů obdržíte následující typ rozsahu.
Nyní klikněte levým tlačítkem myši na buňku, ve které bude umístěn rozevírací seznam, a přejděte na kartu "Data"(předchozí metoda vám říká, jak to udělat).
Ve vstupním poli "Zdroj" musíte zadat funkci se syntaxí “=NEPŘÍMÉ(“TableName[Header]”)”. Snímek obrazovky ukazuje konkrétnější příklad.

Seznam je tedy připraven. Bude to vypadat takto.

Na poznámku! Tato metoda má zásadní rozdíl od první - jako seznam se používá již hotový, nikoli rozsah hodnot. To znamená, že jakékoli změny v tabulce se projeví v rozevíracím seznamu.

Pojďme to otestovat. Nejprve do naší nové formátované tabulky přidáme novou buňku „vánoční stromeček“. Jak vidíte, do seznamu byla přidána stejná hodnota.

Pokud smažete buňku (v tomto případě jsme smazali „bříza“), projeví se to také v obsahu rozevíracího seznamu.

Na poznámku! V této metodě jsme se zabývali tzv. „chytrou tabulkou“. Je snadno rozšiřitelná a tato vlastnost je užitečná pro mnoho manipulací s tabulkami Excelu, včetně vytváření rozevíracího seznamu.

Závislé rozbalovací seznamy

Chcete-li vytvořit závislé rozevírací seznamy, postupujte podle těchto podrobných pokynů:

Nejprve musíte vytvořit pojmenovaný rozsah. Přejděte na kartu "vzorce" a poté vyberte "Správce jmen" A "Vytvořit".
Zadejte název a rozsah a poté klikněte "OK". Pokud jste předtím vybrali požadované buňky myší, rozsah bude určen automaticky. Všimněte si také, že název rozsahu musí být jedinečný a nesmí obsahovat interpunkci s mezerami.
Pomocí stejné techniky vytvořte tolik pojmenovaných rozsahů, kolik existuje logických závislostí, které chcete vytvořit. V tomto příkladu se jedná o další dva rozsahy: "keře" A "bylinky".
Otevřete kartu "Data"(v první metodě je uvedena cesta k němu) a uveďte názvy pojmenovaných rozsahů ve zdroji, jak je znázorněno na snímku obrazovky.
Nyní musíte vytvořit další rozevírací seznam se stejným designem. Tento seznam bude odrážet slova, která odpovídají názvu. Pokud jste například vybrali „Strom“, bude to „bříza“, „lípa“, „javor“ a tak dále. Chcete-li to provést, opakujte výše uvedené kroky, ale ve vstupním poli "Zdroj" zadejte funkci "=NEPŘÍMÉ(E1)" . V tomto případě "E1"– toto je adresa buňky s názvem prvního rozsahu. Stejnou metodou můžete vytvořit tolik vzájemně propojených seznamů, kolik potřebujete.

Nyní znáte několik způsobů, jak vytvořit rozevírací nabídku v . To se vám může hodit v řadě případů a zejména při ověřování údajů. Výběr správné metody by měl záviset na typu tabulky, se kterou pracujete. Pokud se jedná o „jednorázovou“ tabulku, pak je vhodná první metoda - je rychlá a snadná. Pokud tabulka vyžaduje neustálé změny, pak by bylo nejlepší použít metodu, která zahrnuje vytváření „inteligentních tabulek“ a pojmenovaných rozsahů.

Video – Související rozbalovací seznamy: Snadné a rychlé

Rozbalovací seznam v buňce umožňuje uživateli vybrat pro zadání pouze zadané hodnoty. To je užitečné zejména při práci se soubory strukturovanými jako databáze, kde zadání nevhodné hodnoty do pole může vést k nežádoucím výsledkům.

K vytvoření rozevíracího seznamu tedy potřebujete:

1. Vytvořte seznam hodnot, které budou poskytnuty uživateli na výběr (v našem příkladu se jedná o rozsah M1:M3), poté vyberte buňku, ve které bude rozevírací seznam (v našem příkladu je to buňka K1), poté přejděte na kartu „ Data", skupina" Práce s daty", knoflík " Kontrola dat"

2. Vyberte " Datový typ" -"Seznam“ a uveďte rozsah seznamu

3. Pokud chcete uživatele vyzvat k jeho akcím, přejděte na kartu „ Zpráva k zadání“ a vyplňte název a text zprávy

který se zobrazí, když vyberete buňku s rozevíracím seznamem

4. Volitelně můžete také vytvořit zprávu, která se zobrazí, když se pokusíte zadat nesprávná data

Pokud neprovedete kroky 3 a 4, pak kontrola dat bude fungovat, ale při aktivaci buňky se uživateli nezobrazí zpráva o jeho zamýšlených akcích a místo chybové zprávy s vaším textem se zobrazí standardní zpráva.

5. Pokud je seznam hodnot na jiném listu, nebudete moci vytvořit rozevírací seznam pomocí výše popsané metody (až do Excelu 2010). Chcete-li to provést, budete muset seznamu pojmenovat. To lze provést několika způsoby. První: vyberte seznam a klikněte pravým tlačítkem, v kontextové nabídce vyberte " Přiřadit jméno"

Pro verze Excelu nižší než 2007 vypadají stejné kroky takto:

Druhý: použití Správce jmen(Verze aplikace Excel vyšší než 2003 - karta " Vzorce" - skupina " Konkrétní jména"), který se v jakékoli verzi Excelu nazývá klávesovou zkratkou Ctrl+F3.
Ať už zvolíte jakoukoli metodu, nakonec budete muset zadat název (rozsah jsem pojmenoval seznamem seznam) a adresu samotného rozsahu (v našem příkladu to je "2"! $A$1:$A$3)

6. Nyní v buňce s rozevíracím seznamem zadejte do pole "Zdroj" název rozsahu

7. Připraveno!

Pro dokreslení dodám, že seznam hodnot lze zadat přímo do kontroly dat, aniž byste se museli uchylovat k umístění hodnot na list (to vám také umožní pracovat se seznamem na libovolném listu ). Dělá se to takto:

Tedy ručně, skrz ; (středník) zadejte seznam do pole " Zdroj“, v pořadí, v jakém jej chceme vidět (hodnoty zadané zleva doprava se zobrazí v buňce shora dolů).

Se všemi svými výhodami má rozevírací seznam vytvořený výše popsaným způsobem jednu, ale velmi „tučnou“ nevýhodu: ověřování dat funguje pouze při přímém zadávání hodnot z klávesnice. Pokud se pokusíte vložit do buňky s ověření dat hodnoty ze schránky, tj. zkopírované dříve jakýmkoli způsobem, pak uspějete. Vložená hodnota z vyrovnávací paměti navíc ODEBRANÍ SEZNAM KONTROLY A DROPPING DAT Z BUŇKY, do které byla vložena dříve zkopírovaná hodnota. Tomu se nelze vyhnout pomocí standardních nástrojů Excelu.

Pokud vyplňujete tabulku v Excelu a údaje ve sloupci se mohou někdy opakovat, například název produktu nebo jméno zaměstnance, pak, aby se požadovaný parametr nezadával pokaždé, je jednodušší a jednodušší vytvořit rozevírací seznam jednou a vybrat z něj hodnotu.

V tomto článku se podíváme na to, jak vytvořit rozevírací seznamy různých typů v tabulce aplikace Excel.

Vytvořte jednoduchý rozevírací seznam

Za tímto účelem do buněk A1:A7 zadáme údaje, které se zobrazí v seznamu. Nyní vybereme buňku, ve které budeme tvořit rozevírací seznam – B2.

Přejděte na kartu „Data“ a klikněte na tlačítko „Kontrola dat“.

Na kartě „Parametry“ v poli „Typ dat“ vyberte „Seznam“. Hodnoty do pole Zdroj můžete zadat různými způsoby:

1 – ručně zadejte hodnoty pro seznam oddělené středníky;

2 – označte rozsah buněk, do kterých se zadávají údaje pro rozevírací seznam;

3 – vyberte buňky s názvy, klikněte na ně pravým tlačítkem a z nabídky vyberte „Přiřadit název“.

Vyberte buňku B2 a do pole „Zdroj“ vložte „=“, poté napište vytvořený název.

Vytvořili jsme tedy jednoduchý rozevírací seznam v Excelu.

Pokud máte záhlaví sloupce a potřebujete vyplnit každý řádek hodnotami, vyberte ne jednu buňku, ale rozsah buněk - B2:B9. Poté můžete vybrat požadovanou hodnotu v každé buňce z rozevíracího seznamu.

Přidání hodnot do rozevíracího seznamu - dynamický seznam

V tomto případě přidáme hodnoty do požadovaného rozsahu a automaticky se přidají do rozevíracího seznamu.

Vyberte rozsah buněk – D1:D8, poté na kartě „Domů“ klikněte na „Formátovat jako tabulku“ a vyberte libovolný styl.

Potvrďte umístění dat a zaškrtněte políčko „Tabulka se záhlavími“.

V horní části napíšeme název tabulky - „Zaměstnanci“ a vyplníme ji údaji.

Vyberte buňku, ve které bude rozevírací seznam, a klikněte na tlačítko „Kontrola dat“. V dalším okně do pole „Zdroj“ napište následující: =NEPŘÍMÝ(“Tabulka1”). Mám jednu tabulku na listu, takže píšu „Tabulka1“, pokud existuje druhá – „Tabulka2“ a tak dále.

Nyní do našeho seznamu přidáme nové jméno zaměstnance: Ira. Objevil se v rozevíracím seznamu. Pokud nějaké jméno z tabulky smažeme, smaže se i ze seznamu.

Rozbalovací seznam s hodnotami z jiného listu

Pokud je tabulka s rozbalovacími seznamy na jednom listu a data pro tyto seznamy na druhém, pak nám tato funkce velmi pomůže.

Na Listu 2 vyberte jednu buňku nebo rozsah buněk a klikněte na tlačítko „Ověření dat“.

Přejděte na List 1, umístěte kurzor do pole „Zdroj“ a vyberte požadovaný rozsah buněk.

Nyní můžete přidat jména na List 1, budou přidána do rozevíracích seznamů na Listu 2.

Vytváření závislých rozevíracích seznamů

Předpokládejme, že máme tři rozsahy: křestní jména, příjmení a patronymie zaměstnanců. Každému musíte přiřadit jméno. Vybereme buňky tohoto rozsahu, můžete je i vyprázdnit – časem do nich můžete přidat data, která se objeví v rozevíracím seznamu. Klikněte na ně pravým tlačítkem myši a ze seznamu vyberte „Přiřadit název“.

Prvnímu říkáme „Jméno“, druhému – „Příjmení“, třetímu – „Otec“.

Udělejme další rozsah, do kterého se budou zapisovat přiřazená jména. Říkejme tomu „Zaměstnanci“.

Vytvoříme první rozevírací seznam, který se bude skládat z názvů rozsahů. Vyberte buňku E1 a na záložce „Data“ vyberte „Ověření dat“.

V poli „Typ dat“ vyberte „Seznam“ a v poli Zdroj zadejte „=Zaměstnanci“ nebo vyberte rozsah buněk, kterým byl přiřazen název.

První rozevírací seznam byl vytvořen. Nyní v buňce F2 vytvoříme druhý seznam, který by měl záviset na prvním. Pokud v prvním vybereme „Jméno“, v druhém se zobrazí seznam jmen, pokud zvolíme „Příjmení“, zobrazí se seznam příjmení.

Vyberte buňku a klikněte na tlačítko „Kontrola dat“. V poli „Typ dat“ vyberte „Seznam“ a do pole Zdroj zadejte následující: =NEPŘÍMÉ($E$1). Zde E1 je buňka s prvním rozevíracím seznamem.

Pomocí tohoto principu můžete vytvářet závislé rozevírací seznamy.

Pokud v budoucnu budete muset zadat hodnoty do rozsahu, který je pojmenován, například „Příjmení“. Přejděte na kartu Vzorce a klikněte na Správce názvů. Nyní v názvu rozsahu vyberte „Příjmení“ a níže místo poslední buňky C3 napište C10. Klepněte na zaškrtnutí. Poté se rozsah zvětší a můžete do něj přidat data, která se automaticky objeví v rozevíracím seznamu.

Nyní víte, jak vytvořit rozevírací seznam v aplikaci Excel.

Jak vytvořit rozevírací seznam skládající se z několika buněk najednou (například tak, aby název měl cenu)

Díky, vše fungovalo dobře.

Rozbalovací seznam s hodnotami z jiného listu nefunguje, protože okno při ověřování dat neumožňuje práci s jinými okny, zejména s jiným listem!

Závislý rozevírací seznam vám umožňuje provést trik, který uživatelé šablon Excelu často chválí. Trik, který usnadňuje a urychluje práci. Trik, díky kterému budou vaše křivky pohodlné a příjemné.

Příklad vytvoření závislého rozevíracího seznamu v buňce Excelu

Příklad použití závislého rozevíracího seznamu pro vytvoření pohodlného formuláře pro vyplnění dokumentů, pomocí kterých si prodejci objednali zboží. Z celého sortimentu si museli vybrat produkty, které budou prodávat.

Každý prodejce nejprve identifikoval skupinu produktů a poté konkrétní produkt z této skupiny. Formulář musí obsahovat celý název skupiny a konkrétní index produktů. Protože psát to ručně by bylo příliš časově náročné (a otravné), přišel jsem s velmi rychlým a jednoduchým řešením - 2 závislé rozbalovací nabídky.

První byl seznam všech kategorií produktů, druhý byl seznam všech produktů ve vybrané kategorii. Vytvořil jsem tedy rozevírací seznam závislý na výběru provedeném v předchozím seznamu (zde najdete materiál o tom, jak vytvořit dva závislé rozevírací seznamy).

Uživatel šablony domácího rozpočtu chce dosáhnout stejného výsledku tam, kde potřebuje kategorii a podkategorii výdajů. Příklad dat je na obrázku níže:

Pokud tedy například vybereme kategorii Zábava, pak by seznam podkategorií měl obsahovat: Kino, Divadlo, Bazén. Velmi rychlé řešení, pokud chcete analyzovat podrobnější informace ve svém domácím rozpočtu.

Seznam kategorií a podkategorií v rozevíracím seznamu závislém na Excelu

Přiznám se, že ve verzi mnou navrhovaného domácího rozpočtu se omezuji pouze na kategorii, jelikož mi takové rozdělení výdajů zcela stačí (název výdajů/příjmů je považován za podkategorii). Pokud je však potřebujete rozdělit do podkategorií, pak je ideální způsob, který popisuji níže. Klidně to použijte!

A konečný výsledek vypadá takto:

Závislý rozevírací seznam podkategorií

Abychom toho dosáhli, musíme vytvořit trochu jinou datovou tabulku, než kdybychom vytvářeli jediný rozevírací seznam. Tabulka by měla vypadat takto (rozsah G2:H15):

Pracovní Excel zdrojová tabulka

V této tabulce musíte zadat kategorii a její podkategorie vedle ní. Název kategorie se musí opakovat tolikrát, kolikrát existuje podkategorií. Je velmi důležité, aby byly údaje seřazeny podle sloupce Kategorie. To bude nesmírně důležité, až budeme vzorec psát později.

Můžete také použít tabulky z prvního obrázku. Samozřejmě, že vzorce by byly jiné. Jednou jsem takové řešení našel na internetu, ale nelíbilo se mi, protože mělo pevnou délku seznamu: což znamená, že seznam někdy obsahoval prázdná pole a někdy nezobrazoval všechny prvky. Tomuto omezení se samozřejmě můžu vyhnout, ale přiznám se, že se mi mé řešení líbí víc, takže jsem se k tomu řešení nikdy nevrátil.

Dobře tedy. Nyní popíšu kroky vytváření závislého rozevíracího seznamu jeden po druhém.

1. Názvy oblastí buněk

Jedná se o volitelný krok, bez něj to bez problémů zvládneme. Rád však používám názvy, protože díky nim je vzorec mnohem jednodušší jak pro psaní, tak pro čtení.

Pojďme přiřadit názvy dvěma rozsahům. Seznam všech kategorií a pracovní seznam kategorií. Tyto rozsahy by byly A3:A5 (seznam kategorií v zeleném listu na prvním obrázku) a G3:G15 (seznam opakujících se kategorií ve fialovém listu).

Chcete-li pojmenovat seznam kategorií:

Vyberte rozsah A3:A5.
Do pole Název (pole nalevo od řádku vzorců) zadejte název „Kategorie“.
Potvrďte klávesou Enter.

Proveďte stejnou akci pro rozsah pracovního seznamu kategorií G3:G15, který můžete nazvat „Work_List“. Tento rozsah použijeme ve vzorci.

2. Vytvořte rozevírací seznam pro kategorii

Bude to jednoduché:

Vyberte buňku, kam chcete umístit seznam. V mém případě je to A12.
Z nabídky DATA vyberte nástroj Data Validation. Zobrazí se okno „Check Input Values“.
Jako datový typ vyberte Seznam.
Jako zdroj zadejte: =Category (obrázek níže).
Potvrďte tlačítkem OK.

Výsledek je následující:

Rozbalovací seznam pro kategorii.

3. Vytvořte závislý rozevírací seznam pro podkategorii

Teď to bude zábava. Víme, jak vytvářet seznamy – právě jsme to udělali pro kategorii. Jen jedna otázka: "Jak řeknu Excelu, aby vybral pouze ty hodnoty, které jsou určeny pro konkrétní kategorii?" Jak asi tušíte, budu zde používat pracovní list a samozřejmě vzorce.

Začněme tím, co již víme, tedy vytvořením rozevíracího seznamu v buňce B12. Vyberte tedy tuto buňku a klikněte na Ověření dat/dat a Typ dat je Seznam.

Do zdroje seznamu zadejte následující vzorec:

Pohled na okno „Kontrola vstupních hodnot“:

Ověřování vstupních hodnot pro podkategorii v závislém rozevíracím seznamu

Jak vidíte, celý trik na závislý seznam spočívá v použití funkce OFFSET. Dobře, skoro všechno. Pomáhají jí funkce MATCH a COUNTIF. Funkce OFFSET umožňuje dynamicky definovat rozsahy. Nejprve definujeme buňku, od které má posun rozsahu začínat, a v dalších argumentech definujeme její velikost.

V našem příkladu se rozsah přesune přes sloupec Podkategorie v listu (G2:H15). Začneme se pohybovat od buňky H2, což je také první argument naší funkce. Ve vzorci je buňka H2 zapsána jako absolutní reference, protože předpokládám, že v mnoha buňkách použijeme rozevírací seznam.

Protože je list seřazen podle kategorie, rozsah, který by měl být zdrojem pro rozevírací seznam, bude začínat tam, kde se vybraná kategorie poprvé objeví. Například pro kategorii Jídlo chceme zobrazit rozsah H6:H11, pro Doprava - rozsah H12:H15 atd. Všimněte si, že se neustále pohybujeme po sloupci H a jediné, co se mění, je začátek rozsahu a jeho výšky (tj. počtu prvků v seznamu).

Začátek rozsahu bude posunut vzhledem k buňce H2 o tolik buněk dolů (v počtu), jako je číslo pozice první vyskytující se kategorie ve sloupci Kategorie. Snáze to pochopíte na příkladu: rozsah pro kategorii Jídlo byl posunut o 4 buňky dolů vzhledem k buňce H2 (začíná od 4 buněk od H2). Ve 4. buňce sloupce Podkategorie (bez záhlaví, protože mluvíme o rozsahu zvaném Work_List) je slovo Výživa (jeho první výskyt). Tuto skutečnost využíváme k tomu, abychom skutečně určili začátek rozsahu. K tomu nám poslouží funkce MATCH (představená jako druhý argument funkce OFFSET):

Výška rozsahu je určena funkcí COUNTIF. Ta počítá všechny výskyty opakování v kategorii, tedy slovo Výživa. Kolikrát se toto slovo vyskytuje, počet pozic bude v našem rozsahu. Počet pozic v rozsahu je jeho výška. Zde je funkce:

Obě funkce jsou samozřejmě již obsaženy ve výše popsané funkci OFFSET. Všimněte si také, že ve funkcích MATCH i COUNTIF je odkaz na rozsah nazvaný WorkList. Jak jsem již zmínil, nemusíte používat názvy rozsahů, stačí zadat $H3:$H15. Použití názvů rozsahů ve vzorci však usnadňuje a usnadňuje čtení.

To je vše:

Stáhněte si příklad závislého rozevíracího seznamu v Excelu

Jeden vzorec, ne tak jednoduchý, ale usnadňuje práci a chrání před chybami při zadávání dat!