Электронные таблицы 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, как показано), если в ячейки ^{(FruitChoices)}H11 или ниже добавить дополнительные варианты , они не будут отображаться в раскрывающемся списке. (поскольку эти ячейки не входят в диапазон FruitChoices ).

Аналогичным образом, если, например, записи « Груши^(Pears) » и « Клубника^{(Strawberries)} » удалены, они больше не будут отображаться в раскрывающемся списке, а вместо этого в раскрывающемся списке будут два «пустых» варианта, поскольку раскрывающийся список по-прежнему ссылается на весь диапазон FruitChoices, включая пустые ячейки H9 и Н10^(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) .

Затем IПУСТО^(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) и СМЕЩЕНИЕ . ^(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,the выше,1)^{(OFFSET(FruitsHeading,1,0,the above,1))} возвращает диапазон, который мы на самом деле ищем: начните с ячейки FruitsHeading , спуститесь на 1 строку и более чем на 0 столбцов, затем выберите область, длина которой равна количеству строк. в списке есть записи (и 1 столбец шириной). Таким образом, вся формула вместе вернет диапазон, содержащий только фактические записи (вплоть до первой пустой ячейки).

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

Используемый здесь файл примера (динамические списки) включен и доступен для загрузки с этого веб-сайта. Однако макросы не работают, потому что WordPress не любит книги 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 spreadsheets often include cell dropdowns to simplify and/or standardize data entry.  These dropdowns аre created using the data validation feature to ѕpecify 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

• Как удалить пустые строки в Excel

• Как использовать функцию «Говорить ячейки» в Excel

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

• Как использовать анализ «что, если» в Excel

• Как исправить строку в Excel

• Подключение Excel к MySQL

• Как использовать операторы If и вложенные операторы If в Excel

• Как объединить ячейки, столбцы и строки в Excel

• Как использовать Flash Fill в Excel

• Как написать формулу/оператор ЕСЛИ в Excel

• Как быстро вставить несколько строк в Excel

• 3 способа разделить ячейку в Excel

• Используйте клавиатуру для изменения высоты строки и ширины столбца в Excel

• Как рассчитать Z-показатель в Excel

• Почему вы должны использовать именованные диапазоны в Excel

• Как искать в Excel

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

• Когда использовать Index-Match вместо VLOOKUP в Excel

• Как сделать круговую диаграмму в Excel

• Группировать строки и столбцы на листе Excel