Bisher haben wir in dieser Datenvalidierungsserie gelernt, normale Dropdown-Listen und dynamische Dropdown-Listen mit verschiedenen Techniken mit Datenvalidierung in Excel zu erstellen.
Und heute zeigen wir Ihnen in diesem Kapitel, wie Sie mit verschiedenen Methoden eine abhängige Dropdown-Liste in Microsoft Excel erstellen.
Die abhängige Dropdown-Liste wird auch als kaskadierende Datenvalidierung bezeichnet und begrenzt die Auswahl in einer Dropdown-Liste abhängig vom ausgewählten Wert in der anderen Zelle, die die Datenvalidierung enthält. Mit anderen Worten, es hängt von dem in der ersten Dropdown-Liste ausgewählten Wert ab, der die in der zweiten Dropdown-Liste anzuzeigenden Werte bestimmt.
Dies ist ein sehr häufiges Szenario bei der Arbeit mit großen Datenmengen oder einigen dynamischen Berichten, bei denen Sie 2nd Zelle zeigt eine Liste an, die von dem im ersten Dropdown ausgewählten Listenelement abhängt.
Wie wir wissen, gibt es in Excel viele Möglichkeiten, eine bestimmte Aufgabe zu erledigen, und auf ähnliche Weise gibt es viele Möglichkeiten, eine abhängige Datenvalidierung in Excel zu erstellen. Und heute werden wir 5 verschiedene Techniken zum Erstellen einer abhängigen Datenvalidierungsliste demonstrieren.
Die Rohdaten können in beliebiger Reihenfolge oder Format vorliegen und Sie können die Daten oder das Format jedes Mal nicht ändern, um das zu erhalten, wonach Sie suchen.
Wir haben also einen Datensatz genommen, jedoch in 3 verschiedenen Formaten, um die abhängige Dropdown-Liste zu erhalten. Und, wie Sie sehen können, befinden sich unsere Daten auf der linken Seite von Spalte A bis Spalte E, und wir haben unsere erwartete Ausgabe auf der rechten Seite, die sich in Spalte J und K befindet. Spalte J wird die primäre Validierung haben Liste, während die Spalte K abhängig ist und die Werte abhängig von dem in Spalte J ausgewählten Wert anzeigt.
1NS Beispiel:-
2nd Beispiel:-
3rd Beispiel:-
1NS Beispiel:-
Wir haben eine Produktliste für jeden Produktcode von Spalte A8 bis E13. Und wir möchten den Produktcode in J10 auswählen, dann je nach ausgewähltem Produktcode einen Produktnamen in Zelle K10.
Erste Methode:-
Die erste Methode ist sehr einfach und kurz und erfordert nur 3 Schritte, um die entsprechende Dropdown-Liste zu erhalten. Dies funktioniert jedoch nur so lange erfolgreich, bis Sie keine Änderungen an Ihrem Sortiment vornehmen. Nachdem Sie Ihre Daten geändert haben, müssen Sie zuerst den benannten Bereich ändern, um die aktualisierte kaskadierende Datenvalidierung zu erhalten.
Befolgen Sie die unten angegebenen Schritte:-
- Wählen Sie die gesamte Tabelle von A8 bis E13
- Gehen Sie dann auf die Registerkarte „Formeln“ und klicken Sie unter der Kategorie „Definierte Namen“ auf „Aus Auswahl erstellen“.
- Sie können auch die Tastenkombination STRG+UMSCHALT+F3 verwenden
- Das Dialogfeld Namen aus Auswahl erstellen wird angezeigt
- Es fordert Sie auf zu bestätigen, welche Zeilen und Spalten verwendet werden sollen, um die Namen für andere Zeilen und Spalten zu erstellen. Wir bestätigen, dass Sie die "obere Reihe" verwenden, um die Namen zu erstellen, und deaktivieren Sie die 2nd Option und dann klicken wir auf OK
Notiz: - Leerzeichen und andere Sonderzeichen außer Unterstrich und Punkt sind als Namen nicht erlaubt. Standardmäßig wird es in einen Unterstrich umgewandelt. Verwenden Sie daher Unterstrich und Punkt, um Wörter zu trennen. Außerdem darf der erste Buchstabe keine Zahl sein; es muss ein Buchstabe, ein Unterstrich oder ein umgekehrter Schrägstrich sein.
- Um nun zu bestätigen, dass jeder Bereich einen Namen hat, gehen wir zum „Namensmanager“ (drücken Sie STRG + F3)
- Dort können wir alle 5 verfügbaren benannten Bereiche sehen
- Und wir können auch sehen, dass jeder Bereichsname einen Unterstrich anstelle eines Leerzeichens in der Mitte der Zeichenfolge hat
Jetzt erstellen wir eine Dropdown-Liste:-
- Wählen Sie Zelle J10 aus und drücken Sie ALT++D+L, um das Dialogfeld Datenvalidierung zu öffnen
- Wählen Sie Liste > und geben Sie dann den Bereich A8:E8 in der Registerkarte Quelle ein
- Klicken Sie auf OK
- Jetzt erstellen wir eine abhängige Liste in Zelle K10
- Öffnen Sie das Dialogfeld Datenvalidierung durch Drücken der Tasten ALT+D+L
- Wählen Sie Liste, geben Sie in der Quelle diese Funktion ein :- =INDIRECT(SUBSTITUTE($J$10," ","_"))
Bei der Datenvalidierung haben wir zum Erstellen der abhängigen Liste die INDIRECT-Funktion verwendet, um den Wert basierend auf der primären Datenvalidierungsliste zurückzugeben. Und um den Unterstrich durch Leerzeichen zu ersetzen, verwenden wir die Funktion SUBSTITUTE innerhalb der Funktion INDIRECT.
- Klicken Sie auf OK
Wenn wir einen Produktcode in Zelle J10 auswählen, wird die Produktliste des ausgewählten Produktcodes in Zelle K10 angezeigt. Zum Beispiel: - Wir haben ETV 501 ausgewählt, jetzt können Sie sehen, dass die Liste der abhängigen Produkte in Zelle K10 erscheint
Notiz: - Immer wenn Sie den Produktnamen und den Produktcode hinzufügen, die nicht in der Liste erscheinen.
Zum Beispiel: - Wir haben Produkt 26 unter dem Produktcode ETV 505 hinzugefügt, aber wenn wir das Produkt ETV 505 auswählen, wird das hinzugefügte Produkt nicht in der Dropdown-Liste angezeigt.
So können Sie mit einfacher Technik in nur 3 einfachen Schritten eine abhängige Dropdown-Liste erstellen.
2nd Beispiel:-
In diesem Beispiel sehen wir, wie Sie eine abhängige Dropdown-Liste erhalten, wenn Sie Ihre Daten haben, wie in dieser vertikalen Tabelle gezeigt.
Wir werden zwei verschiedene Methoden verwenden, um eine abhängige Dropdown-Liste zu erstellen. Beides sind fast ähnliche Techniken. Der eine ist jedoch ohne den benannten Bereich und der andere hat den benannten Bereich.
1NS Methode:-
Um dasselbe zu tun, verwenden wir die Funktionen OFFSET, MATCH & COUNTIF zusammen.
Da wir wissen, dass die OFFSET-Funktion verwendet wird, um den Dynamikbereich zu erstellen, verwenden wir daher zum Erstellen der Liste "Dynamische Datenvalidierung" die OFFSET-Funktion, um den Dynamikbereich zurückzugeben.
MATCH wird verwendet, um die relative Position eines Elements in einer Liste in Excel zurückzugeben. Und hier hilft es uns, die in der primären Dropdown-Liste ausgewählte Kategorie in unserem Bereich auf dem Blatt abzugleichen, und es wird eine Zahl zurückgegeben.
Und ZÄHLENWENN wird verwendet, um die Anzahl der Zellen zu ermitteln, die den Kriterien entsprechen. Und hier werden wir dies verwenden, um die Anzahl der anzuzeigenden Zeilen zu zählen, indem wir die ZÄHLENWENN-Funktion verwenden.
Befolgen Sie die unten angegebenen Schritte:-
- Wählen Sie die Zelle J21 aus, in der wir unsere primäre Datenvalidierungsliste erstellen
- Drücken Sie die Tasten ALT+D+L, um das Dialogfeld Datenvalidierung zu öffnen
- Liste aus Zulassungskategorie auswählen
- Klicken Sie auf die Registerkarte Quelle und wählen Sie den Bereich von B20: B24
- Und klicke auf OK
- Gehen Sie zu Zelle K21 und öffnen Sie das Dialogfeld zur Datenvalidierung erneut
- Dann wählen wir Liste und geben in Quelle die folgende Funktion ein:
- =OFFSET($E$19,MATCH($J$21,$D$20:$D$32,0),0,COUNTIF($D$20:$D$32,$J$21))
- Klicken Sie auf OK
- In Zelle K21 können wir alle entsprechenden Werte des ausgewählten Produktcodes sehen:
So können Sie die abhängige Liste erhalten, indem Sie Zellbezüge in der Funktion verwenden.
2nd Methode:-
In der nächsten Methode verwenden wir den benannten Bereich in derselben Funktion, um die kaskadierende Datenvalidierung zu erhalten. Zuerst müssen wir die dynamische Liste für den Produktcode erstellen. Falls den Daten ein neues Produkt hinzugefügt wird, sollte das Dropdown-Menü aktualisiert werden, um dasselbe anzuzeigen.
Um dasselbe zu tun, befolgen Sie die unten angegebenen Schritte:-
- Wählen Sie B19 und drücken Sie dann STRG + F3, um das Fenster „Namensmanager“ zu öffnen
- Jetzt klicken wir auf „Neu“ und das Dialogfeld „Name definieren“ erscheint
- Wir können sehen, dass der Name bereits im Namensfeld angezeigt wird - das liegt daran, dass wir B9 ausgewählt haben, bevor wir das Fenster "Namensmanager" geöffnet haben. Und da B19 Text enthält, können wir ihn bei Bedarf in einen anderen Namen ändern.
- Geben Sie die folgende Erwähnungsformel ein:
=OFFSET('DependentDropDownList'!$B$20,0,0,COUNTA('DependentDropDownList'!$B$20:$B$32))
- Klicken Sie auf OK
Da wir eine dynamische Liste für einzigartige Produkte erstellt haben, erstellen wir jetzt einen dynamischen Bereich für den Produktcodebereich, der sich in Spalte D befindet.
Befolgen Sie die gleichen Schritte, die wir für ein einzigartiges Produkt befolgt haben:
- Wählen Sie die Zelle D19 aus, öffnen Sie das Dialogfeld Namen definieren
- Sie werden feststellen, dass der Name bereits da ist
- Geben Sie in bezieht die folgende Formel ein:-
=OFFSET('Abhängige Dropdown-Liste'!$D$20,0,0,COUNTA('Abhängige Dropdown-Liste'!$D$20:$D$35))
- Klicken Sie auf OK
- Jetzt sind beide Dynamikbereiche fertig. Also gehen wir zu J22 und drücken "ALT + D + L" und wählen "Liste"
- In der Quelle haben wir den benannten Bereich, den wir für "Unique Product Code" definiert haben, also drücken wir F3, um alle verfügbaren benannten Bereiche anzuzeigen
- Wir können den benannten Bereich "Eindeutiger Produktcode" sehen, also klicken wir darauf und dann auf OK und drücken Sie die Eingabetaste
- In dem Moment, in dem wir die Eingabetaste drücken, erhalten wir in Zelle J22 einen Dropdown-Pfeil, der die Liste der eindeutigen Produktcodes enthält
- Wählen Sie Zelle K22 und öffnen Sie das Dialogfeld "Datenvalidierung".
- Wir werden die gleiche Funktion verwenden, die wir in der letzten Methode verwendet haben, jedoch mit dem benannten Bereich
- Wählen Sie die Liste aus und geben Sie dann in der Quelle die folgende Formel ein:
=OFFSET($E$19,MATCH($J$22,Produktcode,0),0,ZÄHLENWENN(Produktcode,J22))
- Klicken Sie auf OK
- Jetzt haben wir das primäre Dropdown-Menü sowie die untergeordnete Produktliste
- Wählen Sie das Produkt „ETV-101“ von J22 aus, und in K22 können wir nur die Namen sehen, die unter dieses Produkt „ETV-101“ fallen. Und wenn wir ein Produkt („ETV-103) in J22 ändern, zeigt K22 die entsprechenden Werte für diesen Code an
Jetzt werden wir sehen, was passiert, wenn wir der Liste einen neuen Produktcode hinzufügen? Werden diese Dropdown-Listen aktualisiert?
Fügen wir der Liste ein neues Produkt hinzu; Befolgen Sie die unten angegebenen Schritte: -
- Produktcode in die Liste von Unique_Prod_Code hinzufügen
- Fügen Sie auch den Product_Code und Product_Name in den Daten hinzu:-
- Überprüfen Sie nun die Dropdown-Liste - der hinzugefügte Produktcode und der Name werden angezeigt
3rd Beispiel:-
Wir haben die dynamischen Überschriften direkt aus der Tabelle und werden neue Produkte ins Sortiment aufnehmen. Die Tabelle hat das gleiche Format, das wir für 1 . verwendet habenNS Methode.
4NS Methode:-
Befolgen Sie die unten angegebenen Schritte:-
- Wählen Sie die Überschrift A40:E40
- Erstellen Sie zuerst den dynamischen Bereich für Überschriften, öffnen Sie das Dialogfeld "Namen definieren".
- Schreiben Sie „Überschrift“ an die Stelle des Namens und geben Sie dann in „bezieht sich auf“ die unten genannte Formel ein:
- Geben Sie die folgende Funktion ein:-
- =OFFSET('Abhängige Dropdown-Liste'!$A$40,,,,COUNTA('Abhängige Dropdown-Liste'!$40:$40))
- Klicken Sie auf OK
- Der dynamische Bereich „Heading“ ist jetzt fertig
Und jetzt erstellen wir den benannten Bereich für jede Überschrift. Führen Sie die folgenden Schritte aus:
- Wählen Sie die Tabelle von A40 bis E50
- STRG + UMSCHALT + F3-Tastenkombination
- Wir deaktivieren die 2nd Möglichkeit
- Und bevor wir auf OK klicken, stellen Sie sicher, dass die 1NS Option „Obere Reihe“ ist ausgewählt
- Jetzt sind wir mit beiden Sortimenten fertig
Jetzt bereiten wir die Eltern-Dropdown-Liste vor
- Wählen Sie die Zelle J42
- Öffnen Sie das Dialogfeld Datenvalidierung
- Nachdem wir „Liste“ ausgewählt haben, drücken wir F3 in der Quelle, um den benannten Bereich für Überschriften zu erhalten. Wir klicken auf „Überschrift“ und dann auf OK und drücken die Eingabetaste. Wir haben jetzt die Elternliste in J42
- Um die Liste der Artikeldetails zu erstellen, wählen Sie cellK42
- Öffnen Sie das Dialogfeld Datenvalidierung durch Drücken der Tasten ALT+D+L
- Wählen Sie Liste und geben Sie die folgende Funktion in der Registerkarte Quelle ein:-
- =OFFSET(INDIREKT(ERSETZEN($J$42," ","_")),,,ANZAHL(INDIREKT(ERSETZEN($J$42," ","_"))))
- Klicken Sie auf OK
Wählen Sie nun ein Element im J42 aus, sagen wir, wir wählen "Element 01" und sehen sich die Dropdown-Liste K42 an. Und wie bei den vorherigen 3 Methoden haben wir auch hier eine abhängige Liste.
Was gibt es Neues? Im ersten Beispiel konnten Sie der Liste kein Produkt hinzufügen, aber hier können Sie jedes neue Produkt hinzufügen. Angenommen, wir fügen diesem Artikel ein neues Produkt hinzu. Wir gehen zur A45, geben „ETV-501 Prod 05“ ein und kehren dann zur K42 zurück und los geht's. Sie sehen, das neue Produkt wurde hinzugefügt.
- Fügen Sie nun einige Produkte unter dem neuen Artikel hinzu
Wenn wir „Artikel 06“ auswählen, gehen wir zu K42 und klicken auf die Dropdown-Liste. Überraschenderweise passiert nichts, wenn wir auf den Dropdown-Pfeil klicken. Das liegt daran, dass wir alles dynamisch erstellt haben und vergessen haben, einen dynamischen Bereich für die Tabelle zu erstellen, weshalb die Produkte nicht in der untergeordneten Liste angezeigt werden.
Dazu müssen wir verschiedene Techniken anwenden. Dafür gibt es zwei Methoden. Sie können entweder die Tabelle erstellen oder einfach nur die OFFSET-Funktion verwenden. Und in der nächsten Methode verwenden wir die OFFSET-Funktion und sehen den Trick, den Tabellenbereich ebenfalls zu erweitern.
- Also gehen wir zuerst zu J43 und drücken "ALT + D + L"
- Wir wählen „Liste“ und dann in der Quelle drücken wir F3 und wählen „Überschrift“ und klicken auf OK und drücken dann die Eingabetaste
- Nun gehen wir zu K43, und nachdem wir "Liste" ausgewählt haben, gehen wir zu "Quelle" und geben die unten genannte Funktion ein
=OFFSET($A$40,1,MATCH($J$43,$40:$40,0)-1,COUNTA(OFFSET($A$40,1,MATCH($J$43,$40:$40,0)-1,1000 ,1)))
- Klicken Sie auf OK
Jetzt gehen wir zurück und wählen „Element 06 in der Zelle J43“, kehren zu K43 zurück und klicken auf den Dropdown-Pfeil. Aber diese Zeitliste zeigt Produkte, die wir für neue Artikel hinzugefügt haben. Und wir wählen das erste Produkt „ETV-506 Prod 01“ aus.
Auf diese Weise können Sie die abhängige Dropdown-Liste mit verschiedenen Methoden für jeden Datenformattyp erstellen.
Video: So erstellen Sie eine abhängige (kaskadierende) Dropdown-Liste in Excel mit 5 verschiedenen Techniken in Microsoft Excel
Klicken Sie auf den Videolink, um eine schnelle Referenz zur Verwendung zu erhalten. Abonniere unseren neuen Kanal und lerne weiter mit uns!
Wenn Ihnen unsere Blogs gefallen haben, teilen Sie sie mit Ihren Freunden auf Facebook. Und Sie können uns auch auf Twitter und Facebook folgen.
Wir würden uns freuen, von Ihnen zu hören, lassen Sie uns wissen, wie wir unsere Arbeit verbessern, ergänzen oder erneuern und für Sie verbessern können. Schreiben Sie uns auf der E-Mail-Site