Generička formula
=(SUMPRODUCT(--ISNUMBER(SEARCH(include,A1)))>0) *(SUMPRODUCT(--ISNUMBER(SEARCH(exclude,A1)))=0)
Sažetak
Da biste testirali ćeliju za jedan od mnogih nizova, a izuzeli ostale, možete upotrijebiti formulu koja se temelji na funkcijama SEARCH, ISNUMBER i SUMPRODUCT. U prikazanom primjeru formula u C5 je:
=(SUMPRODUCT(--ISNUMBER(SEARCH(include,B5)))>0) *(SUMPRODUCT(--ISNUMBER(SEARCH(exclude,B5)))=0)
gdje je "uključi" imenovani raspon E5: E9, a "izuzmi" imenovani raspon G5: G6.
Obrazloženje
U osnovi, ova formula koristi funkciju SEARCH za traženje višestrukih nizova unutar ćelije. Unutar lijevog SUMPRODUCT-a, SEARCH traži sve nizove u imenovanom rasponu "include".
U desnom SUMPRODUCT, SEARCH traži sve nizove u imenovanom rasponu "izuzeti".
U oba dijela formule, SEARCH vraća numeričke položaje kad se pronađu nizovi, a pogreške ako ne. Funkcija ISNUMBER pretvara brojeve u TRUE, a pogreške u FALSE, a dvostruki negativ TRUE FALSE vrijednosti u 1 i 0.
Rezultat u ovom trenutku izgleda ovako:
=(SUMPRODUCT((1;0;0;0;0))>0)*(SUMPRODUCT((0;0))=0)
Zatim:
=(1>0)*(0=0) =TRUE*TRUE =1
Napomena: ova formula vraća ili 1 ili nulu, a obrađuju se kao TRUE i FALSE u formulama, uvjetnom oblikovanju ili provjeri valjanosti podataka.
