Wie vergleiche ich durch Kommas getrennte Werte in zwei Zellen und gebe doppelte oder eindeutige Werte in Excel zurück?
Wie im Screenshot unten gezeigt, gibt es zwei Spalten – Column1 und Column2, jede Zelle in der Spalte enthält durch Kommas getrennte Zahlen. Was könnten Sie tun, um die durch Kommas getrennten Zahlen in Spalte1 mit den Zelleninhalten in derselben Zeile von Spalte2 zu vergleichen und alle doppelten oder eindeutigen Werte zurückzugeben?
Dieses Lernprogramm bietet zwei Methoden, die Ihnen bei der Durchführung dieser Aufgabe helfen.
Vergleichen Sie durch Kommas getrennte Werte in zwei Zellen und geben Sie doppelte oder eindeutige Werte mit Formeln zurück
Dieser Abschnitt enthält zwei Formeln, mit denen Sie die durch Kommas getrennten Werte in zwei Zellen vergleichen und die doppelten oder eindeutigen Werte zwischen ihnen zurückgeben können.
Note: Die folgenden Formeln funktionieren nur in Excel für 365. Wenn Sie andere Excel-Versionen verwenden, versuchen Sie es mit der folgenden VBA-Methode.
Nehmen Sie die beiden obigen Spalten als Beispiel, um die kommagetrennten Zahlen in Spalte1 mit den kommagetrennten Zahlen in derselben Zeile von Spalte2 zu vergleichen und doppelte oder eindeutige Werte zurückzugeben, gehen Sie bitte wie folgt vor.
Geben Sie doppelte Werte zurück
1. Wählen Sie eine Zelle aus, um die doppelten Zahlen zwischen den beiden angegebenen Zellen mit durch Kommas getrennten Zahlen auszugeben. In diesem Fall wähle ich Zelle D2 aus, geben Sie dann die folgende Formel ein und drücken Sie die Enter Schlüssel. Wählen Sie die Formelzelle aus und ziehen Sie sie AutoFill-Griff nach unten, um die doppelten Zahlen zwischen den Zellen in den anderen Zeilen zu erhalten.
=LET(x, TRANSPOSE(TEXTSPLIT(TEXTJOIN(", ",TRUE,A2:B2), ", ")),y,UNIQUE(x),z,UNIQUE(x,,1), TEXTJOIN(", ",TRUE,IF(ISERROR(MATCH(y,z,0)),y, "")))
Gibt eindeutige Werte zurück
Um die eindeutigen Zahlen zwischen den beiden angegebenen Zellen mit kommagetrennten Zahlen in derselben Zeile zurückzugeben, kann die folgende Formel hilfreich sein.
1. Wählen Sie eine Zelle aus, um die eindeutigen Zahlen auszugeben. In diesem Fall wähle ich Zelle E2 aus, geben Sie dann die folgende Formel ein und drücken Sie die Enter Schlüssel. Wählen Sie die Formelzelle aus und ziehen Sie sie AutoFill-Griff nach unten, um die eindeutigen Zahlen zwischen den Zellen in den anderen Zeilen zu erhalten.
=TEXTJOIN(", ",TRUE,UNIQUE(TRANSPOSE(TEXTSPLIT(TEXTJOIN(", ",TRUE,A2:B2), ", ")),,1))
Notizen:
Vergleichen Sie zwei Spalten mit kommagetrennten Werten und geben Sie doppelte oder eindeutige Werte mit VBA zurück
Die in diesem Abschnitt bereitgestellte benutzerdefinierte Funktion hilft, die durch Kommas getrennten Werte in zwei angegebenen Zellen zu vergleichen und die doppelten Werte oder eindeutigen Werte zwischen ihnen zurückzugeben. Bitte gehen Sie wie folgt vor.
Nehmen Sie dasselbe Beispiel wie oben, um die kommagetrennten Zahlen in Spalte1 mit den kommagetrennten Zahlen in derselben Zeile von Spalte2 zu vergleichen und doppelte oder eindeutige Werte zurückzugeben, versuchen Sie bitte die benutzerdefinierte Funktion in diesem Abschnitt.
1. Drücken Sie in der sich öffnenden Arbeitsmappe auf Andere + F11 Schlüssel zum Öffnen der Microsoft Visual Basic für Applikationen Fenster.
2. In dem Microsoft Visual Basic für Applikationen Klicken Sie im Fenster Insert > Modul, und kopieren Sie den folgenden VBA-Code in die Modul (Code) Fenster.
VBA-Code: Vergleichen Sie durch Kommas getrennte Werte in zwei Zellen und geben Sie doppelte/eindeutige Werte zurück
Private Function COMPARE(Rng1, Rng2 As Range, Op As Boolean)
'Updated by Extendoffice 20221019
Dim R1Arr As Variant
Dim R2Arr As Variant
Dim Ans1 As String
Dim Ans2 As String
Dim Separator As String
Dim d1 As New Dictionary
Dim d2 As New Dictionary
Dim d3 As New Dictionary
Application.Volatile
Separator = ", "
R1Arr = Split(Rng1.Value, Separator)
R2Arr = Split(Rng2.Value, Separator)
Ans1 = ""
Ans2 = ""
For Each ch In R2Arr
If Not d2.Exists(ch) Then
d2.Add ch, "1"
End If
Next
If Op Then
For Each ch In R1Arr
If d2.Exists(ch) Then
If Not d3.Exists(ch) Then
d3.Add ch, "1"
Ans1 = Ans1 & ch & Separator
End If
End If
Next
If Ans1 <> "" Then
Ans1 = Mid(Ans1, 1, Len(Ans1) - Len(Separator))
End If
COMPARE = Ans1
Else
For Each ch In R1Arr
If Not d1.Exists(ch) Then
d1.Add ch, "1"
End If
Next
For Each ch In R1Arr
If Not d2.Exists(ch) Then
If Not d3.Exists(ch) Then
d3.Add ch, "1"
Ans2 = Ans2 & ch & Separator
End If
End If
Next
For Each ch In R2Arr
If Not d1.Exists(ch) Then
If Not d3.Exists(ch) Then
d3.Add ch, "1"
Ans2 = Ans2 & ch & Separator
End If
End If
Next
If Ans2 <> "" Then
Ans2 = Mid(Ans2, 1, Len(Ans2) - Len(Separator))
End If
COMPARE = Ans2
End If
End Function3. Nach dem Einfügen des Codes in die Modul (Code) Fenster, gehen Sie zu klicken Tools > Bibliographie öffnen Referenzen - VBAProject Fenster, überprüfen Sie die Microsoft Scripting-Laufzeit und klicken Sie dann auf OK .
4. Drücken Sie die Taste Andere + Q Tasten zum Schließen der Microsoft Visual Basic für Applikationen Fenster.
5. Jetzt müssen Sie zwei Funktionen separat anwenden, um die doppelten und eindeutigen Werte aus zwei kommagetrennten Wertzellen zurückzugeben.
Doppelten Wert zurückgeben
Wählen Sie eine Zelle aus, um die doppelten Zahlen auszugeben. In diesem Beispiel wähle ich die Zelle D2 aus, geben Sie dann die folgende Formel ein und drücken Sie die Enter Taste, um die doppelten Zahlen zwischen Zelle A2 und B2 zu erhalten.
Wählen Sie die Formelzelle aus und ziehen Sie ihren AutoFill-Griff nach unten, um die doppelten Zahlen zwischen Zellen in den anderen Zeilen zu erhalten.
=COMPARE(A2,B2,TRUE)
Gibt eindeutige Werte zurück
Wählen Sie eine Zelle aus, um die eindeutigen Zahlen auszugeben. In diesem Beispiel wähle ich die Zelle E2 aus, geben Sie dann die folgende Formel ein und drücken Sie die Enter Taste, um die eindeutigen Zahlen zwischen Zelle A2 und B2 zu erhalten.
Wählen Sie die Formelzelle aus und ziehen Sie ihren AutoFill-Handle nach unten, um die eindeutigen Zahlen zwischen den Zellen in den anderen Zeilen zu erhalten.
=COMPARE(A2,B2,FALSE)
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, 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 Oberfläche mit Registerkarten in Office und erleichtert Ihnen die Arbeit erheblich
- Aktivieren Sie das Bearbeiten und Lesen von Registerkarten in Word, Excel und PowerPoint, Publisher, Access, Visio und Project.
- Öffnen und erstellen Sie mehrere Dokumente in neuen Registerkarten desselben Fensters und nicht in neuen Fenstern.
- Steigert Ihre Produktivität um 50 % und reduziert jeden Tag Hunderte von Mausklicks für Sie!
