Generička formula
=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))=COUNTA(things)
Sažetak
Ako želite testirati ćeliju kako biste vidjeli sadrži li sve stavke na popisu, to možete učiniti pomoću formule koja koristi funkciju SEARCH, uz pomoć funkcija BROJ, SUMPRODUCT i COUNTA.
Kontekst
Recimo da imate popis tekstualnih nizova u rasponu B5: B8 i želite saznati sadrže li ove ćelije sve riječi iz drugog raspona, E5: E7.
Mogli biste stvoriti formulu koja koristi ugniježđene IF izraze za provjeru svake stavke, ali to se neće dobro prilagoditi ako imate puno stvari koje trebate potražiti. Svaki put kad dodate riječ koju ćete tražiti, trebat ćete dodati još jedan ugniježđeni IF i prilagoditi zagrade.
Riješenje
Rješenje je stvoriti formulu koja broji sva podudaranja odjednom. Jednom kad to imamo, jednostavno uspoređujemo taj broj s brojem predmeta koje tražimo. Ako se podudaraju, znamo da ćelija sadrži sve stavke.
U prikazanom primjeru formula koju koristimo je:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))=COUNTA(things)
Obrazloženje
Ključ je ovog isječka:
ISNUMBER(SEARCH(things,B5)
To se temelji na drugoj formuli (koja je ovdje detaljno objašnjena) koja jednostavno provjerava u ćeliji jedan podniz. Ako ćelija sadrži podniz, formula vraća TRUE. Ako nije, formula vraća FALSE.
Međutim, ako istoj formuli damo popis stvari (u ovom slučaju koristimo imenovani raspon nazvan "stvari", E5: E7), vratit će nam popis TRUE / FALSE vrijednosti, po jednu za svaku stavku u . Rezultat je niz koji izgleda ovako:
(ISTINA; ISTINA; ISTINA)
Gdje svaka TRUE predstavlja pronađenu stavku, a svaka FALSE predstavlja stavku koja nije pronađena.
Vrijednosti TRUE / FALSE možemo prisiliti na 1s i 0s s dvostrukim negativom (-, koji se naziva i dvostrukim unarnim):
--ISNUMBER(SEARCH(things,B5))
koji daje niz poput ovog:
(1; 1; 1)
Zatim obrađujemo ovaj niz SUMPRODUCT-om, što će nam dati ukupni zbroj. Ako je ovaj zbroj jednak broju stavki u imenovanom rasponu "stvari", znamo da smo pronašli sve stvari i možemo vratiti TRUE. Način na koji to radimo je da izravno usporedimo dva broja. Dobivamo broj nepraznih stanica u "stvarima" pomoću COUNTA:
=COUNTA(things)
S tvrdim kodiranim popisom
Ne postoji zahtjev da za popis stvari koristite niz. Ako tražite samo mali broj stvari, možete upotrijebiti popis u formatu polja, koji se naziva konstanta niza. Na primjer, ako tražite samo crvenu, plavu i zelenu boju, možete koristiti ("crvena", "plava", "zelena") ovako:
=SUMPRODUCT(--ISNUMBER(SEARCH(("yellow","green","dog"),B5)))=COUNTA(things)
