Generisk formel
=SUMPRODUCT(--ISNUMBER(SEARCH(list,A1)))=0
Sammendrag
For å ikke tillate inndata som inneholder en av mange ting, kan du bruke en tilpasset datavalideringsregel basert på SØK-funksjonen.
I eksemplet som vises er datavalidering som brukes på B5: B11:
=SUMPRODUCT(--ISNUMBER(SEARCH(list,B5)))=0
Forklaring
Datavalideringsregler utløses når en bruker legger til eller endrer en celleverdi.
Denne formelen bruker SØK-funksjonen til å teste brukerinngang for hver verdi i det nevnte området "liste". Søkelogikken er "inneholder" - når en verdi fra "liste" blir funnet, returnerer SØK posisjonen til verdien som et tall. Hvis den ikke blir funnet, returnerer SEARCH en feil.
ISNUMBER-funksjonen konverterer deretter tall til SANT og feil til FALSE, og den doble negative operatoren endrer SANNE FALSE-verdier til 1s og nuller. Siden det nevnte området "liste" inneholder 5 verdier, får vi tilbake 5 resultater i en matrise som dette:
(0; 0; 0; 0; 0)
SUMPRODUCT oppsummerer deretter elementene i matrisen, og resultatet testes mot null. Så lenge alle elementene er null, returnerer SUMPRODUCT null og valideringen lykkes. Hvis SUMPRODUCT returnerer et annet nummer (dvs. når et element i "liste" blir funnet), returnerer formelen FALSE og validering mislykkes.
Merk: Cellereferanser i datavalideringsformler er i forhold til den øverste venstre cellen i området som er valgt når valideringsregelen er definert, i dette tilfellet B5.
