Note: The other languages of the website are Google-translated. Back to English

Hoe dynamisch een lijst met unieke waarden extraheren uit een kolombereik in Excel?

Voor een kolombereik waarvan de waarden regelmatig veranderen, moet u altijd alle unieke waarden uit het bereik halen, ongeacht hoe deze is gewijzigd. Hoe maak je een dynamische lijst met unieke waarden? In dit artikel wordt uitgelegd hoe u hiermee om kunt gaan.

Extraheer dynamisch een lijst met unieke waarden uit een kolombereik met formule
Extraheer dynamisch een lijst met unieke waarden uit een kolombereik met VBA-code


Extraheer dynamisch een lijst met unieke waarden uit een kolombereik met formule

Zoals onderstaand screenshot laat zien, moet u dynamisch een lijst met unieke waarden extraheren uit bereik B2: B9. Probeer de volgende matrixformule.

1. Selecteer een lege cel zoals D2, voer de onderstaande formule erin in en druk op Ctrl + Shift + Enter toetsen tegelijk. (B2: B9 zijn de kolomgegevens waarvan u de unieke waarden wilt extraheren, D1 is de cel hierboven waarin uw formule zich bevindt)

=IFERROR(INDEX($B$2:$B$9, MATCH(0,COUNTIF($D$1:D1, $B$2:$B$9), 0)),"")

2. Blijf cel D2 selecteren en sleep vervolgens de vulhendel omlaag om alle unieke waarden uit het opgegeven bereik op te halen.

Nu worden alle unieke waarden in kolombereik B2: B9 geëxtraheerd. Wanneer waarden in dit bereik worden gewijzigd, wordt de lijst met unieke waarden onmiddellijk dynamisch gewijzigd.

Selecteer en markeer eenvoudig alle unieke waarden in een bereik in Excel:

Het Selecteer Dubbele en unieke cellen nut van Kutools for Excel kan u helpen bij het eenvoudig selecteren en markeren van alle unieke waarden (inclusief de eerste duplicaten) of de unieke waarden die slechts één keer voorkomen, evenals dubbele waarden die u nodig heeft, zoals onderstaand screenshot.
Download nu Kutools voor Excel! (Gratis parcours van 30 dagen)


Extraheer dynamisch een lijst met unieke waarden uit een kolombereik met VBA-code

U kunt ook een lijst met unieke waarden dynamisch extraheren uit een kolombereik met de volgende VBA-code.

1. druk op anders + F11 toetsen tegelijkertijd om het Microsoft Visual Basic voor toepassingen venster.

2. In de Microsoft Visual Basic voor toepassingen venster klikt Invoegen > Module. Kopieer en plak vervolgens de onderstaande VBA-code in het Module venster.

VBA-code: extraheer een lijst met unieke waarden uit een bereik

Sub CreateUniqueList()
Dim xRng As Range
Dim xLastRow As Long
Dim xLastRow2 As Long
Dim I As Integer
On Error Resume Next
Set xRng = Application.InputBox("Please select range:", "Kutools for Excel", Selection.Address, , , , , 8)
If xRng Is Nothing Then Exit Sub
On Error Resume Next
xRng.Copy Range("D2")
xLastRow = xRng.Rows.Count + 1
ActiveSheet.Range("D2:D" & xLastRow).RemoveDuplicates Columns:=1, Header:=xlNo
xLastRow2 = Cells(Rows.Count, "B").End(xlUp).Row
For I = 1 To xLastRow2
  If ActiveSheet.Range("D2:D" & xLastRow2).Cells(I).Value = "" Then
     ActiveSheet.Range("D2:D" & xLastRow2).Cells(I).Delete
  End If
Next
End Sub

Opmerking:: In de code is D2 de cel waarin u de lijst met unieke waarden zult vinden. U kunt het naar behoefte wijzigen.

3. Ga terug naar het werkblad en klik op Invoegen > Vormen > Rechthoek. Zie screenshot:

4. Teken een rechthoek in uw werkblad en typ vervolgens enkele woorden die u erop wilt weergeven. Klik er vervolgens met de rechtermuisknop op en selecteer Wijs macro toe vanuit het rechtsklikmenu. In de Wijs macro toe dialoogvenster, selecteert u het Unieke lijst makenL functie in het Macronaam vak en klik vervolgens op het OK knop. Zie screenshot:

5. Klik nu op de rechthoekknop, een Kutools for Excel dialoogvenster verschijnt, selecteer het bereik dat de unieke waarden bevat die u wilt extraheren en klik vervolgens op het OK knop.

Vanaf nu kunt u de bovenstaande stap 5 herhalen om de unieke waardenlijst automatisch bij te werken.


Gerelateerde artikelen:


De beste tools voor kantoorproductiviteit

Kutools voor Excel lost de meeste van uw problemen op en verhoogt uw productiviteit met 80%

  • visfuik: Snel invoegen complexe formules, grafieken en alles wat je eerder hebt gebruikt; Versleutel cellen met wachtwoord; Maak een mailinglijst en stuur e-mails ...
  • Super Formula-balk (bewerk eenvoudig meerdere regels tekst en formule); Lay-out lezen (gemakkelijk grote aantallen cellen lezen en bewerken); Plakken in gefilterd bereik...
  • Voeg cellen / rijen / kolommen samen zonder gegevens te verliezen; Gespleten cellen inhoud; Combineer dubbele rijen / kolommen... Voorkom dubbele cellen; Vergelijk Ranges...
  • Selecteer Dupliceren of Uniek Rijen; Selecteer lege rijen (alle cellen zijn leeg); Super zoeken en fuzzy zoeken in veel werkboeken; Willekeurige selectie ...
  • Exacte kopie Meerdere cellen zonder de formuleverwijzing te wijzigen; Maak automatisch verwijzingen naar meerdere bladen; Plaats kogels, Selectievakjes en meer ...
  • Extraheer tekst, Tekst toevoegen, Verwijderen op positie, Ruimte verwijderen; Paging-subtotalen maken en afdrukken; Converteren tussen celinhoud en opmerkingen...
  • Super filter (bewaar en pas filterschema's toe op andere bladen); Geavanceerd sorteren per maand / week / dag, frequentie en meer; Speciaal filter door vet, cursief ...
  • Combineer werkmappen en werkbladen; Tabellen samenvoegen op basis van sleutelkolommen; Gegevens splitsen in meerdere bladen; Batch Converteer xls, xlsx en PDF...
  • Meer dan 300 krachtige functies. Ondersteunt Office / Excel 2007-2019 en 365. Ondersteunt alle talen. Eenvoudig te implementeren in uw onderneming of organisatie. Gratis proefperiode van 30 dagen met volledige functies. 60 dagen geld-terug-garantie.
kte tabblad 201905

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 elke dag honderden muisklikken!
officetab onderkant
Comments (32)
Nog geen beoordelingen. Beoordeel als eerste!
Deze opmerking is gemaakt door de moderator op de site
Bedankt voor de bijles. Hoe zou u met behulp van de formulemethode de formule wijzigen als u een categoriekwalificatie zou willen toevoegen? Zeg in kolom C dat je onderscheid maakt of het item een ​​groente of een fruit is. Hoe zou je de code veranderen om alleen de unieke vruchten te sorteren en de groenten uit te sluiten? Ik heb geprobeerd AANTAL.ALS te vervangen door AANTAL.ALS, gebruikmakend van de tweede countifs-criteria van (LIJSTBEREIK, "CATEGORIE"), maar het geeft blanco terug. Moet ik mijn array uitbreiden en VERT.ZOEKEN opnemen?
Eric
Deze opmerking is gemaakt door de moderator op de site
Ik ben fatsoenlijk in Excel, maar ik probeer echt te begrijpen hoe en waarom de bovenstaande formule werkt (het werkt voor waar ik het voor gebruik, maar ik moet begrijpen waarom). Ik raak soms een beetje in de war met het gebruik van arrays, dus elke uitleg in idiote termen zou buitengewoon nuttig zijn Met vriendelijke groeten
PJ
Deze opmerking is gemaakt door de moderator op de site
Deze formule is verouderd en werkt niet. Ik heb letterlijk dit exacte Excel-blad ingesteld om te zien of ik deze formule kon laten werken en dat doet het niet.
Achterhaald
Deze opmerking is gemaakt door de moderator op de site
Hey Dude,
Welke Office-versie gebruik je?
crystal
Deze opmerking is gemaakt door de moderator op de site
{=INDEX($Q$3:$Q$263,MATCH(0,COUNTIF(V$2:V2,$Q$3:$Q$263),0))} - vond dit werken vanaf een andere site...

Gebruik Ctrl+Shift+Enter om de matrixfunctie (accolades) te krijgen. Sleep de formules en plak ze totdat de #NA wordt weergegeven. Mijn dataset stond in Kolom-Q, het werd vergeleken om te zien of het bestond in de unieke lijst in Kolom-V, die zich voortdurend langs dezelfde kolom uitstrekt.
Viswanath Urala
Deze opmerking is gemaakt door de moderator op de site
Goede dag.
Maak een lijst van alle unieke waarden van kolom Q met de abobv-formule, en gebruik dan zijn formule =IF(D2=V1,"Match","No match") om te vergelijken of de uniques in cilumn Q vergeleken worden met kolom V in dezelfde rij .
crystal
Deze opmerking is gemaakt door de moderator op de site
Hallo, en bedankt voor je hulp.

Ik heb precies deze functionaliteit nodig, maar mijn lijst met "unieke waarden" moet zich over kolommen uitstrekken in plaats van rijen, dus de uitvouwbare lijst langs de rijen werkt niet voor mij.

Hoe kan ik deze formule wijzigen zodat de lijst met "unieke waarden" groter wordt terwijl ik deze over de kolommen sleep?

Offset()?
Transponeren()?
Indirect() met een reeks absolute verwijzingen aaneengeschakeld met een verwijzing naar de kolom in plaats van naar de rij?


Nogmaals bedankt!
Ryan
Deze opmerking is gemaakt door de moderator op de site
Beste Ryan,
Deze formule =IFERROR(INDEX($B$2:$B$9, MATCH(0,COUNTIF($D$2:D2, $B$2:$B$9), 0)),"") + Ctrl + Shift + Enter kan u helpen om het probleem op te lossen.
Zie onderstaande schermafbeelding:
crystal
Deze opmerking is gemaakt door de moderator op de site
Ook, om welke reden dan ook, de oorspronkelijke formule voorzag:
=IFERROR(INDEX($B$2:$B$9, MATCH(0,COUNTIF($D$1:D1, $B$2:$B$9), 0)),"")

retourneert een "circulaire referentie"-waarschuwing en zal niet berekenen..
Ryan
Deze opmerking is gemaakt door de moderator op de site
Beste Ryan,
Welke Office-versie gebruik je? De formule werkt goed in mijn Office 2016 en 2013.
crystal
Deze opmerking is gemaakt door de moderator op de site
Ik heb dit eerder meegemaakt - mijn oplossing was dat ik de formule in de cel D1 invoerde (equivalent in het werkblad dat ik gebruikte). Welke cel de $D:$1 ook is, u moet deze in de onderstaande cel invoeren - D2. Excuses als dat niet de reden is waarom je de fout hebt gekregen
Andrew
Deze opmerking is gemaakt door de moderator op de site
Tips om de VBA-optie te laten werken met Excel 2016 voor macOS? Ik heb de stappen gevolgd; wanneer ik de macro echter uitvoer, gebeurt er helemaal niets. Bedankt!
Jones
Deze opmerking is gemaakt door de moderator op de site
Daer Jones,
Probeer de onderstaande VBA-code en laat me weten of deze voor u werkt. Dank u!

Sub CreëerUniekeLijst()
Dim xRng als bereik
Dim xLaatste Rij Zo Lang
Dim xLastRow2 zo lang
Dim ik als geheel getal
' Bij fout Hervatten volgende
Set xRng = Application.InputBox ("Selecteer bereik:", "Kutools for Excel", Selection.Address, , , , , 8)
Als xRng niets is, sluit dan Sub af
On Error Resume Next
xRng.Kopieerbereik ("D2")
xLastRow = xRng.Rows.Count + 1
ActiveSheet.Range("D2:D" & xLastRow).RemoveDuplicates Columns:=1, Header:=xlNo
xLastRow2 = Cellen(Rijen.Aantal, "B").Einde(xlOmhoog).Rij
Voor I = 1 Tot xLaatsteRij2
If ActiveSheet.Range("D2:D" & xLastRow2).Cells(I).Value = "" Dan
ActiveSheet.Range("D2:D" & xLastRow2).Cellen(I).Verwijderen
End If
volgende
End Sub
crystal
Deze opmerking is gemaakt door de moderator op de site
Hallo Kristal,
Ik probeer de VB-versie van de unieke waardenlijst te gebruiken en loop tegen een probleem aan.
Het bereik waarvan ik een kolom met unieke waarden wil maken, zijn alle formules die naar verschillende tabbladen verwijzen.
Hoe krijg je de waarde om over te dragen in plaats van de formule?
Mike
Deze opmerking is gemaakt door de moderator op de site
Beste Mike,
Converteer uw formuleverwijzingen naar absoluut en pas vervolgens het VB-script toe.
crystal
Deze opmerking is gemaakt door de moderator op de site
Ik heb hetzelfde probleem, behalve dat mijn formule verwijst naar kolomnamen en niet kan converteren naar absoluut.
Hoe verander ik de vba om de waarden te plakken en niet de formule?
Odette Meintjes
Deze opmerking is gemaakt door de moderator op de site
Hoe zou u meerdere criteria toevoegen, bijvoorbeeld als u alleen aan de dynamische lijst zou willen toevoegen als de datum slechts 9/12 was?

Ik probeer "&" in de MATCH-formule, maar het werkt niet.

Bijvoorbeeld op basis van uw voorbeeld:
=IFERROR(INDEX($B$2:$B$9, MATCH(0 & B4,COUNTIF($D$1:D1, $B$2:$B$9) & $A$2:$A$9, 0)),"" )
Dit genereert een fout of creëert duplicaten.

Als alternatief heb ik gelezen dat "+" zou kunnen werken, hoewel ik het niet werkend krijg. Of het gebruik van KLEIN.

Ideeën?
Zac
Deze opmerking is gemaakt door de moderator op de site
Beste Zac,
Sorry dat ik je hier niet mee kan helpen, je kunt je vraag stellen op ons forum: https://www.extendoffice.com/forum.html om meer Excel-ondersteuning te krijgen van onze professional.
crystal
Deze opmerking is gemaakt door de moderator op de site
Hoe zou je een tweede variabele toevoegen? Ik wil bijvoorbeeld alle unieke elementen in één kolom die ook een vergelijkbare waarde in een andere kolom delen. Stel je in je voorbeeld een 3e kolom voor met de titel "Afdeling" met waarden als product, vlees, enz. Ik realiseer me dat dit allemaal Produce zijn, maar hopelijk begrijp je mijn punt. Zou u de CountIF-formule wijzigen in een COUNTIFS of op een andere manier wijzigen?
Mat
Deze opmerking is gemaakt door de moderator op de site
hoi Matt
Probeer deze formule eens =IF(ISNA(VLOOKUP(A2,$C$2:$C$13,1,FALSE)),"Yes","").
Stel dat de twee vergeleken lijsten kolom A en kolom C zijn, als de unieke waarden alleen in kolom A maar niet in kolom C blijven, wordt Ja weergegeven in kolom B; terwijl als niets in kolom B wordt geretourneerd, dit betekent dat de overeenkomstige waarde in zowel kolom A als kolom C blijft.
crystal
Deze opmerking is gemaakt door de moderator op de site
Bedankt voor het antwoord .. maar het is goed om die unieke waarde eruit te halen als deze JA weergeeft .. zou je me alsjeblieft de formule kunnen adviseren om de unieke waarde in een andere kolom te halen.
jyotiba mali
Deze opmerking is gemaakt door de moderator op de site
Als ik dit doe voor een Excel-blad met duizend rijen in de nieuwste versie van Excel op een Mac, komt het nooit meer terug. De eerste rij werkt, maar als ik naar beneden dupliceer, gaat Excel in een rekenmodus die nu al meer dan twee uur geen waarden heeft geretourneerd.

Enig idee hoe dit te doen voor grote lijsten (tot 2k rijen) die 50 of 60 unieke waarden zullen retourneren?

Ik bespotte dit in de "Numbers"-app, en het werkt daar perfect, het kost maar een paar minuten om te berekenen. Het duurt gewoon zo lang in Excel dat ik me afvraag of het ooit zal worden voltooid. Ik ben van plan om het 's nachts te laten "lopen" om te zien wat er zal gebeuren.
Steve Parker
Deze opmerking is gemaakt door de moderator op de site
Controleer uw Rekenopties. Het moet op automatisch worden ingesteld. Bestand > Opties > Formules > Rekenopties > Werkmapberekening (Automatische selectie)
Josh
Deze opmerking is gemaakt door de moderator op de site
Ik probeer de formule voorbij mijn werkelijke gegevens te slepen, zodat ik gegevenssets van verschillende grootte kan invoeren en niets hoef aan te passen. De laatste rij nadat mijn werkelijke gegevens zijn beëindigd, retourneert echter altijd een "0". Ik gebruik de unieke waarden voor iets anders in een aangrenzende kolom, en de 0 zorgt ervoor dat de laatste waarde wordt herhaald (wanneer ik de 0 verwijder, wordt de waarde niet langer herhaald). Enig idee hoe dit op te lossen? Ik gebruik ook Office 365 Business
Charley
Deze opmerking is gemaakt door de moderator op de site
Hallo, bedankt voor je hulp.
Hoe kan ik mijn waarden nu ook alfabetisch laten sorteren? (Ik wil het filter op mijn mastertabel niet gebruiken)
Moet ik een AANTAL.ALS gebruiken in plaats van AANTAL.ALS?
Help alstublieft
Alexis
Deze opmerking is gemaakt door de moderator op de site
Hallo Alexis,
Sorry kan de geëxtraheerde waarde niet tegelijkertijd alfabetisch sorteren met de formule. Bedankt voor je reactie.
crystal
Deze opmerking is gemaakt door de moderator op de site
Ik gebruik die =IFERROR(INDEX($B$2:$B$9, MATCH(0,COUNTIF($D$1:D1, $B$2:$B$9), 0)),"") formule die geweldig is voor één kolom, maar mijn gegevens zijn verspreid over een reeks kolommen en rijen. Kan ik de formule bewerken om het hele gebied op te nemen? Mijn gegevens lopen van AC4 tot AR60...
Cameron
Deze opmerking is gemaakt door de moderator op de site
Ik probeer de VBA-code en de formule. De code VBA werkt heel goed, maar ik kan geen bestand met macro behouden. Maar het probleem is dat ik de formule niet kan laten werken. Had iemand een idee? Dank u
Charlotte
Deze opmerking is gemaakt door de moderator op de site
Hallo Charlotte,
Bedankt voor je reactie. U kunt het bestand met macro bewaren voor toekomstig gebruik door de werkmap op te slaan als een Excel-werkmap met macro's.
Zou je voor het formuleprobleem een ​​screenshot van je gegevens willen geven? Bedankt voor je reactie.
crystal
Deze opmerking is gemaakt door de moderator op de site
Veel dank
A
Deze opmerking is gemaakt door de moderator op de site
hoe de vba-code te laten werken voor een bereik waar een andere formule is gebruikt? in kolom BI heb je een formule, verwijzend naar kolommen D en E.
Als ik de code toepas op kolom L (laten we zeggen), (uiteraard door de cellen in de code op de juiste manier aan te passen), retourneert de macro de formule die is toegepast op de kolommen M en N ... Het werkt dan, maar niet zoals ik wil! Hoe de waarden in kolom B te houden? bedankt
Francesco
Deze opmerking is gemaakt door de moderator op de site
Ik heb toegevoegd dat de 'formule'-methode erg traag werkt bij grote datasets. Een goed alternatief is het gebruik van een draaitabel. Kies dan alleen de rijlabels, je krijgt een lijstje met bijzondere waarden. Het kan zijn dat je wat extra's krijgt "(leeg)" bijvoorbeeld. Je kunt deze er vervolgens uitfilteren. Helaas kun je maar op 1 criterium filteren. Ook daar zijn wel weer oplossingen voor, maar dat is wat complexer.
Richard
Er zijn nog geen reacties geplaatst
Laat uw commentaar
Posten als gast
×
Beoordeel dit bericht:
0  Personages
Voorgestelde locaties