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

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

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

• в ячейку А1 ввести заголовок Сотрудники ;
• начиная с А2 ввести перечень сотрудников;

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

• создать имя Сотрудники ( Формулы/ Определенные имена/ Присвоить имя );
• в поле Диапазон ввести =СМЕЩ(Cписок!$A$2;;;СЧЁТЗ(Cписок!$A2:$A14)-1) ;
• нажать ОК.

На листе Пример будем вводить фамилии сотрудников, которым будет выплачена премия. Задачу решим 2 способами.

Вариант 1: использование Проверки данных

• На листе Пример выделите диапазон А11:А24 ;
• Вызовите инструмент Проверка данных ( Данные/Работа с данными/Проверка данных );
• На вкладке Параметры выберите тип данных Другой ;
• В поле Формула введите:

=И(СЧЁТЕСЛИ(Сотрудники;A11)=1; СЧЁТЕСЛИ($A$11:$A$24;A11)<2)

• Нажмите ОК.

Обратите внимание, что в формуле использована относительная адресация , поэтому активной ячейкой при вводе формулы должна быть А11 (т.е. диапазон нужно выделять сверху вниз). Активная ячейка в выделенном диапазоне – белая и ее адрес отображается в поле Имя .

Чтобы запретить пропуски в диапазоне нужно изменить вышеуказанную формулу: =И(СЧЁТЕСЛИ(Сотрудники;A11)=1;СЧЁТЕСЛИ($A$11:$A$24;A11)<2;СЧЁТЕСЛИ($A$11:$A11;"")=0)

Теперь в диапазон А11:А24 можно ввести только сотрудников компании и только 1 раз. Единственное неудобство – необходимо вводить фамилии вручную. Попробуем изменить пример так, чтобы выбирать их из Выпадающего (раскрывающегося) списка , содержащего перечень сотрудников.

Вариант 2: Использование Выпадающего списка и Условного форматирования

Создадим Выпадающий список с фамилиями сотрудников.

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

Теперь фамилии можно выбирать из списка Сотрудники , но ничто нас не ограничивает ввести фамилии более 1 раза (в разные ячейки списка). Для сигнализации пользователю о вводе повторов, используем Условное форматирование .

• выделите снова диапазон B 11: B 24 ;
• вызовите Условное форматирование ( Главная/ Стили/ Условное форматирование/ Правила выделения ячеек/ Повторяющиеся значения… );
• Нажмите ОК

Теперь при вводе повторов одинаковые фамилии будут выделены.

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

СОВЕТ: Решение, сочетающее преимущества быстроты ввода Выпадающим списком (Вариант 2) и запрета на ввод повторов (Вариант 1) существует, и о нем написано в статье Создание списка неповторяющихся значений с использованием Динамического выпадающего списка .