Kapitel 17 · 17.17
Alle Produkte eines Kunden in einer Zelle, durch Kommas getrennt
Eine Bestellliste enthält eine Zeile pro Produkt. Für einen Lieferschein, ein Mailing oder eine Übersicht möchten Sie dagegen eine Zeile pro Kunde: "Hotel De Haven: Krentenbollen, Croissants, Tompoucen". Früher ging das nur mit einem Makro. Mit TEXTVERKETTEN und FILTER genügt eine einzige Formel.
Als Beispiel nehmen wir wieder das Arbeitsblatt Bestellingen der Bäckerei Bakkerij De Korenaar: in B der Kunde und in C das Produkt, von Zeile 2 bis einschließlich 13.
Hinweis: Auf Excel-Websites finden Sie auch die Variante mit WENN statt FILTER: =TEXTVERKETTEN(", ";1;WENN(Bestellingen!B2:B13=C2;Bestellingen!C2:C13;"")). Diese liefert in Euro-Office mit einfachem Enter eine leere Zelle. Mit Strg+Umschalt+Enter funktioniert sie zwar, doch dann bleiben doppelte Produkte stehen. FILTER mit EINDEUTIG ist eleganter.
Schritt 1: Jeder Kunde einmal
Erstellen Sie eine Übersicht auf einem anderen Arbeitsblatt, zum Beispiel mit den Kunden in Spalte C. Geben Sie in C2 =SORTIEREN(EINDEUTIG(Bestellingen!B2:B13)) ein. Sie erhalten die Kunden alphabetisch untereinander (siehe Listen, die sich selbst aktualisieren, mit FILTER, EINDEUTIG und SORTIEREN).
Schritt 2: Die Produkte pro Kunde
Markieren Sie E2 bis einschließlich zur Zeile des letzten Kunden (hier E2:E5), geben Sie Folgendes ein:
=TEXTVERKETTEN(", ";1;EINDEUTIG(FILTER(Bestellingen!$C$2:$C$13;Bestellingen!$B$2:$B$13=C2)))
und drücken Sie Strg+Enter, damit die Formel neben jedem Kunden erscheint.
- FILTER holt alle Produkte des Kunden in C2.
- EINDEUTIG sorgt dafür, dass ein Produkt, das der Kunde mehrfach bestellt hat, nur einmal genannt wird. Möchten Sie Wiederholungen sehen, lassen Sie EINDEUTIG einfach weg.
- TEXTVERKETTEN fügt sie aneinander. Das erste Argument ist das Trennzeichen (Komma und Leerzeichen), das zweite gibt an, ob leere Zellen übersprungen werden sollen: 1 bedeutet ja.
Für Hotel De Haven steht dann dort "Krentenbollen, Croissants, Tompoucen".

Hinweis: Geben Sie als zweites Argument 1 oder TRUE ein, nicht WAHR. Euro-Office erkennt WAHR ohne Klammern nicht und liefert dann #NAME? (siehe Formeln aus Excel übernehmen: deutsche Funktionsnamen und das Semikolon). WAHR() mit Klammern funktioniert dagegen.
Varianten
- Jedes Produkt in einer eigenen Zeile innerhalb der Zelle: Verwenden Sie ZEICHEN(10) als Trennzeichen, =TEXTVERKETTEN(ZEICHEN(10);TRUE;...), und aktivieren Sie Zeilenumbruch auf der Registerkarte Startseite. Praktisch für einen Lieferschein.
- Mit Mengen, etwa "2x Appeltaart": Legen Sie in der Bestellliste eine Hilfsspalte mit =F2&"x "&C2 an (mit der Menge in F) und verketten Sie diese Spalte.
Fallstricke
- Hat ein Kunde sehr viele Produkte, wird eine einzelne Zelle unübersichtlich. Listen Sie sie dann mit FILTER untereinander auf (siehe Listen, die sich selbst aktualisieren, mit FILTER, EINDEUTIG und SORTIEREN).
- Ein Kunde ohne Produkte führt bei FILTER zu einem Fehlerwert. Fangen Sie diesen mit WENNFEHLER(...;"") ab.
- Ein Kollege mit Excel 2016 oder älter sieht #NAME?. Wandeln Sie das Ergebnis zuerst in Werte um, wenn Sie die Datei weiterleiten.
