Hoe unieke waarden in Excel samenvoegen?
Bij het werken met spreadsheets is het gebruikelijk om situaties tegen te komen waarin je alleen de unieke waarden uit een kolom moet samenvoegen (concateneren) of lijsten moet samenstellen die unieke items samenvatten met hun bijbehorende records. Het afhandelen van duplicaten en het presenteren van samengevatte informatie maakt niet alleen je gegevens netter, maar maakt ook rapporten duidelijker en informatiever. In Excel zijn er verschillende praktische manieren om deze doelen te bereiken, variërend van het gebruik van ingebouwde functies tot het benutten van geavanceerde invoegtoepassingen of aangepaste code. Deze handleiding biedt een gedetailleerde verkenning van meerdere methoden om unieke waarden samen te voegen en unieke items met hun gerelateerde gegevens weer te geven. De behandelde oplossingen zijn afgestemd op verschillende Excel-versies en gebruikersvoorkeuren, wat je helpt de beste aanpak voor jouw situatie te selecteren.
Alleen unieke waarden uit een kolom samenvoegen
- Door gebruik te maken van TEXTJOIN en UNIEKE functies
- Door gebruik te maken van Kutools AI Assistent
- Door gebruik te maken van een door de gebruiker gedefinieerde functie
- Door gebruik te maken van een geavanceerde Excel-formule (alternatieve oplossing)
Lijst met unieke waarden en samenvoegen van overeenkomstige waarden
- Door gebruik te maken van TEXTJOIN en UNIEKE functies
- Door gebruik te maken van Kutools voor Excel
- Door gebruik te maken van VBA-code
- Door gebruik te maken van Excel PivotTable met formules (alternatieve oplossing)
Alleen unieke waarden uit een kolom samenvoegen
Tijdens Excel-gegevensanalyse is een veelvoorkomende taak om alleen de unieke items in een kolom samen te voegen in één cel. Dit kan bijzonder nuttig zijn voor het maken van samenvattingsrapporten, het vermijden van dubbele waarden in een lijst of het voorbereiden van gegevens voor verdere verwerking. De keuze van methode hangt af van je Excel-versie, de grootte van je dataset en je bekendheid met formules of code. De volgende methoden behandelen verschillende behoeften, belichten overwegingen voor elk en bieden praktische tips om correcte uitvoering te garanderen.
Methode 1: Gebruik van TEXTJOIN en UNIEKE functies
Voor gebruikers van Excel365 en Excel2021 maakt de introductie van de TEXTJOIN en UNIEKE functies het combineren van unieke waarden uit een kolom zowel eenvoudig als flexibel.
Deze oplossing is het beste wanneer uw gegevenskolom continu is en u snel alle unieke items in één cel wilt samenvoegen met een gekozen scheidingsteken. Het verwijdert automatisch duplicaten, is gemakkelijk te controleren en stelt u in staat het bereik of scheidingsteken naar behoefte te wijzigen. Let op dat deze aanpak alleen beschikbaar is in de nieuwste Excel-versies; oudere versies ondersteunen de UNIEKE functie niet.
Voer in de cel waar u het resultaat wilt weergeven de volgende formule in (ervan uitgaande dat uw gegevens zich in cellen A2:A18 bevinden):
=TEXTJOIN(", ", TRUE, UNIQUE(A2:A18))
- UNIQUE(A2:A18) filtert duplicaten uit en retourneert alleen de unieke waarden uit het bereik A2:A18.
- TEXTJOIN(", ", TRUE, ...) combineert (samenvoegt) deze unieke waarden in één cel, gescheiden door een komma en spatie. Het argument TRUE zorgt ervoor dat lege cellen worden genegeerd bij de samenvoeging.
Handige tips en probleemoplossing:
- Zorg ervoor dat uw Excel-versie de UNIQUE en TEXTJOIN functies ondersteunt. Als u een #NAAM? fout ziet, gebruikt u mogelijk een oudere versie.
- Het scheidingsteken dat in TEXTJOIN wordt gebruikt, kan naar wens worden gewijzigd, zoals "; " of "|".
- Als u gegevens toevoegt of verwijdert in het originele bereik, wordt de formule automatisch bijgewerkt.
- Om onbedoelde extra spaties of scheidingstekens te voorkomen, controleer dan uw scheidingstekenargument in de formule.
Methode 2: Gebruik van Kutools AI Assistent
Wanneer u een snellere, volledig geautomatiseerde manier nodig hebt om unieke waarden samen te voegen - zonder formules te schrijven - biedt het hulpmiddel 'AI Assistent' van Kutools voor Excel een praktische oplossing die tijd bespaart voor gebruikers van alle vaardigheidsniveaus. Deze methode is vooral handig als u niet bekend bent met geavanceerde Excel-formules of als uw gegevens vaak veranderen en herhaalde taken vereisen.
Na installatie van Kutools voor Excel kunt u deze functie openen door te klikken op "Kutools" > "AI Assistent" om het paneel "Kutools AI Assistent" te openen.
- Selecteer de cellen die de waarden bevatten die u in één cel wilt combineren, waarbij u ervoor zorgt dat uw selectie overeenkomt met uw bedoelde gegevens.
- Typ in het chatvenster uw vereiste. Bijvoorbeeld, u kunt typen:
Unieke waarden samenvoegen met komma uit geselecteerd bereik en het samengevoegde resultaat in cel C2 plaatsen - Druk op de Enter toets of klik op de "Verzenden" knop. De AI analyseert uw verzoek en na verwerking drukt u op "Uitvoeren" om Kutools de operatie uit te laten voeren. Het resultaat wordt zoals beschreven geretourneerd.
Opmerkingen en tips:
- Controleer of u de meest recente versie van Kutools draait om alle AI-functies te kunnen gebruiken.
- Wees specifiek in uw tekstopdracht voor de beste resultaten; noem scheidingsteken en doelcel.
- Kutools AI is bijzonder efficiënt voor grote bereiken of workflows die op verschillende datasets moeten worden herhaald.
Methode 3: Gebruik van een door de gebruiker gedefinieerde functie
Voor gebruikers die geavanceerde flexibiliteit nodig hebben, aangepaste scheidingstekens willen of een herbruikbaar hulpmiddel voor meerdere werkboeken nodig hebben, is het coderen van een door de gebruiker gedefinieerde functie (UDF) in VBA een effectieve manier om automatisch unieke waarden samen te voegen. Deze VBA-oplossing is compatibel met alle Excel-versies en wordt niet beperkt door de beschikbaarheid van nieuwe functies.
- U moet macros in uw werkmap inschakelen.
- Sla het bestand op als "macro's ingeschakeld" (.xlsm) voor het geval u deze VBA-code in de toekomst blijft gebruiken.
- Regelmatige back-ups van uw werkmap voordat u nieuwe code uitvoert, worden aanbevolen.
1. Houd ALT + F11 ingedrukt om het Microsoft Visual Basic for Applications-venster te openen.
2. Klik in het VBA-venster op Invoegen > Module, kopieer en plak vervolgens de volgende code:
VBA-code: Unieke waarden samenvoegen in één cel:
Function ConcatUniq(xRg As Range, xChar As String) As String
'updateby Extendoffice
Dim xCell As Range
Dim xDic As Object
Set xDic = CreateObject("Scripting.Dictionary")
For Each xCell In xRg
xDic(xCell.Value) = Empty
Next
ConcatUniq = Join$(xDic.Keys, xChar)
Set xDic = Nothing
End Function
3. Ga terug naar uw werkblad en typ in een lege cel (bijvoorbeeld C2) de volgende formule:
=ConcatUniq(A2:A18,",")Druk op Enter om te bevestigen. De cel toont alle unieke waarden uit het opgegeven bereik, gescheiden door komma's.
- Als uw bereik anders is, pas A2:A18 dienovereenkomstig aan.
- Als een ander scheidingsteken nodig is, vervang dan "," in de formule met uw voorkeurssymbool (bijv., ";" of |).
- Als u een #NAAM? fout tegenkomt, controleer dan of macros zijn ingeschakeld en dat de UDF-naam exact overeenkomt.
Tip: Om deze functie in andere werkboeken te hergebruiken, kopieer de VBA-code ook naar hun modules.
Methode 4: Gebruik van een geavanceerde Excel-formule (alternatieve oplossing)
In omgevingen waar de UNIEKE functie niet beschikbaar is (bijvoorbeeld in Excel 2016 of Excel 2019), kunt u nog steeds unieke waarden samenvoegen met behulp van een complexere combinatie van de klassieke ALS, AANTAL.ALS, en TEXTJOIN functies arrayformules. Deze aanpak werkt, maar is het best geschikt voor kleinere datasets vanwege de rekentijd.
1. Voer in een doelcel (bijv. C2) de volgende arrayformule in (na invoer, druk op Ctrl+Shift+Enter in plaats van alleen Enter):
=TEXTJOIN(", ", TRUE, IF(MATCH(A2:A18, A2:A18,0) = ROW(A2:A18) - MIN(ROW(A2:A18)) +1, A2:A18, ""))2. Als u accolades {} rond uw formule ziet verschijnen, is deze correct ingevoerd als een arrayformule. De formule geeft samengevoegde unieke waarden uit het bereik A2:A18, gescheiden door komma's.
Opmerking: Deze methode vereist dat u de bereiken aanpast om overeen te komen met uw gegevens. Voor zeer grote bereiken kan de rekentijd toenemen. Als u niet op uw gemak bent met arrayformules, overweeg dan de VBA- of invoegtoepassingsoplossingen hierboven beschreven.
Lijst met unieke waarden en samenvoegen van overeenkomstige waarden
Vaak in gegevensrapportage wilt u niet alleen de unieke waarden uit één kolom extraheren, maar ook de bijbehorende items uit een andere kolom samenvoegen. Bijvoorbeeld, alle producten verkocht door elke verkoper samenvatten of alle items die aan hetzelfde ID zijn gekoppeld, compileren. De juiste methode kiezen hangt af van de complexiteit van uw gegevens en of automatisering, gebruiksgemak of compatibiliteit uw prioriteit is.
Methode 1: Gebruik van TEXTJOIN en UNIEKE functies
Wanneer u Excel 365 of Excel 2021 gebruikt, kunt u de UNIEKE en FILTER functies combineren met TEXTJOIN voor een robuuste, volledig formule-gedreven aanpak. Deze methode werkt goed voor het samenvatten van gegevens waarbij één waarde kan corresponderen met meerdere records en u een door scheidingstekens gescheiden lijst van die gerelateerde records wilt.
1. Voer in een lege kolom de volgende formule in om alle unieke waarden uit Kolom A weer te geven:
=UNIQUE(A2:A17)
2. Voer nu, om de overeenkomstige waarden uit Kolom B voor elk uniek item samen te voegen, in de volgende kolom naast uw unieke waarde (bijv. E2 als uw unieke waarden beginnen bij D2), deze formule in en sleep omlaag indien nodig:
=TEXTJOIN(", ", TRUE, FILTER($B$2:$B$17, $A$2:$A$17 =D2))
- UNIQUE(A2:A17) maakt een array van unieke items uit Kolom A.
- FILTER(B2:B17, A2:A17 = D2) genereert een array met alle overeenkomstige waarden uit Kolom B voor elk uniek item in D2.
- TEXTJOIN(", ", TRUE, ...) voegt deze overeenkomstige waarden samen, gescheiden door komma's.
- Als u een ander scheidingsteken nodig heeft, wijzig dan ", " in TEXTJOIN dienovereenkomstig.
- Om fouten te voorkomen, zorg ervoor dat de bereiken in uw formules even lang zijn en dat FILTER geen fouten retourneert voor ontbrekende matches.
- Deze aanpak werkt resultaten automatisch bij terwijl uw gegevens veranderen, waardoor het geschikt is voor dynamische samenvattingstabellen.
Methode 2: Gebruik van Kutools voor Excel
Kutools voor Excel heeft een "Geavanceerd samenvoegen van rijen"-hulpmiddel speciaal ontworpen voor het groeperen van gegevens op basis van unieke waarden en het samenvoegen van overeenkomstige waarden met uw voorkeurscheidingsteken. Dit is geschikt voor gebruikers die een grafische oplossing willen en niet bekend zijn met het schrijven van formules of code. Het is vooral waardevol bij het omgaan met grote datasets of wanneer frequent hergroeperen nodig is, zoals in periodieke rapporten of lopend gegevensonderhoud.
Voordat u wijzigingen aanbrengt, is het een goede gewoonte om uw gegevens te back-uppen door de originele gegevens naar een andere locatie te kopiëren. Volg vervolgens deze stappen:
- Selecteer het gegevensbereik dat u wilt organiseren.
- Navigeer naar "Kutools" > "Samenvoegen & Splitsen" > "Geavanceerd samenvoegen van rijen", zoals hieronder geïllustreerd:
- In het dialoogvenster dat opent:
- Selecteer de kolom met duplicaten die samengevoegd moeten worden en stel deze in als "Primaire sleutel" in de "Operatie" kolom.
- Kies de kolom die u wilt aggregeren (de waarden om samen te voegen); specificeer uw voorkeurscheidingsteken in de vervolgkeuzelijst onder "Operatie".
- Klik op OK om uit te voeren.
Resultaat:
Kutools zal uw gegevens reorganiseren, unieke items extraheren en alle bijbehorende waarden samenvoegen, gebaseerd op uw instellingen.
- Als u een fout maakt, gebruik dan de Ongedaan maken-functie in Excel (Ctrl+Z) om terug te keren.
- Het proces werkt voor datasets met mogelijk honderden of duizenden records en ondersteunt een verscheidenheid aan scheidingstekens.
Methode 3: Gebruik van VBA-code
Het gebruik van een VBA-script biedt volledige controle over hoe u gegevens extraheert en samenvat. Deze aanpak is compatibel met alle versies van Excel en is vooral geschikt voor aangepaste workflows, automatisering of wanneer functies zoals UNIEKE of FILTER niet beschikbaar zijn. Als uw gegevensstructuur vaak verandert, kan deze VBA-oplossing gemakkelijk worden aangepast.
Volg deze stappen om de onderstaande code te gebruiken:
1. Druk op ALT + F11 om de VBA-editor te openen.
2. Ga naar Invoegen > Module, plak vervolgens de volgende code in het geopende modulevenster:
VBA-code: Lijst met unieke waarden en samenvoegen van overeenkomstige gegevens
Sub test()
'updateby Extendoffice
Dim xRg As Range
Dim xArr As Variant
Dim xCell As Range
Dim xTxt As String
Dim I As Long
Dim xDic As Object
Dim xOutputRg As Range
On Error Resume Next
xTxt = ActiveWindow.RangeSelection.Address
Set xRg = Application.InputBox("Please select the data range", "Kutools for Excel", xTxt, , , , , 8)
Set xRg = Application.Intersect(xRg, xRg.Worksheet.UsedRange)
If xRg Is Nothing Then Exit Sub
If xRg.Areas.Count > 1 Then
MsgBox "Does not support multiple selections", , "Kutools for Excel"
Exit Sub
End If
If xRg.Columns.Count <> 2 Then
MsgBox "There must be only two columns in the selected range", , "Kutools for Excel"
Exit Sub
End If
Set xOutputRg = Application.InputBox("Please select the output cell", "Kutools for Excel", Type:=8)
If xOutputRg Is Nothing Then Exit Sub
xArr = xRg
Set xDic = CreateObject("Scripting.Dictionary")
xDic.CompareMode = 1
For I = 1 To UBound(xArr)
If Not xDic.Exists(xArr(I, 1)) Then
xDic.Item(xArr(I, 1)) = xDic.Count + 1
xArr(xDic.Count, 1) = xArr(I, 1)
xArr(xDic.Count, 2) = xArr(I, 2)
Else
xArr(xDic.Item(xArr(I, 1)), 2) = xArr(xDic.Item(xArr(I, 1)), 2) & "," & xArr(I, 2)
End If
Next
xOutputRg.Resize(xDic.Count, 2).Value = xArr
End Sub
3. Druk op F5 om het script uit te voeren. Er verschijnt een pop-up die vraagt om het gegevensbereik te selecteren. Zorg ervoor dat u precies twee kolommen selecteert: de eerste voor unieke waarden en de tweede voor overeenkomstige waarden.
4. Klik op OK en selecteer de eerste cel waar de resultaattabel moet beginnen.
5. Nadat u op OK hebt geklikt, genereert de code een tabel met alleen unieke waarden en hun bijbehorende samengevoegde gegevens.
- Als u een foutmelding krijgt over het aantal kolommen, controleer dan of uw selectie slechts twee kolommen omvat.
- Als uw scheidingsteken moet veranderen van een komma naar een ander symbool, pas dan de code aan in de regel
xArr(xDic.Item(xArr(I,1)),2) = xArr(xDic.Item(xArr(I,1)),2) & "," & xArr(I,2)naar behoefte. - Maak altijd een back-up van uw bestand voordat u nieuwe VBA-scripts uitvoert.
Samenvattend biedt Excel een verscheidenheid aan benaderingen voor het samenvoegen van unieke waarden en het consolideren van gerelateerde gegevens. Formulemethoden zijn snel en dynamisch in moderne Excel, terwijl VBA- en Kutools-oplossingen bredere compatibiliteit en meer controle bieden. Kies altijd de methode die past bij uw gegevensgrootte, Excel-versie en voorkeursworkflow. Vergeet niet om te testen op voorbeeldgegevens of uw werk te back-uppen, vooral bij het proberen van scriptgebaseerde of batchbewerkingen. Voor verdere Excel-hulp en geavanceerde tips, bekijk onze volledige verzameling handleidingen.
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