Tutorial

[Gelöst] Excel Dropdown Liste Erstellen

Eine Dropdown-Liste ist eine hervorragende Möglichkeit, dem Benutzer eine Option zur Auswahl aus einer vordefinierten Liste zu geben.

Sie kann verwendet werden, wenn ein Benutzer ein Formular ausfüllen soll, oder beim Erstellen von interaktiven Excel-Dashboards.

Dropdown-Listen sind auf Websites/Apps recht häufig anzutreffen und sind für den Benutzer sehr intuitiv.

Erstellen Sie mit der Datenüberprüfungsfunktion von Excel eine Dropdown-Liste von Elementen in einer Zelle. Dies erleichtert die Dateneingabe und reduziert Eingabefehler und Tippfehler.

In diesem Tutorial lernen Sie, wie Sie eine Dropdown-Liste in Excel erstellen (das dauert nur ein paar Sekunden), zusammen mit all den tollen Sachen, die Sie damit machen können.

Was ist die Datenüberprüfung?

In Excel hilft Ihnen die Datenüberprüfungsfunktion zu kontrollieren, was in Ihr Arbeitsblatt eingegeben werden kann. Zum Beispiel können Sie:

  • eine Dropdown-Liste von Elementen in einer Zelle erstellen
  • Eingaben einschränken, z. B. auf einen Datumsbereich oder nur ganze Zahlen
  • benutzerdefinierte Regeln dafür erstellen, was eingegeben werden kann

So erstellen Sie eine Dropdown-Liste in Excel

In diesem Abschnitt lernen Sie die genauen Schritte zum Erstellen einer Einblendliste in Excel kennen:

  • Daten aus Zellen verwenden.
  • Manuelles Eingeben von Daten.
  • Verwenden der Formel OFFSET.

Methode #1: Daten aus Zellen verwenden

Nehmen wir an, Sie haben eine Liste von Elementen wie unten gezeigt:

Sie haben eine Liste von Elementen

Hier sind die Schritte zum Erstellen einer Excel-Dropdown-Liste:

  • Wählen Sie eine Zelle aus, in der Sie die Auswahlliste erstellen möchten.
  • Gehen Sie zu Daten -> Datentools -> Datenüberprüfung.
Wählen Sie eine Zelle aus, in der Sie die Auswahlliste erstellen möchten
  • Wählen Sie im Dialogfeld “Datenüberprüfung” auf der Registerkarte “Einstellungen” als Überprüfungskriterium “Liste”.
  • Sobald Sie Liste wählen, erscheint das Quellfeld
Geben Sie in das Quellfeld =$A$2:$A$8 ein
  • Geben Sie in das Quellfeld =$A$2:$A$8 ein, oder klicken Sie einfach in das Quellfeld und markieren Sie die Zellen mit der Maus und klicken Sie auf OK. Dadurch wird eine Dropdown-Liste in Zelle C2 eingefügt.
  • Vergewissern Sie sich, dass die Option “Dropdown in Zelle” aktiviert ist (sie ist standardmäßig aktiviert). Wenn diese Option nicht aktiviert ist, wird in der Zelle keine Auswahlliste angezeigt, Sie können die Werte jedoch manuell in die Liste eingeben.

Hinweis: Wenn Sie Einblendlisten in mehreren Zellen auf einmal erstellen möchten, markieren Sie alle Zellen, in denen Sie sie erstellen möchten, und führen Sie dann die oben genannten Schritte aus. Achten Sie darauf, dass die Zellbezüge absolut (z. B. $A$2) und nicht relativ (z. B. A2 oder A$2 oder $A2) sind.

Methode #2: Durch manuelle Eingabe von Daten

Im obigen Beispiel werden Zellbezüge im Feld Quelle verwendet. Sie können Elemente auch direkt hinzufügen, indem Sie sie manuell in das Quellfeld eingeben.

Nehmen wir zum Beispiel an, Sie möchten zwei Optionen, Ja und Nein, im Einblendmenü in einer Zelle anzeigen. So können Sie sie direkt in das Datenüberprüfungsquellfeld eingeben:

  • Wählen Sie eine Zelle aus, in der Sie die Einblendliste erstellen möchten (Zelle C2 in diesem Beispiel).
  • Gehen Sie zu Daten -> Datentools -> Datenüberprüfung
  • Wählen Sie im Dialogfeld “Datenüberprüfung” auf der Registerkarte “Einstellungen” als Überprüfungskriterium “Liste”.
  • Sobald Sie Liste wählen, erscheint das Quellfeld
  • Geben Sie im Feld Quelle Ja; Nein
  • Vergewissern Sie sich, dass die Dropdown-Option In der Zelle aktiviert ist.
  • Klicken Sie auf OK.
Geben Sie im Feld Quelle Ja; Nein

Dadurch wird eine Dropdown-Liste in der ausgewählten Zelle erstellt. Alle im Quellfeld eingegebenen Elemente werden, durch ein Komma getrennt, in verschiedenen Zeilen im Dropdown-Menü aufgelistet.

Alle im Quellfeld eingegebenen Elemente, die durch ein Komma getrennt sind, werden in verschiedenen Zeilen in der Dropdown-Liste angezeigt.

Hinweis: Wenn Sie Dropdown-Listen in mehreren Zellen auf einmal erstellen möchten, markieren Sie alle Zellen, in denen Sie sie erstellen möchten, und führen Sie dann die oben genannten Schritte aus.

Methode 3: Excel-Formeln verwenden

Neben der Auswahl aus Zellen und der manuellen Eingabe von Daten können Sie auch eine Formel im Quellfeld verwenden, um eine Excel-Einblendliste zu erstellen.

Jede Formel, die eine Liste von Werten zurückgibt, kann zum Erstellen einer Einblendliste in Excel verwendet werden.

Nehmen Sie zum Beispiel an, Sie haben den unten gezeigten Datensatz:

Sie haben eine Liste von Elementen

Hier sind die Schritte zum Erstellen einer Excel-Einblendliste mit der Funktion OFFSET:

  • Wählen Sie eine Zelle aus, in der Sie die Einblendliste erstellen möchten (Zelle C1 in diesem Beispiel).
  • Gehen Sie zu Daten -> Datentools -> Datenüberprüfung.
Gehen Sie zu Daten -> Datentools -> Datenüberprüfung
  • Wählen Sie im Dialogfeld “Datenüberprüfung” auf der Registerkarte “Einstellungen” als Überprüfungskriterium “Liste”.
  • Sobald Sie Liste auswählen, wird das Quellfeld angezeigt.
  • Geben Sie in das Feld Quelle die folgende Formel ein: =OFFSET($A$2;0;0;7)
  • Stellen Sie sicher, dass die Dropdown-Option “In der Zelle” aktiviert ist.
  • Klicken Sie auf OK.

Dadurch wird eine Einblendliste erstellt, die alle Fruchtnamen auflistet (wie unten gezeigt).

Dadurch wird eine Einblendliste erstellt, die alle Fruchtnamen auflistet

Hinweis: Wenn Sie eine Einblendliste in mehreren Zellen auf einmal erstellen möchten, markieren Sie alle Zellen, in denen Sie sie erstellen möchten, und führen Sie dann die oben genannten Schritte aus. Achten Sie darauf, dass die Zellbezüge absolut (z. B. $A$2) und nicht relativ (z. B. A2 oder A$2 oder $A2) sind.

Wie funktioniert diese Formel?

Im obigen Fall haben wir eine OFFSET-Funktion verwendet, um die Dropdown-Liste zu erstellen. Sie gibt eine Liste von Elementen aus dem Bereich ra

Sie gibt eine Liste von Einträgen aus dem Bereich A2:A8 zurück.

Hier ist die Syntax der OFFSET-Funktion:

=OFFSET(reference; rows; cols; [height]; [width])

Sie nimmt fünf Argumente entgegen, wobei wir die Referenz als A2 (den Startpunkt der Liste) angegeben haben. Zeilen/Spalten werden als 0 angegeben, da wir die Referenzzelle nicht versetzen wollen. Die Höhe wird mit 5 angegeben, da die Liste fünf Elemente enthält.

Wenn Sie nun diese Formel verwenden, gibt sie ein Array zurück, das die Liste der fünf Früchte in A2:A8 enthält. Wenn Sie die Formel in eine Zelle eingeben, sie markieren und F9 drücken, sehen Sie, dass sie ein Array mit den Namen der Früchte zurückgibt.

Fortgeschrittene: Erstellen einer dynamischen Dropdown-Liste in Excel (mit OFFSET)

Die oben beschriebene Technik der Verwendung einer Formel zum Erstellen einer Einblendliste kann auch zum Erstellen einer dynamischen Einblendliste verwendet werden. Wenn Sie, wie oben gezeigt, die Funktion OFFSET verwenden, wird die Auswahlliste nicht automatisch aktualisiert, selbst wenn Sie der Liste weitere Elemente hinzufügen. Sie müssen sie jedes Mal manuell aktualisieren, wenn Sie die Liste ändern.

Hier ist eine Möglichkeit, es dynamisch zu machen (und es ist nichts weiter als eine kleine Änderung in der Formel):

  • Wählen Sie eine Zelle aus, in der Sie die Dropdown-Liste erstellen möchten (Zelle C2 in diesem Beispiel).
  • Gehen Sie zu Daten -> Datentools -> Datenüberprüfung.
  • Wählen Sie im Dialogfeld Datenüberprüfung auf der Registerkarte Einstellungen die Option Liste als Überprüfungskriterium. Sobald Sie Liste ausgewählt haben, wird das Feld Quelle angezeigt.
  • Geben Sie in das Quellfeld die folgende Formel ein:
=OFFSET($A$2;0;0;COUNTIF($A$2:$A$100;"<>"))
  • Vergewissern Sie sich, dass die Dropdown-Option “In der Zelle” aktiviert ist.
  • Klicken Sie auf OK.
  • In dieser Formel habe ich das Argument 5 durch COUNTIF($A$2:$A$100;”<>”) ersetzt.

Die Funktion COUNTIF zählt die nicht leeren Zellen im Bereich A2:A100. Daher passt sich die Funktion OFFSET an, um alle nicht leeren Zellen einzuschließen.

die Funktion OFFSET an, um alle nicht leeren Zellen einzuschließen

Hinweis:

  • Damit dies funktioniert, dürfen sich KEINE leeren Zellen zwischen den gefüllten Zellen befinden.
  • Wenn Sie eine Einblendliste in mehreren Zellen auf einmal erstellen möchten, markieren Sie alle Zellen, in denen Sie sie erstellen möchten, und führen Sie dann die oben genannten Schritte aus. Achten Sie darauf, dass die Zellbezüge absolut (z. B. $A$2) und nicht relativ (z. B. A2, oder A$2, oder $A2) sind.

Fortgeschrittene: Kopieren und Einfügen von Drop-Down-Listen in Excel

Sie können die Zellen mit der Datenüberprüfung per Copy-Paste in andere Zellen einfügen, wobei die Datenüberprüfung mitkopiert wird.

Wenn Sie z. B. eine Dropdown-Liste in Zelle C2 haben und diese auch auf C3:C8 anwenden möchten, kopieren Sie einfach die Zelle C2 und fügen sie in C3:C8 ein. Dadurch wird die Einblendliste kopiert und in C3:C8 verfügbar gemacht (zusammen mit der Einblendliste wird auch die Formatierung kopiert).

Wenn Sie nur das Dropdown und nicht die Formatierung kopieren möchten, gehen Sie wie folgt vor:

  • Kopieren Sie die Zelle, in der sich das Dropdown befindet.
  • Markieren Sie die Zellen, in die Sie das Einblendmenü kopieren möchten.
  • Gehen Sie zu Start -> Einfügen -> Einfügen Spezial.
Gehen Sie zu Start -> Einfügen -> Einfügen Spezial
  • Wählen Sie im Dialogfeld “Einfügen spezial” die Option “Validierung” in den Einfügeoptionen
Wählen Sie im Dialogfeld “Einfügen spezial” die Option “Validierung” in den Einfügeoptionen
  • Klicken Sie auf OK.

Dadurch wird nur das Dropdown und nicht die Formatierung der kopierten Zelle kopiert.

Vorsicht bei der Arbeit mit Excel-Dropdown-Listen

Bei der Arbeit mit Einblendlisten in Excel ist Vorsicht geboten.

Wenn Sie eine Zelle (die keine Auswahlliste enthält) über eine Zelle kopieren, die eine Auswahlliste enthält, geht die Auswahlliste verloren.

Das Schlimmste daran ist, dass Excel keine Warnung oder Aufforderung anzeigt, um den Benutzer darauf hinzuweisen, dass eine Auswahlliste überschrieben wird.

So wählen Sie alle Zellen aus, die eine Auswahlliste enthalten

Manchmal ist es schwer zu wissen, welche Zellen die Auswahlliste enthalten.

Daher ist es sinnvoll, diese Zellen zu markieren, indem man sie entweder mit einem deutlichen Rahmen oder einer Hintergrundfarbe versieht.

Anstatt alle Zellen manuell zu überprüfen, gibt es eine schnelle Möglichkeit, alle Zellen auszuwählen, die Einblendlisten (oder eine beliebige Datenüberprüfungsregel) enthalten.

  • Gehen Sie zu Home -> Suchen & Auswählen -> Gehe zu Spezial
Suchen & Auswählen -> Gehe zu Spezial
  • Wählen Sie in der Dialogbox Gehe zu Spezial die Option Datenüberprüfung
  • Für die Datenüberprüfung gibt es zwei Optionen: Alle und Gleiche. Alle würde alle Zellen auswählen, auf die eine Datenüberprüfungsregel angewendet wird. Gleich würde nur die Zellen auswählen, die die gleiche Datenüberprüfungsregel wie die der aktiven Zelle haben.
Für die Datenüberprüfung gibt es zwei Optionen: Alle und Gleiche
  • Klicken Sie auf OK.

Dies würde sofort alle Zellen auswählen, auf die eine Datenüberprüfungsregel angewendet wird (dies schließt auch Dropdown-Listen ein).

Jetzt können Sie die Zellen einfach formatieren (mit einem Rahmen oder einer Hintergrundfarbe versehen), so dass sie visuell sichtbar sind und Sie nicht versehentlich eine andere Zelle darauf kopieren.

Experte: Erstellen einer abhängigen / bedingten Excel-Dropdown-Liste

Manchmal haben Sie vielleicht mehr als eine Dropdown-Liste und Sie möchten, dass die in der zweiten Dropdown-Liste angezeigten Elemente davon abhängen, was der Benutzer in der ersten Dropdown-Liste ausgewählt hat.

Diese werden als abhängige oder bedingte Dropdown-Listen bezeichnet.

Diese werden als abhängige oder bedingte Dropdown-Listen bezeichnet.

Schauen wir uns nun an, wie man dies erstellt.

Hier sind die Schritte zum Erstellen einer abhängigen / bedingten Dropdown-Liste in Excel:

  • Wählen Sie die Zelle aus, in der Sie die erste (Haupt-) Dropdown-Liste erstellen möchten.
  • Gehen Sie zu Daten -> Datenüberprüfung. Dadurch wird das Dialogfeld für die Datenüberprüfung geöffnet
  • Wählen Sie im Dialogfeld “Datenüberprüfung” auf der Registerkarte “Einstellungen” die Option “Liste”.
  • Geben Sie im Feld Quelle den Bereich an, der die Elemente enthält, die in der ersten Dropdown-Liste angezeigt werden sollen.
Wählen Sie im Dialogfeld “Datenüberprüfung” auf der Registerkarte “Einstellungen” die Option “Liste”.
  • Klicken Sie auf OK. Damit wird die “Liste 1” erstellt
Damit wird die “Liste 1” erstellt
  • Wählen Sie den gesamten Datensatz aus (in diesem Beispiel A1:B6)
  • Gehen Sie zu Formeln -> Definierte Namen -> Aus Auswahl erstellen (oder verwenden Sie den Tastaturbefehl Strg + Umschalt + F3).
Wählen Sie den gesamten Datensatz aus (in diesem Beispiel A1:B6)
  • Aktivieren Sie im Dialogfeld “Benannte aus Auswahl erstellen” die Option “Oberste Zeile” und deaktivieren Sie alle anderen. Dadurch werden 2 Namensbereiche (“Früchte” und “Gemüse”) erstellt. Der Namensbereich “Früchte” bezieht sich auf alle Früchte in der Liste und der Namensbereich “Gemüse” bezieht sich auf alle Gemüse in der Liste.
Check: Top row
  • Klicken Sie auf OK.
  • Wählen Sie die Zelle aus, in der die Dropdown-Liste “Abhängig/bedingt” erscheinen soll (E3 in diesem Beispiel).
  • Gehen Sie zu Daten -> Datenüberprüfung
  • Vergewissern Sie sich im Dialogfeld “Datenüberprüfung” auf der Registerkarte “Einstellung”, dass “Liste” ausgewählt ist.
  • Geben Sie in das Feld Quelle die Formel =INDIRECT(D3) ein. Hier ist D3 die Zelle, die das Haupt-Dropdown enthält
  • Klicken Sie auf OK.
würden die in Dropdown-Liste 2 aufgeführten Optionen automatisch aktualisiert.

Wenn Sie nun die Auswahl in Dropdown-Liste 1 treffen, würden die in Dropdown-Liste 2 aufgeführten Optionen automatisch aktualisiert.

Wie funktioniert das?

Die bedingte Dropdown-Liste (in Zelle E3) bezieht sich auf =INDIRECT(D3). Das heißt, wenn Sie in Zelle D3 “Früchte” auswählen, bezieht sich die Einblendliste in E3 auf den benannten Bereich “Früchte” (über die Funktion INDIRECT) und listet daher alle Elemente in dieser Kategorie auf.

Wichtiger Hinweis beim Arbeiten mit bedingten Dropdown-Listen in Excel:

  • Wenn Sie die Auswahl getroffen haben und dann das übergeordnete Dropdown ändern, würde sich das abhängige Dropdown nicht ändern und wäre daher ein falscher Eintrag. Wenn Sie z. B. die USA als Land und dann Florida als Staat auswählen und dann zurückgehen und das Land in Indien ändern, würde der Staat Florida bleiben. Hier finden Sie eine großartige Anleitung von Debra zum Löschen von abhängigen (bedingten) Dropdown-Listen in Excel, wenn die Auswahl geändert wird.
  • Wenn die Hauptkategorie aus mehr als einem Wort besteht (z. B. “Saisonale Früchte” statt “Früchte”), dann müssen Sie die Formel =INDIRECT(SUBSTITUTE(D3;” “;”_”)) verwenden, anstatt der oben gezeigten einfachen INDIRECT-Funktion. Der Grund dafür ist, dass Excel keine Leerzeichen in benannten Bereichen zulässt. Wenn Sie also einen benannten Bereich mit mehr als einem Wort erstellen, fügt Excel automatisch einen Unterstrich zwischen den Wörtern ein. Der benannte Bereich “Saisonale Früchte” würde also “Saisonale_Früchte” heißen. Die Verwendung der Funktion SUBSTITUTE innerhalb der Funktion INDIRECT sorgt dafür, dass Leerzeichen in Unterstriche umgewandelt werden.

FAQ

Frage 1:
Wenn ich versuche, eine OFFSET-Formel in meinem Quellfeld in der Datenüberprüfung zu verwenden, erhalte ich eine Fehlermeldung (“Es gibt ein Problem mit dieser Formel…”), obwohl die Formel korrekt funktioniert, wenn sie in einer Zelle selbst steht. Hat jemand eine Idee, warum das passieren könnte?

Antwort 1:
Sie können versuchen, das Trennzeichen der Formel zu ändern: Semikolon ( ; ) mit Koma ( , )


Frage 2:
Gibt es eine Möglichkeit, eine relationale Datenbank in Excel zu erstellen, in der ich verschiedene Eintragszellen behalten und sie mit einem anderen Satz von Einträgen verknüpfen kann. z.B. : Kategorie in einer Tabelle verknüpft mit angebotenen Produkten in einer anderen Tabelle. Wenn ja, bitte antworten?

Antwort 2:
Es ist möglich. Ich sehe zwei oder mehrere Möglichkeiten: (a) fomelhaft, größtenteils mit referenzierenden Funktionen, z.B. Index, Lookup etc. mit logischen Funktionen wahrscheinlich, oder (b) programmatisch mit Excel VBA.


Frage 3:
Ich habe mich gefragt, wie oder ob es überhaupt möglich ist, die Funktion “Dynamische Auswahlliste” für verschiedene Tabellenblätter zu verwenden? Um meine Tabellenblätter sauber zu halten, behalte ich die Informationen, die zum Befüllen der verschiedenen Pull-Down-Listen verwendet werden, auf einem Blatt, verwende sie aber auf einem anderen

Antwort 3:
Auf jeden Fall, JA! Stellen Sie sicher, dass Sie den Quellort nicht ändern, wenn Sie in Zukunft keine Fehler bekommen wollen.

Tags: excelwindows