Generisk formel
(=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(exclude),data))),ROW(exclude)^0)>0)))
Sammendrag
For å telle celler som ikke inneholder mange forskjellige strenger, kan du bruke en ganske kompleks formel basert på MMULT-funksjonen. I eksemplet vist er formelen i F5:
(=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(exclude),data))),ROW(exclude)^0)>0)))
der "data" er det navngitte området B5: B14, og "ekskluder" er det navngitte området D5: D7.
Merk: dette er en matriseformel og må angis med kontroll + skift + enter
Forord
Denne formelen kompliseres av kravet "inneholder". Hvis du bare trenger en formel for å telle celler som ikke * tilsvarer * mange ting, kan du bruke en mer enkel formel basert på MATCH-funksjonen. Hvis du har et begrenset antall strenger å ekskludere, kan du også bruke COUNTIFS-funksjonen slik:
=COUNTIFS(data,"*pink*",data,"*orange*",data,"*black*")
Imidlertid, med denne tilnærmingen, må du oppgi et nytt par av rekkevidde / kriterier for hver streng som skal ekskluderes. I motsetning kan formelen forklart nedenfor håndtere et stort antall strenger for å ekskludere angitt direkte på regnearket.
Til slutt er denne formelen kompleks. Gi meg beskjed hvis du har en enklere formel å foreslå :)
Forklaring
Kjernen i denne formelen er ISNUMBER og SØK:
ISNUMBER(SEARCH(TRANSPOSE(exclude),data))
Her transponerer vi elementene i det navngitte området "ekskluder", og før deretter resultatet til SØK som "finn tekst", med "data" som "innenfor tekst". SØK-funksjonen returnerer et 2d-utvalg av SANNE og FALSE verdier, 10 rader med 3 kolonner, slik:
(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)
For hver verdi i "data" har vi tre resultater (ett per søkestreng) som enten er #VALUE feil eller tall. Tall representerer plasseringen til en funnet tekststreng, og feil representerer tekststrenger som ikke ble funnet. TRANSPOSE-funksjonen er forresten nødvendig for å generere 10 x 3-arrayet med komplette resultater.
Denne matrisen mates inn i ISNUMBER for å få TRUE FALSE verdier, som vi konverterer til 1s og 0s med en dobbel negativ (-) operator. Resultatet er en matrise som dette:
(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 inn i MMULT-funksjonen som array1. Etter reglene for matrisemultiplikasjon, må antall kolonner i array1 være lik antall rader i array2. For å generere array2 bruker vi ROW-funksjonen slik:
ROW(exclude)^0
Dette gir en matrise på 1s, 3 rader med 1 kolonne:
(1;1;1)
som går inn i MMULT som array2 . Etter matrixmultiplikasjon har vi en matrise dimensjonert for å matche de opprinnelige dataene:
(2;1;0;0;1;1;0;0;0;2)
I denne matrisen representerer et tall som ikke er null en verdi der minst en av de ekskluderte strengene er funnet. Nuller indikerer at det ikke ble funnet noen ekskluderte strenger. For å tvinge alle verdier som ikke er null til 1, bruker vi større enn null:
(2;1;0;0;1;1;0;0;0;2)>0
som skaper enda en matrise eller SANNE og FALSE verdier:
(TRUE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE)
Vårt endelige mål er å telle bare tekstverdier der det ikke ble funnet noen ekskluderte strenger, så vi må reversere disse verdiene. Vi gjør dette ved å trekke matrisen fra 1. Dette er et eksempel på boolsk logikk. Matematikkoperasjonen tvinger automatisk SANNE og FALSE verdier til 1s og 0s, og vi har endelig en matrise for å gå tilbake til SUM-funksjonen:
=SUM((0;0;1;1;0;0;1;1;1;0))
SUM-funksjonen returnerer et endelig resultat på 5.
