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





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

Конспекты

Курсы

Практикум


Скачать файл
Теги: Бесплатный курс 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 по разным столбцам, используя числовую закономерность расположения знаков или символ-разделитель
Текст по столбцам
Как разделить текст по разным столбцам (колонкам), если в исходном документе разнородные фрагменты текста записаны в одной ячейке?

Настройки сводных таблиц
Сводные таблицы. Часть 2
Перестройка макета отчёта. Традиционная фильтрация данные в сводных таблиц. Использование специальных срезов (для новых версий).

Заказчики

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

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

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

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



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