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

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

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

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

• создаем на листе Список исходный перечень элементов Выпадающего (раскрывающегося) списка , например перечень сотрудников компании;
• на листе ДинамическийСписок определяем диапазон для ввода сотрудников, например, выдвинутых на премию. Диапазон должен содержать неповторяющиеся фамилии (иначе кто-то получит 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;"*?"))

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

• Нажмите ОК.

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

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

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

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

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