Verwenden Sie Dynamic -Bereichsnamen in Excel für flexible Dropdowns

Excel -Tabellenkalkulationen umfassen häufig Zell -Dropdowns, um die Dateneingabe zu vereinfachen und/oder zu standardisieren.  Diese Dropdowns werden mithilfe der Funktion zur Datenvalidierung erstellt, um eine Liste zulässiger Einträge anzugeben.

Um eine einfache Dropdown -Liste einzurichten, wählen Sie die Zelle aus, in der Daten eingegeben werden, und klicken Sie dann auf Datenvalidierung (auf der Daten Registerkarte), Datenvalidierung auswählen, auswählen Aufführen (unter erlauben :), und geben Sie dann die Listenelemente (getrennt durch Kommas) in die Quelle: Feld (siehe Abbildung 1).

Diese zweite Methode erleichtert es, die Auswahlmöglichkeiten in der Liste zu bearbeiten. Das Hinzufügen oder Entfernen von Elementen kann jedoch problematisch sein.  Da sich der benannte Bereich (Fruitchoices, in unserem Beispiel) auf einen festen Bereich von Zellen bezieht ($ H $ 3: $ H $ 10, wie gezeigt), werden sie in der Dropdown nicht mehr Möglichkeiten hinzugefügt, die Zellen H11 oder darunter hinzugefügt werden. (Da diese Zellen nicht Teil des Fruitchoices -Bereichs sind).

Ebenso, wenn beispielsweise die Birnen- und Erdbeereneinträge gelöscht werden, werden sie im Dropdown nicht mehr angezeigt. Stattdessen enthält der Dropdown zwei "leere" Auswahlmöglichkeiten, da der Dropdown immer noch den gesamten Fruitchoices -Bereich, einschließlich der leeren Zellen H9 und dessen verweist, verweist, einschließlich der leeren Zellen H9 und der H10.

Aus diesen Gründen muss der benannte Bereich selbst bei Verwendung eines normalen benannten Bereichs als Listenquelle für einen Dropdown -Bereich so bearbeitet werden.

Eine Lösung für dieses Problem besteht darin, a zu verwenden dynamisch Bereichsname als Quelle für die Dropdown -Auswahlmöglichkeiten.  Ein Name des dynamischen Bereichs ist einer, der automatisch erweitert (oder Verträge) so erweitert (oder Verträge) mit der Größe eines Datenblocks übereinstimmt, wenn Einträge hinzugefügt oder entfernt werden.  Dazu verwenden Sie a Formel, anstatt einen festen Bereich von Zelladressen, um den benannten Bereich zu definieren.

So richten Sie einen Dynamikbereich in Excel ein

Ein normaler (statischer) Bereichsname bezieht sich auf einen bestimmten Bereich von Zellen ($ H $ 3: $ H $ 10 In unserem Beispiel siehe unten):

Ein dynamischer Bereich wird jedoch unter Verwendung einer Formel definiert (siehe unten, aus einer separaten Tabelle, die den Namen Dynamic Range verwendet):

Bevor wir anfangen, stellen Sie sicher, dass Sie unsere Excel -Beispieldatei herunterladen (sortieren Makros wurden deaktiviert).

Lassen Sie uns diese Formel im Detail untersuchen.  Die Auswahl für Früchte befindet sich in einem Zellenblock direkt unter einer Überschrift (Früchte).  Diese Überschrift wird auch ein Name zugewiesen: Fruchtekopf:

Die gesamte Formel, mit der der Dynamikbereich für die Auswahl der Früchte definiert wird, lautet:

= Offset (Fruitsheading, 1,0, Ifror (Match (True, Index, ISBlank (Offset (Fruitsheading, 1,0,20,1)), 0,0), 0) -1,20), 1), 1)

Fruchtekopf Bezieht sich auf die Überschrift, die eine Zeile über dem ersten Eintrag in der Liste ist.  Die Zahl 20 (zweimal in der Formel verwendet) ist die maximale Größe (Anzahl der Zeilen) für die Liste (dies kann wie gewünscht angepasst werden).

Beachten Sie, dass in diesem Beispiel nur 8 Einträge in der Liste vorhanden sind, aber es stehen auch leere Zellen darunter, in denen zusätzliche Einträge hinzugefügt werden könnten.  Die Nummer 20 bezieht sich auf den gesamten Block, in dem Einträge vorgenommen werden können, nicht auf die tatsächliche Anzahl von Einträgen.

Lassen Sie uns nun die Formel in Stücke zerlegen (jedes Stück farblich codieren), um zu verstehen, wie sie funktioniert:

= Offset (Fruitsheading, 1,0, Ifror (Match (True, Index (ISBLANK) (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 bezieht sich auf den Block von 20 Zellen (unter der Fruits -Shading -Zelle), in der Auswahlmöglichkeiten eingegeben werden können.  Diese Offset -Funktion besagt im Grunde: Beginnen Sie bei der Fruchtekopf Zelle, gehen.  Das gibt uns den 20-Reihen-Block, in dem die Früchteauswahl eingegeben wird.

Das nächste Stück der Formel ist das IST LEER Funktion:

= Offset (Fruitsheading, 1,0, Ifror (Übereinstimmung (True, Index (IndexIslank (das obige),0,0), 0) -1,20), 1)

Hier wurde die Offset -Funktion (oben erläutert) durch „die oben genannten“ ersetzt (um die Lesen des Lesens zu erleichtern).  Die ISlank-Funktion arbeitet jedoch auf dem 20-Reihen-Bereich von Zellen, den die Offset-Funktion definiert.

IsBlank erstellt dann einen Satz von 20 wahren und falschen Werten, was angibt, ob jede der einzelnen Zellen im 20-Reihen-Bereich, auf die die Offset-Funktion verwiesen wird, leer (leer) oder nicht.  In diesem Beispiel sind die ersten 8 Werte im Satz falsch, da die ersten 8 Zellen nicht leer sind und die letzten 12 Werte wahr sind.

Das nächste Stück der Formel ist die Indexfunktion:

= Offset (Fruitsheading, 1,0, Ifror (Match (True),Index (die oben genannten, 0,0),0) -1,20), 1)

Auch hier bezieht sich „The oben“ auf die oben beschriebenen ISlank- und Offset -Funktionen.  Die Indexfunktion gibt ein Array zurück, das die von der ISBlank -Funktion erstellten 20 truen / falschen Werte enthält.

INDEX wird normalerweise verwendet, um einen bestimmten Wert (oder einen Wertebereich) aus einem Datenblock auszuwählen, indem eine bestimmte Zeile und Spalte angegeben wird (innerhalb dieses Blocks).  Das Einstellen der Zeilen- und Spalteneingänge auf Null (wie hier geschehen) führt dazu, dass der Index ein Array zurückgibt, das den gesamten Datenblock enthält.

Das nächste Stück der Formel ist die Match -Funktion:

= Offset (Fruitsheading, 1,0, Ifror (Match (wahr, die oben genannten, 0) -1,20), 1)

Der PASSEN Die Funktion gibt die Position des ersten tatsächlichen Wertes innerhalb des Arrays zurück, das von der Indexfunktion zurückgegeben wird.  Da die ersten 8 Einträge in der Liste nicht leer sind, sind die ersten 8 Werte im Array falsch, und der neunte Wert wird wahr sein (da die 9^th Reihe im Bereich ist leer).

Die Match -Funktion gibt also den Wert von zurück 9.  In diesem Fall möchten wir jedoch wirklich wissen, wie viele Einträge in der Liste enthalten sind. Die Formel subtrahiert also 1 vom Match -Wert (der die Position des letzten Eintrags angibt).  Letztendlich gibt übereinstimmen (wahr, die oben genannten, 0) -1 gibt den Wert von zurück 8.

Das nächste Stück der Formel ist die ISTERROR -Funktion:

= Offset (Fruitsheading, 1,0,IFERROR (das obige, 20),1)

Die ISTERROR -Funktion gibt einen alternativen Wert zurück, wenn der erste angegebene Wert zu einem Fehler führt.  Diese Funktion ist enthalten, da der gesamte Zellblock (alle 20 Zeilen) mit Einträgen gefüllt ist, die Übereinstimmungsfunktion gibt einen Fehler zurück.

Dies liegt daran.  Wenn die Übereinstimmung den Zielwert (TRUE) in Array nicht finden kann, wird er gesucht, es gibt einen Fehler zurück.

Wenn also die gesamte Liste voll ist (und damit übereinstimmt, gibt der Übereinstimmung einen Fehler zurück), gibt die ISTERROR -Funktion stattdessen den Wert von 20 zurück (weiß, dass in der Liste 20 Einträge vorhanden sind).

Endlich, Offset (Fruitsheading, 1,0, oben, 1) Gibt den Bereich zurück, nach dem wir tatsächlich suchen: Beginnen Sie in der Fruitsheading -Zelle, gehen Sie 1 Zeile und über 0 Spalten, und wählen Sie dann einen Bereich aus, der jedoch viele Zeilen ausgeht, solange es Einträge in der Liste (und 1 Spalte breit) gibt.  Daher gibt die gesamte Formel zusammen den Bereich zurück, der nur die tatsächlichen Einträge enthält (bis zur ersten leeren Zelle).

Verwenden dieser Formel, um den Bereich zu definieren, der die Quelle für den Dropdown -Bereich ist, bedeutet, dass Sie die Liste frei bearbeiten können (Hinzufügen oder Entfernen von Einträgen, solange die verbleibenden Einträge in der oberen Zelle beginnen und zusammenhängend sind) und die Dropdown immer den Strom widerspiegelt Liste (siehe Abbildung 6).

Die hier verwendete Beispieldatei (Dynamic Lists) ist hier enthalten und kann von dieser Website heruntergeladen werden. Die Makros funktionieren jedoch nicht, weil WordPress keine Excel -Bücher mit Makros mögen.

Als Alternative zur Angabe der Anzahl der Zeilen im Listenblock kann dem Listenblock seinen eigenen Bereichsnamen zugewiesen werden, der dann in einer modifizierten Formel verwendet werden kann.  In der Beispieldatei verwendet eine zweite Liste (Namen) diese Methode.  Hier wird dem gesamten Listenblock (unter der Überschrift "Namen", 40 Zeilen in der Beispieldatei) zugewiesen, der Bereichsname von NAMEBLOCK.  Die alternative Formel zum Definieren der NamensList lautet dann:

= Offset (Namesheading, 1,0, Ifror (Match (True, Index (ISBLANK) (ISBLANK (Namensblock), 0,0), 0) -1,Zeilen (Namensblock)), 1)

Wo Namensblock ersetzt Offset (Fruitsheading, 1,0,20,1) und Zeilen (Namensblock) ersetzt die 20 (Anzahl der Zeilen) in der früheren Formel.

Für Dropdown -Listen, die leicht bearbeitet werden können (einschließlich anderer Benutzer, die möglicherweise unerfahren sein)!  Und beachten. Genießen!