Excel-formel: Cell motsvarar en av många saker -

Innehållsförteckning

Generisk formel

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

Sammanfattning

Om du vill testa en cell för att se om den motsvarar en av flera saker kan du göra det med en formel som använder SUMPRODUCT-funktionen.

Sammanhang

Låt oss säga att du har en lista med textsträngar i intervallet B5: B11, och att du vill testa varje cell mot en annan lista över saker i intervallet E5: E9. Med andra ord, för varje cell i B5: B11 vill du veta: motsvarar denna cell något av sakerna i E5: E9?

Du kan börja bygga en stor formel baserad på kapslade IF-uttalanden, men en matrisformel baserad på SUMPRODUCT är ett enklare och renare tillvägagångssätt.

Lösning

Lösningen är att skapa en formel som testar flera värden och returnerar en lista med SANT / FALSKA värden. När vi väl har det kan vi bearbeta listan (en array, faktiskt) med SUMPRODUCT.

Formeln vi använder ser ut så här:

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

Förklaring

Nyckeln är detta utdrag:

--(B5=things)

som helt enkelt jämför värdet i B5 med varje värde i det namngivna intervallet "saker". Eftersom vi jämför B5 med en matris (dvs. det namngivna intervallet "saker", E5: E11) blir resultatet en matris med SANT / FALSKA värden så här:

(SANT; FALSE; FALSE; FALSE; FALSE)

Om vi har till och med en SANT i matrisen vet vi att B5 är lika med minst en sak i listan, så för att tvinga SANT / FALSKA värden till 1s och 0s använder vi en dubbel negativ (-, även kallad en dubbel unary). Efter denna tvång har vi detta:

(1; 0; 0; 0; 0)

Nu bearbetar vi resultatet med SUMPRODUCT, vilket kommer att lägga till elementen i matrisen. Om vi får något resultat som inte är noll har vi minst en matchning, så vi använder> 1 för att tvinga ett slutresultat av antingen SANT eller FALSK.

Med en hårdkodad lista

Det finns inget krav på att du använder ett intervall för din lista över saker. Om du bara letar efter ett litet antal saker kan du använda en lista i array-format, som kallas en arraykonstant. Om du till exempel bara letar efter färgerna röd, blå och grön kan du använda ("röd", "blå", "grön") så här:

--(B5=("red","blue","green"))

Hantera extra utrymmen

Om cellerna du testar innehåller något extra utrymme matchar de inte ordentligt. För att ta bort allt extra utrymme kan du ändra formeln för att använda TRIM-funktionen så: