Dropdown-Liste Spezial 24

Mit ein paar kleinen Tricks ist die Pflege von Dropdown-Listen ein Kinderspiel.
 

Auswahllisten oder Dropdown-Listen sind in Excel extrem beliebt. Sie erleichtern dem Anwender die Dateneingabe, verhindern Fehler und lassen deine Tabelle einfach professionell erscheinen.

Es gibt nur ein kleines Ärgernis: Wenn neue Einträge in der Auswahlliste dazukommen sollen, ist meistens Handarbeit angesagt.

Heute zeige ich dir, wie man eine Dropdown-Liste erstellt, die

  • automatisch neue Einträge aufnimmt
  • alphabetisch sortiert ist
  • keine doppelten Einträge enthält

Und das alles (fast) vollautomatisch!

Und so geht’s:

Beginnen wir beim Ende

Ich habe eine kleine Beispieltabelle vorbereitet, die du bei Bedarf hier herunterladen kannst. Sie enthält eine lange Länderliste und ihre Zuordnung zu den Kontinenten:

Das Endergebnis

Das Endergebnis

Nun möchte ich in Zelle E1 eine Dropdown-Liste haben, die mir alle Kontinente aus der Liste anbietet. Und im Feld E2 soll dann die Anzahl der Länder im ausgewählten Kontinent berechnet werden.

Wie bekomme ich nun meine Kontinent-Dropdown-Liste so hin, dass
a) jeder Kontinent nur einmal vorkommt
b) neue Kontinente, die am Ende meiner Länderliste ergänzt werden, automatisch übernommen werden
c) die Liste immer alphabetisch sortiert ist

Die Lösung führt über eine…

…Pivot-Tabelle

Vorbereitungsarbeiten

Auf den ersten Blick nicht ganz naheliegend, bei näherer Betrachtung aber genial. Aber alles der Reihe nach.

Basis unserer Pivot-Tabelle ist die Länderliste. Diese formatiere ich zunächst als “intelligente” Tabelle:

Die "nackte" Ausgangstabelle

Die “nackte” Ausgangstabelle

Da sich die aktive Zelle innerhalb meiner Länderliste befindet, schlägt mir Excel bereits den richtigen Tabellenbereich vor. Wichtig ist nur der Haken “Tabelle hat Überschriften”:

Als Tabelle formatieren

Als Tabelle formatieren

Solange sich die aktive Zelle innerhalb der formatierten Tabelle befindet, wird im Menüband ein neuer Eintrag “Tabellentools” mit der Registerkarte “Entwurf” angezeigt. Hier überschreibe ich den vorgeschlagenen Tabellennamen mit einem etwas aussagefähigeren, wie z.B. “Länderliste”:

Einen sprechenden Namen vergeben

Einen sprechenden Namen vergeben

Einer der großen Vorteile von formatierten Tabellen: Der Bereich, den die Tabelle umfasst, erweitert sich automatisch, sobald man unten neue Einträge anfügt. Somit kann ich über den Namen “Länderliste” im den kompletten Bereich ansprechen.

Die Pivot-Tabelle

Jetzt erstellen wir aus unserer Länderliste eine extrem simple Pivot-Tabelle. Die aktive Zelle sollte sich wieder irgendwo innerhalb der Liste befinden, dann rufen wir im Menüband im Register “Einfügen” die Schaltfläche “PivotTable” auf:

Die Pivot-Tabelle einfügen

Die Pivot-Tabelle einfügen

Als Tabelle/Bereich zeigt mir Excel bereits den Namen “Länderliste” an. Als Ziel für meine Pivot-Tabelle gebe ich eine freie Zelle etwas rechts von meiner Tabelle an, z.B. G2:

Pivot-Tabelle im gleichen Blatt

Pivot-Tabelle im gleichen Blatt

Nun wähle ich aus der Liste der vorhandene Felder “Kontinent” aus. Die standardmäßige Summenzeile entferne ich über einen Rechtsklick in die Pivot-Tabelle. Hier kann man unter “Pivot-Table-Optionen…” in der Registerkarte “Summen & Filter” die beiden Gesamtsummen-Haken entfernen:

Pivot-Tabelle: Die Feldauswahl

Pivot-Tabelle: Die Feldauswahl

Jetzt müssen wir nur noch sicherstellen, dass die Einträge auch immer alphabetisch sortiert sind. Dazu klicken wir einfach auf den Pfeil neben “Zeilenbeschriftungen” und wählen die Sortieroption “Von A bis Z sortieren”:

Alphabetische Sortierung einstellen

Alphabetische Sortierung einstellen

Wie du siehst, nähern wir uns dem Ziel schon: Die Pivot-Tabelle liefert mir eine alphabetisch sortierte, dublettenfreie Liste aller Kontinente.

Damit wir diese aber für unsere Dropdown-Liste verwenden können, müssen wir noch einen Namen definieren.

Dynamische Namen

Die Vergabe eines Namens für eine Zelle oder einen Zellbereich erfolgt über die Registerkarte “Formeln”. Hier gibt es die Schaltfläche “Namen definieren”:

Einen Namen definieren

Einen Namen definieren

Als Name vergebe ich z.B. “Dropdown”. Fehlt noch der Eintrag in “Bezieht sich auf”. Da wir nicht wissen können, wie viele Einträge unsere Pivot-Tabelle in Zukunft haben wird, dürfen wir hier keinen festen Zellenbezug eingeben. Stattdessen berechnen wir die Größe des Zellbereichs dynamisch mit der BEREICH.VERSCHIEBEN-Funktion.

Mehr Dynamik mit einer Formel

Mehr Dynamik mit einer Formel

Die allgemeine Syntax lautet:
=BEREICH.VERSCHIEBEN(Bezug;Zeilen;Spalten;[Höhe];[Breite])

Damit wird also ein gegebener Bezug, also eine Zelladresse um eine bestimmte Anzahl von Zeilen und/oder Spalten “verschoben”, so dass sich ein neuer Bezug ergibt. Dieser kann dann optional auch eine andere Höhe und Breite haben, als der Ausgangsbezug.

In unserem Fall lautet die Funktion also:
=BEREICH.VERSCHIEBEN(Tabelle1!$G$3;0;0;ANZAHL2(Tabelle1!$G:$G)-1)

Die Ausgangszelle G3 (wichtig: mit den Dollarzeichen absolut referenzieren!) ist der erste Eintrag unserer Pivot-Tabelle und das soll auch so bleiben. Daher geben wir für die Parameter Zeilen und Spalten jeweils den Wert 0 (Null) an.
Aber wir wollen eine dynamische Länge der Liste ermitteln (= Parameter “Höhe”). Dies erfolgt über die Funktion ANZAHL2, mit der wir alle Einträge in Spalte G zählen und davon den Wert 1 (für die Kopfzeile) abziehen.

Das funktioniert allerdings nur, wenn in der Spalte G nicht noch irgendwelche anderen Daten vorhanden sind. Gegebenenfalls musst Du die Pivot-Tabelle dann in einer anderen leeren Spalte platzieren.

Die Dropdown-Liste

Jetzt müssen wir nur noch für das Feld E2 die Dropdown-Liste aktivieren. Dazu stellen wir die aktive Zelle in das Feld E1 und klicken in der Registerkarte “Daten” auf die Schaltfläche “Datenüberprüfung”.
Im Feld “Zulassen” wählen wir “Liste” und geben dann im Feld “Quelle” den Namen ein, den wir zuvor definiert haben (Gleichheitszeichen davor nicht vergessen!):

Datenüberprüfung einstellen

Datenüberprüfung einstellen

Wie man sieht, liefert die Dropdown-Liste die aktuellen Werte aus der Pivot-Tabelle:

Die Dropdown-Liste im Einsatz

Die Dropdown-Liste im Einsatz

Wenn du noch tiefer in die Welt der Dropdown-Listen einsteigen möchtest, dann empfehle ich dir mein E-Book:


Dropdown-Leitfaden-mittel
Dropdown-Listen mit Excel. Der große Leitfaden

 

Was passiert nun, wenn wir neue Einträge in unserer Länderliste vornehmen? Ich hänge als einfach mal zwei neue Länder unten an, die natürlich auf einem neuen Kontinent liegen:

Neue Werte in der Länderliste

Neue Werte in der Länderliste

Damit dieser Eintrag nun in der Dropdown-Liste erscheint, muss nur die Pivot-Tabelle aktualisiert werden. Also die aktive Zelle irgendwo in die Pivot-Tabelle stellen und in den PivotTable-Tools die Schaltfläche “Aktualisieren” klicken:

Pivot-Tabelle aktualisieren

Pivot-Tabelle aktualisieren

Und schon erscheint auch in der Dropdown-Liste der neue Eintrag alphabetisch korrekt einsortiert:

Der neue Kontinent erscheint automatisch

Der neue Kontinent erscheint automatisch

Fehlt nur noch in Zelle E2 die Formel, welche die Anzahl der Länder zum gewählten Kontinent liefert. Das erledigen wir mit der ZÄHLENWENN-Funktion:

Strukturierte Verweise in ZÄHLENWENN

Strukturierte Verweise in ZÄHLENWENN

Hier erkennt man auch einen weiteren Vorteil von formatierten Tabellen: Es werden in den Funktionen, die sich darauf beziehen, automatisch sogenannte strukturierte Verweise verwendet. Statt z.B. B2:B200 steht dann ein wesentlich aussagefähigeres “Länderliste[Kontinent]”.

Bonus-Tipp zum Schluss:
Man kann auch einstellen, dass die Pivot-Tabelle (und damit die Dropdown-Liste) zumindest bei jedem neuen Öffnen der Datei aktualisiert wird. Dazu öffnet man die PivotTable-Optionen:

Pivot-Table-Optionen aufrufen

Pivot-Table-Optionen aufrufen

Jetzt noch im Register “Daten” das Häkchen “Aktualisieren beim Öffnen der Datei” setzen:

Automatisch aktualisieren beim Öffnen der Datei

Automatisch aktualisieren beim Öffnen der Datei

Fertig!

Wie du siehst, gibt es auch für Pivot-Tabellen durchaus exotische Einsatzmöglichkeiten.

 

Das könnte dich auch interessieren:

Und immer daran denken: Excel beißt nicht!

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.



Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.

24 Gedanken zu “Dropdown-Liste Spezial

  • Marcel Pfeifer

    Grüß dich Martin,

    Danke für den Tipp mit der Pivottabelle!

    Anstelle der etwas unschönenen (da volatil) Funktion Bereich.Verschieben würde ich die Index-Formel in der Bezugsvariante empfehlen.
    Im Namensmanager bei “Bezieht sich auf”: =INDEX(Tabelle1!G:G;3):INDEX(Tabelle1!G:G;ANZAHL2(Tabelle1!G:G))

    Das Ergbnis ist das gleiche 😉

    Viele Grüße
    Marcel

    • Martin Weiß Autor des Beitrags

      Hallo Marcel,

      danke für den Tipp mit der INDEX-Funktion. Es führen mal wieder viele Wege nach Rom… 🙂

      Schöne Grüße,
      Martin

    • Luschi

      Hallo Marcel,

      ich staune, daß bisher noch Niemanden aufgefallen ist, daß Deine Index-Formeln 2 Zellen zu wenig erfassen und damit in der Gültigkeitsliste dann fehlen.
      Das erste Index kann man weglassen, da auch dies hier sauber funktioniert:
      Tabelle1!$G$3:INDEX(Tabelle1!$G:$G;ANZAHL2(Tabelle1!$G:$G)+ZEILE(Tabelle1!$G$3)-1)
      Zudem ist es ratsam, auch ganze Zeilen/Spalten absolut zu setzen, da es schnell mal passieren kann, daß man sich im Namensmanager vertut und dann die Zeilen/Spalten plötzlich wandern.

      Gruß von Luschi
      aus klein-Paris

  • Frank Lehmann

    Hallo Martin,
    als nicht ganz unerfahrener Excel-Anwender haben mich die meisten “Gurus” mit ihren angeblich tollen Tips meist gelangweilt.
    Endlich habe ich die Webseite gefunden, bei der ich noch was lernen kann.
    Danke

    Frank

  • Jürgen Beer

    Hallo, ich stehe vor folgender Herausforderung: eine Tabelle mit zwei Spalten. In Spalte B wähle ich einen Wert aus einer Drop-Down-Liste aus. Je nach ausgewähltem Wert soll die zeilengleiche Zelle in Spalte A mit einer farblichen Füllung versehen werden (quasi eine bedingte Formatierung, die sich an der Auswahl in Zelle B orientiert). Geht sowas ohne Makro?

    Die Tips sind übrigens alle goldwert. Vielen Dank. Eine super Seite.

    • Martin Weiß Autor des Beitrags

      Hallo Jürgen,

      danke für das Lob.
      Was Dein Problem angeht: Genau dazu sind bedingte Formatierungen da. Man kann Regeln auch in Abhängigkeit von Werten in anderen Zellen definieren. Dabei musst Du als Regeltyp nur den letzten Eintrag “Formel zur Ermittlung der zu formatierenden Zellen verwenden” auswählen und die gewünschte Bedingung/Formel eingeben.
      Alles ohne Makro/VBA.

      Schöne Grüße,
      Martin

  • Mieke Bluhm

    Hallo Martin,
    gibt es in Excel auch eine Möglichkeit für eine Dropdown-Liste aus der ich mehrere Dinge auswählen kann?
    Beispiel: Ich habe eine Liste mit Personen, die verschiedene Instrumente spielen. Die Instrument möchte ich über eine Dropdown-Auswahl wählen. Das Problem: Einige Personen spielen zwei oder drei verschiedene Instrumente…
    Wie kann ich das lösen, ohne weitere Spalten für weitere Instrumente anzulegen? Ich möchte am Ende nämlich auch in der Lage sein zu filtern, z.B. nach allen Personen, die Klarinette spielen und das ist meiner Meinung nach schwierig wenn sich die Instrumente über mehrere Spalten verteilen.

    Vielen Dank in Voraus 🙂
    Mieke

    • Martin Weiß Autor des Beitrags

      Hallo Mieke,

      nein, die Möglichkeit, mehrere Einträge in einem Dropdown-Feld auszuwählen, gibt es nicht. Wenn Du nach dem beschriebenen Fall filtern möchtest, musst Du wohl oder übel auf andere Krücken zurückgreifen, z.B. pro Person und Instrument einen eigenen Datensatz (Zeile) anlegen.

      Schöne Grüße,
      Martin

  • Matthias Meineke

    Hi Martin,
    in einer Stunde lockeren des Lesens quer durch die Seite habe ich mehr sinnvolle Infos erhalten, als in 24 Stunden verschärften Wühlens durch die Excel-Hilfe.
    Weiter so…

  • Olga M.

    Hallo Martin, ich finde die Dropdown Liste Spezial echt klasse..Danke, habe einiges dazu gelernt ..Habe nur noch eine Frage, wie bekomme ich durch eine Privot Liste mit untertitel(verschiede Tätigkeit) in die dropdown liste. also habe verschiede Tätigkeiten und es muss sortiert in die Dropdown Liste. Denke muss den letzen Schritt (Formel) ändern. Statt nur die Ausgewählte Tabelle, noch den Untertitel eingebn. Nur wie? Wäre dir sehr dankbar für die Hilfe.
    Gruss Olga

    • Martin Weiß Autor des Beitrags

      Hallo Olga,

      ich bin mir nicht ganz sicher, ob ich Dich richtig verstanden habe. In meinem Beispiel ist es ja so, dass die Pivot-Tabelle nur die Ländernamen enthält. Je nachdem, wie Deine Tabelle aufgebaut ist, musst Du in der Pivot-Tabelle eben nur das Feld aufnehmen, welches Du für die Dropdown-Liste benötigst, also beispielsweise die Tätigkeiten. Die Formel für den dynamischen Bereich (mit BEREICH.VERSCHIEBEN) bleibt dann grundsätzlich auch gleich.

      Oder habe ich Dich falsch verstanden?

      Schöne Grüße,
      Martin

      • Olga M.

        Danke für schnelle Rückmeldung Martin 🙂 Ja soweit hatte ich es. Ich habe aber 5 Verschiede Tätigkeiten in meiner Tabelle. Wenn ich mir die Privot Tabelle aufbaue , quasi für jede Tätigkeit eine Tabelle , würde es ja gehen.(Dann habe ich aber 5 Tabellen) Aber ich überlege, vielleicht geht es mit einer Tabelle so, dass er mir immer die Tätigkeit raussucht und die Mitarbeiter dann zusammenfügt und Dropdown. Hoffe konnte es richtig beschreiben .
        Viele Grüße
        Olga

      • Olga M.

        Martin, ich überlege. Vielleicht gibt es da bessere Lösung als Privot Tabelle. Erkläre es noch mal kurz. Habe eine mitarbeiter Liste mit Namen und Tätigkeiten(von den Mitarbeiter). Ich baue einen Dienstplan auf. Die Mitarbeiter muss ich in der Tabelle per Dropdown finden. Z.b. von allen Säger sollen die Namen rauskommen. Soll mir aus der Tabelle alle Säger Namen wiedergeben. Weiß nicht wie ich es hinkriege ohne extra Tabellen usw. Wäre dir wirklich dankbar, wenn ich hier weiterkomme.
        Gruß
        Olga

  • Uwe Demmer

    Hallo
    super Seite mit tollen Tipps

    Leider hab ich trotzdem noch eine Herausforderung, wo ich hänge.

    Als Datengültikeit gibt es eine Tabelle mit Belegnummern. (Benannt als Belege)

    Auf einem anderen Blatt möchte ich über Dropdown / Gültigkeiten die Einträge aus Belege angezeigt bekommen.
    Das ist ja kein Problem.

    … Liste und bei Quelle =Belege rein

    Aber die Werte, die in der Spalte bereits “vergeben” (weiter oben oder unten) sind, sollen im Dropdown NICHT mehr angezeigt werden.
    Wird ein Wert (weiter oben oder unten) gelöscht, ist der Wert wieder wählbar.

    Erschwert wird das ganze noch, dass es in der Eingabespalte 10.000 Eingaben geben wird.
    Die Vorschlagsliste wird also immer kürzer.

    Gibt es dafür eine Lösung?

    Ich würde mich über eine Hilfe riesig freuen

    Danke und Gruß Uwe

    • Martin Weiß Autor des Beitrags

      Hallo Uwe,

      da gibt es schön Lösungen dafür, die sind aber nicht mit ein paar Sätzen hier erklärt. Ich möchte Dir mein E-Book “Dropdown-Listen in Excel” nicht aufdrängen, aber dort werden solche Themen auch behandelt. Vielleicht wäre das ja interessant für Dich.

      Schöne Grüße,
      Martin

  • Georg

    Danke für deinen Beitrag. Ich habe jedoch gerade folgendes Problem bzgl. Dropdownmenus.

    In einem Dropdownmenü sind verschiedene Bezeichnungen hinterlegt, z.B. Datum, Tag, Uhrzeit etc.
    Da die Datei später von Benutzern mit verschiedenen Sprachen genutzt wird, sollen die Benutzer die Bezeichnungen in der Quelltabelle übersetzen. Das Dropdownmenü passt sich dann automatisch an.
    Allerdings hätte ich es gerne so, dass wenn z.B. “Datum” im Dropdownmenü ausgewählt wurde und der Benutzer in der Quelltabelle “Datum” mit “Date” überschreibt, nicht nur die Dropdownliste aktualisiert wird, sondern auch direkt der ausgewählte Punkt in der Dropdownliste aktualisiert wird, ohne dass der Benutzer dann nochmal extra “Date” auswählen muss. Gibt es für dieses Problem eine Lösung?

    • Martin Weiß Autor des Beitrags

      Hallo Georg,

      einmal aus der Dropdown-Liste ausgewählte Punkte können mit normalen Mitteln nicht automatisch geändert werden, dazu wäre VBA erforderlich. Gegebenenfalls kann man den Anwender aber auf die nunmehr ungültigen Einträge per Bedingter Formatierung zumindest hinweisen.

      Schöne Grüße,
      Martin

  • Anne H.

    Hallo Martin,

    vielen lieben Dank für den Super-Tipp – in der Tat sehr hilfreich..und doch so einfach.
    Ich habe eine Frage und zwar habe ich mir auf die beschriebene Weise drei eindeutige Dropdown-Listen erstellt, die bisher alle Werte der Tabelle enthalten. Da jedoch nicht alle Kombinationen möglich sind, möchte ich die Auswahl basierend aus dem vorherigen Dropdown beschränken. (z.B. wenn als Versandart “Luftfracht” ausgewählt wurde, dass nur die Luftfracht-Spediteure angezeigt werden und wenn der Spediteur ausgewählt wurde im nächsten Dropdown nur die Lieferanten, die diesen Spediteur nutzen). Ist eine Verschachtelung über die Indirekt-Funktion möglich?

    Viele liebe Grüße!
    Anne