Celle er lig med en af ​​mange ting

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 funktionen SUMPRODUCT.

tastaturgenvej for at indsætte navne på intervaller

Sammenhæng

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

Du kan begynde at bygge en stor formel baseret på indlejrede IF -erklæringer , men en matrixformel baseret på SUMPRODUCT er en enklere og renere tilgang.

Løsning

Løsningen er at oprette en formel, der tester for flere værdier og returnerer 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 (--(A1=things))>0

Nøglen er denne uddrag:

 
= SUMPRODUCT (--(B5=things))>0

som simpelthen sammenligner værdien i B5 med hver værdi i det navngivne område 'ting'. Fordi vi sammenligner B5 med en matrix (dvs. det navngivne område 'ting', E5: E11), vil resultatet være en række SANDE / FALSKE værdier som dette:

{TRUEFALSEFALSEFALSEFALSE}

Hvis vi har endda en SAND i arrayet, ved vi, at B5 er lig med mindst én ting på listen, så for at tvinge de SANDE / FALSKE 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 vil tilføje elementerne i arrayet. Hvis vi får et resultat uden nul, har vi mindst en kamp, ​​så vi bruger> 1 til at tvinge et slutresultat på enten SAND eller FALSK.

Med en hårdkodet liste

Der er ikke noget krav om, at du bruger et område til din liste over ting. Hvis du kun leder efter et lille antal ting, kan du bruge en liste i matrixformat, som 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åledes:

 
--(B5=things)

Beskæftiger sig med ekstra rum

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

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