Hoe unieke waarden op basis van criteria in Excel optellen?
Bij het werken met Excel-gegevenssets — zoals orderlogs, financiële records of enquête resultaten — kom je vaak in de situatie dat je de som moet berekenen van unieke waarden uit één kolom, gebaseerd op filters of criteria in een andere kolom. Stel bijvoorbeeld dat je een datatabel hebt met twee kolommen: Naam en Bestelling. Als je de som wilt berekenen van alleen de unieke Bestelling-waarden voor elke Naam (terwijl herhaalde waarden genegeerd worden), hoe kun je dit dan efficiënt doen in Excel? Dit is een veelvoorkomende vereiste in veel zakelijke of data-analyse scenario's waarbij simpelweg alle overeenkomende items optellen zou leiden tot opgeblazen resultaten door duplicaten.
De onderstaande voorbeeldscreenshot demonstreert een typisch scenario: gegeven een lijst van namen en hun bijbehorende bestelwaarden — inclusief duplicaten — wil je de gegevens samenvatten door unieke bestelwaarden voor elke naam apart op te tellen.
Veelvoorkomende uitdagingen bij deze taak omvatten het identificeren van unieke items op basis van specifieke criteria, ervoor zorgen dat alleen de eerste instantie wordt geteld, en het voorkomen van handmatige fouten die kunnen ontstaan door gefilterde gegevens te kopiëren en plakken. Verschillende praktische Excel-benaderingen, waaronder array-formules, Kutools en Power Query, kunnen je helpen dit probleem op te lossen, elk geschikt voor verschillende gebruiksscenario's.
- Unieke waarden optellen op basis van een of meer criteria met array-formules
- Unieke waarden optellen op basis van criteria met behulp van Kutools voor Excel’s Geavanceerd Samenvoegen van Rijen
- Andere ingebouwde Excel-methoden: Gebruik een draaitabel voor unieke somanalyse
<h4"> Unieke waarden optellen op basis van een of meer criteria met array-formules
Eén effectieve en flexibele aanpak is het gebruik van array-formules, waarmee je unieke waarden kunt samenvatten die voldoen aan specifieke criteria. Dit werkt vooral goed wanneer je de berekening automatisch wilt laten bijwerken als de gegevens of criteria veranderen.
Om alleen de unieke waarden in een kolom op te tellen volgens het filter of de conditie in een andere kolom, kun je de volgende formule toepassen:
1. Voer in een lege cel (bijvoorbeeld E2) deze formule in:
=SUM(IF(FREQUENCY(IF($A$2:$A$12=D2,MATCH($B$2:$B$12,$B$2:$B$12,0)),ROW($B$2:$B$12)-ROW($B$2)+1),$B$2:$B$12))Controleer voordat je de formule bevestigt zorgvuldig of:
- A2:A12: Het bereik dat de criteria bevat (in dit geval namen).
- D2: De cel waar je doelconditie (zoals een specifieke naam) zich bevindt.
- B2:B12: Het bereik van waarden dat je uniek wilt optellen.
Je kunt deze bereiken indien nodig aanpassen op basis van je daadwerkelijke gegevensstructuur. Zorg ervoor dat alle bereiken even lang zijn om formulefouten te voorkomen.
2. Om deze array-formule te activeren, druk je tegelijkertijd op Ctrl + Shift + Enter nadat je het hebt ingevoerd. Accolades verschijnen rond de formule, wat aangeeft dat het een array-formule is. Sleep vervolgens de vulgreep naar beneden om de formule te kopiëren voor elke bijbehorende waarde in je samenvattingskolom. Op deze manier krijgt elk item automatisch de juiste unieke som.
Praktische tip: Als je Excel 365 of Excel 2021 gebruikt, kunnen nieuwe dynamische array-functies zoals UNIEK en SOM.ALS sommige van deze berekeningen verder vereenvoudigen, maar de bovenstaande formule werkt betrouwbaar in veel Excel-versies.
=SOM.ALS(A2:B12, UNIEK(D2), B2:B12)
Omgaan met meer criteria: =SOM(SOMMEN.ALS(sombereik, criteriabereik1, UNIEK(criteriabereik1), [criteriabereik2, criteria2], ...)
Voorzorgsmaatregelen:
- Zorg ervoor dat je array-formule invoer gebruikt (Ctrl + Shift + Enter) als je Excel 2019 of eerder gebruikt. Voor Excel 365/2021 kan een gewone Enter voldoende zijn voor dynamische formules.
- Als je gegevensbereiken erg groot zijn, kan de array-aanpak langzamer worden, dus overweeg om je gegevens eerst te filteren of andere methoden te gebruiken voor zeer grote datasets.
- Let goed op extra spaties of consistentie in gegevenstypen — ongelijkmatige tekst- of nummeropmaak kan leiden tot niet-overeenkomende fouten.
Tips: Als je alle unieke waarden moet optellen op basis van twee criteria, kan de volgende uitbreiding van de array-formule worden gebruikt:
=SUM(IF(FREQUENCY(IF($A$2:$A$12=E2,IF($B$2:$B$12=F2,MATCH($C$2:$C$12,$C$2:$C$12,0))),ROW($C$2:$C$12)-ROW($C$2)+1),$C$2:$C$12))Deze formule is in principe vergelijkbaar, maar ondersteunt een extra filter uit kolom B (nu vergeleken met F2 als secundaire voorwaarde), en telt unieke waarden uit kolom C bij elkaar op. Nadat je deze formule hebt ingevoerd in je geselecteerde samenvattingscel, gebruik je Ctrl + Shift + Enter om het te bevestigen, en pas je het vervolgens toe op andere samenvattingsrijen indien nodig.
Samenvattingsuggestie: Hoewel array-formules nauwkeurige resultaten opleveren voor de meeste scenario's, controleer altijd dubbel op verborgen duplicaten (zoals die met extra spaties of verschillen in tekstopmaak) en zorg ervoor dat je samenvattingsgebieden gegevens halen uit correct gefilterde lijsten.
Ontdek de Magie van Excel met Kutools AI
- Slimme Uitvoering: Voer celbewerkingen uit, analyseer gegevens en maak diagrammen – allemaal aangestuurd door eenvoudige commando's.
- Aangepaste Formules: Genereer op maat gemaakte formules om uw workflows te versnellen.
- VBA-codering: Schrijf en implementeer VBA-code moeiteloos.
- Formule-uitleg: Begrijp complexe formules gemakkelijk.
- Tekstvertaling: Overbrug taalbarrières binnen uw spreadsheets.
Unieke waarden optellen op basis van criteria met behulp van Kutools voor Excel’s Geavanceerd Samenvoegen van Rijen
Kutools voor Excel’s Geavanceerd Samenvoegen van Rijen maakt het mogelijk om alleen unieke waarden op te tellen op basis van een specifieke voorwaarde zonder moeite! Met slechts een paar klikken groepeert het je gegevens intelligent en past het aangepaste samenvattingslogica toe — geen formules, geen gedoe, alleen nauwkeurige resultaten.
Stap 1: Selecteer uw datatabel
Markeer de volledige tabel inclusief de koppen.
Stap 2: Ga naar Kutools > Tekst > Geavanceerd Samenvoegen van Rijen.
Stap 3: Stel de groeperingskolom in
In het pop-up dialoogvenster kies je de kolom waarop je wilt groeperen (bijvoorbeeld Fruit), stel deze in als Primaire Sleutel in de Bewerking sectie.
Stap 4: Stel het veld in voor unieke som
Selecteer de Verkoop kolom, stel de berekening in die je wilt (bijvoorbeeld Som) in de Bewerking sectie.
Tip: Je kunt het gecombineerde resultaat in het dialoogvenster bekijken.
Stap 5: Klik op OK De tabel is nu gegroepeerd per klant, elk met de som van unieke producthoeveelheden.
Andere ingebance Excel-methoden: Gebruik een draaitabel voor unieke somanalyse
Excel's Draaitabel-functie biedt een andere krachtige ingebouwde aanpak om gegevens samen te vatten op basis van criteria. Hoewel Draaitabellen standaard geen unieke waarden optellen, ondersteunen ze sinds Excel 2013 de Aantal Uniek-berekening, wat je helpt de unieke aantallen voor een gespecificeerd veld te analyseren. Hoewel dit de som van unieke waarden niet direct berekent, kun je Aantal Uniek combineren met handmatige aanpassing of een berekend veld om een vergelijkbare samenvatting te faciliteren.
Voordelen: Draaitabellen vereisen geen formule-onthouden of VBA-codering, en bieden een zeer flexibele sleep-en-drop interface. Geschikt voor periodieke rapportage, groepanalyse, snelle overzichten of bij samenwerking tussen teams. Ze zijn echter het beste geschikt voor samenvatting en analyse, niet voor het construeren van formules voor verdere berekeningen of automatisering.
Hier is hoe je een draaitabel gebruikt voor unieke somanalyse:
- Selecteer je gegevensbereik (bijvoorbeeld A1:B12 inclusief koppen), en ga naar Invoegen > Draaitabel. In het dialoogvenster selecteer je of je de draaitabel in een nieuw werkblad of een bestaand wilt plaatsen.
- Sleep in de Draaitabel Veldenlijst Naam naar het Rijen gebied, en Bestelling naar het Waarden gebied.
- Voor de Bestelling-items in het Waarden gebied, klik op de vervolgkeuzepijl > Waardeveldinstellingen > instellen op Som (toont de totale bestelsom inclusief duplicaten).
Beperkingen:
- De functie Aantal Uniek is alleen beschikbaar in Excel 2013 of nieuwer; eerdere versies vereisen meer handmatig werk.
Samenvattend, terwijl een draaitabel uitstekend is voor interactieve samenvatting en analyse, overweeg deze te combineren met formule- of VBA-gebaseerde benaderingen als je echte unieke somberekeningen nodig hebt.
Meer gerelateerde artikelen:
- Optellen van meerdere kolommen op basis van één criterium in Excel
- In Excel moet je vaak meerdere kolommen optellen op basis van één criterium. Bijvoorbeeld, ik heb een reeks gegevens zoals in de volgende screenshot weergegeven, en nu wil ik de totale waarden van KTE in drie maanden - januari, februari en maart - verkrijgen.
- Vlookup en somovereenkomsten in rijen of kolommen in Excel
- Het gebruik van vlookup en de somfunctie helpt je snel de gespecificeerde criteria vinden en tegelijkertijd de bijbehorende waarden optellen. In dit artikel gaan we je twee methoden laten zien om vlookup te gebruiken en de eerste of alle overeenkomende waarden in rijen of kolommen in Excel op te tellen.
- Waarden optellen op basis van maand en jaar in Excel
- Als je een reeks gegevens hebt, waar kolom A enkele datums bevat en kolom B het aantal bestellingen, moet je nu de cijfers optellen op basis van maand en jaar uit een andere kolom. In dit geval wil ik de totale bestellingen van januari 2016 berekenen om het volgende resultaat te krijgen. En in dit artikel zal ik het hebben over enkele trucs om deze taak in Excel op te lossen.
- Waarden optellen op basis van tekstcriteria in Excel
- In Excel heb je misschien geprobeerd waarden op te tellen op basis van een andere kolom met tekstcriteria. Bijvoorbeeld, ik heb een reeks gegevens in het werkblad zoals in de volgende screenshot weergegeven, en nu wil ik alle getallen in kolom B optellen die overeenkomen met de tekstwaarden in kolom A die voldoen aan een bepaald criterium, zoals de getallen optellen als de cellen in kolom A KTE bevatten.
- Waarden optellen op basis van de selectie van een keuzelijst in Excel
- Zoals in de onderstaande screenshot weergegeven, heb je een tabel met een Categorie-kolom en een Bedrag-kolom, en je hebt een validatie keuzelijst gemaakt die alle categorieën bevat. Wanneer je een bepaalde categorie selecteert uit de keuzelijst, wil je alle bijbehorende celwaarden in Kolom B optellen en het resultaat in een gespecificeerde cel plaatsen. Bijvoorbeeld, wanneer je categorie CC selecteert uit de keuzelijst, moet je de waarden in cel B5 en B8 optellen en het totaalnummer 40+70=110 krijgen. Hoe kun je dit bereiken? De methode in dit artikel kan je helpen.
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