Deze zelfstudie laat zien hoe u deExcel AANTAL.ALS en AANTAL.ALS Functies in Excel om gegevens te tellen die aan bepaalde criteria voldoen.
AANTAL.ALS-functieoverzicht
U kunt de AANTAL.ALS-functie in Excel gebruiken om cellen te tellen die een specifieke waarde bevatten, cellen te tellen die groter zijn dan of gelijk zijn aan een waarde, enz.
(Let op hoe de formule-invoer verschijnt)
AANTAL.ALS Functiesyntaxis en argumenten:
=AANTAL.ALS (bereik, criteria)
bereik - Het bereik van te tellen cellen.
criteria - De criteria die bepalen welke cellen moeten worden geteld.
Wat is de AANTAL.ALS-functie?
De AANTAL.ALS-functie is een van de oudere functies die in spreadsheets worden gebruikt. In eenvoudige bewoordingen is het geweldig om een bereik te scannen en u te vertellen hoeveel van de cellen aan die voorwaarde voldoen. We zullen bekijken hoe de functie werkt met tekst, cijfers en datums; evenals enkele van de andere situaties die zich kunnen voordoen.
Basis voorbeeld
Laten we beginnen met het bekijken van deze lijst met willekeurige items. We hebben enkele cijfers, lege cellen en enkele tekenreeksen.
Als u wilt weten hoeveel items exact overeenkomen met de criteria, kunt u als tweede argument aangeven waarnaar u wilt zoeken. Een voorbeeld van deze formule kan er als volgt uitzien:
=AANTAL.ALS(A2:A9; "Appel")
Deze formule zou het getal 3 retourneren, omdat er 3 cellen in ons bereik zijn die aan die criteria voldoen. Als alternatief kunnen we een celverwijzing gebruiken in plaats van een waarde hard te coderen. Als we 'Apple' in cel G2 hadden geschreven, zouden we de formule kunnen wijzigen in
=AANTAL.ALS(A2:A9, G2)
Bij het omgaan met getallen is het belangrijk om onderscheid te maken tussen getallen en getallen die als tekst zijn opgeslagen. Over het algemeen zet je bij het schrijven van formules geen aanhalingstekens om getallen. Dus om een formule te schrijven die controleert op het getal 5, zou je schrijven
=AANTAL.ALS(A2:A9, 5)
Ten slotte kunnen we ook controleren op lege cellen door een tekenreeks met lengte nul te gebruiken. We zouden die formule schrijven als
=AANTAL.ALS(A2:A9; "")
Opmerking: Deze formule telt zowel cellen die echt leeg zijn als cellen die leeg zijn als resultaat van een formule, zoals een ALS-functie.
Gedeeltelijke overeenkomsten
De AANTAL.ALS-functie ondersteunt het gebruik van jokertekens, "*" of "?", in de criteria. Laten we eens kijken naar deze lijst met smakelijke bakkerijproducten:
Om alle items te vinden die met Apple beginnen, kunnen we "Apple*" schrijven. Dus, om een antwoord van 3 te krijgen, onze formule in D2 is
=AANTAL.ALS(A2:A5, "Apple*")
Opmerking: De AANTAL.ALS-functie is niet hoofdlettergevoelig, dus u kunt ook "appel*" schrijven als u dat wilt.
Terug naar onze gebakken goederen, we willen misschien ook weten hoeveel taarten we in onze lijst hebben. We kunnen dat vinden door het jokerteken aan het begin van onze zoekterm te plaatsen en te schrijven
=AANTAL.ALS(A2:A5; "*taart")
Deze formule geeft het resultaat van 2.
We kunnen ook jokertekens gebruiken om te controleren op cellen met tekst. Laten we teruggaan naar onze oorspronkelijke lijst met gegevens.
Om het aantal cellen te tellen dat op zijn minst wat tekst bevat, dus geen getallen of lege cellen tellen, kunnen we schrijven:
=AANTAL.ALS(A2:A9; "*")
U kunt zien dat onze formule correct een resultaat van 4 retourneert.
Vergelijkingsoperatoren in AANTAL.ALS
Bij het schrijven van de criteria tot nu toe hebben we gesuggereerd dat onze vergelijkingsoperator "=" is. In feite hadden we dit kunnen schrijven:
=AANTAL.ALS(A2:A9; "= Appel")
Het is echter een extra teken om uit te schrijven, dus het wordt meestal weggelaten. Dit betekent echter dat u de andere operatoren kunt gebruiken, zoals groter dan, kleiner dan of niet gelijk aan. Laten we eens kijken naar deze lijst met geregistreerde leeftijden:
Als we willen weten hoeveel kinderen er minstens 5 jaar oud zijn, kunnen we een "groter dan of gelijk aan" vergelijking uitschrijven als volgt:
=AANTAL.ALS(A2:A8; ">=5")
Opmerking: De vergelijkingsoperator wordt altijd gegeven als een tekenreeks en moet dus tussen aanhalingstekens staan.
Op dezelfde manier kunt u ook controleren op items die kleiner zijn dan een bepaalde waarde. Als we willen weten hoeveel er minder dan 8 zijn, kunnen we wegschrijven
=AANTAL.ALS(A2:A8, "<8")
Dit geeft ons het gewenste resultaat van 5. Laten we ons nu voorstellen dat alle 6-jarige kinderen op uitje gaan. Hoeveel kinderen blijven er over? We kunnen dit achterhalen door een "niet gelijk aan" vergelijking als deze te gebruiken:
=AANTAL.ALS(A2:A8; "6")
Nu kunnen we snel zien dat we 6 kinderen hebben die nog geen 6 jaar oud zijn.
In deze vergelijkingsvoorbeelden hebben we tot nu toe hard gecodeerd de waarden die we wilden. U kunt ook een celverwijzing gebruiken. De truc is dat je de vergelijkingsoperator moet samenvoegen met de celverwijzing. Laten we zeggen dat we het getal 7 in cel C2 plaatsen en we willen dat onze formule in D2 laat zien hoeveel kinderen jonger zijn dan 7 jaar.
Onze formule in D2 moet er als volgt uitzien:
=AANTAL.ALS(A2:A8, "<"&C2)
Opmerking: Let er bij het schrijven van deze formules op of u een item binnen of buiten aanhalingstekens moet plaatsen. De operators staan altijd binnen citaten, celverwijzingen staan altijd buiten citaten. Cijfers staan buiten als je een exacte match doet, maar binnen als je een vergelijkingsoperator doet.
Werken met datums
We hebben gezien hoe je een tekst of nummer als criterium kunt geven, maar hoe zit het met wanneer we met datums moeten werken? Hier is een snelle voorbeeldlijst waarmee we kunnen werken:
Om te tellen hoeveel data er na 4 mei zijn, moeten we voorzichtig zijn. Computers slaan datums op als getallen, dus we moeten ervoor zorgen dat de computer het juiste nummer gebruikt. Als we deze formule zouden schrijven, zouden we dan het juiste resultaat krijgen?
=AANTAL.ALS(A2:A9, "
Het antwoord is "mogelijk". Omdat we het jaartal uit onze criteria hebben weggelaten, gaat de computer ervan uit dat we het huidige jaar bedoelen. Als alle datums waarmee we werken voor het huidige jaar zijn, dan krijgen we het juiste antwoord. Als er echter datums in de toekomst zijn, krijgen we het verkeerde antwoord. Zodra het volgende jaar begint, zal deze formule ook een ander resultaat opleveren. Als zodanig moet deze syntaxis waarschijnlijk worden vermeden.
Omdat het moeilijk kan zijn om datums correct in een formule te schrijven, is het het beste om de datum die u wilt gebruiken in een cel te schrijven, en dan kunt u die celverwijzing gebruiken in uw AANTAL.ALS-formule. Dus laten we de datum 7-mei-2020 in cel C2 schrijven, en dan kunnen we onze formule in C4 zetten.
De formule in C4 is
=AANTAL.ALS(A2:A9, "<"&C2)
Nu weten we dat het resultaat van 7 correct is, en het antwoord zal niet onverwacht veranderen als we deze spreadsheet ergens in de toekomst openen.
Voordat we deze sectie verlaten, is het gebruikelijk om de functie VANDAAG te gebruiken bij het werken met datums. We kunnen dat net zo gebruiken als een celverwijzing. We kunnen bijvoorbeeld de vorige formule veranderen in deze:
=AANTAL.ALS(A2:A9, "<"&VANDAAG())
Nu wordt onze formule nog steeds bijgewerkt naarmate de realtime vordert, en we zullen een aantal items hebben die minder zijn dan vandaag.
Meerdere criteria en AANTAL.ALS
De originele AANTAL.ALS-functie kreeg een verbetering in 2007 toen AANTAL.ALS uitkwam. De syntaxis tussen de twee lijkt erg op elkaar, waarbij u met de laatste extra bereiken en criteria kunt opgeven. U kunt AANTALLEN.ALS eenvoudig gebruiken in elke situatie waarin AANTAL.ALS bestaat. Het is gewoon een goed idee om te weten dat beide functies bestaan.
Laten we eens kijken naar deze gegevenstabel:
Om erachter te komen hoeveel mensen zich in loonniveaus 1 tot 2 bevinden, kunt u een sommatie van AANTAL.ALS-functies als volgt schrijven:
=AANTAL.ALS(B2:B7; ">=1")-AANTAL.ALS(B2:B7; ">2")
Deze formule werkt, omdat u alles vindt dat boven de 1 ligt, maar dan het aantal records aftrekt dat zich buiten uw afkappunt bevindt. Als alternatief kunt u AANTALLEN.ALS als volgt gebruiken:
=AANTAL.ALS(B2:B7; ">=1", B2:B7, "<=2")
Dit laatste is intuïtiever om te lezen, dus misschien wilt u die route gebruiken. COUNTIFS is ook krachtiger wanneer u meerdere kolommen moet overwegen. Laten we zeggen dat we willen weten hoeveel mensen er in Management en in salarisniveau 1 zitten. Dat kan niet met slechts een AANTAL.ALS; je zou moeten uitschrijven
=AANTALLEN.ALS(A2:A7; "Beheer", B2:B7, 1)
Deze formule zou u het juiste resultaat van 2 geven. Voordat we deze sectie verlaten, laten we eens kijken naar een logica van het type Of. Wat als we wilden weten hoeveel mensen er in Management zitten of? U moet enkele AANTALLEN.ALS bij elkaar optellen, maar er zijn twee manieren om dit te doen. De eenvoudigere manier is om het als volgt te schrijven:
=AANTAL.ALS(A2:A7; "HR")+AANTAL.ALS(A2:A7; "Beheer")
U kunt ook gebruik maken van een array en deze matrixformule schrijven:
=SOM(AANTAL.ALS(A2:A7; {"HR", "Beheer"}))
Opmerking: Matrixformules moeten worden bevestigd met `Ctrl+Shift+Enter` en niet alleen met `Enter`.
Hoe deze formule zal werken, is dat het zal zien dat u een array als invoer hebt gegeven. Het berekent dus het resultaat naar twee verschillende AANTAL.ALS-functies en slaat ze op in een array. De SOM-functie zal dan alle resultaten in onze array bij elkaar optellen om een enkele uitvoer te maken. Onze formule wordt dus als volgt geëvalueerd:
=SOM(AANTAL.ALS(A2:A7; {"HR", "Management"})) =SOM({2, 3}) =5
Tel unieke waarden
Nu we hebben gezien hoe we een array kunnen gebruiken met de AANTAL.ALS-functie, kunnen we nog een stap verder gaan om ons te helpen tellen hoeveel unieke waarden er in een bereik zijn. Laten we eerst nog eens kijken naar onze lijst met afdelingen.
=SOM(1/AANTAL.ALS(A2:A7,A2:A7))
We kunnen zien dat er 6 cellen aan gegevens zijn, maar er zijn slechts 3 verschillende items. Om de wiskunde te laten werken, moeten we elk item 1/N waard hebben, waarbij N het aantal keren is dat een item wordt herhaald. Als elke HR bijvoorbeeld slechts 1/2 waard was, dan zou je, als je ze optelde, een telling van 1 krijgen voor 1 unieke waarde.
Terug naar onze AANTAL.ALS, die is ontworpen om erachter te komen hoe vaak een item in een bereik voorkomt. In D2 schrijven we de matrixformule
=SOM(1/AANTAL.ALS(A2:A7, A2:A7))
Hoe deze formule zal werken, is voor elke cel in het bereik van A2:A7, het zal controleren om te zien hoe vaak het verschijnt. Met onze steekproef zal dit een array van produceren
{2, 2, 3, 3, 3, 1}
Vervolgens veranderen we al die getallen in breuken door een deling uit te voeren. Nu ziet onze array er als volgt uit:
{1/2, 1/2, 1/3, 1/3, 1/3, 1/1}
Als we deze allemaal bij elkaar optellen, krijgen we ons gewenste resultaat van 3.
Countif met twee of meerdere voorwaarden - De Countifs-functie
Tot nu toe hebben we alleen met de AANTAL.ALS-functie gewerkt. De AANTAL.ALS-functie kan slechts één criterium tegelijk verwerken. Om AANTAL.ALS met meerdere criteria te gebruiken, moet u de AANTAL.ALS-functie gebruiken. AANTAL.ALS gedraagt zich precies als AANTAL.ALS. Je voegt gewoon extra criteria toe. Laten we eens kijken naar onderstaand voorbeeld.
=AANTALLEN.ALS(B2:B7,"=130")
AANTAL.ALS & AANTAL.ALS in Google Spreadsheets
De AANTAL.ALS & AANTAL.ALS Functie werkt precies hetzelfde in Google Spreadsheets als in Excel:
