Ввод данных из списка значений. Часть 2: Выпадающий список в MS 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);
  • перейдите во вкладку Сообщение об ошибке, в поле Сообщение введите текст: Повторный ввод фамилии запрещен!;
  • нажмите ОК.

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

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

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

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

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

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

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

Комментарии

Артур (не проверено)

Как можно сделать, что бы искомое значение выдавалось при вводе символов в ячейке из диапазона данных. Например: есть список Фамилий, как в примере только с Именами. Вводим в ячейку Фамилию и в раскрывающемся списке, предлагаются варианты, которые можно выбрать. Некое подобие фиьтра, реализованного в 2010 версии, строка поиск. Возможно такое сделать?

Creator

Нет, нельзя стандартными средствами. Пока Вы не завершили ввод значения в ячейку (нажатием ENTER, TAB) значение ячейки не пересчитывается (не обновляется), поэтому выпадающий список модифицироваться не будет (на VBA также нельзя обработать событие ввода отдельного символа, т.к. событие Worksheet_Change после нажатия ENTER). Посмотрите статью http://excel2.ru/articles/dinamicheskiy-vypadayushchiy-spisok-v-ms-excel может Вам подойдет такое решение.