Как создать динамический список в excel

Динамический выпадающий список в EXCEL

history 21 января 2013 г.

Группы статей
• Выпадающий список
• Проверка данных
• Создание списка неповторяющихся значений

При заполнении ячеек данными, бывает необходимо ограничить возможность ввода определенным списком значений – это можно сделать с помощью Выпадающего списка . Если одновременно необходимо обеспечить ввод только неповторяющихся значений, то необходим Динамический выпадающий список: с помощью него пользователь не сможет дважды выбрать одно и тоже значение.

«Динамизм» Динамического выпадающего списка заключается в следующем: после ввода в столбец какого-нибудь значения из Выпадающего списка , список изменяется – введенное значение исчезает из Выпадающего списка . Таким образом, Динамический выпадающий список может обеспечить ввод в диапазон только неповторяющихся значений (см. файл примера ).

Алгоритм решения задачи следующий:

• создаем на листе Список исходный перечень элементов Выпадающего (раскрывающегося) списка , например перечень сотрудников компании;
• на листе ДинамическийСписок определяем диапазон для ввода сотрудников, например, выдвинутых на премию. Диапазон должен содержать неповторяющиеся фамилии (иначе кто-то получит 2 или 3 премии!) и все сотрудники должны быть из компании (иначе премию получат чужие!)
• создаем на листе Список модифицированный перечень сотрудников, не содержащий фамилии, которые уже введены на листе ДинамическийСписок ;
• создаем обычный Выпадающий список на основе модифицированного перечня сотрудников, созданного на предыдущем шаге.

• На листе Список , в столбце А создадим исходный перечень фамилий сотрудников. Введем заголовок – Сотрудники .

• На листе ДинамическийСписок определяем диапазон, в который будут вводиться фамилии сотрудников с помощью Выпадающего списка ( Ведомость для премии ). Это диапазон A3:A16 .
• На листе Список , в столбце B , напротив каждого значения из исходного перечня введем формулу = ЕСЛИ(СЧЁТЕСЛИ(ДинамическийСписок!$A$3:$A$16;A2);"";СТРОКА())

Формула ищет уже введенные в диапазон A3:A16 на листе ДинамическийСписок фамилии и, в случае успеха, возвращает значение Пустой текст ("") . Если значение не найдено, то выводится номер строки, в которой находится формула;

• На листе Список , в ячейке С2 , введем Формулу массива = ЕСЛИОШИБКА(ИНДЕКС($A$2:$A$9; НАИМЕНЬШИЙ($B$2:$B$9;СТРОКА(1:1))-СТРОКА($A$1));"") После ввода вместо ENTER нажмите CTRL+SHIFT+ENTER .

Разберем работу формулы подробнее. Функция НАИМЕНЬШИЙ() сортирует по возрастанию столбец B и для каждой строки выводит значение. Функция ИНДЕКС() , в зависимости от результата функции НАИМЕНЬШИЙ() , извлекает фамилии из исходного перечня. Формулу скопируйте вниз до конца исходного перечня сотрудников. В результате в столбце С формируется перечень фамилий, еще не введенных на листе ДинамическийСписок . Это как раз наша цель. Промежуточный результат легко увидеть с помощью клавиши F9 (например, выделите в строке формул НАИМЕНЬШИЙ($B$2:$B$9;СТРОКА(1:1)) , нажмите F9 — вместо формулы отобразится ее результат).

Чтобы наш Динамический выпадающий список содержал именно столько позиций, сколько имеется фамилий в столбце С , создадим Динамический диапазон :

• На вкладке Формулы в группе Определенные имена выберите команду Присвоить имя ;
• В поле Имя введите: Сотрудники ;
• В поле Область введите Книга ;
• В поле Диапазон введите формулу

Примечание : для числовых значений используйте = СМЕЩ(Cписок!$C$2;;;СУММПРОИЗВ(—ЕЧИСЛО(Cписок!$C$2:$C$29)))

Завершающий шаг: создаем Динамический выпадающий список :

• На листе ДинамическийСписок выделим диапазон, в который будут вводиться значения с использованием Динамического выпадающего списка ( A3:A16 );
• Вызываем инструмент Проверка данных ( Данные/ Работа с данными/ Проверка данных ) , устанавливаем тип данных Список , в поле Источник указываем = Сотрудники

Протестируем наше решение. На листе ДинамическийСписок с помощью выпадающего списка выберем фамилию Сидоров .

Затем, в ячейке ниже, вызовем выпадающий список и убедимся, что он не содержит фамилию Сидоров .

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Если материалы office-menu.ru Вам помогли, то поддержите, пожалуйста, проект, чтобы я мог развивать его дальше.

Комментарии

Добрый день, Александр!

Такое возможно осуществить посредством макросов. Иных способов я не знаю.