Všeobecný vzorec
=SUMPRODUCT(--ISNUMBER(SEARCH(list,A1)))=0
Zhrnutie
Ak chcete zakázať vstup, ktorý obsahuje jednu z mnohých vecí, môžete použiť vlastné pravidlo na overenie údajov založené na funkcii SEARCH.
V zobrazenom príklade je overenie údajov použité na B5: B11:
=SUMPRODUCT(--ISNUMBER(SEARCH(list,B5)))=0
Vysvetlenie
Pravidlá overovania údajov sa spustia, keď používateľ pridá alebo zmení hodnotu bunky.
Tento vzorec využíva funkciu VYHĽADÁVANIE na testovanie vstupu používateľa pre každú hodnotu v pomenovanom rozsahu „zoznam“. Logika vyhľadávania je „obsahuje“ - keď sa nájde hodnota zo „zoznamu“, funkcia SEARCH vráti pozíciu hodnoty ako číslo. Ak nenájde, funkcia SEARCH vráti chybu.
Funkcia ISNUMBER potom prevádza čísla na TRUE a chyby na FALSE a operátor dvojitého záporného čísla zmení hodnoty TRUE FALSE na 1s a nuly. Pretože pomenovaný rozsah „list“ obsahuje 5 hodnôt, dostaneme späť 5 výsledkov v poli ako je tento:
(0; 0; 0; 0; 0)
SUMPRODUCT potom sčíta položky v poli a výsledok sa testuje proti nule. Pokiaľ sú všetky položky nulové, program SUMPRODUCT vráti nulu a overenie bude úspešné. Ak SUMPRODUCT vráti iné číslo (tj. Keď sa nájde položka v „zozname“), vráti vzorec FALSE a overenie zlyhá.
Poznámka: Odkazy na bunky vo vzorcoch na overenie údajov sú relatívne k ľavej ľavej bunke v rozsahu vybratom, keď je definované pravidlo overovania, v tomto prípade B5.
