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

Helfe beim Thema Excel - (abhängige) DropDown-Menüs in Microsoft Excel Tutorials um das Problem gemeinsam zu lösen; Hallo! (Im Anhang befindet sich eine Beispieldatei, auf die sich die Erklärungen dieses Tutorials beziehen.) Eine Frage, die immer wieder im... Dieses Thema im Forum "Microsoft Excel Tutorials" wurde erstellt von schatzi, 26. März 2008.

  1. schatzi Super-Moderator

    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???Excel - (abhängige) DropDown-Menüs [​IMG]
    Dann melde dich bitte im Excel-Forum mit einem Hinweis auf dieses Tutorial!
     
    Zuletzt von einem Moderator bearbeitet: 9. Dezember 2020
    schatzi, 26. März 2008
    #1
  2. silex1
    silex1 Super-Moderator
    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.
     
    Zuletzt bearbeitet: 17. Mai 2013
    silex1, 22. März 2012
    #2
Thema:

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

Die Seite wird geladen...
  1. Excel - (abhängige) DropDown-Menüs - Similar Threads - Excel abhängige DropDown

  2. Excel 2016 Daten vom Datum abhängig anzeigen

    in Microsoft Excel Hilfe
    Excel 2016 Daten vom Datum abhängig anzeigen: Liebe Community, nach einer ersten Tabelle mit viel Unterstützung von Klaus-Dieter, vielen Dank hierfür noch mal, habe ich die Tabelle umstrukturiert und für andere Zwecke entfremdet....
  3. Wiedergabe abhängig des Datums, auch mehrere Einträge pro Datum möglich

    in Microsoft Excel Hilfe
    Wiedergabe abhängig des Datums, auch mehrere Einträge pro Datum möglich: Hallo Leute, ich versuche mich derzeit an einer automatisierten Tabelle. Leider bin ich ein Excelanfänger und komme nicht mehr weiter. Mein Ziel ist es, in einer Tabelle (T1) Listen mit...
  4. Excel Dropdown mit abhängigen Werten

    in Microsoft Excel Hilfe
    Excel Dropdown mit abhängigen Werten: Hallo liebes Forum, über eine Google-Recherche und die einschlägigen Excel-Forum bin ich in meiner Problemstellung leider nicht weiter gekommen. Es geht um Folgendes: Ich habe eine Tabelle...
  5. Frage zu: Tutorial "Excel - (abhängige) DropDown-Menüs"

    in Microsoft Excel Hilfe
    Frage zu: Tutorial "Excel - (abhängige) DropDown-Menüs": Hallo Excel Fans, viele Dank für das tolle Tutorial zum Thema "Excel - (abhängige) DropDown-Menüs" von silex1. Es funktioniert in meinem selbst erstellten Haushaltsbuch hervorragend. Was...
  6. Hintergrund Farbe von Feldern ; abhängige Dropdown Liste verändern

    in Microsoft Excel Hilfe
    Hintergrund Farbe von Feldern ; abhängige Dropdown Liste verändern: Guten Tag zusammen, ich habe folgendes Problem. Zum einen habe ich eine Formel in die Spalte M geschrieben. Diese ist Abhängig von der Auswahl einer abhängigen Dropdown Liste (wurde in anderen...
  7. Excel 2007: Abhängige Dropdowns, obwohl dynamischer Bereich?

    in Microsoft Excel Hilfe
    Excel 2007: Abhängige Dropdowns, obwohl dynamischer Bereich?: Halo an alle, bin neu im Forum und mein Anliegen ist relativ speziell, sodass ich nichts dazu bei der SuFu oder im Web finden konnte. Ich bin Praktikant in einer Berliner Firma und habe ein...
  8. Excel-(abhängige) DropDown-Menüs

    in Microsoft Excel Hilfe
    Excel-(abhängige) DropDown-Menüs: Hallo, folgendes Tutorial https://www.office-hilfe.com/support/threads/excel-abhaengige-dropdown-menues.8039/ ist schon sehr hilfreich für meine Tabelle, ich habe aber noch eine Unklarheit,...
  1. Diese Seite verwendet Cookies, um Inhalte zu personalisieren, diese deiner Erfahrung anzupassen und dich nach der Registrierung angemeldet zu halten.
    Auf dieser Website werden Cookies für die Zugriffsanalyse und Anzeigenmessung verwendet.
    Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.
    Information ausblenden