Abhängige (dynamische) Drop-Down-Listen

13. Juli 202013. Juli 2020 Roman Allgemein, Formeln abhängig, Dropdown, dynamisch, Dynamischer Name, Liste, Namensmanager

In Formularen sind abhängige Drop-Down-Listen oftmals unabdingbar. Was bedeutet das? Wählt man im ersten Feld eine Oberkategorie "Obst" oder "Fleischwaren", dann sollen in einer zweiten Auswahlliste nur die entsprechenden Einträge zur Option stehen. Wir haben diese abhängigen Drop-Down-Listen bereits im Beitrag "Dropdown einer Dropdown-Liste erstellen - abhängige Dropdown" illustriert. Das funktioniert dann einwandfrei, wenn sich die Inhalte der Unterkategorien nicht ändern. Ansonsten ist es notwendig, die Bereiche (Namen) von "Obst" oder "Fleischwaren" den neuen Umständen entsprechend anzupassen (Bereich erweitern oder reduzieren). Es suggeriert also eine dynamische Drop-Down-Liste. Wie man einen solchen Namen (mit der Formel BEREICH.VERSCHIEBEN) anlegt, kannst Du in diesem Beitrag nachschlagen: Bereich eines Namens automatisch erweitern

Wir würden also alle Namen für die Drop-Down-Listen entsprechend anlegen, wie im Screenshot unten dargestellt (mit der Formel "BEREICH.VERSCHIEBEN", referenzierend auf das separate Blatt "Parameter"):

Wählt man im Anschluss im Formular die erste Option zu "Essen", sollte in der abhängigen Drop-Down-Liste dann die jeweilige Unterkategorie abrufbar sein. Dies ist jedoch nicht der Fall, die Auswahl-Liste lässt sich mit Klick auf den Erweiterungspfeil gar nicht erst aufrufen und anzeigen. Wie können wir also sowohl abhängige wie auch dynamische Drop-Down-Listen erstellen? Das Vorgehen zeige ich Dir nachfolgend Schritt für Schritt auf, lösche jedoch bitte im Namensmanager (Menüpunkt "Formeln" → "Namensmanager") allfällige bereits definierten Namen für Deine Drop-Down-Listen.

Abhängige, dynamische Drop-Down-Listen (Lösungsansatz)

Wir müssen die ursprünglichen Daten auf dem separaten Blatt "Parameter" also anders definieren, um jederzeit unsere Auswahl-Kriterien ergänzen und unmittelbar nachher im Formular abrufen können. Gehe dazu im Menüband auf das Register "Einfügen" und selektiere die Option "Tabelle". Du erstellst nun also eine Tabelle und wählst als erstes den Bereich "A1:A3" aus, um die Oberkategorie abzubilden. Falls Du die Zeile 1 inkludierst, wähle zudem "Tabelle hat Überschriften".

Wiederhole den Vorgang auch für die Unterkategorien "Fleischwaren" und "Obst".

Wenn Du nun in der Spalte A Fleischwaren und Obst markierst, siehst Du links oben im Namensfeld (normalerweise steht da die Zelle "A2" und so weiter) "Tabelle1" stehen. Der Name "Tabelle1" ist aktuell also stellvertretend für diesen Bereich. Klicke in dieses Namensfeld und überschreibe einfach "Tabelle1" mit "Essen" - bestätige mit der Enter-Taste. Du wirst in dem Namensfeld kurz etwas aufblinken sehen, danach steht allerdings wieder "Tabelle1" drin. Mache das nun auch für B2:B3 (=Tabelle2) und setze da den Namen "Fleischwaren" sowie für C2:C4 (=Tabelle3) und definiere da "Obst". Rufe nun erneut Deinen Namensmanager auf (Menüpunkt "Formeln" → "Namensmanager"). Dort siehst Du die Resultate entsprechend - beachte auch die Spalte "Bezieht sich auf", die auf die jeweilige Tabelle referenziert:

Navigiere nun auf Dein effektives Formular. Wähle die Hauptkategorie (Essen) und versuche nun, Deine Unterkategorie (Drop-Down-Einstellung: =INDIREKT(B5) -- wobei B5 dem Feld mit Fleischwaren/Obst entspricht) kann nun korrekt aufgerufen werden:

Zuletzt kannst Du prüfen, ob Deine Listen auf dem Blatt "Parameter" tatsächlich dynamisch sind. Gib einfach einen neuen Begriff unterhalb ein, beispielsweise "Schweinefleisch" als zusätzliche Fleischware. Die Tabelle erweitert sich automatisch (Du siehst dies auch farblich) - und auf der Drop-Down-Liste im Formular kannst Du die neue Option ebenfalls gleich selektieren.

Vereinfachter (S-)Verweis - Anwendung der Formel XVERWEIS

23. April 202024. April 2020 Roman Formeln Lookup, Suche, SVERWEIS, VERWEIS, WVERWEIS

Die Formel "SVERWEIS" ist eine der bekanntesten in Excel - da sie mächtige Überprüfungen durchführen und Ergebnisse liefern kann, aber eben auch manch einem Benutzer Probleme bereitet. Diverse Angaben und Regeln müssen erfüllt werden, damit man zum gewünschten Resultat gelangt. Ein Nachteil der Formel ist zudem, dass die Ergebnisspalte rechts von dem Suchwert stehen muss (siehe hierzu eine ebenfalls valide Lösung SVERWEIS nach links).

In diesem Beitrag wird eine neuere Formel "XVERWEIS" vorgestellt. Diese Funktion ist deutlich intuitiver, wie die ersten drei Argumente der Syntax zeigen (nur diese drei sind notwendig, die Angaben in den eckigen Klammern können ignoriert werden):

Die erste Angabe verlangt analog dem SVERWEIS ein Suchkriterium - nach welchem Wert soll gesucht werden? Die Suchmatrix ist z.B. die Spalte, in welcher das Suchkriterium steht. Beim SVERWEIS wird es bereits an dieser Stelle kompliziert, da die Matrix sowohl das Suchkriterium wie auch das Resultat enthalten muss. In dieser Formel ist das dritte Argument die Rückgabematrix (z.B. die Spalte, in der das Resultat steht). Wie erwähnt hat die Formel drei weitere Argumente in eckigen Klammern - diese sind fakultativ und bieten weitere Möglichkeiten, auf die wir hier nicht eingehen.

Schauen wir uns ein konkretes und simples Beispiel an. Eine Tabelle enthält die Regionalleiter und deren E-Mail-Adressen. In Spalte F suchen wir nach der Region, um die E-Mail zu erhalten:

Wechseln wir das Suchkriterium von "Nordeuropa" zu "Nordamerika", erhalten wir die entsprechende E-Mail-Adresse.

Dies ist die effektive Formel in der Zelle "G3":

=XVERWEIS(F3;B3:B8;D3:D8)

Selbstverständlich könnte man auch hier Werte "absolut" setzen (Dollarzeichen), damit man die Formel vereinfacht kopieren könnte - hier wird allerdings nur ein einziger Wert gesucht.

Beachte zudem: Mit der Formel ist nicht nur der "SVERWEIS" (Spalten) möglich, sondern auch der "waagrechte" Verweis (WVERWEIS). Die oben aufgeführte Tabelle habe ich transponiert, um den XVERWEIS auf ein solches Format anzuwenden. Unten wird die "gedrehte" (transponierte) Tabelle sowie die angepasste XVERWEIS-Formel dargestellt. Um die Abbildung übersichtlicher zu gestalten, habe ich gewisse Spalten (D bis G) gruppiert und ausgeblendet.

Die Formel in Zelle "C7" lautet:

=XVERWEIS(B7;C2:H2;C4:H4)

"Überlauf" - Formel-Ausgabe mehrerer Werte

13. Januar 20206. Januar 2020 Roman Formeln Array, Bereich, BEREICH.VERSCHIEBEN, SORTIEREN, Spill, Überlauf

In früheren Excel-Versionen hat man teilweise trotz korrekter Eingabe einer Formel die Fehlermeldung "#WERT!" erhalten. Dies war beispielsweise bei "=BEREICH.VERSCHIEBEN" der Fall, wenn das Ergebnis der Formel mehrere Werte beinhaltete. Dabei kommt es zu einem "Überlauf", da innerhalb einer Zelle nur ein Wert dargestellt werden kann. Angewendet hat man die erwähnte Formel unter anderem, um dynamische Bereiche zu definieren - damit Pivot-Tabellen oder Drop-Down-Listen (allenfalls nach dem Aktualisieren) die angepassten Datenquellen berücksichtigen. Dabei wird mit dem Namensmanager gearbeitet, wie bereits auf Excelblog.ch im Beitrag "Pivot-Datenquelle automatisch erweitern" sowie "Bereich eines Namens automatisch erweitern" erläutert wurde.

In diesem Beitrag gehen wir erneut auf die Formel "BEREICH.VERSCHIEBEN" ein und stellen damit innerhalb einer Excel-Tabelle eine dynamische Liste erneut dar - ohne den Namensmanager zu verwenden. Dabei simulieren wir erneut den angesprochenen "Überlauf" und zeigen, wie der Ergebnisbereich neuerdings in Excel dargestellt wird. Um dieses Beispiel zu erläutern, verwenden wir nachfolgende Länder-Liste auf dem Tabellenblatt "Parameter":

Eine Drop-Down-Liste könnte ganz einfach auf diesen Bereich verweisen und nur diese Werte wären zulässig. Falls man jedoch weitere Länder in die Liste eintragen sollte, bleibt der Bezug der Drop-Down-Liste statisch. Weitere Erläuterungen zu diesem dynamischen Bereich findest Du im bereits verlinkten Beitrag "Bereich eines Namens automatisch erweitern". Die Formel, die wir verwenden, um den Bereich in unserem Beispiel dynamisch zu gestalten, lautet wie folgt:

=BEREICH.VERSCHIEBEN(Parameter!$A$2;;;ANZAHL2(Parameter!$A:$A)-1;)

Ausgehend von der Zelle "A2" (erstes Land) wird gemessen, wie viele Einträge in der Spalte A vorhanden sind (abzüglich 1 für die Überschrift). Dies ergibt unseren dynamischen Bereich, welchen wir in einer Drop-Down-Liste verwenden können:

Falls man obige Formel jedoch nicht im Namen eingibt, sondern wie ansonsten üblich in einer einfachen Zelle (im Screenshot unten Zelle "B2"), dann erhalten wir folgendes Resultat:

Die Liste wird exakt gleich dargestellt und die Werte werden in den Zellen unterhalb von B2 weitergeführt - die Zelle B2 ist "überlaufen". Wenn Du in die Zelle B2 klickst, siehst Du die Formel in der Bearbeitungsleiste normal angezeigt. Klickst Du jedoch beispielsweise in die Zelle "B3", dann ist die Formel zwar angezeigt, aber ausgegraut. Du kannst sie da auch nicht bearbeiten - nur eben in Zelle "B2". Falls Du einen Wert in den "Überlaufbereich" einfügst (z.B. in Zelle "B3"), dann erhältst Du neu die Fehlermeldung "#ÜBERLAUF!". Dies ist ein Hinweis, dass der Überlaufbereich nicht leer ist. Über die Fehlermeldung kann man auch direkt in die "blockierende Zelle" navigieren - dies kann vor allem bei grösseren Listen hilfreich sein:

=SORTIEREN - ein weiterer Anwendungsfall

Eine weitere Formel, die nun ein interessantes Ergebnis mit Überlauf liefert, ist "=SORTIEREN". Dies kann erneut mit der obigen Länder-Liste illustriert werden. Angenommen die Länder sind einfach über die Zeit in die Liste eingetragen worden, ohne einer Reihenfolge oder Sortierung zu unterliegen. Mit der Formel "=SORTIEREN" kann einfach die gesamte Liste markiert werden, um die Länder im Zielbereich alphabetisch zu sortieren - dafür benötigt es in der Formel keine weiteren Angabe, der Bereich (z.B. "A2:A11") genügt! Die Formel wird in einer Zelle eingetragen, sämtliche Länder werden mithilfe des Überlaufs als Bereich ausgegeben. Dies ist das Resultat, nebeneinander dargestellt: