Динамический выпадающий список в MS 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)))

  • Нажмите ОК.

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

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

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

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

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

Связанные статьи

Похожие задачи
Прочитайте другие статьи, решающие похожие задачи в MS Excel. Это позволит Вам решать широкий класс подобных задач.
Средняя: 5 (4 оценок)