TOP
Verknüpfte Dropdown-Listen
Beschreibung
In Excel geht das schnell und einfach Erstellen Sie eine Dropdown-Liste , aber haben Sie schon einmal versucht, eine abhängige Dropdown-Liste zu erstellen? Es gibt mehrere Möglichkeiten, eine solche Liste zu erstellen. Schauen wir uns diese also an.
Methode 1. INDIRECT-Funktion
Diese Technik basiert auf der Anwendung der Funktion INDIRECT, die eine einfache Sache tun kann: den Inhalt einer beliebigen angegebenen Zelle in eine Bereichsadresse umwandeln, die Excel versteht. Das heißt, wenn die Zelle den Text enthält „ А1 ", dann gibt die Funktion als Ergebnis einen Verweis auf die Zelle aus А1 . Wenn die Zelle das Wort „ Auto ", dann gibt die Funktion einen Verweis auf den benannten Bereich mit dem Namen aus Auto usw.
Nehmen Sie zum Beispiel diese Liste von Automodellen Toyota, Ford Und Nissan :
Wählen Sie die gesamte Liste der Toyota-Modelle aus (aus Zelle А2 bis zum Ende der Liste) und geben Sie diesem Bereich einen Namen Toyota im Menü Einfügen – Name – Zuweisen (Insert - Name - Define) . Dann wiederholen wir dasselbe mit den Listen Ford Und Nissan , wobei die Namen der Bereiche entsprechend angegeben werden Ford Und Nissan .
Beachten Sie bei der Namensvergabe, dass die Namen der Bereiche in Excel keine Leerzeichen und Satzzeichen enthalten dürfen und mit einem Buchstaben beginnen müssen. Wenn es also eine Lücke bei einer der Automarken gab (z. B Ssang Yong), dann müsste es in der Zelle und im Namen des Bereichs durch einen Unterstrich ersetzt werden (z. B Ssang_Yong).
Lassen Sie uns nun die erste Dropdown-Liste zur Auswahl einer Automarke erstellen. Wählen Sie eine leere Zelle aus und öffnen Sie das Menü Daten – Prüfen (Data - Validation) , dann aus der Dropdown-Liste Datentyp Wähle eine Option Aufführen und auf dem Feld Quelle - Markieren Sie Zellen mit Markennamen (in unserem Beispiel gelbe Zellen). Nach dem Klicken auf ОК Die erste Dropdown-Liste ist fertig:
Erstellen wir nun eine zweite Dropdown-Liste, in der die Modelle der in der ersten Liste ausgewählten Marke angezeigt werden. Wählen Sie wie im vorherigen Fall eine leere Zelle aus und öffnen Sie das Menü Daten – Prüfen - weiter Aufführen . Im Feld Quelle Sie müssen die folgende Formel eingeben:
=INDIRECT(F3)
Wo:
- F3 - die Adresse der Zelle mit der ersten Dropdown-Liste – ersetzen Sie sie durch Ihre.
Alle. Nach dem Klicken auf OK Der Inhalt der zweiten Liste wird anhand des Namens des in der ersten Liste ausgewählten Bereichs ausgewählt.
Nachteile dieser Methode:
- Dynamische Bereiche, die durch Formeln vom Typ OFFSET angegeben werden, können nicht als sekundäre (abhängige) Bereiche fungieren. Sie können sie für die primäre (unabhängige) Liste verwenden, die sekundäre Liste muss jedoch starr und ohne Formeln definiert werden.
- Die sekundären Bereichsnamen müssen mit den primären Dropdown-Listenelementen übereinstimmen. Das heißt, wenn Text Lücken enthält, müssen diese durch Unterstreichungen usw. ersetzt werden.
- Sie müssen viele benannte Bereiche manuell erstellen.
Methode 2. Übereinstimmungsliste (OFFSET und MATCH)
Diese Methode erfordert eine sortierte Liste von Make-Model-Übereinstimmungen des folgenden Typs:
Um eine primäre Dropdown-Liste mit Marken zu erstellen, können Sie die oben beschriebene übliche Methode verwenden, d. h.:
- Geben Sie den Namen des Bereichs D1:D3 an (z. B Marken )
- Wählen Sie auf der Registerkarte aus Daten (Data) Team Datenüberprüfung (Data validation)
- Wählen Sie eine Prüfoption aus der Dropdown-Liste aus Aufführen (List) und als Qualität angeben Quellen (Source) = Marken oder wählen Sie einfach die Zellen D1:D3 aus (sofern sie sich auf demselben Blatt wie die Liste befinden).
Für eine abhängige Liste von Modellen müssen Sie jedoch mit der Funktion OFFSET einen benannten Bereich erstellen, der sich dynamisch nur auf Zellen von Modellen einer bestimmten Marke bezieht. Dafür:
- Drücken Sie Strg + F3 oder verwenden Sie die Schaltfläche Namensmanager (Name manager) auf der Registerkarte Formeln (Formulas) . In Versionen vor 2003 war dies ein Menübefehl Einfügen – Name – Zuweisen (Insert - Name - Define)
- Erstellen Sie einen neuen benannten Bereich mit einem beliebigen Namen (z. B Modelle ) und im Feld Verknüpfung (Reference) Geben Sie im unteren Teil des Fensters manuell die folgende Formel ein:
=OFFSET( 1 $A$ ; MATCH($G$7;$A:$A;0)-1 ; 1 ; COUNTIF($A:$A;$G$7) ; 1 )
Bezüge müssen absolut sein (mit Vorzeichen). $ ). Nach dem Drücken von Enter werden die Blattnamen automatisch zur Formel hinzugefügt.
Die Funktion OFFSET kann einen Verweis auf einen Bereich der gewünschten Größe ausgeben, der relativ zur Ausgangszelle um die angegebene Anzahl von Zeilen und Spalten verschoben ist. In einer verständlicheren Version lautet die Syntax dieser Funktion wie folgt:
=OFFSET( start_cell ; Herunterschalten ; Shift_right ; range_size_in_rows ; range_size_in_columns )
Also:
- start_cell - Wir nehmen die erste Zelle unserer Liste, d.h A1 ;
- Herunterschalten - Wir zählen die Funktion MATCH, die vereinfacht gesagt die Seriennummer der Zelle mit der ausgewählten Marke (G7) im angegebenen Bereich (Spalten) ausgibt UND );
- Shift_right =1 , daher möchten wir auf die Modelle in der nebenstehenden Spalte verweisen ( IN );
- range_size_in_rows - Wir berechnen mit der Funktion COUNTIF, die in der Lage ist, die Anzahl der Vorkommen in der Liste (Spalte A) der von uns benötigten Werte zu zählen - Automarken ( G7 );
- range_size_in_columns =1 , also brauchen wir eine Spalte mit Modellen.
Das Ergebnis sollte etwa so aussehen:
Es bleibt noch, der Zelle eine Dropdown-Liste hinzuzufügen, die auf der erstellten Formel basiert G8 . Dafür:
- Wählen Sie eine Zelle aus G8
- Wählen Sie auf der Registerkarte aus Daten (Data) Team Datenüberprüfung (Data validation) oder im Menü Daten – Prüfen (Data - Validation)
- Wählen Sie die Prüfoption aus der Dropdown-Liste aus Aufführen (List) und geben Sie als ein Quellen (Source) Das Zeichen entspricht dem Namen unseres Sortiments, d. h = Modelle .
Artikel zum Thema:
