Wie gibt man mehrere Suchwerte, durch Kommas getrennt, in einer Zelle zurück?
Beim Arbeiten mit Daten in Excel kann es vorkommen, dass Sie mehrere übereinstimmende Werte für eine Suche zurückgeben und diese in einer einzigen Zelle anzeigen müssen, getrennt durch Kommas, Bindestriche usw. Während die integrierten Suchfunktionen von Excel wie „SVERWEIS“ und „INDEX-VERGLEICH“ hervorragend zur Ermittlung einer einzelnen Übereinstimmung geeignet sind, erfordert das Kombinieren mehrerer Ergebnisse in einer Zelle einen fortgeschritteneren Ansatz. Dieser Artikel stellt einige nützliche Methoden zur Lösung dieser Aufgabe in Excel vor.
Mehrere Suchwerte in einer Zelle, durch Kommas getrennt, zurückgeben
Mehrere Suchwerte in einer Zelle, durch Kommas getrennt, mit den Funktionen TEXTVERBINDEN und FILTER zurückgeben
Wenn Sie Zugriff auf Excel 365 oder Excel 2021 und spätere Versionen haben, ist die Verwendung der dynamischen Arrayformel die einfachste Methode.
Wenden Sie bitte die folgende Formel in eine leere Zelle an, um das Ergebnis auszugeben, und drücken Sie dann die Eingabetaste, um das Ergebnis zu erhalten.
=TEXTJOIN(", ", TRUE, FILTER($B$2:$B$15, $A$2:$A$15=D2))Ziehen Sie dann den Ausfüllkursor nach unten, um die Formel in andere Zellen zu übertragen, siehe Screenshot:
- FILTER(B2:B15; A2:A15=D2): Extrahiert alle Werte in Spalte B, wo Spalte A gleich der Zelle D2 ist.
- TEXTVERBINDEN(", "; WAHR; ...): Fügt die resultierenden Werte zu einer einzigen Zeichenfolge zusammen, getrennt durch Kommas.
Mehrere Suchwerte in einer Zelle, durch Kommas getrennt, mit Kutools für Excel zurückgeben
„Kutools für Excel“ ist ein Add-In, das entwickelt wurde, um komplexe Aufgaben in Excel zu vereinfachen. Seine erweiterte „Ein-zu-viele-Suche“-Funktion ermöglicht es Ihnen, mühelos alle übereinstimmenden Werte in eine einzige Zelle mit einem angegebenen Trennzeichen zu extrahieren – keine komplizierten Formeln oder VBA-Codierung erforderlich.
Nach der Installation von Kutools für Excel gehen Sie bitte wie folgt vor:
Klicken Sie auf „Kutools“ > „Erweiterte SVERWEIS“ > „Ein-zu-viele-Suche (gibt mehrere Ergebnisse zurück)“, um das Dialogfeld zu öffnen. Geben Sie im Dialogfeld bitte die folgenden Operationen an:
- Wählen Sie den „Platzierungsbereich der Liste und Bereich der zu durchsuchenden Werte“ in den Textfeldern separat aus;
- Wählen Sie den Tabellenbereich aus, den Sie verwenden möchten;
- Geben Sie die Schlüsselspalte und Rückgabespalte aus den Dropdown-Menüs „Schlüsselspalte“ und „Rückgabespalte“ separat an;
- Klicken Sie abschließend auf die Schaltfläche „OK“.
Ergebnis:
Alle entsprechenden Zellen mit demselben Wert wurden in eine Zelle kombiniert, die durch Kommas getrennt sind, siehe Screenshot:
Mehrere Suchwerte in einer Zelle, durch Kommas getrennt, mit einer benutzerdefinierten Funktion zurückgeben
Wenn Sie nicht über Excel 365 oder Excel 2021 verfügen, wird Sie dieser Abschnitt Schritt für Schritt durch die Erstellung und Verwendung einer UDF führen, um mehrere Suchwerte in einer einzigen Zelle, durch Kommas getrennt, zurückzugeben.
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: Mehrere Suchwerte in eine durch Kommas getrennte Zelle zurückgeben
Function JoinLookupValues(lookupValue As String, lookupRange As Range, returnRange As Range, delimiter As String) As String
'Updateby Extendoffice
Dim result As String
Dim i As Long
result = ""
For i = 1 To lookupRange.Rows.Count
If lookupRange.Cells(i, 1).Value = lookupValue Then
result = result & returnRange.Cells(i, 1).Value & delimiter
End If
Next i
If Len(result) > 0 Then
JoinLookupValues = Left(result, Len(result) - Len(delimiter))
Else
JoinLookupValues = ""
End If
End Function
3. Speichern Sie diesen Code und schließen Sie das Modulfenster, kehren Sie zu Ihrem Arbeitsblatt zurück und geben Sie die folgende Formel in eine leere Zelle ein, in der Sie das Ergebnis zurückgeben möchten. Ziehen Sie dann den Ausfüllkursor nach unten, um diese Formel in andere Zellen zu übertragen, siehe Screenshot:
=JoinLookupValues(D2, $A$2:$A$15, $B$2:$B$15, ", ")
Zusammengefasst kann das Zurückgeben mehrerer Suchwerte in einer einzigen Zelle, durch Kommas getrennt, effizient mit verschiedenen Methoden erreicht werden, abhängig von Ihrer Excel-Version und Ihren Anforderungen. Durch die Auswahl der Methode, die am besten zu Ihren Anforderungen passt, können Sie Ihre Datenanalyse optimieren und die Produktivität steigern. Wenn Sie daran interessiert sind, weitere Excel-Tipps und -Tricks zu erkunden, bietet unsere Website Tausende von Tutorials.
Verwandte Artikel:
- SVERWEIS und Rückgabe mehrerer Werte ohne Duplikate
- Beim Arbeiten mit Daten in Excel möchten Sie manchmal mehrere übereinstimmende Werte für ein bestimmtes Suchkriterium zurückgeben. Die Standard-SVERWEIS-Funktion gibt jedoch nur einen einzigen Wert zurück. In Situationen, in denen mehrere Übereinstimmungen existieren und Sie diese in einer einzigen Zelle ohne Duplikate anzeigen möchten, können Sie alternative Methoden verwenden, um dies zu erreichen.
- Rückgabe mehrerer übereinstimmender Werte basierend auf einem oder mehreren Kriterien
- Normalerweise ist es für die meisten von uns einfach, einen bestimmten Wert zu suchen und den passenden Eintrag zurückzugeben, indem wir die SVERWEIS-Funktion verwenden. Aber haben Sie jemals versucht, mehrere übereinstimmende Werte basierend auf einem oder mehreren Kriterien zurückzugeben, wie im folgenden Screenshot gezeigt? In diesem Artikel stelle ich einige Formeln vor, um diese komplexe Aufgabe in Excel zu lösen.
- SVERWEIS und Rückgabe des letzten übereinstimmenden Werts
- Wenn Sie eine Liste von Elementen haben, die viele Male wiederholt werden, und Sie möchten nur den letzten übereinstimmenden Wert mit Ihren spezifischen Daten kennen. Wie machen Sie das?
- SVERWEIS und Rückgabe übereinstimmender Daten zwischen zwei Werten
- In Excel können wir die normale SVERWEIS-Funktion anwenden, um den entsprechenden Wert basierend auf einem gegebenen Datensatz zu erhalten. Manchmal möchten wir jedoch einen SVERWEIS durchführen und den übereinstimmenden Wert zwischen zwei Werten zurückgeben, wie im folgenden Screenshot gezeigt. Wie könnten Sie diese Aufgabe in Excel bewältigen?
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