Excel-Arbeitsblätter enthalten häufig Dropdown-Felder für Zellen, um die Dateneingabe zu vereinfachen und / oder zu standardisieren. Diese Dropdown-Listen werden mithilfe der Datenüberprüfungsfunktion erstellt, um eine Liste zulässiger Einträge anzugeben.
Um eine einfache Dropdown-Liste einzurichten, wählen Sie die Zelle aus, in die Daten eingegeben werden sollen, und klicken Sie dann auf Datenüberprüfung(auf dem Tab Daten) wählen Sie Datenüberprüfung, wählen Sie Liste(unter Zulassen :) und geben Sie die durch Kommas getrennten Listenelemente in das Feld ein >Quelle: Feld (siehe Abbildung 1).
In dieser Art von Basis-Dropdown wird die Liste der zulässigen Einträge angegeben innerhalb der Datenvalidierung selbst; Um Änderungen an der Liste vorzunehmen, muss der Benutzer die Datenüberprüfung öffnen und bearbeiten. Dies kann jedoch für unerfahrene Benutzer oder in Fällen, in denen die Auswahlliste lang ist, schwierig sein.
Eine andere Option besteht darin, die Liste in einer benannten Bereich innerhalb der Tabelle zu platzieren und dann anzugeben dieser Bereichsname (mit vorangestelltem Gleichheitszeichen) im Feld Quelle: der Datenüberprüfung (wie in Abbildung 2 dargestellt).
Diese zweite Methode erleichtert das Bearbeiten der Auswahlmöglichkeiten in der Liste, das Hinzufügen oder Entfernen von Elementen kann jedoch problematisch sein. Da sich der benannte Bereich (in unserem Beispiel FruitChoices) auf einen festgelegten Bereich von Zellen bezieht ($ H $ 3: $ H $ 10, wie gezeigt), werden die Zellen H11 oder darunter, wenn mehr Auswahlmöglichkeiten hinzugefügt werden, nicht in der Dropdown-Liste angezeigt (Da diese Zellen nicht zum FruitChoices-Bereich gehören.)
Wenn beispielsweise die Einträge für Birnen und Erdbeeren gelöscht werden, werden sie nicht mehr in der Dropdown-Liste angezeigt, sondern enthalten stattdessen zwei "Leere" Auswahlmöglichkeiten, da das Dropdown-Menü weiterhin auf den gesamten FruitChoices-Bereich verweist, einschließlich der leeren Zellen H9 und H10.
Aus diesen Gründen wird bei Verwendung eines normalen benannten Bereichs als Listenquelle für ein Dropdown-Menü der benannte Bereich verwendet selbst muss so bearbeitet werden, dass mehr oder weniger Zellen enthalten sind, wenn Einträge zur Liste hinzugefügt oder daraus gelöscht werden.
Eine Lösung für dieses Problem ist die Verwendung einer dynamischenBereichsname als Quelle für die Dropdown-Auswahl. Ein dynamischer Bereichsname wird automatisch erweitert (oder verkleinert), um genau der Größe eines Datenblocks zu entsprechen, wenn Einträge hinzugefügt oder entfernt werden. Zu diesem Zweck verwenden Sie eine Formelanstelle eines festgelegten Bereichs von Zelladressen, um den benannten Bereich zu definieren.
Einrichten einer Dynamik Bereich in Excel
Ein normaler (statischer) Bereichsname bezieht sich auf einen bestimmten Zellbereich (in unserem Beispiel $ H $ 3: $ H $ 10, siehe unten):
Ein dynamischer Bereich wird jedoch mithilfe einer Formel definiert (siehe unten, entnommen aus einer separaten Tabelle, in der dynamische Bereichsnamen verwendet werden):
Bevor wir beginnen, stellen Sie sicher, dass Sie unsere Excel-Beispieldatei herunterladen (Sortiermakros wurden deaktiviert).
Lassen Sie uns diese Formel im Detail untersuchen. Die Auswahlmöglichkeiten für Früchte befinden sich in einem Zellenblock direkt unter einer Überschrift (FRÜCHTE). Dieser Überschrift wird auch ein Name zugewiesen: FruitsHeading:
Die gesamte Formel, die zum Definieren des Dynamikbereichs für die verwendet wird Die Auswahlmöglichkeiten für Früchte sind:
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(OFFSET(FruitsHeading,1,0,20,1)),0,0),0)-1,20),1)
FruitsHeadingbezieht sich auf die Überschrift, die eine Zeile über dem ersten Eintrag in der Liste steht. Die Zahl 20 (zweimal in der Formel verwendet) gibt die maximale Größe (Anzahl der Zeilen) der Liste an (diese kann nach Wunsch angepasst werden).
Beachten Sie, dass in diesem Beispiel nur 8 Einträge vorhanden sind in der Liste, aber es gibt auch leere Zellen darunter, in denen zusätzliche Einträge hinzugefügt werden könnten. Die Zahl 20 bezieht sich auf den gesamten Block, in dem Eingaben vorgenommen werden können, und nicht auf die tatsächliche Anzahl der Eingaben.
Lassen Sie uns nun die Formel in Teile aufteilen (Farbcodierung für jedes Teil), um zu verstehen, wie sie funktioniert :
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(OFFSET(FruitsHeading,1,0,20,1)),0,0),0)-1,20),1)
Das "innerste" Stück ist OFFSET (FruitsHeading, 1,0,20,1). Dies verweist auf den Block mit 20 Zellen (unter der FruitsHeading-Zelle), in den Auswahlmöglichkeiten eingegeben werden können. Diese OFFSET-Funktion lautet im Wesentlichen: Beginnen Sie in der Zelle FruitsHeading, gehen Sie 1 Zeile über 0 Spalten und wählen Sie dann einen Bereich aus, der 20 Zeilen lang und 1 Spalte breit ist. Damit erhalten wir den 20-Zeilen-Block, in dem die Fruchtauswahl eingegeben wird.
Der nächste Teil der Formel ist die ISBLANK-Funktion:
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(the above),0,0),0)-1,20),1)
Hier wurde die OFFSET-Funktion (oben erklärt) durch "oben" ersetzt (um die Lesbarkeit zu verbessern). Die ISBLANK-Funktion verarbeitet jedoch den von der OFFSET-Funktion definierten 20-Zeilen-Zellenbereich.
ISBLANK erstellt dann einen Satz von 20 TRUE- und FALSE-Werten, der angibt, ob jede der einzelnen Zellen in den 20-Zeilen-Zellen Der von der OFFSET-Funktion referenzierte Zeilenbereich ist leer (leer) oder nicht. In diesem Beispiel sind die ersten 8 Werte in der Menge FALSE, da die ersten 8 Zellen nicht leer sind und die letzten 12 Werte TRUE sind.
Der nächste Teil der Formel ist die INDEX-Funktion:
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(the above,0,0),0)-1,20),1)
Wiederum bezieht sich "oben" auf die oben beschriebenen Funktionen ISBLANK und OFFSET. Die INDEX-Funktion gibt ein Array mit den 20 von der ISBLANK-Funktion erstellten TRUE / FALSE-Werten zurück.
INDEXwird normalerweise verwendet, um einen bestimmten Wert (oder Wertebereich) auszuwählen einen Datenblock durch Angabe einer bestimmten Zeile und Spalte (innerhalb dieses Blocks). Wenn Sie jedoch die Zeilen- und Spalteneingaben auf Null setzen (wie hier), gibt INDEX ein Array zurück, das den gesamten Datenblock enthält.
Der nächste Teil der Formel ist die Funktion MATCH:
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,the above,0)-1,20),1)
Die Funktion MATCHgibt die Position des ersten TRUE-Werts innerhalb des Arrays zurück, das von der INDEX-Funktion zurückgegeben wird. Da die ersten 8 Einträge in der Liste nicht leer sind, sind die ersten 8 Werte im Array FALSE und der neunte Wert ist TRUE (da die 9. Zeile im Bereich leer ist).
Die Funktion MATCH gibt also den Wert 9zurück. In diesem Fall möchten wir jedoch wirklich wissen, wie viele Einträge in der Liste enthalten sind. Daher subtrahiert die Formel 1 vom MATCH-Wert (der die Position des letzten Eintrags angibt). Letztendlich gibt MATCH (TRUE, the above, 0) -1 den Wert von 8zurück.
Der nächste Teil der Formel ist die IFERROR-Funktion:
=OFFSET(FruitsHeading,1,0,IFERROR(the above,20),1)
Die Funktion IFERROR gibt einen alternativen Wert zurück, wenn der erste angegebene Wert zu einem Fehler führt. Diese Funktion ist enthalten, da die MATCH-Funktion einen Fehler zurückgibt, wenn der gesamte Zellenblock (alle 20 Zeilen) mit Einträgen gefüllt ist.
Dies liegt daran, dass wir der MATCH-Funktion die Suche anweisen der erste TRUE-Wert (in dem Array von Werten aus der ISBLANK-Funktion), aber wenn KEINE der Zellen leer ist, wird das gesamte Array mit FALSE-Werten gefüllt. Wenn MATCH den Zielwert (TRUE) in dem von ihm gesuchten Array nicht finden kann, wird ein Fehler zurückgegeben.
Wenn also die gesamte Liste voll ist (und daher MATCH einen Fehler zurückgibt), wird die IFERROR-Funktion ausgeführt Geben Sie stattdessen den Wert 20 zurück (in dem Wissen, dass die Liste 20 Einträge enthalten muss).
Schließlich gibt OFFSET (FruitsHeading, 1,0, the above, 1)das zurück Bereich, nach dem wir tatsächlich suchen: Beginnen Sie in der FruitsHeading-Zelle, gehen Sie eine Zeile tiefer und über 0 Spalten und wählen Sie dann einen Bereich aus, der jedoch viele Zeilen lang ist, solange Einträge in der Liste vorhanden sind (und 1 Spalte breit sind). Die gesamte Formel gibt also den Bereich zurück, der nur die tatsächlichen Einträge enthält (bis zur ersten leeren Zelle).
Wenn Sie mit dieser Formel den Bereich definieren, aus dem die Dropdown-Liste stammt, können Sie ihn frei bearbeiten Die Liste (Hinzufügen oder Entfernen von Einträgen, solange die verbleibenden Einträge in der obersten Zelle beginnen und zusammenhängend sind) und das Dropdown-Menü geben immer die aktuelle Liste wieder (siehe Abbildung 6).
Die hier verwendeten Beispieldatei (Dynamische Listen) sind enthalten und können von dieser Website heruntergeladen werden. Die Makros funktionieren jedoch nicht, da WordPress keine Excel-Bücher mit Makros mag.
Alternativ zur Angabe der Anzahl der Zeilen im Listenblock kann dem Listenblock eine eigene zugewiesen werden eigener Bereichsname, der dann in einer geänderten Formel verwendet werden kann. In der Beispieldatei verwendet eine zweite Liste (Namen) diese Methode. Hier wird dem gesamten Listenblock (unter der Überschrift "NAMES", 40 Zeilen in der Beispieldatei) der Bereichsname NameBlockzugewiesen. Die alternative Formel zum Definieren der NamesList lautet dann:
=OFFSET(NamesHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(NamesBlock),0,0),0)-1,ROWS(NamesBlock)),1)
wobei NamesBlockOFFSET (FruitsHeading, 1,0,20,1) und ROWS (NamesBlock)ersetzt die 20 (Anzahl der Zeilen) in der vorherigen Formel.
Für Dropdown-Listen, die einfach bearbeitet werden können (auch von anderen Benutzern, die möglicherweise unerfahren sind). versuchen Sie es mit dynamischen Bereichsnamen! Beachten Sie außerdem, dass der Schwerpunkt dieses Artikels auf Dropdown-Listen liegt. Die Namen von dynamischen Bereichen können jedoch überall dort verwendet werden, wo Sie auf einen Bereich oder eine Liste verweisen möchten, deren Größe variieren kann. Viel Spaß!