Раскрывающийся список в Excel
Опубликовано 18 Мар 2014
Рубрика: Справочник Excel | 12 комментариев
Чтобы не набирать по буквам и цифрам уже набранные ранее текстовые и числовые значения ячеек, для ускорения процесса заполнения информацией ячеек листа MS Excel и для минимизации ошибок, в том числе опечаток и орфографических, удобно применять раскрывающийся список.
Из раскрывающегося списка несколькими кликами мыши можно ввести в назначенные ячейки нужную информацию. Широко применяются раскрывающиеся списки при написании расчетных программ в Excel.
Программа MS Excel, имея очень дружественный интерфейс, предлагает пользователю несколько разных вариантов помощи при вводе в ячейки рабочего листа повторяющейся информации.
Предположим, что мы ведем базу данных поступления металлопроката на склад. В первом столбце мы указываем вид прокатного профиля.
Вариант №0 — «Элементарный».
Делая очередную запись в ячейку А9, при наборе первой буквы наименования профиля, например «Ш», Excel предлагает заполнить ячейку словом «Швеллер». После набора «Ш» достаточно нажать кнопку «Ввод» на клавиатуре – и слово будет введено в ячейку.
«Минусом» данного варианта является необходимость ввода иногда нескольких букв и невозможность заранее создать справочник наименований, ограничивающий свободу деятельности пользователя.
Переходим непосредственно к вариантам создания раскрывающихся списков.
Вариант №1 — «Простейший».
Если активировать мышью ячейку А9, нажать сочетание клавиш «Alt» «↓», то появится раскрывающийся список, содержащий все ранее введенные в этом столбце значения. Остается лишь выбрать мышью нужную запись. Вместо набора вышеуказанного сочетания клавиш можно щелчком правой кнопки мыши вызвать контекстное меню и выбрать в нем пункт «Выбрать из раскрывающегося списка…». В результате увидим тот же выпадающий список.
В данном варианте активная ячейка обязательно должна примыкать снизу к диапазону значений, а сам диапазон не должен содержать пустых ячеек!
Вариант №2 — «Простой».
Этот вариант позволяет заранее создать список (справочник) значений из которых пользователь в дальнейшем сможет выбирать необходимые записи. При этом список может быть размещен где угодно на листе (или даже на другом листе) и может быть при необходимости скрыт от пользователя.
Для того чтобы создать раскрывающийся список в этом варианте необходимо выполнить ряд последовательных шагов.
1. Создаем список возможных значений, записав их в столбец по одному в ячейку. Допустим это перечень в ячейках А2…А8.
2. Активируем ячейку, в которой необходимо поместить раскрывающийся список путем установки в нее курсора. Пусть это будет та же ячейка А9.
3. Выбираем в главном меню кнопку «Данные» – «Проверка…».
4. В выпавшем окне «Проверка вводимых значений» выбираем вкладку «Параметры».
5. В поле «Тип данных:» из раскрывающегося списка (подобного тому, который мы создаем) выбираем значение «Список».
6. В появившемся поле «Источник:» указываем диапазон, содержащий список возможных значений.
7. Устанавливаем (если он не установлен по умолчанию) флажок «Список допустимых значений» и нажимаем кнопку «ОК».
Раскрывающийся список готов. Его можно скопировать как формулы в любое количество ячеек!
Вариант №3 — «Сложный».
Этот вариант создания раскрывающегося списка, не смотря на свое название «Сложный», по сути таковым не является. Для создания выпадающего списка в нем используется элемент «Поле со списком» панели инструментов «Формы».
Создадим раскрывающийся список этим способом.
1. Создаем список-справочник в ячейках А2…А8.
2. Выбираем в главном меню кнопку «Вид» – «Панели инструментов» – «Формы».
3. В появившейся панели «Формы» выбираем «Поле со списком» и рисуем его, например, в ячейке А9.
Элемент «Поле со списком» размещается не в самой ячейке, а, как бы, над ней!!! Элемент может быть большим и находиться над несколькими ячейками.
4. Щелкаем правой кнопкой мыши по нарисованному элементу и в появившемся контекстном меню выбираем «Формат объекта».
5. В выпавшем окне «Форматирование объекта» на вкладке «Элемент управления» заполняем поля в соответствии с рисунком, расположенном ниже и нажимаем «ОК».
6. Раскрывающийся список готов. Он выводит порядковый номер элемента списка в связанную ячейку В9. (Можете назначить любую удобную вам ячейку, не обязательно В9!)
Для вывода в какую-либо ячейку самого значения из списка-справочника применим функцию ИНДЕКС. Допустим, нам необходимо вывести значение в ячейку А9, расположенную под элементом «Поле со списком».
Для этого в ячейку А9 запишем формулу: =ИНДЕКС(A2:A8;В9)
Наглядный пример подробно рассмотрен в статье «Поле со списком и функция ИНДЕКС в Excel». Можно перейти по ссылке и ознакомиться.
Раскрывающийся список, созданный этим способом плюс применение функций ИНДЕКС и/или ВПР предоставляют безграничные возможности пользователю при извлечении данных из различных базовых справочных таблиц.
Вариант №4 — «Самый сложный».
Для создания выпадающего списка в этом случае используется также элемент «Поле со списком», но панели инструментов «Элементы управления» (в MS Excel 2003). Это так называемые элементы ActiveX. Здесь все очень похоже внешне на вариант №3, но значительно шире возможности настройки и форматирования элемента.
1. Выбираем в главном меню кнопку «Вид» – «Панели инструментов» – «Элементы управления».
2. В появившейся панели «Элементы управления» выбираем «Поле со списком» и рисуем его в ячейке А9. Элемент ActiveX «Поле со списком» размещается не в самой ячейке, а сверху, накрывая ее!!!
3. Нажимаем кнопку «Свойства» на панели «Элементы управления» и в выпавшем окне «Properties» («Свойства») вручную вписываем диапазон исходных данных, адрес связанной ячейки (ячейки, куда будет введено выбранное значение) и количество отображаемых строк.
4. Далее при желании можно изменить шрифт, его цвет, цвет фона, и еще ряд параметров… Ничего сложного нет в использовании «Самого сложного» варианта – убедитесь сами. Все интуитивно понятно, хотя базовые знания английского языка не помешают!
5. Отжимаем кнопку «Выход из режима конструктора» на панели «Элементы управления» и проверяем работу раскрывающегося списка. Все работает! Выбранное значение записано в ячейку А9, в нашем примере — под элементом «Поле со списком». Вообще связанной ячейкой может быть абсолютно любая кроме ячеек, где располагается базовый список.
Итоги.
Вариант №0 автоматизирует в некоторой степени заполнение ячеек, но к раскрывающимся спискам, конечно, отношения не имеет и приведен здесь под соответствующим номером, как элементарный вариант автоматизации ввода повторяющихся данных.
На практике я чаще всего создаю раскрывающиеся списки в Excel, используя варианты №1 и №3, реже — вариант №2 и совсем редко — вариант №4, хотя именно он является, безусловно, самым гибким, предоставляющим самые широкие возможности.
Но часто наш выбор в жизни определяют вкусы, стереотипы и привычки! В зависимости от задачи, которую необходимо решить при работе в Excel, следует выбирать наиболее приемлемый и удобный для каждого конкретного случая вариант создания раскрывающихся списков.
Статьи с близкой тематикой
Отзывы
12 комментариев на «Раскрывающийся список в Excel»
Ваш отзыв
Здравствуйте Александр. В Excel я пока учусь. Вопрос: возможен ли в принципе сложный выпадающий список с возможностью внутреннего перехода из одного списка в другой? Лет 25 тому назад в попавшейся под руку электронной таблице SuperCalc мне удалось сделать подобный с несколькими переходами из одного выпадающего списка в другой. Потом ушел на пенсию, и компьютер приобрёл не так давно.
Добрый день, Михаил.
Никогда не делал, но думаю возможно с помощью программирования на VBA.
Какую задачу вы решаете? Я предпочитаю «сложное» разбивать на «простое» и не использовать макросы до последнего... Даже без VBA возможности Excel огромны!!!
Интересна реализация функции «Если--то». Полезна при расчете однотрубной горизонтальной системы отопления.
Сам часто использую Exel в расчетах, возможности действительно, очень большие. Про раскрывающийся список очень полезная информация, как раз искал инфу про эту функцию. Автору респект. (teplosniks.ru)
А вот есть ли возможность, что бы при выборе какого то значения из выпадающего списка в следующей по порядку ячейке при выборе из такого же списка этого значения уже не было?
Можно формулами попробовать, а можно на VBA.
Добрый день!
Задача: нужно чтобы при выборе одного значения из выпадающего списка, в соседней ячейке появлялись связанные значения.
Пример: на «распоряжение» у нас бланк, из выпадающего списка выбираем компанию ооо стелс, (на листе 2 у нас в столбце а название фирмы в столбце В адрес в столбце С инн в столбце D адрес.) И как сделать так, чтобы при выборе значения из выпадающего списка, появлялись соответствующие значения в других ячейках... ?
Артур, добрый день.
Сделать можно так, как описано здесь.
Здравствуйте Александр! Меня интересует как можно сделать так, чтобы в выпадающем списке конкретные значения обретали цвет. А именно или цвет шрифта или строки в списке. Уточню. Есть список при открытии которого значения списка все одинакового размера и цвета, а я хотел бы чтобы именно допустим модель или же бренд был определенного размера, цвета и даже если можно стиля. Спасибо.
Эмиль, здравствуйте. В Excel 2003 (который я использую) такой возможности нет. Реализовать ее можно попробовать только с помощью VBA. Посмотрите более новые версии Excel, может в них реализована такая возможность. Посмотрите также Надстройку PLEX и вообще весь сайт Николая Павлова planetaexcel.ru
Добрый день, Александр,
Возможно ли сделать, используя Вариант 2, при выборе определенной записи из списка, чтобы вся строка таблицы выделялась другим фоном автоматически.
Конкретный пример: мне необходимо, если выбран вариант «выполнено», вся строчка выделялась другим цветом.
Спасибо!
Игорь, здравствуйте.
Для решения вашей задачи используйте условное форматирование ячеек.