Dropdown- einer Dropdown-Liste erstellen (abhängige Dropdown)

Über normale Dropdown-Listen haben wir bereits in diesem Beitrag gesprochen. Nun gehen wir einen Schritt weiter: Anhand des ersten selektierten Kriteriums soll eine zweite, abhängige Dropdown-Liste verfügbar sein. Konkret: Wählen wir "Essen" aus, sollen beispielsweise "Ravioli" und "Pizza" zur Auswahl stehen - bei "Trinken" soll "Coca Cola" und "Rotwein" aufgeführt werden.

Als erstes erstellen wir eine ganz normale Dropdown-Liste (wie im oben verlinkten Beitrag erklärt) mit der Auswahl "Essen" und "Trinken". Benennen wir diesen Bereich "Typ", um später in der "Datenüberprüfung" diesen Namen als Liste aufrufen zu können:

D1Typ

Als nächstes definieren wir die nächsten Namen: "Essen" und "Trinken". Dies sind unsere jeweiligen Auswahlkriterien:

D2EssenTrinken

Beachte, dass ich lediglich den Bereich "C3:C6" auswähle und der Name ("Essen" oder "Trinken") exakt mit dem jeweiligen Auswahlkriterium der Liste "Typ" übereinstimmen muss! Nun ist sämtliche Vorarbeit geleistet - gehen wir weiter zu den effektiven Dropdown-Listen: Sobald Du eine beliebigen Zelle selektiert hast, gehe auf "Daten" → "Datenüberprüfung" und selektiere in der Auswahl "Liste" und gebe unterhalb "=Typ" ein:

D1Liste

Jetzt wirst Du in Deiner Zeile entweder "Essen" oder "Trinken" auswählen können. Möchtest Du nun nebenan die abhängige Dropdown-Liste erstellen, selektierst Du diese eine Zelle und gehst wiederum auf "Daten" → "Datenüberprüfung". Auch hier selektierst Du "Liste", gibst aber bei "Quelle:" eine Formel ein, welche sich auf Deine erste Dropdown-Liste bezieht. Die Formel heisst "=INDIREKT":

D2Liste

Beachte zudem, dass wir keinen Bezug absolut ($) gesetzt haben - folglich kannst Du die Zelle im Anschluss einfach kopieren und wirst immer den relativen Bezug zu der Ausgangs-Dropdown bewahren.

Weitere Anwendungsmöglichkeiten dieser abhängigen Dropdown-Listen sind beispielsweise Anmeldungen zu einem gewissen Datum, wo mehrere Zeiten zur Verfügung stehen (von 10:00 - 11:00 oder 14:00 - 15:00) sowie verschiedene Produkte in mehreren Ausführungen (Schuh X; Grösse 44 oder 45).

Bereich eines Namens automatisch erweitern

Wir haben uns bereits den Namensmanager und die Dropdown-Listen angeschaut. Wenn Du zu der Kriterienliste weitere Begriffe hinzufügst, wird der Bereich Deines Namens allerdings nicht automatisch erweitert - Du musst via Namensmanager die letzte Zelle manuell angeben, respektive den effektiven Bereich neu selektieren. Mit der Formel "BEREICH.VERSCHIEBEN" kann dieser Schritt automatisiert werden. Excel sagt uns folgendes über diese Formel:

Gibt einen Bezug zurück, der gegenüber dem angegebenen Bezug versetzt ist.

Man kann damit nicht nur den Bereich "A1:A3" nach "A3:A5" verschieben, sondern auch auf "A1:A5" erweitern - und genau das wollen wir in diesem Beitrag thematisieren. Schauen wir uns einmal die Syntax der Formel an:

BEREICH.VERSCHIEBEN(Bezug; Zeilen; Spalten; [Höhe]; [Breite])

Wir beginnen also mit einem Bezug - einer einzelnen Zelle (A1). "Zeilen" und "Spalten" stehen jeweils für eine Verschiebung um X Zellen nach unten respektive nach rechts. "[Höhe]" und "[Breite]" hingegen lässt uns einen Bereich nach unten oder nach rechts erweitern. Mithilfe der Formel "ANZAHL2" als "Höhe" kannst Du zählen, wie viele Argumente in einer Spalte stehen und so die Anzahl Zellen Deines Bereiches ermitteln. Die Formel lautet demnach:

=BEREICH.VERSCHIEBEN($A$1;;;ANZAHL2(A:A);)

Wir setzen A1 absolut ($), weil diese Formel als Namen definiert werden soll. Der Name ist in einem Tabellenblatt oder einer gesamten Excel-Arbeitsmappe vorhanden. Ohne die Dollarzeichen würde sich deshalb in anderen Zellen die Referenz automatisch verschoben werden und nicht an A1 festhalten. Beachte anschliessend die nacheinanderfolgenden Semikolon (;) - wir geben weder bei "Zeilen" noch bei "Spalten" etwas ein - die ANZAHL2-Formel ermittelt die Höhe unseres Bereiches anhand sämtlicher nichtleerer Zellen in Spalte A.

Die obengenannte Formel musst Du bei Deinem Namen als Bezug eingeben, um die Kriterien nachher in Deiner Dropdown-Liste aufzuführen (siehe "Namensmanager" und "Dropdown-Liste"). Klicke hierfür auf den Menüpunkt "Formeln" → "Namen definieren". Der Bezug "$A$1" wird Dir automatisch mit dem Namen des entsprechenden Tabellenblattes ergänzt (bspw. Tabelle1!$A$1). Bitte beachte deshalb folgendes, wenn Du auf andere Tabellenblätter Bezug nehmen musst: Falls Du Deine Kriterien zum Beispiel im separaten Tabellenblatt "Parameter" hast, heissen Deine Bezüge "Parameter!$A$1" und "Parameter!A:A".

Wenn wir nun ein neues Kriterium zu unserer Liste hinzufügen, wird dies automatisch zum Namen und folglich in die Dropdown-Liste mitaufgenommen.

Dropdown-Liste erstellen

Stell Dir vor, Du erstellst ein Formular zur Erfassung von Privatkunden-Daten. Da möglicherweise nicht Du selbst die Daten erfassen wirst, werden die Angaben bereits im Feld "Geschlecht" oder "Anrede" unterschiedlich ausgefüllt. Der Eine schreibt "weiblich", der Andere "w". Solche Ungereimtheiten und Datenbank-Inkonsistenzen kann man im Voraus ausschliessen, indem man eine Dropdown-Liste erstellt - also eine Auswahlliste mit möglichen Kriterien.

Es eignet sich hierzu, ein separates Tabellenblatt zu erstellen, wo Du sämtliche möglichen Auswahlkriterien erfasst. Ich nenne dieses Blatt "Parameter". Für das Feld "Geschlecht" schreibst Du beispielsweise in die Zelle A1 "weiblich" und in die Zelle A2 "männlich". Um eine Dropdown-Liste zu erstellen, markierst Du zuerst die gewünschten Zellen, bei denen Du eine Auswahlliste erstellt haben möchtest. Danach gehst Du über das Menüband auf "Daten" → "Datenüberprüfung". Im nun angezeigten Fenster wählst Du folgendes aus:

Dropdown1

Bei "Zulassen" wollen wir eine "Liste" und die "Quelle" sind die beiden Kriterien aus dem Tabellenblatt "Parameter". Danach klickst Du auf OK und kannst Deine Dropdown-Liste aufklappen:

Dropdown2

Wer nun beispielsweise "w" eingeben will, wird eine Fehlermeldung erhalten - diese Eingabe ist nicht mehr zugelassen. Die Fehlermeldung kannst Du via "Daten" → "Datenüberprüfung" im Reiter "Fehlermeldung" übrigens auch noch anpassen.

Verknüpfung zum Namensmanager

In einem früheren Beitrag habe ich Dir den Namensmanager vorgestellt (hier geht's zum Beitrag). Du kannst Deinen beiden Kriterien (weiblich; männlich) selbstverständlich einen Namen zuteilen (Geschlecht) und dann bei Quelle direkt "=Geschlecht" eingeben - so wird ebenfalls zu Deinen beiden Kriterien referenziert! ... und in diesem Beitrag kannst Du nachschlagen, wie man Dropdown-Listen voneinander abhängig macht.