Выпадающий список в Excel с помощью инструментов или макросов

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

Как сделать списки в Excel 2007

Для примера я создал список городов Московской области. Выделяем список и создаем именованный диапазон. Для этого после щелчка правой кнопки мыши выбираем в контекстном меню «Имя диапазона». Задаем имя «Город_М_О» и жмем «ОК». Теперь переходим в ту ячейку, где мы хотим иметь выпадающий список и переходим на закладку «Данные» верхней панели. Здесь нам понадобится кнопка «Проверка данных», расположенная в группе «Работа с данными». Выбираем пункт «Проверка данных». В появившемся окне выбираем тип данных «Список» и в поле «Источник» вводим «=Город_М_О», то есть заданное нами имя диапазона, который содержит список. Вот собственно и все. В заданной нами ячейке появился выпадающий список, из которого мы можем выбирать любое значение. При необходимости мы можем использовать тот же список и в других ячейках. А исходный список мы можем и спрятать, к примеру, на другом листе, чтобы не портил вида таблицы. Система все равно будет работать, потому что список у нас находится в именованном диапазоне, видимом в пределах всей книги Excel.

Как сделать списки в Excel 2007
Как сделать списки в Excel 2007
Как сделать списки в Excel 2007
Как сделать списки в Excel 2007
Как сделать списки в Excel 2007

Простой способ

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

Простой способ
  1. Перейдите на первую пустую клетку после вашего списка.
  1. Сделайте правый клик. Затем выберите указанный пункт.
Простой способ
  1. В результате этого появится следующий список.
  1. Для перехода по нему достаточно нажать на горячие клавиши Alt+.
Простой способ

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

  1. Затем для выбора можно использовать только стрелочки ( и ). Для того чтобы вставить нужный продукт (в нашем случае), достаточно нажать на клавишу Enter.
Простой способ

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

Обратите внимание на то, что этот метод не работает, если вы выберите клетку, выше которой нет никакой информации.

Читайте также:  Подробное руководство по установке Joomla

Способ (умные таблицы + функция ДВССЫЛ)

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

Способ (умные таблицы + функция ДВССЫЛ)

Для начала необходимо превратить обычные таблицы в умные. Выделите любое значение в столбце А. На вкладке Главная в группе Стили выбираем Форматировать как таблицу. Стиль таблицы выбираем любой, для нас это не принципиально.

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

Способ (умные таблицы + функция ДВССЫЛ)

Повторим эту операцию для всех столбцов.

Далее присвоим имя для каждой таблицы. Особенность умных таблиц в том, что для присвоения имени достаточно выделить любой из элементов таблицы, и выбрать во вкладке Конструктор в группе Свойства, Имя таблицы. Укажем название страны, в нашем примере для первого столбца будет страна Италия.

Способ (умные таблицы + функция ДВССЫЛ)

Повторим эту операцию для всех столбцов.

Подготовительная  работа завершена.

Способ (умные таблицы + функция ДВССЫЛ)

Создадим теперь первый выпадающий список. Мы предварительно выписали названия всех стран, на них и будем ссылаться.

Выделяем ячейку I2, которая соответствует выпадающему списку для выбора страны. Далее во вкладке Данные в группе Работа с данными выберем Проверка данных. Во всплывающем окне в Параметрах в поле Тип данных выберем Список, в Источнике укажем подготовленный диапазон со списком стран. Нажимаем ОК.

Способ (умные таблицы + функция ДВССЫЛ)

Первый выпадающий список готов. Сразу создадим второй для выбора города. Во всплывающем окне в поле Источник укажем только формулу, как и в Способе 1, с использованием функции ДВССЫЛ. Формула имеет вид «=ДВССЫЛ($I$2)», и нажимаем ОК.

Готово!

Способ (умные таблицы + функция ДВССЫЛ)

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

Так же, для Вашего удобства есть видеоурок.

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

Дополнение Сообщение об ошибках

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

Дополнение Сообщение об ошибках

Можно настроить это сообщение при создании или редактировании выпадающего списка во вкладке Сообщение об ошибке.

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

Дополнение Сообщение об ошибках

Устранение неполадок с Google Таблицами

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

Читайте также:  Исправляем проблемы с отображением русских букв в Windows 10

Только чтение

Google Таблицы не позволят другому человеку взаимодействовать с документом, если он находится в статусе «Только для чтения». Это означает, что они не могут вносить какие-либо изменения. Чтобы другие могли использовать ваши раскрывающиеся списки, следуйте этим инструкциям:

  1. Нажмите «Поделиться» в правом верхнем углу.
  2. Назовите свой лист, если будет предложено, и нажмите «Сохранить» в правом нижнем углу всплывающего окна.
  3. Введите контактную информацию тех, с кем вы делитесь документом.
  4. Щелкните раскрывающийся список справа.
  5. В меню выберите «Редактор».
  6. Нажмите «Отправить».

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

Дополнительную помощь по Google Таблицам можно найти на Справочный сайт редакторов документов здесь.

Второй способ создания списка

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

Чтобы это сделать, необходимо в программе Excel отыскать вкладку «Разработчик». Сразу ее можно не найти, так как программы установлены по умолчанию, поэтому сначала зайдите в «Офис» и найдите правку «Параметры», выберите пункт «Основные» и нажмите на надпись «Показывать вкладку».

После того как на ленте появится пункт «Разработчик», отыщите «Вставить» и нажмите на «Поле со списком».

Сделайте обводку этой надписи контуром с помощью мыши. Применив правую кнопку нажмите на «Формат объекта» в образовавшемся окне. Там же в первом поле выбирается и диапазон ячейки, а во втором – указывается ячейка, где будет указываться номер элемента.

Второй способ создания списка

Заполнение второго поля пригодится во время обработки данных. Здесь же можно создать набор элементов, которые в дальнейшем будут здесь отражены. Применяя первый метод в экселе, вам предоставят лишь 8 элементов.

Читайте также:  Умная система UPnP: как включить на роутере и настроить на компьютере

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

Таким образом, стоит немного подразобраться, и вы с легкостью сможете создать выпадающий список в Excel, раскрывающийся в том виде, в каком вы его создадите.

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

Делаем выпадающий список в ячейке эксель с выбором по первой букве

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

Делаем выпадающий список в ячейке эксель с выбором по первой букве

В режиме конструктора вставим в наш документ элемент управления Active X «Поле со списком«:

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

Делаем выпадающий список в ячейке эксель с выбором по первой букве
  • ListFillRange — сюда вставляем наш именованный диапазон из «диспетчера имен»;
  • LinkedCell — связанная ячейка, куда будет выводиться выбранный из списка элемент — нужно, если данные ее будут использоваться в других таблицах, формулах и т.д;
  • ListRows — количество отображаемых строк в списке;
  • Font — выбираем шрифт, размер, стиль;
  • ForeColor и BackColor — цвет текста и фона;
  • ListRow — количество возвращаемых строк в списке;
  • PrintObject — определяет — выводить элемент управления на печать или нет (истина или ложь)

Пока активен режим конструктора — вы не сможете работать со списком как пользователь. Но вы можете изменять его размеры , свойства , положение на листе. После окончания настроек нужно выключить режим конструктора.

Делаем выпадающий список в ячейке эксель с выбором по первой букве

Попробуйте найти имя по букве, посмотрите что получается. Можно скопировать и разместить на нужных местах созданный нами элемент управления. Такой способ создания списков хорош для серьезных проектов в Excek, может не всем подойти, но у него есть свои плюсы, которых нет в других способах.