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