Excel 2016 bietet dank seiner zahlreichen Funktionen und Formeln eine Vielzahl von Optionen, mit denen wir Daten auf einfache, aber professionelle Weise steuern können. Mit Excel 2016 verfügen wir über verschiedene Ressourcen wie Grafiken , Pivot-Tabellen und Pivots. Eine der umfassendsten Funktionen sind jedoch die Dropdown-Listen, da diese uns die Möglichkeit geben, die von Benutzern eingegebenen Daten zu steuern.
In diesem Fall haben wir folgende Daten:
Wir haben dieses Blatt Listen genannt. In diesem Blatt nehmen wir die erforderlichen Konfigurationen vor, um die abhängigen Dropdown-Listen auf anderen Seiten dieses Buches zu erstellen .
1. Bereiten Sie Excel-Daten vor
Wie wir sehen, haben wir eine Liste von Ländern mit Städten von jedem von ihnen erstellt. Daher besteht der erste Schritt darin, eine Liste von eindeutigen Ländern zu erstellen, und wir werden Folgendes tun: Wir haben die Daten aus Spalte A in eine andere Spalte kopiert, In diesem Fall Spalte E, und sobald wir sie ausgewählt haben, gehen wir zur Registerkarte Daten, Gruppe Datentools und klicken dort auf die Schaltfläche Duplikate entfernen.
Nun erstellen wir mit den Daten eindeutiger Länder eine neue Liste mit dem Namen Land. Dazu wählen wir die Zellen aus, die die Daten enthalten, und geben den Namen in das Namensfeld oben ein.
Als nächstes haben wir die Spalte A zur besseren Kontrolle in aufsteigender Reihenfolge angeordnet:
Als nächstes ordnen wir die Bereiche den Städten entsprechend ihrem jeweiligen Land zu. Dazu müssen wir in jedem Land den Zellenbereich auswählen und in der Namenstabelle das jeweilige Land eintragen.
Wir müssen bedenken, dass der Name des Ranges dem Land entsprechen muss, da dies die Verknüpfungen zwischen beiden Listen sind. Um die von uns erstellten Bereiche anzuzeigen, wechseln Sie zur Registerkarte Formeln, Gruppe Definierte Namen, und wählen Sie dort die Option Namensmanager aus
Im Falle eines Fehlers können wir die Option Ändern verwenden und die erforderlichen Anpassungen vornehmen.
2. Erstellen Sie Excel-Dropdown-Listen
Dazu erstellen wir ein neues Blatt im Buch und suchen es in Zelle A2. Dort wechseln wir zur Registerkarte Daten, Gruppe Datentools, und wählen die Option Datenüberprüfung aus
Bei Auswahl dieser Option wird der folgende Assistent angezeigt, in dem wir in der Option Zulassen festlegen müssen. Im Feld Ursprung geben wir den Namen des Bereichs ein, den wir im Anfangsblatt erstellt haben, wobei die Länderdaten durch das Symbol = vorangestellt werden. Sobald diese Werte definiert sind, klicken Sie auf OK
Wir können in Zelle A2 suchen, und automatisch wird die Dropdown-Liste angezeigt. Wenn wir darauf klicken, werden die verfügbaren Optionen angezeigt:
Als nächstes erstellen wir die abhängige Dropdown-Liste in Zelle B2 und markieren dafür diese Zelle und gehen auf die Registerkarte Daten / Datenwerkzeuge / Datenvalidierung. Im erweiterten Assistenten wählen wir erneut Liste im Feld Zulassen und im Feld Ursprung wir werden folgendes eingeben :
= INDIREKT (A2)
Die Funktion INDIRECT ist dafür verantwortlich, den Zellenbereich zu ermitteln, in dem der Name mit dem Wert der Zelle A2 übereinstimmt. In der Regel wird durch Drücken von OK eine Meldung mit dem folgenden Fehler generiert: “Der Ursprung wertet derzeit einen Fehler aus. Möchten Sie fortfahren?”, Da in Zelle A2 keine Daten ausgewählt sind.
Wenn Sie ein Land in Zelle A2 auswählen, werden die in Zelle B2 verfügbaren Optionen angezeigt:
Auf diese Weise haben wir in Excel 2016 eine abhängige Dropdown-Liste erstellt.
3. So bereinigen Sie die Auswahl in der abhängigen Liste
Ein häufiger Fehler bei dieser Art von Dropdown-Liste besteht darin, dass nach der erstmaligen Auswahl von Daten in Zelle B2 der Wert in Zelle A2 unverändert bleibt und nicht aktualisiert wird:
Wenn wir die Daten mit diesen Daten speichern, werden falsche Informationen gespeichert, die sich auf viele Aufgaben auswirken können. Um dieses Problem zu lösen, verfügt Excel 2016 nicht über einen Befehl, der die Informationen automatisch aktualisiert. Hierzu muss ein VBA-Code verwendet werden. Klicken Sie mit der rechten Maustaste auf den Namen des Blatts, in dem Sie die abhängige Dropdown-Liste erstellt haben, und wählen Sie die Option Code anzeigen aus
Das folgende Fenster wird angezeigt, in dem wir die Option Arbeitsblatt bzw. Ändern auswählen müssen:
Dort müssen wir folgenden Code eingeben:
Private Sub Worksheet_Change (ByVal Target As Range) Wenn Target = Range ("A2") Dann Range ("B2"). Value = "" End IfEnd Sub
Mit diesem Code haben wir Folgendes:
- Das Worksheet_Change-Ereignis wird jedes Mal aktiviert, wenn eine Änderung am Arbeitsblatt vorgenommen wird.
- Die Variable Target analysiert, ob die Änderung aus Zelle A2 stammt, und wenn sie positiv ist, wird Zelle B2 mit dem neuen Wert bereinigt.
4. Fügen Sie der Dropdown-Liste neue Daten hinzu
In diesem Fall wird die Stadt Malaga (Spanien) hinzugefügt, sodass eine neue Zeile mit den Daten hinzugefügt wird
Wir können sehen, dass Spanien jetzt 3 Städte hat (B6: B8). Wenn wir zur Registerkarte Formeln / Namen-Manager gehen, sehen wir Folgendes:
Beachten Sie, dass der Bereich Spanien den Bereich der Zellen B6: B7 aufweist, der angibt, dass er nicht aktualisiert wurde. Dazu müssen Sie auf die Schaltfläche Ändern klicken und den entsprechenden Bereich anpassen:
Wenn wir jetzt zu der Seite des Buches gehen, auf der wir die unabhängige Dropdown-Liste erstellt haben, werden die aufgetretenen Änderungen angezeigt:
Wir verstehen den Nutzen und den Umfang der unabhängigen Dropdown-Listen in Excel 2016.
Funktionen und Tricks Excel