Verwenden Sie in Excel für dynamische Dropdown-Namen Namen für dynamische Bereiche

• 2019

Excel-Kalkulationstabellen enthalten häufig Dropdowns für Zellen, um die Dateneingabe zu vereinfachen und / oder zu standardisieren. Diese Dropdown-Listen werden mit der Datenüberprüfungsfunktion erstellt, um eine Liste der zulässigen Einträge anzugeben.

Um eine einfache Dropdown-Liste einzurichten, wählen Sie die Zelle aus, in die Daten eingegeben werden sollen, klicken Sie dann auf Datenüberprüfung (auf der Registerkarte Daten ), wählen Sie Datenüberprüfung aus, wählen Sie Liste (unter Zulassen :), und geben Sie die Listenelemente (durch Kommas getrennt) ein ) im Feld Quelle : (siehe Abbildung 1).

Bei diesem grundlegenden Dropdown-Typ wird die Liste der zulässigen Einträge in der Datenprüfung selbst angegeben. Um Änderungen an der Liste vorzunehmen, muss der Benutzer die Datenüberprüfung öffnen und bearbeiten. Dies kann jedoch für unerfahrene Benutzer schwierig sein oder in Fällen, in denen die Auswahlliste lang ist.

Eine andere Option besteht darin, die Liste in einem benannten Bereich innerhalb der Tabelle zu platzieren und dann diesen Bereichsnamen (mit vorangestelltem Gleichheitszeichen) im Feld Quelle : der Datenüberprüfung anzugeben (wie in Abbildung 2 gezeigt).

Diese zweite Methode erleichtert das Bearbeiten der Auswahlmöglichkeiten in der Liste. Das Hinzufügen oder Entfernen von Elementen kann jedoch problematisch sein. Da der benannte Bereich (FruitChoices in unserem Beispiel) sich auf einen festen Zellbereich bezieht ($ H $ 3: $ H $ 10 wie gezeigt), werden den Zellen H11 oder darunter mehr Auswahlmöglichkeiten hinzugefügt, so dass sie nicht in der Dropdown-Liste angezeigt werden (da diese Zellen nicht zum FruitChoices-Sortiment gehören).

Wenn beispielsweise die Einträge für Birnen und Erdbeeren gelöscht werden, werden sie nicht mehr in der Dropdown-Liste angezeigt. Stattdessen werden in der Dropdown-Liste zwei leere Optionen angezeigt, da die Dropdown-Liste immer noch den gesamten FruitChoices-Bereich referenziert, einschließlich der leeren Zellen H9 und H10.

Wenn Sie einen normalen benannten Bereich als Listenquelle für eine Dropdown-Liste verwenden, muss der benannte Bereich selbst so bearbeitet werden, dass mehr oder weniger Zellen eingeschlossen werden, wenn Einträge hinzugefügt oder aus der Liste gelöscht werden.

Eine Lösung für dieses Problem ist die Verwendung eines dynamischen Bereichsnamens 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 Formel anstelle eines festen Bereichs von Zellenadressen, um den benannten Bereich zu definieren.

So richten Sie einen Dynamikbereich in Excel ein

Ein normaler (statischer) Bereichsname bezieht sich auf einen angegebenen 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, die dynamische Bereichsnamen verwendet):

Bevor Sie beginnen, laden Sie bitte unsere Excel-Beispieldatei herunter (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 ( FRUITS ). Dieser Überschrift wird auch ein Name zugewiesen: FruitsHeading :

Die gesamte Formel zur Definition des dynamischen Bereichs für die Obstauswahl ist:

 = OFFSET (FruitsHeading, 1, 0, IFERROR (MATCH (TRUE, INDEX (ISBLANK (OFFSET (FruitsHeading, 1, 0, 20, 1))), 0, 0), 0) -1, 20), 1) 

FruitsHeading bezieht sich auf die Überschrift, die eine Zeile über dem ersten Eintrag in der Liste liegt. Die Zahl 20 (zweimal in der Formel verwendet) ist die maximale Größe (Anzahl der Zeilen) für die Liste (diese kann nach Wunsch angepasst werden).

Beachten Sie, dass in diesem Beispiel nur 8 Einträge in der Liste vorhanden sind, darunter jedoch auch leere Zellen, in denen zusätzliche Einträge hinzugefügt werden könnten. Die Zahl 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 Teile zerlegen (jedes Stück farbcodieren), um zu verstehen, wie es 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 von 20 Zellen (unter der Zelle FruitsHeading), in die Auswahlmöglichkeiten eingegeben werden können. Diese OFFSET-Funktion besagt im Wesentlichen: Beginnen Sie mit der Zelle FruitsHeading, gehen Sie eine Zeile und mehr als 0 Spalten nach unten, und wählen Sie dann einen Bereich aus, der 20 Zeilen lang und 1 Spalte breit ist. Das gibt uns den 20-reihigen Block, in den die Obstauswahl eingegeben wird.

Der nächste Teil der Formel ist die ISBLANK- Funktion:

 = OFFSET (FruitsHeading, 1, 0, IFERROR (MATCH (TRUE, INDEX ( ISBLANK (oben)), 0, 0), 0) -1, 20), 1) 

Hier wurde die OFFSET-Funktion (oben erläutert) durch „das Obige“ ersetzt (um das Lesen zu erleichtern). Die ISBLANK-Funktion arbeitet jedoch mit dem 20-Zeilen-Zellenbereich, den die OFFSET-Funktion definiert.

ISBLANK erstellt dann einen Satz von 20 TRUE- und FALSE-Werten, die angeben, ob jede der einzelnen Zellen im 20-Zeilen-Bereich, auf die die OFFSET-Funktion verweist, leer (leer) ist. In diesem Beispiel sind die ersten 8 Werte in der Gruppe 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 (oben, 0, 0), 0) -1, 20), 1) 

„Das Vorstehende“ bezieht sich wiederum auf die oben beschriebenen Funktionen ISBLANK und OFFSET. Die INDEX-Funktion gibt ein Array zurück, das die von der ISBLANK-Funktion erstellten 20 TRUE / FALSE-Werte enthält.

INDEX wird normalerweise verwendet, um einen bestimmten Wert (oder Wertebereich) aus einem Datenblock auszuwählen, indem eine bestimmte Zeile und Spalte (in diesem Block) angegeben wird. Wenn Sie jedoch die Zeilen- und Spalteneingaben auf Null setzen (wie hier beschrieben), wird von INDEX ein Array zurückgegeben, das den gesamten Datenblock enthält.

Der nächste Teil der Formel ist die MATCH-Funktion:

 = OFFSET (FruitsHeading, 1, 0, IFERROR ( MATCH (TRUE, der obige Wert , 0) -1, 20), 1) 

Die Funktion MATCH gibt die Position des ersten TRUE-Werts innerhalb des Arrays zurück, das von der Funktion INDEX 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 des Bereichs leer ist).

Die MATCH-Funktion gibt also den Wert 9 zurück . In diesem Fall möchten wir jedoch wirklich wissen, wie viele Einträge in der Liste vorhanden sind. Die Formel zieht also 1 vom MATCH-Wert (der die Position des letzten Eintrags angibt) ab. Letztendlich gibt MATCH (TRUE, das Oben 0) -1 den Wert 8 zurück .

Der nächste Teil der Formel ist die IFERROR-Funktion:

 = OFFSET (FruitsHeading, 1, 0, IFERROR (die oben genannten, 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 Funktion MATCH einen Fehler zurückgibt, wenn der gesamte Zellenblock (alle 20 Zeilen) mit Einträgen gefüllt ist.

Das liegt daran, dass wir der MATCH-Funktion sagen, dass sie nach dem ersten TRUE-Wert (im Array der Werte aus der ISBLANK-Funktion) suchen soll. Wenn jedoch KEINE Zellen leer sind, wird das gesamte Array mit FALSE-Werten gefüllt. Wenn MATCH den Zielwert (TRUE) in dem durchsuchten Array nicht finden kann, wird ein Fehler zurückgegeben.

Wenn also die gesamte Liste voll ist (und daher MATCH einen Fehler zurückgibt), gibt die IFERROR-Funktion stattdessen den Wert 20 zurück (wissend, dass 20 Einträge in der Liste vorhanden sein müssen).

Schließlich gibt OFFSET (FruitsHeading, 1, 0, oben, 1) den Bereich zurück, den wir tatsächlich suchen: Beginnen Sie bei der Zelle FruitsHeading, gehen Sie 1 Zeile und über 0 Spalten nach unten, und wählen Sie dann einen Bereich aus, der jedoch viele Zeilen lang ist Es gibt Einträge in der Liste (und 1 Spalte breit). Die gesamte Formel zusammen 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 als Quelle für die Dropdown-Liste definieren, können Sie die Liste beliebig bearbeiten (Einträge hinzufügen oder entfernen, sofern die verbleibenden Einträge in der obersten Zelle beginnen und zusammenhängend sind). Die Dropdown-Liste zeigt immer den aktuellen Wert an Liste (siehe Abbildung 6).

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

Alternativ zur Angabe der Zeilenanzahl im Listenblock kann dem Listenblock ein eigener Bereichsname zugewiesen werden, der dann in einer modifizierten Formel verwendet werden kann. In der Beispieldatei verwendet eine zweite Liste (Names) diese Methode. Hier wird dem gesamten Listenblock (unter der Überschrift „NAMES“, 40 Zeilen in der Beispieldatei) der Bereichsname NameBlock zugewiesen . 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) 

Dabei ersetzt NamesBlock OFFSET (FruitsHeading, 1, 0, 20, 1) und ROWS (NamesBlock) die 20 (Anzahl der Zeilen) in der früheren Formel.

Verwenden Sie für Dropdown-Listen, die leicht bearbeitet werden können (auch von anderen Benutzern, die möglicherweise unerfahren sind) die Verwendung von Namen für dynamische Bereiche! Beachten Sie, dass, obwohl sich dieser Artikel auf Dropdown-Listen konzentriert hat, die Namen von dynamischen Bereichen überall verwendet werden können, um auf einen Bereich oder eine Liste zu verweisen, die in der Größe variieren können. Genießen!