Wie findet man gemeinsame Werte in drei Spalten in Excel?
Die Arbeit mit Daten in Excel umfasst häufig den Vergleich von Listen, um gemeinsame oder doppelte Einträge zu identifizieren. Während der Abgleich zweier Spalten zur Ermittlung gemeinsamer Werte eine alltägliche Aufgabe ist, gibt es Fälle, in denen Sie herausfinden müssen, welche Werte gleichzeitig in drei separaten Spalten vorkommen. Beispielsweise beim Zusammenführen von Umfragedaten, Verkaufsunterlagen oder bei der Analyse doppelter Einträge über mehrere Listen hinweg ist es entscheidend, präzise diejenigen Elemente zu extrahieren, die in allen drei Spalten enthalten sind – wie in der folgenden Abbildung veranschaulicht. Dieser Artikel stellt mehrere praktische Methoden vor, um dieses Problem in Excel effizient zu lösen und zuverlässig die gemeinsamen Werte aus drei Spalten zu ermitteln – ganz gleich, ob Sie Formeln oder VBA bevorzugen.
Gemeinsame Werte in 3 Spalten mithilfe von Matrixformeln finden
VBA-Makro zum Extrahieren von Werten, die in allen drei Spalten enthalten sind
Gemeinsame Werte in 3 Spalten mithilfe von Matrixformeln finden
Um gemeinsame Werte aus drei Spalten zu finden und zu extrahieren, können Sie Matrixformeln nutzen, die speziell dafür entwickelt wurden, Elemente zu identifizieren, die in allen ausgewählten Bereichen vorkommen. Das ist besonders praktisch für Datensätze, bei denen Sie auf zusätzliche Excel-Add-Ins oder externe Tools verzichten möchten.
Geben Sie diese Matrixformel in eine leere Zelle ein, in der der erste gemeinsame Wert angezeigt werden soll:
=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))))So verwenden Sie diese Matrixformel:
- Nachdem Sie die Formel in die ausgewählte Zelle eingegeben haben, drücken Sie Umschalt + Strg + Enter (nicht nur Enter). Excel umschließt die Formel automatisch mit geschweiften Klammern, um anzuzeigen, dass es sich um eine Matrixformel handelt.
- Ziehen Sie die Formel die Spalte hinunter, bis leere Zellen erscheinen. So werden alle Werte aufgelistet, die in den drei Spalten gemeinsam vorkommen – leere Zellen zeigen an, dass keine weiteren Übereinstimmungen vorhanden sind.
Hinweise und Parametererklärungen:
- Falls Sie eine alternative Matrixformel bevorzugen, liefert auch diese alle eindeutigen Werte zurück, die in allen drei Spalten enthalten sind:
Denken Sie auch hier daran, nach der Eingabe oder dem Einfügen der Formel Umschalt + Strg + Enter zu drücken.=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 diesen Formeln:
- A2:A10, B2:B8 und C2:C9 sind die Bereiche in jeder der drei Spalten, die Sie vergleichen möchten.
- E1 bezieht sich auf die Zelle direkt oberhalb der Stelle, an der Ihre Formel beginnt (für die Ausschlusslogik). Passen Sie die Zellbezüge entsprechend Ihrem tatsächlichen Bereich und dem gewünschten Ergebnisort an.
- Diese Methoden eignen sich hervorragend für mittelgroße Datensätze, können bei sehr großen Datenmengen jedoch aufgrund des hohen Rechenaufwands von Matrixformeln langsamer werden.
- Achten Sie darauf, den Quellbereich währenddessen nicht anzupassen, da dies zu ungenauen Ergebnissen oder Formelfehlern führen kann.
- Enthält das Ergebnis leere Zeilen, wurden alle gemeinsamen Werte erfolgreich extrahiert – die verbleibenden Zellen weisen keine weiteren Überschneidungen auf.
Entfesseln Sie die Magie von Excel mit KUTOOLS AI
- Intelligente Ausführung: Führen Sie Zelloperationen durch, analysieren Sie Daten und erstellen Sie Diagramme – alles ganz einfach per Sprachbefehl.
- Benutzerdefinierte Formeln: Erstellen Sie maßgeschneiderte Formeln, um Ihre Arbeitsabläufe optimal zu optimieren.
- VBA-Programmierung: Schreiben und implementieren Sie VBA-Code ganz mühelos.
- Formelinterpretation: Verstehen Sie komplexe Formeln spielend leicht.
- Textübersetzung: Überwinden Sie Sprachbarrieren direkt in Ihren Tabellenkalkulationen.
VBA-Makro zum Extrahieren von Werten, die in allen drei Spalten enthalten sind
Wenn Sie einen automatisierten Ansatz bevorzugen, der das manuelle Eingeben oder Kopieren komplexer Formeln überflüssig macht, nutzen Sie Excel-VBA, um Ihre Daten zeilenweise zu durchlaufen und ausschließlich die Werte auszugeben, die in allen drei Spalten enthalten sind. Diese Methode eignet sich besonders für sehr große Datensätze oder dynamische Bereiche – denn VBA verarbeitet wiederkehrende Aufgaben und benutzerdefinierte Kriterien deutlich effizienter.
1. Klicken Sie auf Entwickler > Visual Basic, um den VBA-Editor zu öffnen (falls die)Entwickler-Registerkarte nicht sichtbar ist, können Sie sie unter Datei > Optionen > Menüband und Symbolleiste anpassen > Menüband aktivieren).
2. Klicken Sie im VBA-Editor auf Einfügen > Modul, um ein neues Modul zu erstellen. Fügen Sie anschließend den folgenden Code in das Modulfenster ein:
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. Drücken Sie im VBA-Fenster bei ausgewähltem Modul F5 oder klicken Sie auf die Schaltfläche Ausführen (▶), um den Code auszuführen. Anschließend werden Sie nacheinander aufgefordert, die drei Spaltenbereiche auszuwählen, die Sie vergleichen möchten. Markieren Sie bei jeder Aufforderung die entsprechenden Zellen mit der Maus.
4. Das Makro verarbeitet Ihre Auswahl und gibt alle Werte, die in allen drei Spalten enthalten sind, ab der ersten Zeile in die nächste leere Spalte rechts neben Ihrem aktuellen Datensatz aus.
Diese Methode eignet sich hervorragend für große oder dynamische Datensätze und lässt sich mühelos auf vier oder mehr Spalten erweitern, indem die Dictionary-Logik einfach dupliziert wird. Speichern Sie Ihre Arbeitsmappe stets vor dem Ausführen von Makros – nicht gespeicherte Änderungen können nämlich nicht rückgängig gemacht werden.
Beste Office-Produktivitätstools
Verbessern Sie Ihre Excel-Kenntnisse mit Kutools für Excel und erleben Sie Effizienz wie nie zuvor.Kutools für Excel bietet über 300 erweiterte Funktionen zur Steigerung der Produktivität und Zeit sparen.Klicken Sie hier, um die Funktion zu erhalten, die Sie am dringendsten benötigen...
Office Tab bringt eine tabbasierte Oberfläche in Office und macht Ihre Arbeit viel einfacher
- Aktivieren Sie tabbasiertes Bearbeiten und Lesen in Word, Excel, PowerPoint, Publisher, Access, Visio und Project.
- Öffnen und erstellen Sie mehrere Dokumente in neuen Registerkarten desselben Fensters – statt jedes in einem separaten Fenster zu öffnen.
- Steigert Ihre Produktivität um 50 % und erspart Ihnen täglich Hunderte von Mausklicks!
Alle Kutools-Add-Ins – ein Installationsprogramm
Kutools for Office-Paket bündelt Add-Ins für Excel, Word, Outlook und PowerPoint sowie Office Tab Pro – ideal für Teams, die mit mehreren Office-Anwendungen arbeiten.
- Alles-in-einem-Paket— Add-Ins für Excel, Word, Outlook & PowerPoint sowie Office Tab Pro
- Ein Installationsprogramm, eine Lizenz— innerhalb weniger Minuten eingerichtet (MSI-fähig)
- Funktioniert besser zusammen— optimierte Produktivität über alle Office-Anwendungen hinweg
- 30-tägige Vollversion zum Testen— keine Registrierung, keine Kreditkarte erforderlich
- Bestes Preis-Leistungs-Verhältnis— sparen Sie im Vergleich zum Kauf einzelner Add-Ins