Wie kann man mit SVERWEIS mehrere Werte in einer einzigen Zelle in Excel zurückgeben?
SVERWEIS ist eine leistungsstarke Funktion in Excel, aber standardmäßig gibt sie nur den ersten übereinstimmenden Wert zurück. Was tun, wenn Sie alle übereinstimmenden Werte abrufen und in einer Zelle zusammenfassen möchten? Dies ist eine häufige Anforderung bei der Analyse von Datensätzen oder der Zusammenfassung von Informationen. In diesem Leitfaden führen wir Sie Schritt für Schritt durch Methoden, um mehrere Werte in eine einzelne Zelle sowohl mit Formeln als auch mit hilfreichen Funktionen zurückzugeben.
Mehrere Werte in eine Zelle mit der TEXTVERBINDEN-Funktion zurückgeben (Excel 2019 und Office 365)
- Alle übereinstimmenden Werte in eine Zelle zurückgeben
- Alle übereinstimmenden Werte ohne Duplikate in eine Zelle zurückgeben
Mehrere Werte in eine Zelle mit Kutools zurückgeben
Mehrere Werte in eine Zelle mit einer benutzerdefinierten Funktion zurückgeben
- Alle übereinstimmenden Werte in eine Zelle zurückgeben
- Alle übereinstimmenden Werte ohne Duplikate in eine Zelle zurückgeben
Mehrere Werte in eine Zelle mit der TEXTVERBINDEN-Funktion zurückgeben (Excel 2019 und Office 365)
Wenn Sie eine höhere Version von Excel wie Excel 2019 oder Office 365 haben, gibt es eine neue Funktion - TEXTVERBINDEN. Mit dieser leistungsstarken Funktion können Sie schnell einen SVERWEIS durchführen und alle übereinstimmenden Werte in einer Zelle zurückgeben.
Alle übereinstimmenden Werte in eine Zelle zurückgeben
Bitte wenden Sie die folgende Formel in einer leeren Zelle an, wo Sie das Ergebnis platzieren möchten, dann drücken Sie gleichzeitig die Tasten Strg + Umschalt + Eingabe, um das erste Ergebnis zu erhalten, und ziehen Sie den Ausfüllkästchen nach unten in die Zelle, in der Sie diese Formel verwenden möchten, und Sie erhalten alle entsprechenden Werte, wie im folgenden Screenshot gezeigt:
Alle übereinstimmenden Werte ohne Duplikate in eine Zelle zurückgeben
Wenn Sie alle übereinstimmenden Werte basierend auf den Suchdaten ohne Duplikate zurückgeben möchten, könnte Ihnen die folgende Formel helfen.
Bitte kopieren und fügen Sie die folgende Formel in eine leere Zelle ein, dann drücken Sie gleichzeitig die Tasten Strg + Umschalt + Eingabe, um das erste Ergebnis zu erhalten, und kopieren Sie diese Formel, um andere Zellen zu füllen, und Sie erhalten alle entsprechenden Werte ohne doppelte Einträge, wie im folgenden Screenshot gezeigt:
Mehrere Werte in eine Zelle mit Kutools zurückgeben
Mit der Funktion „Erweiterte Zeilen zusammenführen“ von Kutools für Excel können Sie ganz einfach mehrere übereinstimmende Werte in eine einzige Zelle abrufen – komplexe Formeln sind nicht erforderlich! Verabschieden Sie sich von manuellen Lösungen und entdecken Sie einen effizienteren Weg, um Ihre Suchaufgaben in Excel zu bewältigen. Lassen Sie uns sehen, wie Kutools für Excel dies möglich macht!
Nach der Installation von Kutools für Excel gehen Sie bitte wie folgt vor:
1. Wählen Sie den Datenbereich aus, den Sie basierend auf einer anderen Spalte kombinieren möchten.
2. Klicken Sie auf „Kutools“ > „Zusammenführen & Aufteilen“ > „Erweiterte Zeilen zusammenführen“, siehe Screenshot:
3. Im erscheinenden Dialogfeld „Erweiterte Zeilen zusammenführen“:
- Klicken Sie auf den Namen der Schlüsselspalte, die kombiniert werden soll, und klicken Sie dann auf „Primärschlüssel“.
- Klicken Sie dann auf eine andere Spalte, deren Daten Sie basierend auf der Schlüsselspalte kombinieren möchten, und klicken Sie auf die Dropdown-Liste im Feld „Berechnung“, wählen Sie ein Trennzeichen zum Trennen der kombinierten Daten im Abschnitt „Konsolidieren“ aus.
- Klicken Sie dann auf die Schaltfläche OK.
Alle entsprechenden Werte aus einer anderen Spalte, basierend auf demselben Wert, werden in einer einzigen Zelle kombiniert. Siehe Screenshots:
 |  |  |
Tipps: Wenn Sie beim Zusammenführen von Zellen doppelte Inhalte entfernen möchten, aktivieren Sie einfach die Option „Doppelte Werte löschen“ im Dialogfeld. Dadurch wird sichergestellt, dass nur eindeutige Einträge in einer einzigen Zelle kombiniert werden, was Ihre Daten sauberer und besser organisiert macht, ohne zusätzlichen Aufwand. Siehe Screenshots:
 | |  |
Jetzt Kutools für Excel herunterladen und kostenlos testen!
Mehrere Werte in eine Zelle mit einer benutzerdefinierten Funktion zurückgeben
Die oben genannte TEXTVERBINDEN-Funktion ist nur für Excel 2019 und Office 365 verfügbar. Wenn Sie andere niedrigere Excel-Versionen haben, sollten Sie einige Codes verwenden, um diese Aufgabe abzuschließen.
Alle übereinstimmenden Werte in eine Zelle zurückgeben
1. Halten Sie die Tasten „ALT + F11“ gedrückt, und es öffnet sich das Fenster „Microsoft Visual Basic for Applications“.
2. Klicken Sie auf „Einfügen“ > „Modul“ und fügen Sie den folgenden Code im Modulfenster ein.
VBA-Code: SVERWEIS zur Rückgabe mehrerer Werte in eine Zelle
Function ConcatenateIf(CriteriaRange As Range, Condition As Variant, ConcatenateRange As Range, Optional Separator As String = ",") As Variant
'Updateby Extendoffice
Dim xResult As String
On Error Resume Next
If CriteriaRange.Count <> ConcatenateRange.Count Then
ConcatenateIf = CVErr(xlErrRef)
Exit Function
End If
For i = 1 To CriteriaRange.Count
If CriteriaRange.Cells(i).Value = Condition Then
xResult = xResult & Separator & ConcatenateRange.Cells(i).Value
End If
Next i
If xResult <> "" Then
xResult = VBA.Mid(xResult, VBA.Len(Separator) + 1)
End If
ConcatenateIf = xResult
Exit Function
End Function
3. Speichern und schließen Sie diesen Code, kehren Sie zum Arbeitsblatt zurück und geben Sie diese Formel ein: =VERKETTENWENN($A$2:$A$11; E2; $C$2:$C$11; ", ") in eine bestimmte leere Zelle, wo Sie das Ergebnis platzieren möchten, dann ziehen Sie den Ausfüllkästchen nach unten, um alle entsprechenden Werte in einer Zelle zu erhalten, die Sie möchten, siehe Screenshot:
Alle übereinstimmenden Werte ohne Duplikate in eine Zelle zurückgeben
Um Duplikate in den zurückgegebenen übereinstimmenden Werten zu ignorieren, gehen Sie bitte wie folgt vor.
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 im Modulfenster ein.
VBA-Code: SVERWEIS und Rückgabe mehrerer eindeutiger übereinstimmender Werte in eine Zelle
Function MultipleLookupNoRept(Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer)
'Updateby Extendoffice
Dim xDic As New Dictionary
Dim xRows As Long
Dim xStr As String
Dim i As Long
On Error Resume Next
xRows = LookupRange.Rows.Count
For i = 1 To xRows
If LookupRange.Columns(1).Cells(i).Value = Lookupvalue Then
xDic.Add LookupRange.Columns(ColumnNumber).Cells(i).Value, ""
End If
Next
xStr = ""
MultipleLookupNoRept = xStr
If xDic.Count > 0 Then
For i = 0 To xDic.Count - 1
xStr = xStr & xDic.Keys(i) & ","
Next
MultipleLookupNoRept = Left(xStr, Len(xStr) - 1)
End If
End Function
3. Nachdem Sie den Code eingefügt haben, klicken Sie auf „Extras“ > „Verweise“ im geöffneten Fenster „Microsoft Visual Basic for Applications“, und dann, im erscheinenden Dialogfeld „Verweise – VBAProject“, aktivieren Sie die Option „Microsoft Scripting Runtime“ in der Liste der verfügbaren Verweise, siehe Screenshots:
 | |  |
4. Klicken Sie dann auf OK, um das Dialogfeld zu schließen, speichern und schließen Sie das Codefenster, kehren Sie zum Arbeitsblatt zurück und geben Sie diese Formel ein: =MEHRFACHSUCHEOHNEWIEDERHOLUNG(E2;$A$2:$C$11;3) in eine leere Zelle, wo Sie das Ergebnis ausgeben möchten, und ziehen Sie den Ausfüllkästchen nach unten, um alle übereinstimmenden Werte zu erhalten, siehe Screenshot:
Ob Sie sich für Formeln wie TEXTVERBINDEN in Kombination mit Array-Funktionen entscheiden, Tools wie Kutools für Excel oder benutzerdefinierte Funktionen nutzen – alle Ansätze helfen dabei, komplexe Suchaufgaben zu vereinfachen. Wählen Sie die Methode, die am besten zu Ihren Bedürfnissen passt. Wenn Sie daran interessiert sind, weitere Excel-Tipps und -Tricks zu erkunden, bietet unsere Website Tausende von Tutorials.
Weitere relevante Artikel:
- SVERWEIS-Funktion mit einigen grundlegenden und fortgeschrittenen Beispielen
- In Excel ist die SVERWEIS-Funktion eine leistungsstarke Funktion für die meisten Excel-Benutzer, die verwendet wird, um einen Wert in der am weitesten links stehenden Spalte eines Datenbereichs zu suchen und einen übereinstimmenden Wert aus einer von Ihnen angegebenen Spalte in derselben Zeile zurückzugeben. Dieses Tutorial behandelt, wie man die SVERWEIS-Funktion mit einigen grundlegenden und fortgeschrittenen Beispielen in Excel verwendet.
- Mehrere übereinstimmende Werte basierend auf einem oder mehreren Kriterien zurückgeben
- Normalerweise ist es für die meisten von uns einfach, einen bestimmten Wert zu suchen und das übereinstimmende Element mit der SVERWEIS-Funktion zurückzugeben. Aber haben Sie jemals versucht, mehrere übereinstimmende Werte basierend auf einem oder mehreren Kriterien zurückzugeben? In diesem Artikel werde ich einige Formeln vorstellen, um diese komplexe Aufgabe in Excel zu lösen.
- SVERWEIS und Rückgabe mehrerer Werte vertikal
- Normalerweise können Sie die SVERWEIS-Funktion verwenden, um den ersten entsprechenden Wert zu erhalten, aber manchmal möchten Sie alle übereinstimmenden Datensätze basierend auf einem bestimmten Kriterium zurückgeben. In diesem Artikel werde ich darüber sprechen, wie man mit SVERWEIS alle übereinstimmenden Werte vertikal, horizontal oder in eine einzige Zelle zurückgibt.
- SVERWEIS und Rückgabe mehrerer Werte aus einer Dropdown-Liste
- In Excel, wie könnten Sie mit SVERWEIS mehrere entsprechende Werte aus einer Dropdown-Liste zurückgeben, was bedeutet, dass, wenn Sie ein Element aus der Dropdown-Liste auswählen, alle seine relativen Werte auf einmal angezeigt werden. In diesem Artikel werde ich die Lösung Schritt für Schritt vorstellen.
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