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

Разрешим ввод в столбец только неповторяющихся значений с использованием специального Выпадающего списка . Для этого необходимо динамически модифицировать Выпадающий список, последовательно исключая из него только что введенные значения.

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

Задача

Создадим список сотрудников, которые должны получить премию. Список должен содержать неповторяющиеся фамилии (иначе кто-то получит 2 или 3 премии!) и все сотрудники должны быть из компании (иначе премию получат чужие!).

Решение

Сначала создадим на листе Список в диапазоне А2:А15 перечень сотрудников компании (см. файле примера ).

Создадим Динамический диапазон СотрудникиИсх с формулой =СМЕЩ(Cписок!$A$2;;;СЧЁТЗ(Cписок!$A$2:$A$15)) . Наличие Динамического диапазона позволит добавлять/ удалять фамилии в перечне сотрудников без редактирования других формул.

Ведомость для начисления премии разместим на листе Ведомость в диапазоне А11:А24 .

Создадим Динамический диапазон Ведомость с формулой =СМЕЩ(Ведомость!$A$11;;;СЧЁТЗ(Ведомость!$A$11:$A$24)) . Наличие Динамического диапазона позволит добавлять/ удалять фамилии в ведомости для начисления премии без редактирования других формул.

Фамилии сотрудников, которым полагается премия, будем вводить с помощью Выпадающего (раскрывающегося) списка . Чтобы создать Выпадающий список с фамилиями сотрудников необходимо сделать следующее:

• выделите диапазон А11:А24 на листе Ведомость ;
• вызовите инструмент Проверка данных ( Данные/ Работа с данными/ Проверка данных );
• на вкладке Параметры выберите тип данных Список ;
• в поле Формула введите: =Сотрудники
• нажмите ОК.

Имя Сотрудники ссылается на Динамический диапазон в столбце B , расположенный на листе Список и определяемый формулой =СМЕЩ(Cписок!$B$2;;;СЧЁТЕСЛИ(Cписок!$B$2:$B$15;"*"))

Этот диапазон формируется с помощью формулы массива =ИНДЕКС(СотрудникиИсх;НАИМЕНЬШИЙ( ЕСЛИ(СЧЁТЕСЛИ(Ведомость;СотрудникиИсх);"";СТРОКА(СотрудникиИсх)-СТРОКА($A$1)); СТРОКА(ДВССЫЛ("A1:A"&ЧСТРОК(СотрудникиИсх)))))

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

Тестируем

1. Введите в ячейку А13 на листе Ведомость любое значение из Выпадающего списка (например, Сидоров )

2. Попробуйте ввести в ячейку А14 снова фамилию Сидоров . Это сделать невозможно, т.к. она отсутствует в списке.

Однако, Проверка данных не позволяет гарантировано запретить ввод повторов : если выделить ячейку А14 и нажать сочетание клавиш CTRL+D , то в ячейку будет скопировано вышерасположенное значение. Добавим Условное форматирование для отображения введенных в этом случае повторов.