Excel -kalkylblad innehåller ofta cellrullgardinsmenyer för att förenkla och/eller standardisera datainmatning. Dessa rullgardinsmenyer skapas med hjälp av datavalideringsfunktionen för att ange en lista över tillåtna poster.

För att skapa en enkel rullgardinslista, välj cellen där data ska matas in, klicka sedan på Datavalidering^{(Data Validation)} (på fliken Data ), välj ^(Data)Datavalidering^{(Data Validation)} , välj Lista^(List) (under Tillåt^(Allow) :) och skriv sedan listobjekten (avgränsade med kommatecken) ) i fältet Källa^(Source) : (se figur 1).

I denna typ av grundläggande rullgardinsmeny anges listan över tillåtna poster i själva datavalideringen; Därför måste användaren öppna och redigera datavalideringen för att göra ändringar i listan. Detta kan dock vara svårt för oerfarna användare, eller i fall där listan med val är lång.

Ett annat alternativ är att placera listan i ett namngivet intervall i kalkylarket^{(named range within the spreadsheet)} och sedan ange det intervallnamnet (förord ​​med ett likhetstecken) i fältet Källa^(Source) : för datavalideringen (som visas i figur 2^{(Figure 2)} ).

Denna andra metod gör det lättare att redigera valen i listan, men att lägga till eller ta bort objekt kan vara problematiskt. Eftersom det namngivna intervallet ( FruitChoices , i vårt exempel) refererar till ett fast intervall av celler ($H$3:$H$10 som visas), om fler val läggs till i cellerna H11 eller nedan, kommer de inte att visas i rullgardinsmenyn (eftersom dessa celler inte är en del av FruitChoices- sortimentet).

På samma sätt, om till exempel posterna Päron^(Pears) och Jordgubbar^{(Strawberries)} raderas, kommer de inte längre att visas i rullgardinsmenyn, utan istället kommer rullgardinsmenyn att innehålla två "tomma" val eftersom rullgardinsmenyn fortfarande refererar till hela FruitChoices-intervallet, inklusive de tomma cellerna H9 och H10 .

Av dessa skäl, när du använder ett normalt namngivet område som listkälla för en rullgardinsmeny, måste själva namngivna området redigeras för att inkludera fler eller färre celler om poster läggs till eller tas bort från listan.

En lösning på detta problem är att använda ett dynamiskt^(dynamic) områdesnamn som källa för valen i rullgardinsmenyn. Ett dynamiskt intervallnamn är ett namn som automatiskt utökas (eller dras samman) för att exakt matcha storleken på ett datablock när poster läggs till eller tas bort. För att göra detta använder du en formel^(formula) , snarare än ett fast intervall av celladresser, för att definiera det namngivna intervallet.

Hur man ställer in ett dynamiskt intervall^{(Dynamic Range)} i Excel

Ett normalt (statiskt) intervallnamn hänvisar till ett specificerat cellintervall ($H$3:$H$10 i vårt exempel, se nedan):

Men ett dynamiskt intervall definieras med en formel (se nedan, hämtat från ett separat kalkylblad som använder dynamiska intervallnamn):

Innan vi börjar, se till att du laddar ner vår Excel-exempelfil  (sorteringsmakron har inaktiverats).

Låt oss undersöka denna formel i detalj. Alternativen för Frukt finns i ett cellblock direkt under en rubrik ( FRUKTER^(FRUITS) ). Den rubriken har också fått ett namn: FruitsHeading :

Hela formeln som används för att definiera det dynamiska intervallet för Frukt-^(Fruits) valen är:

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

FruitsHeading hänvisar till rubriken som är en rad ovanför den första posten i listan. Siffran 20 (används två gånger i formeln) är den maximala storleken (antal rader) för listan (detta kan justeras efter önskemål).

Observera att i det här exemplet finns det bara 8 poster i listan, men det finns även tomma celler under dessa där ytterligare poster kan läggas till. Siffran 20 hänvisar till hela blocket där poster kan göras, inte till det faktiska antalet poster.

Låt oss nu dela upp formeln i bitar (färgkoda varje del), för att förstå hur det fungerar:

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

Den "innersta" biten är OFFSET(FruitsHeading,1,0,20,1) . Detta refererar till blocket med 20 celler (under FruitsHeading -cellen) där val kan anges. Denna OFFSET- funktion säger i princip: Börja vid FruitsHeading -cellen, gå ner 1 rad och över 0 kolumner, välj sedan ett område som är 20 rader långt och 1 kolumn brett. Så det ger oss blocket med 20 rader där Frukt^(Fruits) - valen anges.

Nästa del av formeln är ISBLANK- funktionen:

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

Här har OFFSET- funktionen (förklarad ovan) ersatts med "det ovan" (för att göra saker lättare att läsa). Men ISBLANK- funktionen fungerar på det 20-radiga cellområdet som OFFSET- funktionen definierar.

ISBLANK skapar sedan en uppsättning av 20 TRUE och FALSE- värden, som indikerar om var och en av de individuella cellerna i det 20-radsintervall som refereras till av OFFSET- funktionen är tom (tom) eller inte. I det här exemplet kommer de första 8 värdena i uppsättningen att vara FALSE eftersom de första 8 cellerna inte är tomma och de sista 12 värdena kommer att vara TRUE .

Nästa del av formeln är INDEX- funktionen:

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

Återigen, "ovanstående" syftar på ISBLANK- och OFFSET- funktionerna som beskrivs ovan. Funktionen INDEX returnerar en array som innehåller de 20 TRUE / FALSE värdena som skapats av ISBLANK- funktionen.

INDEX används normalt för att välja ett visst värde (eller värdeintervall) från ett datablock, genom att specificera en viss rad och kolumn (inom det blocket). Men att ställa in rad- och kolumningångarna till noll (som görs här) gör att INDEX returnerar en array som innehåller hela datablocket.

Nästa del av formeln är MATCH- funktionen:

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

MATCH -^(MATCH) funktionen returnerar positionen för det första TRUE- värdet, inom den matris som returneras av INDEX- funktionen. Eftersom de första 8 posterna i listan inte är tomma, kommer de första 8 värdena i arrayen att vara FALSE och det nionde värdet är TRUE (eftersom den 9 ^:e raden i intervallet är tom).

Så MATCH- funktionen returnerar värdet 9 . I det här fallet vill vi dock verkligen veta hur många poster som finns i listan, så formeln subtraherar 1 från MATCH- värdet (vilket ger positionen för den sista posten). Så i slutändan returnerar MATCH ( TRUE ,ovanstående,0)-1 värdet 8 .

Nästa del av formeln är IFERROR- funktionen:

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

Funktionen IFERROR returnerar ett alternativt värde om det första angivna värdet resulterar i ett fel. Denna funktion ingår eftersom, om hela blocket av celler (alla 20 rader) är fyllda med poster, kommer MATCH- funktionen att returnera ett fel.

Detta beror på att vi säger till MATCH- funktionen att leta efter det första TRUE- värdet (i arrayen av värden från ISBLANK- funktionen), men om INGEN^(NONE) av cellerna är tomma, kommer hela arrayen att fyllas med FALSE- värden. Om MATCH inte kan hitta målvärdet ( TRUE ) i den array som den söker returnerar den ett fel.

Så om hela listan är full (och därför MATCH returnerar ett fel), returnerar funktionen IFERROR istället värdet 20 (med vetskapen om att det måste finnas 20 poster i listan).

Slutligen, OFFSET(FruitsHeading,1,0,the above,1) returnerar intervallet vi faktiskt letar efter: Börja vid FruitsHeading -cellen, gå ner 1 rad och över 0 kolumner, välj sedan ett område som är hur många rader som helst som det finns poster i listan (och 1 kolumn bred). Så hela formeln tillsammans kommer att returnera intervallet som bara innehåller de faktiska posterna (ned till den första tomma cellen).

Att använda den här formeln för att definiera intervallet som är källan för rullgardinsmenyn innebär att du fritt kan redigera listan (lägga till eller ta bort poster, så länge som de återstående posterna börjar i den översta cellen och är sammanhängande) och rullgardinsmenyn kommer alltid att spegla den aktuella lista (se figur 6^{(Figure 6)} ).

Exempelfilen (Dynamic Lists) som har använts här ingår och kan laddas ner från denna webbplats. Makron fungerar dock inte eftersom WordPress inte gillar Excel- böcker med makron i.

Som ett alternativ till att ange antalet rader i listblocket kan listblocket tilldelas ett eget områdesnamn, som sedan kan användas i en modifierad formel. I exempelfilen använder en andra lista ( Names ) denna metod. Här tilldelas hela listblocket (under rubriken "NAMES", 40 rader i exempelfilen) intervallnamnet NameBlock . Den alternativa formeln för att definiera NamesList är då:

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

där NamesBlock ersätter OFFSET ( FruitsHeading,1,0,20,1 ) och ROWS(NamesBlock) ersätter 20 (antal rader) i den tidigare formeln.

Så, för rullgardinslistor som enkelt kan redigeras (inklusive av andra användare som kanske är oerfarna), försök använda dynamiska intervallnamn! Och notera att även om den här artikeln har fokuserat på rullgardinslistor, kan dynamiska intervallnamn användas var som helst du behöver för att referera till ett intervall eller en lista som kan variera i storlek. Njut av!

Use Dynamic Range Names in Excel for Flexible Dropdowns

Excеl spreadsheets often include cell dropdowns to simplify and/or standardize data entry.  Thesе dropdowns are crеated using the data validation feature to sрecify a list 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

• Hur man tar bort tomma rader i Excel

• Hur man använder Excels Speak Cells-funktion

• Hur man infogar ett Excel-kalkylblad i ett Word-dokument

• Hur man använder Excels What-If-analys

• Hur man fixar en rad i Excel

• Hur man öppnar Word och Excel i felsäkert läge

• Grundläggande datasortering med en kolumn och flera kolumner i Excel-kalkylblad

• Hur man spårar beroende i Excel

• Hur man använder PMT-funktionen i Excel

• Hur man beräknar Z-poäng i Excel

• Hur man tar bort dubbletter av rader i Excel

• Hur man infogar CSV eller TSV i ett Excel-kalkylblad

• Använd tangentbordet för att ändra radhöjd och kolumnbredd i Excel

• 3 sätt att dela en cell i Excel

• Hur man skapar ett VBA-makro eller skript i Excel

• Centrera dina kalkylbladsdata i Excel för utskrift

• Hur man åtgärdar #N/A-fel i Excel-formler som VLOOKUP

• Hur man använder absoluta referenser i Excel

• Google Sheets vs Microsoft Excel – Vilka är skillnaderna?

• Hur man skapar flera länkade rullgardinslistor i Excel