Выпадающие списки.
Создание и настройка выпадающих списков.





Как ограничить ввод данных в ячейке списком допустимых значений (справочником)? Где расположить этот справочник. Команда Проверка данных содержит такую настройку.

Конспекты

Курсы

Практикум


Скачать файл
Теги: Бесплатный курс Excel, самоучитель Excel, конспекты тренинга Excel, без регистрации.

Создание и настройка выпадающих списков.

Как ограничить ввод данных в ячейке списком допустимых значений (справочником)? Где расположить этот справочник. Команда Проверка данных содержит такую настройку. Инструмент Проверка данных можно использовать для создания жёсткого списка ограничений по вводу значений, а можно сделать его более "мягким" и позволить пользователю вводить свои названия, если в списке нет нужного. Исходный список при этом не страдает. Правда, любопытно? И в ряде бизнес-задач - крайне удобно.

Создание выпадающих списков. Ввод данных не из списка. Оформление примечаний

Для занятия Вам потребуется книга Excel с заготовками таблиц, по которым составлен конспект - этот файл прикреплен в самом низу страницы (без проблем откроется в Excel версии 2007 и новее)

 

Как сделать выпадающий список

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

Разбирать инструменты для создания выпадающего списка будем на примере составления таблицы с планом реализации на следующий месяц. Кстати, у моей таблицы всегда актуальный заголовок – посмотрите, какая там использована формула :-)

Итак, мы соорудили макет таблицы, которую необходимо будет заполнить следующим образом: внести название контрагента и кол-во единиц отгрузки, а далее функция ВПР [VLOOKUP] поищет на листе Список нужную цену по названию контрагента, дальнейшие расчеты в таблице зависят от этого результата. Формулы уже написаны – их можно посмотреть. Но смущает вот что – если наш коллега ошибется в названии контрагента (сделает опечатку, напишет краткое название вместо полного и т.д. и т.п.), то вся затея по автоматизации отчета рухнет. Поэтому хотелось бы исключить всякую самодеятельность при работе с такой таблицей, а фантазию исполнителя ограничить конкретным списком допустимых наименований контрагентов

Для решения такой задачи необходимо связать ячейки, в которых нужно будет внести название, со своим списком. Настраивать такую связь будем посредством знака равно, но ставить его будем не в ячейке. Поступаем следующим образом: все настройки сделаем для одной ячейки (предлагаю начать с первой), а затем скопируем ее на все остальные ячейки таблицы, как формулу, и все настройки будут применены к нужному диапазону. Встаем на ячейку C4 и выполняем команду Данные – Работа с данными – Проверка данных [Data – Data_Tools – Data_Validation]. Далее в открывшемся диалоговом окне на первой же вкладке Параметры [Settings] в списке Тип данных [Allow] выбираем Список [List], после чего в нижней части окна будет доступно поле ввода, в которое помещаем курсор мыши, далее идем на лист, где хранится наш список (лучше в самом деле сформировать его на отдельном листе, а впоследствии, когда вся работа по настройке выпадающего списка будет окончена, скрыть – соответствующая команда есть в контекстном меню – щелчок правой кнопкой мыши на названии листа приведет к ней), и выделяем на нем диапазон ячеек со списком. Ничего лишнего захватывать не нужно

Можно жать ОК – выпадающий список готов – справа от ячейки появилась кнопка выпадающего списка, при нажатии на которую открывается список возможных значений. Если внести такое значение в ячейку вручную – это тоже работать будет. Список, кстати, можно открывать и без нажатия на стрелку – для этого в ячейке с выпадающим списком нажмите Alt + стрелка вниз

 

Работа с выпадающими списками

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

Чтобы сделать комментарий к ячейке с выпадающим списком, выбираем эту ячейку и выполняем команду Данные – Работа с данными – Проверка данных [DataData_ToolsData_Validation]. Далее переходим на вкладку Сообщение для ввода [Input_Message] и в соответствующих разделах пишем заголовок всплывающей подсказки (у меня это обычно слово ВНИМАНИЕ) и ее текст (я написала Выбери название из списка (стрелка справа от ячейки)). Помним о том, что текст комментария – диалог с исполнителем по данному документу, а не информация для печати или для руководства. После того как будут выполнены указанные действия и нажата кнопка ОК рядом с ячейкой появится наш комментарий. Если же выбрать другую ячейку – комментарий пропадет

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

В таком случае для той же ячейки еще раз вызовите окно Проверка вводимых значений [Data_Validation] и перейдите в нем на крайнюю справа вкладку Сообщение об ошибке [Error_Alert]. Здесь Вы можете в левой части окна выбрать действие вместо Останов. [Stop]Предупреждение [Warning] или Сообщение [Information] – в обоих случаях внести новое значение в ячейку получится. И я бы еще в правой части окна переписала текст ругательного сообщения – не лишне будет напомнить коллеге, чтобы он повнимательнее поискал в списке нужные данные либо проверил опечатки в том названии, которое желает внести. У меня получилось вот что Мы не работаем с таким контрагентом - поищи в списке внимательнее! Если это новый - будь готов показать договор:

При нажатии на кнопку ДА [Yes] значение будет внесено в ячейку. Напрашивается вполне закономерный вопрос – как же потом в большом количестве данных быстро найти те новые значения, которых в первоначальном списке не было? Сделать это можно и довольно легко – при выполнении команды Данные – Работа с данными – Проверка данных [Data – Data_Tools – Data_Validation] обратите внимание на стрелку в нижней части кнопки Проверка данных [Data_Validation] – за ней скрывается очень полезная команда Обвести неверные данные [Circle_Invalid_Data], которая выполнит ярко-красную обводку значений, не соответствующих списку. Команда Удалить обводку неверных данных [Clear_Validation_Circles] также предусмотрена, но в целом не требуется, так как эта обводка пропадаем сама – например, после выполнения операции по  сохранению документа

И напоследок – не во всех случаях список для выбора значений нужно формировать в ячейках листа. Допустим, списки дней недели, месяцев, подразделений компании, единиц измерения и пр. аналогичные – невелики по размерам. В таком случае вместо ссылки на ячейки со списком значений можно прописать все элементы такого списка через точку с запятой. В примере буду делать такой список для столбца Ед.изм.. Встаем на ячейку E4 и выполняем команду Данные – Работа с данными – Проверка данных [Data – Data_Tools – Data_Validation]. Далее в открывшемся диалоговом окне на вкладке Параметры [Settings] в списке Тип данных [Allow] выбираем Список [List] и в нижней части окна в поле ввода перечисляем все элементы списка через точку с запятой. Никаких других знаков типа равно и пр. ставить не нужно (я написала кг.; ящ.):

 

Ограничение на количество знаков в ячейке

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

Ее можно использовать например вот в каком случае: чтобы комментарий к позиции не становился копией служебной записки, написанной недавно на эту же тему, можно установить лимит по числу знаков в ячейке – 300 будет вполне достаточно. Оформить комментарии к таким ячейкам стоит однозначно (см. предыдущий раздел этого урока) – иначе Вашим коллегам будет трудно (или вернее сказать практически невозможно) понять, почему Excel не позволяет вписать в ячейку нужный текст. Визуальных признаков у такой ячейки в самом деле нет никаких. В моем примере я установила вот такое ограничение – не более 3 знаков для столбца Кол-во:

 

«Подводные камни» при работе с выпадающими списками

При работе с выпадающими списками нужно быть готовыми к «сюрпризам». Дело в том, что можно игнорировать выпадающий список и внести в ячейку значение, которого в списке нет, даже если Вы не разрешите этого сделать соответствующей настройкой. Для этого нужно скопировать свои данные из других ячеек и вставить поверх ячеек со списком… Можно даже использовать настройку вставки Значения [Values] – сохранится и кнопка списка и форматирование, а значения совсем не те, которые предполагались

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

ВАЖНО (!) При работе с конспектами помним, что все названия программных продуктов являются зарегистрированными торговыми марками. Никакая часть настоящего издания ни в каких целях не может быть воспроизведена в какой бы то ни было форме и какими бы то ни было средствами, будь то электронные и механические, включая фотокопирование и запись на магнитный носитель, если на это нет письменного разрешения автора

Похожие материалы

Правила и закономерности, по которым работает Excel
Общие принципы, лежащие в основе любого инструмента программы Excel
Как понять программу Excel? Это несложно, список правил и настроек приведён в этом уроке.

Текстовые функции для отбора и исправления знаков в ячейке
Текстовые функции
Как взять из ячейки первые или последние несколько знаков? А из середины текста? Как правильно использовать функции ЛЕВСИМВ, ПРАВСИМВ, ПСТР и другие?

Как разделить текст ячейки Excel по разным столбцам, используя числовую закономерность расположения знаков или символ-разделитель
Текст по столбцам
Как разделить текст по разным столбцам (колонкам), если в исходном документе разнородные фрагменты текста записаны в одной ячейке?

Наиболее используемые сочетания клавиш Excel
Горячие клавиши
Горячие клавиши ускоряют доступ ко многим командам, необходимым в ежедневной работе. Если учить хотя бы одно сочетание в 1-2 недели, результат будет заметен и ощутим!

Заказчики

Я сотрудничаю с многими известными компаниями. Вот некоторые из них:

ВТБ МегаФон Глобус Абсолют

Связаться с автором

Оставьте ваше имя и адрес электронной почты, в ближайшее время я свяжусь
с вами для консультации и отвечу на вопросы!



Оставляя заявку, вы соглашаетесь на обработку ваших персональных данных.