Splošna formula
=(SUMPRODUCT(--ISNUMBER(SEARCH(include,A1)))>0) *(SUMPRODUCT(--ISNUMBER(SEARCH(exclude,A1)))=0)
Povzetek
Če želite celico preizkusiti za enega od številnih nizov, pri čemer izključite druge, lahko uporabite formulo, ki temelji na funkcijah SEARCH, ISNUMBER in SUMPRODUCT. V prikazanem primeru je formula v C5:
=(SUMPRODUCT(--ISNUMBER(SEARCH(include,B5)))>0) *(SUMPRODUCT(--ISNUMBER(SEARCH(exclude,B5)))=0)
kjer je "vključi" imenovani obseg E5: E9, "izključi" pa imenovani obseg G5: G6.
Pojasnilo
V osnovi ta formula uporablja funkcijo SEARCH za iskanje več nizov v celici. Znotraj levega SUMPRODUCT ISKANJE išče vse nize v imenovanem obsegu "vključi".
V desnem SUMPRODUCT ISKANJE išče vse nize v imenovanem obsegu "izključi".
V obeh delih formule SEARCH vrne številske položaje, ko najdemo nize, in napake, če jih ne. Funkcija ISNUMBER pretvori številke v TRUE in napake v FALSE, dvojni negativ pa TRUE FALSE vrednosti v 1 in 0.
Rezultat na tej točki je videti takole:
=(SUMPRODUCT((1;0;0;0;0))>0)*(SUMPRODUCT((0;0))=0)
Nato:
=(1>0)*(0=0) =TRUE*TRUE =1
Opomba: ta formula vrne bodisi 1 bodisi nič, kar se v formulah, pogojnem oblikovanju ali preverjanju veljavnosti obravnava kot TRUE in FALSE.
