Vytvářejte propojené rozevírací seznamy v Excelu – nejjednodušší způsob! Strukturování (seskupení) dat na listu

Při práci v Microsoft Excelu v tabulkách s opakujícími se údaji je velmi vhodné použít rozbalovací seznam. S ním můžete jednoduše vybrat požadované parametry z vygenerované nabídky. Pojďme zjistit, jak vytvořit rozevírací seznam různými způsoby.

Nejpohodlnější a zároveň nejfunkčnější způsob, jak vytvořit rozevírací seznam, je metoda založená na sestavení samostatného seznamu dat.

Nejprve si vytvoříme šablonu tabulky, kde budeme používat rozbalovací nabídku, a také vytvoříme samostatný seznam dat, která do této nabídky v budoucnu zařadíme. Tato data lze umístit buď na stejný list dokumentu, nebo na jiný, pokud nechcete, aby byly obě tabulky vizuálně umístěny společně.

Do rozbalovacího seznamu vybereme údaje, které plánujeme zadat. Klikněte pravým tlačítkem myši a z kontextové nabídky vyberte „Přiřadit název...“.

Otevře se formulář pro vytvoření jména. Do pole „Název“ zadejte jakýkoli vhodný název, podle kterého tento seznam rozpoznáme. Ale toto jméno musí začínat písmenem. Můžete také zadat poznámku, ale není to povinné. Klikněte na tlačítko „OK“.

Přejděte na kartu „Data“ v aplikaci Microsoft Excel. Vyberte oblast tabulky, kde budeme používat rozevírací seznam. Klikněte na tlačítko „Kontrola dat“ umístěné na pásu karet.

Otevře se okno pro kontrolu zadaných hodnot. Na kartě „Parametry“ v poli „Typ dat“ vyberte možnost „Seznam“. Do pole „Zdroj“ dáme rovnítko a hned bez mezer napíšeme název seznamu, který jsme mu přiřadili výše. Klikněte na tlačítko „OK“.

Rozbalovací seznam je připraven. Nyní, když kliknete na tlačítko, zobrazí se pro každou buňku zadaného rozsahu seznam parametrů, ze kterého můžete vybrat libovolný, který chcete do buňky přidat.

Vytvoření rozbalovací nabídky pomocí nástrojů pro vývojáře

Druhá metoda zahrnuje vytvoření rozevíracího seznamu pomocí nástrojů pro vývojáře, konkrétně pomocí ActiveX. Ve výchozím nastavení chybí funkce vývojářských nástrojů, takže je budeme muset nejprve povolit. Chcete-li to provést, přejděte na kartu „Soubor“ v aplikaci Excel a poté klikněte na „Možnosti“.

V okně, které se otevře, přejděte do podsekce „Přizpůsobit pás karet“ a zaškrtněte políčko vedle hodnoty „Vývojář“. Klikněte na tlačítko „OK“.

Poté se na pásu karet zobrazí karta s názvem „Vývojář“, kam se přesuneme. V aplikaci Microsoft Excel nakreslíme seznam, který by se měl stát rozbalovací nabídkou. Poté klikněte na ikonu „Vložit“ na pásu karet a z prvků, které se zobrazí ve skupině „Prvek ActiveX“, vyberte „Combo Box“.

Klikněte na místo, kde má být buňka se seznamem. Jak vidíte, objevil se formulář seznamu.

Poté přejdeme do „Design Mode“. Klikněte na tlačítko „Vlastnosti ovládání“.

Otevře se okno vlastností ovládacího prvku. Do sloupce „ListFillRange“ ručně zadejte rozsah buněk tabulky oddělený dvojtečkou, jehož údaje budou tvořit položky rozevíracího seznamu.

Rozbalovací seznam v aplikaci Microsoft Excel je připraven.

Chcete-li vytvořit další buňky s rozevíracím seznamem, jednoduše se postavte na pravý dolní okraj hotové buňky, stiskněte tlačítko myši a přetáhněte ji dolů.

Související seznamy

V aplikaci Excel můžete také vytvářet propojené rozevírací seznamy. Jedná se o seznamy, kde když vyberete jednu hodnotu ze seznamu, v jiném sloupci jste požádáni o výběr odpovídajících parametrů. Například při výběru brambor v seznamu produktů jste požádáni, abyste jako míry vybrali kilogramy a gramy a při výběru rostlinného oleje litry a mililitry.

Nejprve si připravíme tabulku, kde se budou nacházet rozbalovací seznamy, a samostatně si udělejme seznamy s názvy výrobků a měr.

Každému ze seznamů přiřadíme pojmenovaný rozsah, jako jsme to dělali dříve u běžných rozevíracích seznamů.

V první buňce vytvoříme seznam přesně stejným způsobem jako dříve, a to prostřednictvím ověření dat.

V druhé buňce také spustíme okno ověření dat, ale do sloupce „Zdroj“ zadáme funkci „=NEPŘÍMÉ“ a adresu první buňky. Například =NEPŘÍMÉ($B3).

Jak vidíte, seznam byl vytvořen.

Nyní, aby spodní buňky získaly stejné vlastnosti jako minule, vyberte horní buňky a při stisknutém tlačítku myši je přetáhněte dolů.

To je vše, tabulka byla vytvořena.

Přišli jsme na to, jak vytvořit rozevírací seznam v Excelu. V programu můžete vytvářet jak jednoduché rozevírací seznamy, tak i závislé. V tomto případě můžete použít různé metody vytváření. Výběr závisí na konkrétním účelu seznamu, účelu jeho vytvoření, rozsahu použití atd.

Jak vytvořit rozevírací seznam v Excelu? Každý už dávno ví, jak dobře Excel pracuje s tabulkami a různými druhy vzorců, ale málokdo ví, že zde můžete vytvářet rozevírací seznamy. A dnes si o nich povíme.

Existuje tedy několik možností, jak vytvořit rozevírací seznamy pro práci v aplikaci Microsoft Office Excel.

Možnost jedna je velmi jednoduchá. Pokud zadáte podobná data do stejného sloupce shora dolů, stačí se postavit na buňku pod daty a stisknout kombinaci kláves „Alt + šipka dolů“. Objeví se před vámi rozevírací seznam, ze kterého si jedním kliknutím vyberete data, která potřebujete.

Nevýhodou této metody je, že je určena pro sekvenční způsob zadávání dat a pokud kliknete na kteroukoli jinou buňku ve sloupci, rozevírací seznam bude prázdný.

Možnost dvě dává více příležitostí, stále je považována za standardní. To lze provést kontrolou dat. Nejprve musíme vybrat rozsah dat, která se do našeho seznamu dostane, a pojmenovat jej.

Tento rozsah můžete upravit na kartě nabídky „Formulas“ výběrem ikony „Správce jmen“. V něm můžete vytvořit nový rozevírací seznam, upravit stávající nebo jednoduše odstranit nepotřebný.

Dalším krokem je vybrat buňku, kde bude umístěn náš rozevírací seznam, a přejděte na kartu nabídky „Data“ a klikněte na ikonu „Kontrola dat“. V okně, které se otevře, musíme vybrat typ údajů, které se zadají do naší buňky. V našem případě vybereme „Seznamy“ a pod rovnítko zapíšeme název našeho rozsahu a klikneme na OK. Chcete-li použít seznam na všechny buňky, stačí před zapnutím ověřování dat vybrat celý sloupec nebo oblast, kterou potřebujete.

Existují další složitější možnosti pro vytvoření rozevíracího seznamu, například: Vkládání prostřednictvím karty nabídky Vývojář, kde můžete vkládat rozevírací seznamy jako součást prvku formuláře nebo jako součást ovládacího prvku ActiveX. Nebo napište vhodná makra pro vytváření a ovládání rozevíracích seznamů.

Zadejte data do buněk A1:A10, které budou sloužit jako zdroj pro seznam. V našem příkladu jsme zadali čísla, objeví se v rozevíracím seznamu. Vyberte buňku (například E5), která bude obsahovat rozevírací seznam. Vyberte nabídku Data -> Ověření dat a otevřete dialogové okno Ověřit vstupní hodnoty.

3. Na kartě Možnosti vyberte z rozevírací nabídky možnost Seznam. Ujistěte se, že jsou zaškrtnuta správná políčka.

4. Poté klikněte na tlačítko. Zobrazí se následující dialogové okno.

5. Vyberte položky, které se objeví v rozevíracím seznamu na listu pomocí myši, klikněte na tlačítko a vraťte se zpět do okna „Validate input values“, poté klikněte na tlačítko „OK“.

6. V Excelu se vytvoří rozevírací seznam.

Pokud je váš seznam krátký, můžete položky zadat přímo do Zdroj na kartě Nastavení v dialogovém okně Ověřit vstup. Jednotlivé položky seznamu oddělte oddělovači určenými v místním nastavení.
Pokud seznam musí být na jiném listu, můžete před určením rozsahu dat použít volbu "=Seznam".
Jak vytvořit rozevírací seznam v Excelu na základě dat ze seznamu

Představme si, že máme seznam ovoce:
Jak vytvořit rozevírací seznam v aplikaci Excel

Chcete-li vytvořit rozevírací seznam, budeme muset provést následující kroky:

Přejděte na kartu „Data“ => část „Práce s daty“ na panelu nástrojů => vyberte položku „Ověření dat“.

Do pole „Zdroj“ zadejte rozsah názvů ovoce =$A$2:$A$6 nebo jednoduše umístěte kurzor myši do pole pro zadání hodnoty „Zdroj“ a poté vyberte rozsah dat myší:

Pokud chcete vytvořit rozevírací seznamy ve více buňkách najednou, vyberte všechny buňky, ve kterých je chcete vytvořit, a poté postupujte podle výše uvedených kroků. Je důležité zajistit, aby odkazy na buňky byly absolutní (například $A$2) a nikoli relativní (například A2 nebo A$2 nebo $A2).

Jak vytvořit rozevírací seznam v aplikaci Excel pomocí ručního zadávání dat

Ve výše uvedeném příkladu jsme zadali seznam dat pro rozevírací seznam výběrem rozsahu buněk. Kromě této metody můžete data pro vytvoření rozevíracího seznamu zadávat ručně (není nutné jej ukládat do žádných buněk).
Představte si například, že chceme v rozevírací nabídce zobrazit dvě slova „Ano“ a „Ne“.

K tomu potřebujeme:
Vyberte buňku, ve které chceme vytvořit rozevírací seznam;
Přejděte na kartu „Data“ => část „Práce s daty“ na panelu nástrojů =>
Ověřování dat v Excelu

Ve vyskakovacím okně „Kontrola vstupních hodnot“ na záložce „Parametry“ vyberte v typu dat „Seznam“:
Ověřování vstupních hodnot v Excelu

Do pole „Zdroj“ zadejte hodnotu „Ano; Žádný".
Klikněte na „OK“
Vlastně ne

Systém poté ve vybrané buňce vytvoří rozevírací seznam. Všechny prvky uvedené v poli „Zdroj“ oddělené středníky se projeví na různých řádcích rozbalovací nabídky.

Pokud chcete současně vytvořit rozevírací seznam v několika buňkách, vyberte požadované buňky a postupujte podle výše uvedených pokynů.
Jak vytvořit rozevírací seznam v Excelu pomocí funkce OFFSET

Spolu s metodami popsanými výše můžete také použít vzorec OFFSET k vytvoření rozevíracích seznamů.

Máme například seznam se seznamem ovoce:

Chcete-li vytvořit rozevírací seznam pomocí vzorce OFFSET, musíte provést následující:
Vyberte buňku, ve které chceme vytvořit rozevírací seznam;
Přejděte na kartu „Data“ => část „Práce s daty“ na panelu nástrojů => vyberte „Ověření dat“:
Ověřování dat v Excelu

Ve vyskakovacím okně „Kontrola vstupních hodnot“ na záložce „Parametry“ vyberte v typu dat „Seznam“:
Ověřování vstupních hodnot v Excelu

Do pole „Zdroj“ zadejte vzorec: = OFFSET(A$2$,0,0,5)
Klikněte na „OK“

Systém vytvoří rozevírací seznam se seznamem ovoce.
Jak tento vzorec funguje?

Ve výše uvedeném příkladu jsme použili vzorec =OFFSET(link,offset_by_rows,offset_by_columns,[height],[width]).
Tato funkce obsahuje pět argumentů. Argument „link“ (v příkladu $A$2) udává, ze které buňky má offset začít. V argumentech „offset_by_rows“ a „offset_by_columns“ (v příkladu je uvedena hodnota „0“) – kolik řádků/sloupců je třeba posunout pro zobrazení dat.

Argument „[height]“ je nastaven na „5“, což představuje výšku rozsahu buněk. Neuvádíme argument „[šířka]“, protože v našem příkladu se rozsah skládá z jednoho sloupce.
Pomocí tohoto vzorce vám systém vrátí jako data pro rozevírací seznam rozsah buněk začínající buňkou $A$2, který se skládá z 5 buněk.

Jak vytvořit rozevírací seznam v Excelu s náhradou dat (pomocí funkce OFFSET)

Pokud k vytvoření seznamu použijete vzorec OFFSET ve výše uvedeném příkladu, vytváříte seznam dat, která jsou zachycena v určitém rozsahu buněk. Pokud chcete přidat jakoukoli hodnotu jako položku seznamu, budete muset vzorec upravit ručně.

Níže se dozvíte, jak vytvořit dynamický rozevírací seznam, který automaticky načte nová data pro zobrazení.
K vytvoření seznamu budete potřebovat:
Vyberte buňku, ve které chceme vytvořit rozevírací seznam;

Přejděte na kartu „Data“ => část „Práce s daty“ na panelu nástrojů => vyberte „Ověření dat“;
Ve vyskakovacím okně „Kontrola vstupních hodnot“ na záložce „Parametry“ vyberte v datovém typu „Seznam“;
Do pole „Zdroj“ zadejte vzorec: =OFFEST(A$2$,0,0,COUNTIF($A$2:$A$100;“<>”))
Klikněte na „OK“

V tomto vzorci v argumentu „[height]“ uvedeme jako argument označující výšku seznamu s daty – vzorec COUNTIF, který vypočítá počet neprázdných buněk v daném rozsahu A2:A100.

Poznámka: pro správnou funkci vzorce je důležité, aby v seznamu údajů, které se mají zobrazit v rozbalovací nabídce, nebyly žádné prázdné řádky.

Jak vytvořit rozevírací seznam v Excelu s automatickým nahrazováním dat

Aby se nová data automaticky načetla do vámi vytvořeného rozevíracího seznamu, musíte provést následující:
Vytvoříme seznam dat, která se zobrazí v rozevíracím seznamu. V našem případě se jedná o seznam barev. Vyberte seznam levým tlačítkem myši:
rozevírací seznam s automatickým nahrazováním v Excelu

Na panelu nástrojů klikněte na „Formátovat jako tabulku“:

Z rozbalovací nabídky vyberte styl návrhu tabulky

Kliknutím na tlačítko „OK“ ve vyskakovacím okně potvrdíme vybraný rozsah buněk:

Přiřaďte název tabulce v pravé horní buňce nad sloupcem „A“:

Tabulka s údaji je připravena, nyní můžeme vytvořit rozevírací seznam. K tomu potřebujete:
Vyberte buňku, ve které chceme vytvořit seznam;

Přejděte na kartu „Data“ => část „Práce s daty“ na panelu nástrojů => vyberte „Ověření dat“:

Ve vyskakovacím okně „Kontrola vstupních hodnot“ na záložce „Parametry“ vyberte v typu dat „Seznam“:

Ve zdrojovém poli uvedeme = „název vaší tabulky“. V našem případě jsme to nazvali „Seznam“:
Automatické nahrazování dat zdrojového pole v rozevíracím seznamu Excel

Připraveno! Byl vytvořen rozevírací seznam, který zobrazuje všechna data ze zadané tabulky:

Chcete-li do rozevíracího seznamu přidat novou hodnotu, jednoduše přidejte informace do buňky následující za tabulkou s daty:

Tabulka automaticky rozšíří rozsah dat. Rozbalovací seznam bude odpovídajícím způsobem doplněn o novou hodnotu z tabulky:
Automatické vkládání dat do rozevíracího seznamu v Excelu

Jak zkopírovat rozevírací seznam v aplikaci Excel

Excel má schopnost kopírovat vytvořené rozevírací seznamy. Například v buňce A1 máme rozevírací seznam, který chceme zkopírovat do rozsahu buněk A2:A6.

Chcete-li zkopírovat rozevírací seznam s aktuálním formátováním:
klikněte levým tlačítkem myši na buňku s rozevíracím seznamem, který chcete zkopírovat;

vyberte buňky v rozsahu A2:A6, do kterých chcete vložit rozevírací seznam;

Stiskněte klávesovou zkratku CTRL+V.
Zkopírujete tedy rozevírací seznam se zachováním původního formátu seznamu (barva, písmo atd.). Pokud chcete zkopírovat/vložit rozevírací seznam bez uložení formátu, pak:
klikněte levým tlačítkem myši na buňku s rozevíracím seznamem, který chcete zkopírovat;

stiskněte klávesovou zkratku CTRL+C;
vyberte buňku, kam chcete vložit rozevírací seznam;
klikněte pravým tlačítkem => vyvolejte rozevírací nabídku a klikněte na „Vložit jinak“;
rozevírací seznam v excelu

V okně, které se zobrazí, v části „Vložit“ vyberte „podmínky hodnot“:

Klikněte na „OK“
Poté Excel zkopíruje pouze data z rozevíracího seznamu bez zachování formátování původní buňky.
Jak vybrat všechny buňky obsahující rozevírací seznam v aplikaci Excel

Někdy je obtížné pochopit, kolik buněk v souboru aplikace Excel obsahuje rozevírací seznamy. Existuje snadný způsob, jak je zobrazit. Postup:

Klikněte na kartu „Domů“ na panelu nástrojů;
Klikněte na „Najít a vybrat“ a vyberte „Vybrat skupinu buněk“:

V dialogovém okně vyberte „Ověření dat“. V tomto poli můžete vybrat položky „Vše“ a „Stejné“. „Vše“ vám umožní vybrat všechny rozevírací seznamy na listu. Položka „stejné“ zobrazí v rozevírací nabídce rozevírací seznamy s podobným obsahem dat. V našem případě vybereme „vše“:
Rozbalovací seznam v Excelu. Jak najít všechny seznamy

Klikněte na „OK“
Kliknutím na „OK“ Excel vybere všechny buňky s rozevíracím seznamem na listu. Tímto způsobem můžete všechny seznamy převést do společného formátu najednou, zvýraznit hranice atd.

Jak vytvořit závislé rozevírací seznamy v Excelu

Někdy potřebujeme vytvořit několik rozevíracích seznamů, a to tak, že výběrem hodnot z prvního seznamu Excel určí, jaká data se mají zobrazit ve druhém rozevíracím seznamu.
Předpokládejme, že máme seznamy měst ve dvou zemích, Rusku a USA:

K vytvoření závislého rozevíracího seznamu potřebujeme:
Vytvořte dva pojmenované rozsahy pro buňky „A2:A5“ s názvem „Rusko“ a pro buňky „B2:B5“ s názvem „USA“. K tomu musíme vybrat celý rozsah dat pro rozevírací seznamy:
závislý rozevírací seznam v Excelu

Přejděte na kartu „Vzorce“ => klikněte v části „Definované názvy“ na položku „Vytvořit z výběru“:
Závislé rozevírací seznamy v Excelu

Ve vyskakovacím okně „Vytvořit názvy z vybraného rozsahu“ zaškrtněte políčko „na řádku výše“. Poté Excel vytvoří dva pojmenované rozsahy „Rusko“ a „USA“ se seznamy měst:
závislý-rozbalovací-seznam-v-excelu

Klikněte na „OK“
V buňce „D2“ vytvořte rozevírací seznam pro výběr zemí „Rusko“ nebo „USA“. Vytvoříme tedy první rozevírací seznam, ve kterém si uživatel může vybrat jednu ze dvou zemí.

Nyní vytvořte závislý rozevírací seznam:
Vyberte buňku E2 (nebo jakoukoli jinou buňku, ve které chcete vytvořit závislý rozevírací seznam);
Klikněte na záložku „Data“ => „Kontrola dat“;
Ve vyskakovacím okně „Ověřit vstupní hodnoty“ na kartě „Parametry“ v datovém typu vyberte „Seznam“:
Ověřování vstupních hodnot v Excelu

Klikněte na „OK“

Pokud nyní v prvním rozevíracím seznamu vyberete zemi „Rusko“, zobrazí se ve druhém rozevíracím seznamu pouze města, která do této země patří. To je také případ, kdy z prvního rozevíracího seznamu vyberete „USA“.

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

Na novém listu zadejte data, která chcete zobrazit v rozevíracím seznamu. Je žádoucí, aby položky seznamu byly obsaženy v excelové tabulce. Pokud tomu tak není, můžete seznam rychle převést na tabulku výběrem libovolné buňky v rozsahu a stisknutím tlačítka CTRL+T.



Poznámky:

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

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

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

   Na kartě Možnosti v terénu Typ dat vyberte položku 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škrtněte políčko Seznam platných hodnot

   Otevřete kartu Zpráva k zadání.

   
   

2. Otevřete kartu Chybová zpráva.

   
   

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

Práce s rozevíracím seznamem

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

Stahování příkladů

Doporučujeme stáhnout si vzorovou knihu s několika příklady ověření dat, podobně jako v 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

Data můžete zadávat rychleji a přesněji omezením hodnot 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 později sloužit jako zdroj pro rozevírací seznam dat. Pokud je seznam malý, můžete na něj snadno odkazovat a zadávat položky přímo do kontroly dat.

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ě; 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, neproběhnou v něm žádné změny, 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 rozevírací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. souhlas.

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).

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

Vícená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.

Přeji hezký den!

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.

Chcete-li tedy vytvořit rozevírací seznam, 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", tlačítko" Ověření dat"

2. Vyberte " Typ dat" -"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 ověření údajů 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 vkládá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í údajů 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 ODSTRANÍ KONTROLNÍ A DROPPING SEZNAM DAT Z BUŇKY, do které byla vložena dříve zkopírovaná hodnota. Tomu se nelze vyhnout pomocí standardních nástrojů Excelu.