Раскрывающийся список в ячейке

Видео

 У кого мало времени и нужно быстро вникнуть в суть – посмотрите обучающее видео:

Кому интересны подробности и нюансы всех описанных способов – далее по тексту.

Способ 1. Примитивный

Одиночный щелчок правой кнопкой мыши по пустой ячейке под столбцом с данными, команда контекстного меню Выберите из раскрывающегося списка (Выберите из раскрывающегося списка) или нажмите сочетание клавиш ALT+стрелка вниз. Способ не работает, если ячейку и столбец данных разделяет хотя бы одна пустая строка, или если вам нужен товар, который никогда не вводился выше:

Способ 2. Стандартный

1. Выделите ячейки с данными, которые должны быть включены в раскрывающийся список (например, названия продуктов).
2. Если у вас Excel 2003 или более ранняя версия, выберите в меню Вставить – Имя – Назначить (Вставить — Имя — Определить), если Excel 2007 или новее, откройте вкладку Формулы и используйте кнопку Менеджер именЗатем Создавай. Введите имя (можно любое имя, но без пробелов и начинающееся с буквы!) выделенного диапазона (например Продукт). Нажмите на OK.
3. Выделите ячейки (можно сразу несколько), в которых хотите получить выпадающий список и выберите из меню (на вкладке) Данные – Проверка (Данные – Проверка). Из раскрывающегося списка Тип данных (Разрешить) выберите опцию Список и введите в строку Источник знак равенства и имя диапазона (т.е. =Продукты).

Нажмите OK.

Все! Наслаждаться!

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

Способ 3: Контроль

Этот метод заключается в вставке на лист нового объекта — элемента управления полем со списком, а затем привязке его к диапазонам на листе. Для этого:

1. В Excel 2007/2010 откройте вкладку Застройщик. В более ранних версиях панель инструментов Формы через меню Вид – Панели инструментов – Формы (Вид – Панели инструментов – Формы). Если эта вкладка не видна, то нажмите кнопку Офис – Параметры Excel - флажок Показать вкладку «Разработчик» на ленте (Кнопка Office — Параметры Excel — Показать вкладку «Разработчик» на ленте)
2. Найдите значок раскрывающегося списка среди элементов управления формы (не ActiveX!). Следуйте всплывающим подсказкам поле со списком:

   Нажмите на иконку и нарисуйте небольшой горизонтальный прямоугольник – будущий список.

3. Щелкните правой кнопкой мыши по нарисованному списку и выберите команду Формат объекта (управление форматом). В появившемся диалоговом окне установите

• Формировать список по диапазону — выделите ячейки с наименованиями товаров, которые необходимо включить в список
• Сотовая связь – укажите ячейку, в которой хотите отобразить порядковый номер выбранного пользователем элемента.
• Количество строк списка — сколько строк отображать в выпадающем списке. По умолчанию — 8, но возможно и больше, чего не позволяет предыдущий метод.

После нажатия на OK список можно использовать.

Для отображения его имени вместо серийного номера элемента можно дополнительно использовать функцию ИНДЕКС (ИНДЕКС), который может отображать содержимое необходимой ячейки из диапазона:

Способ 4: элемент управления ActiveX

Этот метод частично напоминает предыдущий. Основное отличие состоит в том, что на лист добавляется не элемент управления, а элемент управления ActiveX. "Поле со списком" из раскрывающегося списка под кнопкой Вставить на вкладке Застройщик:

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

Во-первых, созданный выпадающий список ActiveX может находиться в двух принципиально разных состояниях – режиме отладки, когда вы можете настраивать его параметры и свойства, перемещать его по листу и изменять его размеры, и – режиме ввода, когда единственное, что вы можете сделать: выбрать данные из него. Переключение между этими режимами осуществляется с помощью кнопки. Режим проектирования таб Застройщик:

Если эта кнопка нажата, то мы можем настроить параметры выпадающего списка, нажав соседнюю кнопку Объекты, что откроет окно со списком всех возможных настроек выбранного объекта:

Самые нужные и полезные свойства, которые можно и нужно настроить:

• СписокЗаполнитьДиапазон – диапазон ячеек, из которых берутся данные для списка. Он не позволит выделить диапазон мышкой, его нужно просто ввести руками с клавиатуры (например, Лист2!А1:А5)
• Связанная ячейка – связанная ячейка, в которой будет отображаться выбранный из списка элемент
• ЛистРовс – количество отображаемых строк
• шрифт — шрифт, размер, стиль (курсив, подчеркивание и т.д., кроме цвета)
• передний цвет и цвет фона — цвет текста и фона соответственно

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

При использовании этого метода также можно указать как СписокЗаполнитьДиапазон не только одномерные диапазоны. Можно, например, задать диапазон из двух столбцов и нескольких строк, указав дополнительно, что нужно отображать два столбца (свойство количество столбцов=2). Тогда вы сможете получить весьма привлекательные результаты, которые окупят все усилия, затраченные на дополнительные настройки:

Итоговая сравнительная таблица всех методов

:

• Выпадающий список с данными из другого файла
• Создание зависимых раскрывающихся списков
• Автоматическое создание выпадающих списков дополнением PLEX
• Выбор фотографии из выпадающего списка
• Автоматическое удаление уже использованных элементов из выпадающего списка
• Выпадающий список с автоматическим добавлением новых позиций