Eerder hebben we geleerd hoe u unieke waarden in een bereik kunt tellen. We hebben ook geleerd hoe we unieke waarden uit een bereik kunnen halen. In dit artikel zullen we leren hoe u de unieke waarde in het bereik met de conditie in Excel kunt tellen.
Generieke formule
{=SOM(--(FREQUENTIE(ALS(voorwaarde,VERGELIJKEN(bereik,bereik,0)),RIJ(bereik)-ROW(eerste cel in bereik)+1)>0))}
Het is een matrixformule, gebruik CTRL+SHIFT+ENTER
Voorwaarde : De criteria waarop u unieke waarden wilt krijgen.
Bereik : bereik waarin u unieke waarden wilt krijgen.
eerste cel binnen bereik: Het is de referentie van de eerste cel in bereik. Als het bereik A2:A10 is, is het A2.
Voorbeeld:
Hier heb ik deze gegevens van namen. De bijbehorende klassen staan in de kolom hiernaast. We moeten unieke namen in elke klasse tellen.
Gebruik de bovenstaande generieke formule en schrijf deze formule in E2
{=SOM(--(FREQUENTIE(IF(B2:B19="Klasse 1",MATCH(A2:A19,A2:A19,0)),RIJ(A2:A19)-ROW(A2)+1)>0 ))}
De bovenstaande formule retourneert de unieke waarde in het Excel-bereik A2:A19 op voorwaarde van B2:B19="Klasse 1".
Wijzig de criteria om unieke waarden in verschillende klassen te krijgen. We hebben het hier hard gecodeerd, maar je kunt ook een celverwijzing geven. Gebruik benoemde bereiken of absolute verwijzingen voor bereiken, als u niet wilt dat ze te veranderen.
Hoe het werkt?
Laten we het van binnenuit opsplitsen.
INDIEN(B2:B19="Klasse 1",BIJ ELKAAR PASSEN(A2:A19,A2:A19,0))
B2:B19="Klasse 1": Dit deel retourneert een array van waar en onwaar. WAAR voor elke wedstrijd.
{TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE… .}
BIJ ELKAAR PASSEN(A2:A19,A2:A19,0): dit deel retourneert de eerste locatie van elke waarde in het bereik A2:A19 volgens het eigendom van MATCH.
{1;2;1;4;5;4;1;8;9;1;2;1;4;5;4;1;8;9}.
Nu krijgen we voor elke TRUE-waarde de positie en voor false krijgen we FALSE. Dus voor de hele IF-verklaring krijgen we
{1;FALSE;1;FALSE;5;4;FALSE;FALSE;FALSE;FALSE;2;FALSE;FALSE;5;FALSE;1;8;FALSE}.
Vervolgens gaan we naar het frequentiegedeelte.
FREQUENTIE(INDIEN(B2:B19="Klasse 1",BIJ ELKAAR PASSEN(A2:A19,A2:A19,0)),RIJ(A2:A19)-RIJ(A2)+1)
RIJ (A2:A19): Dit retourneert het rijnummer van elke cel in het bereik A2:A19.
{2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19}
RIJ(A2:A19)-RIJ(A2): Nu trekken we het eerste rijnummer af van elk rijnummer. Dit retourneert een array met serienummers vanaf 0.
{0;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17}
Omdat we een serienummer vanaf 1 willen hebben, tellen we er 1 bij op.
RIJ(A2:A19)-RIJ(A2)+1. Dit geeft ons een reeks serienummers vanaf 1.
{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18}
Dit zal ons helpen bij het verkrijgen van een unieke telling op voorwaarde.
Nu hebben we: FREQUENTIE({1;FALSE;1;FALSE;5;4;FALSE;FALSE;FALSE;FALSE;2;FALSE;FALSE;5;FALSE;1;8;FALSE},{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18})
Dit retourneert de frequentie van elk getal in de gegeven array.{3;1;0;1;2;0;0;1;0;0;0;0;0;0;0;0;0;0;0}
Hier gaf elk positief getal het optreden van een unieke waarde aan wanneer aan de criteria wordt voldaan. We moeten waarden groter dan 0 tellen in deze array. Daarvoor controleren we het met >0. Dit geeft TRUE en FALSE terug. We converteren true false met -- (dubbele binaire operator).
SOM(--({3;1;0;1;2;0;0;1;0;0;0;0;0;0;0;0;0;0;0})>0) dit vertaalt zich naar SOM({1;1;0;1;1;0;0;1;0;0;0;0;0;0;0;0;0;0;0})
En tot slot krijgen we het unieke aantal namen in het bereik op criteria als 5.
Ik weet dat het een beetje ingewikkeld is om te begrijpen, maar je controleert het via de formule-evaluatieoptie.
Om unieke waarden met meerdere criteria te tellen, kunnen we booleaanse logica gebruiken:
Tel unieke waarde met meerdere criteria met en logica
{=SOM(--(FREQUENTIE(ALS(voorwaarde1 * Voorwaarde2,VERGELIJKEN(bereik,bereik,0)),RIJ(bereik)-ROW(eerste cel in bereik)+1)>0))}
Bovenstaande generieke formule kan unieke waarden tellen voor meerdere voorwaarden en wanneer ze allemaal waar zijn.
Tel unieke waarde met meerdere criteria met of logica
{=SOM(--(FREQUENTIE(ALS(voorwaarde1 + Voorwaarde2,VERGELIJKEN(bereik,bereik,0)),RIJ(bereik)-ROW(eerste cel in bereik)+1)>0))}
Deze generieke formule kan worden gebruikt om unieke waarden te tellen met Or-logica. Het betekent dat het meetelt als een van de voorwaarden waar is.
Dus ja jongens, zo tel je unieke waarden in een bereik onder meerdere voorwaarden. Dit is een beetje ingewikkeld, maar het is snel. Als je het eenmaal gaat gebruiken, snap je hoe het werkt.
Als je twijfels hebt over dit Excel-formule-artikel, laat het me dan weten in de comments hieronder.
Download bestand:
Hoe unieke waarden in Excel te tellen met criteriaExcel-formule om unieke waarden uit een lijst te halen
Tel unieke waarden in Excel
Populaire artikels:
De VERT.ZOEKEN-functie in Excel
AANTAL.ALS in Excel 2016
Hoe de SUMIF-functie in Excel te gebruiken?
