Таблицы Excel часто включают раскрывающиеся списки ячеек, чтобы упростить и/или стандартизировать ввод данных. Эти раскрывающиеся списки создаются с помощью функции проверки данных для указания списка разрешенных записей.
Чтобы настроить простой раскрывающийся список, выберите ячейку, в которую будут вводиться данные, затем нажмите Проверка данных (на вкладке Данные ), выберите «Проверка данных», выберите Список (в разделе «Разрешить:»), а затем введите элементы списка (через запятую) в поле Источник : (см. рис. 1).
В базовом раскрывающемся списке этого типа список допустимых записей указывается в самой проверке данных; поэтому, чтобы внести изменения в список, пользователь должен открыть и отредактировать проверку данных. Однако это может быть затруднительно для неопытных пользователей или в тех случаях, когда список вариантов длинный.
Другой вариант — поместить список в именованный диапазон в электронной таблице, а затем указать имя этого диапазона (с предварительным знаком равенства) в поле Источник : проверки данных. (как показано на рисунке 2).
Второй метод упрощает редактирование вариантов в списке, но добавление или удаление элементов может быть проблематичным. Поскольку именованный диапазон (FruitChoices в нашем примере) относится к фиксированному диапазону ячеек ($H$3:$H$10, как показано), если в ячейки H11 или ниже будут добавлены дополнительные варианты выбора, они не будут отображаться в раскрывающемся списке. (поскольку эти ячейки не входят в диапазон FruitChoices).
Аналогичным образом, если, например, записи «Груши» и «Клубника» будут удалены, они больше не будут отображаться в раскрывающемся списке, а вместо этого раскрывающийся список будет включать два «пустых» варианта, поскольку раскрывающийся список по-прежнему ссылается на весь диапазон FruitChoices, включая пустой ячейки H9 и H10.
По этим причинам при использовании обычного именованного диапазона в качестве источника списка для раскрывающегося списка сам именованный диапазон необходимо редактировать, чтобы он включал больше или меньше ячеек, если записи добавляются или удаляются из списка.
Решением этой проблемы является использование имени динамического диапазона в качестве источника для выбора в раскрывающемся списке. Имя динамического диапазона — это имя, которое автоматически расширяется (или сжимается), чтобы точно соответствовать размеру блока данных при добавлении или удалении записей. Для этого вы используете формулу , а не фиксированный диапазон адресов ячеек, чтобы определить именованный диапазон..
Как настроить динамический диапазон в Excel
Обычное (статическое) имя диапазона относится к указанному диапазону ячеек (в нашем примере $H$3:$H$10, см. ниже):
Но динамический диапазон определяется с помощью формулы (см. ниже, взятой из отдельной электронной таблицы, в которой используются имена динамического диапазона):
Прежде чем мы начнем, обязательно загрузите наш Пример файла Excel (макросы сортировки отключены).
Давайте рассмотрим эту формулу подробно. Варианты выбора фруктов находятся в блоке ячеек непосредственно под заголовком (ФРУКТЫ ). Этому заголовку также присвоено имя: FruitsHeading :
Вся формула, используемая для определения динамического диапазона выбора фруктов:
=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 строк, в который вводятся варианты выбора фруктов.
Следующая часть формулы — функция ПУСТО :
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(the above),0,0),0)-1,20),1)
Здесь функция СМЕЩ (описанная выше) заменена на «вышеуказанную» (чтобы облегчить чтение). Но функция ЕСПУСТО работает с диапазоном ячеек из 20 строк, который определяет функция СМЕЩ.
ISBLANK затем создает набор из 20 значений TRUE и FALSE, указывая, является ли каждая из отдельных ячеек в 20-строчном диапазоне, на который ссылается функция OFFSET, пустой (пустой) или нет. В этом примере первые 8 значений в наборе будут ЛОЖЬ, поскольку первые 8 ячеек не пусты, а последние 12 значений будут ИСТИНА..
Следующая часть формулы — функция ИНДЕКС:
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(the above,0,0),0)-1,20),1)
Опять же, «вышеупомянутое» относится к функциям ISBLANK и OFFSET, описанным выше. Функция ИНДЕКС возвращает массив, содержащий 20 значений ИСТИНА/ЛОЖЬ, созданный функцией ПУСТОЕ.
ИНДЕКС обычно используется для выбора определенного значения (или диапазона значений) из блока данных путем указания определенной строки и столбца (внутри этого блока). Но установка нуля для входных данных строки и столбца (как это сделано здесь) приводит к тому, что INDEX возвращает массив, содержащий весь блок данных.
Следующая часть формулы — функция ПОИСКПОЗ:
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,the above,0)-1,20),1)
Функция MATCH возвращает позицию первого значения TRUE в массиве, возвращенном функцией INDEX. Поскольку первые 8 записей в списке не пустые, первые 8 значений в массиве будут иметь значение FALSE, а девятое значение будет TRUE (поскольку 9thстрока в диапазоне пуста).
Итак, функция ПОИСКПОЗ вернет значение 9 . Однако в этом случае мы действительно хотим знать, сколько записей находится в списке, поэтому формула вычитает 1 из значения ПОИСКПОЗ (которое дает позицию последней записи). Таким образом, в конечном итоге, ПОИСКПОЗ(ИСТИНА,выше,0)-1 возвращает значение 8 .
Следующая часть формулы — функция ЕСЛИОШИБКА:
=OFFSET(FruitsHeading,1,0,IFERROR(the above,20),1)
Функция ЕСЛИОШИБКА возвращает альтернативное значение, если первое указанное значение приводит к ошибке. Эта функция включена, поскольку если весь блок ячеек (все 20 строк) заполнен записями, функция ПОИСКПОЗ вернет ошибку.
Это потому, что мы приказываем функции ПОИСКПОЗ искать первое значение ИСТИНА (в массиве значений функции ЕСПУСТО), но если НИ ОДНА из ячеек не пуста, то весь массив будет заполнен ЛОЖЬЮ. ценности. Если ПОИСКПОЗ не может найти целевое значение (ИСТИНА) в массиве, который он ищет, он возвращает ошибку.
Итак, если весь список заполнен (и, следовательно, ПОИСКПОЗ возвращает ошибку), функция ЕСЛИОШИБКА вместо этого вернет значение 20 (зная, что в списке должно быть 20 записей).
Наконец, OFFSET(FruitsHeading,1,0,the вышеупомянутый,1) возвращает диапазон, который мы на самом деле ищем: Начните с ячейки FruitsHeading, пройдите 1 строку вниз и более 0 столбцов, затем выберите область длиной столько строк, сколько записей в списке (и шириной 1 столбец). Таким образом, вся формула вместе вернет диапазон, содержащий только фактические записи (вплоть до первой пустой ячейки)..
Использование этой формулы для определения диапазона, являющегося источником раскрывающегося списка, означает, что вы можете свободно редактировать список (добавлять или удалять записи, если оставшиеся записи начинаются с верхней ячейки и являются смежными), а раскрывающийся список всегда будет отражать текущий список (см. рис. 6).
Использованный здесь файл примера (динамические списки) включен в комплект и его можно загрузить с этого веб-сайта. Однако макросы не работают, поскольку WordPress не любит книги Excel с макросами.
В качестве альтернативы указанию количества строк в блоке списка блоку списка можно присвоить собственное имя диапазона, которое затем можно будет использовать в измененной формуле. В файле примера этот метод используется во втором списке (Имена). Здесь всему блоку списка (под заголовком «NAMES», 40 строк в файле примера) присвоено имя диапазона NameBlock . Альтернативная формула для определения списка имен:
=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 (количество строк) в предыдущей формуле.
Итак, для выпадающих списков, которые можно легко редактировать (в том числе другим неопытным пользователям), попробуйте использовать имена динамических диапазонов! Обратите внимание: хотя эта статья посвящена раскрывающимся спискам, имена динамических диапазонов можно использовать везде, где вам нужно сослаться на диапазон или список, размер которых может различаться. Наслаждайтесь!
.