Tel unieke numerieke waarden op basis van criteria in Excel
In het Excel-werkblad kunt u last hebben van een probleem waarbij het aantal unieke numerieke waarden wordt geteld op basis van een specifieke voorwaarde. Hoe kan ik bijvoorbeeld de unieke aantal-waarden van het product "T-shirt" uit het rapport tellen zoals hieronder afgebeeld? In dit artikel zal ik enkele formules laten zien om deze taak in Excel te bereiken.
- Tel unieke numerieke waarden op basis van criteria in Excel 2019, 2016 en eerder
- Tel unieke numerieke waarden op basis van criteria in Excel 365
Tel unieke numerieke waarden op basis van criteria in Excel 2019, 2016 en eerder
In Excel 2019 en eerdere versies kunt u de functies SOM, FREQUENTIE en ALS combineren om een formule te maken voor het tellen van unieke waarden op basis van criteria. De generieke syntaxis is:
Array formula, should press Ctrl + Shift + Enter keys together.
- criteria_range: het cellenbereik dat overeenkomt met de criteria die u hebt opgegeven;
- criteria: de voorwaarde waarop u unieke waarden wilt tellen op basis van;
- range: Het bereik van de cellen met unieke waarden die moeten worden geteld.
Pas de onderstaande formule toe in een lege cel en druk op Ctrl + Shift + Enter toetsen om het juiste resultaat te krijgen, zie screenshot:
Verklaring van de formule:
=SUM(--(FREQUENCY(IF(A2:A12=E2,C2:C12),C2:C12)>0))
- IF(A2:A12=E2,C2:C12): Deze ALS-functie retourneert de waarde in kolom C als het product in kolom A "T-shirt" is, het resultaat is een array als deze: {FALSE;300;500;FALSE;400;FALSE;300;FALSE;FALSE; ONWAAR;350}.
- FREQUENCY(IF(A2:A12=E2,C2:C12),C2:C12)= FREQUENCY({FALSE;300;500;FALSE;400;FALSE;300;FALSE;FALSE;FALSE;350},{200;300;500;350;400;450;300;550;200;260;350}): De functie FREQUENTIE wordt gebruikt om elk van de numerieke waarden in de arraylijst te tellen en het resultaat als volgt te retourneren: {0;2;1;1;1;0;0;0;0;0;0;0} .
- --(FREQUENCY(IF(A2:A12=E2,C2:C12),C2:C12)>0)=--({0;2;1;1;1;0;0;0;0;0;0;0}>0): Test of elke waarde in de array groter is dan 0 en krijg het resultaat als volgt: {FALSE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}. En dan zet het dubbele minteken de TRUE's en FALSE's om in enen en nullen, waardoor een array als volgt wordt geretourneerd: {1;0;0;1;1;1;1;0;0;0;0;0}.
- SUM(--(FREQUENCY(IF(A2:A12=E2,C2:C12),C2:C12)>0))=SUM({0;1;1;1;1;0;0;0;0;0;0;0}): Gebruik ten slotte de SOM-functie om deze waarden bij elkaar op te tellen en het totale aantal te krijgen: 4.
Tips:
Als u de unieke waarden wilt tellen op basis van meer dan één voorwaarde, hoeft u alleen maar andere criteria toe te voegen aan de formule met * teken:
Tel unieke numerieke waarden op basis van criteria in Excel 365
In Excel 365 kan de combinatie van de functies RIJEN, UNIEK en FILTER helpen om unieke numerieke waarden te tellen op basis van criteria, de generieke syntaxis is:
- range: Het bereik van de cellen met unieke waarden die moeten worden geteld.
- criteria_range: het cellenbereik dat overeenkomt met de criteria die u hebt opgegeven;
- criteria: de voorwaarde waarop u unieke waarden wilt tellen op basis van;
Kopieer of voer de volgende formule in een cel in en druk op Enter toets om het resultaat te retourneren, zie screenshot:
Verklaring van de formule:
=ROWS(UNIQUE(FILTER(C2:C12,A2:A12=E2)))
- A2:A12=E2: Deze uitdrukking controleert of de waarde in cel E2 bestaat in het bereik A2:A12 en krijgt dit resultaat: {FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE}.
- FILTER(C2:C12,A2:A12=E2): De functie FREQUENTIE wordt gebruikt om elk van de numerieke waarden in de arraylijst te tellen en het resultaat als volgt te retourneren: {0;2;1;1;1;0;0;0;0;0;0;0} .
- UNIQUE(FILTER(C2:C12,A2:A12=E2))=UNIQUE({300;500;400;300;350}): Hier wordt de functie UNIQUE gebruikt om unieke waarden uit de lijstarray te extraheren om dit resultaat te krijgen: {300;500;400;350}.
- ROWS(UNIQUE(FILTER(C2:C12,A2:A12=E2)))=ROWS({300;500;400;350}): De functie RIJEN retourneert het aantal rijen op basis van een celbereik of matrix, dus het resultaat is: 4.
Tips:
1. Als de overeenkomende waarde niet bestaat in het gegevensbereik, krijgt u een foutwaarde. Om de foutwaarde te vervangen door 0, past u de volgende formule toe:
2. Om de unieke waarden te tellen op basis van meer dan één voorwaarde, hoeft u alleen maar andere criteria aan de formule toe te voegen met een * teken zoals dit:
Gebruikte relatieve functie:
- SOM:
- De Excel SOM-functie retourneert de som van de opgegeven waarden.
- FREQUENTIE:
- De functie FREQUENTIE berekent hoe vaak waarden voorkomen binnen een bereik van waarden en retourneert vervolgens een verticale reeks getallen.
- RIJEN:
- De functie RIJEN retourneert het aantal rijen in een bepaalde verwijzing of matrix.
- UNIEK:
- De functie UNIQUE retourneert een lijst met unieke waarden in een lijst of bereik.
- FILTER:
- De functie FILTER helpt bij het filteren van een reeks gegevens op basis van door u gedefinieerde criteria.
Meer artikelen:
- Tel unieke numerieke waarden of datums in een kolom
- Stel dat u een lijst met nummers heeft die enkele duplicaten bevatten, nu wilt u het aantal unieke waarden tellen of de waarden verschijnen slechts één keer in de lijst, zoals hieronder afgebeeld. In dit artikel zullen we het hebben over enkele handige formules om deze taak snel en gemakkelijk in Excel op te lossen.
- Tel alle overeenkomsten/duplicaten tussen twee kolommen
- Het vergelijken van twee kolommen met gegevens en het tellen van alle overeenkomsten of duplicaten in de twee kolommen kan voor de meesten van ons een veelvoorkomende taak zijn. U hebt bijvoorbeeld twee kolommen met namen, sommige namen verschijnen zowel in de eerste als in de tweede kolom, nu wilt u alle overeenkomende namen (de overeenkomsten die zich ergens in de twee kolommen bevinden) tussen twee kolommen tellen, zoals hieronder afgebeeld, deze tutorial zal enkele formules introduceren om dit doel in Excel te bereiken.
- Tel het aantal cellen is gelijk aan een van de vele waarden
- Stel dat ik een lijst met producten in kolom A heb, nu wil ik het totale aantal specifieke producten Apple, Grape en Lemon krijgen die in bereik C4:C6 uit kolom A staan, zoals hieronder afgebeeld. Normaal gesproken werken in Excel de eenvoudige functies AANTAL.ALS en AANTAL.ALS niet in dit scenario. In dit artikel zal ik het hebben over hoe u deze taak snel en gemakkelijk kunt oplossen met de combinatie van SOMPRODUCT- en AANTAL.ALS-functies.
De beste tools voor kantoorproductiviteit
Kutools for Excel - Helpt u zich te onderscheiden van de menigte
Kutools voor Excel beschikt over meer dan 300 functies, Ervoor zorgen dat wat u nodig heeft slechts één klik verwijderd is...
Office-tabblad - Schakel lezen en bewerken met tabbladen in Microsoft Office in (inclusief Excel)
- Een seconde om te schakelen tussen tientallen geopende documenten!
- Verminder elke dag honderden muisklikken voor u, zeg maar dag tegen muishand.
- Verhoogt uw productiviteit met 50% bij het bekijken en bewerken van meerdere documenten.
- Brengt efficiënte tabbladen naar Office (inclusief Excel), net als Chrome, Edge en Firefox.
