Hoe unieke waarden uit meerdere kolommen in Excel extraheren?
Als je vaak met datasets werkt die verspreid zijn over verschillende kolommen in Excel, kun je situaties tegenkomen waarin bepaalde waarden gedupliceerd zijn binnen dezelfde kolom of tussen verschillende kolommen. Bij veel rapportage- of data-analysetaken is het noodzakelijk om alle unieke waarden te identificeren en te extraheren—diegene die slechts één keer voorkomen in de hele selectie, ongeacht waar ze zich bevinden. Dit handmatig doen kan tijdrovend en foutgevoelig zijn, vooral bij het omgaan met grote datasets of complexe tabellen. Gelukkig biedt Excel een scala aan methoden om deze unieke waarden efficiënt te extraheren.
Deze handleiding introduceert verschillende oplossingen die je kunt gebruiken, afhankelijk van je Excel-versie en voorkeuren—zoals formules die geschikt zijn voor alle versies, dynamische arrayformules voor recente versies, het gebruik van Kutools AI Assistent voor eenvoudige resultaten, draaitabellen voor visuele consolidatie en VBA-code voor geautomatiseerde extractie in complexe scenario's.
Unieke waarden uit meerdere kolommen extraheren met formules
Er zijn momenten waarop je deze extractie wilt uitvoeren met ingebouwde Excel-functies. Deze sectie legt uit hoe je dit kunt doen met behulp van twee benaderingen: een arrayformule die geschikt is voor alle versies van Excel en een dynamische arrayformule die beschikbaar is in nieuwere versies zoals Excel 365 en Excel 2021. Deze methoden zijn ideaal wanneer je een directe op formules gebaseerde oplossing wilt, frequent moet updaten naarmate je gegevens veranderen of externe invoegtoepassingen en code wilt vermijden.
Unieke waarden uit meerdere kolommen extraheren met een arrayformule voor alle Excel-versies
Voor compatibiliteit met alle Excel-versies laat het gebruik van een arrayformule je toe om unieke waarden uit verschillende kolommen te extraheren, zelfs als je Excel geen ondersteuning biedt voor dynamische arrays. Deze methode maakt gebruik van een combinatie van INDIRECT, TEKST, MIN, ALS, AANTAL.ALS, RIJ en KOLOM functies, wat het flexibel maakt voor verschillende gegevensstructuren.
Stel dat je gegevens zich bevinden in het bereik A2:C9. Om de unieke waarden te extraheren beginnend in cel E2, volg je de volgende procedure:
1. Klik op cel E2 (of de eerste cel van je uitvoerbereik) en voer de volgende arrayformule in:
=INDIRECT(TEKST(MIN(ALS(($A$2:$C$9<>"")*(AANTAL.ALS($E$1:E1,$A$2:$C$9)=0),RIJ($2:$9)*100+KOLOM($A:$C),7^8)),"R0K00"),)&""
- A2:C9 is het databereik waaruit je unieke waarden wilt extraheren.
- E1:E1 verwijst naar de cellen direct boven je eerste uitvoercel en is nodig om bij te houden welke items al zijn uitgevoerd.
- $2:$9 zijn de rijverwijzingen van je gegevens; $A:$C zijn de kolomverwijzingen. Pas deze zo nodig aan om te passen bij je eigen werkbladindeling.
2. Nadat je de formule hebt ingevoerd, druk je in plaats van alleen Enter tegelijkertijd op Ctrl + Shift + Enter om het te bevestigen als een arrayformule. Als dit correct wordt gedaan, verschijnen accolades {} rond je formule in de formulebalk. Sleep vervolgens de vulgreep vanaf E2 naar beneden in de kolom. Blijf slepen tot er lege cellen verschijnen, wat aangeeft dat er geen unieke waarden meer over zijn om te extraheren. Dit proces zorgt ervoor dat alle unieke waarden worden weergegeven in de doelkolom.
- $A$2:$C$9: Geeft de hele set cellen aan die moet worden onderzocht op unieke waarden.
- ALS(($A$2:$C$9<>"")*(AANTAL.ALS($E$1:E1,$A$2:$C$9)=0), RIJ($2:$9)*100+KOLOM($A:$C),7^8):
- $A$2:$C$9<>"" zorgt ervoor dat lege cellen worden genegeerd.
- AANTAL.ALS($E$1:E1,$A$2:$C$9)=0 zorgt ervoor dat alleen nieuwe (nog niet geëxtraheerde) waarden worden opgenomen.
- Als beide voorwaarden waar zijn, is de bijbehorende uitkomst een berekening op basis van de rij en kolom van de cel om een uniek indexnummer te genereren.
- Als een van beide voorwaarden onwaar is, retourneert de formule een heel groot getal (7^8) om onbedoelde selectie te voorkomen.
- MIN(...): Identificeert het laagste indexnummer, waardoor effectief de positie van de volgende beschikbare unieke waarde binnen de gegevens wordt gelokaliseerd.
- TEKST(...,"R0K00"): Verandert de index in een geldige celverwijzing met de R1K1-stijl.
- INDIRECT(...): Converteert de hierboven gemaakte celverwijzing naar een waarde uit je databereik.
- &"": Zorgt ervoor dat het formuleresultaat wordt behandeld als tekst, om verrassingen in de vormgeving te voorkomen.
Unieke waarden uit meerdere kolommen extraheren met een formule voor Excel 365, Excel 2021 en nieuwere versies
Als je Excel 365, Excel 2021 of een nieuwere versie gebruikt, heb je toegang tot dynamische arrayfuncties, die een eenvoudigere en intuïtievere manier bieden om unieke waarden uit meerdere kolommen te extraheren. De functies UNIEK en TOCOL maken het gemakkelijker en sneller om gegevens uit kolommen te combineren en duplicaten in één stap te elimineren—bijzonder handig voor wie werkt met constant bijgewerkte of grotere datasets.
Om deze methode te gebruiken, selecteer je gewoon een lege cel (bijvoorbeeld E2, of waar je de resultaten wilt laten verschijnen), voer je deze formule in en druk je op Enter:
=UNIQUE(TOCOL(A2:C9,1))Nadat je op Enter hebt gedrukt, zullen alle unieke waarden uit het bereik A2:C9 automatisch in de cellen onder de formule 'spillen'. Deze functie is bijzonder efficiënt—de output wordt dynamisch bijgewerkt als je brongegevens veranderen, waardoor je handmatige vernieuwingsstappen bespaart.
- TOCOL(A2:C9,1): Converteert je reeks waarden uit meerdere kolommen naar één kolom, waarbij lege cellen automatisch worden verwijderd.
- UNIEK(...): Extraheert elke waarde slechts één keer, wat een schone, gedupliceerde lijst oplevert.
Unieke waarden uit meerdere kolommen extraheren met Kutools AI Assistent
Als je een gestroomlijndere aanpak wilt en de handmatige inspanning wilt verminderen, kan Kutools AI Assistent in Kutools voor Excel je helpen om gemakkelijk unieke waarden uit meerdere kolommen te extraheren. Deze methode is bijzonder waardevol als je niet bekend bent met formules of het risico van formulefouten wilt vermijden. Kutools AI Assistent interpreteert je instructies en verwerkt de gegevens automatisch, wat ideaal is voor beginners en gebruikers die op zoek zijn naar een snelle oplossing in slechts een paar klikken.
Na installatie, klik op Kutools AI > AI Assistent om het "Kutools AI Assistent" paneel te openen:
- Voer je verzoek in het chatvenster in, zoals: "Extraheer unieke waarden uit het bereik A2:C9, negeer lege cellen, en plaats de resultaten beginnend bij E2:"
- Klik op "Verzenden" of druk op Enter, en nadat AI het verzoek heeft geanalyseerd, klik gewoon op "Uitvoeren" om te starten. De resultaten verschijnen onmiddellijk in je werkblad, op de exacte locatie die je hebt aangegeven.
Tip: Deze oplossing is erg nuttig als je data-extractiewerkstroom varieert of als je natuurlijke taalverwerkingsfuncties wilt. Onthoud om de geëxtraheerde lijst te controleren op lege cellen als je originele gegevens niet perfect consistent zijn, omdat lege items mogelijk zijn opgenomen of gefilterd op basis van je AI-verzoekdetails.
Unieke waarden uit meerdere kolommen extraheren met een draaitabel
Draaitabellen zijn een andere handige methode om unieke waarden te extraheren, vooral als je liever met visuele tools werkt en de unieke items wilt samenvatten of verder analyseren, zoals het tellen van voorkomens. Deze aanpak is eenvoudig en vereist geen formules. Er zijn echter een paar stappen nodig voor de setup en lichte gegevensherordenen, vooral als de betrokken kolommen verschillende koppen hebben.
Hier is een voorgestelde procedure om unieke waarden te extraheren met behulp van een draaitabel:
1. Voeg een nieuwe lege kolom in direct links van je gegevens. Bijvoorbeeld, voeg een nieuwe kolom A in als je gegevens beginnen bij kolom B. Deze aanpassing helpt ervoor te zorgen dat het bereik correct wordt samengevoegd.
2. Selecteer een willekeurige cel binnen je dataset, druk op Alt + D, en druk snel op P om de "Draaitabel en Draaigrafiek Wizard" te starten. In de eerste stap van de wizard, selecteer "Meerdere consolidatiebereiken." Dit stelt je in staat om waarden uit vele kolommen te combineren in een enkel samengevat veld.
3. Klik op Volgende, en kies "Maak een enkel paginaveld voor mij." Deze stap organiseert al je gegevens als één groep voor gemakkelijker extractie van unieke waarden.
4. In de volgende stap, selecteer het hele databereik (inclusief de nieuwe lege kolom), klik op de knop Toevoegen om je selectie in de lijst "Alle bereiken" te brengen, en klik op Volgende.
5. In de laatste stap van de wizard, selecteer waar je de draaitabel wilt plaatsen (nieuw werkblad of bestaand blad), en klik op Voltooien om het draaitabelrapport te genereren.
6. In de nieuwe draaitabel, vink alle velden uit in de sectie "Kies velden om toe te voegen aan rapport" om de standaardweergave te wissen.
7. Ten slotte, sleep het veld "Waarde" naar het Rijengebied. De draaitabel toont alle unieke waarden uit je originele multikolombereik, netjes georganiseerd in één kolom.
Beperkingen: Gegevens hebben voorlopige rangschikking nodig, en als je brongegevens worden bijgewerkt, moet je de draaitabel vernieuwen om nieuwe unieke waarden te zien.
Unieke waarden uit meerdere kolommen extraheren met VBA-code
In gevallen waarin je de extractie moet automatiseren of grote en onregelmatige datasets moet verwerken, kan het gebruik van VBA (Visual Basic for Applications)-code een snelle en herbruikbare oplossing bieden. Dit is ideaal voor gebruikers met basiskennis van de Excel VBA-editor, of voor terugkerende taken waarbij je handmatige operaties wilt minimaliseren. VBA kan ook grote hoeveelheden gegevens efficiënter verwerken dan arrayformules.
1. Open de VBA-editor door op Alt + F11 te drukken. In het venster "Microsoft Visual Basic for Applications" dat verschijnt, klik op Invoegen > Module om een nieuwe module toe te voegen.
2. Plak de volgende code in de nieuwe module:
VBA: Unieke waarden uit meerdere kolommen extraheren
Sub Uniquedata()
'Updateby Extendoffice
Dim rng As Range
Dim InputRng As Range, OutRng As Range
Set dt = CreateObject("Scripting.Dictionary")
xTitleId = "KutoolsforExcel"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Range :", xTitleId, InputRng.Address, Type:=8)
Set OutRng = Application.InputBox("Out put to (single cell):", xTitleId, Type:=8)
For Each rng In InputRng
If rng.Value <> "" Then
dt(rng.Value) = ""
End If
Next
OutRng.Range("A1").Resize(dt.Count) = Application.WorksheetFunction.Transpose(dt.Keys)
End Sub
3. Druk op F5 om de code uit te voeren. Een dialoogvenster vraagt je om het databereik te selecteren. Selecteer alle relevante kolommen (inclusief die met lege cellen).
4. Na het klikken op OK, vraagt een andere prompt waar de unieke waarden moeten worden uitgevoerd. Specificeer een bovenste cel waar je de resultaten wilt hebben (bijvoorbeeld E2).
5. Klik op OK, en de macro wordt automatisch uitgevoerd. Alle unieke waarden verschijnen, beginnend op de gespecificeerde locatie.
- Als je fouten zoals #WAARDE! of #SPILL! ontvangt bij het gebruik van formules, controleer dan je bereiken en zorg ervoor dat het uitvoergebied vrij is.
- Controleer altijd op verborgen rijen of samengevoegde cellen in je databereik, omdat deze de juistheid van je unieke waardeextractie kunnen beïnvloeden.
- Array- en dynamische arrayformules worden automatisch bijgewerkt bij wijzigingen, maar Advanced Filter- en draaitabeloplossingen kunnen handmatige vernieuwing of opnieuw uitvoeren vereisen.
- Voor terugkerende taken, overweeg automatisering van extractie met behulp van VBA voor consistentie en snelheid.
- Maak een back-up van je gegevens voordat je massale extractie- of automatiseringsroutines toepast, vooral in complexe werkboeken.
Meer gerelateerde artikelen:
- Tel het aantal unieke en distincte waarden uit een lijst
- Stel dat je een lange lijst met waarden hebt met enkele dubbele items, en je wilt weten hoeveel unieke waarden (de waarden die slechts één keer voorkomen) of totale distincte waarden er in een kolom voorkomen, zoals te zien is in de linker screenshot. Dit artikel legt efficiënte methoden uit voor het tellen van unieke en distincte items in Excel.
- Extraheer unieke waarden op basis van criteria in Excel
- Stel dat je alleen de unieke namen uit kolom B wilt extraheren op basis van een specifieke voorwaarde in kolom A, met resultaten zoals te zien is in de screenshot. Deze tutorial demonstreert manieren om criteria toe te passen bij het extraheren van unieke waarden.
- Alleen unieke waarden toestaan in Excel
- Als je alleen unieke invoer in een werkbladkolom wilt toestaan en dubbele waarden wilt voorkomen, introduceert dit artikel praktische technieken om uniekheidsregels in Excel af te dwingen.
- Som unieke waarden op basis van criteria in Excel
- Bijvoorbeeld, je moet mogelijk alleen de unieke waarden in een "Order"-kolom optellen op basis van namen in een aangrenzende kolom, zoals te zien is in de screenshot. Dit artikel bespreekt benaderingen om unieke en conditionele berekeningen te combineren.
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