Aantal cellen tellen die niet veel waarden bevatten

Normaal gesproken is het eenvoudig om cellen te tellen die niet één specifieke waarde bevatten met de AANTAL.ALS-functie. In dit bericht wordt stap voor stap uitgelegd hoe u het aantal cellen telt dat veel waarden *niet* bevat binnen een beperkt bereik in Excel.

Hoe telt u cellen die weinig waarden bevatten?

Zoals in de onderstaande afbeelding te zien is, telt u op de volgende manier het aantal cellen in B3:B11 die geen waarden bevatten uit D3:D4.

Algemene formule

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

Argumenten

Opmerking: Deze formule moet als matrixformule worden ingevoerd. Als er na het toepassen van de formule accolades rondom verschijnen, is de matrixformule succesvol ingevoerd.

Hoe gebruikt u deze formule?

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

2. Voer de onderstaande formule in en druk daarna tegelijkertijd op Ctrl+Shift+Enter om het resultaat te verkrijgen.

=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(D3:D4),B3:B11))),ROW(D3:D4)^0)>0))

Hoe werken deze formules?

=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(D3:D4),B3:B11))),ROW(D3:D4)^0)>0))

1) --(ISNUMBER(SEARCH(TRANSPOSE(D3:D4),B3:B11))):

• TRANSPOSE(D3:D4):De TRANSPONEREN-functie draait de oriëntatie van D3:D4 en geeft {„count”,„blank”};
• SEARCH({“count”,”blank”},B3:B11): De ZOEKEN-functie bepaalt hier de positie van de substrings „count” en „blank” in het bereik B3:B11 en retourneert een matrix als {#WAARDE!,#WAARDE!;#WAARDE!,#WAARDE!;1,#WAARDE!;#WAARDE!,8;1,#WAARDE!;#WAARDE!,#WAARDE!;#WAARDE!,#WAARDE!;1,
  #WAARDE!;1,7}.
• In dit geval wordt elke cel in B3:B11 twee keer doorzocht, omdat u twee waarden wilt uitsluiten bij het tellen van cellen — waardoor u 18 waarden in de matrix krijgt. Elk getal in die matrix geeft de positie aan van het eerste teken van „count” of „blank” in de betreffende cel uit B3:B11.
• ISNUMBER{#VALUE!,#VALUE!;#VALUE!,#VALUE!;1,#VALUE!;#VALUE!,8;1,#VALUE!;#VALUE!,#VALUE!;#VALUE!,
  #VALUE!;1,#VALUE!;1,7}:De ISGETAL-functie retourneert WAAR als er getallen in de matrix worden aangetroffen en ONWAAR als er fouten worden aangetroffen. Hier wordt het resultaat geretourneerd als {ONWAAR,ONWAAR;ONWAAR,ONWAAR;WAAR,ONWAAR;ONWAAR,WAAR;WAAR,ONWAAR;ONWAAR,ONWAAR;ONWAAR,ONWAAR;WAAR,ONWAAR;
  WAAR,WAAR}.
• --({FALSE,FALSE;FALSE,FALSE;TRUE,FALSE;FALSE,TRUE;TRUE,FALSE;FALSE,FALSE;FALSE,FALSE;TRUE,)
  FALSE;TRUE,TRUE}):Deze twee mintekens zetten „WAAR” om naar 1 en zetten „ONWAAR” om naar 0. Hier krijgt u een nieuwe matrix 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 het rijnummer van de celverwijzing: {3;4}. Vervolgens verheft de machtsverheffingsoperator (^) de getallen 3 en 4 tot de macht 0, wat uiteindelijk resulteert in {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 matrices: {0;0;1;1;1;0;0;1;2}, wat voldoet aan de oorspronkelijke gegevens. Elk getal ongelijk aan nul in de resulterende matrix geeft aan dat ten minste één van de uitgesloten tekenreeksen is gevonden; een nul betekent dat er geen uitgesloten tekenreeksen zijn aangetroffen.

4) SUM(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 matrix groter is dan 0. Als een getal groter is dan 0, wordt WAAR geretourneerd; zo niet, dan ONWAAR. U krijgt vervolgens een nieuwe matrix als {ONWAAR;ONWAAR;WAAR;WAAR;WAAR;ONWAAR;ONWAAR;WAAR;WAAR}.
• 1-{FALSE;FALSE;TRUE;TRUE;TRUE;FALSE,FALSE,TRUE;TRUE}: Omdat we alleen de cellen willen tellen die de opgegeven waarden niet bevatten, moeten we deze waarden in de matrix omkeren door ze af te trekken van 1. De wiskundige operator zet WAAR- en ONWAAR-waarden automatisch om naar 1-en en 0-en en levert uiteindelijk het resultaat {1;1;0;0;0;1;1;0;0}.
• SUM{1;1;0;0;0;1;1;0;0}:De SOM-functie telt alle getallen in de matrix op en retourneert het eindresultaat als 4.

Gerelateerde functies

Gerelateerde formules