Video ansehen - Erstellen einer abhängigen Dropdown-Liste in Excel
Eine Excel-Dropdown-Liste ist eine nützliche Funktion, wenn Sie Dateneingabeformulare oder Excel-Dashboards erstellen.
Es zeigt eine Liste von Elementen als Dropdown in einer Zelle an, und der Benutzer kann eine Auswahl aus der Dropdown-Liste treffen. Dies kann nützlich sein, wenn Sie eine Liste mit Namen, Produkten oder Regionen haben, die Sie häufig in eine Reihe von Zellen eingeben müssen.
Unten sehen Sie ein Beispiel für eine Excel-Dropdown-Liste:
Im obigen Beispiel habe ich die Elemente in A2:A6 verwendet, um eine Dropdown-Liste in C3 zu erstellen.
Lesen: Hier finden Sie eine detaillierte Anleitung zum Erstellen einer Excel-Dropdown-Liste.
Manchmal möchten Sie jedoch möglicherweise mehr als eine Dropdown-Liste in Excel verwenden, sodass die in einer zweiten Dropdown-Liste verfügbaren Elemente von der Auswahl in der ersten Dropdown-Liste abhängig sind.
Diese werden in Excel als abhängige Dropdown-Listen bezeichnet.
Unten ist ein Beispiel dafür, was ich mit einer abhängigen Dropdown-Liste in Excel meine:
Sie können sehen, dass die Optionen in Drop-Down 2 von der Auswahl in Drop-Down 1 abhängen. Wenn ich in Drop-Down 1 'Früchte' auswähle, werden mir die Fruchtnamen angezeigt, aber wenn ich in Drop-Down 1 Gemüse auswähle, dann mir werden die Gemüsenamen in Drop Down 2 angezeigt.
Dies wird in Excel als bedingte oder abhängige Dropdown-Liste bezeichnet.
Erstellen einer abhängigen Dropdown-Liste in Excel
Hier sind die Schritte zum Erstellen einer abhängigen Dropdown-Liste in Excel:
- Wählen Sie die Zelle aus, in der die erste (Haupt-) Dropdown-Liste angezeigt werden soll.
- Gehen Sie zu Daten -> Datenvalidierung. Dadurch wird das Dialogfeld zur Datenvalidierung geöffnet.
- Wählen Sie im Dialogfeld Datenvalidierung auf der Registerkarte Einstellungen die Option Liste aus.
- Geben Sie im Feld Quelle den Bereich an, der die Elemente enthält, die in der ersten Dropdown-Liste angezeigt werden sollen.
- OK klicken. Dadurch wird das Dropdown-Menü 1 erstellt.
- Wählen Sie den gesamten Datensatz aus (A1:B6 in diesem Beispiel).
- Gehen Sie zu Formeln -> Definierte Namen -> Aus Auswahl erstellen (oder verwenden Sie die Tastenkombination Strg + Umschalt + F3).
- Aktivieren Sie im Dialogfeld "Namen aus Auswahl erstellen" die Option Obere Zeile und deaktivieren Sie alle anderen. Dadurch werden 2 Namensbereiche erstellt („Obst“ und „Gemüse“). Der benannte Bereich Obst bezieht sich auf alle Früchte in der Liste und der benannte Bereich Gemüse bezieht sich auf alle Gemüse in der Liste.
- OK klicken.
- Wählen Sie die Zelle aus, in der die Dropdown-Liste Abhängig/Bedingt angezeigt werden soll (in diesem Beispiel E3).
- Gehen Sie zu Daten -> Datenvalidierung.
- Stellen Sie im Dialogfeld Datenvalidierung auf der Registerkarte Einstellungen sicher, dass Liste ausgewählt ist.
- Geben Sie im Feld Quelle die Formel =INDIREKT(D3) ein. Hier ist D3 die Zelle, die das Haupt-Dropdown enthält.
- OK klicken.
Wenn Sie nun die Auswahl in Drop-Down 1 treffen, werden die in Drop-Down-Liste 2 aufgelisteten Optionen automatisch aktualisiert.
Laden Sie die Beispieldatei herunter
Wie funktioniert das? - Die bedingte Dropdown-Liste (in Zelle E3) bezieht sich auf =INDIREKT(D3). Das heißt, wenn Sie in Zelle D3 „Früchte“ auswählen, verweist die Dropdown-Liste in E3 auf den benannten Bereich „Früchte“ (über die INDIREKTE Funktion) und listet daher alle Artikel in dieser Kategorie auf.
Wichtiger Hinweis: Besteht die Hauptkategorie aus mehr als einem Wort (z. B. 'Saisonal Fruits' statt 'Obst'), dann müssen Sie die Formel =INDIRECT(SUBSTITUTE(D3“, „“, _“)) anstelle des . verwenden oben gezeigte einfache INDIREKTE 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. Wenn Sie beispielsweise einen benannten Bereich mit „Seasonal Fruits“ erstellen, wird dieser im Backend Season_Fruits genannt. Die Verwendung der SUBSTITUTE-Funktion innerhalb der INDIRECT-Funktion stellt sicher, dass Leerzeichen sind in Unterstriche umgewandelt.
Inhalt der abhängigen Dropdown-Liste automatisch zurücksetzen/löschen
Wenn Sie die Auswahl getroffen haben und dann die übergeordnete Dropdown-Liste ändern, würde sich die abhängige Dropdown-Liste nicht ändern und wäre daher ein falscher Eintrag.
Wenn Sie beispielsweise „Obst“ als Kategorie auswählen und dann Apple als Element auswählen und dann zurückgehen und die Kategorie in „Gemüse“ ändern, wird in der abhängigen Dropdown-Liste weiterhin Apple als Element angezeigt.
Sie können VBA verwenden, um sicherzustellen, dass der Inhalt der abhängigen Dropdown-Liste zurückgesetzt wird, wenn die Haupt-Dropdown-Liste geändert wird.
Hier ist der VBA-Code zum Löschen des Inhalts einer abhängigen Dropdown-Liste:
Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Target.Column = 4 Then If Target.Validation.Type = 3 Then Application.EnableEvents = False Target.Offset(0, 1).ClearContents End If End If exitHandler: Application.EnableEvents = True Exit Sub End Sub
Die Anerkennung für diesen Code geht an dieses Tutorial von Debra zum Löschen abhängiger Dropdown-Listen in Excel, wenn die Auswahl geändert wird.
So funktioniert dieser Code:
- Kopieren Sie den VBA-Code.
- Gehen Sie in der Excel-Arbeitsmappe, in der Sie die abhängige Dropdown-Liste haben, zur Registerkarte Entwickler und klicken Sie in der Gruppe "Code" auf Visual Basic (Sie können auch die Tastenkombination - ALT + F11 verwenden).
- Im VB-Editor-Fenster links im Projekt-Explorer würden Sie alle Arbeitsblattnamen sehen. Doppelklicken Sie auf dasjenige mit der Dropdown-Liste.
- Fügen Sie den Code in das Codefenster rechts ein.
- Schließen Sie den VB-Editor.
Wenn Sie nun die Haupt-Dropdown-Liste ändern, wird der VBA-Code ausgelöst und der Inhalt der abhängigen Dropdown-Liste gelöscht (wie unten gezeigt).
Wenn Sie kein Fan von VBA sind, können Sie auch einen einfachen Trick zur bedingten Formatierung verwenden, der die Zelle hervorhebt, wenn eine Abweichung auftritt. Dies kann Ihnen helfen, die Abweichung visuell zu erkennen und zu korrigieren (wie unten gezeigt).
Hier sind die Schritte, um Nichtübereinstimmungen in den abhängigen Dropdown-Listen hervorzuheben:
- Wählen Sie die Zelle aus, die die abhängige(n) Dropdown-Liste(n) enthält.
- Gehen Sie zu Home -> Bedingte Formatierung -> Neue Regel.
- Wählen Sie im Dialogfeld "Neue Formatierungsregel" die Option "Formel verwenden, um zu bestimmen, welche Zellen formatiert werden sollen".
- Geben Sie im Formelfeld die folgende Formel ein: =ISERROR(SVERWEIS(E3,INDEX($A$2:$B$6,,MATCH(D3,$A$1:$B$1)),1,0))
- Stellen Sie das Format ein.
- OK klicken.
Die Formel verwendet die SVERWEIS-Funktion, um zu überprüfen, ob das Element in der abhängigen Dropdown-Liste ein Element aus der Hauptkategorie ist oder nicht. Ist dies nicht der Fall, gibt die Formel einen Fehler zurück. Dies wird von der ISERROR-Funktion verwendet, um TRUE zurückzugeben, was der bedingten Formatierung mitteilt, die Zelle hervorzuheben.
Die folgenden Excel-Tutorials könnten Ihnen auch gefallen:
- Extrahieren Sie Daten basierend auf einer Dropdown-Listenauswahl.
- Erstellen einer Dropdown-Liste mit Suchvorschlägen.
- Wählen Sie mehrere Elemente aus einer Dropdown-Liste aus.
- Erstellen Sie mehrere Dropdown-Listen ohne Wiederholung.
- Sparen Sie Zeit mit Dateneingabeformularen in Excel.
