V této sérii ověřování dat jsme se zatím naučili vytvářet normální rozevírací seznam a dynamický rozevírací seznam pomocí různých technik s ověřováním dat v aplikaci Excel.
A dnes v této kapitole vám ukážeme, jak vytvořit závislý rozevírací seznam v aplikaci Microsoft Excel pomocí různých metod.
Závislý rozevírací seznam je také známý jako kaskádová validace dat a omezuje možnosti v rozevíracím seznamu v závislosti na hodnotě vybrané v jiné buňce, která obsahuje ověření dat. Jinými slovy, závisí na hodnotě vybrané v prvním rozevíracím seznamu, která určuje hodnoty, které se mají zobrazit v druhém rozevíracím seznamu.
Toto je velmi běžný scénář práce s velkými daty nebo některými dynamickými sestavami, kde chcete mít 2nd buňka zobrazí seznam, který závisí na položce seznamu zvolené v prvním rozevíracím seznamu.
Jak víme, že v aplikaci Excel existuje mnoho způsobů, jak provést konkrétní úkol, a podobně existuje mnoho způsobů, jak v aplikaci Excel vytvářet ověřování závislých dat. A dnes předvedeme 5 různých technik k vytvoření seznamu ověřování závislých dat.
Surová data mohou být v libovolném pořadí nebo formátu a pokaždé, když nemůžete změnit data nebo formát, abyste získali to, co hledáte.
Vzali jsme tedy jednu sadu dat, ale ve 3 různých formátech, abychom získali závislý rozevírací seznam. A jak vidíte, naše data jsou na levé straně, která je ze sloupce A do sloupce E, a my budeme mít náš očekávaný výstup na pravé straně, která je ve sloupci J a K. Sloupec J bude mít primární ověření seznam, zatímco sloupec K bude závislý a zobrazí hodnoty v závislosti na hodnotě vybrané ve sloupci J.
1Svatý Příklad:-
2nd Příklad:-
3rd Příklad:-
1Svatý Příklad:-
Máme seznam produktů pro každý kód produktu ze sloupců A8 až E13. A chceme vybrat kód produktu v J10, pak v závislosti na zvoleném kódu produktu název produktu v buňce K10.
První metoda:-
První metoda je velmi jednoduchá a krátká a k získání závislého rozevíracího seznamu vyžaduje pouze 3 kroky. Úspěšně však funguje pouze do doby, než ve svém rozsahu provedete jakékoli změny. Jakmile svá data upravíte, budete muset nejprve upravit pojmenovaný rozsah, abyste získali aktualizované ověření kaskádových dat.
Postupujte podle níže uvedených kroků:-
- Vyberte celou tabulku od A8 do E13
- Poté přejděte na kartu „Vzorce“ a poté v kategorii „Definovaná jména“ klikněte na „Vytvořit z výběru“
- Můžete také použít klávesovou zkratku CTRL + SHIFT + F3
- Zobrazí se dialogové okno Vytvořit jména z výběrů
- Požádá o potvrzení, které řádky a sloupce mají být použity k vytvoření názvů pro jiné řádky a sloupce. Potvrzujeme, že k vytvoření jmen použijeme „horní řádek“ a zrušíme zaškrtnutí 2nd možnost a poté klikneme na OK
Poznámka: - Mezery a další speciální znaky kromě podtržítka a tečky nejsou povoleny jako názvy. Ve výchozím nastavení bude převeden na podtržítko. K oddělení slov tedy použijte znak podtržítka a tečku. První písmeno také nemůže být číslo; musí to být písmeno, podtržítko nebo zpětné lomítko.
- Nyní, abychom potvrdili, že každý rozsah má název, přejdeme do „Správce jmen“ (stiskněte CTRL + F3)
- Zde můžeme vidět všech 5 pojmenovaných rozsahů, které jsou k dispozici
- A také vidíme, že každý název rozsahu má podtržítko místo prázdného uprostřed řetězce
Nyní vytvoříme rozevírací seznam:-
- Vyberte buňku J10 a stisknutím ALT ++ D+L otevřete dialogové okno Ověření dat
- Vyberte Seznam> poté zadejte rozsah A8: E8 na kartě Zdroj
- Klikněte na OK
- Nyní vytvoříme závislý seznam v buňce K10
- Otevřete dialogové okno Ověření dat stisknutím klávesy ALT+D+L
- Vyberte Seznam, ve zdroji zadejte tuto funkci:- = NEPŘÍMÝ (SUBSTITUT ($ J $ 10, "", "_"))
Při ověřování dat jsme k vytvoření závislého seznamu použili funkci INDIRECT k vrácení hodnoty na základě primárního seznamu pro ověření dat. A abychom podtržítko nahradili mezerou, použijeme funkci SUBSTITUTE uvnitř funkce INDIRECT.
- Klikněte na OK
Když vybereme jakýkoli kód produktu v buňce J10, pak se v buňce K10 zobrazí seznam produktů vybraného kódu produktu. Například: - Vybrali jsme ETV 501, nyní můžete vidět, že se v buňce K10 zobrazuje seznam závislých produktů
Poznámka: - Kdykoli přidáte název produktu a kód produktu, který se v seznamu neobjeví.
Například: - Přidali jsme produkt 26 pod kód produktu ETV 505, ale když vybereme produkt ETV 505, přidaný produkt se v rozevíracím seznamu nezobrazí.
Takto tedy můžete vytvořit závislý rozevírací seznam pomocí jednoduché techniky ve 3 jednoduchých krocích.
2nd Příklad:-
V tomto příkladu uvidíme, jak získat závislý rozevírací seznam, když máte data, jak je uvedeno v této vertikální tabulce.
K vytvoření závislého rozevíracího seznamu použijeme dvě různé metody. Oba jsou téměř podobné techniky. Jeden je však bez pojmenovaného rozsahu a druhý bude mít pojmenovaný rozsah.
1Svatý Metoda:-
Abychom udělali totéž, použijeme společně funkce OFFSET, MATCH & COUNTIF.
Protože víme, že k vytvoření dynamického rozsahu se používá funkce OFFSET, a proto k vytvoření seznamu „Ověření dynamických dat“ používáme funkci OFFSET k vrácení dynamického rozsahu.
MATCH se používá k vrácení relativní polohy položky v seznamu v aplikaci Excel. A tady nám to pomůže přiřadit kategorii vybranou v primárním rozevíracím seznamu v našem rozsahu na listu a vrátí číslo.
A COUNTIF se používá k získání počtu buněk, který odpovídá kritériím. A zde to použijeme k počítání počtu řádků, které se mají zobrazit pomocí funkce COUNTIF.
Postupujte podle níže uvedených kroků:-
- Vyberte buňku J21, ve které vytvoříme náš primární seznam ověřování dat
- Stisknutím klávesy ALT+D+L otevřete dialogové okno Ověření dat
- Vyberte seznam z kategorie povolení
- Klikněte na kartu Zdroj a vyberte rozsah od B20: B24
- A klikněte na OK
- Přejděte do buňky K21 a znovu otevřete dialogové okno pro ověření dat
- Poté vybereme Seznam a ve zdroji zadáme níže uvedenou funkci:
- = OFFSET ($ E $ 19, MATCH ($ J $ 21, $ D $ 20: $ D $ 32,0), 0, COUNTIF ($ D $ 20: $ D $ 32, $ J $ 21))
- Klikněte na OK
- V buňce K21 vidíme všechny odpovídající hodnoty vybraného kódu produktu:-
Takto tedy můžete získat závislý seznam převzetím odkazů na buňky ve funkci.
2nd Metoda:-
V další metodě použijeme pojmenovaný rozsah ve stejné funkci, abychom získali ověření kaskádových dat. Nejprve musíme vytvořit dynamický seznam pro kód produktu. V případě jakéhokoli nového produktu přidaného do dat by měl být rozevírací seznam aktualizován, aby zobrazoval totéž.
Chcete -li provést totéž, postupujte podle níže uvedených kroků:-
- Vyberte B19 a poté stisknutím CTRL + F3 otevřete okno „Správce jmen“
- Nyní klikneme na „Nový“ a zobrazí se dialogové okno „Definovat jméno“
- Vidíme, že název se již objevuje v poli pro název -to proto, že jsme vybrali B9 před otevřením okna „Správce jmen“. A protože B19 má text, pokud chceme, můžeme jej změnit na jiný název.
- Níže zadejte vzorec pro zmínku:-
= OFFSET ('DependentDropDownList'! $ B $ 20,0,0, COUNTA ('DependentDropDownList'! $ B $ 20: $ B $ 32))
- Klikněte na OK
Protože jsme vytvořili dynamický seznam pro jedinečné produkty, vytvoříme nyní dynamický rozsah pro rozsah kódů produktů, který je ve sloupci D.
Postupujte podle stejných kroků, které jsme použili u jedinečného produktu:-
- Vyberte buňku D19, otevřete dialogové okno Definovat název
- Zjistíte, že jméno již existuje
- V odkazuje zadejte následující vzorec:-
= OFFSET ('závislý rozevírací seznam'! $ D $ 20,0,0, COUNTA ('závislý rozevírací seznam' $ D $ 20: $ D $ 35))
- Klikněte na OK
- Nyní jsou připraveny oba dynamické rozsahy. Přejdeme tedy na J22 a stiskneme „ALT + D + L“ a vybereme „Seznam“
- Ve zdroji budeme mít pojmenovaný rozsah, který jsme definovali pro „Unikátní kód produktu“, takže stisknutím klávesy F3 zobrazíme všechny dostupné pojmenované rozsahy
- Vidíme pojmenovaný rozsah „Unikátní kód produktu“, takže na něj klikneme a poté klikneme na OK a stiskneme Enter
- V okamžiku, kdy stiskneme klávesu Enter, dostaneme v buňce J22 rozbalovací šipku, která obsahuje seznam jedinečných kódů produktů
- Vyberte buňku K22 a otevřete dialogové okno „Ověření dat“
- Použijeme stejnou funkci, kterou jsme použili v poslední metodě, ale s pojmenovaným rozsahem
- Vyberte seznam a poté ve zdroji zadejte níže uvedený vzorec:-
= OFFSET ($ E $ 19, MATCH ($ J $ 22, Product_Code, 0), 0, COUNTIF (Product_Code, J22))
- Klikněte na OK
- Nyní máme primární rozevírací seznam a podřízený seznam produktů
- Vyberte produkt „ETV-101“ z J22 a v K22 vidíme pouze jména, která spadají pod tento produkt „ETV-101“. A když změníme jakýkoli produkt („ETV-103) v J22, K22 zobrazí odpovídající hodnoty pro tento kód
Nyní uvidíme, co se stane, když do seznamu přidáme jakýkoli nový kód produktu? Budou tyto rozevírací seznamy aktualizovány?
Pojďme přidat nový produkt do seznamu; Postupujte podle níže uvedených kroků:-
- Přidejte kód produktu do seznamu Unique_Prod_Code
- Také přidejte kód Product_Code a Product_Name do dat:-
- Nyní zkontrolujte rozevírací seznam -zobrazí se kód a název produktu
3rd Příklad:-
Dynamické nadpisy máme přímo z tabulky a do sortimentu přidáme nové produkty. Tabulka je ve stejném formátu, jaký jsme použili pro 1Svatý metoda.
4th Metoda:-
Postupujte podle níže uvedených kroků:-
- Vyberte nadpis A40: E40
- Nejprve vytvořte dynamický rozsah pro nadpisy, otevřete dialogové okno „Definovat jméno“
- Na místo jména napište „Nadpis“ a poté do „odkazuje na“ zadejte níže uvedený vzorec:-
- Zadejte následující funkci:-
- = OFFSET ('Závislý rozevírací seznam'! $ A $ 40 ,,,, COUNTA ('Závislý rozevírací seznam' $ 40: $ 40))
- Klikněte na OK
- Dynamický rozsah „Nadpis“ je nyní připraven
A nyní vytvoříme pojmenovaný rozsah pro každý nadpis, postupujte podle níže uvedených kroků:-
- Vyberte tabulku od A40 do E50
- Klávesová zkratka CTRL + SHIFT + F3
- Zrušíme zaškrtnutí 2nd volba
- A než klikneme na OK, zajistěte 1Svatý je vybrána možnost „Horní řádek“
- Nyní jsme připraveni s oběma rozsahy
Nyní připravíme rozevírací seznam rodičů
- Vyberte buňku J42
- Otevřete dialogové okno Ověření dat
- Poté, co vybereme „Seznam“, stiskneme F3 ve zdroji, abychom získali pojmenovaný rozsah pro nadpisy. Klikneme na „Nadpis“ a poté klikneme na OK a stiskneme Enter. Nyní máme rodičovský seznam v J42
- Chcete -li vytvořit seznam podrobností položky, vyberte buňkuK42
- Otevřete dialogové okno Ověření dat stisknutím klávesy ALT+D+L
- Vyberte Seznam a poté zadejte níže uvedenou funkci na kartě Zdroj:-
- = OFFSET (INDIRECT (SUBSTITUTE (SUBSTITUTE ($ J $ 42, "", "_")) ),, COUNTA (INDIRECT (SUBSTITUTE ($ J $ 42, "", "_"))))
- Klikněte na OK
Nyní vyberte nějakou položku v J42, řekněme, že vybereme „Item 01“ a podívejte se na rozevírací seznam K42. A stejně jako předchozí 3 metody máme i zde závislý seznam.
Tak co je nového? V prvním příkladu jste nemohli přidat žádný produkt do seznamu, ale zde můžete přidat jakýkoli nový produkt. Řekněme, že do této položky přidáme nový produkt. Jdeme na A45, napíšeme „ETV-501 Prod 05“ a pak se vrátíme do K42 a tady to máte. Vidíte, nový produkt byl přidán.
- Nyní přidejte pod novou položku několik produktů
Když vybereme „Položka 06“, přejdeme na K42 a klikneme na rozevírací seznam. Překvapivě se nic nestane, když klikneme na šipku rozevíracího seznamu. Důvodem je, že jsme vytvořili vše dynamické a zapomněli jsme vytvořit dynamický rozsah pro tabulku, a proto se produkty nezobrazují v podřízeném seznamu.
K tomu musíme použít různé techniky. Existují dva způsoby, jak toho dosáhnout. Tabulku můžete buď vytvořit, nebo jednoduše použít pouze funkci OFFSET. A v další metodě použijeme funkci OFFSET a uvidíme trik, jak rozšířit také rozsah tabulek.
- Nejprve tedy přejdeme na J43 a stiskneme „ALT + D + L“
- Vybereme „Seznam“ a poté ve zdroji stiskneme F3 a vybereme „Nadpis“, klikneme na OK a poté stiskneme Enter
- Nyní přejdeme na K43 a po výběru „Seznam“ přejdeme na „Zdroj“ a zadáme níže uvedenou funkci
= 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)))
- Klikněte na OK
Nyní se vrátíme zpět a v buňce J43 vybereme „Položka 06, vrátíme se do K43 a klikneme na šipku rozevíracího seznamu. Tento časový seznam však zobrazuje produkty, které jsme přidali pro nové položky. A vybereme první produkt „ETV-506 Prod 01“.
Takto můžete vytvořit závislý rozevírací seznam pomocí různých metod pro jakýkoli typ datového formátu.
Video: Jak vytvořit závislý (kaskádový) rozevírací seznam v aplikaci Excel pomocí 5 různých technik v aplikaci Microsoft Excel
Kliknutím na odkaz na video získáte rychlý přehled o jeho použití. Přihlaste se k odběru našeho nového kanálu a učte se s námi!
Pokud se vám naše blogy líbily, sdílejte je se svými přáteli na Facebooku. A také nás můžete sledovat na Twitteru a Facebooku.
Rádi bychom od vás slyšeli, dejte nám vědět, jak můžeme zlepšit, doplnit nebo inovovat naši práci a zlepšit ji pro vás. Napište nám na e -mail