Office: Excel - (abhängige) DropDown-Menüs

Hallo!

(Im Anhang befindet sich eine Beispieldatei, auf die sich die Erklärungen dieses Tutorials beziehen.)

Eine Frage, die immer wieder im Excel-Forum auftaucht, ist die folgende:
"Wie kann ich zwei voneinander abhängige DropDowns erstellen?"
(Der Begriff "DropDown" wird häufig auch "PullDown", "Auswahlmenü", o.ä. genannt.)

Oder ausführlicher: In einem ersten DropDown (im weiteren kürze ich dies mit "DD" ab) wird eine "Vorauswahl" getroffen, und die Einträge in einem zweiten DropDown sollen sich entsprechend dieser Vorauswahl anpassen.
In meinem Beispiel kann man im ersten DD einen Kontinent wählen; im zweiten DD sollen die zugehörigen Länder/Staaten angeboten werden (und zwar NUR diese!).

Die Erstellung des ersten DD ist recht trivial, da dieses ja statisch ist, weil es eben genau fünf Kontinente gibt (keine Spitzfindigkeiten wegen der Antarktis, bitte...).
Die Kontinente stehen in A1:E1, also ist die Erstellung des DD ein Kinderspiel:

1. Markiere H2.
2. Wähle Menü Daten > Gültigkeit (in Excel07: Ribbon Daten > Datentools > Datenüberprüfung)
3. Unter "Zulassen" wählst du den Eintrag "Liste".
4. Unter "Quelle" gibst du den Zellbereich an, in dem die Daten stehen, also "=A1:E1" (ohne Gänsefüßchen!), alternativ kannst du die Daten auch einfach mit der Maus markieren.
5. Klicke "OK" und alles sollte passen.

Nun geht es aber an die Erstellung des zweiten DD, welches sich ja auf das erste DD beziehen soll.
Wenn im ersten DD z.B. "Europa" ausgewählt wurde, dann sollen auch nur europäische Länder/Staaten auswählbar sein; alles andere macht schließlich keinen Sinn...


Erste/einfache Variante (statisch; meist auch ausreichend, bzw. zutreffend):

(Die Funktion INDEX(Bereich;Zeile;Spalte) ist in der Excel-Hilfe dokumentiert. Ebenso die Funktion VERGLEICH und alle weiteren Funktionen, die hier verwendet werden.)

1. Markiere I2.
2. Wähle Menü Daten > Gültigkeit
3. Unter "Zulassen" wählst du den Eintrag "Liste".
4. Unter "Quelle" gibst du folgende Formel an:
=INDEX(A2:E9;;VERGLEICH(H2;A1:E1;))
5. Klicke OK.

Warum funktioniert das?
Die verfügbaren Länder/Staaten befinden sich im Zellbereich A2:E9.
Die Funktion, die bei der Gültigkeit/Datenüberprüfung angegeben ist
=INDEX(A2:E9;;VERGLEICH(H2;A1:E1;))
liefert dir nun den passenden Bereich.
Warum das?
Der Zeilenbereich der INDEX-Funktion wurde freigelassen (deshalb das doppelte Semikolon), daher werden alle Zeilen des Bereichs berücksichtigt.
Die passende Spalte ermittelt die Funktion VERGLEICH.

Als Beispiel (da ich die erbärmliche Excel-Hilfe kenne...):
In H2 steht "Afrika".
VERGLEICH(H2;A1:E1;) gibt als Ergebnis "3", da "Afrika" in der dritten Spalte des angegebenen Bereichs steht.
Also liefert INDEX(A2:E9;;3) ein Array mit allen Werten aus der dritten Spalte des Bereichs A2:E9, also genau das, was du gerne hättest (C2:C9), und damit auch genau das, was dein DD anzeigen/anbieten soll.

Soweit zu dieser Variante...


Manchmal (wie auch in diesem Beispiel) haben die unterschiedlichen Bereiche auch unterschiedliche Längen; so sind z.B. bei "Europa" sechs Länder/Staaten aufgeführt, bei „Australien“ aber nur zwei.
Wie du sicher schon bemerkt hast, haben alle DDs der ersten Variante die gleiche Länge, nämlich 8, weil der Bereich A2:E9 genau 8 Zeilen umfasst.
Aber warum sollen im DD irgendwelche leeren(!) Einträge aufgeführt werden? Die Auswahl dieser leeren Einträge macht ja schließlich keinen Sinn.
Kann man das umgehen?
Ja!
In diesem Fall ist es leider etwas aufwändiger, da Excel offensichtlich seine eigenen Regeln hat, in welcher Reihenfolge es gewisse (vom Benutzer festgelegte) Definitionen abfragt...


Zweite/dynamische Variante:

Wie du siehst, werden auch im Fall "Australien" acht Auswahlmöglichkeiten angeboten, obwohl praktisch nur zwei Länder/Staaten zur Auswahl stehen.
Schöner wäre es daher, die Länge des DD auf die tatsächlich zur Verfügung stehenden Länder/Staaten anzupassen.

Um dies zu erreichen, musst du für jeden Kontinent einen Namen definieren.
Das ist einfach, wenn auch mit einiger (dafür aber ein(!)maligen) Tipparbeit verbunden:

1. Wähle Menü Einfügen > Namen > Definieren (in Excel07: Ribbon Formeln > Definierte Namen > Namen definieren)
2. Unter "Name" trägst du "Europa" ein.
3. Unter "Bezieht sich auf" trägst du diese Formel ein:
=BEREICH.VERSCHIEBEN($A$2;;;ANZAHL2($A:$A)-1)
(weitere Informationen zum Thema "dynamische Bereichsnamen" findest du hier)
4. Klicke "Hinzufügen"

5. Unter "Name" trägst du "Asien" ein.
6. Unter "Bezieht sich auf" trägst du diese Formel ein:
=BEREICH.VERSCHIEBEN($B$2;;;ANZAHL2($B:$B)-1)
7. Klicke "Hinzufügen" und fahre in analoger Weise fort bis "Australien", wobei du immer den Spaltenindex anpassen musst (von B auf C, von C auf D und von D auf E).

8. Klicke "OK".

9. Markiere I3.
10. Wähle Menü Daten > Gültigkeit
11. Unter "Zulassen" gibst du wieder "Liste" ein.
12. Unter "Quelle" gibst du jetzt diese Formel ein:
=WAHL(VERGLEICH(H2;$A$1:$E$1;);Europa;Asien;Afrika;Amerika;Australien)
13. Klicke "OK".

Was macht denn nun wieder diese WAHL(VERGLEICH(...-Formel?

VERGLEICH(...) liefert (wie oben auch schon) den Spaltenindex vom Wert in H2.
Steht z.B. in H2 "Afrika", dann liefert VERGLEICH(...) den Wert 3.

WAHL(3;A;B;C;D;E;F;G;H;...)
liefert den Bereich, der an dritter Stelle steht, hier also C. (Bis zu 30 Argumente sind möglich, und das sollte in den meisten Fällen auch reichen.)

Nun sollte es genauso aussehen und funktionieren, wie in der angehängten Beispieldatei.
Wenn du die Liste(n) manuell erweiterst, passen sich die Namen sogar an. Allerdings solltest du Leerzeilen vermeiden, sonst müssen noch weitere Abfragen eingebaut werden.

Das tut es nicht???
Dann melde dich bitte im Excel-Forum mit einem Hinweis auf dieses Tutorial!

 

Hallo,

es gibt einige Varianten der "Abhängigen Drop Down Menüs", in denen eine NAMENsvergabe erforderlich ist für jeden Bereich.
Manchmal wird die Ansprache des abhängigen DropDown gelöst über BEREICH.VERSCHIEBEN() oder mittels INDIREKT().
Beide Varianten haben den Nachteil, dass sie volatil sind, also jedesmal neu berechnet werden müssen oder berechnet werden.

Hier ist eine Variante, wo dies entfallen kann, wenn der Aufbau der Ober- und Unterkategorietabelle excelkonform erfolgt.

Vorgabe: Blatt "Stammdaten"
In Spalte A werden die Hauptkategorien erfasst, welche "automatisch" in Zeile eins eingetragen werden.
Für Spalte A muss ein NAMEN vergeben werden. Wie man dies tut, wird weiter unten erläutert.
Im Bsp. ist es "Art" mit der hinterlegten Formel:
=Stammdaten!$A$2:INDEX(Stammdaten!$A:$A;ANZAHL2(Stammdaten!$A:$A))

Nun muss nur noch für die Abhängigkeit des zweiten Menüs, folgende Formel vergeben werden (für beliebigen NAMEN. Hier im Bsp. "Liste")
=INDEX(Stammdaten!$B$1:$T$20;2;VERGLEICH(INDEX(Erfassung!$D:$D;ZEILE());Stammdaten!$B$1:$AE$1;0)):INDEX(Stammdaten!$B$1:$AE$20;20;VERGLEICH(INDEX(Erfassung!$D:$D;ZEILE());Stammdaten!$B$1:$T$1;0))

Somit muss im Blatt "Erfassung" nur noch der NAMEN, in der jeweiligen Spalte in der DATEN>Gültigkeit eingetragen und nach unten kopiert werden. Fertsch!

Hoffe, Ihr seht wie ich es meine und bei Fragen, fragen!

___________________________________________________________________________________
NAMEN einfügen:
bis Excel2007

im Menüband auf
"Einfügen"-
Namen-
Definieren gehen,

dann unter "Namen in dieser Arbeitsmappe" den Namen vergeben...z.B. wie oben genannt "Art" (ohne Gänsetappsen"") und unter "Bezieht sich auf" dann halt die Formel reinkopieren oder eingeben :) und Enter drücken oder auf "Hinzufügen" klicken.

ab Excel2007

im Reiter "Formeln" auf "Namen definieren" klicken,
im Feld "Name:" halt den Namen einegeben ("Art", ebenfalls ohne Gänsetappsen""),
und unter "bezieht sich auf:" halt wieder die Formel einfügen.

Die Besonderheit ab XL07 ist vor allem, dass der Name nicht nur für die gesamte Arbeitsmappe gilt, sondern man(n, Frau auch) kann es nur auf ein Blatt beschränken.