Generisk formel
=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))>0
Resumé
For at teste en celle for at se, om den indeholder en af mange strenge, kan du bruge en formel baseret på funktionerne SØG, ISNUMBER og SUMPRODUCT. Formlen i C5, kopieret ned, er:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))>0
hvor ting er det navngivne interval E5: E9.
Forklaring
Vi ønsker at teste hver celle i B5: B11 for at se, om den indeholder nogen af de strenge i det navngivne område ting (E5: E9). Formlen, vi bruger i C5, kopieret ned, er:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))>0
Denne formel er baseret på en formel (forklaret her), der kontrollerer en celle for en enkelt understreng. Hvis cellen indeholder understrengen, returnerer formlen SAND. Hvis ikke, returnerer formlen FALSK:
ISNUMBER(SEARCH(things,B5))
Men i dette tilfælde giver vi SØG en liste over strenge. Da der er 5 strenge i ting , returnerer SEARCH 5 resultater i en matrix som denne:
(1;#VALUE!;#VALUE!;#VALUE!;#VALUE!)
Når SEARCH finder en streng, returnerer den strengens position. Hvis SEARCH ikke finder en streng, returnerer den en #VALUE! fejl. Fordi "gul" vises som det første ord i B5, ser vi en 1. Fordi de andre strenge ikke findes, er de andre 4 elementer fejl.
Denne matrix returneres direkte til ISNUMBER-funktionen. ISNUMBER returnerer derefter en matrix med SAND / FALSK-værdier:
(TRUE;FALSE;FALSE;FALSE;FALSE)
Hvis vi har en SAND i matrixen, ved vi, at en celle indeholder mindst en af strengene, vi leder efter. Den nemmeste måde at kontrollere for SAND er at tilføje alle værdier sammen. Vi kan gøre det med SUMPRODUCT, men først skal vi tvinge de SANDE / FALSKE værdier til 1s og 0s med en dobbelt negativ (-) som denne:
--ISNUMBER(SEARCH(things,B5))
Dette giver et nyt array, der kun indeholder 1s og 0s:
(1;0;0;0;0)
leveret direkte til SUMPRODUCT:
=SUMPRODUCT((1;0;0;0;0))
Med kun et array at behandle tilføjer SUMPRODUCT elementerne i arrayet og returnerer et resultat. Ethvert resultat, der ikke er nul, betyder, at vi har et "hit", så vi tilføjer> 0 for at tvinge det endelige resultat af SAND eller FALSK:
=SUMPRODUCT((1;0;0;0;0))>0 // returns TRUE
Med en hårdkodet liste
Det er ikke nødvendigt at bruge et interval til listen over strenge, du skal kigge efter. Du kan også bruge en matrixkonstant. For at kontrollere for "rød", "blå" og "grøn" kan du f.eks. Bruge en formel som denne:
=SUMPRODUCT(--ISNUMBER(SEARCH(("red","blue","green"),B5)))>0
Forebyggelse af falske kampe
Et problem med denne tilgang er, at du kan få falske matches fra understrenge, der vises i længere ord. For eksempel, hvis du prøver at matche "dr", kan du også finde "Andrea", "drikke", "tør" osv., Da "dr" vises inde i disse ord. Dette sker, fordi SEARCH automatisk matcher en "indeholder".
For et hurtigt hack kan du tilføje plads omkring søgeordene (dvs. "dr" eller "dr") for at undgå at fange "dr" i et andet ord. Men dette mislykkes, hvis "dr" vises først eller sidst i en celle eller vises med tegnsætning.
Hvis du har brug for en mere nøjagtig løsning, er en mulighed at normalisere teksten først i en hjælpekolonne og passe på at tilføje et ledende og efterfølgende rum. Derefter bruger du formlen på denne side på den resulterende tekst.
