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

При заполнении ячеек данными иногда необходимо ограничить возможность ввода определенным списком значений. Например, при заполнении ведомости ввод фамилий сотрудников с клавиатуры можно заменить выбором из определенного заранее списка (табеля), а чтобы контролировать повторный ввод фамилий в ведомость используйте Условное форматирование .

Одним из вариантов заполнения ячеек является выбор значений из заранее определенного списка. Предположим, что в таблицу ведомости необходимо вводить фамилии сотрудников. Чтобы не ошибиться с написанием фамилий, можно предварительно создать список всех сотрудников организации, а заполнение ведомости свести к выбору фамилии из этого списка. Кроме того, необходимо контролировать повторный ввод фамилий в ведомость.

Эта задача решена в статье Ввод данных из списка значений. Часть 1. Выпадающий (раскрывающийся) список , но в ее решении отсутствует контроль повторного ввода фамилий. В этой статье мы настроим этот контроль.

Чтобы контролировать дублирование фамилий в ведомости, можно для этого настроить правила Условного форматирования (см. Файл пример а, лист Вариант1) .

• выделите ячейки ведомости А2:А8 ;
• вызовите инструмент Условное форматирование ( Главная/ Стили/ Условное форматирование/ Создать правило );
• выберите Использовать формулу для определения форматируемых ячеек
• в поле « Форматировать значения, для которых следующая формула является истинной » введите =ПОИСКПОЗ(A2;$A:$A;0)<>СТРОКА(A2)
• выберите требуемый формат, например, красный цвет заливки.

Функция ПОИСКПОЗ() (с аргументом Тип_сопоставления = 0) возвращает первую позицию аргумента Искомое_значение (в нашем случае – значение текущей ячейки) в указанном диапазоне (в нашем случае – столбец А , поэтому возвращаемая позиция совпадает с номером строки). Если фамилия уже была введена ранее, например, в ячейке А3 , то при вводе той же фамилии в ячейку А5 формула =ПОИСКПОЗ(A5;$A:$A;0)<>СТРОКА(A5) вернет результат ИСТИНА, т.к. правая часть формулы - СТРОКА(A5) вернет результат 5, а левая ПОИСКПОЗ(A5;$A:$A;0) вернет результат 3.

Задача решена. Конечно, пользователь сможет ввести повторяющиеся фамилии в ведомость, но теперь Условное форматирование подскажет о наличии ошибочных данных.

В принципе, можно вообще запретить пользователю повторный ввод фамилий. Для этого перенастроим Проверку данных для диапазона А2:А5 следующим образом (см. Файл примера , лист Вариант2) :

• выделите ячейки ведомости, куда будут вводиться фамилии сотрудников ( А2:А5 );
• вызовите инструмент Проверка данных ( Данные/ Работа с данными/ Проверка данных );
• установите условие проверки Другой ;
• в поле Формула введите: =И(СЧЁТЕСЛИ(Сотрудники;A2)=1; СЧЁТЕСЛИ($A$2:$A$5;A2)=1) ;
• перейдите во вкладку Сообщение об ошибке , в поле Сообщение введите текст: Повторный ввод фамилии запрещен! ;
• нажмите ОК.

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

СОВЕТ : Чтобы найти все ячейки на листе, к которым применены правила Условного форматирования необходимо:

• на вкладке Главная в группе Редактирование щелкните стрелку рядом с командой Найти и выделить ;
• выберите в списке пункт Условное форматирование;
• будут выделены все ячейки, которым применено Условное форматирование .

Чтобы найти все ячейки на листе, к которым применена Проверка данных необходимо:

• на вкладке Главная в группе Редактирование щелкните стрелку рядом с командой Найти и выделить ;
• выберите в списке пункт Проверка данных;
• будут выделены все ячейки, которым применена Проверка данных .