Pozrite si video - Vytvorenie závislého rozbaľovacieho zoznamu v programe Excel
Rozbaľovací zoznam programu Excel je užitočná funkcia pri vytváraní formulárov na zadávanie údajov alebo informačných panelov programu Excel.
Zobrazuje zoznam položiek ako rozbaľovací zoznam v bunke a používateľ môže z rozbaľovacieho zoznamu vybrať. To môže byť užitočné, ak máte zoznam názvov, produktov alebo oblastí, ktoré často potrebujete zadať do sady buniek.
Nasleduje príklad rozbaľovacieho zoznamu programu Excel:
Vo vyššie uvedenom príklade som použil položky v A2: A6 na vytvorenie rozbaľovacej ponuky v C3.
Čítať: Tu je podrobný návod, ako vytvoriť rozbaľovací zoznam programu Excel.
Niekedy však môžete chcieť použiť viac ako jeden rozbaľovací zoznam v programe Excel, takže položky dostupné v druhom rozbaľovacom zozname závisia od výberu vykonaného v prvom rozbaľovacom zozname.
V programe Excel sa to nazýva závislé rozbaľovacie zoznamy.
Nasleduje príklad toho, čo mám na mysli závislým rozbaľovacím zoznamom v programe Excel:
Môžete vidieť, že možnosti v rozbaľovacom zozname 2 závisia od výberu vykonaného v rozbaľovacom zozname 1. Ak v rozbaľovacom zozname 1 vyberiem „Ovocie“, zobrazia sa mi názvy plodov, ale ak v rozbaľovacom zozname 1 vyberiem zeleninu, potom V rozbaľovacom zozname 2 sú uvedené názvy zeleniny.
V programe Excel sa to nazýva podmienený alebo závislý rozbaľovací zoznam.
Vytvorenie závislého rozbaľovacieho zoznamu v programe Excel
Tu je postup, ako vytvoriť závislý rozbaľovací zoznam v programe Excel:
- Vyberte bunku, v ktorej chcete prvý (hlavný) rozbaľovací zoznam.
- Prejdite na položku Údaje -> Overenie údajov. Otvorí sa dialógové okno na overenie údajov.
- V dialógovom okne overenia údajov na karte nastavení vyberte položku Zoznam.
- Do poľa Zdroj zadajte rozsah, ktorý obsahuje položky, ktoré sa majú zobraziť v prvom rozbaľovacom zozname.
- Kliknite na tlačidlo OK. Tým sa vytvorí rozbaľovací zoznam 1.
- Vyberte celý súbor údajov (v tomto prípade A1: B6).
- Prejdite na Vzorce -> Definované mená -> Vytvoriť z výberu (alebo môžete použiť klávesovú skratku Ctrl + Shift + F3).
- V dialógovom okne „Vytvoriť pomenované z výberu“ začiarknite možnosť Horný riadok a zrušte začiarknutie všetkých ostatných. Týmto sa vytvoria 2 rozsahy mien („Ovocie“ a „Zelenina“). Ovocie s názvom rozsah odkazuje na všetky druhy ovocia v zozname a zelenina s názvom rozsah označuje všetku zeleninu v zozname.
- Kliknite na tlačidlo OK.
- Vyberte bunku, kde chcete rozbaľovací zoznam Závislé/Podmienené (v tomto prípade E3).
- Prejdite na položku Údaje -> Overenie údajov.
- V dialógovom okne Overenie údajov na karte nastavení skontrolujte, či je vybratá možnosť Zoznam.
- Do poľa Zdroj zadajte vzorec = NEPRIAMY (D3). Tu je D3 bunka, ktorá obsahuje hlavnú rozbaľovaciu ponuku.
- Kliknite na tlačidlo OK.
Teraz, keď vyberiete položku v rozbaľovacom zozname 1, možnosti uvedené v rozbaľovacom zozname 2 sa automaticky aktualizujú.
Stiahnite si ukážkový súbor
Ako to funguje? - Podmienený rozbaľovací zoznam (v bunke E3) odkazuje na = NEPRIAMY (D3). To znamená, že keď v bunke D3 vyberiete „Ovocie“, rozbaľovací zoznam v E3 bude odkazovať na pomenovaný rozsah „Ovocie“ (prostredníctvom funkcie NEPRIAMY) a zobrazí sa zoznam všetkých položiek v tejto kategórii.
Dôležitá poznámka: Ak je hlavnou kategóriou viac ako jedno slovo (napríklad „sezónne ovocie“ namiesto „ovocie“), potom musíte namiesto jednoduchá NEPRIAMA funkcia zobrazená vyššie.
- Dôvodom je to, že Excel nepovoľuje medzery v pomenovaných rozsahoch. Keď teda vytvoríte pomenovaný rozsah pomocou viac ako jedného slova, Excel automaticky vloží medzi slová podčiarkovník. Keď napríklad vytvoríte pomenovaný rozsah pomocou položky „Sezónne ovocie“, bude mať v backende názov Season_Fruits. Použitím funkcie SUBSTITUTE v rámci funkcie INDIRECT sa zaistia medzery sú premenené na podčiarkovníky.
Automaticky resetovať/vymazať obsah závislého rozbaľovacieho zoznamu
Keď vykonáte výber a potom zmeníte rozbaľovaciu ponuku rodiča, závislý rozbaľovací zoznam sa nezmení, a preto by bol nesprávnym záznamom.
Ak napríklad vyberiete ako kategóriu „Ovocie“ a potom vyberiete položku Apple, potom sa vrátite späť a zmeníte kategóriu na „Zelenina“, v závislom rozbaľovacom zozname sa bude ako položka naďalej zobrazovať Apple.
VBA môžete použiť na zabezpečenie toho, aby sa obsah závislého rozbaľovacieho zoznamu resetoval vždy, keď sa zmení hlavný rozbaľovací zoznam.
Tu je kód VBA na vymazanie obsahu závislého rozbaľovacieho zoznamu:
Súkromný čiastkový pracovný list_Zmeniť (ByVal cieľ ako rozsah) Pri chybe Pokračovať ďalej Ak Target.Column = 4 Potom If Target.Validation.Type = 3 Potom 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 putuje do tohto tutoriálu spoločnosti Debra o vymazaní závislých rozbaľovacích zoznamov v programe Excel pri zmene výberu.
Tu je návod, ako zabezpečiť, aby tento kód fungoval:
- Skopírujte kód VBA.
- V zošite programu Excel, kde máte rozbaľovací zoznam závislých, prejdite na kartu Vývojár a v skupine „Kód“ kliknite na položku Visual Basic (môžete tiež použiť klávesovú skratku - ALT + F11).
- V okne editora VB, vľavo v prieskumníkovi projektu, uvidíte všetky názvy pracovných hárkov. Dvakrát kliknite na ten, ktorý má rozbaľovací zoznam.
- Prilepte kód do okna kódu vpravo.
- Zatvorte editor VB.
Teraz, kedykoľvek zmeníte hlavný rozbaľovací zoznam, spustí sa kód VBA a vymaže obsah závislého rozbaľovacieho zoznamu (ako je uvedené nižšie).
Ak nie ste fanúšikom VBA, môžete tiež použiť jednoduchý trik podmieneného formátovania, ktorý bunku zvýrazní vždy, keď dôjde k nesúladu. To vám môže pomôcť vizuálne vidieť a opraviť nesúlad (ako je uvedené nižšie).
Tu sú kroky, ako zvýrazniť nezhody v závislých rozbaľovacích zoznamoch:
- Vyberte bunku, ktorá má závislý rozbaľovací zoznam (zoznamy).
- Prejdite na domovskú stránku -> Podmienené formátovanie -> Nové pravidlo.
- V dialógovom okne Nové pravidlo formátovania vyberte „Pomocou vzorca určiť, ktoré bunky sa majú formátovať“.
- Do poľa vzorca zadajte nasledujúci vzorec: = ISERROR (VLOOKUP (E3, INDEX ($ A $ 2: $ B $ 6,, MATCH (D3, $ A $ 1: $ B $ 1)), 1,0))
- Nastavte formát.
- Kliknite na tlačidlo OK.
Vzorec používa funkciu VLOOKUP na kontrolu, či položka v závislom rozbaľovacom zozname je položka z hlavnej kategórie alebo nie. Ak nie je, vzorec vráti chybu. Túto funkciu používa ISERROR na vrátenie hodnoty TRUE, ktorá určuje podmienené formátovanie a zvýrazní bunku.
Tiež by sa vám mohli páčiť nasledujúce návody na Excel:
- Extrahujte údaje na základe výberu z rozbaľovacieho zoznamu.
- Vytvorenie rozbaľovacieho zoznamu s návrhmi na vyhľadávanie.
- Vyberte viac položiek z rozbaľovacieho zoznamu.
- Vytvorte viac rozbaľovacích zoznamov bez opakovania.
- Šetrite čas pomocou formulárov na zadávanie údajov v programe Excel.
