Tabuľky programu Excel^(Excel) často obsahujú rozbaľovacie zoznamy buniek na zjednodušenie a/alebo štandardizáciu zadávania údajov. Tieto rozbaľovacie ponuky sa vytvárajú pomocou funkcie overenia údajov na určenie zoznamu povolených položiek.

Ak chcete nastaviť jednoduchý rozbaľovací zoznam, vyberte bunku, do ktorej sa budú zadávať údaje, kliknite na položku Overenie údajov^{(Data Validation)} (na karte Údaje^(Data) ), vyberte položku Overenie údajov^{(Data Validation)} , vyberte položku Zoznam^(List) (v časti Povoliť^(Allow) :) a potom zadajte položky zoznamu (oddelené čiarkami ) v poli Zdroj^(Source) : (pozri obrázok 1).

V tomto type základného rozbaľovacieho zoznamu je zoznam povolených záznamov špecifikovaný v rámci samotnej validácie údajov; preto, aby mohol používateľ vykonať zmeny v zozname, musí otvoriť a upraviť overenie údajov. To však môže byť ťažké pre neskúsených používateľov alebo v prípadoch, keď je zoznam možností dlhý.

Ďalšou možnosťou je umiestniť zoznam do pomenovaného rozsahu v tabuľkovom hárku^{(named range within the spreadsheet)} a potom zadať názov rozsahu (s predvoleným znakom rovnosti) v poli Zdroj^(Source) : overenia údajov (ako je znázornené na obrázku 2^{(Figure 2)} ).

Táto druhá metóda uľahčuje úpravu možností v zozname, ale pridávanie alebo odstraňovanie položiek môže byť problematické. Keďže pomenovaný rozsah ( v našom príklade FruitChoices ) odkazuje na pevný rozsah buniek ($H$3:$H$10, ako je znázornené), ak sa do buniek H11 alebo nižšie pridá viac možností, v rozbaľovacej ponuke sa nezobrazia. (keďže tieto bunky nie sú súčasťou sortimentu FruitChoices ).

Podobne, ak sa napríklad vymažú položky Hrušky^(Pears) a Jahody^{(Strawberries)} , už sa nebudú zobrazovať v rozbaľovacej ponuke, ale namiesto toho bude rozbaľovacia ponuka obsahovať dve „prázdne“ možnosti, pretože rozbaľovacia ponuka stále odkazuje na celý rozsah FruitChoices vrátane prázdnych buniek H9 a H10 .

Z týchto dôvodov sa pri použití normálneho pomenovaného rozsahu ako zdroja zoznamu pre rozbaľovaciu ponuku musí samotný pomenovaný rozsah upraviť tak, aby obsahoval viac alebo menej buniek, ak sa položky pridajú alebo odstránia zo zoznamu.

Riešením tohto problému je použitie názvu dynamického^(dynamic) rozsahu ako zdroja pre rozbaľovacie možnosti. Názov dynamického rozsahu je taký, ktorý sa automaticky rozšíri (alebo zmenší) tak, aby presne zodpovedal veľkosti bloku údajov pri pridávaní alebo odstraňovaní záznamov. Ak to chcete urobiť, použite vzorec^(formula) , a nie pevný rozsah adries buniek na definovanie pomenovaného rozsahu.

Ako nastaviť dynamický rozsah^{(Dynamic Range)} v Exceli^(Excel)

Normálny (statický) názov rozsahu odkazuje na špecifikovaný rozsah buniek ($H$3:$H$10 v našom príklade, pozri nižšie):

Dynamický rozsah je však definovaný pomocou vzorca (pozri nižšie, prevzatý zo samostatnej tabuľky, ktorá používa názvy dynamických rozsahov):

Skôr ako začneme, uistite sa, že ste si stiahli náš vzorový súbor programu Excel  (makrá na triedenie boli zakázané).

Pozrime sa na tento vzorec podrobne. Voľby pre Ovocie sú v bloku buniek priamo pod nadpisom ( OVOCIE^(FRUITS) ). Tomuto nadpisu je priradený aj názov: FruitsHeading :

Celý vzorec použitý na definovanie dynamického rozsahu pre výber ovocia^(Fruits) je:

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

FruitsHeading odkazuje na nadpis, ktorý je o jeden riadok nad prvou položkou v zozname. Číslo 20 (použité dvakrát vo vzorci) je maximálna veľkosť (počet riadkov) pre zoznam (dá sa upraviť podľa želania).

Všimnite si, že v tomto príklade je v zozname iba 8 položiek, ale pod nimi sú aj prázdne bunky, do ktorých je možné pridať ďalšie položky. Číslo 20 sa vzťahuje na celý blok, v ktorom je možné vykonávať záznamy, nie na skutočný počet záznamov.

Teraz rozložme vzorec na kúsky (farebné označenie každého dielu), aby sme pochopili, ako to funguje:

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

„Najvnútornejší“ kus je OFFSET (FruitsHeading,1,0,20,1) . Toto odkazuje na blok 20 buniek (pod bunkou FruitsHeading ), kde je možné zadať voľby. Táto funkcia OFFSET v podstate hovorí: Začnite v bunke FruitsHeading , prejdite nadol o 1 riadok a viac ako 0 stĺpcov, potom vyberte oblasť, ktorá má dĺžku 20 riadkov a šírku 1 stĺpca. To nám dáva 20-riadkový blok, do ktorého sa zadávajú možnosti ovocia .^(Fruits)

Ďalším kúskom vzorca je funkcia ISBLANK :

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

Tu bola funkcia OFFSET (vysvetlená vyššie) nahradená „vyššie“ (aby sa veci ľahšie čítali). Ale funkcia ISBLANK funguje na 20-riadkovom rozsahu buniek, ktoré definuje funkcia OFFSET .

ISBLANK potom vytvorí množinu 20 hodnôt TRUE a FALSE , ktoré označujú, či je každá z jednotlivých buniek v rozsahu 20 riadkov, na ktorý odkazuje funkcia OFFSET , prázdna (prázdna) alebo nie. V tomto príklade bude prvých 8 hodnôt v množine FALSE , pretože prvých 8 buniek nie je prázdnych a posledných 12 hodnôt bude TRUE .

Ďalšou časťou vzorca je funkcia INDEX :

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

Opäť platí, že „vyššie uvedené“ sa vzťahuje na funkcie ISBLANK a OFFSET opísané vyššie. Funkcia INDEX vracia pole obsahujúce 20 hodnôt TRUE / FALSE vytvorených funkciou ISBLANK .

INDEX sa bežne používa na výber určitej hodnoty (alebo rozsahu hodnôt) z bloku údajov zadaním určitého riadka a stĺpca (v rámci tohto bloku). Ale nastavenie vstupov riadkov a stĺpcov na nulu (ako sa to robí tu) spôsobí , že INDEX vráti pole obsahujúce celý blok údajov.

Ďalšou časťou vzorca je funkcia MATCH :

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

Funkcia MATCH vráti pozíciu prvej hodnoty TRUE v rámci poľa, ktoré vráti funkcia INDEX . Keďže prvých 8 položiek v zozname nie je prázdnych, prvých 8 hodnôt v poli bude FALSE a deviata hodnota bude TRUE (keďže 9. ^riadok v rozsahu je prázdny).

Takže funkcia MATCH vráti hodnotu 9 . V tomto prípade však skutočne chceme vedieť, koľko záznamov je v zozname, takže vzorec odpočíta 1 od hodnoty MATCH (čo udáva pozíciu posledného záznamu). Takže v konečnom dôsledku MATCH ( TRUE ,vyššie uvedené,0)-1 vráti hodnotu 8 .

Ďalšou časťou vzorca je funkcia IFERROR :

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

Ak prvá zadaná hodnota spôsobí chybu, funkcia IFERROR vráti alternatívnu hodnotu. Táto funkcia je zahrnutá, pretože ak je celý blok buniek (všetkých 20 riadkov) vyplnený záznamami, funkcia MATCH vráti chybu.

Je to preto, že funkcii MATCH^(MATCH) hovoríme, aby hľadala prvú hodnotu TRUE^(TRUE) (v poli hodnôt z funkcie ISBLANK ), ale ak ŽIADNA^(NONE) z buniek nie je prázdna, potom sa celé pole vyplní hodnotami FALSE . Ak MATCH nemôže nájsť cieľovú hodnotu ( TRUE ) v poli, ktoré hľadá, vráti chybu.

Ak je teda celý zoznam plný (a preto MATCH vráti chybu), funkcia IFERROR namiesto toho vráti hodnotu 20 (s vedomím, že v zozname musí byť 20 položiek).

Nakoniec OFFSET(FruitsHeading,1,0,vyššie uvedené,1)^{(OFFSET(FruitsHeading,1,0,the above,1))} vráti rozsah, ktorý skutočne hľadáme: Začnite v bunke FruitsHeading , prejdite o 1 riadok nadol a viac ako 0 stĺpcov, potom vyberte oblasť, ktorá má dĺžku ľubovoľného počtu riadkov. v zozname sú položky (a 1 stĺpec široký). Takže celý vzorec spolu vráti rozsah, ktorý obsahuje iba skutočné položky (až po prvú prázdnu bunku).

Použitie tohto vzorca na definovanie rozsahu, ktorý je zdrojom pre rozbaľovaciu ponuku, znamená, že môžete voľne upravovať zoznam (pridávať alebo odstraňovať položky, pokiaľ zostávajúce položky začínajú v hornej bunke a sú priľahlé) a rozbaľovacia ponuka bude vždy odrážať aktuálnu zoznam (pozri obrázok 6^{(Figure 6)} ).

Vzorový súbor (dynamické zoznamy) , ktorý sa tu použil, je zahrnutý a je možné ho stiahnuť z tejto webovej stránky. Makrá však nefungujú, pretože WordPress nemá rád knihy Excelu^(Excel) s makrami.

Ako alternatívu k zadávaniu počtu riadkov v bloku zoznamu možno bloku zoznamu priradiť vlastný názov rozsahu, ktorý sa potom môže použiť v upravenom vzorci. Vo vzorovom súbore používa túto metódu druhý zoznam ( Mená ). ^(Names)Tu je celému bloku zoznamu (pod nadpisom „NAMES“, 40 riadkov v súbore príkladu) priradený názov rozsahu NameBlock . Alternatívny vzorec na definovanie NamesList je potom:

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

kde NamesBlock nahrádza OFFSET ( FruitsHeading,1,0,20,1 ) a ROWS(NamesBlock) nahrádza 20 (počet riadkov) v predchádzajúcom vzorci.

Takže pre rozbaľovacie zoznamy, ktoré možno ľahko upravovať (vrátane iných používateľov, ktorí nemusia byť skúsení), skúste použiť názvy dynamických rozsahov! A všimnite si, že hoci bol tento článok zameraný na rozbaľovacie zoznamy, názvy dynamických rozsahov možno použiť kdekoľvek, kde potrebujete odkazovať na rozsah alebo zoznam, ktorý sa môže líšiť veľkosťou. Užite si to!

Use Dynamic Range Names in Excel for Flexible Dropdowns

Exсel spreadsheets often inclυde cell dropdowns to simplify and/or standardize datа entrу.  These drоpdowns are created using the dаtа validation fеature to specifу a list of allowable entrіеs.

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

• Ako odstrániť prázdne riadky v Exceli

• Ako používať funkciu Speak Cells v Exceli

• Ako vložiť pracovný hárok programu Excel do dokumentu programu Word

• Ako používať analýzu What-If v Exceli

• Ako opraviť riadok v Exceli

• Ako vytvoriť viacero prepojených rozbaľovacích zoznamov v Exceli

• Použite Spike na vystrihnutie a prilepenie viacerých textových položiek v programe Word

• Ako odstrániť mriežku v Exceli

• Rýchlo vyhláskujte čísla vo Worde a Exceli

• Pokročilá príručka VBA pre MS Excel

• Ako používať príkazy If a vnorené If v Exceli

• Prečo by ste mali používať pomenované rozsahy v Exceli

• Zoskupte riadky a stĺpce v pracovnom hárku programu Excel

• Ako vypočítať Z-skóre v Exceli

• Ako vytvoriť koláčový graf v Exceli

• Ako vytvoriť jednoduchú kontingenčnú tabuľku v Exceli

• Tabuľky Google vs Microsoft Excel – Aké sú rozdiely?

• 4 spôsoby použitia značky začiarknutia v Exceli

• Ako používať Flash Fill v Exceli

• Ako vložiť CSV alebo TSV do pracovného hárka programu Excel