Generisk formel
(=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(exclude),data))),ROW(exclude)^0)>0)))
Resumé
For at tælle celler, der ikke indeholder mange forskellige strenge, kan du bruge en ret kompleks formel baseret på MMULT-funktionen. I det viste eksempel er formlen i F5:
(=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(exclude),data))),ROW(exclude)^0)>0)))
hvor "data" er det navngivne område B5: B14, og "ekskluder" er det navngivne område D5: D7.
Bemærk: dette er en matrixformel og skal indtastes med kontrol + skift + enter
Forord
Denne formel kompliceres af kravet "indeholder". Hvis du bare har brug for en formel til at tælle celler, der ikke * svarer * til mange ting, kan du bruge en mere ligetil formel baseret på MATCH-funktionen. Hvis du har et begrænset antal strenge, der skal udelades, kan du også bruge COUNTIFS-funktionen som denne:
=COUNTIFS(data,"*pink*",data,"*orange*",data,"*black*")
Men med denne tilgang skal du indtaste et nyt par rækkevidde / kriterierargumenter for hver streng, der skal ekskluderes. I modsætning hertil kan formlen forklaret nedenfor håndtere et stort antal strenge for at udelukke indtastet direkte på regnearket.
Endelig er denne formel kompleks. Lad mig vide, hvis du har en enklere formel at foreslå :)
Forklaring
Kernen i denne formel er ISNUMBER og SØG:
ISNUMBER(SEARCH(TRANSPOSE(exclude),data))
Her transponerer vi elementerne i det navngivne interval "ekskluder", og før derefter resultatet til SØG som "find tekst" med "data" som "inden for tekst". SEARCH-funktionen returnerer et 2d-array med SAND og FALSK-værdier, 10 rækker med 3 kolonner, som denne:
(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 værdi i "data" har vi 3 resultater (en pr. Søgestreng), der enten er #VALUE fejl eller tal. Tal repræsenterer placeringen af en fundet tekststreng, og fejl repræsenterer tekststrenge, der ikke blev fundet. Forresten er TRANSPOSE-funktionen nødvendig for at generere 10 x 3-arrayet med komplette resultater.
Denne matrix indføres i ISNUMBER for at få SAND FALSE-værdier, som vi konverterer til 1s og 0s med en dobbelt negativ (-) operator. Resultatet er en matrix som denne:
(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 ind i MMULT-funktionen som array1. Efter reglerne for matrixmultiplikation skal antallet af kolonner i array1 være lig med antallet af rækker i array2. For at generere array2 bruger vi ROW-funktionen på denne måde:
ROW(exclude)^0
Dette giver en matrix på 1s, 3 rækker med 1 kolonne:
(1;1;1)
som går ind i MMULT som array2 . Efter matrixmultiplikation har vi en matrix dimensioneret til at matche de oprindelige data:
(2;1;0;0;1;1;0;0;0;2)
I dette array repræsenterer ethvert tal, der ikke er nul, en værdi, hvor mindst en af de udelukkede strenge er fundet. Nuller angiver, at der ikke blev fundet nogen udelukkede strenge. For at tvinge alle værdier, der ikke er nul, til 1, bruger vi større end nul:
(2;1;0;0;1;1;0;0;0;2)>0
som skaber endnu et array eller SAND og FALSK værdier:
(TRUE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE)
Vores endelige mål er kun at tælle tekstværdier, hvor der ikke blev fundet nogen udelukkede strenge, så vi er nødt til at vende disse værdier. Vi gør dette ved at trække arrayet fra 1. Dette er et eksempel på boolsk logik. Matematikoperationen tvinger automatisk TRUE og FALSE-værdier til 1s og 0s, og vi har endelig en matrix til at vende tilbage til SUM-funktionen:
=SUM((0;0;1;1;0;0;1;1;1;0))
SUM-funktionen returnerer et slutresultat på 5.
