Tel het aantal cellen dat niet veel waarden bevat

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

Hoe cellen te tellen die niet veel waarden bevatten?

Zoals de onderstaande schermafbeelding laat zien, kunt u als volgt cellen tellen in B3:B11 die geen waarden bevatten die worden vermeld in D3:D4.

Generieke 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 om de formule zijn gewikkeld, is met succes een matrixformule gemaakt.

Hoe gebruik je deze formule?

1. .Selecteer een lege cel om het resultaat uit te voeren.

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

=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 TRANSPOSE-functie roteert de richting van D3:D4 en geeft terug {“tellen”,”leeg”};
• SEARCH({“count”,”blank”},B3:B11):De SEARCH-functie vindt hier de positie van substrings "count" en "blank" uit bereik B3:B11, en retourneert een array als {#VALUE!,#VALUE!;#VALUE!,#VALUE!;1,#VALUE!;#VALUE!,8;1,#VALUE!;#WAARDE!,#WAARDE!;#WAARDE!,#WAARDE! ;1,
  #WAARDE!;1,7}.
• In dit geval wordt elke cel in B3:B11 twee keer doorzocht omdat er twee waarden zijn die u wilt uitsluiten bij het tellen van cellen, dus u krijgt 18 waarden in de array. Elk getal in de array geeft de positie aan van het eerste teken van "count" of "blanco" in elke cel van B3:B11.
• ISNUMBER{#VALUE!,#VALUE!;#VALUE!,#VALUE!;1,#VALUE!;#VALUE!,8;1,#VALUE!;#VALUE!,#VALUE!;#VALUE!,
  #VALUE!;1,#VALUE!;1,7}:De functie ISNUMBER retourneert TRUE als ontmoetingsnummers in de array en FALSE als resultaat van ontmoetingsfouten. 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 "TRUE" om in 1 en zetten "FALSE" 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) ROW(D3:D4)^0:De ROW-functie retourneert de rijnummers van de celverwijzing: {3;4}, en vervolgens retourneert de exponentiatie-operator (^) het resultaat van de getallen 3 en 4 verheven tot een macht 0, en geeft 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 functie MMULT retourneert het matrixproduct van deze twee arrays: {0;0;1;1;1;0;0;1;2} om aan de oorspronkelijke gegevens te voldoen. Elk niet-nul getal in de array vertegenwoordigt een waarde waarbij ten minste één van de uitgesloten tekenreeksen is gevonden, en nul geeft aan dat er geen uitgesloten tekenreeksen zijn gevonden.

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

• {0;0;1;1;1;0;0;1;2}>0:Controleer hier of elk getal in de array groter is dan 0. Als een getal groter is dan 0, retourneert WAAR, zo niet, retourneert FALSE. Dan krijg je een nieuwe array 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 hoeven te 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 TRUE en FALSE waarden naar 1s en 0s, en geeft uiteindelijk terug het resultaat als {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 array op en geeft het uiteindelijke resultaat als 4.

Gerelateerde functies

Gerelateerde formules

De beste tools voor kantoorproductiviteit

Kutools for Excel - Helpt u zich te onderscheiden van de menigte

Wilt u uw dagelijkse werkzaamheden snel en perfect afronden? Kutools voor Excel biedt 300 krachtige geavanceerde functies (werkmappen combineren, optellen op kleur, celinhoud splitsen, datum converteren, enzovoort ...) en 80% tijd voor u besparen.

• Ontworpen voor 1500 werkscenario's, helpt u 80% Excel-problemen op te lossen.
• Verminder elke dag duizenden toetsenbord- en muisklikken, verlicht uw vermoeide ogen en handen.
• Word een Excel-expert in 3 minuten. U hoeft geen pijnlijke formules en VBA-codes meer te onthouden.
• 30 dagen onbeperkte gratis proefperiode. 60 dagen geld-terug-garantie. Gratis upgrade en ondersteuning gedurende 2 jaar.

Lees meer Downloaden

---

Office-tabblad - Schakel lezen en bewerken met tabbladen in Microsoft Office in (inclusief Excel)

• Een seconde om te schakelen tussen tientallen geopende documenten!
• Verminder elke dag honderden muisklikken voor u, zeg maar dag tegen muishand.
• Verhoogt uw productiviteit met 50% bij het bekijken en bewerken van meerdere documenten.
• Brengt efficiënte tabbladen naar Office (inclusief Excel), net als Chrome, Firefox en New Internet Explorer.

Lees meer Downloaden