Dropdown-Liste Spezial 7

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.



Kommentar erstellen

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

7 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ß Beitragsautor

      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

  • 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ß Beitragsautor

      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