Wie kann ich eine dynamische, abhängige Datenvalidierungsliste in Excel implementieren?

Question

Quellen im Internet zeigen, wie abhängige Datenvalidierungslisten implementiert werden, aber alle, die ich gefunden habe, sind statisch und werden nicht automatisch aktualisiert.

Ich denke, dass jeder, der diese Frage beantworten kann, bereits ein professioneller Fachmann für die Funktionsweise von Datenvalidierungslisten ist. Für Anfänger wie mich werde ich jedoch eine kurze Beschreibung geben (ich empfehle, bei Google weiterzulesen und sich Videos auf YouTube anzuschauen).

Datenüberprüfungslisten ermöglichen Ihnen die Erstellung einer definierten Datenstruktur in Ihrer Arbeitsmappe. Sie sind eher für die Interaktion mit Benutzern gedacht. Wenn Sie möchten, dass ein Benutzer der Arbeitsmappe nur einen Satz vorgegebener Daten einfügt, beispielsweise Donuts, Kuchen, Muffins und Crumpets, können Sie eine Liste mit einem benannten Bereich erstellen, beispielsweise „Snacks“, oder Sie können eine Tabelle erstellen und Ihre Tabelle „Snacks“ nennen. Dabei besteht der angegebene Name aus einem Wort (d. h. der angegebene Name enthält keine Leerzeichen).

Der Nachteil bei der Verwendung von Listen besteht darin, dass wir die ursprünglich erstellten Listen neu definieren müssten, wenn wir der Liste „Croissants“ einen weiteren Snack hinzufügen möchten. Tabellen umgehen diese Ineffizienz in dem Sinne, dass Sie einer Tabelle ganz einfach eine neue Zeile hinzufügen können, indem Sie sie aus der letzten Zeile anhängen oder die nächste Zeile nach der aktuellen Tabellenzeile schreiben und die Eingabetaste drücken.

Zur Datenvalidierung empfehle ich Ihnen, den Link unten für eine ausführliche Darstellung aufzurufen, da eine Erklärung diese Frage zu langwierig machen würde.http://analyticsdemystified.com/excel-tips/excel-dropdowns-done-right/

Eine abhängige Validierungsliste ist daher eine Liste, die auf einem vom Benutzer ausgewählten Wert aus einer vorherigen Validierungsliste basiert. Zu den Donut-Typen gehören beispielsweise Donuts mit Zuckerguss, Donuts mit Schokoladenglasur, Donuts mit Marmelade und Donuts mit Vanillepudding. Zu den Muffin-Typen gehören Banane, Blaubeere und Schokolade. Zu den Kuchentypen gehören Red Velvet, Karotte und Kokosnuss. Zu den Crumpet-Typen gehören schließlich Englisch, Schottisch und Pikelets.

Wenn eine Zelle also ein Dropdown-Menü mit der Hauptkategorienliste hat, wie erstellen Sie eine dynamische, abhängige Validierungsliste basierend auf den Unterkategorien?

Mit dynamischer abhängiger Validierungsliste meine ich, dass alle Spaltenlisten Tabellen sind, wie unten gezeigt.

Nun stellt sich die Frage, wie ich eine angrenzende Zelle implementiere. Wenn beispielsweise Zelle C13 eine Validierungsliste für die Snacks-Tabelle hat, wie mache ich Zelle D13 zu einer Validierungsliste basierend auf der vom Benutzer ausgewählten Eingabe von C13 (d. h. wenn C13 als Donuts ausgewählt ist, dann sind die möglichen Auswahlen der Validierungsliste von D13 die Spaltendaten der Donuts-Tabelle).

Bitte beachten Sie, dass die ursprüngliche Validierungsliste in C13 mit der Funktion INDIREKT erstellt worden wäre, da es sich um eine Tabelle und nicht um einen benannten Bereich handelt.

Answer 1

Einfacher Weg in 4 einfachen Schritten

Schritt 1 Erstellen Sie Ihre Tabellen:

Schritt 2 Benennen Sie Ihre Tabellen: Wählen Sie die Tabellen aus und benennen Sie sie so, dass sie mit dem Wert in der Primärtabelle übereinstimmen.Tabelle 1, z. B. sollte Tabelle2 mit der Überschrift "Kuchen" heißenKuchen, da dies der Wert in der Primärtabelle istTabelle 1.
Aber wie? Klicken Sie auf die Ecke der Tabelle, um sie auszuwählen, und geben Sie eine neue Beschriftung ein, wo stehtTabelle 2oder ähnliches ein und drücken Sie die Eingabetaste. Machen Sie sich keine Sorgen, wenn immer noch „Tabelle2“ usw. angezeigt wird.
(Notiz!Dies ist für die Primärtabelle (z. B. Tabelle 1) nicht erforderlich.)

Schritt 3 Wählen Sie aus, wo Sie Ihre erste Liste haben möchten, gehen Sie zur Registerkarte „Daten“ und zur Datenüberprüfung. Wählen Sie „Liste“ und geben Sie =INDIRECT("Table1")im Quellfenster Folgendes ein:

Schritt 4 Wählen Sie eine Zelle für die zweite Liste aus, die von der ersten Liste abhängig sein wird. Kehren Sie zur Datenüberprüfung zurück, wählen Sie „Liste“ und geben Sie ein, =INDIRECT(K2)wobei „K2“ der Speicherort der ersten Liste ist.

ERLEDIGT

Für eine dritte Liste, die von der zweiten Liste abhängt, wiederholen Sie Schritt 4 und beziehen Sie sich stattdessen auf die zweite Liste. Viel Glück!

Answer 2

Sie müssen mir einfach glauben, dass das funktioniert, aber ich habe genau zu diesem Zweck ein (sehr chaotische Spaghetti-Code-)Makro geschrieben. Leider ist der Code überhaupt nicht elegant, aber hey, er funktioniert! Wie man so schön sagt: „Lass es funktionieren, mach es richtig, mach es schnell.“

Schritt 1: Der VBA-Code

Sie benötigen drei Dinge:

1. Chip Pearsons Arrays-Modul--- der eigentliche VBA-Code steht gegen Ende.

2. Sortiermodul von Chip Pearson-- eigentlicher VBA-Code auch gegen Ende.

3. Mein schrecklich organisiertes Makro

Um diese in VBA-Module in Ihrer Arbeitsmappe einzufügen, drücken Sie Alt+ F11, suchen Sie Ihr Projekt im Projekt-Explorer, klicken Sie mit der rechten Maustaste auf den Ordner „Module“ und wählen Sie „Modul einfügen“. Fügen Sie den Code von jedem der 3 oben stehenden Links in separate Module ein.

Öffnen Sie im VBA-Editor das Dialogfeld „Verweise“ ( Alt+ Tund dann Enter) und aktivieren Sie „Microsoft Scripting Runtime“.

Schritt 2: Strukturieren Sie Ihre Daten

Nachdem das erledigt ist, möchten Sie jetzt ein Blatt mit Ihren Daten (sagen wir „Daten“), als wäre es eine gut strukturierte Datenbank. So würde ich es machen:

Beachten Sie, dass ich zwei weitere Blätter erstellt habe. Eines heißt „Validierung“, in dem die Drilldown-Validierungslogik stattfindet, und eines heißt „Haupt“ für den eigentlichen Inhalt, der für den Endbenutzer von Bedeutung ist.

Schritt 3: Hauptblatt

Gehen wir zum Datenblatt und richten die folgende Struktur ein:

Wählen Sie die Zelle aus C2und benennen Sie sie Snack.Selected. Um eine Zelle zu benennen, wählen Sie sie aus und drücken Sie dann . Alt M M DGeben Sie dann den Namen in das angezeigte Dialogfeld ein, genau wie im Bild gezeigt. Geben Sie vorerst einen Wert wie „Kuchen“ ein, damit der nächste Schritt mehr Sinn ergibt.

Benennen Sie die Zelle auch C3„Type.Selected“, lassen Sie sie jedoch vorerst leer.

Schritt 4: Werte aus unserer Tabelle im Datenblatt abrufen

Gehen Sie zu Ihrem Validierungsblatt und richten Sie die folgende Struktur ein:

Wie Sie auf dem Screenshot sehen können, sollten Sie Zellen auswählen B3:B20und die folgende Formel eingeben. Und hier kommt mein Makro ins Spiel:

=MultiLookup("Data","Snack",TRUE,TRUE)

Anstatt Enter zu drücken, drücken Sie Strg+Shift+Enter, denn dies ist einArray-Formel

Wenn es wie angekündigt funktioniert, sollten Sie eine Liste Ihrer Snacks sehen, gefolgt von einigen #N/A-Fehlern. Die Fehler sind zu erwarten – sie bedeuten nur, dass Sie weniger Snacks haben, als Sie aufgrund der Größe von erwartet haben B3:B20.

Erläuterung der Parameter in der Formel:

1. Blatt, in dem sich die Daten befinden („Daten“)
2. Gewünschtes Feld aus diesem Blatt („Snack“)
3. Duplikate entfernen (also gruppieren)? (WAHR)
4. Alphabetisch sortiert? (WAHR)

Ok, das scheint viel Arbeit für wenig Nutzen zu sein, aber hier kommt das große Finale. In den Zellen C3:C20platzieren Sie die folgende Formel:

=MultiLookup("Data","Type",TRUE,TRUE,"Snack",Snack.Selected)

Hier sollten die verfügbaren Kuchenarten angezeigt werden. Das ist der Wert, den Sie im vorherigen Schritt im Hauptblatt eingegeben haben, erinnern Sie sich?

Dies funktioniert, indem der Formel zwei Argumente hinzugefügt werden:

5. Zu filterndes Feld („Snack“)
6. Wählen Sie nur Werte aus, die gleich (Snack.Selected) sind.

Wenn Sie also Ihre Daten im Hauptmenü ändern, wird die Spalte „Typen“ automatisch aktualisiert!

Schritt 5: Validierung abschließen

Zum Abschluss beenden wir unsere Datenüberprüfung, indem wir die Namen Snack.Choicesund erstellen Type.Choices. Bitte benennen Sie die Zellen B1und C1in der Validierung entsprechend. Sie können auch einfach in dieses kleine Feld tippen, um eine Zelle zu benennen:

In die Zelle B1müssen Sie die folgende Formel eingeben:

="Validation!"&CELL("address",B3)&":"&CELL("address",OFFSET(B$2,COUNTIF(B3:B50,"*"),0))

Dadurch wird ein Textverweis auf „Validation!$B$3:$B$6“ erstellt, wo sich Ihre Snackauswahl befindet. Kopieren Sie diese Formel nach rechts und fertig!

Gehen Sie zurück zu Ihrem Hauptblatt und beziehen Sie sich in den Validierungsregeln für Snack und Typ auf diese Zellen.

Wählen Sie die Zelle aus C2und drücken Sie die Tastenkombination > Alt, A V Vum die Datenüberprüfung zu starten. Wählen Sie „Liste“ und legen Sie die Quelle auf fest =INDIRECT(Snack.Choices). Beachten Sie, dass keine Anführungszeichen vorhanden sind.Snack.Choices

Machen Sie dasselbe auf dem Handy C3, aber stellen Sie die Quelle auf ein =INDIRECT(Type.Choices).

Wir verwenden INDIRECT(), weil die Werte von Snack.Choicesund Type.Choicesindirekte (d. h. Text-)Verweise auf Bereiche in unserer Arbeitsmappe sind.

Probieren Sie jetzt die Validierungsoptionen aus, um das Ganze in Aktion zu sehen.

Wenn Sie Fragen haben, lassen Sie es mich wissen!