Ověření typu „jednou a dost“ reprezentuje stav, kdy prostřednictvím roletky z Data / Ověření chceme vybírat položku ze seznamu ve stylu
– rozlosování sportovních zápasů
– tahání čísel maturitních otázek z klobouku (bez jejich vracení nazpět)
– rozdání jednohlavých karet hry Prší
– rozebrání tomboly na plese
– atd.
V praxi to znamená, že
– počet buněk využívajících takové ověření je menší nebo roven počtu položek (ověření funguje do vyčerpání seznamu)
– všechny buňky budou obsahovat unikátní hodnoty
Jestliže tedy například v první buňce (z maximálně šesti) vybereme položku č. 3 (na výběr č. 1, 2, 3, 4, 5 a 6), pak ve druhé buňce se již nesmí objevit a my máme na výběr z položek č. 1, 2, 4, 5 a 6. Teď je to věřím již jasné, jen chybí doplnit drobnost – jak to udělat.
Je zřejmé, že potřebujeme upravovat původní seznam podle toho, jestli se jeho položky uplatnily v cílových buňkách. To lze řešit prostřednictvím funkce COUNTIF. Zbytek vzorce představuje techniku užívanou pro setřídění (možná lépe řečeno setřásání) vyhovujících položek v maticovém pojetí (funkce SMALL, generátor čísel čítače 1 .. počet buněk).
C3:C8
{=IFERROR(INDEX(rngZdrojPuvodni;SMALL(KDYŽ(COUNTIF(rngCil;rngZdrojPuvodni)=0; ŘÁDEK(NEPŘÍMÝ.ODKAZ(„1:“&ŘÁDKY(rngZdrojPuvodni)))); ŘÁDEK(NEPŘÍMÝ.ODKAZ(„1:“&ŘÁDKY(rngZdrojPuvodni)))));““)}
Nakonec si upravený seznam převezme definovaný název (viz funkce POSUN a technika známá pro seznamy s proměnným počtem položek). Pozor na to, že vzorcem výše si vracíme prázdné řetězce, a tak se ve funkci POSUN neuplatní jen POČET2, ale také funkce COUNTBLANK. Až takto definovaný název aplikujeme v Data / Ověření jako zdroj pro seznam. V daném případě se sice navzájem ovlivňuje zdroj a cíl, nicméně zacyklení nehrozí, protože proces není kontinuální. Ve výchozím stavu je počet položek upraveného seznamu samozřejmě roven počtu položek seznamu původního.
Dodatek
Obrázek níže ukazuje, jak postupovat v případě, kdy chcete uživateli pouze znemožnit vstup duplicitních hodnot v oblasti buněk (bez možnosti výběru unikátních položek ze seznamu). Řešení lze samozřejmě vhodně doplnit o ošetření chybových stavů (třetí záložka dialogu Ověření dat).
Příloha:
overeni_jednou_a_dost.zip