Tweet

Výběrový seznam bez opakování - Excel

Úvodem

V komentářích zazněl zajímavý požadavek. Přiřazení pracovníku na stroje, tak aby byl využit výběrový seznam. Ve výběrovém seznamu musí být k dispozici jen osoby, které nejsou zatím přiřazeny k pracovnímu stroji. Nesmí být možnost přiřadit jednu osobu na dva stroje. Nikdo nezvládá pracovat na dvou místech zároveň.

Ukázka

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

• Vstupní data
• Požadované znalosti
• Postup řešení
• Ke stažení
• Příbuzná řešení - závislé seznamy, dynamicky výběr pro graf, ...

Vstupní data

K dispozici máte seznam zaměstanců, případně jiný seznam ze kterého potřebujete vybírat unikátní data:

Jednoho zaměstance lze přiřadit pouze na jednu práci (nemůže být v Brně a Praze zároveň - neplatí pro naše voly-telné zástupce, ti zvládají pracovat i 25 hodin denně). Excel to musí ohlídat za nás.

Požadované znalosti

Předpokládám, že máte základní znalosti (funguje jako odkaz na články s podrobným popisem):

• Definované názvy
• Ovládací prvky formuláře
• Funkce KDYŽ, COUNTIF, IFERROR, SMALL
• Ověření dat

Postup řešení

Připravíte si pomocnou tabulku.

• Vybraná jména - pokud jméno vyberete - nebude k dispozici "zmizí" z přehledu
• Pomocné číslo sloupce - pokud jméno ještě není vybráno, je zde číslo řádku (bere se ze sloupce ID)
• Postupka čísel - pokud číslo řádku existuje, seřadíme je od nemenšího po největší
• Unikátní jména - k danému číslu přiřadíme dané jméno a takto máte k dispozici unikátní seznam jmen, které zatím nebyla vybrána.

Příslušné kódy

=KDYŽ(COUNTIF($D$6:$D$10;L12)>0;"";L12) =KDYŽ(M12="";"";K12) =IFERROR(SMALL($N$12:$N$19;K12);"") =IFERROR(INDEX($L$12:$L$19;O12);"")

Definovaný název

Ze sloupce Unikátní jména vytvoříte definovaný název Jméno

=POSUN(List1!$P$12; 0; 0; POČET(List1!$O$12:$O$19); 1)

Ověření dat

Do příslušných buněk přiřadíme ověření dat s odkazem na definovaný název Jméno.

Ke stažení

Soubor Ověření dat bez opakování v Excel ke stažení zdarma. Bez použití maker a VBA.

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ů:

• Propojené (závislé) seznamy - vybere se ovoce a nebo zelenina a na zákaldě výběru lze teprve zvolit příslušné ovoce (jahodu, jablko), nebo zeleninu (mrkev, petržel)
• 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

Máte k dispozici nějakou fintu, která využívá ověření dat? Můžete se zmínit v komentářích.

Článek byl aktualizován: 19.09.2020 11:02

Pavel Lasák - autor webu

Microsoft Office (Word, Excel, Google tabulky, PowerPoint) se věnuji od roku 2000 (od 2004 na této doméně) - V roce 2017 jsem od Microsoft získal prestižní ocenění MVP (zatím jsem jediný z ČR v kategorií Excel). 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 15 let. Zdarma je zde přes 1.000 návodu, tipů a triků, včetně přes 250 různých šablon, sešitů.

           

Komentáře