In diesem Artikel erfahren Sie, wie Sie eine dynamische Dropdown-Liste in Microsoft Excel erstellen.
Wie wir wissen, verbessert die Datenvalidierungsfunktion die Effizienz der Dateneingabe in Excel und reduziert Fehler und Tippfehler. Es wird verwendet, um den Benutzer für die Art der Daten einzuschränken, die in den Bereich eingegeben werden können. Im Falle einer ungültigen Eingabe wird eine Nachricht angezeigt und der Benutzer kann die Daten basierend auf der angegebenen Bedingung eingeben.
Eine dynamische Dropdown-Liste in Excel ist jedoch eine bequemere Möglichkeit, Daten auszuwählen, ohne Änderungen an der Quelle vorzunehmen. Mit anderen Worten, sagen Sie, Sie werden die Liste häufig aktualisieren, die Sie in der Dropdown-Liste aufgenommen haben. Und Sie denken, wenn Sie Änderungen an der Liste vornehmen, müssen Sie die Datenvalidierung jedes Mal ändern, um die aktualisierte Dropdown-Liste zu erhalten.
Aber hier kommt das dynamische Dropdown ins Spiel, und es ist die beste Option, Daten auszuwählen, ohne Änderungen an der Datenvalidierung vorzunehmen. Es ist der normalen Datenvalidierung sehr ähnlich. Wenn Sie jedoch die Liste aktualisieren, ändert sich die dynamische Dropdown-Liste, um diese Aktion aufzunehmen, während dies bei der normalen Dropdown-Liste nicht der Fall ist.
Nehmen wir also ein Beispiel und verstehen, wie wir eine dynamische Dropdown-Liste erstellen:
Wir haben eine Liste von Produkten in Spalte A und wir haben die dynamische Dropdown-Liste von Produkten in Zelle D9.
Tabellenname mit indirekter Funktion
Zuerst erstellen wir eine Tabelle; Befolgen Sie die unten angegebenen Schritte:-
- Wählen Sie den Bereich A8: A16
- Gehen Sie zu Registerkarte einfügen und klicken Sie dann auf Tabelle
- Nachdem Sie auf die Option „Tabelle“ geklickt haben, öffnet sich ein Tabellenfenster
- Wählen Sie dann den Bereich aus, für den wir die Tabelle A8:A17 einfügen möchten
- Klicken Sie auf OK
- Jetzt klicken wir auf OK
- Sie können sehen, dass dieser Bereich in eine Tabelle umgewandelt wurde, und die Kopfzeile dieser Tabelle hat auch eine Filter-Dropdown-Option
Notiz: - Wenn wir am Ende der Liste ein Produkt oder einen Artikel hinzufügen, wird die Tabelle automatisch erweitert, um die neuen Produkte oder Artikel aufzunehmen.
Jetzt erstellen wir die dynamische Dropdown-Liste in Zelle D9, befolgen Sie die unten angegebenen Schritte:-
- Wählen Sie die Zelle D9
- Öffnen Sie das Dialogfeld Datenvalidierung durch Drücken der Tasten ALT+D+L
- Wählen Sie in der Dropdown-Liste Zulassen die Option Liste
- Und dann geben Sie diese Funktion =INDIREKT("Tabelle1") in der Registerkarte "Quelle" ein
- Klicken Sie auf OK
Notiz: - Wenn wir auf OK klicken, erscheint in Excel ein Fenster mit der Meldung, dass mit der Eingabe etwas nicht stimmt. Das liegt daran, dass Excel keine sich selbst erweiternden Tabellen direkt in der Datenvalidierung akzeptiert.
Fügen Sie nun neue Produkte in der Produktliste hinzu.
Wir können im obigen Bild sehen, dass ein neu hinzugefügtes Produkt in der Dropdown-Liste angezeigt wird.
2nd Beispiel:-
In diesem Beispiel lernen wir, wie man den Tabellennamen als Bereichsnamen angibt
Wir haben bereits den Tabellennamen, aber hier müssen wir den Namen dieser Tabelle definieren, um die dynamische Dropdown-Liste zu erhalten; Befolgen Sie die folgenden Schritte: -
- Wählen Sie die Zelle D10
- Gehen Sie zum Tabellenbereich, und außer der Kopfzeile wählen wir den Bereich vom ersten Produkt bis zum letzten Produkt aus
- Gehen Sie zum Namensfeld und geben Sie den Kurznamen "tablerange" ein. Drücken Sie die Eingabetaste
- Nachdem wir die Eingabetaste gedrückt haben, sehen wir, dass sich im Namensfeld nichts geändert hat
- Klicken Sie auf die Dropdown-Listenoption, um alle verfügbaren benannten Bereiche anzuzeigen
- In der Dropdown-Liste sehen wir auch den Namen, den wir gerade für diese Tabelle definiert haben
- Nun gehen wir zur Datenvalidierung und geben in „Source“ den „tablerange“ ein.
Hinweis:- Wenn Sie sich nicht mehr erinnern, welchen Namen Sie diesem Bereich gegeben haben, können Sie die Taste F3 drücken und ein Fenster öffnet sich, um Ihnen alle verfügbaren benannten Bereiche vorzuschlagen.
- Gehen Sie nun zur Registerkarte "Input Message" und geben Sie im Titel "Select Product" ein und dann im Nachrichtentext: "Bitte wählen Sie Ihr Produkt aus der Liste aus"
- Gehen Sie nun zur Registerkarte "Fehlerbenachrichtigung", und dort schreiben wir im Titel "Ungültiges Produkt" und in der Fehlermeldung geben wir "Sie haben ein falsches Produkt eingegeben" ein
- Klicken Sie auf OK
- Zelle D10 mit Eingabenachricht zusammen mit Dropdown-Liste
- Wenn wir nun ein Produkt zur Liste hinzufügen, wird es automatisch in der Dropdown-Liste angezeigt
Aber was passiert, wenn wir eine Zelle nach der letzten Zelle überspringen und dann ein neues Produkt oder einen neuen Artikel hinzufügen? Wie Sie sehen, hat sich das Tischsortiment dieses Mal nicht erweitert, sondern das neu hinzugefügte Produkt hat ein allgemeines Format. Wird es also in der Dropdown-Liste angezeigt oder nicht? Um dies zu überprüfen, können wir, wenn wir zu Zelle D10 gehen und die Dropdown-Liste überprüfen, dieselbe alte Dropdown-Liste ohne neues Produkt sehen. Dies liegt daran, dass der Tabellenbereich nach der allerletzten Zelle nichts gefunden hat und daher der Bereich nicht verbraucht wurde.
3rd Beispiel:-
In den nächsten beiden Methoden lernen wir, wie wir unsere Dropdown-Liste dynamischer gestalten können, indem wir die Funktion OFFSET und COUNTA verwenden.
Befolgen Sie die unten angegebenen Schritte:-
- Wählen Sie Zelle D11 aus und drücken Sie ALT + D + L
- Das Dialogfeld Datenvalidierung wird geöffnet
- Wählen Sie nun die Liste in der Option "Zulassen" aus
- Geben Sie dann in der Option Quelle die folgende Formel ein:
=OFFSET($A$9,0,0,COUNTA($A:$A),1)
Formelerklärung:- Wir haben A9 ausgewählt, das erste Produkt im Sortiment, und geben dann 0 auf der 2 einnd Argument, da wir die Zeile nicht vom Anfangspunkt verschieben möchten; dann wieder 0 in der 3rd Argument, da wir hier keine Änderungen in der Anzahl der Spalten sowie am Ausgangspunkt wünschen. Und dann haben wir die COUNTA-Funktion eingegeben und die gesamte Spalte A ausgewählt. Dieses Argument überprüft die Höhe in der Anzahl der Zeilen, um die nicht leere Anzahl zurückzugeben. Es erweitert den Bereich, wenn Änderungen im Bereich vorgenommen werden.
Und das letzte Argument „Width“ ist ein optionales Argument. Es ist die Breite in Anzahl der Spalten. Wir können es entweder überspringen oder hier vorerst 1 eingeben. Wenn wir überspringen, wird standardmäßig die Breite des zurückgegebenen Bereichs berücksichtigt, den wir im Argument angegeben haben, und dann schließen wir die Klammern.
- Nachdem wir auf OK geklickt haben, sehen wir in Zelle D11 eine Dropdown-Liste
- Es zeigt die Liste einschließlich des Leerzeichens und dann die Produkte, die wir hinzugefügt haben
4NS Beispiel:-
In diesem Beispiel verwenden wir die Funktion, um den Namen zu definieren.
Führen Sie die folgenden Schritte aus, um den Bereichsnamen zu definieren:
- Drücken Sie STRG + F3, das Dialogfeld Name Manager wird angezeigt
- Klicken Sie auf Neu
- Definieren Sie den Bereichsnamen „ProdName“ und geben Sie die folgende Formel ein:
=OFFSET('Dynamische Dropdown-Liste mit DV'!$A$9,0,0,COUNTA('Dynamische Dropdown-Liste mit DV'!$A:$A))
- Klicken Sie auf OK
- Öffnen Sie den Datenvalidierungsdialog durch Drücken der Taste Alt + D + L
- Wählen Sie in der Dropdown-Liste Zulassen Liste aus
- Geben Sie =ProdName in die Registerkarte Quelle ein
- Klicken Sie auf OK
- Wenn wir nun etwas in der Liste hinzufügen, wird dasselbe in der Liste angezeigt
So können Sie die dynamische Liste für jedes Produkt oder jeden Artikel mit verschiedenen Methoden mithilfe der Datenvalidierung abrufen. Das ist alles für jetzt. Im nächsten Video dieser Serie erklären wir, wie Sie die abhängige Dropdown-Liste mit verschiedenen Methoden in Excel erstellen.
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