Obecný vzorec
=SUMPRODUCT(--ISNUMBER(SEARCH(list,A1)))=0
souhrn
Chcete-li zakázat vstup, který obsahuje jednu z mnoha věcí, můžete použít vlastní pravidlo pro ověření dat založené na funkci SEARCH.
V zobrazeném příkladu je ověření dat aplikované na B5: B11:
=SUMPRODUCT(--ISNUMBER(SEARCH(list,B5)))=0
Vysvětlení
Pravidla ověření dat se aktivují, když uživatel přidá nebo změní hodnotu buňky.
Tento vzorec používá funkci VYHLEDÁVÁNÍ k testování vstupu uživatele pro každou hodnotu v pojmenovaném rozsahu „list“. Logika hledání je „obsahuje“ - je-li nalezena hodnota ze „seznamu“, funkce SEARCH vrátí pozici hodnoty jako číslo. Pokud není nalezen, vrátí SEARCH chybu.
Funkce ISNUMBER poté převede čísla na TRUE a chyby na FALSE a operátor dvojitého záporného čísla změní hodnoty TRUE FALSE na 1s a nuly. Protože pojmenovaný rozsah „list“ obsahuje 5 hodnot, dostaneme zpět 5 výsledků v poli, jako je tento:
(0; 0; 0; 0; 0)
SUMPRODUCT poté shrnuje položky v poli a výsledek je testován proti nule. Pokud jsou všechny položky nulové, vrátí funkce SUMPRODUCT nulu a ověření bude úspěšné. Pokud SUMPRODUCT vrací jiné číslo (tj. Když je nalezena položka v „seznamu“), vrátí vzorec FALSE a ověření selže.
Poznámka: Odkazy na buňky ve vzorcích pro ověření dat jsou relativní k buňce vlevo nahoře v rozsahu vybraném, když je definováno pravidlo ověření, v tomto případě B5.
