Generička formula
=SUMPRODUCT(--(A1=things))>0
Sažetak
Ako želite testirati ćeliju kako biste vidjeli je li jednaka jednoj od nekoliko stvari, to možete učiniti pomoću formule koja koristi funkciju SUMPRODUCT.
Kontekst
Recimo da imate popis tekstualnih nizova u rasponu B5: B11 i želite testirati svaku ćeliju na drugom popisu stvari u rasponu E5: E9. Drugim riječima, za svaku ćeliju u B5: B11 želite znati: odgovara li ta stanica nekoj od stvari u E5: E9?
Mogli biste početi graditi veliku formulu na temelju ugniježđenih IF izraza, ali formula niza temeljena na SUMPRODUCT jednostavniji je i čistiji pristup.
Riješenje
Rješenje je stvoriti formulu koja će testirati više vrijednosti i vratiti popis TRUE / FALSE vrijednosti. Jednom kad to imamo, možemo taj popis (niz, zapravo) obraditi pomoću SUMPRODUCT.
Formula koju koristimo izgleda ovako:
=SUMPRODUCT(--(B5=things))>0
Obrazloženje
Ključ je ovog isječka:
--(B5=things)
koji jednostavno uspoređuje vrijednost u B5 sa svakom vrijednošću u imenovanom rasponu "stvari". Budući da uspoređujemo B5 s nizom (tj. Imenovani raspon "stvari", E5: E11), rezultat će biti niz TRUE / FALSE vrijednosti poput ove:
(ISTINITO; NETOČNO; NETOČNO; NETOČNO; NETOČNO)
Ako u polju imamo čak i jednu ISTINU, znamo da je B5 jednaka barem jednoj stvari na popisu, pa, da bismo prisilili vrijednosti TRUE / FALSE na 1s i 0s, koristimo dvostruku negativu (-, naziva se i dvostrukom unarni). Nakon ove prisile imamo sljedeće:
(1; 0; 0; 0; 0)
Sada obrađujemo rezultat sa SUMPRODUCT, koji će zbrajati elemente u polju. Ako dobijemo bilo koji rezultat koji nije nula, imamo barem jedno podudaranje, pa koristimo> 1 za forsiranje konačnog rezultata ili TRUE ili FALSE.
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:
--(B5=("red","blue","green"))
Suočavanje s dodatnim razmacima
Ako stanice koje testirate sadrže dodatni prostor, neće se pravilno podudarati. Da biste uklonili sav dodatni prostor, možete izmijeniti formulu tako da koristi funkciju TRIM na sljedeći način:
=SUMPRODUCT(--(TRIM(A1)=things))>0
