Hoe unieke waarden uit meerdere kolommen in Excel te extraheren?
Stel dat u meerdere kolommen met meerdere waarden heeft, sommige waarden worden in dezelfde kolom of in een andere kolom herhaald. En nu wilt u de waarden vinden die in beide kolommen slechts één keer voorkomen. Zijn er snelle trucs om unieke waarden uit meerdere kolommen in Excel te extraheren?
Extraheer unieke waarden uit meerdere kolommen met formules
In deze sectie worden twee formules behandeld: de ene gebruikt een matrixformule die geschikt is voor alle Excel-versies, en de andere gebruikt een dynamische matrixformule specifiek voor Excel 365.
Extraheer unieke waarden uit meerdere kolommen met de matrixformule voor alle Excel-versies
Voor gebruikers met elke versie van Excel kunnen matrixformules een krachtig hulpmiddel zijn voor het extraheren van unieke waarden over meerdere kolommen. Hier ziet u hoe u het kunt doen:
1. Ervan uitgaande dat uw waarden binnen bereik zijn A2: C9, voer de volgende formule in cel E2 in:
=INDIRECT(TEXT(MIN(IF(($A$2:$C$9<>"")*(COUNTIF($E$1:E1,$A$2:$C$9)=0),ROW($2:$9)*100+COLUMN($A:$C),7^8)),"R0C00"),)&""
2. Druk vervolgens op Shift+Ctrl+Enter toetsen samen en sleep vervolgens de vulgreep om de unieke waarden te extraheren totdat er lege cellen verschijnen. Zie screenshot:
- $ A $ 2: $ C $ 9: dit specificeert het gegevensbereik dat moet worden gecontroleerd, namelijk de cellen van A2 tot C9.
- IF(($A$2:$C$9<>"")*(COUNTIF($E$1:E1,$A$2:$C$9)=0), ROW($2:$9)*100+COLUMN($A:$C), 7^8):
- $A$2:$C$9<>"" controleert of de cellen in het bereik niet leeg zijn.
- COUNTIF($E$1:E1,$A$2:$C$9)=0 bepaalt of de waarden van deze cellen nog niet zijn vermeld in het celbereik van E1 tot E1.
- Als aan beide voorwaarden is voldaan (dwz de waarde is niet leeg en nog niet vermeld in kolom E), berekent de IF-functie een uniek getal op basis van de rij en kolom (ROW($2:$9)*100+COLUMN($A: $C)).
- Als niet aan de voorwaarden wordt voldaan, retourneert de functie een groot getal (7^8), dat als tijdelijke aanduiding dient.
- MIN(...): Vindt het kleinste getal dat wordt geretourneerd door de IF-functie hierboven, overeenkomend met de locatie van de volgende unieke waarde.
- TEKST(...,"R0C00"): Converteert dit minimumaantal naar een adres in R1C1-stijl. De formaatcode R0C00 geeft de conversie van het getal naar het Excel-celreferentieformaat aan.
- INDIRECTE(...): Gebruikt de INDIRECT-functie om het in de vorige stap gegenereerde adres in R1C1-stijl terug te converteren naar een normale celverwijzing in A1-stijl. De functie INDIRECT maakt celverwijzingen mogelijk op basis van de inhoud van een tekstreeks.
- &"": Het toevoegen van &"" aan het einde van de formule zorgt ervoor dat de uiteindelijke uitvoer als tekst wordt behandeld, zodat zelfs getallen als tekst worden weergegeven.
Extraheer unieke waarden uit meerdere kolommen met formule voor Excel 365
Excel 365 ondersteunt dynamische matrices, waardoor het veel eenvoudiger wordt om unieke waarden uit meerdere kolommen te extraheren:
Voer de volgende formule in of kopieer deze naar een lege cel waar u het resultaat wilt plaatsen, en klik vervolgens op Enter sleutel om alle unieke waarden in één keer te krijgen. Zie schermafbeelding:
=UNIQUE(TOCOL(A2:C9,1))
Extraheer unieke waarden uit meerdere kolommen met Kutools AI Aide
De kracht van Kutools AI-assistent om naadloos unieke waarden uit meerdere kolommen in Excel te extraheren. Met slechts een paar klikken doorzoekt deze intelligente tool uw gegevens en identificeert en vermeldt unieke vermeldingen binnen elk geselecteerd bereik. Vergeet het gedoe van complexe formules of vba-code; omarm de efficiëntie van Kutools AI-assistent en transformeer uw Excel-workflow in een productievere en foutloze ervaring.
Klik na het installeren van Kutools voor Excel op Kutools AI > AI-assistent om de te openen Kutools AI-assistent paneel:
- Typ uw vereiste in het chatvenster en klik Verzenden knop of druk op Enter toets om de vraag te verzenden;
"Extraheer unieke waarden uit het bereik A2:C9, waarbij u lege cellen negeert, en plaats de resultaten vanaf E2:" - Klik na het analyseren Uitvoeren knop om te rennen. Kutools AI Aide verwerkt uw verzoek met behulp van AI en retourneert de resultaten rechtstreeks in Excel in de opgegeven cel.
Extraheer unieke waarden uit meerdere kolommen met draaitabel
Als u bekend bent met de draaitabel, kunt u de unieke waarden eenvoudig uit meerdere kolommen extraheren met de volgende stappen:
1. Voeg eerst een nieuwe lege kolom in links van uw gegevens, in dit voorbeeld zal ik kolom A invoegen naast de originele gegevens.
2. Klik op een cel in uw gegevens en druk op Alt + D toetsen en druk vervolgens op P toets onmiddellijk om het te openen Draaitabel en draaigrafiekwizard, kiezen Meerdere consolidatiebereiken zie screenshot in de wizard step1:
3. Dan klikken Volgende knop, controleer Maak een veld met één pagina voor mij optie in wizard step2, zie screenshot:
4. Blijf klikken Volgende knop, klik om het gegevensbereik te selecteren dat de linker nieuwe kolom met cellen omvat, en klik vervolgens op Toevoegen knop om het gegevensbereik toe te voegen aan het Alle reeksen keuzelijst, zie screenshot:
5. Na het selecteren van het gegevensbereik, gaat u verder met klikken Volgende, kies in stap 3 van de wizard waar u het draaitabelrapport wilt plaatsen.
6. Eindelijk, klik Finish om de wizard te voltooien en er is een draaitabel gemaakt in het huidige werkblad en schakel vervolgens alle velden uit Kies velden om toe te voegen aan rapport sectie, zie screenshot:
7. Controleer dan het veld Waarde of sleep de waarde naar het Rijen label, nu krijgt u de unieke waarden uit de meerdere kolommen als volgt:
Extraheer unieke waarden uit meerdere kolommen met VBA-code
Met de volgende VBA-code kunt u ook de unieke waarden uit meerdere kolommen extraheren.
1. Houd de ALT + F11 toetsen, en het opent de Microsoft Visual Basic for Applications-venster.
2. Klikken Invoegen > Moduleen plak de volgende code in het modulevenster.
VBA: extraheer unieke waarden uit meerdere kolommen
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 vervolgens op F5 om deze code uit te voeren, en er verschijnt een promptvenster om u eraan te herinneren het gegevensbereik te selecteren dat u wilt gebruiken. Zie screenshot:
4. En klik vervolgens op OK, verschijnt er een ander promptvenster om u een plaats te laten kiezen om het resultaat te plaatsen, zie screenshot:
5. Klik OK om dit dialoogvenster te sluiten, en alle unieke waarden zijn in één keer geëxtraheerd.
Meer relatieve artikelen:
- Tel het aantal unieke en onderscheidende waarden uit een lijst
- Stel dat u een lange lijst met waarden heeft met enkele dubbele items, nu wilt u het aantal unieke waarden tellen (de waarden die slechts één keer in de lijst voorkomen) of verschillende waarden (allemaal verschillende waarden in de lijst, het betekent uniek waarden + 1e dubbele waarden) in een kolom zoals het linker screenshot wordt getoond. In dit artikel zal ik het hebben over hoe ik deze taak in Excel kan aanpakken.
- Extraheer unieke waarden op basis van criteria in Excel
- Stel dat u het volgende gegevensbereik heeft waarvan u alleen de unieke namen van kolom B wilt weergeven op basis van een specifiek criterium van kolom A om het resultaat te krijgen zoals onderstaand screenshot. Hoe kunt u deze taak snel en gemakkelijk in Excel afhandelen?
- Sta alleen unieke waarden toe in Excel
- Als u alleen unieke waarden wilt behouden die in een kolom of werkblad worden ingevoerd en duplicaten wilt voorkomen, zal dit artikel enkele snelle trucs voor u introduceren om met deze taak om te gaan.
- Som unieke waarden op op basis van criteria in Excel
- Ik heb bijvoorbeeld een gegevensbereik dat de kolommen Naam en Bestelling bevat, nu om alleen unieke waarden in de kolom Bestelling op te tellen op basis van de kolom Naam, zoals in het volgende screenshot. Hoe los ik deze taak snel en gemakkelijk op in Excel?
Beste Office-productiviteitstools
Geef uw Excel-vaardigheden een boost met Kutools voor Excel en ervaar efficiëntie als nooit tevoren. Kutools voor Excel biedt meer dan 300 geavanceerde functies om de productiviteit te verhogen en tijd te besparen. Klik hier om de functie te krijgen die u het meest nodig heeft...
Office-tabblad Brengt een interface met tabbladen naar Office en maakt uw werk veel gemakkelijker
- Schakel bewerken en lezen met tabbladen in Word, Excel, PowerPoint in, Publisher, Access, Visio en Project.
- Open en maak meerdere documenten in nieuwe tabbladen van hetzelfde venster in plaats van in nieuwe vensters.
- Verhoogt uw productiviteit met 50% en vermindert honderden muisklikken voor u elke dag!