Vor gar nicht langer Zeit habe ich einen Artikel darüber geschrieben, wie man heutzutage mit modernen Excel-Funktionen dynamische, mehrstufige Dropdown-Liste erstellt.
In dem Beispiel bin ich von 3 einzelnen Eingabezellen ausgegangen.
Wie sieht es jedoch aus, wenn man die abhängigen Dropdowns innerhalb einer ganzen Tabelle benötigt? Also nicht nur in einzelnen Zellen, sondern immer gleich für ganze Spalten?
Genau darum dreht sich der heutige Artikel.
Und so geht’s:
Beispieldatei herunterladen
Beispiel: Ein Produktkonfigurator
Wie auch schon im Artikel mit den abhängigen Dropdowns in einzelnen Zellen dient als Beispiel wieder ein Produkte-Katalog, aus dem bestimmte Fahrzeuge ausgewählt werden sollen.
Hinweis:
Da ich für die im Folgenden vorgestellte Lösung dynamischen Array-Funktionen verwende, benötigst du dafür mindestens Excel 2021 oder neuer (Excel 2024, Microsoft 365).
Online-Kurs „Die neuen Excel-Funktionen, die jeder kennen sollte“
In den Spalten A:C befindet sich eine Eingabetabelle, in der die Dropdown-Felder eingerichtet werden sollen. Hierbei handelt es sich um eine formatierte („intelligente“) Tabelle mit dem Namen t_Produktauswahl, die nach unten hin anwachsen kann.
Die zukünftige Eingabetabelle mit der Produktauswahl
Dabei wird zunächst in der ersten Spalte eine Marke ausgewählt. In Spalte B sollen danach immer nur Modelle angeboten werden, die es für diese Marke gibt. Und die Variante in Spalte C muss wiederum zum gewählten Modell passen.
In den Spalten F:H habe ich den Produkte-Katalog ebenfalls als intelligente Tabelle mit dem Namen t_Katalog angelegt. Hier sind sämtliche Marken, Modell und Varianten gepflegt:
Der Produktkatalog mit allen Marken, Modellen und Varianten
Der Katalog könnte natürlich genauso gut in einem anderen Arbeitsblatt liegen.
Des erste Dropdown: Marke
Zunächst wird eine Liste aller Markennamen aus dem Produkte-Katalog benötigt. Da die Liste überschaubar ist, packe ich sie direkt rechts neben den Produkt-Katalog in Zelle J4:
=SORTIEREN(EINDEUTIG(t_Fahrzeuge[Marke]))
Die Basis für das erste Dropdown-Feld
Sollten später neue Marken im Katalog dazukommen, werden diese in der Formel automatisch berücksichtigt.
In der Eingabetabelle t_Produktauswahl markiere ich nun die Spalte „Marke“ und richte dort über das Menü „Daten | Datenüberprüfung“ das erste Dropdown-Feld ein:
Das Dropdown für die Marke wird eingerichtet
Im Feld „Zulassen“ wähle ich „Liste“, in der Quelle verweise ich auf die Zelle $J$4, gefolgt von dem #-Zeichen. Letzteres sorgt dafür, dass das gesamte dynamische Array angesprochen wird.
Damit wäre der einfachste Teil erledigt 🙂
Das zweite Dropdown: Modell
Jetzt wird es etwas kniffeliger. Da sich die angebotenen Modelle immer auf die ausgewählte Marke in der aktuellen Zeile beziehen sollen, kann ich nicht einfach mit einem einzelnen Array arbeiten.
Vielmehr muss ein eigenes Array für jede einzelne Zeile erstellt werden.
Zur besseren Übersicht mache ich das in einem neuen Arbeitsblatt, das ich „dd_Modelle“ nenne. Dort kommt in Zelle A5 folgende Formel:
=MTRANS(SORTIEREN(EINDEUTIG(FILTER(t_Katalog[Modell];t_Katalog[Marke]=Produktauswahl!A5;""))))
Array-Formel zur Bestimmung der Modelle
Was bedeutet diese Formel und warum ausgerechnet in Zelle A5?
In meiner Eingabetabelle beginnt der erste Eintrag in Zeile 5. Aus Gründen der besseren Nachvollziehbarkeit habe ich die oben genannten Formel daher ebenfalls in Zeile 5 eingeben.
Schauen wir uns die Formel von innen nach außen näher an:
FILTER(t_Katalog[Modell];t_Katalog[Marke]=Produktauswahl!A5;"")
Damit erzeuge ich eine gefilterte Liste aller Modelle, wobei das Filterkriterium das in Zelle A5 in der Produktauswahl eingegebene Modell ist. Mit dem letzten Argument "" wird verhindert, dass ein #CALC!-Fehler ausgegeben wird, wenn noch kein Modell ausgewählt wurde.
Wählt man nun in der Eingabetabelle beispielsweise „Audi“ als Marke aus, liefert die FILTER-Funktion sämtliche Audi-Modelle aus t_Katalog in einer Spalte. Zur Veranschaulichung habe ich diese Formel in G5 eingeben:
So würde die FILTER-Funktion für sich genommen arbeiten
Damit jedes Modell nur ein einziges Mal auftaucht und die Liste auch noch sortiert wird, erweitere ich die Formel entsprechend:
=SORTIEREN(EINDEUTIG(FILTER(t_Katalog[Modell];t_Katalog[Marke]=Produktauswahl!A5;"")))
SORTIEREN und EINDEUTIG verdichten das Ergebnis
Jetzt muss die Ausgabe nur noch gedreht werden, so dass alle Werte in einer einzelnen Zeile stehen. Dazu verwende ich die alte MTRANS-Funktion, danach ergibt sich für die vollständige Formel in A5 folgendes Bild:
=MTRANS(SORTIEREN(EINDEUTIG(FILTER(t_Katalog[Modell];t_Katalog[Marke]=Produktauswahl!A5;""))))
Mit MTRANS wird die spaltenweise Ausgabe in eine Zeile gedreht
Statt MTRANS wäre auch die neue Funktion ZUSPALTE möglich gewesen, die allerdings erst ab Excel 2024 verfügbar ist.
Ändert man nun in der Eingabetabelle in der ersten Zeile die Marke, werden hier auch die entsprechenden Modelle ausgegeben.
Das funktioniert jetzt allerdings nur für die erste Eingabezeile. Damit das auch in den anderen Eingabezeilen klappt, muss die Formel auf Verdacht soweit nach unten kopiert werden, wie Eingabezeilen zu erwarten sind. Das ist zwar nicht sehr elegant, aber es funktioniert (eine wirklich voll-dynamische Formel, die auch nach unten überläuft, wäre mit einer komplexeren Lambda-Funktion möglich, aber das erspare ich dir an dieser Stelle).
Durch das Kopieren passt sich der ursprüngliche Zellbezug von A5 auf A6, A7 usw. an:
Beim Kopieren nach unten passt sich der Zeilenbezug an
Fehlt nur noch das eigentliche Dropdown-Feld.
Das Ziel ist es ja, immer auf das dynamische Array mit den Modellen im Blatt dd_Modelle zuzugreifen, welches sich dort in der jeweils passenden Zeile befindet. Dazu wird die Spalte „Modell“ markiert und eine Datenüberprüfung mit der folgenden Formel eingerichtet:
=dd_Modelle!$A5#
Das zweite Dropdown für das Modell
Achte bei der Formel unbedingt darauf, dass nur die Spalte absolut gesetzt wird ($A), und nicht auch die Zeile 5. Daher $A5 und nicht $A$5. Ansonsten würden für jede Zeile immer nur die gleichen Modelle zur Auswahl angeboten werden.
Das Rautezeichen sorgt wieder dafür, dass das gesamte dynamische Array in der betreffenden Zeile ausgelesen wird.
Das dritte Dropdown: Variante
Auch hierfür habe ich ein neues Blatt „dd_Varianten“ angelegt. Dort kommt in Zelle A5 (ja, wir sind analog zur Tabelle mit der Produktauswahl wieder in der fünften Zeile) folgende Formel:
=MTRANS(SORTIEREN(EINDEUTIG(FILTER(t_Katalog[Variante];(t_Katalog[Marke]=Produktauswahl!A5)*(t_Katalog[Modell]=Produktauswahl!B5);""))))
Die Formel für die Varianten
Das ist fast die gleiche Formel wie für das Dropdown 2 mit den Modellen. Der wesentliche Unterschied ist, dass es in der FILTER-Funktion zwei Filterkriterien gibt:
(t_Katalog[Marke]=Produktauswahl!A5)*(t_Katalog[Modell]=Produktauswahl!B5)
Zur Erklärung:
Wenn in FILTER – wie in unserem Fall – mehrere Kriterien gleichzeitig zutreffen sollen (= logisches UND), müssen die Kriterien in Klammern gesetzt und multipliziert werden.
Sollen hingegen mehrere Kriterien nur wahlweise zutreffen (= logisches ODER), müssten diese stattdessen addiert werden.
Anschließend wird auch diese Formel wieder auf Verdacht so weit nach unten kopiert, wie später Eingabezeilen zu erwarten sind.
Somit können wir das letzte Dropdown-Feld einrichten. Dazu wird in der Eingabetabelle die Spalte „Variante“ markiert und in der Datenüberprüfung folgende Quelle angegeben:
=dd_Varianten!$A5#
Das dritte Dropdown für die Variante
Auch hier wieder auf das Dollarzeichen achten und das #-Symbol am Ende nicht vergessen.
Sofern wir keinen Fehler gemacht haben, sollte jetzt unsere Produktauswahl dynamisch funktionieren.
Die fertige Eingabetabelle im Einsatz
Wie man mit dem Problem umgeht, wenn nachträglich eine Marke oder ein Modell geändert wird und die zuvor eingegebene Kombination ungültig wird, habe ich in diesem Artikel beschrieben:
Abhängige Dropdown-Listen ganz einfach
Stichwort „Bedingte Formatierung“.
Fazit
Ja, damit mehrstufige dynamische Dropdown-Felder in einer ganzen Datentabelle genutzt werden können, bedarf es etwas mehr Aufwand, als es für einzelne Zellen notwendig wäre.
Der größte Schwachpunkt ist, dass für die zweite und dritte Ebene die Formeln manuell und „auf Verdacht“ nach unten kopiert werden müssen. Man muss also immer eine Kröte schlucken:
- Wenn man in den beiden Hilfsblättern die Formeln in zu wenige Zeilen nach unten kopiert, als später in der Eingabetabelle benötigt werden, dann versagen in den betreffenden Zeilen die Dropdowns.
- Sieht man hingegen zu viele Zeilen vor, schlägt sich das eventuell auf die Performance nieder.
Auf keinen Fall sollte man jedoch auf die Idee kommen, die Formeln sicherheitshalber bis ans Ende des Arbeitsblattes zu kopieren. Es sei denn, man Excel unbedingt in die Knie zwingen…
Das A und O in dieser Lösung ist also ein gesunder Kompromiss, denn jeder für sich und seinen Anwendungsfall individuell finden muss. Dann sollte aber auch mehrstufigen Dropdowns in einer Datentabelle nichts mehr im Weg stehen.
P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)
P.P.S. Das Problem sitzt meistens vor dem Computer.