Wie findet man gemeinsame Werte in 3 Spalten in Excel?
Das Arbeiten mit Daten in Excel umfasst oft den Vergleich von Listen, um gemeinsame oder doppelte Einträge zu identifizieren. Während das Vergleichen von zwei Spalten, um gemeinsame Werte zu finden, eine häufige Aufgabe ist, gibt es Situationen, in denen Sie bestimmen müssen, welche Werte gleichzeitig in drei separaten Spalten erscheinen. Zum Beispiel beim Konsolidieren von Umfrageergebnissen, Zusammenführen von Verkaufsdaten oder der Analyse von Duplikaten über mehrere Listen hinweg, ist es wichtig, die Menge an Elementen genau zu extrahieren, die in allen drei Spalten vorhanden sind, wie im folgenden Screenshot gezeigt. Dieser Artikel stellt mehrere praktische Methoden vor, dieses Problem in Excel zu lösen und Ihnen dabei zu helfen, gemeinsame Werte effizient und zuverlässig in drei Spalten zu identifizieren – egal ob Sie Formeln oder VBA bevorzugen.
Gemeinsame Werte in 3 Spalten mit Array-Formeln finden
VBA-Makro zum Extrahieren von Werten, die in allen drei Spalten vorkommen
Gemeinsame Werte in 3 Spalten mit Array-Formeln finden
Um gemeinsame Werte in drei Spalten zu finden und zu extrahieren, können Sie Array-Formeln verwenden, die nach Elementen suchen, die in allen ausgewählten Bereichen erscheinen. Dies ist besonders nützlich in Datensätzen, in denen Sie keine zusätzlichen Excel-Add-Ins oder externe Tools verwenden möchten.
Geben Sie diese Array-Formel in eine leere Zelle ein, in der Sie den ersten gemeinsamen Wert anzeigen möchten:
=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 Array-Formel:
- Drücken Sie nach dem Eingeben der Formel in die gewählte Zelle Shift + Ctrl + Enter (nicht nur Enter). Excel schließt die Formel in geschweifte Klammern ein, um anzuzeigen, dass es sich um eine Array-Formel handelt.
- Ziehen Sie die Formel nach unten in der Spalte, bis leere Zellen erscheinen. Dadurch werden alle Werte aufgelistet, die von den drei Spalten gemeinsam genutzt werden, und leere Zellen zeigen an, dass keine weiteren Übereinstimmungen mehr vorhanden sind.
Hinweise und Parametererklärungen:
- Wenn Sie eine andere Array-Formel bevorzugen, gibt diese ebenfalls alle eindeutigen Werte zurück, die in allen drei Spalten enthalten sind:
Denken Sie erneut daran, Shift + Ctrl + Enter zu drücken, nachdem Sie die Formel eingegeben oder eingefügt haben.=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, C2:C9 sind die Datenbereiche in jeder der drei Spalten, die Sie vergleichen möchten.
- E1 bezieht sich auf die Zelle direkt oberhalb des Startpunkts Ihrer Formel (für Ausschlusslogik). Passen Sie die Zellbezüge an, um Ihren tatsächlichen Datenbereich und die Stelle, an der Sie die Ergebnisse anzeigen möchten, widerzuspiegeln.
- Diese Methoden funktionieren gut für moderate Datensätze, können jedoch bei sehr großen Mengen aufgrund der Berechnungsanforderungen von Array-Formeln langsam werden.
- Achten Sie darauf, die Quellbereiche nicht währenddessen zu ändern, da dies zu ungenauen Ergebnissen oder Formelfehlern führen kann.
- Wenn das Ergebnis leere Zeilen enthält, bedeutet dies, dass alle gemeinsamen Werte extrahiert wurden und die restlichen Zellen keine weiteren Überschneidungen aufweisen.
Entfesseln Sie die Magie von Excel mit Kutools AI
- Intelligente Ausführung: Führen Sie Zellenoperationen durch, analysieren Sie Daten und erstellen Sie Diagramme – alles angetrieben durch einfache Befehle.
- Benutzerdefinierte Formeln: Erstellen Sie maßgeschneiderte Formeln, um Ihre Arbeitsabläufe zu optimieren.
- VBA-Codierung: Schreiben und implementieren Sie VBA-Code mühelos.
- Formelinterpretation: Verstehen Sie komplexe Formeln mit Leichtigkeit.
- Textübersetzung: Überwinden Sie Sprachbarrieren in Ihren Tabellen.
VBA-Makro zum Extrahieren von Werten, die in allen drei Spalten vorkommen
Wenn Sie einen automatisierten Ansatz bevorzugen, der nicht das Eingeben oder Kopieren komplexer Formeln erfordert, können Sie Excel VBA verwenden, um Ihre Daten zu durchlaufen und nur diejenigen Werte auszugeben, die in jeder der drei Spalten vorhanden sind. Diese Methode ist besonders nützlich für sehr große Datensätze oder beim Arbeiten mit dynamischen Bereichen, da VBA effizienter bei der Verarbeitung wiederholter Aufgaben und benutzerdefinierter Kriterien ist.
1. Klicken Sie auf Entwickler > Visual Basic, um den VBA-Editor zu öffnen (wenn die Registerkarte „Entwickler“ nicht sichtbar ist, können Sie sie über Datei > Optionen > Menüband anpassen aktivieren).
2. Klicken Sie im VBA-Editor auf Einfügen > Modul, um ein neues Modul zu erstellen. Fügen Sie dann 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 mit dem ausgewählten Modul F5 oder klicken Sie auf die Schaltfläche Ausführen (▶), um den Code auszuführen. Sie werden nacheinander aufgefordert, jeden der drei Spaltenbereiche auszuwählen, die Sie vergleichen möchten. Markieren Sie mit der Maus die entsprechenden Zellen bei jeder Aufforderung.
4. Das Makro verarbeitet Ihre Auswahl und gibt alle Werte, die in allen drei Spalten vorhanden sind, in die nächste leere Spalte rechts neben Ihrem aktuellen Datensatz aus, beginnend in der ersten Zeile.
Diese Methode ist effizient beim Arbeiten mit großen oder dynamischen Datensätzen und kann leicht auf vier oder mehr Spalten erweitert werden, indem die Dictionary-Logik dupliziert wird. Denken Sie immer daran, Ihre Arbeitsmappe vor dem Ausführen von Makros zu speichern, da nicht gespeicherte Änderungen nicht rückgängig gemacht werden können, wenn Sie zurückkehren möchten.
Die besten Produktivitätstools für das Büro
Stärken Sie Ihre Excel-Fähigkeiten mit Kutools für Excel und genießen Sie Effizienz wie nie zuvor. Kutools für Excel bietet mehr als300 erweiterte Funktionen, um die Produktivität zu steigern und Zeit zu sparen. Klicken Sie hier, um die Funktion zu erhalten, die Sie am meisten benötigen...
Office Tab bringt die Tab-Oberfläche in Office und macht Ihre Arbeit wesentlich einfacher
- Aktivieren Sie die Tabulator-Bearbeitung und das Lesen in Word, Excel, PowerPoint, Publisher, Access, Visio und Project.
- Öffnen und erstellen Sie mehrere Dokumente in neuen Tabs innerhalb desselben Fensters, statt in neuen Einzelfenstern.
- Steigert Ihre Produktivität um50 % und reduziert hunderte Mausklicks täglich!
Alle Kutools-Add-Ins. Ein Installationspaket
Das Kutools for Office-Paket bündelt Add-Ins für Excel, Word, Outlook & PowerPoint sowie Office Tab Pro und ist ideal für Teams, die mit mehreren Office-Anwendungen arbeiten.
- All-in-One-Paket — Add-Ins für Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Ein Installationspaket, eine Lizenz — in wenigen Minuten einsatzbereit (MSI-kompatibel)
- Besser gemeinsam — optimierte Produktivität in allen Office-Anwendungen
- 30 Tage kostenlos testen — keine Registrierung, keine Kreditkarte erforderlich
- Bestes Preis-Leistungs-Verhältnis — günstiger als Einzelkauf der Add-Ins