Lost your password?


Хитрости »
Основные понятия (26)
Сводные таблицы и анализ данных (10)
Графики и диаграммы (5)
Работа с VB проектом (12)
Power BI и Power Query (20)
Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (68)
Разное (43)
Баги и глюки Excel (4)

Выпадающие списки

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 KiB, 13 362 скачиваний)

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


Статья помогла? Поделись ссылкой с друзьями!
  Плейлист   Видеоуроки

Поиск по меткам

Access apple watch Multex Power Query и Power BI VBA управление кодами Бесплатные надстройки Дата и время Записки ИП Надстройки Печать Политика Конфиденциальности Почта Программы Работа с приложениями Разработка приложений Росстат Тренинги и вебинары Финансовые Форматирование Функции Excel акции MulTEx ссылки статистика
Обсуждение: 61 комментарий
  1. Zirko:

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

  2. Размеры стандартного выпадающего списка изменить нельзя. Такое возможно только в элементах форм или ActiveX.

  3. tr0mb:

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

  4. Это делается через условное форматирование. Для примера почитайте статью: Выделение текущей даты

  5. tr0mb:

    @Дмитрий(Админ)
    Огромное спасибо за быстрый ответ и в целом за сайт!

  6. Andrei:

    А возможно ли задать пароли на выбор КАЖДОМУ элементу выпадающего списка?
    Каждому элементу - свой пароль.

  7. Задать пароль нельзя. Только если программно обрабатывать событие изменения значения в ячейке.

  8. Денис:

    Здравствуйте! Шикарный сайт, все просто, ясно и понятно. Большое спасибо!!!

  9. Виктория:

    Добрый день!
    У меня небольшая проблемка. Я использую достаточно большую таблицу (количество строк к концу года переваливает за 15 000 строк) Через проверку данных создала список, сейчас на строке 3 119 последние ячейки со списком. И дальше не протягивается и не дает менять, даже существующий список (проверка данных не активна). Есть ли ограничение по колличеству ячеек?

  10. Есть ограничение на кол-во уникальных форматов/стилей ячеек - 64000. Еще кол-во имен ограничено объемом доступной оперативной памяти. Конкретно про проверку данных лично я никаких упоминаний ни в справке, ни на офф.сайте не видел, но я полагаю, что либо опер. памяти не хватает, либо достигнут предел по уникальным форматам.

Поделитесь своим мнением

Комментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум


Для оформления сообщений Вы можете использовать следующие тэги:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Тренинги

Заказать
Юридическая информация

Использование материалов сайта

Политика Конфиденциальности

ИП Щербаков Дмитрий Валентинович
ОГРНИП: 318502700083307
ИНН: 504013350772

Наши партнеры

Перейти
Перейти

Счетчики

Рейтинг@Mail.ru Яндекс.Метрика
© 2022 Excel для всех   Войти