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

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

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

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

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

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

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