Generisk formel
(=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(exclude),data))),ROW(exclude)^0)>0)))
Sammanfattning
För att räkna celler som inte innehåller många olika strängar kan du använda en ganska komplex formel baserad på MMULT-funktionen. I exemplet som visas är formeln i F5:
(=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(exclude),data))),ROW(exclude)^0)>0)))
där "data" är det namngivna området B5: B14 och "exkludera" är det namngivna området D5: D7.
Obs: detta är en matrisformel och måste anges med kontroll + skift + enter
Förord
Denna formel kompliceras av kravet "innehåller". Om du bara behöver en formel för att räkna celler som inte * motsvarar * många saker, kan du använda en enklare formel baserad på MATCH-funktionen. Om du har ett begränsat antal strängar att utesluta kan du också använda COUNTIFS-funktionen så här:
=COUNTIFS(data,"*pink*",data,"*orange*",data,"*black*")
Men med den här metoden måste du ange ett nytt par av intervall / kriterier för varje sträng att utesluta. Däremot kan formeln som förklaras nedan hantera ett stort antal strängar för att exkludera direkt i kalkylbladet.
Slutligen är denna formel komplex. Låt mig veta om du har en enklare formel att föreslå :)
Förklaring
Kärnan i denna formel är ISNUMBER och SÖK:
ISNUMBER(SEARCH(TRANSPOSE(exclude),data))
Här transponerar vi objekten i det namngivna intervallet "exkludera" och matar sedan resultatet till SÖK som "hitta text", med "data" som "inom text". SÖK-funktionen returnerar en 2d-array med SANT och FALSKA värden, 10 rader med 3 kolumner, så här:
(3,#VALUE!,12;#VALUE!,4,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,3;14,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;3,#VALUE!,12)
För varje värde i "data" har vi tre resultat (ett per söksträng) som antingen är #VALUE-fel eller siffror. Siffror representerar positionen för en hittad textsträng och fel representerar textsträngar som inte hittades. Förresten, TRANSPOSE-funktionen behövs för att generera 10 x 3-arrayen med kompletta resultat.
Denna matning matas in i ISNUMBER för att få Sanna FALSKA värden, som vi konverterar till 1s och 0s med en dubbel negativ (-) operator. Resultatet är en array som denna:
(1,0,1;0,1,0;0,0,0;0,0,0;0,0,1;1,0,0;0,0,0;0,0,0;0,0,0;1,0,1)
som går in i MMULT-funktionen som array1. Efter reglerna för matrixmultiplikation måste antalet kolumner i array1 vara lika med antalet rader i array2. För att generera array2 använder vi ROW-funktionen så här:
ROW(exclude)^0
Detta ger en matris med 1s, 3 rader med 1 kolumn:
(1;1;1)
som går in i MMULT som array2 . Efter multiplikation av array har vi en array dimensionerad för att matcha originaldata:
(2;1;0;0;1;1;0;0;0;2)
I denna matris representerar alla tal som inte är noll ett värde där minst en av de uteslutna strängarna har hittats. Nollor indikerar att inga uteslutna strängar hittades. För att tvinga alla värden som inte är noll till 1 använder vi större än noll:
(2;1;0;0;1;1;0;0;0;2)>0
vilket skapar ytterligare en array eller SANT och FALSKA värden:
(TRUE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE)
Vårt slutmål är att bara räkna textvärden där inga uteslutna strängar hittades, så vi måste vända dessa värden. Vi gör detta genom att subtrahera matrisen från 1. Detta är ett exempel på boolesk logik. Matematikoperationen tvingar automatiskt SANT och FALSKT till 1s och 0s, och vi har äntligen en matris för att återgå till SUM-funktionen:
=SUM((0;0;1;1;0;0;1;1;1;0))
SUM-funktionen returnerar ett slutresultat på 5.
