V tejto sérii overovania údajov sme sa doteraz naučili vytvárať normálny rozbaľovací zoznam a dynamický rozbaľovací zoznam pomocou rôznych techník s overovaním údajov v programe Excel.
A dnes, v tejto kapitole vám ukážeme, ako vytvoriť závislý rozbaľovací zoznam v programe Microsoft Excel pomocou rôznych metód.
Závislý rozbaľovací zoznam je známy aj ako kaskádová validácia údajov a obmedzuje možnosti v rozbaľovacom zozname v závislosti od hodnoty vybratej v druhej bunke, ktorá obsahuje validáciu údajov. Inými slovami, závisí to od hodnoty vybratej v prvom rozbaľovacom zozname, ktorá určuje hodnoty, ktoré sa majú zobraziť v druhom rozbaľovacom zozname.
Toto je veľmi bežný scenár práce s veľkými údajmi alebo dynamickými zostavami, kde chcete mať 2nd bunka zobrazí zoznam, ktorý závisí od položky zoznamu vybranej v prvom rozbaľovacom zozname.
Ako vieme, že v programe Excel existuje veľa spôsobov, ako vykonať konkrétnu úlohu, a podobne existuje mnoho spôsobov, ako vytvoriť overenie závislých údajov v programe Excel. A dnes predvedieme 5 rôznych techník na vytvorenie zoznamu validácií závislých dát.
Surové údaje môžu byť v ľubovoľnom poradí alebo formáte a vždy, keď nemôžete zmeniť údaje alebo formát, aby ste získali to, čo hľadáte.
Zobrali sme teda jednu množinu údajov, ale v 3 rôznych formátoch, aby sme získali závislý rozbaľovací zoznam. A ako vidíte, naše údaje sú na ľavej strane od stĺpca A do stĺpca E a očakávaný výstup budeme mať na pravej strane v stĺpci J a K. Stĺpec J bude mať primárnu validáciu zoznam, zatiaľ čo stĺpec K bude závislý a bude zobrazovať hodnoty v závislosti od hodnoty zvolenej v stĺpci J.
1sv Príklad:-
2nd Príklad:-
3rd Príklad:-
1sv Príklad:-
Máme zoznam produktov pre každý kód produktu od stĺpca A8 do E13. A chceme vybrať kód produktu v J10, potom v závislosti od zvoleného kódu produktu názov produktu v bunke K10.
Prvá metóda:-
Prvá metóda je veľmi jednoduchá a krátka a na získanie závislého rozbaľovacieho zoznamu vyžaduje iba 3 kroky. Funguje však iba úspešne, kým vo svojom sortimente nevykonáte žiadne zmeny. Akonáhle upravíte svoje údaje, budete musieť najskôr zmeniť pomenovaný rozsah, aby ste získali aktualizované overenie kaskádových údajov.
Postupujte podľa nižšie uvedených krokov:-
- Vyberte celú tabuľku od A8 do E13
- Potom prejdite na kartu „Vzorce“ a potom v kategórii „Definované mená“ kliknite na „Vytvoriť z výberu“
- Môžete tiež použiť klávesovú skratku CTRL + SHIFT + F3
- Zobrazí sa dialógové okno Vytvoriť mená z výberov
- Požiada o potvrdenie, ktoré riadky a stĺpce sa majú použiť na vytvorenie názvov pre ostatné riadky a stĺpce. Potvrdzujeme, že na vytvorenie mien použijeme „horný riadok“, a zrušíme začiarknutie políčka 2nd možnosť a potom klikneme na OK
Poznámka: - Medzery a iné špeciálne znaky okrem podčiarkovníka a bodky nie sú povolené ako názvy. Štandardne sa prevedie na podčiarkovník. Na oddelenie slov teda používajte podčiarkovník a bodku. Prvé písmeno nemôže byť ani číslo; musí to byť písmeno, podčiarkovník alebo spätné lomítko.
- Teraz, aby sme potvrdili, že každý rozsah má názov, ideme do „Správcu mien“ (stlačte CTRL + F3)
- Tam vidíme všetkých 5 pomenovaných rozsahov, ktoré sú k dispozícii
- A tiež vidíme, že každý názov rozsahu má namiesto medzery v strede reťazca podčiarkovník
Teraz vytvoríme rozbaľovací zoznam:-
- Vyberte bunku J10 a stlačením klávesov ALT ++ D+L otvorte dialógové okno Overenie údajov
- Vyberte položku Zoznam> a potom zadajte rozsah A8: E8 na karte Zdroj
- Kliknite na OK
- Teraz vytvoríme závislý zoznam v bunke K10
- Otvorte dialógové okno Overenie údajov stlačením klávesov ALT+D+L
- Vyberte Zoznam, v zdroji zadajte túto funkciu:- = NEPRIAMY (SUBSTITUT ($ J $ 10, "", "_"))
Pri validácii údajov sme na vytvorenie závislého zoznamu použili funkciu INDIRECT na vrátenie hodnoty na základe zoznamu primárnych validácií údajov. A aby sme podčiarkovník nahradili medzerou, použijeme funkciu SUBSTITUTE vo funkcii INDIRECT.
- Kliknite na OK
Keď v bunke J10 vyberieme ľubovoľný kód produktu, v bunke K10 sa zobrazí zoznam produktov vybraného kódu produktu. Napríklad: - Vybrali sme ETV 501, teraz môžete vidieť, že v bunke K10 sa zobrazuje zoznam závislých produktov
Poznámka: - Kedykoľvek pridáte názov produktu a kód produktu, ktoré sa nezobrazia v zozname.
Napríklad: - Produkt 26 sme pridali pod kód produktu ETV 505, ale keď zvolíme produkt ETV 505, pridaný produkt sa v rozbaľovacom zozname nezobrazí.
Takto teda môžete vytvoriť závislý rozbaľovací zoznam pomocou jednoduchej techniky v troch jednoduchých krokoch.
2nd Príklad:-
V tomto prípade uvidíme, ako získať závislý rozbaľovací zoznam, keď máte údaje, ako sú uvedené v tejto vertikálnej tabuľke.
Na vytvorenie závislého rozbaľovacieho zoznamu použijeme dve rôzne metódy. Oba sú takmer podobné techniky. Jeden je však bez pomenovaného rozsahu a druhý bude mať pomenovaný rozsah.
1sv Metóda:-
Aby sme urobili to isté, použijeme spoločne funkcie OFFSET, MATCH & COUNTIF.
Pretože vieme, že funkcia OFFSET sa používa na vytvorenie dynamického rozsahu, a preto na vytvorenie zoznamu „Overenie dynamických údajov“ používame funkciu OFFSET na vrátenie dynamického rozsahu.
MATCH sa používa na vrátenie relatívnej polohy položky v zozname v programe Excel. A tu nám to pomôže zodpovedať kategórii vybranej v primárnom rozbaľovacom zozname v našom rozsahu na hárku a vráti číslo.
A COUNTIF sa používa na získanie počtu buniek, ktoré zodpovedajú kritériám. A tu to použijeme na spočítanie počtu riadkov, ktoré sa majú zobraziť, pomocou funkcie COUNTIF.
Postupujte podľa nižšie uvedených krokov:-
- Vyberte bunku J21, v ktorej vytvoríme náš primárny zoznam overovania údajov
- Stlačením klávesov ALT+D+L otvorte dialógové okno Overenie údajov
- Vyberte zoznam z kategórie povolených
- Kliknite na kartu Zdroj a vyberte rozsah od B20: B24
- A kliknite na OK
- Prejdite do bunky K21 a znova otvorte dialógové okno na overenie údajov
- Potom vyberieme Zoznam a v zdroji zadajte nižšie uvedenú funkciu:
- = OFFSET ($ E $ 19, MATCH ($ J $ 21, $ D $ 20: $ D $ 32,0), 0, COUNTIF ($ D $ 20: $ D $ 32, $ J $ 21))
- Kliknite na OK
- V bunke K21 vidíme všetky zodpovedajúce hodnoty vybraného kódu produktu:-
Takto teda môžete získať závislý zoznam prijatím odkazov na bunky vo funkcii.
2nd Metóda:-
V ďalšej metóde použijeme pomenovaný rozsah v tej istej funkcii na získanie validácie kaskádových údajov. Najprv musíme vytvoriť dynamický zoznam pre kód produktu. V prípade akéhokoľvek nového produktu pridaného k údajom by sa mala rozbaľovacia ponuka aktualizovať, aby sa zobrazovali rovnako.
Ak chcete urobiť to isté, postupujte podľa nižšie uvedených krokov:-
- Vyberte B19 a potom stlačením klávesov CTRL + F3 otvorte okno „Správca mien“
- Teraz klikneme na „Nové“ a zobrazí sa dialógové okno „Definovať meno“
- Vidíme, že názov sa už zobrazuje v poli s názvom -pretože sme vybrali B9 pred otvorením okna „Správca mien“. A keďže B19 má v sebe text, ak chceme, môžeme ho zmeniť na iný názov.
- Zadajte nižšie uvedený vzorec:-
= OFFSET ('DependentDropDownList'! $ B $ 20,0,0, COUNTA ('DependentDropDownList'! $ B $ 20: $ B $ 32))
- Kliknite na OK
Keďže sme vytvorili dynamický zoznam pre jedinečné produkty, vytvoríme teraz dynamický rozsah pre rozsah kódov produktov, ktorý je v stĺpci D.
Postupujte podľa rovnakých krokov, ktoré sme použili pri jedinečnom produkte:-
- Vyberte bunku D19, otvorte dialógové okno Definovať meno
- Zistíte, že meno už existuje
- V prípade odkazov zadajte nasledujúci vzorec:-
= OFFSET („závislý rozbaľovací zoznam“! $ D $ 20,0,0, COUNTA („závislý rozbaľovací zoznam! $ D $ 20: $ D $ 35))
- Kliknite na OK
- Teraz sú oba dynamické rozsahy pripravené. Prejdeme teda na J22 a stlačíme „ALT + D + L“ a vyberieme „Zoznam“
- V zdroji budeme mať pomenovaný rozsah, ktorý sme definovali pre „Unikátny kód produktu“, takže stlačením klávesu F3 zobrazíme všetky dostupné pomenované rozsahy.
- Vidíme pomenovaný rozsah „Unikátny kód produktu“, klikneme naň a potom klikneme na tlačidlo OK a stlačíme kláves Enter
- V okamihu, keď stlačíme kláves Enter, zobrazí sa šípka nadol v bunke J22, ktorá obsahuje zoznam jedinečných kódov produktov
- Vyberte bunku K22 a otvorte dialógové okno „Overenie údajov“
- Použijeme rovnakú funkciu, akú sme použili v minulej metóde, ale s pomenovaným rozsahom
- Vyberte zoznam a potom v zdroji zadajte nasledujúci vzorec:-
= OFFSET ($ E $ 19, MATCH ($ J $ 22, Product_Code, 0), 0, COUNTIF (Product_Code, J22))
- Kliknite na OK
- Teraz máme primárny rozbaľovací zoznam a podradený zoznam produktov
- Vyberte produkt „ETV-101“ z J22 a v K22 vidíme iba názvy, ktoré spadajú pod tento produkt „ETV-101“. A keď zmeníme akýkoľvek produkt („ETV-103) v J22, K22 zobrazí zodpovedajúce hodnoty pre tento kód
Teraz uvidíme, čo sa stane, keď do zoznamu pridáme nový kód produktu? Budú tieto rozbaľovacie zoznamy aktualizované?
Pridajte nový produkt do zoznamu; Postupujte podľa nižšie uvedených krokov:-
- Pridajte kód produktu do zoznamu Unique_Prod_Code
- Do údajov pridajte aj kód_produktu a názov_produktu:-
- Teraz skontrolujte rozbaľovací zoznam -zobrazí sa kód a názov pridaného produktu
3rd Príklad:-
Dynamické nadpisy máme priamo z tabuľky a do sortimentu pridáme nové produkty. Tabuľka je v rovnakom formáte, aký sme použili pre 1sv metóda.
4th Metóda:-
Postupujte podľa nižšie uvedených krokov:-
- Vyberte nadpis A40: E40
- Najprv vytvorte dynamický rozsah pre nadpisy a otvorte dialógové okno „Definovať meno“
- Na miesto názvu napíšte „Nadpis“ a potom do „odkazuje“ zadajte nasledujúci vzorec:-
- Zadajte nasledujúcu funkciu:-
- = OFFSET ('Závislý rozbaľovací zoznam'! $ A $ 40 ,,,, COUNTA ('Závislý rozbaľovací zoznam' $ 40: $ 40))
- Kliknite na OK
- Dynamický rozsah „Nadpis“ je teraz pripravený
A teraz vytvoríme pomenovaný rozsah pre každý nadpis, postupujte podľa nižšie uvedených krokov:-
- Vyberte tabuľku od A40 do E50
- Klávesová skratka CTRL + SHIFT + F3
- Zrušíme začiarknutie políčka 2nd možnosť
- A predtým, ako klikneme na OK, skontrolujte 1sv je vybratá možnosť „Horný riadok“
- Teraz sme pripravení s oboma rozsahmi
Teraz pripravíme rozbaľovací zoznam rodičov
- Vyberte bunku J42
- Otvorte dialógové okno Overenie údajov
- Potom po výbere „Zoznam“ stlačíme F3 v zdroji, aby sme získali pomenovaný rozsah pre nadpisy. Kliknite na „Nadpis“ a potom kliknite na tlačidlo OK a stlačte kláves Enter. Teraz máme rodičovský zoznam v J42
- Ak chcete vytvoriť zoznam podrobností položky, vyberte bunku K42
- Otvorte dialógové okno Overenie údajov stlačením klávesov ALT+D+L
- Vyberte položku Zoznam a potom zadajte nižšie uvedenú funkciu na karte Zdroj:-
- = OFFSET (NEPRIAMY (NÁHRADA (SUBSTITUTE ($ J $ 42, "", "_")) ),, COUNTA (NEPRIAMY (SUBSTITUTE ($ J $ 42, "", "_"))))
- Kliknite na OK
Teraz vyberte nejakú položku v J42, povedzme, že vyberieme „položku 01“ a pozrite sa na rozbaľovací zoznam K42. A rovnako ako predchádzajúce 3 metódy, aj tu máme závislý zoznam.
Tak čo je nové? V prvom prípade ste nemohli pridať žiadny produkt do zoznamu, ale tu môžete pridať nový produkt. Povedzme, že do tejto položky pridávame nový produkt. Ideme na A45, napíšeme „ETV-501 Prod 05“ a potom sa vrátime k K42 a tu to máte. Môžete vidieť, že bol pridaný nový produkt.
- Teraz pridajte do novej položky niekoľko produktov
Keď vyberieme položku „Položka 06“, prejdeme na K42 a klikneme na rozbaľovací zoznam. Prekvapivo sa nič nestane, keď klikneme na šípku nadol. Je to preto, že sme vytvorili všetko dynamické a zabudli sme vytvoriť dynamický rozsah pre tabuľku, a preto sa výrobky nezobrazujú v podradenom zozname.
Na to musíme použiť rôzne techniky. Existujú dva spôsoby, ako to urobiť. Tabuľku môžete vytvoriť alebo jednoducho použiť iba funkciu OFFSET. A v ďalšej metóde použijeme funkciu OFFSET a uvidíme trik na rozšírenie rozsahu tabuliek.
- Najprv teda prejdeme na J43 a stlačíme „ALT + D + L“
- Vyberieme „Zoznam“ a potom v zdroji stlačíme F3 a vyberieme „Nadpis“, klikneme na tlačidlo OK a potom stlačíme kláves Enter
- Teraz prejdeme na K43 a po výbere „Zoznam“ prejdeme na „Zdroj“ a zadáme nižšie uvedenú funkciu
= OFFSET ($ A $ 40,1, MATCH ($ J $ 43, $ 40: $ 40,0) -1, COUNTA (OFFSET ($ A $ 40,1, MATCH ($ J $ 43, $ 40: $ 40,0) -1,1000 , 1)))
- Kliknite na OK
Teraz sa vrátime späť a v bunke J43 vyberieme „Položku 06“, vrátime sa do K43 a klikneme na šípku nadol. Tento časový zoznam však zobrazuje produkty, ktoré sme pridali pre novú položku. A vyberáme prvý produkt „ETV-506 Prod 01“.
Takto môžete vytvoriť závislý rozbaľovací zoznam pomocou rôznych metód pre akýkoľvek typ formátu údajov.
Video: Ako vytvoriť závislý (kaskádový) rozbaľovací zoznam v programe Excel pomocou 5 rôznych techník v programe Microsoft Excel
Kliknutím na odkaz na video získate rýchly prehľad o jeho použití. Prihláste sa na odber nášho nového kanála a učte sa s nami!
Ak sa vám páčili naše blogy, zdieľajte ich so svojimi priateľmi na Facebooku. A tiež nás môžete sledovať na Twitteri a Facebooku.
Radi by sme vás počuli, dajte nám vedieť, ako môžeme zlepšiť, doplniť alebo inovovať našu prácu a zlepšiť ju pre vás. Napíšte nám na emailovú stránku