Hoe gemeenschappelijke waarden in 3 kolommen in Excel te vinden?
Het werken met gegevens in Excel omvat vaak het vergelijken van lijsten om gedeelde of gedupliceerde items te identificeren. Hoewel het vergelijken van twee kolommen om gemeenschappelijke waarden te vinden een veelvoorkomende taak is, zijn er situaties waarin je moet bepalen welke waarden tegelijkertijd in drie afzonderlijke kolommen voorkomen. Bijvoorbeeld bij het samenvoegen van enquêtegegevens, het samenvoegen van verkooprecords of het analyseren van dubbele items in meerdere lijsten, is het belangrijk om nauwkeurig de set items te extraheren die aanwezig zijn in alle drie de kolommen, zoals in onderstaande schermafbeelding wordt gedemonstreerd. Dit artikel introduceert verschillende praktische methoden om dit probleem in Excel op te lossen, zodat je efficiënt en betrouwbaar de gemeenschappelijke waarden tussen drie kolommen kunt identificeren – of je nu formules of VBA prefereert.
Zoek gemeenschappelijke waarden in 3 kolommen met arrayformules
VBA-macro om waarden te extraheren die in alle drie de kolommen aanwezig zijn
Zoek gemeenschappelijke waarden in 3 kolommen met arrayformules
Om gemeenschappelijke waarden in drie kolommen te vinden en extraheren, kun je arrayformules gebruiken die zijn ontworpen om items te zoeken die in alle geselecteerde bereiken voorkomen. Dit is vooral handig in datasets waarin je geen gebruik wilt maken van extra Excel-invoegtoepassingen of externe tools.
Voer deze arrayformule in een lege cel in waar je de eerste gemeenschappelijke waarde wilt weergeven:
=LOOKUP("zzz",CHOOSE({1,2},"",INDEX(A$2:A$10,MATCH(0,COUNTIF(E$1:E1,A$2:A$10)+IF(IF(COUNTIF(B$2:B$8,A$2:A$10)>0,1,0)+IF(COUNTIF(C$2:C$9,A$2:A$10)>0,1,0)=2,0,1),0))))Hoe gebruik je deze arrayformule:
- Nadat je de formule hebt ingevoerd in je geselecteerde cel, druk je op Shift + Ctrl + Enter (niet alleen Enter). Excel zal de formule tussen accolades plaatsen om aan te geven dat het een arrayformule is.
- Sleep de formule naar beneden in de kolom totdat lege cellen verschijnen. Dit zal alle waarden weergeven die door de drie kolommen worden gedeeld, en lege cellen zullen aangeven dat er geen verdere overeenkomsten bestaan.
Opmerkingen en uitleg van parameters:
- Als je een andere arrayformule prefereert, retourneert deze ook alle unieke waarden die in alle drie de kolommen voorkomen:
Vergeet niet opnieuw te drukken op Shift + Ctrl + Enter nadat je de formule hebt getypt of geplakt.=INDEX($A$2:$A$10, MATCH(0, COUNTIF($E$1:E1, $A$2:$A$10)+IF(IF(COUNTIF($B$2:$B$8, $A$2:$A$10)>0,1,0)+IF(COUNTIF($C$2:$C$9, $A$2:$A$10)>0,1,0)=2,0,1),0)) - In deze formules:
- A2:A10, B2:B8, C2:C9 zijn de gegevensbereiken in elk van de drie kolommen die je wilt vergelijken.
- E1 verwijst naar de cel direct boven waar je formule begint (voor uitsluitingslogica). Pas celverwijzingen aan om te matchen met je daadwerkelijke gegevensbereiken en de locatie waar je resultaten wilt laten verschijnen.
- Deze methoden werken goed voor gemiddelde gegevenssets, maar kunnen traag worden bij zeer grote volumes vanwege de rekenkracht die arrayformules vereisen.
- Wees voorzichtig om bronbereiken niet halverwege aan te passen, omdat dit kan leiden tot onnauwkeurige resultaten of formulefouten.
- Als het resultaat lege rijen bevat, betekent dit dat alle gemeenschappelijke waarden zijn geëxtraheerd en de resterende cellen geen verdere doorsnijdingen hebben.
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.
VBA-macro om waarden te extraheren die in alle drie de kolommen aanwezig zijn
Als je een geautomatiseerde aanpak prefereert die geen invoeren of kopiëren van complexe formules vereist, kun je Excel VBA gebruiken om door je gegevens te lussen en alleen die waarden uit te voeren die in elk van de drie kolommen aanwezig zijn. Deze methode is vooral handig voor zeer grote gegevenssets of bij het werken met dynamische bereiken, omdat VBA efficiënter is in het afhandelen van herhalende taken en aangepaste criteria.
1. Klik op Ontwikkelaar > Visual Basic om de VBA-editor te openen (als het tabblad Ontwikkelaar niet zichtbaar is, kun je het inschakelen via Bestand > Opties > Werkbalk aanpassen).
2. Klik in de VBA-editor op Invoegen > Module om een nieuwe module te maken. Plak vervolgens de onderstaande code in het modulevenster:
Sub FindCommonValuesThreeColumns()
Dim dict1 As Object
Dim dict2 As Object
Dim dict3 As Object
Dim resultDict As Object
Dim rngA As Range
Dim rngB As Range
Dim rngC As Range
Dim cell As Range
Dim outputRow As Long
Dim key As Variant
On Error Resume Next
Set dict1 = CreateObject("Scripting.Dictionary")
Set dict2 = CreateObject("Scripting.Dictionary")
Set dict3 = CreateObject("Scripting.Dictionary")
Set resultDict = CreateObject("Scripting.Dictionary")
' Prompt the user to select the three column ranges
Set rngA = Application.InputBox("Select the first column range", "KutoolsforExcel", Selection.Address, Type:=8)
Set rngB = Application.InputBox("Select the second column range", "KutoolsforExcel", Selection.Address, Type:=8)
Set rngC = Application.InputBox("Select the third column range", "KutoolsforExcel", Selection.Address, Type:=8)
' Store all unique values from each column into corresponding dictionaries
For Each cell In rngA
If Not dict1.exists(cell.Value) And cell.Value <> "" Then
dict1.Add cell.Value, 1
End If
Next
For Each cell In rngB
If Not dict2.exists(cell.Value) And cell.Value <> "" Then
dict2.Add cell.Value, 1
End If
Next
For Each cell In rngC
If Not dict3.exists(cell.Value) And cell.Value <> "" Then
dict3.Add cell.Value, 1
End If
Next
' Check which values exist in all three dictionaries
For Each key In dict1.keys
If dict2.exists(key) And dict3.exists(key) Then
resultDict.Add key, 1
End If
Next
' Output result to next empty column on the active sheet
outputRow = 1
For Each key In resultDict.keys
Cells(outputRow, Columns.Count).End(xlToLeft).Offset(0, 1).Value = key
outputRow = outputRow + 1
Next
MsgBox "Common values extracted next to your data.", vbInformation, "KutoolsforExcel"
End Sub3. Druk in het VBA-venster, met de module geselecteerd, op F5 of klik op de knop Uitvoeren (▶) om de code uit te voeren. Je wordt achtereenvolgens gevraagd om elk van de drie kolombereiken die je wilt vergelijken te selecteren. Gebruik je muis om de juiste cellen te markeren tijdens elke prompt.
4. De macro zal je selecties verwerken en alle waarden die in alle drie de kolommen aanwezig zijn, uitvoeren naar de eerstvolgende lege kolom rechts van je huidige gegevensset, beginnend bij de eerste rij.
Deze methode is efficiënt bij het werken met grote of dynamische gegevenssets en kan eenvoudig worden uitgebreid naar vier of meer kolommen door de woordenboeklogica te dupliceren. Vergeet niet altijd je werkmap op te slaan voordat je macros uitvoert, omdat niet-opgeslagen wijzigingen niet ongedaan kunnen worden gemaakt als je wilt terugkeren.
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