Jak vytvořit rozevírací seznam v aplikaci Excel (jediný průvodce, který potřebujete)

Rozevírací seznam je skvělý způsob, jak dát uživateli možnost vybrat si z předem definovaného seznamu.

Lze jej použít, když chcete, aby uživatel vyplnil formulář, nebo při vytváření interaktivních panelů aplikace Excel.

Rozbalovací seznamy jsou na webech/aplikacích celkem běžné a jsou pro uživatele velmi intuitivní.

Podívejte se na video - Vytvoření rozevíracího seznamu v aplikaci Excel

V tomto tutoriálu se naučíte, jak vytvořit rozevírací seznam v Excelu (provedení vám zabere jen několik sekund) spolu se všemi úžasnými věcmi, které s ním můžete dělat.

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

V této části se dozvíte přesné kroky k vytvoření rozevíracího seznamu aplikace Excel:

1. Použití dat z buněk.
2. Ruční zadávání dat.
3. Pomocí vzorce OFFSET.

#1 Používání dat z buněk

Řekněme, že máte seznam položek, jak je uvedeno níže:

Zde jsou kroky k vytvoření rozevíracího seznamu aplikace Excel:

1. Vyberte buňku, ve které chcete vytvořit rozevírací seznam.
2. Přejděte na Data -> Datové nástroje -> Ověření dat.
3. V dialogovém okně Ověření dat na kartě Nastavení vyberte Seznam jako ověřovací kritéria.
   • Jakmile vyberete Seznam, zobrazí se zdrojové pole.
4. Do pole zdroj zadejte = $ A $ 2: $ A $ 6, nebo jednoduše klikněte do pole Zdroj a vyberte buňky pomocí myši a klikněte na OK. Tím se do buňky C2 vloží rozevírací seznam.
   • Ujistěte se, že je zaškrtnuta možnost Rozbalovací nabídka v buňce (což je ve výchozím nastavení zaškrtnuto). Pokud není tato možnost zaškrtnuta, buňka nezobrazuje rozevírací seznam, hodnoty však můžete zadat ručně do seznamu.

Poznámka: Pokud chcete vytvořit rozevírací seznamy ve více buňkách najednou, vyberte všechny buňky, kde je chcete vytvořit, a poté postupujte podle výše uvedených kroků. Zajistěte, aby odkazy na buňky byly absolutní (například $ A $ 2) a nikoli relativní (například A2 nebo A $ 2 nebo $ A2).

#2 Ručním zadáním údajů

Ve výše uvedeném příkladu se odkazy na buňky používají v poli Zdroj. Položky můžete také přidávat přímo zadáním ručně do pole zdroje.

Řekněme například, že chcete v rozevíracím seznamu v buňce zobrazit dvě možnosti, Ano a Ne. Zde je návod, jak jej můžete přímo zadat do pole zdroje ověření dat:

• Vyberte buňku, ve které chcete vytvořit rozevírací seznam (v tomto případě buňka C2).
• Přejděte na Data -> Datové nástroje -> Ověření dat.
• V dialogovém okně Ověření dat na kartě Nastavení vyberte Seznam jako ověřovací kritéria.
  • Jakmile vyberete Seznam, zobrazí se zdrojové pole.
• Do pole zdroje zadejte Ano, Ne
  • Ujistěte se, že je zaškrtnuta možnost Rozbalovací nabídka v buňce.
• Klikněte na OK.

Tím se ve vybrané buňce vytvoří rozevírací seznam. Všechny položky uvedené ve zdrojovém poli oddělené čárkou jsou v rozevíracím seznamu uvedeny na různých řádcích.

Všechny položky zadané ve zdrojovém poli oddělené čárkou jsou v rozevíracím seznamu zobrazeny v různých řádcích.

Poznámka: Pokud chcete vytvořit rozevírací seznamy ve více buňkách najednou, vyberte všechny buňky, kde je chcete vytvořit, a poté postupujte podle výše uvedených kroků.

#3 Používání vzorců aplikace Excel

Kromě výběru z buněk a ručního zadávání dat můžete také použít vzorec ve zdrojovém poli k vytvoření rozevíracího seznamu aplikace Excel.

K vytvoření rozevíracího seznamu v aplikaci Excel lze použít libovolný vzorec, který vrací seznam hodnot.

Předpokládejme například, že máte sadu dat, jak je uvedeno níže:

Zde jsou kroky k vytvoření rozevíracího seznamu aplikace Excel pomocí funkce OFFSET:

• Vyberte buňku, ve které chcete vytvořit rozevírací seznam (v tomto případě buňka C2).
• Přejděte na Data -> Datové nástroje -> Ověření dat.
• V dialogovém okně Ověření dat na kartě Nastavení vyberte Seznam jako ověřovací kritéria.
  • Jakmile vyberete Seznam, zobrazí se zdrojové pole.
• Do pole Zdroj zadejte následující vzorec: = OFFSET ($ A $ 2,0,0,5)
  • Ujistěte se, že je zaškrtnuta možnost Rozevírací nabídka v buňce.
• Klikněte na OK.

Tím se vytvoří rozevírací seznam se seznamem všech názvů ovoce (jak je uvedeno níže).

Poznámka: Pokud chcete vytvořit rozevírací seznam ve více buňkách najednou, vyberte všechny buňky, kde je chcete vytvořit, a poté postupujte podle výše uvedených kroků. Zajistěte, aby odkazy na buňky byly absolutní (například $ A $ 2) a nikoli relativní (například A2 nebo A $ 2 nebo $ A2).

Jak tento vzorec funguje?

Ve výše uvedeném případě jsme k vytvoření rozevíracího seznamu použili funkci OFFSET. Vrátí seznam položek z ra

Vrátí seznam položek z rozsahu A2: A6.

Zde je syntaxe funkce OFFSET: = OFFSET (reference, řádky, sloupce, [výška], [šířka])

Trvá pět argumentů, kde jsme specifikovali odkaz jako A2 (počáteční bod seznamu). Řádky/sloupce jsou zadány jako 0, protože nechceme přesunout referenční buňku. Výška je uvedena jako 5, protože v seznamu je pět prvků.

Když teď použijete tento vzorec, vrátí pole, které má seznam pěti plodů v A2: A6. Všimněte si, že pokud zadáte vzorec do buňky, vyberte jej a stiskněte F9, uvidíte, že vrátí řadu názvů ovoce.

Vytvoření dynamického rozevíracího seznamu v aplikaci Excel (pomocí OFFSET)

Výše uvedenou techniku ​​použití vzorce k vytvoření rozevíracího seznamu lze rozšířit také o dynamický rozevírací seznam. Pokud použijete funkci OFFSET, jak je uvedeno výše, i když do seznamu přidáte více položek, rozevírací seznam se neaktualizuje automaticky. Budete je muset ručně aktualizovat při každé změně seznamu.

Zde je způsob, jak to udělat dynamickým (a nejde jen o drobné vylepšení vzorce):

• Vyberte buňku, ve které chcete vytvořit rozevírací seznam (v tomto případě buňka C2).
• Přejděte na Data -> Datové nástroje -> Ověření dat.
• V dialogovém okně Ověření dat na kartě Nastavení vyberte Seznam jako ověřovací kritéria. Jakmile vyberete Seznam, zobrazí se zdrojové pole.
• Do pole zdroje zadejte následující vzorec: = OFFSET ($ A $ 2,0,0, COUNTIF ($ A $ 2: $ A $ 100, ””))
• Ujistěte se, že je zaškrtnuta možnost Rozevírací nabídka v buňce.
• Klikněte na OK.

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

Funkce COUNTIF počítá neprázdné buňky v rozsahu A2: A100. Funkce OFFSET se tedy sama upraví tak, aby zahrnovala všechny neprázdné buňky.

Poznámka:

• Aby to fungovalo, NESMÍ být mezi vyplněnými buňkami žádné prázdné buňky.
• Pokud chcete vytvořit rozevírací seznam ve více buňkách najednou, vyberte všechny buňky, kde je chcete vytvořit, a poté postupujte podle výše uvedených kroků. Zajistěte, aby odkazy na buňky byly absolutní (například $ A $ 2) a nikoli relativní (například A2 nebo A $ 2 nebo $ A2).

Zkopírujte rozbalovací seznamy v aplikaci Excel

Buňky s ověřením dat můžete zkopírovat do jiných buněk a zkopíruje se také ověření dat.

Pokud například máte v buňce C2 rozevírací seznam a chcete jej použít také na C3: C6, jednoduše zkopírujte buňku C2 a vložte ji do C3: C6. Tím se zkopíruje rozevírací seznam a zpřístupní se v C3: C6 (spolu s rozevíracím seznamem se zkopíruje také formátování).

Pokud chcete pouze zkopírovat rozevírací seznam, nikoli formátování, postupujte takto:

• Zkopírujte buňku s rozevíracím seznamem.
• Vyberte buňky, do kterých chcete zkopírovat rozevírací seznam.
• Přejděte na Domů -> Vložit -> Vložit jinak.
• V dialogovém okně Vložit jinak vyberte Ověření v možnostech Vložit.
• Klikněte na OK.

Tím se zkopíruje pouze rozevírací seznam, nikoli formátování zkopírované buňky.

Pozor při práci s rozevíracím seznamem aplikace Excel

Při práci s rozevíracími seznamy v aplikaci Excel musíte být opatrní.

Když zkopírujete buňku (která neobsahuje rozevírací seznam) přes buňku, která obsahuje rozevírací seznam, rozevírací seznam se ztratí.

Nejhorší na tom je, že Excel nezobrazí žádné upozornění nebo výzvu, aby uživateli oznámil, že rozevírací seznam bude přepsán.

Jak vybrat všechny buňky, které obsahují rozevírací seznam

Někdy je těžké zjistit, které buňky obsahují rozevírací seznam.

Proto má smysl tyto buňky označit buď tím, že jim dáte výrazné ohraničení nebo barvu pozadí.

Namísto ruční kontroly všech buněk existuje rychlý způsob, jak vybrat všechny buňky, které obsahují rozevírací seznamy (nebo jakékoli pravidlo ověřování dat).

• Přejděte na Domů -> Najít a vybrat -> Přejít na speciální.
• V dialogovém okně Přejít na speciální vyberte Ověření dat
  • Ověření dat má dvě možnosti: Všechny a stejné. Všichni by vybrali všechny buňky, na které je aplikováno pravidlo pro ověření dat. Stejné by vybralo pouze ty buňky, které mají stejné pravidlo ověřování dat jako aktivní buňka.
• Klikněte na OK.

Tím by byly okamžitě vybrány všechny buňky, na které je aplikováno pravidlo pro ověření dat (to zahrnuje i rozevírací seznamy).

Nyní můžete buňky jednoduše naformátovat (dát ohraničení nebo barvu pozadí), aby byly vizuálně viditelné a nekopírovali jste do nich omylem jinou buňku.

Zde je další technika od Jon Acampora, kterou můžete použít k tomu, aby byla ikona rozevírací šipky vždy viditelná. Některé způsoby, jak toho dosáhnout, můžete také vidět v tomto videu od pana Excelu.

Vytvoření závislého / podmíněného rozevíracího seznamu aplikace Excel

Zde je video o tom, jak v aplikaci Excel vytvořit závislý rozevírací seznam.

Pokud dáváte přednost čtení před sledováním videa, pokračujte ve čtení.

Někdy můžete mít více než jeden rozevírací seznam a chcete, aby položky zobrazené ve druhém rozevíracím seznamu byly závislé na tom, co uživatel vybral v prvním rozevíracím seznamu.

Nazývají se závislé nebo podmíněné rozevírací seznamy.

Níže je uveden příklad podmíněného/závislého rozevíracího seznamu:

Ve výše uvedeném příkladu, když položky uvedené v „Rozevíracím seznamu 2“ závisí na výběru provedeném v „Rozevíracím seznamu 1“.

Nyní se podívejme, jak to vytvořit.

Zde jsou kroky k vytvoření závislého / podmíněného rozevíracího seznamu v aplikaci Excel:

• Vyberte buňku, kde chcete první (hlavní) rozevírací seznam.
• Přejděte na Data -> Ověření dat. Otevře se dialogové okno ověření dat.
• V dialogovém okně ověření dat na kartě nastavení vyberte Seznam.
• Do pole Zdroj zadejte rozsah, který obsahuje položky, které se mají zobrazit v prvním rozevíracím seznamu.
• Klikněte na OK. Tím se vytvoří rozevírací seznam 1.
• Vyberte celou sadu dat (v tomto příkladu A1: B6).
• Přejděte na Vzorce -> Definovaná jména -> Vytvořit z výběru (nebo můžete použít klávesovou zkratku Control + Shift + F3).
• V dialogovém okně „Vytvořit pojmenované z výběru“ zaškrtněte možnost Horní řádek a zrušte zaškrtnutí všech ostatních. Tím se vytvoří 2 rozsahy názvů („Ovoce“ a „Zelenina“). Ovoce pojmenované rozsah odkazuje na veškeré ovoce v seznamu a Zelenina pojmenovaný rozsah odkazuje na veškerou zeleninu v seznamu.
• Klikněte na OK.
• Vyberte buňku, do které chcete rozevírací seznam Závislé/Podmíněné (v tomto případě E3).
• Přejděte na Data -> Ověření dat.
• V dialogovém okně Ověření dat na kartě nastavení zkontrolujte, zda je vybrána možnost Seznam.
• Do pole Zdroj zadejte vzorec = NEPŘÍMÝ (D3). Zde D3 je buňka, která obsahuje hlavní rozevírací seznam.
• Klikněte na OK.

Nyní, když provedete výběr v rozevíracím seznamu 1, možnosti uvedené v rozevíracím seznamu 2 se automaticky aktualizují.

Stáhněte si ukázkový soubor

Jak to funguje? - Podmíněný rozevírací seznam (v buňce E3) odkazuje na = NEPŘÍMÝ (D3). To znamená, že když v buňce D3 vyberete „Ovoce“, rozevírací seznam v E3 bude odkazovat na pojmenovaný rozsah „Ovoce“ (prostřednictvím funkce NEPŘÍMO) a zobrazí tedy všechny položky v této kategorii.

Důležitá poznámka při práci s podmíněnými rozevíracími seznamy v aplikaci Excel:

• Když provedete výběr a poté změníte rozevírací seznam rodičů, závislé rozevírací seznam se nezmění, a bude tedy špatným zadáním. Pokud například jako zemi vyberete USA a poté jako stát zvolíte Floridu a poté se vrátíte zpět a změníte zemi na Indii, zůstane stát jako Florida. Zde je skvělý návod Debra na vymazání závislých (podmíněných) rozevíracích seznamů v aplikaci Excel při změně výběru.
• Pokud je hlavní kategorií více než jedno slovo (například „sezónní ovoce“ místo „ovoce“), pak musíte místo jednoduchá NEPŘÍMÁ funkce zobrazená výše. Důvodem je to, že aplikace Excel nepovoluje mezery v pojmenovaných rozsazích. Když tedy vytvoříte pojmenovaný rozsah pomocí více než jednoho slova, Excel mezi slova automaticky vloží podtržítko. Pojmenovaná řada „Sezónní ovoce“ by tedy byla „Sezónní ovoce“. Použitím funkce SUBSTITUTE ve funkci INDIRECT zajistíte, že mezery jsou převedeny na podtržítka.