Dropdown-Liste: Dynamisch erweitert und sortiert 15

Eine etwas andere Art, wie man Auswahllisten dynamisch gestalten kann.
 

Ein kleiner Dauerbrenner in Excel sind Dropdown-Listen: die praktischen kleinen Auswahlfelder, über die man die Dateneingabe komfortabel gestalten und auf zulässige Werte beschränken kann.

Heute zeige ich dir einen Weg, wie man die Einträge solcher Listen (fast) automatisch sortiert, erweitert und um Duplikate bereinigt anzeigen lassen kann, wenn sich die zugrunde liegende Datenquelle dynamisch verändert.

Und so geht’s:

Das Szenario

Nehmen wir an, du hast eine Adressenliste (meine Beispieldatei kannst du hier herunterladen), in der laufend neue Datensätze dazukommen oder nicht mehr benötigte entfernt werden:

Beispiel: Adressenliste

Um die Eingabe etwas komfortabler zu gestalten, soll für den Ort ein Auswahlfeld eingerichtet werden, das alle in der Tabelle vorhandenen Orte anbietet. Dabei sollen folgende Kriterien beachtet werden:

  • die Orte in der Auswahlliste sollen alphabetisch sortiert sein und natürlich keine Duplikate enthalten
  • wenn in der Adressenliste ein Ort nicht mehr enthalten ist (weil beispielsweise alle Adressen dazu entfernt wurden), soll dieser Ort auch nicht mehr in der Auswahlliste angeboten werden
  • die Eingabe von neuen, noch nicht in der Auswahlliste enthaltenen Orten muss möglich sein
  • der neue Ort soll anschließend automatisch in der Auswahlliste verfügbar sein

Das sind ziemliche viele Anforderungen, die bei einer „normalen“ Dropdown-Liste erst einmal nicht vorgesehen sind. Aber du bist ja hier, um etwas Neues zu lernen, oder?

Gehen wir es also der Reihe nach an.

Schritt 1: Eine formatierte Tabelle

Falls es sich bei deinen Daten bereits um eine formatierte („intelligente“) Tabelle handelt, kannst du diesen Schritt natürlich überspringen. Ansonsten wandeln wir die Adressenliste erst in eine formatierte Tabelle um:

Start | Als Tabelle formatieren (oder über die Tastenkombination Strg+T)

Du solltest dir grundsätzlich angewöhnen, für formatierte Tabellen einen aussagefähigen Namen festzulegen. Das ist für die Funktionalität zwar nicht zwingend erforderlich, macht aber die Orientierung einfacher, sobald man es mit mehr als nur einer Tabelle zu tun hat. Meine formatierte Tabelle heißt „tblAdressen“:

Umwandeln in eine formatierte Tabelle

Schritt 2: Eine Pivot-Tabelle

Ein nicht unbedingt naheliegender Einsatzbereich für eine Pivot-Tabelle, aber für unsere Zwecke ziemlich praktisch, wie du gleich noch sehen wirst. Stelle also die aktive Zelle irgendwo in die Adressentabelle und wähle aus dem Menü:

Einfügen | PivotTable

Im sich nun öffnenden Fenster ist als Tabelle/Bereich bereits der Name der Pivot-Tabelle enthalten. Unten im Fenster habe ich noch angegeben, dass die Pivot-Tabelle rechts neben meiner Adressenliste erstellt werden soll:

Eine neue Pivot-Tabelle erstellen

Im „echten“ Leben würde ich sie in einem separaten Blatt erstellen, das man später ausblenden kann. Aber zu Demozwecken ist es besser, wenn man sie direkt im Blick hat.

Nun ziehen wir einfach das Feld „Ort“ in den Zeilenbereich der Pivot-Tabelle und erhalten folgendes Ergebnis:

Ort-Feld in den Zeilenbereich

Wie zu erwarten, wird jeder Ort aus unserer Adressenliste genau einmal angezeigt. Allerdings sind noch ein paar Anpassungen notwendig, damit diese Pivot-Tabelle später als Datenquelle für unser Dropdown-Feld taugt. Wähle in der Feldüberschrift die Option „Von A bis Z sortieren“:

Sortieren der Pivot-Tabelle

Die Pivot-Tabelle ist bei der Erstellung zwar standardmäßig schon sortiert, aber wenn später neue Einträge dazukommen, dann ist das nicht mehr zwingend der Fall. Daher also die explizite Sortierung.

Dann brauchen wir natürlich keine Zeile mit einem Gesamtergebnis:

PivotTable-Tools | Entwurf |Gesamtergebnisse | Für Zeilen und Spalten deaktiviert

Ergebniszeile deaktivieren

Nun rufen wir über einen Rechtsklick auf irgendeinen Eintrag in der Pivot-Tabelle die PivotTable-Optionen auf:

PivotTable-Optionen aufrufen

Hier sind zwei Einstellungen im Registerblatt „Daten“ interessant. Zum einen soll die Pivot-Tabelle automatisch beim Öffnen der Datei aktualisiert werden. Und wir wollen später keine Elemente mehr sehen, die aus der Datenquelle gelöscht wurden. Daher setzen wir die entsprechenden Einstellungen:

Daten-Einstellungen anpassen


Damit sind wir mit der Pivot-Tabelle fertig und können uns um die Datenprüfung kümmern.

Schritt 3: Datenprüfung

Das Dropdown-Feld werden wir gleich über die Datenprüfung einrichten. Zuvor müssen wir aber noch einen Namen festlegen, der immer genau die in der Pivot-Tabelle enthaltenen Zeilen umfasst. Nicht mehr und nicht weniger. Legen wir also einen neuen Namen an:

Formeln | Namen definieren

Als Name habe ich „Dropdown“ gewählt, aber du kannst natürlich jeden anderen Namen verwenden. Wichtig ist jedoch Feld „Bezieht sich auf:“, den hier muss folgende Formel eingetragen werden:
=BEREICH.VERSCHIEBEN(Tabelle1!$H$1;1;0;ANZAHL2(Tabelle1!$H:$H)-1)

Einen dynamischen Namen definieren

Was genau passiert hier?

Die Funktion BEREICH.VERSCHIEBEN ermöglicht es uns, einen dynamischen Bereich festzulegen. Die allgemeine Syntax lautet:
=BEREICH.VERSCHIEBEN(Bezug;Zeilen;Spalten;[Höhe];[Breite])

Der erste Parameter „Bezug“ stellt den Ausgangspunkt dar. In unserem Fall ist das die Kopfzelle unserer Pivot-Tabelle, also H1. Da dieser Bezug fix ist, verwende ich die absolute Schreibweise $H$1 (zusammen mit dem vorausgestellten Blattnamen)
Die beiden nächsten Parameter legen fest, um wie viele Zeilen und Spalten dieser Bezug verschoben werden soll. In unserem Beispiel soll es ja mit dem ersten Ortsnamen losgehen, also eine Zeile unterhalb der Überschrift. Daher der Wert 1. Da wir in der gleichen Spalte bleiben, ist der Wert für den dritten Parameter „Spalte“ 0 (Null).
Und jetzt kommt der eigentlich spannende Teil, nämlich die Höhe. Oder anders gesagt, wie viele Zeilen soll unser Bereich umfassen. Da sich die Pivot-Tabelle im laufenden Betrieb ja dynamisch verändern wird (es kommen neue Orte dazu oder alte fallen weg), zählen wir mit der Funktion ANZAHL2 einfach die Anzahl der Einträge in Spalte H. Und vom Ergebnis ziehen wir 1 ab, da die Überschrift ja nicht mitgezählt werden soll.

Jetzt haben wir also unseren dynamischen Bereich, auf den wir in der Datenprüfung verweisen können. Dazu markieren wir erst die Spalte mit den Orten in unserer Adressentabelle und richten dann die Datenprüfung ein:

Daten | Datenüberprüfung

Datenüberprüfung einrichten

Im Register „Einstellungen“ wählst du im Feld „Zulassen“ den Eintrag „Liste“. Als Quelle gibst du den Namen an, den wir zuvor erstellt haben mit einem vorangestellten Gleichheitszeichen ein (Tipp: Du kannst dir mit der Funktionstaste F3 auch eine Liste aller Namen anzeigen lassen und den gewünschten dort auswählen):

Der definierte Name als Datenquelle

Jetzt wechselst du in das Registerblatt „Fehlermeldung“ und änderst den Typ von „Stopp“ auf „Information“. Das ist notwendig, damit die Eingabe eines neuen Ortes überhaupt möglich wird. Du solltest dann auch einen entsprechenden Hinweis in den Titel und das Feld mit der Fehlermeldung eintragen:

Fehlermeldung anpassen

Und damit können wir unser Auswahlfeld gleich mal testen und einen neuen Datensatz hinzufügen. Zunächst sind in der Dropdown-Liste alle Orte enthalten, die sich in der Pivot-Tabelle befinden:

Dropdown-Liste im Einsatz

Wenn du jetzt aber stattdessen einen ganz neuen Ort eintippst, erscheint unsere zuvor definierte Hinweismeldung, die wir mit OK bestätigen können:

Ein neuer Ort kommt dazu

Damit wird die Eingabe erst einmal akzeptiert, aber in der Dropdown-Liste taucht der Ort trotzdem noch nicht auf. Dazu muss erst die Pivot-Tabelle mit der Tastenkombination „Strg + Alt + F5“ aktualisiert werden (oder über das Menü Daten | Alle aktualisieren). Das ist der einzige Wermutstropfen, den du bei dieser Lösung hast. Allerdings wird aufgrund unserer weiter oben vorgenommenen Einstellung die Pivot-Tabelle und damit auch die Dropdown-Liste zumindest dann automatisch aktualisiert, wenn die Datei das nächste Mal geöffnet wird.

Schritt 5: Ein letzter Feinschliff

Damit man als Anwender wenigstens auf neue Einträge aufmerksam gemacht wird, die sich noch nicht in der Auswahlliste befinden, kann man noch eine bedingte Formatierung einsetzen. Hierfür markieren wir wieder die Spalte mit den Orten und richten eine neue Formatierungsregel ein:
Start | Bedingte Formatierung | Neue Regel
Als Regeltyp wählst du „Formel zur Ermittlung der zu formatierenden Zellen verwenden aus“ und gibst dann folgende Formel ein:
=UND(E2<>"";ISTFEHLER(SVERWEIS(E2;Dropdown;1;falsch)))
Und legst zum Beispiel eine gelbe Hintergrundfarbe fest:

Formatierungsregel definieren

Achte beim Eingeben der Formel unbedingt darauf, dass die angegebene Zelle (bei mir E2) auch tatsächlich mit der momentan aktiven Zelle übereinstimmt.

Die Formel prüft im ersten Teil, ob überhaupt etwas im Ort eingetippt wurde. Ansonsten würde bei jeder neuen Zeile bereits die Ortsspalte gelb eingefärbt werden.
Der zweite Teil der Formel prüft über einen SVERWEIS, ob der Wert in E2 in der Dropdown-Liste enthalten ist. Falls nicht, wird ein #NV-Fehler zurückgeliefert, der über die umschließende ISTFEHLER-Funktion in ein logisches WAHR umgewandelt wird. Erst wenn beide Bedingungen erfüllt sind, greift die Formatierungsregel.

Wenn du jetzt einen neuen Ort eintippst, wird dieser solange gelb hervorgehoben, bis die Pivot-Tabelle einmal aktualisiert wurde. Also bis du Strg+Alt+F5 drückst oder spätestens beim nächsten Öffnen der Datei, wenn die Pivot-Tabelle automatisch aktualisiert wird.

Der fehlende Ort wird hervorgehoben

Es gibt natürlich auch andere Wege, Dropdown-Listen dynamisch zu erweitern und zu sortieren. Aber ich finde, der Einsatz einer Pivot-Tabelle an dieser Stelle hat durchaus seinen Reiz. Was meinst du?

 

Wenn dir der Artikel gefallen hat: Bitte weitersagen!

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.

15 Gedanken zu “Dropdown-Liste: Dynamisch erweitert und sortiert

  • Bo Rado

    Super, diese Eingabemöglichkeit habe ich immer gesucht. Und nebenbei hervorragend erklärt. Ich werde mich wohl in Zukunft öfter mal mit Pivot-Tabellen beschäftigen.
    Vielen Dank und Beste Grüße,
    Bo Rado

    • Martin Weiß Autor des Beitrags

      Hallo Bo,

      ja, Pivot-Tabellen sind ein lohnendes Betätigungsfeld. Und sie sind wirklich nicht so schwer, wie viele meinen.

      Schöne Grüße,
      Martin

    • Michael Krüger

      Ich habe das Buch „Pivot-Tabellen für Dummies“ von Martin gekauft. Es ist sehr gut strukturiert und lesbar; ich kann es sehr empfehlen.

  • Roswitha

    Hallo Martin,
    danke, diese dynamisch erweiterte Dropdownliste kam für mich zur richtigen Zeit, genau danach hatte ich gesucht, kann ich wunderbar in meine Projektdaten einarbeiten. Ganz prima 🙂
    Liebe Grüße Roswitha

  • Andreas

    Da kann ich den vorherigen Kommentaren nur zustimmen. Auch ich habe schon lange nach einer solchen Möglichkeit gesucht. Klar hätte ich einen Makro schreiben können, aber so finde ich es viel besser. Nur für die die automatische Aktualisierung brauche ich nun noch einen sehr einfachen Makro.
    Viele Grüße
    Andreas

    • Martin Weiß Autor des Beitrags

      Hallo Andreas,

      ja, mit einem kleinen Makro lässt sich auch der letzte Schritt automatisieren.

      Schöne Grüße,
      Martin

      • Andreas

        So jetzt habe ich mal meine erste vollautomatische dynamische Dropdown-Liste erstellt.
        Für alle, die sie Aktualisierung auch per Makro vornehmen wollen hier das passende Statement, was beim SheetChange-Event aufgerufen werden müsste:
        Tabelle_xyz.PivotTables(„<<>>“).PivotCache.Refresh

        Übrigens: Falls die Quellspalte leere Zellen enthält (das ist bei mir der Fall und soll so sein), dann erscheint am Ende der Pivot-Tabelle der Eintrag (leer). Dieser soll natürlich nicht mit in der Dropdown-Liste erscheinen. Das ist leicht bewerkstelligt: einfach am Ende des Bereich.Verschieben-Statements statt -1 nun -2 eintragen. Schon passt alles.
        Das funktioniert alles super.
        Martin, Dir noch mal vielen Dank für die Idee! Das war sicher nicht das letzte Mal, dass ich diese Idee eingesetzt habe.
        Gruß Andreas