Ako vytvoriť rozbaľovací zoznam v Exceli

Rozbaľovací zoznam je neuveriteľne užitočný nástroj, ktorý vám môže pomôcť spríjemniť prácu s informáciami. Umožňuje v bunke naraz obsahovať niekoľko hodnôt, s ktorými môžete pracovať ako s inými. Ak chcete vybrať ten, ktorý potrebujete, stačí kliknúť na ikonu šípky, po ktorej sa zobrazí zoznam hodnôt uXNUMXbuXNUMXbis. Po výbere konkrétneho sa ním bunka automaticky naplní a na základe neho sa prepočítajú vzorce.

Excel poskytuje mnoho rôznych metód na generovanie rozbaľovacej ponuky a navyše umožňuje ich flexibilné prispôsobenie. Poďme analyzovať tieto metódy podrobnejšie.

Proces vytvárania zoznamu

Ak chcete vygenerovať kontextové menu, kliknite na položky ponuky pozdĺž cesty „Údaje“ – „Overenie údajov“. Otvorí sa dialógové okno, kde musíte nájsť kartu „Parametre“ a kliknúť na ňu, ak ešte nebola otvorená. Má veľa nastavení, ale dôležitá je pre nás položka „Typ údajov“. Zo všetkých významov je „Zoznam“ ten správny.

Počet metód, ktorými sa informácie zadávajú do rozbaľovacieho zoznamu, je pomerne veľký.

1. Nezávislé označenie prvkov zoznamu oddelených bodkočiarkou v poli „Zdroj“ umiestnenom na tej istej karte toho istého dialógového okna.
   

   

2. Predbežný údaj o hodnotách. Pole Zdroj obsahuje rozsah, v ktorom sú dostupné požadované informácie.
   

   

3. Určenie pomenovaného rozsahu. Metóda, ktorá opakuje predchádzajúcu, ale je potrebné len predbežne pomenovať rozsah.
   

   

Ktorákoľvek z týchto metód prinesie požadovaný výsledok. Pozrime sa na metódy generovania rozbaľovacích zoznamov v reálnych situáciách.

Na základe údajov zo zoznamu

Povedzme, že máme tabuľku popisujúcu druhy rôznych druhov ovocia.

Ak chcete vytvoriť zoznam v rozbaľovacej ponuke na základe tohto súboru informácií, musíte urobiť nasledovné:

1. Vyberte bunku vyhradenú pre budúci zoznam.
2. Na páse s nástrojmi nájdite kartu Údaje. Tam klikneme na „Overiť údaje“.
   

   

3. Nájdite položku „Typ údajov“ a prepnite hodnotu na „Zoznam“.
   

   

4. V poli označujúcom možnosť „Zdroj“ zadajte požadovaný rozsah. Upozorňujeme, že musia byť uvedené absolútne odkazy, aby sa pri kopírovaní zoznamu informácie neposunuli.
   

Okrem toho existuje funkcia na generovanie zoznamov naraz vo viac ako jednej bunke. Aby ste to dosiahli, mali by ste ich všetky vybrať a vykonať rovnaké kroky, ako je popísané vyššie. Opäť sa musíte uistiť, že sú napísané absolútne referencie. Ak adresa nemá vedľa názvov stĺpcov a riadkov znak dolára, musíte ich pridať stláčaním klávesu F4, kým sa vedľa názvov stĺpcov a riadkov nenachádza znak $.

S manuálnym záznamom údajov

Vo vyššie uvedenej situácii bol zoznam napísaný zvýraznením požadovaného rozsahu. Ide o pohodlný spôsob, ale niekedy je potrebné údaje zaznamenať manuálne. To umožní vyhnúť sa duplicite informácií v zošite.

Predpokladajme, že stojíme pred úlohou vytvoriť zoznam obsahujúci dve možnosti: áno a nie. Na splnenie úlohy je potrebné:

1. Kliknite na bunku pre zoznam.
2. Otvorte "Údaje" a nájdite sekciu "Kontrola údajov", ktorú poznáme.
   

   

3. Opäť vyberte typ „Zoznam“.
   

   

4. Tu musíte zadať „Áno; Nie“ ako zdroj. Vidíme, že informácie sa zadávajú manuálne pomocou bodkočiarky na výpočet.

Po kliknutí na OK máme nasledujúci výsledok.

Ďalej program automaticky vytvorí rozbaľovaciu ponuku v príslušnej bunke. Všetky informácie, ktoré používateľ zadal ako položky v rozbaľovacom zozname. Pravidlá pre vytváranie zoznamu vo viacerých bunkách sú podobné ako predchádzajúce, s jedinou výnimkou, že informácie musíte zadať ručne pomocou bodkočiarky.

Vytvorenie rozbaľovacieho zoznamu pomocou funkcie OFFSET

Okrem klasickej metódy je možné použiť funkciu LIKVIDÁCIAna generovanie rozbaľovacích ponúk.

Otvoríme list.

Ak chcete použiť funkciu pre rozbaľovací zoznam, musíte urobiť nasledovné:

1. Vyberte bunku záujmu, do ktorej chcete umiestniť budúci zoznam.
2. Postupne otvorte kartu „Údaje“ a okno „Overenie údajov“.
   

   

3. Nastavte „Zoznam“. Toto sa robí rovnakým spôsobom ako v predchádzajúcich príkladoch. Nakoniec sa použije nasledujúci vzorec: =OFFSET(A$2$;0;0;5). Zadáme ho tam, kde sú špecifikované bunky, ktoré budú použité ako argument.

Potom program vytvorí menu so zoznamom ovocia.

Syntax pre toto je:

=OFFSET(odkaz,odsadenie_riadku,odsadenie_stĺpca,[výška],[šírka])

Vidíme, že táto funkcia má 5 argumentov. Najprv sa zadá adresa prvej bunky, ktorá má byť posunutá. Nasledujúce dva argumenty určujú, koľko riadkov a stĺpcov sa má odsadiť. Keď už hovoríme o nás, argument Výška je 5, pretože predstavuje výšku zoznamu. 

Rozbaľovací zoznam v Exceli s nahradením údajov (+ pomocou funkcie OFFSET)

V danom prípade LIKVIDÁCIA umožňuje vytvoriť kontextové menu umiestnené v pevnom rozsahu. Nevýhodou tohto spôsobu je, že po pridaní položky si budete musieť vzorec upraviť sami.

Ak chcete vytvoriť dynamický zoznam s podporou zadávania nových informácií, musíte:

1. Vyberte bunku, ktorá vás zaujíma.
2. Rozbaľte kartu „Údaje“ a kliknite na „Overenie údajov“.
3. V okne, ktoré sa otvorí, znova vyberte položku „Zoznam“ a ako zdroj údajov zadajte nasledujúci vzorec: =СМЕЩ(A$2$;0;0;СЧЕТЕСЛИ($A$2:$A$100;”<>”))
4. Kliknite na tlačidlo OK.

Toto obsahuje funkciu COUNTIF, aby sa okamžite zistilo, koľko buniek je vyplnených (hoci má oveľa väčší počet použití, len to tu píšeme pre konkrétny účel).

Aby vzorec fungoval normálne, je potrebné sledovať, či sú na ceste vzorca prázdne bunky. Nemali by byť.

Rozbaľovací zoznam s údajmi z iného hárka alebo súboru Excel

Klasická metóda nefunguje, ak potrebujete získať informácie z iného dokumentu alebo dokonca z listu obsiahnutého v tom istom súbore. Na to slúži funkcia NEPRIAME, ktorý umožňuje zadať v správnom formáte odkaz na bunku umiestnenú v inom hárku alebo vo všeobecnosti – súbor. Musíte urobiť nasledovné:

1. Aktivujeme bunku, do ktorej umiestňujeme zoznam.
2. Otvárame okno, ktoré už poznáme. Na rovnakom mieste, kde sme predtým uviedli zdroje pre iné rozsahy, je vo formáte uvedený vzorec =NEPRIAME(“[List1.xlsx]Hárok1!$A$1:$A$9”). Prirodzene, namiesto Zoznam1 a Hárok1 môžete vložiť názvy svojej knihy a listu. 

Pozor! Názov súboru je uvedený v hranatých zátvorkách. V tomto prípade Excel nebude môcť použiť súbor, ktorý je momentálne zatvorený, ako zdroj informácií.

Treba tiež poznamenať, že samotný názov súboru má zmysel iba vtedy, ak sa požadovaný dokument nachádza v rovnakom priečinku ako ten, do ktorého bude vložený zoznam. Ak nie, musíte uviesť úplnú adresu tohto dokumentu.

Vytváranie závislých rozbaľovacích zoznamov

Závislý zoznam je taký, ktorého obsah je ovplyvnený voľbou používateľa v inom zozname. Predpokladajme, že máme pred sebou otvorený stôl, ktorý obsahuje tri rozsahy, z ktorých každý dostal meno.

Ak chcete vygenerovať zoznamy, ktorých výsledok je ovplyvnený možnosťou vybratou v inom zozname, musíte postupovať podľa týchto krokov.

1. Vytvorte 1. zoznam s názvami rozsahov.
   

   

2. Na vstupnom bode zdroja sú požadované indikátory zvýraznené jeden po druhom.
   

   

3. Vytvorte 2. zoznam v závislosti od typu rastliny, ktorú si osoba vybrala. Prípadne, ak zadáte stromy v prvom zozname, potom informácie v druhom zozname budú „dub, hrab, gaštan“ a ďalšie. Vzorec je potrebné zapísať na miesto vstupu zdroja údajov =PRIAME (E3). E3 – bunka obsahujúca názov rozsahu 1.=NEPRIAME(E3). E3 – bunka s názvom zoznamu 1.

Teraz je všetko pripravené.

Ako vybrať viacero hodnôt z rozbaľovacieho zoznamu?

Niekedy nie je možné uprednostniť iba jednu hodnotu, preto je potrebné vybrať viacero. Potom musíte do kódu stránky pridať makro. Pomocou kombinácie klávesov Alt + F11 otvoríte editor jazyka Visual Basic. A tam je vložený kód.

Súkromný podriadený pracovný hárok_zmena (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

        Ak Len (Target.Offset (0, 1)) = 0 Potom

            Target.Offset (0, 1) = Target

        inak

            Target.End (xlToRight) .Offset (0, 1) = Target

        End If

        Target.ClearContents

        Aplikácia EnableEvents = True

    End If

End Sub 

Aby sa obsah buniek zobrazil nižšie, vložíme do editora nasledujúci kód.

Súkromný podriadený pracovný hárok_zmena (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

        Ak Len (Target.Offset (1, 0)) = 0 Potom

            Target.Offset (1, 0) = Target

        inak

            Target.End (xlDown) .Offset (1, 0) = Target

        End If

        Target.ClearContents

        Aplikácia EnableEvents = True

    End If

End Sub

A nakoniec sa tento kód používa na zápis do jednej bunky.

Súkromný podriadený pracovný hárok_zmena (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 = Cieľ

        Aplikácia.Späť

        oldval = Cieľ

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

            Cieľ = Cieľ & «,» & newVal

        inak

            Cieľ = newVal

        End If

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

        Aplikácia EnableEvents = True

    End If

End Sub

Rozsahy sú upraviteľné.

Ako vytvoriť rozbaľovací zoznam s vyhľadávaním?

V tomto prípade musíte najprv použiť iný typ zoznamu. Otvorí sa karta „Vývojár“, po ktorej musíte kliknúť alebo klepnúť (ak je obrazovka dotyková) na prvok „Vložiť“ – „ActiveX“. Má combo box. Budete vyzvaní, aby ste tento zoznam nakreslili a potom sa pridá do dokumentu.

Ďalej sa konfiguruje cez vlastnosti, kde je rozsah špecifikovaný vo voľbe ListFillRange. Bunka, v ktorej sa zobrazuje používateľom definovaná hodnota, sa konfiguruje pomocou možnosti LinkedCell. Ďalej si stačí zapísať prvé znaky, pretože program automaticky navrhne možné hodnoty.

Rozbaľovací zoznam s automatickým nahrádzaním údajov

Existuje aj funkcia, že údaje sa po pridaní do rozsahu automaticky nahradia. Je to jednoduché:

1. Vytvorte skupinu buniek pre budúci zoznam. V našom prípade ide o sadu farieb. Vyberáme to.
   

   

2. Ďalej je potrebné ju naformátovať ako tabuľku. Musíte kliknúť na tlačidlo s rovnakým názvom a vybrať štýl tabuľky.
   

   

   

Ďalej musíte tento rozsah potvrdiť stlačením tlačidla „OK“.

Vyberieme výslednú tabuľku a pomenujeme ju cez vstupné pole umiestnené v hornej časti stĺpca A.

To je všetko, existuje tabuľka a možno ju použiť ako základ pre rozbaľovací zoznam, pre ktorý potrebujete:

1. Vyberte bunku, v ktorej sa nachádza zoznam.
2. Otvorte dialógové okno Overenie údajov.
   

   

3. Dátový typ nastavíme na „Zoznam“ a ako hodnoty dáme názov tabuľky pomocou znamienka =.
   

   

   

Všetko, bunka je pripravená a v nej sú zobrazené názvy farieb, ako sme pôvodne potrebovali. Teraz môžete pridať nové pozície jednoducho tak, že ich napíšete do bunky umiestnenej o niečo nižšie hneď za poslednou.

To je výhoda tabuľky, že rozsah sa automaticky zväčšuje pri pridávaní nových údajov. Preto je to najpohodlnejší spôsob pridania zoznamu.

Ako skopírovať rozbaľovací zoznam?

Na kopírovanie stačí použiť kombináciu kláves Ctrl + C a Ctrl + V. Rozbaľovací zoznam sa teda skopíruje spolu s formátovaním. Na odstránenie formátovania je potrebné použiť špeciálne prilepenie (v kontextovom menu sa táto možnosť zobrazí po skopírovaní zoznamu), kde je nastavená možnosť „podmienky hodnôt“.

Vyberte všetky bunky obsahujúce rozbaľovací zoznam

Na splnenie tejto úlohy musíte použiť funkciu „Vybrať skupinu buniek“ v skupine „Nájsť a vybrať“.

Potom sa otvorí dialógové okno, kde by ste mali vybrať položky „All“ a „These same“ v menu „Data Validation“. Prvá položka vyberie všetky zoznamy a druhá vyberie iba tie, ktoré sú podobné niektorým.