Tel unieke waarden in Excel met behulp van de AANTAL.ALS-functie

In deze zelfstudie leert u hoe u unieke waarden in Excel kunt tellen met formules (AANTAL.ALS en SOMPRODUCT-functies).

Unieke waarden tellen in Excel

Laten we zeggen dat we een dataset hebben zoals hieronder weergegeven:

Voor deze tutorial noem ik het bereik A2:A10 als NAMEN. In de toekomst zullen we dit genoemde bereik in de formules gebruiken.

Zie ook: Benoemde bereiken maken in Excel.

In deze dataset zit een herhaling in het NAMES-bereik. Om het aantal unieke namen uit deze dataset (A2:A10) te krijgen, kunnen we een combinatie van AANTAL.ALS- en SOMPRODUCT-functies gebruiken, zoals hieronder weergegeven:

=SOMPRODUCT(1/AANTAL.ALS(NAMEN,NAMEN))

Hoe werkt deze formule?

Laten we deze formule opsplitsen om een ​​beter begrip te krijgen:

• AANTAL.ALS(NAMEN,NAMEN)
  • Dit deel van de formule retourneert een matrix. In het bovenstaande voorbeeld zou dit {2;2;3;1;3;1;2;3;2} zijn. De getallen hier geven aan hoe vaak een waarde voorkomt in het gegeven celbereik.
    De naam is bijvoorbeeld Bob, die twee keer in de lijst voorkomt, dus het zou het nummer 2 voor Bob opleveren. Op dezelfde manier komt Steve drie keer voor en daarom wordt er 3 geretourneerd voor Steve.
• 1/AANTAL.ALS(NAMEN,NAMEN)
  • Dit deel van de formule retourneert een array - {0.5;0.5;0.3333333333333333;1;0.3333333333333333;1;0.5;0.3333333333333333;0.5}
    Omdat we 1 hebben gedeeld door de array, wordt deze array geretourneerd.
    Het eerste element van de hierboven geretourneerde array was bijvoorbeeld 2. Als 1 wordt gedeeld door 2, wordt .5 geretourneerd.
• SOMPRODUCT(1/AANTAL.ALS(NAMEN,NAMEN))
  • SOMPRODUCT voegt eenvoudig al deze getallen toe. Merk op dat als Bob twee keer voorkomt in de lijst, de bovenstaande array .5 retourneert waar de naam van Bob in de lijst verscheen. Evenzo, aangezien Steve driemaal in de lijst voorkomt, retourneert de array .3333333 wanneer de naam van Steve verschijnt. Wanneer we de nummers voor elke naam toevoegen, zou dit altijd 1 opleveren. En als we alle nummers toevoegen, zou het totale aantal unieke namen in de lijst worden geretourneerd.

Deze formule werkt prima totdat je geen lege cellen in het bereik hebt. Maar als je lege cellen hebt, zou het een #DIV/0 opleveren! fout.

Hoe om te gaan met LEGE cellen?

Laten we eerst begrijpen waarom het een fout retourneert wanneer er een lege cel in het bereik is. Stel dat we de gegevensset hebben zoals hieronder weergegeven (waarbij cel A3 leeg is):

Als we nu dezelfde formule gebruiken die we hierboven hebben gebruikt, retourneert het AANTAL.ALS-gedeelte van de formule een matrix {2;0;3;1;3;1;2;3;1}. Aangezien er geen tekst in cel A3 staat, wordt het aantal geretourneerd als 0.

En aangezien we 1 delen door deze hele array, retourneert het een #DIV/0! fout.

Gebruik de onderstaande formule om deze delingsfout in het geval van lege cellen op te lossen:

=SOMPRODUCT((1/AANTAL.ALS(NAMEN,NAMEN&””)))

Een wijziging die we in deze formule hebben aangebracht, is het criteriagedeelte van de AANTAL.ALS-functie. We hebben NAMEN&”” gebruikt in plaats van NAMEN. Door dit te doen, zou de formule het aantal lege cellen retourneren (eerder retourneerde het 0 waar er een lege cel was).

OPMERKING: Deze formule telt lege cellen als een unieke waarde en retourneert deze in het resultaat.

In het bovenstaande voorbeeld zou het resultaat 5 moeten zijn, maar het retourneert 6 omdat de lege cel wordt geteld als een van de unieke waarden.

Hier is de formule die voor de lege cellen zorgt en deze niet meetelt in het eindresultaat:

=SOMPRODUCT((NAMEN””)/AANTAL.ALS(NAMEN,NAMEN&””))

In deze formule hebben we, in plaats van 1 als teller, NAMEN” gebruikt. Dit retourneert een array van TRUEs en FALSEs. Het retourneert ONWAAR wanneer er een lege cel is. Aangezien WAAR gelijk is aan 1 en ONWAAR gelijk is aan 0 in berekeningen, worden lege cellen niet meegeteld aangezien de teller 0 (ONWAAR) is.

Nu we het basisskelet van de formule klaar hebben, kunnen we een stap verder gaan en verschillende gegevenstypen tellen.

Unieke waarden in Excel tellen die tekst zijn

We zullen hetzelfde concept gebruiken dat hierboven is besproken om de formule te maken die alleen tekstwaarden telt die uniek zijn.

Hier is de formule die unieke tekstwaarden in Excel telt:

=SOMPRODUCT((ISTEKST(NAMEN)/AANTAL.ALS(NAMEN,NAMEN&””)))

We hebben alleen de formule ISTEXT(NAMES) als teller gebruikt. Het geeft WAAR terug als de cel tekst bevat en ONWAAR als dat niet het geval is. Het telt geen lege cellen, maar telt cellen met een lege tekenreeks ("").

Unieke waarden in Excel tellen die numeriek zijn

Hier is de formule die unieke numerieke waarden telt in Excel

=SOMPRODUCT((ISGETAL(NAMEN))/AANTAL.ALS(NAMEN,NAMEN&””))

Hier gebruiken we ISNUMBER(NAMES) als de teller. Het geeft WAAR terug als de cel een numeriek gegevenstype bevat, en ONWAAR als dat niet het geval is. Het telt geen lege cellen.