Mehrere übereinstimmende Werte basierend auf mehreren Kriterien in Excel zurückgeben (Vollständige Anleitung)
Excel-Benutzer stoßen häufig auf Szenarien, in denen es notwendig ist, mehrere Werte zu extrahieren, die gleichzeitig mehrere Kriterien erfüllen, und alle übereinstimmenden Ergebnisse in einer Spalte, einer Zeile oder konsolidiert in einer einzigen Zelle darzustellen. Diese Anleitung untersucht Methoden für alle Excel-Versionen sowie die neuere FILTER-Funktion, die in Excel 365 und 2021 verfügbar ist.
Mehrere übereinstimmende Werte basierend auf mehreren Kriterien in einer einzigen Zelle zurückgeben
In Excel stellt das Extrahieren von mehreren übereinstimmenden Werten basierend auf mehreren Kriterien innerhalb einer einzigen Zelle eine häufige Herausforderung dar. Hier werden zwei effiziente Methoden vorgestellt.
Methode 1: Verwenden der Textjoin-Funktion (Excel365 / 2021, 2019)
Um alle übereinstimmenden Werte mit Trennzeichen in einer einzigen Zelle zu erhalten, kann Ihnen die TEXTJOIN-Funktion helfen.
Geben Sie die folgende Formel in eine leere Zelle ein oder kopieren Sie sie und drücken Sie dann die Eingabetaste (Excel 2021 und Excel 365) oder Strg + Umschalt + Eingabetaste in Excel 2019, um das Ergebnis zu erhalten:
=TEXTJOIN(", ", TRUE, IF(($A$2:$A$18=E2)*($B$2:$B$18=F2), $C$2:$C$18, ""))
- ($A$2:$A$21=E2)*($B$2:$B$21=F2) überprüft, ob jede Zeile beide Bedingungen erfüllt: „Verkäufer entspricht E2“ und „Monat entspricht F2“. Wenn beide Bedingungen erfüllt sind, ist das Ergebnis 1; andernfalls 0. Das Sternchen * bedeutet, dass beide Bedingungen wahr sein müssen.
- IF(..., $C$2:$C$21, "") gibt den Produktnamen zurück, wenn die Zeile übereinstimmt; andernfalls wird eine Leerzeile zurückgegeben.
- TEXTJOIN(", ", WAHR, ...) kombiniert alle nicht leeren Produktnamen in einer Zelle, getrennt durch ", ".
Methode 2: Verwenden von Kutools für Excel
Kutools für Excel bietet eine leistungsstarke und einfache Lösung, mit der Sie schnell mehrere Übereinstimmungen abrufen und in eine einzelne Zelle basierend auf mehreren Kriterien zusammenführen können, ohne komplexe Formeln.
Nach der Installation von Kutools für Excel gehen Sie wie folgt vor:
- Wählen Sie den Datenbereich aus, aus dem Sie alle entsprechenden Werte basierend auf den Kriterien abrufen möchten.
- Klicken Sie dann auf Kutools > Zusammenführen & Aufteilen > Erweiterte Zeilen zusammenführen, siehe Screenshot:
- Konfigurieren Sie im Dialogfeld Erweiterte Zeilen zusammenführen die folgenden Optionen:
- Wählen Sie die Spaltenüberschriften aus, die Ihre Übereinstimmungskriterien enthalten (z. B. Verkäufer und Monat). Für jede ausgewählte Spalte klicken Sie auf Primärschlüssel, um diese als Ihre Suchbedingungen zu definieren.
- Klicken Sie auf die Spaltenüberschrift, in der Sie die kombinierten Ergebnisse anzeigen möchten (z. B. Produkt). Wählen Sie im Bereich Kombinieren Ihr bevorzugtes Trennzeichen (z. B. Komma, Leerzeichen oder benutzerdefiniertes Trennzeichen) aus.
- Klicken Sie abschließend auf die Schaltfläche OK.
Ergebnis: Kutools führt sofort alle übereinstimmenden Werte pro eindeutiger Kriterienkombination in einer einzigen Zelle zusammen.
Mehrere übereinstimmende Werte basierend auf mehreren Kriterien in einer Spalte zurückgeben
Wenn Sie mehrere übereinstimmende Datensätze aus einem Datensatz basierend auf mehreren Bedingungen extrahieren und anzeigen müssen und die Ergebnisse im vertikalen Spaltenformat zurückgeben möchten, bietet Excel mehrere leistungsstarke Lösungen.
Methode 1: Verwenden einer Array-Formel (für alle Versionen)
Sie können die folgende Array-Formel verwenden, um die Ergebnisse vertikal in einer Spalte zurückzugeben:
1. Kopieren oder geben Sie die folgende Formel in eine leere Zelle ein:
=IFERROR(INDEX($C$2:$C$18, SMALL(IF(($A$2:$A$18=$E$2)*($B$2:$B$18=$F$2), ROW($C$2:$C$18)-ROW($C$2)+1), ROW(1:1))), "")2. Drücken Sie Strg + Umschalt + Eingabe, um das erste übereinstimmende Ergebnis zu erhalten, und wählen Sie dann die erste Formelzelle aus und ziehen Sie den Ausfüllknauf nach unten bis zu den Zellen, bis eine leere Zelle angezeigt wird. Nun werden alle übereinstimmenden Werte wie im folgenden Screenshot gezeigt zurückgegeben:
- $A$2:$A$18=$E$2: Überprüft, ob der Verkäufer mit dem Wert in Zelle E2 übereinstimmt.
- $B$2:$B$18=$F$2: Überprüft, ob der Monat mit dem Wert in Zelle F2 übereinstimmt.
- * ist ein logischer UND-Operator (beide Bedingungen müssen wahr sein).
- ZEILE($C$2:$C$18)-ZEILE($C$2)+1: Generiert eine relative Zeilennummer für jedes Produkt.
- KLEIN(..., ZEILE(1:1)): Ruft die n-kleinste übereinstimmende Zeile ab (je nachdem, wie weit die Formel nach unten gezogen wurde).
- INDEX(...): Gibt das Produkt aus der übereinstimmenden Zeile zurück.
- WENNFEHLER(..., ""): Gibt eine leere Zelle zurück, falls keine weiteren Übereinstimmungen vorhanden sind.
Methode 2: Verwenden der Filterfunktion (Excel365 / 2021)
Wenn Sie Excel 365 oder Excel 2021 verwenden, ist die FILTER-Funktion eine hervorragende Wahl, um mehrere Ergebnisse basierend auf mehreren Kriterien zurückzugeben, dank ihrer Einfachheit, Klarheit und Fähigkeit, Ergebnisse dynamisch ohne komplexe Array-Formeln zu verteilen.
Kopieren oder geben Sie die folgende Formel in eine leere Zelle ein und drücken Sie die Eingabetaste. Alle übereinstimmenden Datensätze werden basierend auf den mehreren Kriterien zurückgegeben.
=FILTER(C2:C18, (A2:A18=E2)*(B2:B18=F2), "No match")
- FILTER(...) gibt alle Werte aus C2:C18 zurück, wo beide Bedingungen erfüllt sind.
- (A2:A18=E2)*(B2:B18=F2): Logisches Array, das auf übereinstimmenden Verkäufern und Monaten prüft.
- "Keine Übereinstimmung": Optionale Meldung, falls keine Werte gefunden werden.
Mehrere übereinstimmende Werte basierend auf mehreren Kriterien in einer Zeile zurückgeben
Excel-Benutzer müssen oft mehrere Werte aus einem Datensatz extrahieren, die mehrere Bedingungen erfüllen, und sie horizontal anzeigen (in einer Zeile). Dies ist nützlich zum Erstellen dynamischer Berichte, Dashboards oder Übersichtstabellen, bei denen der vertikale Platz begrenzt ist. In diesem Abschnitt werden zwei leistungsstarke Methoden untersucht.
Methode 1: Verwenden einer Array-Formel (für alle Versionen)
Traditionelle Array-Formeln ermöglichen das Extrahieren mehrerer übereinstimmender Werte unter Verwendung der Funktionen INDEX, KLEIN, WENN und SPALTE. Im Gegensatz zur vertikalen Extraktion (spaltenbasiert) passen wir die Formel so an, dass die Ergebnisse in einer Zeile zurückgegeben werden.
1. Kopieren oder geben Sie die folgende Formel in eine leere Zelle ein:
=IFERROR(INDEX($C$2:$C$18, SMALL(IF(($A$2:$A$18=$E$2)*($B$2:$B$18=$F$2), ROW($C$2:$C$18)-ROW($C$2)+1), COLUMN(A1))), "")2. Drücken Sie Strg + Umschalt + Eingabe, um das erste übereinstimmende Ergebnis zu erhalten, und wählen Sie dann die erste Formelzelle aus und ziehen Sie die Formel nach rechts über die Spalten, um alle Ergebnisse abzurufen.
- $A$2:$A$18=$E$2: Überprüft, ob der Verkäufer übereinstimmt.
- $B$2:$B$18=$F$2: Überprüft, ob der Monat übereinstimmt.
- *: Logisches UND – beide Bedingungen müssen wahr sein.
- ZEILE($C$2:$C$18)-ZEILE($C$2)+1: Erstellt relative Zeilennummern.
- SPALTE(A1): Passt an, welches Match zurückgegeben werden soll, abhängig davon, wie weit die Formel nach rechts gezogen wurde.
- WENNFEHLER(...): Verhindert Fehler, sobald die Übereinstimmungen erschöpft sind.
Methode 2: Verwenden der Filterfunktion (Excel365 / 2021)
Kopieren oder geben Sie die folgende Formel in eine leere Zelle ein und drücken Sie die Eingabetaste, alle übereinstimmenden Werte werden extrahiert und in einer Zeile platziert. Siehe Screenshot:
=TRANSPOSE(FILTER(C2:C18, (A2:A18=E2)*(B2:B18=F2), "No match"))
- FILTER(...): Ruft übereinstimmende Werte aus Spalte C basierend auf den zwei Bedingungen ab.
- (A2:A18=E2)*(B2:B18=F2): Beide Bedingungen müssen wahr sein.
- TRANSPOSE(...): Konvertiert das von FILTER zurückgegebene vertikale Array in ein horizontales Array.
🔚 Fazit
Das Abrufen mehrerer übereinstimmender Werte basierend auf mehreren Kriterien in Excel kann auf verschiedene Arten erfolgen, je nachdem, wie Sie die Ergebnisse anzeigen möchten – ob in einer Spalte, einer Zeile oder innerhalb einer einzigen Zelle.
- Für Benutzer mit Excel 365 oder Excel 2021 bietet die FILTER-Funktion eine moderne, dynamische und elegante Lösung, die die Komplexität minimiert.
- Für diejenigen, die ältere Versionen verwenden, bleiben Array-Formeln leistungsstarke Werkzeuge, obwohl sie etwas mehr Einrichtung und Sorgfalt erfordern.
- Darüber hinaus, wenn Sie Ergebnisse in einer einzigen Zelle zusammenfassen oder eine No-Code-Lösung bevorzugen, können die TEXTJOIN-Funktion oder Drittanbietertools wie Kutools für Excel den Prozess erheblich vereinfachen.
Wählen Sie die Methode, die am besten zu Ihrer Excel-Version und Ihrem bevorzugten Layout passt, und Sie werden gut gerüstet sein, Mehrfachkriterien-Suchen effizient und genau zu handhaben. Wenn Sie daran interessiert sind, weitere Excel-Tipps und -Tricks zu erkunden, bietet unsere Website Tausende von Tutorials, die Ihnen helfen, Excel zu beherrschen.
Weitere relevante Artikel:
- Mehrere Suchwerte in einer durch Kommas getrennten Zelle zurückgeben
- In Excel können wir die VLOOKUP-Funktion anwenden, um den ersten übereinstimmenden Wert aus Tabellenzellen zurückzugeben, aber manchmal müssen wir alle übereinstimmenden Werte extrahieren und dann durch ein bestimmtes Trennzeichen, wie Komma, Bindestrich usw., in einer einzigen Zelle trennen, wie im folgenden Screenshot gezeigt. Wie könnten wir mehrere Suchwerte in einer durch Kommas getrennten Zelle in Excel zurückgeben?
- Vlookup und Rückgabe mehrerer übereinstimmender Werte auf einmal in Google Sheets
- Die normale Vlookup-Funktion in Google Sheets kann Ihnen helfen, den ersten übereinstimmenden Wert basierend auf einem bestimmten Datensatz zu finden und zurückzugeben. Aber manchmal müssen Sie möglicherweise vlookup und alle übereinstimmenden Werte zurückgeben, wie im folgenden Screenshot gezeigt. Haben Sie eine gute und einfache Möglichkeit, diese Aufgabe in Google Sheets zu lösen?
- Vlookup und Rückgabe mehrerer Werte aus einer Dropdown-Liste
- In Excel, wie können Sie vlookup und mehrere entsprechende Werte aus einer Dropdown-Liste zurückgeben, was bedeutet, dass, wenn Sie ein Element aus der Dropdown-Liste auswählen, alle zugehörigen Werte auf einmal angezeigt werden, wie im folgenden Screenshot gezeigt. In diesem Artikel werde ich die Lösung Schritt für Schritt vorstellen.
- Vlookup und Rückgabe mehrerer Werte vertikal in Excel
- Normalerweise können Sie die Vlookup-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 Sie vlookup und alle übereinstimmenden Werte vertikal, horizontal oder in einer einzigen Zelle zurückgeben können.
- Vlookup und Rückgabe übereinstimmender Daten zwischen zwei Werten in Excel
- In Excel können wir die normale Vlookup-Funktion anwenden, um den entsprechenden Wert basierend auf einem bestimmten Datensatz zu erhalten. Aber manchmal möchten wir vlookup 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