Generieke formule
(=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(exclude),data))),ROW(exclude)^0)>0)))
Samenvatting
Om cellen te tellen die niet veel verschillende strings bevatten, kunt u een vrij complexe formule gebruiken op basis van de MMULT-functie. In het getoonde voorbeeld is de formule in F5:
(=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(exclude),data))),ROW(exclude)^0)>0)))
waar "data" het benoemde bereik B5: B14 is, en "uitsluiten" het benoemde bereik D5: D7 is.
Opmerking: dit is een matrixformule en moet worden ingevoerd met control + shift + enter
Voorwoord
Deze formule wordt bemoeilijkt door de "bevat" -vereiste. Als u alleen een formule nodig heeft om cellen te tellen die niet * gelijk zijn aan * veel dingen, kunt u een eenvoudiger formule gebruiken op basis van de MATCH-functie. Als u een beperkt aantal strings heeft om uit te sluiten, kunt u de AANTAL.ALS-functie als volgt gebruiken:
=COUNTIFS(data,"*pink*",data,"*orange*",data,"*black*")
Bij deze benadering moet u echter een nieuw paar bereik- / criteriumargumenten invoeren voor elke uit te sluiten string. De formule die hieronder wordt uitgelegd, kan daarentegen een groot aantal strings aan om uit te sluiten die rechtstreeks op het werkblad worden ingevoerd.
Ten slotte is deze formule complex. Laat het me weten als je een eenvoudigere formule hebt om voor te stellen :)
Uitleg
De kern van deze formule is ISNUMBER en SEARCH:
ISNUMBER(SEARCH(TRANSPOSE(exclude),data))
Hier transponeren we de items in het benoemde bereik "uitsluiten", en voeren het resultaat vervolgens naar SEARCH als de "zoektekst", met "data" als "binnen tekst". De functie SEARCH retourneert een 2d array van TRUE en FALSE waarden, 10 rijen bij 3 kolommen, als volgt:
(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)
Voor elke waarde in "data" hebben we 3 resultaten (één per zoekreeks) die #VALUE-fouten of getallen zijn. Getallen vertegenwoordigen de positie van een gevonden tekstreeks, en fouten vertegenwoordigen tekstreeksen die niet zijn gevonden. Overigens is de functie TRANSPONEREN nodig om de 10 x 3 reeks volledige resultaten te genereren.
Deze array wordt in ISNUMBER ingevoerd om TRUE FALSE-waarden te krijgen, die we converteren naar 1s en 0s met een dubbele negatieve (-) operator. Het resultaat is een array als deze:
(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)
die in de MMULT-functie gaat als array1. Volgens de regels van matrixvermenigvuldiging, moet het aantal kolommen in array1 gelijk zijn aan het aantal rijen in array2. Om array2 te genereren , gebruiken we de ROW-functie als volgt:
ROW(exclude)^0
Dit levert een array op van 1s, 3 rijen bij 1 kolom:
(1;1;1)
die in MMULT gaat als array2 . Na arrayvermenigvuldiging hebben we een array gedimensioneerd om overeen te komen met de originele gegevens:
(2;1;0;0;1;1;0;0;0;2)
In deze array vertegenwoordigt elk getal dat niet nul is een waarde waarin ten minste een van de uitgesloten strings is gevonden. Nullen geven aan dat er geen uitgesloten strings zijn gevonden. Om alle niet-nulwaarden naar 1 te forceren, gebruiken we groter dan nul:
(2;1;0;0;1;1;0;0;0;2)>0
die nog een andere array of TRUE en FALSE waarden creëert:
(TRUE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE)
Ons uiteindelijke doel is om alleen tekstwaarden te tellen waar geen uitgesloten strings zijn gevonden, dus we moeten deze waarden omkeren. We doen dit door de array van 1 af te trekken. Dit is een voorbeeld van booleaanse logica. De wiskundige bewerking dwingt automatisch TRUE en FALSE waarden naar 1s en 0s, en we hebben eindelijk een array om terug te keren naar de SUM-functie:
=SUM((0;0;1;1;0;0;1;1;1;0))
De functie SOM retourneert het eindresultaat van 5.
