Sie können das Video nicht sehen, weil Sie der Anzeige von Youtube nicht zugestimmt haben.
Die Einstellungen kann in der Datenschutzerklärung geändert werdeb.
Die Ausgabe von Youtube-Videos kann auch direkt hier erlaubt werden.
Zunächst einmal: Was ist eine kaskadieren Liste?
Eine kaskadieren Liste bedeutet, dass man aus einem ersten Dropdown-Feld einen Wert auswählt und in einem zweiten Dropdown-Feld dazu passende Werte ausgegeben werden.
In diesem Beispiel soll der Benutzer zuerst die Kategorie auswählen (Zelle F4) und anschließend einen Artikel aus dieser Kategorie (Zelle F7). Die Herausforderung besteht also darin, dass das zweite Listenfeld nur solche Artikel ausgeben soll, die zu der im erstes Listenfeld gewählten Kategorie gehören.
Erstellen von Dropdown-Feldern
Ein Dropdown-Feld erstellen wir so:
Wir wählen zunächst die Zelle aus, in der der Dropdown realisiert werden soll. Anschließend gehen wir über die Registerkarte Daten auf Datenüberprüfung, wählen unter Zulassen den Wert "Liste" aus und markieren anschließend unter Quelle den Bereich, dessen Werte in der Liste angezeigt werden sollen.
Auswahl der Kategorien (1. Dropdown-Feld)
Das Dropdown-Feld zeigt die Werte aus dem Bereich A2:A9. In der Datenüberprüfung ist als Quelle für die Liste daher folgende Formel angegeben:
=$A$2:$A$9
Auswahl der Artikel (2. Dropdown-Feld)
Auch hier ist mittels Datenüberprüfung ein Dropdown-Feld erzeugt worden. Allerdings ist die Formel schon etwas komplexer. Sie lautet:
Wir schauen uns die einzelnen Bestandteile der Formel der Reihe nach an:
Die Funktion BEREICH.VERSCHIEBEN
Die Funktion gibt uns den Bezug auf jene Artikel, die in die zuvor gewählte Kategorie gehören. Dazu muss zunächst ein Startbezug gewählt werden (die Zelle in Spalte C, in welcher die gewählte Kategorie zum ersten Mal steht), der dann um eine Spalte nach rechts verschoben und um eine bestimmte Anzahl von Zeilen erweitert wird.
Das Argument Bezug
Hier wird der Startpunkt ermittelt. Das machen wir über folgende Formel:
INDIREKT("C"&VERGLEICH(F4;C1:C78;0))
Lassen wir kurz die Funktion INDIREKT außer Acht. Dann steht dort:
"C"&VERGLEICH(F4;C1:C78;0)
Der Buchstabe C wird verkettet mit dem Wert, den die Funktion VERGLEICH ermittelt. Die Funktion durchsucht den Bereich C1:C78 nach der gewünschten Kategorie (F4 ist die Zelle mit dem ersten Dropdown-Feld, in dem die Kategorie gewählt wurde). Die Funktion gibt die Zeilennummer des Bereichs zurück. Das wäre in unserem Beispiel die Zeilennummer 8.
C wird verkettet mit 8, herauskommt C8. Damit Excel diesen als Text gespeicherten Zellenbezug auch wirklich als Zellen Bezug verstehen kann, müssen wir ihn von der Funktion INDIREKT in eine richtigen Zellbezug umwandeln. Die Funktion INDIREKT sorgt also nur dafür, dass Excel eine als Text gespeicherten Zellenbezug verstehen kann.
Das Argument Zeilen
Dieses Argument von BEREICH.VERSCHIEBEN gibt an, um wie viele Zeilen der Bezug verschoben werden soll. Positive Zahlen verschieben nach unten, negative Zahlen oben.
Die 0, die ich hier eingetragen habe, bewirkt dass der Bezug gar nicht verschoben wird.
Das Argument Spalten
Dieses Argument gibt nun an, um wie viele Spalten der Bezug verschoben werden soll. Auch hier können positive oder negative Zahlen angegeben werden. Positive Zahlen verschieben den Bezug nach rechts, negative Zahlen links.
Ich habe eine 1 eingegeben. Das bewirkt, dass der Bezug um eine Spalte nach rechts verschoben wird. Aus dem Startpunkt C8 wird jetzt also D8.
Das Argument Höhe
Dieses Argument gibt an, wie hoch der Bezug sein soll. Positive Zahlen erweitern den Bezug, negative Zahlen reduzieren ihn. Der Bezug muss um die Anzahl der Artikel aus der gewünschten Kategorie erweitert werden. Diese Zahl ändert sich natürlich mit jeder gewählten Kategorie und muss daher dynamisch berechnet werden. Dazu nutze ich ganz einfach die Funktion ZÄHLENWENN.
ZÄHLENWENN(C2:C78;F4)
Sie durchsucht den Bereich C2:78 und zählt alle Zellen, die das Wort Getränke enthalten (noch mal zur Erinnerung: in F4 steht die gewählte Kategorie. Diese lautet aktuell Getränke). In unserem Beispiel ermittelt die Funktion den Wert 11. Der von C8 auf D8 verschobene Bezug wird nun um 11 Zeilen nach unten erweitert und umfasst nun D8:D19.
Ich hoffe Sie mit meinen Blog-Artikeln bei der täglichen Arbeit mit Excel unterstützen zu können. Wenn Sie mehr spannende Tricks zum Thema Excel lernen wollen, empfehle ich Ihnen meinen Excel-Aufbaukurs.