So erstellen Sie eine Dropdown-Liste in Excel

Die Dropdown-Liste ist ein unglaublich nützliches Werkzeug, das die Arbeit mit Informationen komfortabler machen kann. Sie ermöglicht es, mehrere Werte gleichzeitig in einer Zelle zu enthalten, mit denen man wie mit allen anderen arbeiten kann. Um den gewünschten auszuwählen, klicken Sie einfach auf das Pfeilsymbol, woraufhin eine Liste mit Werten angezeigt wird. Nachdem Sie eine bestimmte ausgewählt haben, wird die Zelle automatisch damit gefüllt und die Formeln werden basierend darauf neu berechnet.

Excel bietet viele verschiedene Methoden zum Generieren eines Dropdown-Menüs und ermöglicht es Ihnen außerdem, diese flexibel anzupassen. Lassen Sie uns diese Methoden genauer analysieren.

Listenerstellungsprozess

Um ein Popup-Menü zu erzeugen, klicken Sie auf die Menüpunkte entlang des Pfades „Daten“ – „Datenvalidierung“. Ein Dialogfeld wird geöffnet, in dem Sie die Registerkarte „Parameter“ finden und darauf klicken müssen, falls sie noch nicht geöffnet wurde. Es hat viele Einstellungen, aber der Punkt „Datentyp“ ist uns wichtig. Von allen Bedeutungen ist „Liste“ die richtige.

Die Anzahl der Methoden, mit denen Informationen in die Popup-Liste eingegeben werden, ist ziemlich groß.

1. Eigenständige Angabe von durch Semikolon getrennten Listenelementen im Feld „Quelle“ auf derselben Registerkarte derselben Dialogbox.
   

   

2. Vorläufige Angabe der Werte. Das Feld Quelle enthält den Bereich, in dem die erforderlichen Informationen verfügbar sind.
   

   

3. Angeben eines benannten Bereichs. Eine Methode, die die vorherige wiederholt, aber es ist nur notwendig, den Bereich vorläufig zu benennen.
   

   

Jede dieser Methoden führt zum gewünschten Ergebnis. Sehen wir uns Methoden zum Generieren von Dropdown-Listen in realen Situationen an.

Basierend auf Daten aus der Liste

Nehmen wir an, wir haben eine Tabelle, die die Arten verschiedener Früchte beschreibt.

Um basierend auf diesen Informationen eine Liste in einem Dropdown-Menü zu erstellen, müssen Sie Folgendes tun:

1. Wählen Sie die für die zukünftige Liste reservierte Zelle aus.
2. Suchen Sie die Registerkarte Daten im Menüband. Dort klicken wir auf „Daten überprüfen“.
   

   

3. Suchen Sie das Element „Datentyp“ und ändern Sie den Wert auf „Liste“.
   

   

4. Geben Sie im Feld „Quelle“ den gewünschten Bereich ein. Bitte beachten Sie, dass absolute Bezüge angegeben werden müssen, damit sich beim Kopieren der Liste die Informationen nicht verschieben.
   

Darüber hinaus gibt es eine Funktion zum gleichzeitigen Generieren von Listen in mehr als einer Zelle. Um dies zu erreichen, sollten Sie sie alle auswählen und dieselben Schritte wie zuvor beschrieben ausführen. Auch hier müssen Sie sicherstellen, dass absolute Referenzen geschrieben werden. Wenn die Adresse kein Dollarzeichen neben den Spalten- und Zeilennamen hat, müssen Sie sie hinzufügen, indem Sie die Taste F4 drücken, bis das $-Zeichen neben den Spalten- und Zeilennamen steht.

Mit manueller Datenerfassung

In der obigen Situation wurde die Liste geschrieben, indem der erforderliche Bereich hervorgehoben wurde. Dies ist eine bequeme Methode, aber manchmal ist es notwendig, die Daten manuell aufzuzeichnen. Dadurch wird es möglich, doppelte Informationen in der Arbeitsmappe zu vermeiden.

Angenommen, wir stehen vor der Aufgabe, eine Liste mit zwei Auswahlmöglichkeiten zu erstellen: ja und nein. Um die Aufgabe zu erfüllen, ist Folgendes erforderlich:

1. Klicken Sie auf die Zelle für die Liste.
2. Öffnen Sie „Daten“ und finden Sie dort den uns bekannten Abschnitt „Datencheck“.
   

   

3. Wählen Sie wieder den Typ „Liste“.
   

   

4. Hier müssen Sie „Ja; Nein“ als Quelle. Wir sehen, dass Informationen manuell mit einem Semikolon für die Aufzählung eingegeben werden.

Nachdem Sie auf OK geklickt haben, haben wir das folgende Ergebnis.

Als nächstes erstellt das Programm automatisch ein Dropdown-Menü in der entsprechenden Zelle. Alle Informationen, die der Benutzer als Elemente in der Popup-Liste angegeben hat. Die Regeln zum Erstellen einer Liste in mehreren Zellen ähneln den vorherigen, mit der einzigen Ausnahme, dass Sie die Informationen manuell mit einem Semikolon angeben müssen.

Erstellen einer Dropdown-Liste mit der OFFSET-Funktion

Neben der klassischen Methode ist es möglich, die Funktion zu verwenden VERFÜGUNGum Dropdown-Menüs zu generieren.

Lassen Sie uns das Blatt öffnen.

Um die Funktion für die Dropdown-Liste zu verwenden, müssen Sie Folgendes tun:

1. Wählen Sie die gewünschte Zelle aus, in der Sie die zukünftige Liste platzieren möchten.
2. Öffnen Sie nacheinander die Registerkarte „Daten“ und das Fenster „Datenvalidierung“.
   

   

3. Stellen Sie „Liste“ ein. Dies geschieht auf die gleiche Weise wie in den vorherigen Beispielen. Schließlich wird die folgende Formel verwendet: =OFFSET(A$2$;0;0;5). Wir geben es dort ein, wo die Zellen angegeben sind, die als Argument verwendet werden.

Dann erstellt das Programm ein Menü mit einer Liste von Früchten.

Die Syntax dafür ist:

=OFFSET(Referenz,Zeilenoffset,Spaltenoffset,[Höhe],[Breite])

Wir sehen, dass diese Funktion 5 Argumente hat. Zuerst wird die erste zu verschiebende Zellenadresse angegeben. Die nächsten beiden Argumente geben an, wie viele Zeilen und Spalten versetzt werden sollen. Apropos, das Height-Argument ist 5, weil es die Höhe der Liste darstellt. 

Dropdown-Liste in Excel mit Datenersetzung (+ mit der OFFSET-Funktion)

Im gegebenen Fall VERFÜGUNG erlaubt, ein Popup-Menü zu erstellen, das sich in einem festen Bereich befindet. Der Nachteil dieser Methode besteht darin, dass Sie nach dem Hinzufügen des Artikels die Formel selbst bearbeiten müssen.

Um eine dynamische Liste mit Unterstützung für die Eingabe neuer Informationen zu erstellen, müssen Sie:

1. Wählen Sie die gewünschte Zelle aus.
2. Erweitern Sie die Registerkarte „Daten“ und klicken Sie auf „Datenvalidierung“.
3. Wählen Sie im sich öffnenden Fenster erneut den Punkt „Liste“ und geben Sie als Datenquelle folgende Formel an: =СМЕЩ(A$2$;0;0;СЧЕТЕСЛИ($A$2:$A$100;”<>”))
4. Klicken Sie auf OK.

Diese enthält eine Funktion COUNTIF, um sofort zu bestimmen, wie viele Zellen gefüllt sind (obwohl es eine viel größere Anzahl von Anwendungen hat, schreiben wir es hier nur für einen bestimmten Zweck).

Damit die Formel normal funktioniert, muss nachverfolgt werden, ob sich im Pfad der Formel leere Zellen befinden. Sie sollten nicht sein.

Dropdown-Liste mit Daten aus einem anderen Blatt oder einer Excel-Datei

Die klassische Methode funktioniert nicht, wenn Sie Informationen aus einem anderen Dokument oder sogar einem Blatt in derselben Datei abrufen müssen. Dazu wird die Funktion verwendet INDIREKT, mit dem Sie im richtigen Format einen Link zu einer Zelle eingeben können, die sich in einem anderen Blatt oder allgemein – einer Datei befindet. Sie müssen Folgendes tun:

1. Aktivieren Sie die Zelle, in der wir die Liste platzieren.
2. Wir öffnen das Fenster, das wir bereits kennen. An der gleichen Stelle, an der wir zuvor Quellen für andere Bereiche angegeben haben, ist im Format eine Formel angegeben =INDIREKT(“[Liste1.xlsx]Tabelle1!$A$1:$A$9“). Anstelle von Liste1 und Blatt1 können Sie natürlich auch Ihren Buch- bzw. Blattnamen eingeben. 

Aufmerksamkeit! Der Dateiname wird in eckigen Klammern angegeben. In diesem Fall kann Excel die aktuell geschlossene Datei nicht als Informationsquelle verwenden.

Zu beachten ist auch, dass der Dateiname selbst nur dann Sinn macht, wenn sich das gewünschte Dokument im selben Ordner befindet, in dem auch die Liste eingefügt werden soll. Wenn nicht, müssen Sie die Adresse dieses Dokuments vollständig angeben.

Abhängige Dropdowns erstellen

Eine abhängige Liste ist eine Liste, deren Inhalt durch die Auswahl des Benutzers in einer anderen Liste beeinflusst wird. Angenommen, wir haben eine offene Tabelle vor uns, die drei Bereiche enthält, denen jeweils ein Name gegeben wurde.

Sie müssen diese Schritte ausführen, um Listen zu generieren, deren Ergebnis von der in einer anderen Liste ausgewählten Option beeinflusst wird.

1. Erste Liste mit Bereichsnamen erstellen.
   

   

2. Am Quelleneinstiegspunkt werden die erforderlichen Indikatoren nacheinander hervorgehoben.
   

   

3. Erstellen Sie je nach gewählter Pflanzenart eine 2. Liste. Wenn Sie alternativ Bäume in der ersten Liste angeben, lauten die Informationen in der zweiten Liste „Eiche, Hainbuche, Kastanie“ und mehr. Es ist notwendig, die Formel an der Stelle der Eingabe der Datenquelle aufzuschreiben =INDIREKT(E3). E3 – Zelle mit dem Namen des Bereichs 1.=INDIREKT(E3). E3 – Zelle mit dem Namen der Liste 1.

Jetzt ist alles bereit.

Wie wähle ich mehrere Werte aus einer Dropdown-Liste aus?

Manchmal ist es nicht möglich, nur einen Wert zu bevorzugen, daher müssen mehrere ausgewählt werden. Dann müssen Sie dem Seitencode ein Makro hinzufügen. Mit der Tastenkombination Alt + F11 öffnet sich der Visual Basic Editor. Und der Code wird dort eingefügt.

Private Sub Worksheet_Change (ByVal Target As Range)

    On Error Resume Next

    Wenn nicht, ist Intersect(Target, Range(«Е2:Е9»)) nichts und Target.Cells.Count = 1, dann

        Application.EnableEvents = Falsch

        Wenn Len (Target.Offset (0, 1)) = 0, dann

            Ziel.Offset (0, 1) = Ziel

        sonst

            Ziel.Ende (xlToRight) .Offset (0, 1) = Ziel

        End If

        Ziel.ClearContents

        Application.EnableEvents = Wahr

    End If

End Sub 

Damit die Inhalte der Zellen unten angezeigt werden, fügen wir den folgenden Code in den Editor ein.

Private Sub Worksheet_Change (ByVal Target As Range)

    On Error Resume Next

    Wenn nicht, ist Intersect(Target, Range(«Н2:К2»)) nichts und Target.Cells.Count = 1, dann

        Application.EnableEvents = Falsch

        Wenn Len (Target.Offset (1, 0)) = 0, dann

            Ziel.Offset (1, 0) = Ziel

        sonst

            Ziel.Ende (xlDown) .Offset (1, 0) = Ziel

        End If

        Ziel.ClearContents

        Application.EnableEvents = Wahr

    End If

End Sub

Und schließlich wird dieser Code verwendet, um in eine Zelle zu schreiben.

Private Sub Worksheet_Change (ByVal Target As Range)

    On Error Resume Next

    Wenn Not Intersect(Target, Range(«C2:C5»)) ist Nothing und Target.Cells.Count = 1 Then

        Application.EnableEvents = Falsch

        newVal = Ziel

        Anwendung.Rückgängig

        oldval = Ziel

        If Len (oldval) <> 0 And oldval <> newVal Then

            Ziel = Ziel & «,» & newVal

        sonst

            Ziel = newVal

        End If

        Wenn Len (newVal) = 0, dann Target.ClearContents

        Application.EnableEvents = Wahr

    End If

End Sub

Bereiche sind editierbar.

Wie erstelle ich eine Dropdown-Liste mit einer Suche?

In diesem Fall müssen Sie zunächst einen anderen Listentyp verwenden. Die Registerkarte „Entwickler“ wird geöffnet, danach müssen Sie auf das Element „Einfügen“ – „ActiveX“ klicken oder tippen (wenn der Bildschirm berührt wird). Es hat ein Kombinationsfeld. Sie werden aufgefordert, diese Liste zu zeichnen, wonach sie dem Dokument hinzugefügt wird.

Außerdem wird es über Eigenschaften konfiguriert, wobei ein Bereich in der ListFillRange-Option angegeben wird. Die Zelle, in der der benutzerdefinierte Wert angezeigt wird, wird mit der Option LinkedCell konfiguriert. Als nächstes müssen Sie nur die ersten Zeichen aufschreiben, da das Programm automatisch mögliche Werte vorschlägt.

Dropdown-Liste mit automatischer Datenersetzung

Es gibt auch eine Funktion, dass die Daten automatisch ersetzt werden, nachdem sie dem Bereich hinzugefügt wurden. Das geht ganz einfach:

1. Erstellen Sie eine Reihe von Zellen für die zukünftige Liste. In unserem Fall ist dies eine Reihe von Farben. Wir wählen es aus.
   

   

2. Als nächstes muss es als Tabelle formatiert werden. Sie müssen auf die gleichnamige Schaltfläche klicken und den Tabellenstil auswählen.
   

   

   

Als nächstes müssen Sie diesen Bereich mit der Schaltfläche „OK“ bestätigen.

Wir wählen die resultierende Tabelle aus und geben ihr einen Namen über das Eingabefeld, das sich über Spalte A befindet.

Das ist es, es gibt eine Tabelle, und sie kann als Grundlage für eine Dropdown-Liste verwendet werden, für die Sie Folgendes benötigen:

1. Wählen Sie die Zelle aus, in der sich die Liste befindet.
2. Öffnen Sie das Dialogfeld Datenvalidierung.
   

   

3. Wir setzen den Datentyp auf „Liste“, und als Werte geben wir den Namen der Tabelle durch das =-Zeichen an.
   

   

   

Alles, die Zelle ist fertig, und die Namen der Farben werden darin angezeigt, wie wir sie ursprünglich brauchten. Jetzt können Sie neue Positionen hinzufügen, indem Sie sie einfach in eine etwas tiefer liegende Zelle direkt nach der letzten schreiben.

Das hat den Vorteil der Tabelle, dass sich die Reichweite automatisch vergrößert, wenn neue Daten hinzukommen. Dementsprechend ist dies die bequemste Art, eine Liste hinzuzufügen.

Wie kopiere ich die Dropdown-Liste?

Zum Kopieren genügt es, die Tastenkombination Strg + C und Strg + V zu verwenden. Die Dropdown-Liste wird also zusammen mit der Formatierung kopiert. Um die Formatierung zu entfernen, müssen Sie ein spezielles Einfügen verwenden (im Kontextmenü wird diese Option nach dem Kopieren der Liste angezeigt), bei dem die Option „Bedingungen für Werte“ festgelegt ist.

Wählen Sie alle Zellen aus, die eine Dropdown-Liste enthalten

Um diese Aufgabe zu erfüllen, müssen Sie die Funktion „Eine Gruppe von Zellen auswählen“ in der Gruppe „Suchen und Auswählen“ verwenden.

Danach öffnet sich ein Dialogfenster, in dem Sie im Menü „Data Validation“ die Punkte „All“ und „These same“ auswählen sollten. Das erste Element wählt alle Listen aus, und das zweite wählt nur diejenigen aus, die bestimmten ähnlich sind.