Excel

Celle indeholder en af ​​mange ting

Cell Contains One Many Things

Excel-formel: Celle indeholder en af ​​mange tingGenerisk formel
= SUMPRODUCT (-- ISNUMBER ( SEARCH (things,A1)))>0
Resumé

For at teste, om en celle indeholder en af ​​mange strenge, kan du bruge en formel baseret på SØG , ISNUMBER og SUMPRODUKT funktioner. Formlen i C5, kopieret ned, er:

 
= SUMPRODUCT (-- ISNUMBER ( SEARCH (things,B5)))>0

hvor ting er navngivet rækkevidde E5: E7.



Forklaring

Målet med dette eksempel er at teste hver celle i B5: B14 for at se, om den indeholder nogen af ​​strengene i navngivet rækkevidde ting (E5: E7). Disse strenge kan vises overalt i cellen, så dette er et bogstaveligt 'indeholder' problem. Formlen i C5, kopieret ned, er:



 
= SUMPRODUCT (-- ISNUMBER ( SEARCH (things,B5)))>0

Denne formel er baseret på en anden formel der kontrollerer en celle for et enkelt underlag. Hvis cellen indeholder understrengen, returnerer formlen SAND. Hvis ikke, returnerer formlen FALSK:

 
 ISNUMBER ( SEARCH (substring,B5)) // test for substring

Når SØG-funktion finder en streng, returnerer den strengens position som et tal. Hvis SØG ikke find en streng, den returnerer en #VÆRDI! fejl. Dette betyder, at ISNUMBER returnerer SAND, hvis der er en kamp, ​​og FALSK, hvis ikke.



I dette eksempel er målet at kontrollere mere end én streng, så vi giver funktionen SØG en liste over strenge i navngivet rækkevidde ting . Da der er 3 strenge i ting ('rød', 'grøn' og 'blå'), SØG returnerer 3 resultater i et array sådan her:

excel roundup til næste hele tal
 
{#VALUE!#VALUE!23}

Fordi 'rød' og 'blå' ikke findes, returnerer SØGNINGEN en #VÆRDI! fejl. Men fordi 'grøn' vises nær slutningen af ​​teksten i celle B5, returnerer SØG 23 (dvs. 'grøn' begynder med det 23. tegn).

Denne matrix returneres direkte til ISNUMBER-funktion , der konverterer elementerne i arrayet til enten TRUE eller FALSE:



 
 ISNUMBER ({#VALUE!#VALUE!23}) // returns {FALSEFALSETRUE}

Logisk set, hvis vi endda 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-funktion , men først skal vi tvinge de SANDE / FALSKE værdier til 1s og 0s med a dobbelt negativ (--) sådan her:

 
--{FALSEFALSETRUE} // coerce to 1s and 0s

Dette giver et nyt array, der kun indeholder 1 og 0:

hvordan man tilføjer solver til excel
 
{001}

som leveres direkte til SUMPRODUCT:

 
= SUMPRODUCT ({001}) // returns 1

Med kun en matrix, der skal behandles, summerer SUMPRODUCT elementerne i matrixen 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 ({001})>0 // returns TRUE

Bemærk, at enhver kombination af matches returnerer et tal større end nul og får formlen til at returnere 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 array konstant . For at kontrollere for 'rød', 'blå' eller 'grøn' kan du f.eks. Bruge en formel som denne:

 
= SUMPRODUCT (-- ISNUMBER ( SEARCH ({'red','blue','green'},B5)))>0

SUM-funktion

Historisk set SUMPRODUKT vises ofte i matrixformler , fordi det kan håndtere arrays indbygget, uden kontrol + skift + enter . Dette gør formlen 'mere venlig' for de fleste brugere. I Excel 365 , som håndterer arrays indfødt , det SUM-funktion kan bruges i stedet for SUMPRODUCT uden kontrol + shift + enter:

hvordan man bruger matchfunktionen i Excel
 
= SUM (-- ISNUMBER ( SEARCH (things,A1)))>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 normaliser teksten først i en hjælper kolonne , sørger for også at tilføje et førende og bageste rum. Derefter bruger du formlen på denne side på den resulterende tekst.

Vedhæftede filer Fil celle indeholder en af ​​mange ting. xlsx Forfatter Dave Bruns


^