Seitdem 1997 Dropdown-Menüs zur Datenüberprüfung zu Excel hinzugefügt wurden, haben die Benutzer versucht, eine Möglichkeit zu finden, wie das zweite Dropdown-Menü basierend auf der Auswahl im ersten Dropdown-Menü geändert werden kann.
Wenn Sie beispielsweise Obst in A2 auswählen, bietet das Dropdown-Menü in A4 Apfel, Banane und Kirsche. Wenn Sie jedoch Kräuter aus A2 auswählen, enthält die Liste in A4 Anis, Basilikum und Zimt. Im Laufe der Jahre gab es viele Lösungen. Ich habe es mindestens zweimal im Podcast behandelt:
- Die klassische Methode verwendete viele benannte Bereiche, wie in Episode 383 gezeigt.
- Eine andere Methode verwendete OFFSET-Formeln in Episode 1606.
Mit der Veröffentlichung der neuen Dynamic Array-Formeln in der öffentlichen Vorschau bietet die neue FILTER-Funktion eine weitere Möglichkeit zur abhängigen Validierung.
Angenommen, dies ist Ihre Produktdatenbank:
Verwenden Sie eine Formel von =SORT(UNIQUE(B4:B23))in D4, um eine eindeutige Liste der Klassifikationen zu erhalten. Dies ist eine brandneue Art von Formel. Eine Formel in D4 gibt viele Antworten zurück, die in viele Zellen gelangen. Um auf den Spiller-Bereich zu verweisen, würden Sie =D4#anstelle von verwenden =D4.
Wählen Sie eine Zelle aus, in der sich das Menü Datenüberprüfung befindet. Wählen Sie Alt + DL, um die Datenüberprüfung zu öffnen. Ändern Sie Zulassen auf "Liste". Geben Sie =D4#als Quelle der Liste an. Beachten Sie, dass der Hashtag (#) der Spiller ist - dies bedeutet, dass Sie sich auf die gesamte Spiller-Reichweite beziehen.
Der Plan ist, dass jemand eine Klassifizierung aus dem ersten Dropdown-Menü auswählt. Dann gibt eine Formel von =FILTER(A4:A23,B4:B23=H3,"Choose Class First")in E4 alle Produkte in dieser Kategorie zurück. Beachten Sie, dass die Verwendung von "Choose Class First" als optionales drittes Argument. Dies verhindert einen #WERT! Fehler beim Erscheinen.
Abhängig von der ausgewählten Kategorie kann die Liste eine unterschiedliche Anzahl von Elementen enthalten. Wenn Sie die Datenüberprüfung einrichten, auf die verweist, =E4#wird die Länge der Liste erweitert oder verringert.
Schau Video
Video-Transkript
Lernen Sie Excel aus, Podcast-Episode 2248: Abhängige Validierung mithilfe von Arrays.
Nun, hey. Dies wurde bereits zweimal im Podcast angesprochen, wie eine abhängige Validierung durchgeführt wird und welche abhängige Validierung Sie wählen müssen, indem Sie zuerst eine Kategorie auswählen und als Reaktion darauf ändert sich das zweite Dropdown-Menü in "Nur" Elemente aus dieser Kategorie, und zuvor war dies kompliziert, und mit den neuen dynamischen Arrays, die im September 2018 angekündigt wurden… und diese werden eingeführt, sodass Sie Office 365 haben müssen. Im Moment, dem 10. Oktober, habe ich gehört dass sie bei etwa 50% der Office-Insider sind, also rollen sie sie sehr langsam aus. Es wird wahrscheinlich bis zum ersten Halbjahr 2019 dauern, bis Sie diese erhalten, aber es wird uns ermöglichen, die abhängige Validierung auf viel einfachere Weise durchzuführen.
Ich habe hier also zwei Formeln. Die erste Formel ist die EINZIGARTIGE aller Klassifikationen, die ich an den Befehl SORT gesendet habe. Das gibt mir also 1 Formel, die 5 Ergebnisse zurückgibt und die in D4 lebt. Also, hier, wo ich die Datenvalidierung auswählen möchte, werde ich (DL - 1:09) … die QUELLE wird = D4 # sein. Das # - wir haben es den Spiller genannt - stellen Sie sicher, dass alle Ergebnisse von D4 zurückgegeben werden. Wenn ich hier eine neue Kategorie hinzufügen würde und diese wächst, wird D4 # diesen zusätzlichen Betrag aufnehmen, okay? (= SORTIEREN (EINZIGARTIG (B4: B23)))
Diese erste Validierung ist also ziemlich einfach, aber jetzt, da wir wissen, dass wir CITRUS ausgewählt haben - dies wird schwieriger -, möchte ich die Liste in Spalte A filtern, wobei das Element in Spalte B dem ausgewählten Element entspricht , in Ordung? Also müssen wir sie zuerst etwas auswählen lassen und dann, wenn ich weiß, dass es ZITRUS ist, und mir dann LIME, ORANGE und TANGERINE geben, würden sie etwas anderes auswählen. BEERE. Überprüfen Sie dies heraus. Die wissenschaftlichen Zeitschriften sagen, dass eine Banane eine Beere ist. Dem stimme ich nicht zu. Fühlt sich für mich nicht wie eine Beere an, aber beschuldige mich nicht. Ich benutze nur das Internet. BANANE, ELDERBERRY und RASPBERRY.
Weißt du, der Ärger damit ist, dass jemand anfänglich hierher kommt, ohne etwas ausgewählt zu haben, und in diesem Fall haben wir also zuerst die Klasse gewählt. Dies ist das dritte Argument, das besagt, wenn nichts gefunden wird, in Ordnung? Wenn wir also mit diesem Szenario beginnen, wird die Wahl zuerst KLASSE WÄHLEN. Die Idee ist, dass sie die KLASSE, GEMÜSE, diese Aktualisierungen auswählen und diese Elemente dann aus dieser Liste stammen. Die Datenvalidierung hier natürlich, na ja, das ist ein weiterer Spiller, = E4 #, damit das funktioniert, okay? Das ist also cool. (= FILTER (A4: A23, B4: B23 = H3, "Klasse zuerst wählen"))
Schauen Sie sich mein Buch Excel Dynamic Arrays an. Dies ist… es wird bis Ende 2018 kostenlos sein. Überprüfen Sie den Link dort unten in der YouTube-Beschreibung, wie Sie ihn herunterladen können, für dieses Beispiel sowie 29 weitere Beispiele für die Verwendung dieser Elemente.
Nun, wickeln Sie für heute ein. Dynamische Arrays bieten uns eine weitere Möglichkeit zur abhängigen Validierung. Wenn Sie nicht mit Office 365 arbeiten und diese noch nicht haben, können Sie zu Video 1606 zurückkehren, das die alte Vorgehensweise zeigt.
Ich möchte mich bei Ihnen für Ihren Besuch bedanken. Wir sehen uns beim nächsten Mal für einen weiteren Netcast von.
Laden Sie die Excel-Datei herunter
So laden Sie die Excel-Datei herunter: abhängige Validierung mit arrays.xlsx
Weitere Informationen zu dynamischen Arrays finden Sie unter Excel Dynamic Arrays direkt auf den Punkt.
Excel-Gedanke des Tages
Ich habe meine Excel Master-Freunde um Rat zu Excel gebeten. Der heutige Gedanke zum Nachdenken:
"Löschen Sie niemals eine Excel-Datei, ohne sie zuvor zu sichern."
Mike Alexander
