Wie kann man in Excel mit SVERWEIS nachschlagen und mehrere entsprechende Werte verketten?
Wenn Sie SVERWEIS in Excel verwenden, gibt die Funktion normalerweise nur den ersten übereinstimmenden Wert zurück, den sie für ein bestimmtes Suchkriterium findet. Es gibt jedoch viele gängige Szenarien, in denen Sie möglicherweise alle übereinstimmenden Werte abrufen und kombinieren müssen, die mit einem bestimmten Schlüssel verknüpft sind, wie zum Beispiel das Auflisten aller Schüler in einer Klasse oder aller Produkte, die zu einer bestimmten Kategorie gehören. Da die Standard-SVERWEIS-Funktion in dieser Hinsicht begrenzt ist, fragen Sie sich vielleicht, wie Sie die Fähigkeit erlangen können, sowohl nachzuschlagen als auch mehrere entsprechende Ergebnisse in einer einzigen Zelle zu verketten. Im Folgenden werden wir mehrere praktische und effiziente Methoden untersuchen, um diese Aufgabe zu bewältigen, die für verschiedene Excel-Versionen und Benutzervorlieben geeignet sind.
SVERWEIS und Verkettung mehrerer entsprechender Werte in Excel
SVERWEIS und Verkettung mehrerer entsprechender Werte mit TEXTVERBINDEN und FILTER-Funktionen
Wenn Sie Excel 365 oder Excel 2021 verwenden, bietet die Kombination der Funktionen TEXTVERBINDEN und FILTER einen effizienten, formelbasierten Ansatz, um mit SVERWEIS nachzuschlagen und alle übereinstimmenden Werte zu verketten. Diese Lösung ist besonders für dynamische und aktualisierte Datensätze geeignet, da sie das Ergebnis automatisch aktualisiert, wenn sich die Quelldaten ändern. Am besten wird sie angewendet, wenn Ihre Excel-Version die FILTER-Funktion unterstützt, die ausschließlich in den neuesten Office-Versionen verfügbar ist.
Geben Sie in der Zielformel die folgende Formel ein und ziehen Sie die Formel nach unten, wenn Sie sie auf andere Zeilen anwenden möchten. Alle entsprechenden übereinstimmenden Werte werden extrahiert und in eine Zelle kombiniert. Siehe Screenshot:
=TEXTJOIN(", ", TRUE, FILTER($B$2:$B$16, $A$2:$A$16=D2, ""))
- FILTER($B$2:$B$16, $A$2:$A$16=D2, ""): Dieser Teil der Formel überprüft jeden Wert in $A$2:$A$16; falls er mit dem Wert in D2 übereinstimmt, wird der entsprechende Wert in $B$2:$B$16 im Ergebnisarray enthalten sein.
- $B$2:$B$16: Der Bereich, aus dem die übereinstimmenden Werte abgerufen werden.
- $A$2:$A$16=D2: Die Bedingung, unter der Werte ausgewählt werden – nur die Zeilen, in denen $A$2:$A$16 gleich dem Inhalt von D2 ist, werden verarbeitet.
- TEXTVERBINDEN(", ", WAHR, ...): Diese Funktion nimmt die Ausgabe der FILTER-Funktion (ein Array von Übereinstimmungen) und verkettet sie zu einem Textstring, getrennt durch das angegebene Trennzeichen (Komma und Leerzeichen), wobei leere Einträge automatisch ignoriert werden.
- ", ": Legt Komma und Leerzeichen als Trennzeichen fest; Sie können dieses Symbol bei Bedarf ändern, zum Beispiel Semikolon oder Zeilenumbrüche verwenden.
- WAHR: Stellt sicher, dass leere Zellen im Kombinationsprozess ignoriert werden, sodass Sie ein ordentlich formatiertes Ergebnis erhalten.
Besonderer Hinweis: Diese Methode erfordert Excel 365 oder 2021 und funktioniert nicht in älteren Versionen (z.B. Excel 2019, 2016 oder früher). Überprüfen Sie immer Ihre Excel-Version, bevor Sie sie anwenden.
Tipp: Wenn sich Ihr Suchwert (z.B. D2) ändert oder zusätzliche übereinstimmende Elemente zum Datenbereich hinzugefügt werden, aktualisiert sich das Ergebnis automatisch, ohne dass zusätzliche Schritte erforderlich sind.
Potenzielle Einschränkungen: Bei sehr großen Datensätzen kann die Berechnungszeit der Formel zunehmen. Außerdem müssen Benutzer sicherstellen, dass es keine zusammengeführten Zellen im Such- oder Ergebnisbereich gibt, da diese zu Formelfehlern führen können.
SVERWEIS und Verkettung mehrerer entsprechender Werte mit Kutools für Excel
Wenn Sie eingebaute Formelmethoden knifflig finden oder Ihre Excel-Version keine erweiterten Funktionen wie TEXTVERBINDEN und FILTER unterstützt, bietet Kutools für Excel eine benutzerfreundliche grafische Lösung. Die Ein-zu-viele-Suche in Kutools ermöglicht es Ihnen, mit wenigen Schritten nachzuschlagen und mehrere übereinstimmende Ergebnisse zu verketten, was sowohl für Anfänger als auch für fortgeschrittene Benutzer geeignet ist. Mit Kutools gibt es keine Notwendigkeit, komplizierte Formeln oder Codes zu schreiben, und es ist besonders praktisch, wenn Sie mit großen oder variablen Datensätzen arbeiten, die wiederholte Nachschlage- und Aggregationsvorgänge erfordern.
Nach der Installation von Kutools für Excel folgen Sie den folgenden Schritten:
Klicken Sie auf Kutools > Erweiterte SVERWEIS > Ein-zu-viele-Suche (mehrere Ergebnisse zurückgeben), um das Setup-Dialogfeld zu öffnen. Innerhalb dieses Dialogfelds können Sie Ihre Nachschlage- und Ausgabeeinstellungen schnell konfigurieren, indem Sie die folgenden Schritte ausführen:
- Wählen Sie Ihre Zieldatenausgabe für die verketteten Ergebnisse sowie die Zellen aus, die die Werte enthalten, nach denen Sie suchen möchten;
- Geben Sie den Tabellenbereich an, der sowohl den Suchschlüssel als auch die Ergebnisspalten enthält;
- Geben Sie an, welche Spalte die Suchschlüssel (Schlüsselspalte) und die Spalte enthält, deren Werte verkettet werden sollen (Rückgabespalte);
- Klicken Sie auf die Schaltfläche OK, um Ihre Einstellungen zu bestätigen und die Daten zu verarbeiten.
Ergebnis: Kutools zeigt nun alle übereinstimmenden und verketteten Werte in Ihrer ausgewählten Ausgabezelle an. Siehe Screenshot:
Diese Methode wird besonders empfohlen für diejenigen, die lieber direkt über die Excel-Oberfläche arbeiten, ohne komplexe Formeln oder Code. Sie reduziert auch die Wahrscheinlichkeit von Formelfehlern und verbessert die Produktivität bei der Handhabung wiederholter Nachschlage- und Verkettungsaufgaben.
SVERWEIS und Verkettung mehrerer entsprechender Werte mit einer benutzerdefinierten Funktion
Für Benutzer, die mit VBA (Visual Basic for Applications) vertraut sind, oder solche, die ältere Excel-Versionen verwenden, die keine dynamischen Arrays oder FILTER-Funktionsunterstützung haben, können Sie eine benutzerdefinierte Funktion erstellen (UDF), um flexible Verkettungen mehrerer Ergebnisse zu erreichen. Diese Methode ist universell mit allen Excel-Versionen kompatibel und kann auf bestimmte Trennsymbole oder Bedingungen zugeschnitten werden.
1. Halten Sie die Tasten ALT + F11 gedrückt, um das Fenster Microsoft Visual Basic for Applications zu öffnen.
2. Klicken Sie auf Einfügen > Modul und fügen Sie den folgenden Code in das Modulfenster ein.
VBA-Code: SVERWEIS und Verkettung mehrerer übereinstimmender Werte in einer Zelle
Function ConcatenateMatches(LookupValue As String, LookupRange As Range, ReturnRange As Range, Optional Delimiter As String = ", ") As String
'Updateby Extendoffice
Dim Cell As Range
Dim Result As String
Result = ""
For Each Cell In LookupRange
If Cell.Value = LookupValue Then
Result = Result & Cell.Offset(0, ReturnRange.Column - LookupRange.Column).Value & Delimiter
End If
Next Cell
If Result <> "" Then
Result = Left(Result, Len(Result) - Len(Delimiter))
End If
ConcatenateMatches = Result
End Function
3. Speichern und schließen Sie den VBA-Editor. Gehen Sie zurück zu Ihrem Arbeitsblatt und verwenden Sie diese UDF, indem Sie die Formel: =ConcatenateMatches(D2, $A$2:$A$16, $B$2:$B$16) in eine leere Zelle eingeben, wo Sie Ihr Ergebnis wünschen. Ziehen Sie den Ausfüllkursor nach unten, um die Formel bei Bedarf in andere Zellen zu kopieren. Alle entsprechenden Werte basierend auf einem bestimmten Suchwert werden zurückgegeben und in einer Zelle verkettet, getrennt durch ein Komma und ein Leerzeichen. Siehe Screenshot:
- D2: Der Suchwert, der innerhalb Ihres Datensatzes übereinstimmen soll (LookupValue).
- A2:A16: Der Bereich, in dem die Funktion nach dem Suchwert sucht (LookupRange).
- B2:B16: Der Bereich, der die Werte enthält, die verkettet werden sollen, wenn der Suchwert übereinstimmt (ReturnRange).
SVERWEIS und Verkettung mehrerer entsprechender Werte mit VBA-Code
Für Szenarien, die wiederholte Nutzung erfordern, oder für diejenigen, die benutzerdefinierte Funktionen in Arbeitsblattzellen vermeiden möchten, können Sie ein fertiges VBA-Makro verwenden, um Ergebnisse direkt zu verketten. Diese Methode funktioniert gut in freigegebenen Umgebungen, in denen nicht alle Benutzer dieselbe Version oder Add-ins haben.
1. Klicken Sie auf Entwicklertools > Visual Basic, um den VBA-Editor zu öffnen.
2. Klicken Sie im VBA-Fenster auf Einfügen > Modul und fügen Sie diesen Code in das Modul ein:
Sub VLookupAndConcatenate()
Dim ws As Worksheet
Dim dataRange As Range, lookupRange As Range, resultRange As Range
Dim dict As Object
Dim i As Long, lastRow As Long
Dim lookupValue As Variant, result As String
Dim delimiter As String
delimiter = ", "
Set dict = CreateObject("Scripting.Dictionary")
Set ws = ActiveSheet
On Error Resume Next
Set dataRange = Application.InputBox( _
Prompt:="Please select the data range (contains lookup column and result column)", _
Title:="Select Data Range", _
Type:=8)
On Error GoTo 0
If dataRange Is Nothing Then Exit Sub
On Error Resume Next
Set lookupRange = Application.InputBox( _
Prompt:="Please select the lookup range (single column)", _
Title:="Select Lookup Range", _
Type:=8)
On Error GoTo 0
If lookupRange Is Nothing Then Exit Sub
On Error Resume Next
Set resultRange = Application.InputBox( _
Prompt:="Please select the starting cell for results output", _
Title:="Select Output Location", _
Type:=8)
On Error GoTo 0
If resultRange Is Nothing Then Exit Sub
resultRange.Resize(lookupRange.Rows.Count, 1).ClearContents
For i = 1 To dataRange.Rows.Count
lookupValue = dataRange.Cells(i, 1).Value
If Not dict.Exists(lookupValue) Then
dict.Add lookupValue, dataRange.Cells(i, 2).Value
Else
dict(lookupValue) = dict(lookupValue) & delimiter & dataRange.Cells(i, 2).Value
End If
Next i
For i = 1 To lookupRange.Rows.Count
lookupValue = lookupRange.Cells(i, 1).Value
If dict.Exists(lookupValue) Then
resultRange.Cells(i, 1).Value = dict(lookupValue)
Else
resultRange.Cells(i, 1).Value = "Not Found"
End If
Next i
MsgBox "Operation completed! Processed " & lookupRange.Rows.Count & " lookup values.", vbInformation
End Sub
3. Klicken Sie auf die 
Schaltfläche, um das Makro auszuführen. Die Eingabefelder fordern Sie auf, Ihren Datenbereich, Suchbereich und Ergebnisbereich auszuwählen. Das verkettete Ergebnis wird dann direkt in den ausgewählten Ausgabezellen angezeigt.
Dieser Makroansatz ist besonders nützlich, wenn Sie häufig mehrere Verkettungssuchen mit unterschiedlichen Werten durchführen, da er verhindert, dass das Arbeitsblatt mit UDF-Aufrufen überladen wird.
Sie können das Trennzeichen im Code leicht anpassen, falls nötig, und das Makro erweitern, um Ergebnisse gemäß Ihrem Workflow in eine Zelle oder Datei auszugeben.
Das Verketten mehrerer entsprechender Werte in Excel ist möglich mit verschiedenen Ansätzen, die jeweils spezifische Vorteile je nach Ihrer Situation bieten. Ob Sie sich für dynamische Arrayformeln, Add-ins wie Kutools für Excel oder VBA-basierte Methoden entscheiden, Sie werden Ihre Fähigkeit verbessern, gruppierte Daten effizient zu analysieren und darzustellen. Abhängig von der Größe und Komplexität Ihres Datensatzes sollten Sie überlegen, welcher Ansatz die optimale Leistung und Wartbarkeit für Sie oder Ihr Team bietet. Überprüfen Sie bei täglichen Operationen auf Datenkonsistenz, vermeiden Sie zusammengeführte Zellen und überprüfen Sie Referenzbereiche für beste Ergebnisse. Wenn Sie Fehler bei der Formelberechnung feststellen, überprüfen Sie doppelt, ob Ihre Bereiche mit den Daten übereinstimmen und ob Sie die richtige Formeleingabemethode für Ihre Excel-Version verwenden.
Für weitere fortschrittliche Excel-Techniken und eine Vielzahl praktischer Anleitungen besuchen Sie unsere umfangreiche Tutorialbibliothek.
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