Aantal cellen tellen dat niet veel waarden bevat

Normaal gesproken is het eenvoudig om cellen te tellen die één specifieke waarde niet bevatten met de functie AANTAL.ALS. In dit artikel wordt uitgelegd hoe je het aantal cellen kunt tellen dat niet veel waarden bevat in een opgegeven bereik in Excel.

Hoe tel je cellen die niet veel waarden bevatten?

Zoals in de onderstaande schermafbeelding te zien is, om cellen in B3:B11 te tellen die geen waarden bevatten die vermeld staan in D3:D4, kun je als volgt te werk gaan.

Algemene formule

{=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(criteria_range),range))),ROW(criteria_range)^0)>0))}

Argumenten

Opmerking: Deze formule moet worden ingevoerd als een matrixformule. Nadat je de formule hebt toegepast, als er accolades om de formule heen staan, is een matrixformule succesvol gemaakt.

Hoe gebruik je deze formule?

1. Selecteer een lege cel om het resultaat weer te geven.

2. Voer de onderstaande formule in en druk vervolgens tegelijkertijd op de toetsen Ctrl + Shift + Enter om het resultaat te krijgen.

=SOM(1-(MMULT(--(ISGETAL(ZOEKEN(TRANSPONEREN(D3:D4);B3:B11)));RIJ(D3:D4)^0)>0))

Hoe werken deze formules?

=SOM(1-(MMULT(--(ISGETAL(ZOEKEN(TRANSPONEREN(D3:D4);B3:B11)));RIJ(D3:D4)^0)>0))

1) --(ISGETAL(ZOEKEN(TRANSPONEREN(D3:D4);B3:B11))):

• TRANSPONEREN(D3:D4): De TRANSPONEREN-functie draait de oriëntatie van D3:D4 en retourneert {“count”,”blank”};
• ZOEKEN({“count”,”blank”};B3:B11): De ZOEKEN-functie zoekt hier de positie van de substring “count” en “blank” in het bereik B3:B11 en retourneert een array zoals {#WAARDE!;#WAARDE!;#WAARDE!;#WAARDE!;1;#WAARDE!;#WAARDE!;8;1;#WAARDE!;#WAARDE!;#WAARDE!;1;
  #WAARDE!;1;7}.
• In dit geval zal elke cel in B3:B11 tweemaal doorzocht worden omdat er twee waarden zijn die je wilt uitsluiten bij het tellen van cellen, dus krijg je 18 waarden in de array. Elk getal in de array geeft de positie aan van het eerste teken van “count” of “blank” in elke cel van B3:B11.
• ISGETAL{#WAARDE!;#WAARDE!;#WAARDE!;#WAARDE!;1;#WAARDE!;#WAARDE!;8;1;#WAARDE!;#WAARDE!;#WAARDE!;1;
  #WAARDE!;1;7}: De ISGETAL-functie retourneert WAAR als er getallen in de array worden gevonden, en ONWAAR als er fouten worden gevonden. Hier wordt het resultaat geretourneerd als {ONWAAR;ONWAAR;WAAR;ONWAAR;WAAR;ONWAAR;ONWAAR;ONWAAR;ONWAAR;WAAR;ONWAAR;ONWAAR;WAAR;WAAR}
  TRUE,TRUE}.
• --({FALSE,FALSE;FALSE,FALSE;TRUE,FALSE;FALSE,TRUE;TRUE,FALSE;FALSE,FALSE;FALSE,FALSE;TRUE,
  FALSE;TRUE,TRUE}):Deze twee mintekens zetten “WAAR” om in 1 en “ONWAAR” om in 0. Hier krijg je een nieuwe array als {0,0;0,0;1,0;0,1;1,0;0,0;0,0;1,0;1,1}.

2) RIJ(D3:D4)^0: De RIJ-functie retourneert de rijnummers van de celverwijzing: {3;4}, en vervolgens retourneert de machtsverheffingsoperator (^) het resultaat van de getallen 3 en 4 verheven tot de macht 0, en retourneert uiteindelijk het resultaat als {1;1}.

3) MMULT({0,0;0,0;1,0;0,1;1,0;0,0;0,0;1,0;1,1},{1;1}): De MMULT-functie retourneert het matrixproduct van deze twee arrays: {0;0;1;1;1;0;0;1;2} om aan de originele data te voldoen. Elk niet-nulgetal in de array vertegenwoordigt een waarde waarbij minstens één van de uitgesloten strings gevonden is, en nul geeft aan dat er geen uitgesloten strings gevonden zijn.

4) SOM(1-{0;0;1;1;1;0;0;1;2}>0):

• {0;0;1;1;1;0;0;1;2}>0:Hier wordt gecontroleerd of elk getal in de array groter is dan 0. Als een getal groter is dan 0, retourneert het WAAR, zo niet, retourneert het ONWAAR. Vervolgens krijg je een nieuwe array als {ONWAAR;ONWAAR;WAAR;WAAR;WAAR;ONWAAR;ONWAAR;WAAR;WAAR}.
• 1-{ONWAAR;ONWAAR;WAAR;WAAR;WAAR;ONWAAR;ONWAAR;WAAR;WAAR}:Omdat we alleen de cellen moeten tellen die de opgegeven waarden niet bevatten, moeten we deze waarden in de array omkeren door ze af te trekken van 1. Hier converteert de wiskundige operator automatisch WAAR- en ONWAAR-waarden naar 1-en en 0-en, en retourneert uiteindelijk het resultaat als {1;1;0;0;0;1;1;0;0}.
• SOM{1;1;0;0;0;1;1;0;0}: De SOM-functie somt alle getallen in de array op en retourneert het eindresultaat als 4.

Gerelateerde functies

Gerelateerde formules