Создание списка неповторяющихся значений с использованием Динамического выпадающего списка в MS 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, то в ячейку будет скопировано вышерасположенное значение. Добавим Условное форматирование для отображения введенных в этом случае повторов.

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

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