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

Rozbalovací seznam je neuvěřitelně užitečný nástroj, který vám může pomoci zpříjemnit práci s informacemi. Umožňuje obsahovat v buňce několik hodnot najednou, se kterými lze pracovat jako s ostatními. Chcete-li vybrat ten, který potřebujete, stačí kliknout na ikonu šipky, po které se zobrazí seznam hodnot uXNUMXbuXNUMXbis. Po výběru konkrétního se jím buňka automaticky naplní a na základě něj se přepočítají vzorce.

Excel poskytuje mnoho různých metod pro generování rozbalovací nabídky a navíc umožňuje jejich flexibilní přizpůsobení. Pojďme analyzovat tyto metody podrobněji.

Proces vytváření seznamu

Chcete-li vygenerovat vyskakovací nabídku, klikněte na položky nabídky podél cesty „Data“ – „Ověření dat“. Otevře se dialogové okno, kde musíte najít záložku „Parametry“ a kliknout na ni, pokud již nebyla otevřena. Má spoustu nastavení, ale pro nás je důležitá položka „Data Type“. Ze všech významů je „Seznam“ ten správný.

Počet metod, kterými jsou informace zadávány do vyskakovacího seznamu, je poměrně velký.

1. Nezávislé označení prvků seznamu oddělených středníkem v poli „Zdroj“ umístěném na stejné záložce stejného dialogového okna.
   

   

2. Předběžná indikace hodnot. Pole Zdroj obsahuje rozsah, kde jsou k dispozici požadované informace.
   

   

3. Určení pojmenovaného rozsahu. Metoda, která opakuje předchozí, ale je potřeba pouze předběžně pojmenovat rozsah.
   

   

Každá z těchto metod přinese požadovaný výsledek. Podívejme se na metody pro generování rozevíracích seznamů v reálných situacích.

Na základě údajů ze seznamu

Řekněme, že máme tabulku popisující druhy různých druhů ovoce.

Chcete-li vytvořit seznam v rozevírací nabídce na základě této sady informací, musíte provést následující:

1. Vyberte buňku vyhrazenou pro budoucí seznam.
2. Najděte na pásu karet kartu Data. Zde klikneme na „Ověřit data“.
   

   

3. Najděte položku „Data Type“ a přepněte hodnotu na „List“.
   

   

4. Do pole označujícího možnost „Zdroj“ zadejte požadovaný rozsah. Upozorňujeme, že musí být uvedeny absolutní odkazy, aby se při kopírování seznamu informace neposouvaly.
   

Navíc existuje funkce pro generování seznamů najednou ve více buňkách. Abyste toho dosáhli, měli byste je všechny vybrat a provést stejné kroky, jaké byly popsány výše. Opět se musíte ujistit, že jsou napsány absolutní odkazy. Pokud adresa nemá vedle názvů sloupců a řádků znak dolaru, musíte je přidat stisknutím klávesy F4, dokud nebude znak $ vedle názvů sloupců a řádků.

S manuálním záznamem dat

Ve výše uvedené situaci byl seznam napsán zvýrazněním požadovaného rozsahu. Jedná se o pohodlný způsob, ale někdy je nutné data zaznamenat ručně. To umožní vyhnout se duplicitě informací v sešitu.

Předpokládejme, že stojíme před úkolem vytvořit seznam obsahující dvě možné volby: ano a ne. Ke splnění úkolu je nutné:

1. Klikněte na buňku pro seznam.
2. Otevřete „Data“ a najděte sekci „Kontrola dat“, která je nám známá.
   

   

3. Opět vyberte typ „Seznam“.
   

   

4. Zde musíte zadat „Ano; Ne“ jako zdroj. Vidíme, že informace se zadávají ručně pomocí středníku pro výčet.

Po kliknutí na OK máme následující výsledek.

Dále program automaticky vytvoří rozevírací nabídku v příslušné buňce. Všechny informace, které uživatel zadal jako položky v rozbalovacím seznamu. Pravidla pro vytváření seznamu ve více buňkách jsou podobná jako u předchozích, s jedinou výjimkou, že informace musíte zadat ručně středníkem.

Vytvoření rozevíracího seznamu pomocí funkce OFFSET

Kromě klasické metody je možné použít funkci LIKVIDACEpro generování rozbalovacích nabídek.

Otevřeme list.

Chcete-li použít funkci pro rozevírací seznam, musíte provést následující:

1. Vyberte buňku zájmu, kam chcete umístit budoucí seznam.
2. Postupně otevřete záložku „Data“ a okno „Ověření dat“.
   

   

3. Nastavte „Seznam“. To se provádí stejným způsobem jako v předchozích příkladech. Nakonec se použije následující vzorec: =POSUN(A$2$;0;0;5). Zadáme jej tam, kde jsou specifikovány buňky, které budou použity jako argument.

Poté program vytvoří menu se seznamem ovoce.

Syntaxe pro toto je:

=POSUN(odkaz,odsazení_řádku,odsazení_sloupce,[výška],[šířka])

Vidíme, že tato funkce má 5 argumentů. Nejprve je dána adresa první buňky, která má být posunuta. Další dva argumenty určují, kolik řádků a sloupců se má odsadit. Když už mluvíme o nás, argument Výška je 5, protože představuje výšku seznamu. 

Rozbalovací seznam v Excelu s náhradou dat (+ pomocí funkce OFFSET)

V daném případě LIKVIDACE umožňuje vytvořit vyskakovací nabídku umístěnou v pevném rozsahu. Nevýhodou této metody je, že po přidání položky budete muset vzorec upravit sami.

Chcete-li vytvořit dynamický seznam s podporou zadávání nových informací, musíte:

1. Vyberte požadovanou buňku.
2. Rozbalte záložku „Data“ a klikněte na „Ověření dat“.
3. V okně, které se otevře, znovu vyberte položku „Seznam“ a jako zdroj dat zadejte následující vzorec: =СМЕЩ(A$2$;0;0;СЧЕТЕСЛИ($A$2:$A$100;”<>”))
4. Klepněte na tlačítko OK.

Toto obsahuje funkci COUNTIF, abychom okamžitě určili, kolik buněk je zaplněno (i když má mnohem větší počet použití, jen to sem píšeme pro konkrétní účel).

Aby vzorec fungoval normálně, je nutné vysledovat, zda jsou na cestě vzorce prázdné buňky. Neměli by být.

Rozbalovací seznam s daty z jiného listu nebo souboru Excel

Klasická metoda nefunguje, pokud potřebujete získat informace z jiného dokumentu nebo dokonce listu obsaženého ve stejném souboru. K tomu slouží funkce NEPŘÍMÝ, který umožňuje zadat ve správném formátu odkaz na buňku umístěnou v jiném listu nebo obecně – soubor. Musíte provést následující:

1. Aktivujeme buňku, kam umístíme seznam.
2. Otevíráme okno, které už známe. Na stejném místě, kde jsme dříve uváděli zdroje pro jiné rozsahy, je ve formátu uveden vzorec =NEPŘÍMÉ(“[List1.xlsx]List1!$A$1:$A$9”). Přirozeně, místo List1 a List1, můžete vložit název knihy a listu, resp. 

Pozornost! Název souboru je uveden v hranatých závorkách. V tomto případě aplikace Excel nebude moci použít soubor, který je aktuálně uzavřen, jako zdroj informací.

Je třeba také poznamenat, že samotný název souboru má smysl pouze v případě, že se požadovaný dokument nachází ve stejné složce jako ta, kam bude seznam vložen. Pokud ne, musíte uvést celou adresu tohoto dokumentu.

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

Závislý seznam je takový, jehož obsah je ovlivněn volbou uživatele v jiném seznamu. Předpokládejme, že máme před sebou otevřený stůl, který obsahuje tři rozsahy, z nichž každý dostal jméno.

Chcete-li vygenerovat seznamy, jejichž výsledek je ovlivněn možností vybranou v jiném seznamu, musíte postupovat podle těchto kroků.

1. Vytvořte 1. seznam s názvy rozsahů.
   

   

2. Ve vstupním bodě zdroje jsou požadované indikátory zvýrazněny jeden po druhém.
   

   

3. Vytvořte 2. seznam v závislosti na typu rostliny, kterou si osoba vybrala. Alternativně, pokud zadáte stromy v prvním seznamu, pak informace v druhém seznamu budou „dub, habr, kaštan“ a další. Vzorec je nutné zapsat v místě vstupu zdroje dat =NEPŘÍMÉ(E3). E3 – buňka obsahující název rozsahu 1.=NEPŘÍMÝ(E3). E3 – buňka s názvem seznamu 1.

Nyní je vše připraveno.

Jak vybrat více hodnot z rozevíracího seznamu?

Někdy není možné upřednostnit pouze jednu hodnotu, proto je třeba vybrat více hodnot. Poté je potřeba do kódu stránky přidat makro. Pomocí kombinace kláves Alt + F11 otevřete Editor jazyka Visual Basic. A tam se vloží kód.

Private Sub Worksheet_Change (ByVal Target As Range)

    On Error Resume Next

    If Not Intersect(Target, Range(«Е2:Е9»)) Is Nothing And Target.Cells.Count = 1 Then

        Application.EnableEvents = False

        Pokud délka (Target.Offset (0, 1)) = 0 Pak

            Target.Offset (0, 1) = Cíl

        jiný

            Target.End (xlToRight) .Offset (0, 1) = Cíl

        End If

        Target.ClearContents

        Application.EnableEvents = True

    End If

End Sub 

Aby se níže zobrazil obsah buněk, vložíme do editoru následující kód.

Private Sub Worksheet_Change (ByVal Target As Range)

    On Error Resume Next

    If Not Intersect(Target, Range(«Н2:К2»)) Is Nothing And Target.Cells.Count = 1 Then

        Application.EnableEvents = False

        Pokud délka (Target.Offset (1, 0)) = 0 Pak

            Target.Offset (1, 0) = Cíl

        jiný

            Target.End (xlDown) .Offset (1, 0) = Cíl

        End If

        Target.ClearContents

        Application.EnableEvents = True

    End If

End Sub

A nakonec se tento kód používá k zápisu do jedné buňky.

Private Sub Worksheet_Change (ByVal Target As Range)

    On Error Resume Next

    If Not Intersect(Target, Range(«C2:C5»)) Is Nothing And Target.Cells.Count = 1 Then

        Application.EnableEvents = False

        newVal = Cíl

        Aplikace. Zpět

        oldval = Cíl

        If Len (oldval) <> 0 And oldval <> newVal Then

            Target = Target & «,» & newVal

        jiný

            Cíl = newVal

        End If

        Pokud Len (newVal) = 0, potom Target.ClearContents

        Application.EnableEvents = True

    End If

End Sub

Rozsahy jsou upravitelné.

Jak vytvořit rozevírací seznam s vyhledáváním?

V tomto případě musíte zpočátku použít jiný typ seznamu. Otevře se karta „Vývojář“, po které musíte kliknout nebo klepnout (pokud je obrazovka dotyková) na prvek „Vložit“ – „ActiveX“. Má combo box. Budete vyzváni k nakreslení tohoto seznamu a poté bude přidán do dokumentu.

Dále se konfiguruje pomocí vlastností, kde je rozsah specifikován ve volbě ListFillRange. Buňka, kde se zobrazuje uživatelsky definovaná hodnota, se konfiguruje pomocí možnosti LinkedCell. Dále stačí zapsat první znaky, protože program automaticky navrhne možné hodnoty.

Rozbalovací seznam s automatickým nahrazováním dat

Existuje také funkce, že data jsou automaticky nahrazena po jejich přidání do rozsahu. Je to snadné:

1. Vytvořte sadu buněk pro budoucí seznam. V našem případě se jedná o sadu barev. Vybíráme to.
   

   

2. Dále je třeba jej naformátovat jako tabulku. Musíte kliknout na stejnojmenné tlačítko a vybrat styl tabulky.
   

   

   

Dále musíte tento rozsah potvrdit stisknutím tlačítka „OK“.

Vybereme výslednou tabulku a pojmenujeme ji pomocí vstupního pole umístěného v horní části sloupce A.

To je vše, existuje tabulka a lze ji použít jako základ pro rozevírací seznam, pro který potřebujete:

1. Vyberte buňku, kde se nachází seznam.
2. Otevřete dialogové okno Ověření dat.
   

   

3. Datový typ nastavíme na „Seznam“ a jako hodnoty uvedeme název tabulky pomocí znaménka =.
   

   

   

Vše, buňka je připravena a jsou v ní zobrazeny názvy barev, jak jsme původně potřebovali. Nyní můžete přidat nové pozice jednoduše tak, že je zapíšete do buňky umístěné o něco níže bezprostředně za poslední.

To je výhoda tabulky, že rozsah se automaticky zvětšuje, když jsou přidána nová data. V souladu s tím je to nejpohodlnější způsob přidání seznamu.

Jak zkopírovat rozevírací seznam?

Ke kopírování stačí použít kombinaci kláves Ctrl + C a Ctrl + V. Rozbalovací seznam se tedy zkopíruje spolu s formátováním. Pro odstranění formátování je potřeba použít speciální pastu (v kontextovém menu se tato volba objeví po zkopírování seznamu), kde je nastavena volba „podmínky hodnot“.

Vyberte všechny buňky obsahující rozevírací seznam

Chcete-li provést tento úkol, musíte použít funkci „Vybrat skupinu buněk“ ve skupině „Najít a vybrat“.

Poté se otevře dialogové okno, kde byste měli vybrat položky „All“ a „These same“ v nabídce „Data Validation“. První položka vybere všechny seznamy a druhá vybere pouze ty, které jsou podobné některým.