Електронните таблици на Excel^(Excel) често включват падащи менюта на клетки за опростяване и/или стандартизиране на въвеждането на данни. Тези падащи менюта се създават с помощта на функцията за валидиране на данни, за да посочите списък с допустими записи.

За да настроите прост падащ списък, изберете клетката, в която ще бъдат въведени данните, след това щракнете върху Проверка^{(Data Validation)} на данни (в раздела Данни^(Data) ), изберете Проверка на данни^{(Data Validation)} , изберете Списък^(List) (под Разреши^(Allow) :) и след това въведете елементите от списъка (разделени със запетаи ) в полето Източник^(Source) : (вижте фигура 1).

В този тип основно падащо меню списъкът с допустими записи се посочва в рамките на самото валидиране на данни; следователно, за да направи промени в списъка, потребителят трябва да отвори и редактира проверката на данните. Това обаче може да е трудно за неопитни потребители или в случаите, когато списъкът с възможности за избор е дълъг.

Друга възможност е да поставите списъка в наименуван диапазон в електронната таблица^{(named range within the spreadsheet)} и след това да посочите това име на диапазон (предварително със знак за равенство) в полето Източник^(Source) : на валидирането на данните (както е показано на Фигура 2^{(Figure 2)} ).

Този втори метод улеснява редактирането на изборите в списъка, но добавянето или премахването на елементи може да бъде проблематично. Тъй като посоченият диапазон ( FruitChoices , в нашия пример) се отнася до фиксиран диапазон от клетки ($H$3:$H$10, както е показано), ако към клетките H11 или по-долу се добавят повече възможности за избор, те няма да се покажат в падащото меню (тъй като тези клетки не са част от гамата FruitChoices ).

По същия начин, ако например записи за круши^(Pears) и ягоди^{(Strawberries)} бъдат изтрити, те вече няма да се показват в падащото меню, но вместо това падащото меню ще включва два „празни“ избора, тъй като падащото меню все още препраща към целия диапазон на FruitChoices, включително празните клетки H9 и H10 .

Поради тези причини, когато използвате нормален именуван диапазон като източник на списък за падащо меню, самият наименуван диапазон трябва да бъде редактиран, за да включва повече или по-малко клетки, ако записи се добавят или изтриват от списъка.

Решението на този проблем е да се използва име на динамичен^(dynamic) диапазон като източник на падащите опции. Името на динамичен диапазон е това, което автоматично се разширява (или свива), за да съответства точно на размера на блок от данни при добавяне или премахване на записи. За да направите това, използвате формула^(formula) , а не фиксиран диапазон от адреси на клетки, за да дефинирате наименувания диапазон.

Как да настроите динамичен диапазон^{(Dynamic Range)} в Excel

Нормално (статично) име на диапазон се отнася до определен диапазон от клетки ($H$3:$H$10 в нашия пример, вижте по-долу):

Но динамичният диапазон се дефинира с помощта на формула (вижте по-долу, взета от отделна електронна таблица, която използва имена на динамичен диапазон):

Преди да започнем, уверете се, че сте изтеглили нашия примерен файл на Excel  (макросите за сортиране са деактивирани).

Нека разгледаме тази формула подробно. Изборите за Плодове са в блок от клетки точно под заглавие ( Плодове^(FRUITS) ). На това заглавие е присвоено и име: FruitsHeading :

Цялата формула, използвана за определяне на динамичния диапазон за избора на плодове , е:^(Fruits)

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

FruitsHeading се отнася до заглавието, което е един ред над първия запис в списъка. Числото 20 (използвано два пъти във формулата) е максималният размер (брой редове) за списъка (това може да се коригира по желание).

Имайте предвид, че в този пример има само 8 записа в списъка, но има и празни клетки под тях, където могат да се добавят допълнителни записи. Числото 20 се отнася до целия блок, където могат да се правят записи, а не до действителния брой записи.

Сега нека разбием формулата на части (цветово кодиране на всяко парче), за да разберем как работи:

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

„Най-вътрешната“ част е OFFSET(FruitsHeading,1,0,20,1) . Това препраща към блока от 20 клетки (под клетката FruitsHeading ), където могат да бъдат въведени избори. Тази функция OFFSET основно казва: Започнете от клетката FruitsHeading , слезте надолу с 1 ред и над 0 колони, след което изберете област, която е дълга 20 реда и широка 1 колона. Това ни дава 20-редовия блок, където се въвеждат изборите на плодове .^(Fruits)

Следващата част от формулата е функцията ISBLANK :

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

Тук функцията OFFSET (обяснена по-горе) е заменена с „горе“ (за да направи нещата по-лесни за четене). Но функцията ISBLANK работи върху 20-редовия диапазон от клетки, който функцията OFFSET дефинира.

След това ISBLANK^(ISBLANK) създава набор от 20 TRUE и FALSE стойности, показващи дали всяка от отделните клетки в диапазона от 20 реда, посочен от функцията OFFSET , е празна (празна) или не. В този пример първите 8 стойности в набора ще бъдат FALSE , тъй като първите 8 клетки не са празни, а последните 12 стойности ще бъдат TRUE .

Следващата част от формулата е функцията INDEX :

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

Отново, „горе“ се отнася до функциите ISBLANK^(ISBLANK) и OFFSET , описани по-горе. Функцията INDEX връща масив, съдържащ 20-те TRUE / FALSE стойности, създадени от функцията ISBLANK .

INDEX обикновено се използва за избиране на определена стойност (или диапазон от стойности) от блок данни, чрез посочване на определен ред и колона (в рамките на този блок). Но задаване на входните данни за редове и колони на нула (както е направено тук) кара INDEX да върне масив, съдържащ целия блок от данни.

Следващата част от формулата е функцията MATCH :

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

Функцията MATCH връща позицията на първата стойност TRUE в масива, който се връща от функцията INDEX . Тъй като първите 8 записа в списъка не са празни, първите 8 стойности в масива ще бъдат FALSE , а деветата стойност ще бъде TRUE (тъй като 9 ^-ия ред в диапазона е празен).

Така че функцията MATCH ще върне стойността на 9 . В този случай обаче ние наистина искаме да знаем колко записа има в списъка, така че формулата изважда 1 от стойността MATCH (която дава позицията на последния запис). Така че в крайна сметка MATCH ( TRUE , горното, 0)-1 връща стойността на 8 .

Следващата част от формулата е функцията IFERROR :

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

Функцията IFERROR връща алтернативна стойност, ако първата посочена стойност води до грешка. Тази функция е включена, тъй като, ако целият блок клетки (всички 20 реда) са запълнени с записи, функцията MATCH ще върне грешка.

Това е така, защото казваме на функцията MATCH да търси първата стойност TRUE (в масива от стойности от функцията ISBLANK ), но ако НИКОЯ^(NONE) от клетките не е празна, тогава целият масив ще бъде запълнен с FALSE стойности. Ако MATCH не може да намери целевата стойност ( TRUE ) в масива, който търси, връща грешка.

Така че, ако целият списък е пълен (и следователно MATCH връща грешка), функцията IFERROR вместо това ще върне стойността от 20 (знаейки, че трябва да има 20 записа в списъка).

И накрая, OFFSET(FruitsHeading,1,0,горе,1)^{(OFFSET(FruitsHeading,1,0,the above,1))} връща диапазона, който всъщност търсим: Започнете от клетката FruitsHeading , слезте надолу с 1 ред и над 0 колони, след което изберете област, която е дълга колкото и много редове има записи в списъка (и ширина 1 колона). Така цялата формула заедно ще върне диапазона, който съдържа само действителните записи (до първата празна клетка).

Използването на тази формула за дефиниране на диапазона, който е източник на падащото меню, означава, че можете свободно да редактирате списъка (добавяне или премахване на записи, стига останалите записи да започват от горната клетка и да са съседни) и падащото меню винаги ще отразява текущия списък (виж фигура 6^{(Figure 6)} ).

Примерният файл (Динамични списъци) , който е използван тук, е включен и може да се изтегли от този уебсайт. Макросите обаче не работят, защото WordPress не харесва книги на Excel с макроси в тях.^(Excel)

Като алтернатива на определянето на броя на редовете в списъчния блок, на списъчния блок може да бъде присвоено собствено име на диапазон, което след това може да се използва в модифицирана формула. В примерния файл втори списък ( Имена^(Names) ) използва този метод. Тук на целия списъчен блок (под заглавието „NAMES“, 40 реда в примерния файл) се присвоява името на диапазона на NameBlock . След това алтернативната формула за дефиниране на списъка с имена^(NamesList) е:

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

където NamesBlock замества OFFSET ( FruitsHeading,1,0,20,1 ) и ROWS(NamesBlock) замества 20 (брой редове) в по-ранната формула.

Така че, за падащи списъци, които могат лесно да се редактират (включително от други потребители, които може да са неопитни), опитайте да използвате имена на динамичен диапазон! И имайте предвид, че въпреки че тази статия е фокусирана върху падащи списъци, имената на динамични диапазони могат да се използват навсякъде, където трябва да препратите диапазон или списък, които могат да варират по размер. Наслади се!

Use Dynamic Range Names in Excel for Flexible Dropdowns

Excel spreadsheetѕ often include cell dropdowns to simplify and/or standardize data entry.  Theѕe dropdowns are created using the dаta validation feature to specify a list of allоwable 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

• Как да изтриете празни редове в Excel

• Как да използвате функцията "Говорете клетки" на Excel

• Как да вмъкнете работен лист на Excel в Word Doc

• Как да използвате анализа какво-ако на Excel

• Как да коригирате ред в Excel

• 5 начина за конвертиране на текст в числа в Excel

• Как да използвате абсолютни препратки в Excel

• 2 начина за използване на функцията за транспониране на Excel

• Как да обедините данни в няколко файла на Excel

• Как да местите колони в Excel

• Разширено VBA ръководство за MS Excel

• Как да отворите Word и Excel в безопасен режим

• Как да проследите зависими в Excel

• Какво е VBA масив в Excel и как да го програмирам

• Как да напишете IF формула/изявление в Excel

• Как да изчислим Z-Score в Excel

• Разлики между Microsoft Excel онлайн и Excel за настолен компютър

• Как да направите кръгова диаграма в Excel

• Използвайте Spike, за да изрежете и поставите множество текстови елементи в Word

• 4 начина да конвертирате Excel в Google Sheets