Hoe vindt u waarden die in alle drie de kolommen voorkomen in Excel?
Het werken met gegevens in Excel houdt vaak in dat u lijsten met elkaar vergelijkt om gemeenschappelijke of dubbele items te identificeren. Hoewel het vergelijken van twee kolommen om gemeenschappelijke waarden te vinden een veelvoorkomende taak is, zijn er situaties waarin u moet bepalen welke waarden tegelijkertijd in drie afzonderlijke kolommen voorkomen. Denk bijvoorbeeld aan het consolideren van enquêtedata, het samenvoegen van verkoopgegevens of het analyseren van dubbele items in meerdere lijsten. Het is dan belangrijk om nauwkeurig de verzameling items te extraheren die in alle drie kolommen voorkomen, zoals in de onderstaande schermafbeelding wordt gedemonstreerd. In dit artikel worden verschillende praktische methoden beschreven om dit probleem in Excel op te lossen, zodat u efficiënt en betrouwbaar de gemeenschappelijke waarden in drie kolommen kunt achterhalen—of u nu formules of VBA prefereert.
Gemeenschappelijke waarden in 3 kolommen vinden met matrixformules
VBA-macro om waarden te extraheren die in alle drie kolommen voorkomen
Gemeenschappelijke waarden in 3 kolommen vinden met matrixformules
Om gemeenschappelijke waarden in drie kolommen te vinden en te extraheren, kunt u matrixformules gebruiken die speciaal zijn ontworpen om items te identificeren die in alle opgegeven bereiken voorkomen. Dit is vooral handig bij datasets waarbij u liever geen extra Excel-invoegtoepassingen of externe hulpmiddelen gebruikt.
Voer deze matrixformule in een lege cel in waar u 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))))Zo gebruikt u deze matrixformule:
- Nadat u de formule in de geselecteerde cel hebt ingevoerd, drukt u op Shift + Ctrl + Enter (niet alleen op Enter). Excel omsluit de formule dan met accolades om aan te geven dat het een matrixformule is.
- Sleep de formule omlaag in de kolom totdat er lege cellen verschijnen. Zo worden alle waarden weergegeven die in de drie kolommen voorkomen; lege cellen geven aan dat er geen verdere overeenkomsten zijn.
Opmerkingen en uitleg van parameters:
- Als u liever een andere matrixformule gebruikt, geeft deze ook alle unieke waarden terug die in alle drie kolommen voorkomen:
Vergeet opnieuw niet om op Shift + Ctrl + Enter te drukken nadat u 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 en C2:C9 zijn de bereiken in elk van de drie kolommen die u wilt vergelijken.
- E1 verwijst naar de cel direct boven de plek waar uw formule begint (voor uitsluitingslogica). Pas de celverwijzingen aan zodat ze overeenkomen met uw werkelijke bereik en de locatie waar u de resultaten wilt weergeven.
- Deze methoden werken uitstekend voor gematigde datasets, maar kunnen bij zeer grote hoeveelheden gegevens traag worden vanwege de hoge rekenbelasting van matrixformules.
- Pas Bronbereik niet halverwege aan, want dat kan leiden tot onnauwkeurige resultaten of formulefouten.
- Als het resultaat lege rijen bevat, betekent dit dat alle gemeenschappelijke waarden zijn geëxtraheerd en dat de resterende cellen geen verdere overlappende waarden meer bevatten.
Ontgrendel de magie van Excel met KUTOOLS AI
- Slimme uitvoering: Voer celbewerkingen uit, analyseer gegevens en maak grafieken — allemaal met eenvoudige opdrachten.
- aangepaste formules: Genereer op maat gemaakte formules om uw workflows te stroomlijnen.
- VBA-programmeren: Schrijf en implementeer VBA-code moeiteloos.
- Formule-uitleg: Begrijp complexe formules moeiteloos.
- Tekstvertaling: Doorbreek taalbarrières in uw spreadsheets.
VBA-macro om waarden te extraheren die in alle drie kolommen voorkomen
Als u liever een geautomatiseerde aanpak gebruikt die geen handmatige invoer of het kopiëren van complexe formules vereist, kunt u Excel VBA inzetten om uw gegevens te doorlopen en alleen de waarden te extraheren die in elk van de drie kolommen voorkomen. Deze methode is bijzonder geschikt voor zeer grote datasets of bij het werken met dynamische bereiken, omdat VBA herhalende taken en aangepaste criteria efficiënter verwerkt.
1. Klik op Ontwikkelaar > Visual Basicom de VBA-editor te openen (als het tabblad)Ontwikkelaar niet zichtbaar is, kunt u dit inschakelen via Bestand > Opties > Het lint 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. U wordt achtereenvolgens gevraagd elk van de drie kolombereiken te selecteren die u wilt vergelijken. Gebruik uw muis om tijdens elke prompt de juiste cellen te markeren.
4. De macro verwerkt uw selecties en toont alle waarden die in alle drie kolommen voorkomen, in de eerstvolgende lege kolom rechts van uw huidige dataset, te beginnen bij de eerste rij.
Deze methode is uiterst efficiënt bij het verwerken van grote of dynamische datasets en laat zich eenvoudig uitbreiden naar vier of meer kolommen door de woordenboeklogica te dupliceren. Sla uw werkmap altijd op voordat u macro’s uitvoert, aangezien niet-opgeslagen wijzigingen niet ongedaan kunnen worden gemaakt wanneer u terug wilt keren.
Beste Office-productiviteitshulpmiddelen
Geef uw Excel-vaardigheden een boost met Kutools voor Excel en ervaar efficiëntie zoals nooit tevoren.Kutools voor Excel biedt meer dan 300 geavanceerde functies om de productiviteit te verhogen en Tijd besparen.Klik hier om de functie te krijgen die u het meest nodig heeft...
Office Tab brengt een tabbladinterface naar Office en maakt uw werk veel eenvoudiger
- Schakel tabbladbewerking en -lezen in voor Word, Excel, PowerPoint, Publisher, Access, Visio en Project.
- Open en maak meerdere documenten aan in nieuwe tabbladen binnen hetzelfde venster, in plaats van in afzonderlijke vensters.
- Verhoogt uw productiviteit met 50 % en bespaart u dagelijks honderden muisklikken!
Alle Kutools-add-ins in één installatieprogramma.
Kutools for Office bundelt add-ins voor Excel, Word, Outlook en PowerPoint, plus Office Tab Pro—ideaal voor teams die met meerdere Office-apps werken.
- Alles-in-één suite— add-ins voor Excel, Word, Outlook & PowerPoint plus Office Tab Pro
- Één installatieprogramma, één licentie— binnen enkele minuten klaar (MSI-geschikt)
- Werkt beter samen— gestroomlijnde productiviteit in alle Office-apps
- 30 dagen volledig functionele proefversie— geen registratie, geen creditcard
- Beste prijs-kwaliteitverhouding— bespaar ten opzichte van het afzonderlijk kopen van add-ins