Propojené (závislé) seznamy - ověření dat

Úvodem

Obdržel jsem dotaz: "Jak pomocí jednoho rozevíracího seznamu, vybírat data pro druhý rozevírací seznam - nejlépe za použití ověření dat.

Kapitoly

Pro přehlednost je článek rozdělen na kapitoly:

• Úvodem
• Požadované znalosti
• verze 1 - druhý výběr na základě podmínky
  • Vstupní data
  • Postup řešení
  • Ke stažení
• verze 2 - další výběry na základě podmínky jsou dynamické
  • Vstupní data
  • Postup řešení
  • Ke stažení
• Příbuzná řešení - závislé seznamy, dynamicky výběr pro graf, ...

Úvodem

Příklad 1 požadavkem je z prvního seznamu vybrat požadovanou položku (zvíře/rostlina/člověk), která do druhého seznamu dynamicky přiřadí příslušné položky. Na výběr tedy bude například: kůň, kráva, tele, prase (pro zvíře) nebo jetel, dub, buk, růže (pro rostlina) muž, žena (pro člověk). Takže když vyberete rostlina (z prvního seznamu) už ve druhém sloupci nebudete moci vybrat kůň, ale jen položky týkající se rostliny (jetel, dub, buk, růže).

• Vstupní data
• Postup řešení
• Ke stažení

Příklad 2 Podobný dotaz, kdy se další seznamy dynamicky upravují na základě první volby (druhý příklad). V ukázkovém příkladě se vybírají zánky. Vybere se typ (v prvním seznamu) a pro další seznamy (ověření dat) se dynamicky přiřadí příslušné hodnoty. Vybírat tedy bude možno Orientace (pravý, levý, horní, ...), Průměr, Délka. Dané hodnoty jsou pro každý typ odlišné.

• Vstupní data - verze 2
• Postup řešení - verze 2
• Ke stažení - verze 2

Požadované znalosti

Než začnete prakticky řešit, předpokládám tyto základní znalosti práce s Excel:

• Ověření dat v Excel 2010 - základy
• Definované názvy - pojmenování oblasti
• funkce NEPŘIMÝ.ODKAZ

Verze 1 - druhý výběr na základě podmínky

Pro tvorbu tohoto příkladu musíte mít vhodně připravená vstupní data.

Vstupní data

Jeden sloupec pro první výběrový seznam:

• zvíře
• rostlina
• člověk

Pro jednotlivé "obory" připravíte pro druhý seznam hodnoty. Pro zvíře: kůň, kráva, tele, prase, atd

Nejprve si připravte tabulku o čtyřech sloupcích:

volba	zvire	rostlina	clovek
zvíře	kůň	jetel	muž
rostlina	kráva	dub	žena
člověk	tele	buk
	prase	růže

Ukázka

Postup řešení - verze 1

1. Pojmenování

Pojemnujete jednotlivé sloupce první například Volba, druhy zvire, třetí rostlina, čtvrtý clovek. Tj. jména z prvního sloupce, který mate pojmenovan VOLBY musí souhlasit s pojmenovanými dalšími sloupci. (tj. pokud vyberete zvire musí se další sloupce jmenova zvire). Viz ukázka: Správce názvu

2. Ověření dat - první sloupec

Pro první ověřovací sloupce (tam se využije pojmenování volba - tj. bude se vybírat zvíře, rostlina, člověk)

3. Ověření dat - druhý sloupec

Pro druhy ověřovací sloupce (ten už je závislý na prvním), Vyberete opět seznam a do políčka zadáte vzorec:

=NEPŘÍMÝ.ODKAZ(A7)

Podle potřeby rozkopírujete.

Hotovo

A již můžete otestovat.

Ke stažení zdarma - verze 1

Soubor Propojené (závislé) seznamy - ověření dat - praktická ukázka ke stažení zdarma.

Verze 2 - další výběry na základě podmínky jsou dynamické

Další možnost jak vytvořit propojený seznam. Cílem je v prvním ovládacím pole se seznamem vybrat typ, položky v dalších vyběrových seznamech je nutno upravit podle tohoto výběru. Počet položek ve výběrových seznamech nemusí být stejný.

Vyberu typ a k němu si volím další varianty:

• Orientace
• Průměr
• Délka

Vstupní data

Na obrázku vpravo je ukázka dat, která jsou použita v praktickém řešení.

Soubor zdrojová data ke stažení zdarma, ať můžete sami prakticky zkoušet.

Postup řešení - verze 2

Řešení

Jsou naznačena dvě řešení:

• Funkce a ovládací prvek formuláře - Řešení využívá funkce VVYHLEDAT, KDYŽ, dynamicky definované názvy a ovládací prvek - pole se seznamem.
• Pomocí ověření dat - Pro ověření dat má každý sloupec definovaný název, jeho jméno a číslo typu (tj. například Orientace201, Delka202, atd.)

Nepřímý odkaz je složen ať se může odkazovat na daný definovaný název podle výběru typu:

=NEPŘÍMÝ.ODKAZ(CONCATENATE($O$25;ZPRAVA(N26;3)))

Ke stažení zdarma - verze 2

Soubor Propojené (závislé) seznamy ke stažení zdarma. Pokud se vám například nedaří samostaně vytvořit.

Příbuzná řešení

Pokud hledáte podobné řešení, odlišné úlohy, třeba využijete některé z již vyřešených praktických příkladů:

• Výběrový seznam bez opakování
• Dynamická oblast pro graf - vyberte rozsah a automaticky se vygeneruje příslušný graf
• Zobrazení zvoleného obrázku - vyberete název a zobrazí se příslušný obrázek

Závěrem

Pomohla Vám některá z verzí? Nebo popsaný "problém" řešíte úplně jinak? Můžete zmínit v komentářích své řešení.

Článek byl aktualizován: 19.09.2020 10:56

Pavel Lasák - autor webu

Microsoft Office (Word, Excel, Google tabulky, PowerPoint) se věnuji od roku 2000 (od dubna roku 2004 na této doméně) - V roce 2017 jsem od Microsoft získal prestižní ocenění MVP (zatím 8x za sebou). Své vědomosti a zkušenosti dávám k dispozici i on-line ve videích pro SEDUO. Ve firmách školím a konzultuji, učím na MUNI. Tento web již tvořím přes 20 let (o Excel píší přes 25). Zdarma je zde přes 1.500 návodu, tipů a triků, včetně přes 350 různých šablon, sešitů a přes 70 taháků v pdf.