Generieke formule
(=SUM(--(FREQUENCY(IF(criteria,values),values)>0)))
Samenvatting
Om unieke numerieke waarden in een bereik te tellen, kunt u een formule gebruiken die is gebaseerd op de functies FREQUENCY, SUM en IF. In het getoonde voorbeeld verschijnen de personeelsnummers in het bereik B5: B14. De formule in G6 is:
=SUM(--(FREQUENCY(IF(C5:C14="A",B5:B14),B5:B14)>0))
wat 2 retourneert, aangezien er 2 unieke werknemer-ID's zijn in gebouw A.
Opmerking: dit is een matrixformule en moet worden ingevoerd met control + shift + enter, tenzij u Excel 365 gebruikt.
Uitleg
Opmerking: vóór Excel 365 had Excel geen speciale functie om unieke waarden te tellen. Deze formule toont een manier om unieke waarden te tellen, zolang ze numeriek zijn. Als u tekstwaarden of een combinatie van tekst en cijfers heeft, moet u een ingewikkelder formule gebruiken.
De Excel-functie FREQUENTIE retourneert een frequentieverdeling, een overzichtstabel die de frequentie van numerieke waarden bevat, georganiseerd in "bakken". We gebruiken het hier als een omweg om unieke numerieke waarden te tellen. Om criteria toe te passen, gebruiken we de ALS-functie.
We werken van binnen naar buiten en filteren eerst waarden met de ALS-functie:
IF(C5:C14="A",B5:B14) // filter on building A
Het resultaat van deze bewerking is een array als deze:
(905;905;905;905;773;773;FALSE;FALSE;FALSE;FALSE)
Merk op dat alle ID's in gebouw B nu FALSE zijn. Deze array wordt rechtstreeks aan de functie FREQUENCY geleverd als de data_array . Voor de bins_array leveren we de id's zelf:
FREQUENCY((905;905;905;905;773;773;FALSE;FALSE;FALSE;FALSE),(905;905;905;905;773;773;801;963;963;963))
Met deze configuratie retourneert FREQUENCY de onderstaande array:
(4;0;0;0;2;0;0;0;0;0;0)
Het resultaat is een beetje cryptisch, maar de betekenis is dat 905 vier keer verschijnt en 773 twee keer. De FALSE-waarden worden automatisch genegeerd.
FREQUENCY heeft een speciale functie die automatisch nul retourneert voor alle getallen die al in de gegevensmatrix zijn verschenen, daarom zijn waarden nul zodra een getal is aangetroffen. Dit is de functie waarmee deze benadering werkt.
Vervolgens wordt getest dat elk van deze waarden groter is dan nul:
(4;0;0;0;2;0;0;0;0;0;0)>0
Het resultaat is een array als deze:
(TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
Elke TRUE in de lijst vertegenwoordigt een uniek nummer in de lijst, en we hoeven alleen de TRUE-waarden bij elkaar op te tellen met SUM. SUM telt echter geen logische waarden op in een array, dus we moeten de waarden eerst dwingen tot 1 of nul. Dit wordt gedaan met het dubbele negatieve (-). Het resultaat is een array van slechts 1'en of 0'en:
(1;0;0;0;1;0;0;0;0;0;0)
Ten slotte telt SUM deze waarden op en retourneert het totaal, wat in dit geval 2 is.
Meerdere criteria
U kunt de formule als volgt uitbreiden om meerdere criteria te verwerken:
(=SUM(--(FREQUENCY(IF((criteria1)*(criteria2),values),values)>0)))
