Динамические выпадающие списки Excel

Возможность проверки данных приложения Excel позволяет создавать выпадающие списки допустимых значений ячейки. Обычные статичные списки настраиваются легко, но для придания им динамики требуется нестандартный подход.

Говоря о динамических выпадающих списках, подразумевается 2 варианта:

Растущий выпадающий список

Растущим списком называется тот, для которого не требуется изменять источник, чтобы добавить новые пункты.

Рассмотрим пример создания такого списка.

На любом листе размещается перечень будущих пунктов. Далее стандартным образом настраивается правило проверки данных: Вкладка «Данные» -> Область «Работа с данными» -> Кнопка «Проверка данных» -> Тип данных «Список». Для источника необходимо создать следующую формулу:

=СМЕЩ($A$1;;;СЧЁТЗ($A:$A)), где:

  • $A$1 – ячейка со значением первого пункта списка;
  • $A:$A – столбец с перечнем всех пунктов списка.

Используя приведенную формулу, следите за отсутствием лишних значений в столбце с перечнем и пустых ячеек между пунктами, так как формула считает количество непустых ячеек и может вернуть ссылку на больший или меньший диапазон.

Параметры динамического выпадающего списка:

Параметры ростущего выпадающего списка

Связанные (зависимые) выпадающие списки

Связанными называются такие списки, когда смена значения первого полностью меняет список в зависимой ячейке. Для этого требуется создать несколько отдельных источников и присвоить им имена.

В качестве примера рассмотрим создание зависимых выпадающих списков с перечнем товаров. Сначала определим категории имеющейся продукции (это будет главный список):

  • Бытовая_техника (пробел в названии специально заменен нижним подчеркиванием, т.к. диспетчер имен, который будет использован далее, не принимает символ пробела);
  • Электроника;
  • Мебель.

Теперь для каждого значения категории создается свой список, после чего ему присваивается имя в диспетчере имен (вкладка «Формулы» -> Область «Определенные имена») согласно этому значению.

Присвоенные имена для связанных выпадающих списков excel

В завершении задаем для ячеек созданные нами связанные выпадающие списки с помощью проверки данных (вкладка «Данные» -> Область «Работа с данными»).

Для главного списка источником достаточно указать ссылку на диапазон, а для зависимых указывается формула:

=ДВССЫЛ($A$1), где:

  • $A$1 – ячейка с главным списком.

Параметры зависимых списков в excel

На этом зависимые выпадающие списки созданы, а так выглядит готовый результат:

Лист excel с перечнем для списков и ячейки с настроенными выпадающими связанными списками

Комментарии  

+1 # Александр 06.04.2016 16:46
Доброго времени суток. Подскажите, пожалуйста, возможно ли прописать автоматическое обновление, либо "обнуление" до пустого значения Зависимого списка от Главного. Например, в главном списке марки автомобилей, а в зависимом модели. Пользователь выбрал, к примеру, марку ВАЗ, затем из зависимого списка 2101. При изменении марки на ГАЗ, модель остается 2101. Как сделать так, чтобы модель при смене марки "обнулилась", т.е ячейка стала пустой? Спасибо.
Ответить | Ответить с цитатой | Цитировать
0 # Андрей 06.04.2016 21:48
Добрый день, Александр!

Такое возможно осуществить посредством макросов. Иных способов я не знаю.
Ответить | Ответить с цитатой | Цитировать
+1 # Александр Львович 12.09.2015 20:59
Здравствуйте!
Помогите, пожалуйста. Все подсказывают, как сделать выпадающие списки, но при этом в одной ячейке. А возможно ли сделать выпадающие динамические (зависимые) списки в таблице? Причём в умной? т.е. в несколько строк.
Спасибо.
Ответить | Ответить с цитатой | Цитировать
0 # Андрей 13.09.2015 19:47
Добрый день, Александр!
Как сделать зависимые списки, описано в этой статье.
Для того, чтобы сделать выпадающий список в нескольких ячейках, достаточно выделить их и произвести настройку проверки данных.
Для "умной таблицы" ничего не меняется. Выделяете нужный столбец и настраиваете. С ростом таблицы, настройки будут сохраняться для новых строк.
Ответить | Ответить с цитатой | Цитировать
+1 # Марина 26.07.2015 17:53
Добрый вечер, помогите пожалуйста.
При создании таблицы с базой данных выпадающие списки сделать получилось, но хотелось бы, чтобы в зависимости от выбора ассортимента, допустим матрас, в списке выпадали серии только матрасов, а при выборе серии матраса, выпадали модели матрасов определенной серии. Заранее спасибо.
Ответить | Ответить с цитатой | Цитировать
+1 # Андрей 27.07.2015 20:06
Марина, статья как раз описывает создание зависимых списков. Если у Вас возникли проблемы с пониманием или реализацией, то пришлите Ваш пример на электронный адрес, указанный в контактах (пункт в нижнем меню сайта). В письме опишите суть проблемы.
Ответить | Ответить с цитатой | Цитировать
+1 # Елена 21.07.2015 22:47
Добрый день
у меня возникла та же самая проблема. На какой адрес я могу отправить файл и описание?
заранее, большое спасибо
Ответить | Ответить с цитатой | Цитировать
+1 # Андрей 22.07.2015 00:21
Пункт Контакты в нижнем меню сайта, как было указано в предыдущем комментарие.
Ответить | Ответить с цитатой | Цитировать
+1 # Максим 21.05.2015 20:29
Добрый вечер. Помогите пожалуйста.
При создании зависимого выпадающего списка, после ввода в зависимый источник ячейки с главным списком и нажатии "ок", у меня выдает окно "При вычислении "Источник" возникает ошибка, продолжить?", после подтверждения зависимый список не выпадает. Делаю все как у вас в статье описано, но не получается
Ответить | Ответить с цитатой | Цитировать
+1 # Андрей 21.05.2015 21:08
Максим,
Для того, чтобы Вам помочь, нужно иметь больше информации, а лучше получить файл с неработающими списками.
Отправьте сообщение с более подробным описанием проблемы и файлом на электронный адрес, указанный в пункте контактов нижнего меню сайта.
Ответить | Ответить с цитатой | Цитировать
+1 # Юля 23.01.2016 22:54
Добрый день.
Проблема такая же как у Максима: при создании зависимого списка выпадает окно "При вычислении "Источник" возникает ошибка, продолжить?"
Кто-то нашел в чем проблема? отпишитесь плиз, ооооочень нужно решить.
Ответить | Ответить с цитатой | Цитировать
+1 # Андрей 24.01.2016 13:56
Юля, скидывайте файл с описанием на почту, указанную в контактах.
Ответить | Ответить с цитатой | Цитировать

Добавить комментарий