Excel -Tabellen enthalten oft Zellen-Dropdowns, um die Dateneingabe zu vereinfachen und/oder zu standardisieren. Diese Dropdowns werden mithilfe der Datenvalidierungsfunktion 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, klicken Sie dann auf Datenvalidierung^{(Data Validation)} (auf der Registerkarte Daten ), wählen Sie ^(Data)Datenvalidierung^{(Data Validation)} aus, wählen Sie Liste^(List) (unter Zulassen^(Allow) :) und geben Sie dann die Listenelemente ein (durch Kommas getrennt ) im Feld Source : (siehe Abbildung 1).

Bei dieser Art von grundlegendem Dropdown wird die Liste der zulässigen Einträge innerhalb der Datenvalidierung selbst angegeben; Um Änderungen an der Liste vorzunehmen, muss der Benutzer daher die Datenvalidierung öffnen und bearbeiten. Dies kann jedoch für unerfahrene Benutzer oder in Fällen, in denen die Auswahlliste lang ist, schwierig sein.

Eine weitere Option besteht darin, die Liste in einem benannten Bereich innerhalb der Tabelle^{(named range within the spreadsheet)} zu platzieren und dann diesen Bereichsnamen (mit vorangestelltem Gleichheitszeichen) im Feld Quelle^(Source) : der Datenvalidierung anzugeben (wie in Abbildung 2^{(Figure 2)} dargestellt ).

Diese zweite Methode erleichtert das Bearbeiten der Auswahlmöglichkeiten in der Liste, aber das Hinzufügen oder Entfernen von Elementen kann problematisch sein. Da sich der benannte Bereich ( in unserem Beispiel FruitChoices ) auf einen festen Bereich von Zellen bezieht ($H$3:$H$10 wie gezeigt), werden, wenn mehr Auswahlmöglichkeiten zu den Zellen H11 oder darunter hinzugefügt werden, diese nicht in der Dropdown-Liste angezeigt (da diese Zellen nicht Teil des FruitChoices- Sortiments sind).

Wenn beispielsweise die Einträge Birnen^(Pears) und Erdbeeren^{(Strawberries)} gelöscht werden, erscheinen sie nicht mehr in der Dropdown-Liste, sondern die Dropdown-Liste enthält zwei „leere“ Auswahlmöglichkeiten, da die Dropdown-Liste immer noch auf den gesamten FruitChoices-Bereich verweist, einschließlich der leeren Zellen H9 und H10 .

Aus diesen Gründen muss bei Verwendung eines normalen benannten Bereichs als Listenquelle für ein Dropdown der benannte Bereich selbst bearbeitet werden, um mehr oder weniger Zellen einzuschließen, wenn Einträge zur Liste hinzugefügt oder aus ihr gelöscht werden.

Eine Lösung für dieses Problem ist die Verwendung eines dynamischen^(dynamic) Bereichsnamens als Quelle für die Dropdown-Auswahlmöglichkeiten. Ein dynamischer Bereichsname ist ein Name, der automatisch erweitert (oder zusammengezogen) wird, um genau der Größe eines Datenblocks zu entsprechen, wenn Einträge hinzugefügt oder entfernt werden. Dazu verwenden Sie eine Formel^(formula) anstelle eines festen Bereichs von Zelladressen, um den benannten Bereich zu definieren.

So richten Sie einen dynamischen Bereich^{(Dynamic Range)} in Excel ein^(Excel)

Ein normaler (statischer) Bereichsname bezieht sich auf einen bestimmten Zellbereich ($H$3:$H$10 in unserem Beispiel, siehe unten):

Ein dynamischer Bereich wird jedoch mithilfe einer Formel definiert (siehe unten, entnommen aus einer separaten Tabelle, die Namen dynamischer Bereiche verwendet):

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^(FRUITS) ). Dieser Überschrift wird auch ein Name zugewiesen: FruitsHeading :

Die gesamte Formel, die verwendet wird, um den dynamischen Bereich für die Fruchtauswahl^(Fruits) zu definieren , lautet:

=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 steht. Die Zahl 20 (zweimal in der Formel verwendet) ist die maximale Größe (Anzahl Zeilen) für die Liste (diese kann beliebig angepasst werden).

Beachten Sie, dass es in diesem Beispiel nur 8 Einträge in der Liste gibt, aber es gibt auch leere Zellen darunter, wo zusätzliche Einträge hinzugefügt werden könnten. Die Zahl 20 bezieht sich auf den gesamten Eingabeblock, nicht auf die tatsächliche Anzahl der Eingaben.

Lassen Sie uns nun die Formel in Teile zerlegen (jedes Teil farblich kennzeichnen), 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 FruitsHeading -Zelle), wo Auswahlmöglichkeiten eingegeben werden können. Diese OFFSET- Funktion besagt im Wesentlichen: Beginnen Sie bei der FruitsHeading -Zelle, gehen Sie 1 Zeile nach unten und über 0 Spalten und wählen Sie dann einen Bereich aus, der 20 Zeilen lang und 1 Spalte breit ist. Das gibt uns also den 20-reihigen Block, in dem die Obstauswahl^(Fruits) eingegeben wird.

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

=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 „das Obige“ ersetzt (zur besseren Lesbarkeit). Die ISBLANK -Funktion arbeitet jedoch mit dem 20-Zeilen-Bereich von Zellen, den die OFFSET - Funktion definiert.

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

Auch hier bezieht sich „das Obige“ auf die oben beschriebenen Funktionen ISBLANK und OFFSET . Die INDEX -Funktion gibt ein Array zurück, das die 20 TRUE / FALSE - Werte enthält, die von der ISBLANK- Funktion erstellt wurden.

INDEX wird normalerweise verwendet, um einen bestimmten Wert (oder Wertebereich) aus einem Datenblock auszuwählen, indem eine bestimmte Zeile und Spalte (innerhalb dieses Blocks) angegeben wird. Wenn Sie jedoch die Zeilen- und Spalteneingaben auf Null setzen (wie hier ausgeführt), gibt INDEX ein Array zurück, das den gesamten Datenblock enthält.

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

=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,the above,0)-1,20),1)

Die MATCH - Funktion gibt 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 MATCH- Funktion gibt also den Wert 9 zurück . In diesem Fall möchten wir jedoch wirklich wissen, wie viele Einträge sich in der Liste befinden, also subtrahiert die Formel 1 vom MATCH- Wert (der die Position des letzten Eintrags angibt). Letztendlich gibt MATCH ( TRUE , the above,0)-1 den Wert 8 zurück .

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

=OFFSET(FruitsHeading,1,0,IFERROR(the above,20),1)

Die Funktion WENNFEHLER^(IFERROR) gibt einen alternativen Wert zurück, wenn der erste angegebene Wert zu einem Fehler führt. Diese Funktion ist enthalten, da die MATCH^(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 sagen, dass sie nach dem ersten ^(MATCH)TRUE -Wert suchen soll (im Array von Werten aus der ISBLANK- Funktion), aber wenn KEINE^(NONE) der Zellen leer ist, wird das gesamte Array mit FALSE - Werten gefüllt. Wenn MATCH den Zielwert ( TRUE ) im durchsuchten Array nicht finden kann, gibt es einen Fehler zurück.

Wenn also die gesamte Liste voll ist (und daher MATCH einen Fehler zurückgibt), gibt die IFERROR- Funktion 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) den Bereich zurück, nach dem wir tatsächlich suchen: Beginnen Sie bei der FruitsHeading -Zelle, gehen Sie 1 Zeile nach unten und über 0 Spalten und wählen Sie dann einen Bereich aus, der so viele Zeilen lang ist wie 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 diese Formel verwenden, um den Bereich zu definieren, der die Quelle für das Dropdown-Menü ist, können Sie die Liste frei bearbeiten (Einträge hinzufügen oder entfernen, solange die verbleibenden Einträge in der obersten Zelle beginnen und zusammenhängend sind), und das Dropdown-Menü spiegelt immer den aktuellen wider Liste (siehe Abbildung 6^{(Figure 6)} ).

Die hier verwendete Beispieldatei (dynamische Listen) ist enthalten und kann von dieser Website heruntergeladen werden. Die Makros funktionieren jedoch nicht, da WordPress keine Excel- Bücher mit Makros darin 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^(NameBlock) . 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 NamesBlock OFFSET ( FruitsHeading ^(OFFSET),1,0,20,1^{(FruitsHeading,1,0,20,1)} ) ersetzt und ROWS(NamesBlock) die 20 (Anzahl der Zeilen) in der früheren Formel ersetzt.

Versuchen Sie also für Dropdown-Listen, die leicht bearbeitet werden können (auch von anderen Benutzern, die möglicherweise unerfahren sind), dynamische Bereichsnamen zu verwenden! Und beachten Sie, dass, obwohl sich dieser Artikel auf Dropdown-Listen konzentriert hat, dynamische Bereichsnamen überall dort verwendet werden können, wo Sie auf einen Bereich oder eine Liste verweisen müssen, deren Größe variieren kann. Genießen!

Use Dynamic Range Names in Excel for Flexible Dropdowns

Excel spreadsheets оften include cell dropdowns to simplify аnd/or standardize data entry.  These dropdowns are created using the data validation feature to specify a list of allowable entrіes.

To set up a simple dropdown list, select the cell where data will be entered, then click Data Validation (on the Data tab), select Data Validation, choose List (under Allow:), and then enter the list items (separated by commas) in the Source: field (see Figure 1).

In this type of basic dropdown, the list of allowable entries is specified within the data validation itself; therefore, to make changes to the list, the user must open and edit the data validation.  This may be difficult, however, for inexperienced users, or in cases where the list of choices is lengthy.

Another option is to place the list in a named range within the spreadsheet, and then specify that range name (prefaced with an equal sign) in the Source: field of the data validation (as shown in Figure 2).

This second method makes it easier to edit the choices in the list, but adding or removing items can be problematic.  Since the named range (FruitChoices, in our example) refers to a fixed range of cells ($H$3:$H$10 as shown), if more choices are added to the cells H11 or below, they will not show up in the dropdown (since those cells are not part of the FruitChoices range).

Likewise if, for example, the Pears and Strawberries entries are erased, they will no longer appear in the dropdown, but instead the dropdown will include two “empty” choices since the dropdown still references the entire FruitChoices range, including the empty cells H9 and H10.

For these reasons, when using a normal named range as the list source for a dropdown, the named range itself must be edited to include more or fewer cells if entries are added or deleted from the list.

A solution to this problem is to use a dynamic range name as the source for the dropdown choices.  A dynamic range name is one that automatically expands (or contracts) to exactly match the size of a block of data as entries are added or removed.  To do this, you use a formula, rather than a fixed range of cell addresses, to define the named range.

How to Setup a Dynamic Range in Excel

A normal (static) range name refers to a specified range of cells ($H$3:$H$10 in our example, see below):

But a dynamic range is defined using a formula (see below, taken from a separate spreadsheet which uses dynamic range names):

Before we get started, make sure you download our Excel example file (sort macros have been disabled).

Let’s examine this formula in detail.  The choices for Fruits are in a block of cells directly below a heading (FRUITS).  That heading is also assigned a name: FruitsHeading:

The entire formula used to define the dynamic range for the Fruits choices is:

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

FruitsHeading refers to the heading that is one row above the first entry in the list.  The number 20 (used two times in the formula) is the maximum size (number of rows) for the list (this can be adjusted as desired).

Note that in this example, there are only 8 entries in the list, but there are also empty cells below these where additional entries could be added.  The number 20 refers to the entire block where entries can be made, not to the actual number of entries.

Now let’s break down the formula into pieces (color-coding each piece), to understand how it works:

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

The “innermost” piece is OFFSET(FruitsHeading,1,0,20,1).  This references the block of 20 cells (underneath the FruitsHeading cell) where choices may be entered.  This OFFSET function basically says: Start at the FruitsHeading cell, go down 1 row and over 0 columns, then select an area that is 20 rows long and 1 column wide.  So that gives us the 20-row block where the Fruits choices are entered.

The next piece of the formula is the ISBLANK function:

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

Here, the OFFSET function (explained above) has been replaced with “the above” (to make things easier to read).  But the ISBLANK function is operating on the 20-row range of cells that the OFFSET function defines.

ISBLANK then creates a set of 20 TRUE and FALSE values, indicating whether each of the individual cells in the 20-row range referenced by the OFFSET function is blank (empty) or not.  In this example, the first 8 values in the set will be FALSE since the first 8 cells are not empty and the last 12 values will be TRUE.

The next piece of the formula is the INDEX function:

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

Again, “the above” refers to the ISBLANK and OFFSET functions described above.  The INDEX function returns an array containing the 20 TRUE / FALSE values created by the ISBLANK function.

INDEX is normally used to pick a certain value (or range of values) out of a block of data, by specifying a certain row and column (within that block).  But setting the row and column inputs to zero (as is done here) causes INDEX to return an array containing the entire block of data.

The next piece of the formula is the MATCH function:

=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,the above,0)-1,20),1)

The MATCH function returns the position of the first TRUE value, within the array that’s returned by the INDEX function.  Since the first 8 entries in the list are not blank, the first 8 values in the array will be FALSE, and the ninth value will be TRUE (since the 9^th row in the range is empty).

So the MATCH function will return the value of 9.  In this case, however, we really want to know how many entries are in the list, so the formula subtracts 1 from the MATCH value (which gives the position of the last entry).  So ultimately, MATCH(TRUE,the above,0)-1 returns the value of 8.

The next piece of the formula is the IFERROR function:

=OFFSET(FruitsHeading,1,0,IFERROR(the above,20),1)

The IFERROR function returns an alternate value, if the first value specified results in an error.  This function is included since, if the entire block of cells (all 20 rows) are filled with entries, the MATCH function will return an error.

This is because we’re telling the MATCH function to look for the first TRUE value (in the array of values from the ISBLANK function), but if NONE of the cells are empty, then the entire array will be filled with FALSE values.  If MATCH cannot find the target value (TRUE) in array it is searching, it returns an error.

So, if the entire list is full (and therefore, MATCH returns an error), the IFERROR function will instead return the value of 20 (knowing that there must be 20 entries in the list).

Finally, OFFSET(FruitsHeading,1,0,the above,1) returns the range we are actually looking for:  Start at the FruitsHeading cell, go down 1 row and over 0 columns, then select an area that is however many rows long as there are entries in the list (and 1 column wide).  So the entire formula together will return the range that contains only the actual entries (down to the first empty cell).

Using this formula to define the range that is the source for the dropdown means you can freely edit the list (adding or removing entries, as long as the remaining entries start at the top cell and are contiguous) and the dropdown will always reflect the current list (see Figure 6).

The example file (Dynamic Lists) that’s been used here is included and is downloadable from this website. The macros don’t work, however, because WordPress doesn’t like Excel books with macros in them.

As an alternative to specifying the number of rows in the list block, the list block can be assigned its own range name, which can then be used in a modified formula.  In the example file, a second list (Names) uses this method.  Here, the entire list block (underneath the “NAMES” heading, 40 rows in the example file) is assigned the range name of NameBlock.  The alternate formula for defining the NamesList is then:

=OFFSET(NamesHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(NamesBlock),0,0),0)-1,ROWS(NamesBlock)),1)

where NamesBlock replaces OFFSET(FruitsHeading,1,0,20,1) and ROWS(NamesBlock) replaces the 20 (number of rows) in the earlier formula.

So, for dropdown lists which can be easily edited (including by other users who may be inexperienced), try using dynamic range names!  And note that, although this article has been focused on dropdown lists, dynamic range names can be used anywhere you need to reference a range or list that can vary in size. Enjoy!

Related posts

• So löschen Sie leere Zeilen in Excel

• So verwenden Sie die Speak Cells-Funktion von Excel

• So fügen Sie ein Excel-Arbeitsblatt in ein Word-Dokument ein

• So verwenden Sie die Was-wäre-wenn-Analyse von Excel

• So reparieren Sie eine Zeile in Excel

• 2 Möglichkeiten, die Transponierungsfunktion von Excel zu verwenden

• So entfernen Sie doppelte Zeilen in Excel

• Ein fortgeschrittener VBA-Leitfaden für MS Excel

• Gruppieren Sie Zeilen und Spalten in einem Excel-Arbeitsblatt

• So sortieren Sie nach Datum in Excel

• Unterschiede zwischen Microsoft Excel Online und Excel für Desktop

• So verwenden Sie Flash Fill in Excel

• So öffnen Sie Word und Excel im abgesicherten Modus

• So suchen Sie in Excel

• Grundlegende einspaltige und mehrspaltige Datensortierung in Excel-Tabellen

• So verschieben Sie Spalten in Excel

• So entfernen Sie Gitternetzlinien in Excel

• Was ist ein VBA-Array in Excel und wie man eines programmiert

• So erstellen Sie ein VBA-Makro oder -Skript in Excel

• So fügen Sie schnell mehrere Zeilen in Excel ein