Vytvoření závislého rozevíracího seznamu v aplikaci Excel (návod krok za krokem)

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

Rozevírací seznam aplikace Excel je užitečnou funkcí při vytváření formulářů pro zadávání dat nebo panelů aplikace Excel.

Zobrazí seznam položek jako rozevírací seznam v buňce a uživatel může provést výběr z rozevíracího seznamu. To může být užitečné, pokud máte seznam názvů, produktů nebo oblastí, které často potřebujete zadat do sady buněk.

Níže je uveden příklad rozevíracího seznamu aplikace Excel:

Ve výše uvedeném příkladu jsem použil položky v A2: A6 k vytvoření rozevíracího seznamu v C3.

Číst: Zde je podrobný návod, jak vytvořit rozevírací seznam aplikace Excel.

Někdy však můžete v aplikaci Excel použít více než jeden rozevírací seznam, takže položky dostupné ve druhém rozevíracím seznamu závisí na výběru provedeném v prvním rozevíracím seznamu.

V aplikaci Excel se jim říká závislé rozevírací seznamy.

Níže je uveden příklad toho, co mám na mysli závislým rozevíracím seznamem v aplikaci Excel:

Můžete vidět, že možnosti v rozevíracím seznamu 2 závisí na výběru provedeném v rozevíracím seznamu 1. Pokud v rozevíracím seznamu 1 vyberu „Ovoce“, zobrazí se názvy plodů, ale pokud v rozevíracím seznamu 1 vyberu zeleninu, pak názvy rostlin se zobrazují v rozevíracím seznamu 2.

V aplikaci Excel se tomu říká podmíněný nebo závislý rozevírací seznam.

Vytvoření závislého rozevíracího seznamu v aplikaci Excel

Zde jsou kroky k vytvoření závislé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: 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. Když například vytvoříte pojmenovaný sortiment pomocí „sezónního ovoce“, bude v backendu pojmenován Season_Fruits. Použitím funkce SUBSTITUTE ve funkci INDIRECT zajistíte, že mezery jsou převedeny na podtržítka.

Automaticky resetovat/vymazat obsah závislého rozevíracího seznamu

Když provedete výběr a poté změníte rozevírací seznam rodičů, závislý rozevírací seznam se nezmění, a proto by byl špatným záznamem.

Pokud například jako kategorii vyberete „Ovoce“ a poté jako položku vyberete Apple a poté se vrátíte zpět a změníte kategorii na „Zelenina“, v závislém rozevíracím seznamu se bude jako položka nadále zobrazovat Apple.

Pomocí VBA můžete zajistit, aby se obsah závislého rozevíracího seznamu resetoval při každé změně hlavního rozevíracího seznamu.

Zde je kód VBA k vymazání obsahu závislého rozevíracího seznamu:

Private Sub Worksheet_Change (ByVal Target As Range) On Error Resume Next If Target.Column = 4 Then If Target.Validation.Type = 3 Then Application.EnableEvents = False Target.Offset (0, 1) .ClearContents End If End If exitHandler: Application.EnableEvents = True Exit Sub End Sub

Kredit za tento kód jde do tohoto kurzu od Debra o vymazání závislých rozevíracích seznamů v aplikaci Excel při změně výběru.

Zde je návod, jak zajistit, aby tento kód fungoval:

• Zkopírujte kód VBA.
• V sešitu Excelu, kde máte závislý rozevírací seznam, přejděte na kartu Vývojář a ve skupině „Kód“ klikněte na Visual Basic (můžete také použít klávesovou zkratku - ALT + F11).
• V okně editoru VB vlevo v průzkumníku projektu uvidíte všechny názvy listů. Poklepejte na ten, který má rozevírací seznam.
• Vložte kód do okna kódu vpravo.
• Zavřete editor VB.

Nyní, kdykoli změníte hlavní rozevírací seznam, spustí se kód VBA a vymaže obsah závislého rozevíracího seznamu (jak je uvedeno níže).

Pokud nejste fanouškem VBA, můžete také použít jednoduchý trik podmíněného formátování, který buňku zvýrazní, kdykoli dojde k nesouladu. To vám může pomoci vizuálně vidět a opravit nesoulad (jak je uvedeno níže).

Zde jsou kroky, které zvýrazní neshody v závislých rozevíracích seznamech:

• Vyberte buňku, která má závislý rozevírací seznam (y).
• Přejděte na Domů -> Podmíněné formátování -> Nové pravidlo.
• V dialogovém okně Nové pravidlo formátování vyberte „Pomocí vzorce určit, které buňky chcete formátovat“.
• Do pole vzorce zadejte následující vzorec: = ISERROR (VLOOKUP (E3, INDEX ($ A $ 2: $ B $ 6,, MATCH (D3, $ A $ 1: $ B $ 1)), 1,0))
• Nastavte formát.
• Klikněte na OK.

Vzorec používá funkci VLOOKUP ke kontrole, zda položka v závislém rozevíracím seznamu je z hlavní kategorie nebo ne. Pokud tomu tak není, vzorec vrátí chybu. Toto používá funkce ISERROR k vrácení hodnoty TRUE, která určuje podmíněné formátování pro zvýraznění buňky.

Také by se vám mohly líbit následující výukové programy pro Excel:

• Extrahujte data na základě výběru z rozevíracího seznamu.
• Vytvoření rozevíracího seznamu s návrhy vyhledávání.
• Vyberte více položek z rozevíracího seznamu.
• Vytvořte více rozevíracích seznamů bez opakování.
• Šetřete čas pomocí formulářů pro zadávání dat v aplikaci Excel.