Excel -regneark inkluderer ofte rullemenuer for at forenkle og/eller standardisere dataindtastning. Disse rullemenuer oprettes ved hjælp af datavalideringsfunktionen for at angive en liste over tilladte poster.

For at opsætte en simpel rulleliste skal du vælge den celle, hvor data skal indtastes, derefter klikke på Datavalidering^{(Data Validation)} (på fanen Data ), vælge Datavalidering^{(Data Validation)} , vælge Liste^(List) (under Tillad^(Allow) :), og derefter indtaste listeelementerne (adskilt af kommaer) ) i feltet Kilde^(Source) : (se figur 1).

I denne type grundlæggende dropdown er listen over tilladte poster angivet i selve datavalideringen; Derfor skal brugeren åbne og redigere datavalideringen for at foretage ændringer i listen. Dette kan dog være svært for uerfarne brugere eller i tilfælde, hvor listen over valgmuligheder er lang.

En anden mulighed er at placere listen i et navngivet område i regnearket^{(named range within the spreadsheet)} og derefter angive dette områdenavn (forordet med et lighedstegn) i feltet Kilde^(Source) : i datavalideringen (som vist i figur 2^{(Figure 2)} ).

Denne anden metode gør det lettere at redigere valgene på listen, men tilføjelse eller fjernelse af elementer kan være problematisk. Da det navngivne område ( FruitChoices , i vores eksempel) refererer til et fast celleområde ($H$3:$H$10 som vist), vil de ikke vises i rullemenuen , hvis der tilføjes flere valgmuligheder til cellerne H11 eller derunder. (da disse celler ikke er en del af FruitChoices- serien).

Ligeledes hvis f.eks. Pærer^(Pears) og Jordbær-^{(Strawberries)} indtastninger slettes, vises de ikke længere i rullemenuen, men i stedet vil rullemenuen indeholde to "tomme" valg, da rullemenuen stadig refererer til hele FruitChoices-området, inklusive de tomme celler H9 og H10 .

Af disse grunde, når du bruger et normalt navngivet område som listekilde for en dropdown, skal selve det navngivne område redigeres for at inkludere flere eller færre celler, hvis poster tilføjes eller slettes fra listen.

En løsning på dette problem er at bruge et dynamisk^(dynamic) områdenavn som kilde til dropdown-valgene. Et dynamisk områdenavn er et, der automatisk udvides (eller trækkes sammen) for nøjagtigt at matche størrelsen på en datablok, efterhånden som indgange tilføjes eller fjernes. For at gøre dette skal du bruge en formel^(formula) i stedet for et fast område af celleadresser til at definere det navngivne område.

Sådan opsætter du et dynamisk område^{(Dynamic Range)} i Excel

Et normalt (statisk) områdenavn refererer til et specificeret celleområde ($H$3:$H$10 i vores eksempel, se nedenfor):

Men et dynamisk område er defineret ved hjælp af en formel (se nedenfor, taget fra et separat regneark, der bruger dynamiske områdenavne):

Før vi går i gang, skal du sørge for at downloade vores Excel-eksempelfil  (sorteringsmakroer er blevet deaktiveret).

Lad os undersøge denne formel i detaljer. Valgene for Frugt er i en blok af celler direkte under en overskrift ( FRUITS ). Denne overskrift er også tildelt et navn: FruitsHeading :

Hele formlen, der bruges til at definere det dynamiske område for Frugt-^(Fruits) valgene, er:

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

FruitsHeading refererer til overskriften, der er en række over den første post på listen. Tallet 20 (brugt to gange i formlen) er den maksimale størrelse (antal rækker) for listen (dette kan justeres efter ønske).

Bemærk, at i dette eksempel er der kun 8 poster på listen, men der er også tomme celler under disse, hvor yderligere poster kan tilføjes. Tallet 20 refererer til hele blokken, hvor der kan indtastes, ikke til det faktiske antal poster.

Lad os nu opdele formlen i stykker (farvekodning af hvert stykke), for at forstå, hvordan det virker:

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

Det "inderste" stykke er OFFSET(FruitsHeading,1,0,20,1) . Dette refererer til blokken med 20 celler (under FruitsHeading -cellen), hvor valg kan indtastes. Denne OFFSET - funktion siger grundlæggende: Start ved FruitsHeading -cellen, gå 1 række ned og over 0 kolonner, og vælg derefter et område, der er 20 rækker langt og 1 kolonne bredt. Så det giver os den 20-rækkede blok, hvor Frugt^(Fruits) - valgene indtastes.

Det næste stykke af formlen er ISBLANK- funktionen:

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

Her er OFFSET- funktionen (forklaret ovenfor) blevet erstattet med "ovenstående" (for at gøre tingene lettere at læse). Men ISBLANK -funktionen fungerer på det 20-rækkers celleområde, som OFFSET - funktionen definerer.

ISBLANK opretter derefter et sæt af 20 SAND-^(TRUE) og FALSK^(FALSE) -værdier, der angiver, om hver af de individuelle celler i 20-rækkernes område, der refereres til af OFFSET - funktionen, er tom (tom) eller ej. I dette eksempel vil de første 8 værdier i sættet være FALSK^(FALSE) , da de første 8 celler ikke er tomme, og de sidste 12 værdier vil være TRUE .

Det næste stykke af formlen er INDEX- funktionen:

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

Igen henviser "ovenstående" til ISBLANK- og OFFSET- funktionerne beskrevet ovenfor. INDEX - funktionen returnerer et array, der indeholder de 20 TRUE / FALSE - værdier, der er oprettet af ISBLANK- funktionen.

INDEX bruges normalt til at vælge en bestemt værdi (eller værdiområde) ud af en datablok ved at angive en bestemt række og kolonne (inden for denne blok). Men indstilling af række- og kolonneinput til nul (som det gøres her) får INDEX til at returnere et array, der indeholder hele datablokken.

Det næste stykke af formlen er MATCH- funktionen:

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

MATCH - funktionen returnerer positionen af ​​den første SAND^(TRUE) -værdi inden for det array, der returneres af INDEX - funktionen. Da de første 8 poster på listen ikke er tomme, vil de første 8 værdier i arrayet være FALSE , og den niende værdi vil være TRUE (da den 9. ^række i området er tom).

Så MATCH- funktionen returnerer værdien af ​​9 . I dette tilfælde vil vi dog rigtig gerne vide, hvor mange poster der er på listen, så formlen trækker 1 fra MATCH- værdien (som giver positionen for den sidste post). Så i sidste ende returnerer MATCH ( TRUE ,ovenstående,0)-1 værdien af ​​8 .

Det næste stykke af formlen er IFERROR- funktionen:

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

Funktionen HVISER^(IFERROR) returnerer en alternativ værdi, hvis den første angivne værdi resulterer i en fejl. Denne funktion er inkluderet, da hvis hele blokken af ​​celler (alle 20 rækker) er fyldt med poster, vil MATCH - funktionen returnere en fejl.

Dette skyldes, at vi fortæller MATCH- funktionen at lede efter den første TRUE -værdi (i rækken af ​​værdier fra ISBLANK- funktionen), men hvis INGEN^(NONE) af cellerne er tomme, vil hele arrayet være fyldt med FALSE - værdier. Hvis MATCH ikke kan finde målværdien ( TRUE ) i det array, det søger, returnerer det en fejl.

Så hvis hele listen er fuld (og derfor returnerer MATCH en fejl), vil HVIS-^(IFERROR) funktionen i stedet returnere værdien 20 (velvidende at der skal være 20 poster på listen).

Til sidst returnerer OFFSET(FruitsHeading,1,0,the above,1) det område, vi faktisk leder efter: Start ved FruitsHeading -cellen, gå 1 række ned og over 0 kolonner, og vælg derefter et område, der dog er mange rækker langt som der er poster i listen (og 1 kolonne bred). Så hele formlen tilsammen vil returnere det område, der kun indeholder de faktiske poster (ned til den første tomme celle).

Ved at bruge denne formel til at definere det område, der er kilden til rullemenuen, betyder det, at du frit kan redigere listen (tilføje eller fjerne poster, så længe de resterende poster starter i den øverste celle og er sammenhængende), og rullemenuen vil altid afspejle den aktuelle liste (se figur 6^{(Figure 6)} ).

Eksempelfilen (Dynamiske lister) , der er blevet brugt her, er inkluderet og kan downloades fra denne hjemmeside. Makroerne virker dog ikke, fordi WordPress ikke kan lide Excel- bøger med makroer i.

Som et alternativ til at angive antallet af rækker i listeblokken, kan listeblokken tildeles sit eget områdenavn, som derefter kan bruges i en ændret formel. I eksempelfilen bruger en anden liste ( Names ) denne metode. Her tildeles hele listeblokken (under "NAMES"-overskriften, 40 rækker i eksempelfilen) områdenavnet NameBlock . Den alternative formel til at definere navnelisten^(NamesList) er så:

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

hvor NamesBlock erstatter OFFSET ( FruitsHeading,1,0,20,1 ) og ROWS(NamesBlock) erstatter de 20 (antal rækker) i den tidligere formel.

Så for dropdown-lister, som nemt kan redigeres (inklusive af andre brugere, som måske er uerfarne), prøv at bruge dynamiske områdenavne! Og bemærk, at selvom denne artikel har været fokuseret på dropdown-lister, kan dynamiske områdenavne bruges overalt, hvor du har brug for at referere til et område eller en liste, der kan variere i størrelse. God fornøjelse!

Use Dynamic Range Names in Excel for Flexible Dropdowns

Excel spreadsheets often inclυde cell dropdowns to simplify and/or standardize data entry.  These dropdowns are created using the data validation feature to specify a lіst of allowable entries.

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

• Sådan slettes tomme linjer i Excel

• Sådan bruger du Excels Tal Cells-funktion

• Sådan indsætter du et Excel-regneark i et Word-dokument

• Sådan bruges Excels What-If-analyse

• Sådan rettes en række i Excel

• Grundlæggende én-kolonne og multi-kolonne datasortering i Excel-regneark

• 4 måder at bruge et flueben i Excel

• Sådan sorteres efter dato i Excel

• Brug tastaturet til at ændre rækkehøjde og kolonnebredde i Excel

• 2 måder at bruge Excels transponeringsfunktion

• Sådan opretter du en VBA-makro eller et script i Excel

• Sådan rettes #N/A-fejl i Excel-formler som VLOOKUP

• Hvornår skal man bruge Index-Match i stedet for VLOOKUP i Excel

• Sådan skriver du en IF-formel/-erklæring i Excel

• Tilslutning af Excel til MySQL

• Sådan indsætter du CSV eller TSV i et Excel-regneark

• Brug Spike til at klippe og indsætte flere tekstelementer i Word

• Hvad er et VBA-array i Excel, og hvordan programmeres et

• Gruppér rækker og kolonner i et Excel-regneark

• Sådan opretter du flere linkede rullelister i Excel