Generieke formule
=SUMPRODUCT(--(FREQUENCY(MATCH(data,data,0),ROW(data)-ROW(data.firstcell)+1)>0))
Samenvatting
Om unieke tekstwaarden in een bereik te tellen, kunt u een formule gebruiken die verschillende functies gebruikt: FREQUENCY, MATCH, ROW en SUMPRODUCT. In het getoonde voorbeeld is de formule in F5:
=SUMPRODUCT(--(FREQUENCY(MATCH(B5:B14,B5:B14,0),ROW(B5:B14)-ROW(B5)+1)>0))
wat 4 retourneert, aangezien er 4 unieke namen zijn in B5: B14.
Opmerking: een andere manier om unieke waarden te tellen, is door de functie AANTAL.ALS te gebruiken. Dit is een veel eenvoudigere formule, maar deze kan langzaam werken op grote gegevenssets. Met Excel 365 kunt u een eenvoudigere en snellere formule gebruiken op basis van UNIQUE.
Uitleg
Deze formule is ingewikkelder dan een vergelijkbare formule die FREQUENCY gebruikt om unieke numerieke waarden te tellen, omdat FREQUENCY niet werkt met niet-numerieke waarden. Het resultaat is dat een groot deel van de formule de niet-numerieke gegevens eenvoudig omzet in numerieke gegevens die FREQUENCY kan verwerken.
Werkend van binnen naar buiten, wordt de MATCH-functie gebruikt om de positie te bepalen van elk item dat in de gegevens voorkomt:
MATCH(B5:B14,B5:B14,0)
Het resultaat van MATCH is een array als deze:
(1;1;1;4;4;6;6;6;9;9)
Omdat VERGELIJKEN altijd de positie van de eerste overeenkomst retourneert, retourneren waarden die meer dan eens in de gegevens voorkomen dezelfde positie. Bijvoorbeeld, omdat "Jim" 3 keer in de lijst voorkomt, verschijnt hij 3 keer in deze array als het nummer 1.
Deze array wordt in FREQUENCY ingevoerd als het data_array- argument. Het argument bins_array is opgebouwd uit dit deel van de formule:
ROW(B5:B14)-ROW(B5)+1)
die een opeenvolgende lijst met getallen opbouwt voor elke waarde in de gegevens:
(1;2;3;4;5;6;7;8;9;10)
Op dit punt is FREQUENCY als volgt geconfigureerd:
FREQUENCY((1;1;1;4;4;6;6;6;9;9),(1;2;3;4;5;6;7;8;9;10))
FREQUENCY retourneert een reeks getallen die een telling aangeven voor elk getal in de gegevensmatrix, geordend op bin. Als een getal al is geteld, retourneert FREQUENCY nul. Dit is een belangrijk kenmerk van de werking van deze formule. Het resultaat van FREQUENCY is een array als deze:
(3;0;0;2;0;3;0;0;2;0;0) // output from FREQUENCY
Opmerking: FREQUENCY retourneert altijd een array met één item meer dan de bins_array .
We kunnen de formule nu als volgt herschrijven:
=SUMPRODUCT(--((3;0;0;2;0;3;0;0;2;0;0)>0))
Vervolgens controleren we op waarden groter dan nul (> 0), die de getallen naar WAAR of ONWAAR converteert, en gebruiken vervolgens een dubbele negatieve (-) om de waarden WAAR en ONWAAR om te zetten in 1s en 0s. Nu hebben we:
=SUMPRODUCT((1;0;0;1;0;1;0;0;1;0;0))
Ten slotte telt SOMPRODUCT gewoon de getallen op en retourneert het totaal, in dit geval 4.
Omgaan met lege cellen
Lege cellen in het bereik zorgen ervoor dat de formule een # N / B-fout retourneert. Om lege cellen af te handelen, kunt u een meer gecompliceerde matrixformule gebruiken die de ALS-functie gebruikt om lege waarden uit te filteren:
(=SUM(IF(FREQUENCY(IF(data"", MATCH(data,data,0)),ROW(data)-ROW(data.firstcell)+1),1)))
Opmerking: als u IF toevoegt, wordt dit een matrixformule waarvoor control-shift-enter vereist is.
Zie deze pagina voor meer informatie.
Andere manieren om unieke waarden te tellen
Als u Excel 365 heeft, kunt u de functie UNIQUE gebruiken om unieke waarden te tellen met een veel eenvoudigere formule.
Een draaitabel is ook een uitstekende manier om unieke waarden te tellen.
