Splošna formula
=SUMPRODUCT(--ISNUMBER(SEARCH(list,A1)))=0
Povzetek
Če želite onemogočiti vnos, ki vsebuje eno od mnogih stvari, lahko uporabite pravilo za preverjanje veljavnosti podatkov po meri, ki temelji na funkciji SEARCH.
V prikazanem primeru je preverjanje veljavnosti podatkov, uporabljeno za B5: B11, naslednje:
=SUMPRODUCT(--ISNUMBER(SEARCH(list,B5)))=0
Pojasnilo
Pravila za preverjanje veljavnosti podatkov se sprožijo, ko uporabnik doda ali spremeni vrednost celice.
Ta formula uporablja funkcijo SEARCH za preizkušanje uporabniškega vnosa za vsako vrednost v imenovanem obsegu "seznam". Logika iskanja je "vsebuje" - ko je najdena vrednost s "seznama", SEARCH vrne položaj vrednosti kot število. Če ga ne najdete, SEARCH vrne napako.
Nato funkcija ISNUMBER pretvori številke v TRUE in napake v FALSE, dvojni negativni operator pa spremeni TRUE FALSE vrednosti na 1s in ničle. Ker imenovani obseg "seznam" vsebuje 5 vrednosti, dobimo 5 rezultatov v matriki, kot je ta:
(0; 0; 0; 0; 0)
Nato SUMPRODUCT povzame elemente v matriki in rezultat se preskusi proti ničli. Dokler so vsi elementi nič, SUMPRODUCT vrne nič in validacija uspe. Če SUMPRODUCT vrne drugo številko (tj. Ko najde element na "seznamu"), formula vrne FALSE in preverjanje ne uspe.
Opomba: Sklici na celice v formulah za preverjanje veljavnosti podatkov se nanašajo na zgornjo levo celico v obsegu, izbranem, ko je določeno pravilo preverjanja veljavnosti, v tem primeru B5.
