Lange Zeit war es mit Excel ziemlich kniffelig, dynamische, voneinander abhängige Dropdown-Listen zu erstellen. Was damit gemeint ist: Man wählt in der ersten Dropdown-Liste einen Eintrag aus, und die zweite Dropdown-Liste bietet dann nur noch dazu passende Einträge an.
Seit es in Excel jedoch die dynamischen Array-Funktionen gibt, bieten sich hierfür ganz neue und ganz einfache Möglichkeiten. Wenn du also Excel 2021 oder neuer im Einsatz hast, dann funktioniert das auch für dich.
Und so geht’s:
Beispieldatei herunterladen
Abhängige Dropdownlisten bisher
Es gab immer schon verschiedene Ansätze, um voneinander abhängige Dropdownlisten in Excel zu realisieren. Dazu benötigte man entweder ein Konstrukt aus definierten Namen und der INDIREKT-Funktion, manchmal kam auch die etwas sperrige BEREICH.VERSCHIEBEN-Funktion zum Einsatz.
Ich habe vor vielen Jahren dazu zwei Artikel veröffentlicht:
Dynamische Drop-Down-Listen (Teil 1)
Dynamische Drop-Down-Listen (Teil 2)
Wenn du also noch Excel 2019 oder älter benutzt, dann sind die dort vorgestellten Techniken immer noch relevant.
Solltest du jedoch schon Excel 2021, 2024 oder Microsoft 365 nutzen, dann wird es jetzt interessant.
Abhängige Dropdownlisten mit dynamischen Array-Funktionen
Für mein Beispiel habe ich eine Liste mit Automarken, Modellen und Modellvarianten vorbereitet. Es handelt sich dabei um eine formatierte („intelligente“) Tabelle mit dem Namen t_Fahrzeuge.
Aus dieser Liste möchte ich daneben eine beliebige Kombination über drei Dropdownfelder auswählen können:
Fahrzeugliste mit drei Auswahlfeldern
Dabei sollen im zweiten Dropdownfeld nur Modelle der zuvor ausgewählten Marke angeboten werden. Und im dritten Dropdownfeld nur Varianten zu gewählter Marke und Modell. Am Ende soll dann noch der Preis dafür ausgegeben werden.
Wie wird so etwas umgesetzt?
Online-Kurs „Dynamische Array-Funktionen“
Erstes Dropdownfeld für die Marken
In einem Hilfsbereich in Spalte I wird eine Liste aller Marken aus der Fahrzeugtabelle erstellt. Dabei soll folgendes gelten:
Die Liste soll automatisch wachsen, wenn dort neue Marken dazukommen.
Außerdem soll jede Marke nur ein einziges Mal vorkommen.
Und zu guter Letzt soll die Liste natürlich alphabetisch sortiert sein.
Gleich drei Wünsche auf einmal, die mit folgender Formel erfüllt werden:
=SORTIEREN(EINDEUTIG(t_Fahrzeuge[Marke]))
Die Liste mit den Marken
Für die Eingabezelle G2 kann nun das Dropdownfeld eingerichtet werden. Über das Menü „Daten | Datenüberprüfung“ wird das entsprechende Dialogfenster aufgerufen. Im Feld „Zulassen“ wird die Option „Liste“ ausgewählt, in das Feld „Quelle“ kommt der Bezug auf die erste Zelle meiner Markenliste, also $I$2. Da es sich bei der Liste um ein dynamisches Array handelt, wird an die Adresse noch das #-Zeichen angehängt:
=$I$2#
Datenüberprüfung für das erste Dropdownfeld
Sollte dir der Umgang mit dem #-Zeichen noch nicht geläufig sein, empfehle ich dir diesen Blogartikel:
Die Raute: Ein kleines Zeichen mit großer Wirkung!
Zweites Dropdownfeld für die Modelle
Testweise habe ich in G2 die Marke Audi ausgewählt. Im nächsten Schritt wird in Spalte J eine Liste aller dazu passenden Modelle erzeugt. Dafür kommt die FILTER-Funktion zum Einsatz:
=FILTER(t_Fahrzeuge[Modell];t_Fahrzeuge[Marke]=G2;"")
Die Liste der Modelle enthält noch Duplikate
Da auch hier jedes Modell nur einmal vorkommen und die Liste immer sortiert sein soll, wird die Formel wieder um EINDEUTIG und SORTIEREN erweitert:
=SORTIEREN(EINDEUTIG(FILTER(t_Fahrzeuge[Modell];t_Fahrzeuge[Marke]=G2;"")))
Anschließend wird für die Eingabezelle G3 das Dropdownfeld eingerichtet, diesmal mit Bezug auf die Modell-Liste in J2:
Die Liste der Modelle enthält noch Duplikate
Drittes Dropdownfeld für die Varianten
Für das dritte Dropdown in G4 wird noch die Variantenliste benötigt. Auch hier kommt wieder die FILTER-Funktion zum Einsatz, allerdings müssen dabei zwei Filterkriterien berücksichtigt werden: Marke und Modell:
=FILTER(t_Fahrzeuge[Variante];(t_Fahrzeuge[Marke]=G2)*(t_Fahrzeuge[Modell]=G3);"")
FILTER mit zwei Kriterien für die Variantenliste
Zwei oder mehr Filterkriterien, die gleichzeitig zutreffen müssen (= logisches UND), werden über eine Multiplikation miteinander verknüpft. Die einzelnen Kriterien müssen dabei mit Klammern eingeschlossen werden.
Hier spare ich mir der Einfachheit halber die zusätzlichen Funktionen SORTIEREN und EINDEUTIG. Wenn du willst, kannst du sie natürlich trotzdem außen herum packen.
Bleibt noch die Datenprüfung für die Eingabezelle G4:
Datenprüfung für die Varianten
Damit sind die Vorbereitungen abgeschlossen und es kann die gewünschte Fahrzeugkonfiguration ausgewählt werden. Was jetzt noch fehlt, ist der Preis.
Preisbestimmung mit XVERWEIS
Mit Hilfe von XVERWEIS wird der Preis auf Basis der gewählten Optionen ermittelt. Da es drei Kriterien gibt, müssen sowohl die 3 Eingabefelder als auch die 3 Suchspalten in der Fahrzeugtabelle kombiniert werden:
=XVERWEIS(G2&G3&G4;t_Fahrzeuge[Marke]&t_Fahrzeuge[Modell]&t_Fahrzeuge[Variante];t_Fahrzeuge[Preis];"")
Preisermittlung mit XVERWEIS und 3 Kriterien
Und schon haben wir unseren (selbstverständlich fiktiven) Preis für das ausgewählte Fahrzeug.
Es gibt nur einen kleinen Schönheitsfehler:
Was passiert, wenn ich alle drei Dropdownfelder befüllt habe und nachträglich die Marke im ersten Feld ändere?
Der Preis verschwindet natürlich, aber es wird weiterhin das falsche Modell und die falsche Variante angezeigt:
Nachträgliche Änderung der Marke: Modell und Variante bleiben unverändert
Wie lässt sich das korrigieren?
Ungültige Kombinationen ausblenden
Da in den Zellen G3 und G4 ja statische Werte stehen, verschwinden diese nicht automatisch, wenn eine andere Marke ausgewählt wird.
Aber wir können sie zumindest ausblenden und den Anwender auf das Problem aufmerksam machen. Und zwar mit Hilfe einer bedingten Formatierung.
Dabei soll geprüft werden, ob der Wert in Zelle G3 überhaupt noch in der Liste der Modelle in Spalte J enthalten ist. Dies kann beispielsweise über einen XVERWEIS erfolgen. Liefert dieser einen Fehlerwert zurück, dann handelt es sich um ein falsches Modell, welches ausgeblendet werden soll. Die Formel für die Formatierungsregel dafür sieht wie folgt aus:
=ISTFEHLER(XVERWEIS($G$3;$J$2#;$J$2#))
Damit das ungültige Modell ausgeblendet wird, definieren wir noch folgendes benutzerdefiniertes Zahlenformat:
;;;“⚠️“
Formatierungsregel zum Ausblenden des Modells
Für das Warndreieck kannst du dir mit der Tastenkombination Windows+Punkt aus einer langen Liste von Smileys und Symbolen auswählen. Im Ergebnis bedeutet dieses Zahlenformat, dass positive Zahlen, negative Zahlen und Nullwerte komplett unterdrückt werden (dafür stehen die 3 Semikolons). Und anstelle von Texten wird das Warndreieck angezeigt.
Eine analoge Formatierungsregel wird für die Zelle G4 mit der Variante angelegt:
=ISTFEHLER(XVERWEIS($G$4;$K$2#;$K$2#))
Formatierungsregel zum Ausblenden der Variante
Sobald jetzt also Modell und/oder Variante nicht mehr passen, wird ein Warndreieck angezeigt. In der Zelle steht natürlich weiterhin das falsche Modell bzw. die falsche Variante, das sieht man als Anwender aber nur, wenn man oben in die Bearbeitungszeile schaut.
Die drei Dropdownfelder im Einsatz
Fazit
Wie du siehst, lassen sich mit den dynamischen Array-Funktionen FILTER, EINDEUTIG und SORTIEREN sehr einfach voneinander abhängige, dynamische Dropdown-Listen erzeugen. In älteren Excel-Versionen (Excel 2019 und früher) waren solche abhängigen Dropdowns auch schon möglich, aber mit wesentlich mehr Aufwand verbunden.
Und wenn man das Ganze noch geschickt mit bedingter Formatierung kombiniert, lassen sich mit wenig Aufwand sehr anwenderfreundliche Eingabemasken erstellen.
Hast du auch schon Dropdown-Felder mit den dynamischen Array-Funktionen erstellt? Lass uns deine Erfahrungen in den Kommentaren wissen!
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.
Hallo Martin,
die Idee mit der bedingten Formatierung bei den Dropdown-Feldern hatte ich bisher noch nicht – finde ich aber genial 😉
Ich nutze die dynamischen Funktionen sehr oft – mittlerweile auch immer öfter die MAP-Funktion. Ich würde mich freuen, wenn du dazu mal einen Artikel schreiben würdest.
Einen kleinen Fehler hast du noch bei der Formel =I3#, das müsste wie im Text auf I2 referenzieren.
Vielen Dank für deine tolle Unterstützung,
Matthias