Hoe telt u unieke waarden op basis van meerdere criteria in Excel?
In veel praktische scenario's is het vaak niet alleen nodig om waarden te tellen, maar ook om te bepalen hoeveel unieke items aan bepaalde voorwaarden in uw gegevens voldoen. Bijvoorbeeld, u wilt misschien weten hoeveel verschillende producten een bepaalde verkoper heeft verkocht, of hoeveel unieke bestellingen binnen een bepaalde periode zijn geplaatst. Het efficiënt afhandelen van dergelijke taken in Excel vereist dat u vertrouwd bent met geschikte formules, geavanceerde functies zoals draaitabellen, of zelfs aangepaste VBA-oplossingen. In dit artikel zullen we verschillende praktische methoden onderzoeken om unieke waarden te tellen op basis van één of meer criteria, met stap-voor-stap instructies en tips.
Unieke waarden tellen op basis van één criterium
Unieke waarden tellen op basis van twee gegeven datums
Unieke waarden tellen op basis van twee criteria
Unieke waarden tellen op basis van drie criteria
Unieke waarden tellen met behulp van een Draaitabel (Distinct Count, Excel 2013+)
Unieke waarden tellen met VBA-code (voor complexe/geautomatiseerde gevallen)
Unieke waarden tellen op basis van één criterium
Laten we een veelvoorkomend geval beschouwen: u wilt weten hoeveel verschillende producten door Tom zijn verkocht. Deze methode is geschikt wanneer u een eenvoudige dataset hebt en u de uniciteit wilt evalueren op basis van één enkele voorwaarde, zoals de verkooprecords van één persoon. Het is eenvoudig, maar vereist zorgvuldig gebruik van matrixformules.
Voer de volgende formule in een lege cel in (bijvoorbeeld cel G2):
=SOM(ALS("Tom"=$C$2:$C$20;1/(AANTAL.ALS($C$2:$C$20; "Tom"; $A$2:$A$20; $A$2:$A$20));0))
Na het typen van de formule drukt u op Ctrl + Shift + Enter (niet alleen Enter) om het als een matrixformule te bevestigen. De accolades verschijnen rond de formule in de formulebalk, en u zult het resultaat direct zien zoals hieronder weergegeven:
Opmerking:
- “Tom” is de voorwaarde die u wilt gebruiken om de resultaten te filteren. U kunt "Tom" vervangen door een verwijzing naar een andere cel (bijvoorbeeld, $F$2) als u meer flexibiliteit wilt.
- $C$2:$C$20 bevat de namen van de verkopers die moeten worden geëvalueerd.
- $A$2:$A$20 is de productkolom waarvoor u unieke tellingen wilt hebben.
- Als uw gegevensbereik verandert, vergeet dan niet om de verwijzingen dienovereenkomstig aan te passen.
Tip: Als u Excel 365 of Excel 2019 en later gebruikt, kunt u proberen de functies UNIEK en FILTER te gebruiken voor eenvoudigere formules.
Als u fouten van het type #DEEL/0! tegenkomt, controleer dan de criteria en zorg ervoor dat uw bereiken gelijk zijn in lengte.
Unieke waarden tellen op basis van twee gegeven datums
Wanneer u het aantal unieke items binnen een specifiek datumbereik wilt vinden, bijvoorbeeld alle unieke producten die tussen 2016/9/1 en 2016/9/30 zijn verkocht, kunt u deze aanpak gebruiken. Dit is vooral nuttig bij het analyseren van datatrends tussen bepaalde periodes, zoals maandelijks, per kwartaal of aangepaste datumbereiken. Wees echter voorzichtig met datumnotatie; deze moet overeenkomen met de datumwaarden in uw werkblad.
Plaats de volgende formule in een lege cel waar u het resultaat wilt weergeven:
=SOM(ALS($D$2:$D$20<=DATUM(2016,9,30)*($D$2:$D$20>=DATUM(2016,9,1));1/AANTAL.ALS( $A$2:$A$20; $A$2:$A$20; $D$2:$D$20; "<="&DATUM(2016,9,30);$D$2:$D$20; ">="&DATUM(2016,9,1)));0)
Druk na het invoeren van de formule op Ctrl + Shift + Enter om het uit te voeren als een matrixformule. De onderstaande schermafbeelding demonstreert het resultaat:
Opmerking:
- 2016,9,1 en 2016,9,30 zijn de start- en einddatumcriteria. U kunt deze naar behoefte wijzigen, of zelfs celverwijzingen gebruiken voor dynamische datumfilters.
- $D$2:$D$20 bevat de datumitems die moeten worden gecontroleerd.
- $A$2:$A$20 is opnieuw de item- of productkolom die u uniek wilt tellen.
- Zorg ervoor dat uw data als geldige Excel-datums zijn opgeslagen, niet als tekststrings. Als uw resultaat niet zoals verwacht verschijnt, controleer dan uw datumnotatie en bereiken.
Tip: Gebruik DATUM(jaar, maand, dag) om problemen met regionale datumnotaties te voorkomen. Wanneer u dynamische bereiken gebruikt, overweeg dan om genaamde bereiken te gebruiken voor duidelijkheid.
Unieke waarden tellen op basis van twee criteria
Stel dat u alleen de producten wilt analyseren die Tom in september heeft verkocht, waarbij naam en een datumbereik worden gecombineerd in uw unieke telling. Dit scenario is gebruikelijk voor prestatiebeoordelingen op basis van een periode of gesegmenteerde analyses. Naarmate uw criteria uitbreiden, wordt de formule complexer, en wordt nauwkeurigheid van de gegevens nog belangrijker.
Voer de volgende formule in een lege cel in, zoals H2:
=SOM(ALS(("Tom"=$C$2:$C$20)*($D$2:$D$20<=DATUM(2016,9,30)*($D$2:$D$20>=DATUM(2016,9,1)));1/AANTAL.ALS($C$2:$C$20; "Tom"; $A$2:$A$20; $A$2:$A$20; $D$2:$D$20; "<="&DATUM(2016,9,30);$D$2:$D$20; ">="&DATUM(2016,9,1)));0)
Nadat u de formule hebt getypt, bevestigt u deze met Ctrl + Shift + Enter. U zou de unieke telling onmiddellijk moeten zien; controleer de volgende illustratie:
Opmerkingen:
- “Tom” is de naamcriterium, terwijl “2016,9,1” en “2016,9,30” uw datumbereikgrenzen zijn. Pas deze indien nodig aan, of maak ze dynamisch met celverwijzingen.
- $C$2:$C$20 is de stafkolom (of een andere eerste criterium); $D$2:$D$20 is de datumkolom; $A$2:$A$20 bevat de unieke items die moeten worden geteld.
- Bereiken moeten allemaal even lang zijn om fouten te voorkomen.
Als u “of”-voorwaarden wilt gebruiken, zoals het tellen van unieke producten die door Tom zijn verkocht of in de regio Zuiden, kunt u de volgende formule gebruiken. Dit biedt bredere zoekvoorwaarden, hoewel resultaten mogelijk overlappen als gegevens aan beide criteria voldoen:
=SOM(--(FREQUENTIE(ALS(("Tom"=$C$2:$C$20)+("South"=$B$2:$B$20); AANTAL.ALS($A$2:$A$20; "<"&$A$2:$A$20); ""); AANTAL.ALS($A$2:$A$20; "<"&$A$2:$A$20))>0))
Vergeet niet op Ctrl + Shift + Enter te drukken. U zult de resultaten zien zoals hieronder weergegeven:
Tip: Let bij het toepassen van OF-voorwaarden op mogelijke dubbel tellingen als hetzelfde record aan beide voorwaarden voldoet. Voor grote datasets kan de prestatie worden beïnvloed.
Unieke waarden tellen op basis van drie criteria
Soms kan uw analyse drie of meer voorwaarden vereisen, zoals het bepalen van unieke producten die door Tom in september zijn verkocht, maar alleen in de regio Noorden. Dit is gebruikelijk bij multidimensionale data-analyse voor rapportage of gerichte zakelijke inzichten. Zorgvuldig referentiebeheer is essentieel bij het hanteren van dergelijke samengestelde logica.
Plaats deze matrixformule in een lege cel (bijvoorbeeld I2):
=SOM(ALS(("Tom"=$C$2:$C$20)*($D$2:$D$20<=DATUM(2016,9,30))*($D$2:$D$20>=DATUM(2016,9,1))*("North"=$B$2:$B$20);1/AANTAL.ALS($C$2:$C$20; "Tom"; $A$2:$A$20; $A$2:$A$20; $D$2:$D$20; "<="&DATUM(2016,9,30); $D$2:$D$20; ">="&DATUM(2016,9,1); $B$2:$B$20; "North"));0)
Druk op Ctrl + Shift + Enter om te voltooien. Hier is een voorbeeldresultaat ter referentie:
Controleer bij geavanceerde voorwaarden altijd of alle bereiken consistent zijn en of de gegevenstypen (bijvoorbeeld datum en tekst) correct zijn. Misaligneringen kunnen fouten of misleidende resultaten veroorzaken.
Tips:
- Als u prestatieproblemen ondervindt bij grote datasets, overweeg dan om de formule op te breken of gebruik de oplossing met de PivotTable van Excel.
- Genaamde bereiken of het refereren naar cellen voor alle criteria verbeteren de leesbaarheid en verminderen formulefouten.
- Voor frequent gebruik kunt u overwegen om deze formules vast te leggen in genoemde celverwijzingen of aangepaste functies.
Unieke waarden tellen met behulp van een Draaitabel (Distinct Count, Excel 2013+)
Voor gebruikers van Excel 2013 of nieuwer bieden Draaitabellen een meer interactief, niet-formule alternatief om unieke waarden te tellen over één of meerdere criteria. De Distinct Count-functie helpt u grote datasets efficiënt samen te vatten en te filteren, waardoor deze methode vooral geschikt is voor dynamische, op rapportage gebaseerde omgevingen. Merk echter op dat eerdere versies van Excel de Distinct Count-functie binnen Draaitabellen niet ondersteunen.
Hoe u deze methode gebruikt:
- Selecteer uw dataset en ga naar Invoegen > Draaitabel.
- Kies in het dialoogvenster Draaitabel maken waar u de Draaitabel wilt plaatsen, vink de optie "Voeg deze gegevens toe aan het Gegevensmodel" aan en klik vervolgens op OK.
- Sleep het veld dat u uniek wilt tellen (bijvoorbeeld Product) naar het Waardengebied. Standaard wordt het weergegeven als "Aantal van...".
- Klik op het veld in het Waardengebied en selecteer Instellingen waardevelden.
- Scroll in het pop-upvenster omlaag en selecteer Distinct Count (Deze optie is alleen beschikbaar in Excel 2013 of later, en verschijnt wanneer de Draaitabel is gemaakt met de optie "Voeg deze gegevens toe aan het Gegevensmodel" ingeschakeld).
- Voeg uw criteriavelden (bijvoorbeeld Verkoper, Regio, Datum) toe aan de Filters of Rijen/Kolommen om enkelvoudige of meervoudige voorwaarden toe te passen.
- Uw Draaitabel zal nu de unieke telling van waarden weergeven gefilterd op uw gekozen criteria.
Voordelen: Zeer visueel, gemakkelijk om filters aan te passen zonder formules te bewerken, en geschikt voor interactieve rapportage.
Beperkingen: Niet beschikbaar in Excel 2010 of eerder; het toevoegen van nieuwe gegevens vereist handmatig vernieuwen van de Draaitabel.
Praktische tip: Zorg er altijd voor dat de brongegevens geen duplicaten bevatten binnen hetzelfde record, tenzij dat de bedoeling is. Als u de optie Distinct Count mist, maak de Draaitabel dan opnieuw en controleer de optie “Voeg deze gegevens toe aan het Gegevensmodel”.
Unieke waarden tellen met VBA-code (voor complexe/geautomatiseerde gevallen)
Soms moet u mogelijk unieke waarden automatisch tellen op basis van verschillende criteria, vooral bij het verwerken van zeer grote datasets of bij het herhaaldelijk uitvoeren van de analyse. Een VBA-macro is ideaal voor dergelijke situaties, omdat het verschillende logica snel kan verwerken - inclusief multi-criteria filtering - zonder handmatige tussenkomst na instelling. VBA is echter geavanceerder dan reguliere Excel-functies, dus het wordt best gebruikt door gebruikers die bekend zijn met macro's of die continue analytische behoeften hebben.
Bewerkingsstappen:
- Druk op Alt + F11 om de VBA-editor te openen. Selecteer in de editor Invoegen > Module om een nieuwe module te maken.
- Kopieer en plak de volgende VBA-code in de module:
Sub CountUniqueWithCriteria()
Dim DataRange As Range
Dim CriteriaRange As Range
Dim CriteriaValue As Variant
Dim Dict As Object
Dim i As Long
Dim UniqueCount As Long
Dim ResultCell As Range
Set Dict = CreateObject("Scripting.Dictionary")
' Prompt for range settings
Set DataRange = Application.InputBox("Select data range (items to count):", "KutoolsforExcel", Type:=8)
Set CriteriaRange = Application.InputBox("Select criteria range (e.g. Salesperson):", "KutoolsforExcel", Type:=8)
CriteriaValue = Application.InputBox("Enter criteria value:", "KutoolsforExcel", "", Type:=2)
Set ResultCell = Application.InputBox("Select cell for result output:", "KutoolsforExcel", Type:=8)
On Error Resume Next
For i = 1 To DataRange.Rows.Count
If CriteriaRange.Cells(i, 1).Value = CriteriaValue Then
If Not Dict.Exists(DataRange.Cells(i, 1).Value) Then
Dict.Add DataRange.Cells(i, 1).Value, 1
End If
End If
Next i
UniqueCount = Dict.Count
ResultCell.Value = UniqueCount
MsgBox "Unique count for '" & CriteriaValue & "': " & UniqueCount, vbInformation, "KutoolsforExcel"
End Sub- Sluit de VBA-editor en ga terug naar uw werkblad. Druk op Alt + F8, selecteer CountUniqueWithCriteria, en voer de macro uit.
- Volg de invoerprompts om de bereiken en criteria op te geven volgens uw gegevens. Het resultaat verschijnt in de cel die u kiest en ook als een berichtenvenster.
Uitleg van parameters en opmerkingen:
- Deze macro is momenteel ingesteld voor één criterium. Om het uit te breiden voor meerdere criteria, wijzig dan de
If ... Then-logica binnen de lus. - Sla altijd uw werkmap op voordat u macro's uitvoert, omdat wijzigingen niet ongedaan kunnen worden gemaakt.
- Schakel macro's in uw Excel-instellingen in als u problemen ondervindt bij het uitvoeren.
- Deze methode werkt goed voor grotere of vaak bijgewerkte gegevens, waarbij handmatige formules lastig zouden zijn.
Voordelen: Zeer aanpasbaar en automatiserbaar, verwerkt grote en veranderende datasets efficiënt. Geschikt voor geavanceerde of herhalende workflowbehoeften.
Nadelen: Vereist macro-rechten, en beginners kunnen tijd nodig hebben om vertrouwd te raken met VBA-bewerkingen.
Wanneer u werkt met unieke waarde tellingen op basis van criteria, controleer dan altijd uw bereikverwijzingen en zorg ervoor dat alle criteriakolommen qua grootte overeenkomen. Niet-overeenkomende bereiken zijn een veelvoorkomende bron van fouten of onjuiste resultaten. Als formules onverwachte resultaten retourneren, controleer dan op verborgen opmaakproblemen of lege cellen. Voor prestatiekritische scenario's bieden Draaitabellen en VBA robuuste alternatieven voor matrixformules. Kies de oplossing die het beste past bij uw comfortniveau en de complexiteit van uw dataset. Onthoud dat Kutools voor Excel extra hulpprogramma’s en snelkoppelingen biedt die veel van deze taken kunnen versnellen voor nog hogere efficiëntie in complexe werkboeken.
Beste productiviteitstools voor Office
Verbeter je Excel-vaardigheden met Kutools voor Excel en ervaar ongeëvenaarde efficiëntie. Kutools voor Excel biedt meer dan300 geavanceerde functies om je productiviteit te verhogen en tijd te besparen. Klik hier om de functie te kiezen die je het meest nodig hebt...
Office Tab brengt een tabbladinterface naar Office en maakt je werk veel eenvoudiger
- Activeer tabbladbewerking en -lezen in Word, Excel, PowerPoint, Publisher, Access, Visio en Project.
- Open en maak meerdere documenten in nieuwe tabbladen van hetzelfde venster, in plaats van in nieuwe vensters.
- Verhoog je productiviteit met50% en bespaar dagelijks honderden muisklikken!
Alle Kutools-invoegtoepassingen. Eén installatieprogramma
Kutools for Office-suite bundelt invoegtoepassingen voor Excel, Word, Outlook & PowerPoint plus Office Tab Pro, ideaal voor teams die werken met Office-toepassingen.
- Alles-in-één suite — invoegtoepassingen voor Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Eén installatieprogramma, één licentie — in enkele minuten geïnstalleerd (MSI-ready)
- Werkt beter samen — gestroomlijnde productiviteit over meerdere Office-toepassingen
- 30 dagen volledige proef — geen registratie, geen creditcard nodig
- Beste prijs — bespaar ten opzichte van losse aanschaf van invoegtoepassingen