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 zusammengefasst 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 mehrerer übereinstimmender Werte basierend auf mehreren Kriterien innerhalb einer einzigen Zelle eine häufige Herausforderung dar. Hier werden zwei effiziente Methoden vorgestellt.
Methode 1: Verwendung der TEXTVERBINDEN-Funktion (Excel365 / 2021, 2019)
Um alle übereinstimmenden Werte mit Trennzeichen in einer einzigen Zelle zu erhalten, kann Ihnen die TEXTVERBINDEN-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 gleich E2“ und „Monat gleich F2“. Wenn beide Bedingungen erfüllt sind, ist das Ergebnis 1; andernfalls 0. Das Sternchen * bedeutet, dass beide Bedingungen wahr sein müssen.
- WENN(..., $C$2:$C$21, "") gibt den Produktnamen zurück, wenn die Zeile übereinstimmt; andernfalls wird eine Leerzeile zurückgegeben.
- TEXTVERBINDEN(", ", WAHR, ...) kombiniert alle nicht leeren Produktnamen in eine Zelle, getrennt durch ", ".
Methode 2: Verwendung von Kutools für Excel
Kutools für Excel bietet eine leistungsstarke und dennoch einfache Lösung, mit der Sie schnell mehrere Übereinstimmungen abrufen und in eine einzige Zelle basierend auf mehreren Kriterien kombinieren 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 Kriterien erhalten möchten.
- Klicken Sie dann auf Kutools > Zusammenführen & Aufteilen > Erweiterte Zeilen zusammenführen, siehe Screenshot:
- Im Dialogfeld „Erweiterte Zeilen zusammenführen“ konfigurieren Sie bitte 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, wo Sie die kombinierten Ergebnisse haben möchten (z. B. Produkt). Wählen Sie im Abschnitt Kombinieren Ihr bevorzugtes Trennzeichen aus (z. B. Komma, Leerzeichen oder benutzerdefiniertes Trennzeichen).
- Klicken Sie abschließend auf die Schaltfläche OK.
Ergebnis: Kutools fügt sofort alle übereinstimmenden Werte pro eindeutiger Kriterienkombination in eine einzige 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, wobei die Ergebnisse im vertikalen Spaltenformat zurückgegeben werden sollen, bietet Excel mehrere leistungsstarke Lösungen.
Methode 1: Verwendung einer Array-Formel (für alle Versionen)
Sie können die folgende Array-Formel verwenden, um 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 + Eingabetaste, um das erste übereinstimmende Ergebnis zu erhalten, und wählen Sie dann die erste Formelzelle aus und ziehen Sie den Ausfüllknauf nach unten 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.
- KLEINSTE(..., ZEILE(1:1)): Ruft die n-te kleinste übereinstimmende Zeile ab (während die Formel nach unten gezogen wird).
- INDEX(...): Gibt das Produkt aus der übereinstimmenden Zeile zurück.
- WENNFEHLER(..., ""): Gibt eine leere Zelle zurück, wenn keine weiteren Übereinstimmungen vorhanden sind.
Methode 2: Verwendung der Filterfunktion (Excel365 / 2021)
Wenn Sie Excel 365 oder Excel 2021 verwenden, ist die FILTER-Funktion eine ausgezeichnete Wahl, um mehrere Ergebnisse basierend auf mehreren Kriterien zurückzugeben, dank ihrer Einfachheit, Klarheit und Fähigkeit, Ergebnisse dynamisch zu verteilen, ohne komplexe Array-Formeln.
Kopieren oder geben Sie die folgende Formel in eine leere Zelle ein, und drücken Sie dann 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äufer und Monat überprü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 diese horizontal (in einer Zeile) anzeigen. Dies ist nützlich zum Erstellen dynamischer Berichte, Dashboards oder Übersichtstabellen, bei denen der vertikale Platz begrenzt ist. In diesem Abschnitt werden wir zwei leistungsstarke Methoden untersuchen.
Methode 1: Verwendung einer Array-Formel (für alle Versionen)
Traditionelle Array-Formeln ermöglichen das Extrahieren mehrerer übereinstimmender Werte unter Verwendung der Funktionen INDEX, KLEINSTE, WENN und SPALTE. Im Gegensatz zur vertikalen Extraktion (spaltenbasiert) passen wir die Formel an, um Ergebnisse in einer Zeile 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), COLUMN(A1))), "")2. Drücken Sie Strg + Umschalt + Eingabetaste, 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 Übereinstimmungsergebnis zurückgegeben wird, abhängig davon, wie weit die Formel nach rechts gezogen wurde.
- WENNFEHLER(...): Verhindert Fehler, sobald Übereinstimmungen erschöpft sind.
Methode 2: Verwendung der Filterfunktion (Excel365 / 2021)
Kopieren oder geben Sie die folgende Formel in eine leere Zelle ein, und drücken Sie dann 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.
- TRANSPONIEREN(...): 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 Weisen 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 eine einzige Zelle zusammenfassen oder eine codefreie Lösung bevorzugen, können die TEXTVERBINDEN-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, um 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, um Ihnen zu helfen, Excel zu beherrschen.
Weitere relevante Artikel:
- Mehrere Suchwerte in einer durch Kommas getrennten Zelle zurückgeben
- In Excel können wir die SVERWEIS-Funktion anwenden, um den ersten übereinstimmenden Wert aus einer Tabelle zu erhalten, 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 erhalten und zurückgeben?
- SVERWEIS und Rückgabe mehrerer übereinstimmender Werte auf einmal in Google Sheets
- Die normale SVERWEIS-Funktion in Google Sheets kann Ihnen helfen, den ersten übereinstimmenden Wert basierend auf einem gegebenen Datensatz zu finden und zurückzugeben. Aber manchmal müssen Sie möglicherweise SVERWEIS verwenden und alle übereinstimmenden Werte wie im folgenden Screenshot gezeigt zurückgeben. Haben Sie gute und einfache Möglichkeiten, diese Aufgabe in Google Sheets zu lösen?
- SVERWEIS und Rückgabe mehrerer Werte aus einer Dropdown-Liste
- In Excel, wie könnten Sie SVERWEIS verwenden und 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, wie im folgenden Screenshot gezeigt. In diesem Artikel werde ich die Lösung Schritt für Schritt vorstellen.
- SVERWEIS und Rückgabe mehrerer Werte vertikal in Excel
- 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 Sie SVERWEIS verwenden und alle übereinstimmenden Werte vertikal, horizontal oder in eine einzige Zelle zurückgeben.
- SVERWEIS und Rückgabe übereinstimmender Daten zwischen zwei Werten in Excel
- In Excel können wir die normale SVERWEIS-Funktion anwenden, um den entsprechenden Wert basierend auf einem gegebenen Datensatz zu erhalten. Aber manchmal möchten wir SVERWEIS verwenden und den übereinstimmenden Wert zwischen zwei Werten zurückgeben, wie im folgenden Screenshot gezeigt. Wie könnten Sie diese Aufgabe in Excel bewältigen?
Beste Büroproduktivitätswerkzeuge
Verbessern Sie Ihre Excel-Fähigkeiten mit Kutools für Excel und erleben Sie Effizienz wie nie zuvor. Kutools für Excel bietet über300 erweiterte Funktionen zur Steigerung der Produktivität und Zeitersparnis. Klicken Sie hier, um die Funktion zu erhalten, die Sie am meisten benötigen...
Office Tab bringt eine Registerkartenoberfläche zu Office und macht Ihre Arbeit viel einfacher
- Aktivieren Sie die Bearbeitung und das Lesen mit Registerkarten in Word, Excel, PowerPoint, Publisher, Access, Visio und Project.
- Öffnen und erstellen Sie mehrere Dokumente in neuen Registerkarten desselben Fensters, anstatt in neuen Fenstern.
- Steigert Ihre Produktivität um50 % und reduziert täglich hunderte von Mausklicks für Sie!