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

Создание списков
Необходимо выбрать ячейку(или сразу несколько ячеек), в которую необходимо поместить этот список. В меню выбираем Данные (Data) -Проверка данных (Data Validation). Переходим на вкладку Параметры (Settings) и в выпадающем списке Тип данных (Allow) выбираем Список (List).
Поле Источник (Source): предназначено как раз для ввода значений, из которых будет состоять выпадающий список. Варианты указания значений:

  • Вариант 1
    Ввести список значений вручную (значения в этом случае необходимо заносить через "точку-с-запятой" для русской локализации и через запятую для английской).
    Параметры списка
  • Вариант 2
    Указание в качестве источника обычной ссылки на диапазон ячеек
    При использовании обычного диапазона в поле Источник (Source) просто указываем диапазон ячеек со значениями (A1:A10). Для этого ставим курсор мыши в это поле и затем выделяем необходимый диапазон со значениями. Перед адресом диапазона Excel сам поставит знак равно: =$A$1:$A$10
    Параметры списка
    Недостаток данного метода: нельзя применять ссылки на другие листы и книги. Список будет работать только в случае, если ячейки расположены на том же листе, что и сам список. Что порой не очень удобно, поэтому чаще используется Вариант 3.
  • Вариант 3
    Указание в качестве источника именованного диапазона
    При использовании именованного диапазона необходимо сначала его создать: Создание именованных диапазонов. Предположим, что мы создали именованный диапазон с именем Список1.
    После создания именованного диапазона в поле Источник (Source) вписываем имя этого диапазона, не забыв поставить перед именем знак равно:
    Параметры списка

    • Большое преимущество данного способа в том, что если имя имеет область действия Книга, то списки можно создавать из данных, расположенных на любом листе книги. Особенно это полезно для версий Excel 2007 и более ранних, т.к. для них нет возможности в качестве диапазона для выпадающего списка указать данные другого листа(не того, на котором создается сам список).

Параметры списков
Распространить изменения на другие ячейки с тем же условием (Apply this chages to all other cells with the same settings) - данный пункт понадобится уже после создания списка в ячейках: если на листе есть много разных проверок данных необходимо изменить тип или условия проверки. Выделяете одну ячейку на листе, меняете необходимые параметры проверки данных и ставите данную галку. Нажимаете ОК. Внесенные изменения будут применены для всех ячеек, в которых были такие же условия.

  • Вкладка Сообщение для ввода (Input message)
    Указывается текст, который будет отображаться при активации ячейки с проверкой данных:
    Сообщение
    это сообщение можно отключить, сняв галку с пункта Отображать подсказку, если ячейка является текущей (Show input message when cell is selected)
  • Вкладка Сообщение об ошибке (Error alert)
    Здесь указывается следует ли отображать сообщение об ошибочном вводе или нет, и сам тип выводимого сообщения об ошибке.

    • Останов (Stop), Сообщение (Message) - можно ввести только значение из выпадающего списка. Различается только внешний вид сообщения.
    • Предупреждение (Information) - помимо выбора из списка есть возможность  ввести и другое значение, которое отсутствует в списке. Может пригодится, если жесткий контроль над вводом данных не нужен и у пользователя была возможность добавить какие-то иные данные в ячейку.

Подробнее с остальными возможностями проверки данных(помимо выпадающих списков) можно ознакомиться в этой статье: Проверка данных

Скачать пример

  Выпадающие списки.xls (45,5 КиБ, 13 614 скачиваний)

Так же см.:
Связанные выпадающие списки
Что еще умеет Проверка данных

61 комментарий

  1. А вот есть ли возможность, что бы при выборе какого то значения из выпадающего списка в следующей по порядку ячейке при выборе из такого же списка этого значения уже не было?

      1. Я использовал код при создании списков и отключении, но структура такой проверки не приходит в голову...это же как то надо их перебирать, а потом из диапазона в коде
        With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=$F$4:$F$13"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
        End With
        Formula1:="=$F$4:$F$13" удалять лишние строки разбивая диапазон на разные группы(

  2. Подскажите, пожалуйста, если у меня есть список, в котором каждое значение имеет свой стиль (для простоты цвет шрифта: значение_1 - красный, значение_2 - зеленый и т.д.), как сделать что бы выбранное значение автоматически подтягивало его стиль (при выборе значения_1 - текст красный, при выборе значения_2 - текст зеленый и т.д.)?

    1. При помощи списков так не сделать. Можно лишь применить к ячейке со списком условное форматирование - тогда сразу после выбора значения из списка ячейка будет отформатировано так, как задано условием. Про условное форматирование можно почитать здесь: Основные понятия условного форматирования и как его создать

  3. Добрый день.
    Как сделать так, чтобы всплывало примечание в изменяющейся ячейке, например, при значении "1" примечание одно, при значении "2" примечание другое?
    Спасибо.

Добавить комментарий

Этот сайт использует Akismet для борьбы со спамом. Узнайте, как обрабатываются ваши данные комментариев.