Excel

Celle er lig med en af ​​mange ting

Cell Equals One Many Things

Excel-formel: Celle svarer til en af ​​mange tingGenerisk formel
= SUMPRODUCT (--(A1=things))>0
Resumé

Hvis du vil teste en celle for at se, om den er lig med en af ​​flere ting, kan du gøre det med en formel, der bruger SUMPRODUCT-funktionen.

tastaturgenvej for at indsætte navne på intervaller

Sammenhæng

Lad os sige, at du har en liste over tekststrenge i området B5: B11, og at du vil teste hver celle mod en anden liste over ting i området E5: E9. Med andre ord, for hver celle i B5: B11 vil du vide: svarer denne celle til noget af tingene i E5: E9?



Du kan begynde at opbygge en stor formel baseret på nestede IF-udsagn , men en matrixformel baseret på SUMPRODUCT er en enklere og renere tilgang.



Opløsning

Løsningen er at oprette en formel, der vil teste for flere værdier og returnere en liste med SAND / FALSK-værdier. Når vi har det, kan vi behandle denne liste (en matrix faktisk) med SUMPRODUCT.

Formlen, vi bruger, ser sådan ud:



hvordan man bruger eksponentiel udjævning i Excel
 
= SUMPRODUCT (--(B5=things))>0
Forklaring

Nøglen er dette uddrag:

 
--(B5=things)

der blot sammenligner værdien i B5 med hver værdi i det navngivne interval 'ting'. Da vi sammenligner B5 med en matrix (dvs. det navngivne interval 'ting', E5: E11), vil resultatet være en matrix med SAND / FALSK-værdier som denne:

{TRUEFALSEFALSEFALSEFALSE}



Hvis vi selv har en SAND i matrixen, ved vi, at B5 svarer til mindst én ting på listen, så for at tvinge de SANDE / FALSE-værdier til 1s og 0s bruger vi en dobbelt negativ (-, også kaldet en dobbelt unary). Efter denne tvang har vi dette:

{10000}

hvordan man gør en forskel i excel

Nu behandler vi resultatet med SUMPRODUCT, som tilføjer elementerne i arrayet. Hvis vi får et resultat, der ikke er nul, har vi mindst en kamp, ​​så vi bruger> 1 til at tvinge et slutresultat af enten SAND eller FALSK.

Med en hårdkodet liste

Der er ikke noget krav om, at du bruger et interval til din liste over ting. Hvis du kun leder efter et lille antal ting, kan du bruge en liste i matrixformat, der kaldes en matrixkonstant. Hvis du f.eks. Bare leder efter farverne rød, blå og grøn, kan du bruge {'rød', 'blå', 'grøn'} sådan:

 
--(B5={'red','blue','green'})

Håndtering af ekstra mellemrum

Hvis cellerne, du tester, indeholder ekstra plads, matcher de ikke korrekt. For at fjerne alt ekstra plads kan du ændre formlen for at bruge TRIM-funktionen som sådan:

 
= SUMPRODUCT (--( TRIM (A1)=things))>0
Forfatter Dave Bruns


^