Optimaler Bedienkomfort in Excel Tools mit Dropdown-Listen

Posted by sebastian.zang on September 21st, 2012

Es geht nicht nur um Bedienkomfort, sondern auch um Datenqualität: Dropdown-Listen in Ihren Excel Tools. So vermeiden Sie fehlerhafte Dateneingaben und erhöhen zudem den Bedienkomfort für Eingabemasken oder Reportings. Hier: Eine einfache technische Anleitung!

Dieser Artikel wird Ihnen bereit gestellt von Categis GmbH: Leistungsfähige Excel Tools zur Beschleunigung von Geschäftsprozessen

Außerdem möglich: Die Gestaltung von Dropdownmenüs, deren Elemente von einer vorherigen Auswahl abhängt. Wir stellen Ihnen in diesem Blog vor, wie man sie erstellt…

Schauen wir das Szenario an mit einer Liste der Bundesländer (hier: 3 Bundesländer der USA, nämlich Alabama, Alaska und Arizona), einer Liste von Städten in jedem dieser Bundesländerund eine Liste von PLZ zu jeder dieser Städte.

Excel_cell_dropdown_list_1

Die Spalten B, C, und D enthalten die Zellen mit den Dropdownlisten. Wenn Sie Arizona in der Dropdownliste in Spalte Bauswählen, werden nur die Städte in Arizona in der Dropdownliste in der Spalte C angezeigt. Wenn Sie Alaskaauswählen, werden nur die Städte in Alaska in der Dropdownliste in Spalte C angezeigt. Wenn Sie dann eine Stadt in Spalte C auswählen, werden nur die zugehörigen PLZ dieser Stadtin Spalte D angezeigt.Nach Auswahl einer PLZ wird die entsprechende Bevölkerung dieser PLZ in Spalte E angezeigt.

Um solche konditionale Listen zu erstellen, gehen Sie wie folgt vor.

Die Daten eintragen

  1. Zuerst erstellen wir die Tabelle, wie oben gezeigt, in Spalten B, C, Dund E nur mit den Spaltenbeschriftungen in der Reihe 2 und ohne Daten.
  2. Dann erstellen wir einige Listen mit Namen (sie werden in Excel Namensfelder genannt).
    1. Zuerst erstellen Sie eine (vertikal ausgerichtete) Liste der Bundesländer:
      • In Zelle G1 tippen Sie „Bundesland“ ein.
      • In Zelle G2 tippen Sie Alabama ein.
      • In Zelle G3 tippen Sie Alaska ein.
      • In Zelle G4 tippen Sie Arizona ein.

      Wählen Sie diesen Bereich G1:G4 aus, drücken Sie CTRL+SHIFT+F3, ein Dialogfeld erscheint, klicken Sie auf OK. Es wird automatisch ein Namensfeld namens Bundesland erstellt und dieser Namensbereich hat 3 Werte (Alabama, Alaska und Arizona).

      Excel_cell_dropdown_list_2

    2. Danach erstellen wir (waagrecht ausgerichtete)Listen der Städte für jedes Bundesland:
      • In Zellen H2, I2, J2geben Sie je Huntsville, Birmingham und Montgomeryein (Städte in Alabama).
      • In Zellen H3, I3, J3geben Sie je Fairbanks, Anchorage und Juneauein (Städte in Alaska).
      • In Zellen H4, I4, J4 geben Sie je Tucson, Phoenix und Mesa ein (Städte in Arizona).
        Excel_cell_dropdown_list_3

      Selektieren Sie die Städte in Alabama im Bereich G2:J2, drücken Sie CTRL+SHIFT+F3, ein Dialogfeld erscheint, klicken Sie auf OK,nachdem Sie das Ankreuzfeld Linke Spalte ausgewählt haben, da es um eine waagrechte Liste geht. Es wird automatisch ein Namensfeld/-bereich namens Alabama erstellt und dieser Bereich hat 3 Werte (Huntsville, Birminghamund Montgomery). Wiederholen Sie dieses Vorgehen für alle anderen Bundesländer.

      Excel_cell_dropdown_list_4
    3. Jetzt erstellen wir (senkrecht ausgerichtete) Listen der PLZ für jede Stadt, und zwar jeweils in den Spalten G bis O. Nicht alle Listen haben dabei die gleiche Länge: Einige Städte haben weniger PLZ, da sie eine kleinere Bevölkerung haben. Erstellen Sie die genannten Bereiche für alle Städte automatisch, indem Sie den ganzen Bereich auswählen und CTRL+SHIFT+F3 drücken und auf OK klicken.
      Excel_cell_dropdown_list_5

Dropdown-Listen Erstellen

  1. Nach der Erstellung der Listen und Definition der Namen wie im Schritt 2 sind wir jetzt bereit, die Dropdownlisten zu erstellen.
    1. Zuerst machen wir eine Zellendropdownliste für das Bundesland.
      • Selektieren Sie die Zelle B3
      • Wählen Sie Daten ->Datentools-Gruppe ->Datenüberprüfung
      • Geben Sie die Werte ein wie unten im Screenshot abgebildetund klicken Sie auf OK.
        Excel_cell_dropdown_list_6
      • Es wird jetzt die Zellendropdownliste in B3 erstellt. Ziehen Sie den Inhalt der Zelle nach unten von B3 bis B7 (oder in beliebig viele Zeilen).
    2. Nun erstellen wir die erste konditionale Zellendropdownliste für Stadt.
      • Wählen Sie die Zelle C3 aus
      • Gehen Sie zu Daten ->Datentools-Gruppe->Datenüberprüfung
      • Geben Sie die Werte ein wie untn im Screenshot gezeigt und klicken Sie auf OK. Achten Sie dabei auf die Verwendung der INDIREKT-Funktion.
        Excel_cell_dropdown_list_7
      • Es wird die konditionale Zellendropdownliste in C3 erstellt. Ziehen Sie den Inhalt der Zelle nach unten von C3 bis C7, um diese konditionale Auswahlliste in die übrigen Zellen in Spalte C zu kopieren [und damit die gleiche Dropdownliste in allen diesen Zellen zu erstellen].Wenn Sie nun ein Bundesland in Spalte B auswählen, wird das Feld in Spalte C nur die Städte für dieses Bundesland anzeigen.
    3. Wiederholen Sie das o.g. Vorgehen mit der Zelle D3, um die zweite konditionale Zellendropdownliste für die PLZ zu erstellen. Dieses Mal verweist die INDIREKT-Formel auf die Spalte C: „=INDIREKT($C3)“.
    4. Wir sind mit der Erstellung der Dropdownlisten fertig.
  2. Um dieses Beispiel abzurunden, habe ich eine letzte Spalte Bevölkerung hinzugefügt; dort wird eine SVERWEIS-Formel eingesetzt, um die Bevölkerung für die ausgewählte PLZ in Spalte D anzuzeigen.
    Excel_cell_dropdown_list_8
    Excel_cell_dropdown_list_9

Mehr…

Letzter Hinweis: Wenn Ihre Daten dynamisch sind (nehmen wir beispielsweise an, dass sich die Anzahl der Städte und/oderder Bundesländer in der Datenbank dynamisch verändert, aus der Sie diese Daten importieren),ist es möglich, diese genannten Namensbereiche für diese Dropdownmenüs automatisch zu aktualisieren. Sie benötigen dafür nur einen minimalen Code – wir nennen das Microtools. Werfen Sie außerdem einen Blick auf unsere Hauptwebseite, wo Sie erfahren, wie ein Team erfahrener Excel-Spezialisten Sie bei Ihren Projekten ON-Demand unterstützen können: Excel Programmierer für anspruchsvolle Business Anwendungen

.



Leave a Comment

Comments

Post a Comment

*