Ниспадающее меню excel. Рекомендации как быстро сделать выпадающей список в MS Excel

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

Инструкция для Excel

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

Чтобы сделать список нужных элементов в отдельном листе, следует щелкнуть ярлычок листа, где вы хотите ввести данные. Выберите тип и затем выделите содержимое, которое появится в списке. Нажмите правой кнопкой мыши по выделенному диапазону ячеек и введите название для диапазона в поле «Имя», после чего выберите «OK». Вы можете защитить или скрыть лист, чтобы другие пользователи не могли вносить изменения в список.

Основы того, как в «Экселе» сделать раскрывающийся список

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

Если вы создали имя диапазона в поле «Источник», введите значок равенства, после чего впишите название диапазона.

Необходимые настройки для создания

Установите или снимите флажок в графе «Игнорировать пустые» в зависимости от того, может ли ячейка, содержащая раскрывающийся список, оставаться пустой.

Перейдите на вкладку "Ввод сообщения", чтобы отобразить окно сообщения, открывающееся при нажатии Убедитесь, что флажок напротив пункта меню "Показать входное сообщение при выборе ячейки" установлен, и введите заголовок. Так вы выполните все указания по тому, как в "Экселе" сделать раскрывающийся список.

Перейдите на вкладку "Ошибка оповещения" для отображения сообщения об ошибке, если будут введены неправильные данные. Убедитесь, что предупреждение «Показать ошибку» включено путем установки соответствующего флажка.

Нажмите "OK" для того, чтобы были сохранены критерии проверки и создан раскрывающийся список.

Как в «Экселе» сделать раскрывающийся список - полезные примечания

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

Чтобы удалить раскрывающейся список, выберите ячейку, содержащую его. Перейдите на вкладку «Данные» в ленте Microsoft Excel. Нажмите кнопку «Проверка данных» из группы «Работа с данными». Перейдите на вкладку «Настройки», нажмите кнопку «Очистить все», а затем - «OK».

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

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

Несколько наиболее распространенных типов выпадающих списков, которые можно создать в программе Excel:

С функцией мультивыбора;
С наполнением;
С добавлением новых элементов;
С выпадающими фото;
Другие типы.

Сделать список в Эксель с мультивыбором

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

Рассмотрим подробнее все основные и самые распространенные типы, и процесс их создание на практике.

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

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

Чтобы создать такой, следуйте инструкции:

Выделите ячейки. Если посмотреть на рисунок, то выделять нужно начиная с C2 и заканчивая C5;
Найдите вкладку «Данные», которая расположена на главной панели инструментов в окне программы. Затем нажмите на клавишу проверки данных, как показано на рисунке ниже;

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

Пр имер заполнения:

Таким образом вы создадите классический горизонтальный список с функцией мультивыбора.

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

Создать список в Экселе с наполнением

Стандартный перечень с наполнением позволит вам автоматизировать процесс ввода информации. При нажатии на ее, появится выбор ее возможных значений.

Пользователю остается только выбрать необходимое значение для заполнения.

Самый простой способ создать такой перечень – использовать «умные таблицы программы».

С их помощью можно легко и быстро форматировать необходимые вам виды списков с наполнением:

Выделите необходимые ячейки и нажмите в главной вкладке на клавишу «Форматировать как таблицу»;

Работа с VB проектом (12)
Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (63)
Разное (39)
Баги и глюки Excel (3)

Связанные выпадающие списки

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

Статья помогла? Поделись ссылкой с друзьями! Видеоуроки

{"Bottom bar":{"textstyle":"static","textpositionstatic":"bottom","textautohide":true,"textpositionmarginstatic":0,"textpositiondynamic":"bottomleft","textpositionmarginleft":24,"textpositionmarginright":24,"textpositionmargintop":24,"textpositionmarginbottom":24,"texteffect":"slide","texteffecteasing":"easeOutCubic","texteffectduration":600,"texteffectslidedirection":"left","texteffectslidedistance":30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":"slide","texteffectslidedirection1":"right","texteffectslidedistance1":120,"texteffecteasing1":"easeOutCubic","texteffectduration1":600,"texteffectdelay1":1000,"texteffect2":"slide","texteffectslidedirection2":"right","texteffectslidedistance2":120,"texteffecteasing2":"easeOutCubic","texteffectduration2":600,"texteffectdelay2":1500,"textcss":"display:block; padding:12px; text-align:left;","textbgcss":"display:block; position:absolute; top:0px; left:0px; width:100%; height:100%; background-color:#333333; opacity:0.6; filter:alpha(opacity=60);","titlecss":"display:block; position:relative; font:bold 14px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff;","descriptioncss":"display:block; position:relative; font:12px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff; margin-top:8px;","buttoncss":"display:block; position:relative; margin-top:8px;","texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"font-size:12px;","descriptioncssresponsive":"display:none !important;","buttoncssresponsive":"","addgooglefonts":false,"googlefonts":"","textleftrightpercentforstatic":40}}

Выпадающие списки – очень удобный объект, который используется практически во всех приложениях. Кроме этого, они очень популярны на веб-страницах, поскольку намного проще данные выбирать, чем вносить их вручную. Особенно подобные формы востребованы при регистрации. Но их также часто используют и в Экселе. В данной статье мы рассмотрим, как сделать выпадающий список в редакторе Excel.

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

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

Перейдите на первую пустую клетку после вашего списка.

Сделайте правый клик. Затем выберите указанный пункт.

В результате этого появится следующий список.

Для перехода по нему достаточно нажать на горячие клавиши Alt +↓ .

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

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

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

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

Стандартный

В этом случае необходимо:

Выделить нужные ячейки. Перейти на вкладку «Формулы». Нажать на кнопку «Определенные имена». Выбрать пункт «Диспетчер имён».

Затем кликнуть на «Создать».

Далее нужно будет указать желаемое имя (нельзя использовать символ тире или пробел). В графе диапазон произойдет автозаполнение, поскольку нужные ячейки были выделены в самом начале. Для сохранения нажмите на «OK».

Затем закройте это окно.

Выберите ячейку, в которой будет раскрываться будущий список. Откройте вкладку «Данные». Кликните на указанную иконку (на треугольник). Нажмите на пункт «Проверка данных».

Нажмите на «Тип данных». Необходимо задать значение «Список».

Вследствие этого появится поле «Источник». Кликните туда.

Затем выделите нужные ячейки. Ранее созданное имя автоматически подставится. Для продолжения нажимаем на «OK».

Благодаря этим действиям вы увидите вот такой элемент.

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

Как включить режим разработчика

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

Нажмите на меню «Файл».

Перейдите в раздел «Параметры».

Откройте категорию «Настроить ленту». Затем поставьте галочку напротив пункта «Разработчик». Для сохранения информации кликните на «OK».

Элементы управления

Для создания списков при помощи этого инструмента, необходимо сделать следующее:

Выделите свою таблицу данных. Перейдите на вкладку «Разработчик». Кликните на иконку «Вставить». Нажмите на указанный элемент.

Также изменится иконка указателя.

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

После этого сделайте правый клик мышкой по этому элементу. Затем выберите пункт «Формат объекта».

В окне «Форматирование объекта» необходимо:
- Указать диапазон значений для формирования списка.
- Выбрать ячейку, в которую будет выводиться результат.
- Указать количество строк будущего списка.
- Нажать на «OK» для сохранения.

Кликните на этот элемент. После этого вы увидите варианты для выбора.

Вследствие этого вы увидите какое-нибудь число. 1 – соответствует первому слову, а 2 – второму. То есть в этой ячейке выводится лишь порядковый номер выбранного слова.

ActiveX

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

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

Обратите внимание на то, что у вас включится режим конструктора. Кроме этого, изменится внешний вид указателя.

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

Кликните на указанную иконку.

Благодаря этому в правой части экрана появится окно «Properties», в котором вы сможете изменить различные настройки для выбранного элемента.

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

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

Далее необходимо кликнуть правой кнопкой мыши по созданному элементу. Выберите «Объект Combobox». Затем – «Edit».

Благодаря этим действиям вы увидите, что внешний вид объекта стал другим. Исчезнет возможность изменения размера.

Теперь вы можете спокойно выбрать что-нибудь из этого списка.

Для завершения необходимо отключить «Режим конструктора». После этого книга примет стандартный внешний вид.

Также необходимо закрыть окно свойств.

Убрать объекты ActiveX довольно просто.

Перейдите на вкладку «Разработчик».
Активируйте «Режим конструктора».

Кликните на этот объект.

Нажмите на горячую клавишу Delete .
И всё сразу же исчезнет.

Связанные списки

Для того чтобы создать двухуровневые списки, нужно выполнить несколько простых операций:

Создайте какую-нибудь похожую таблицу. Главное условие – нужно добавить для каждого пункта несколько дополнительных вариантов выбора.

Затем выделите первую строку. Не целиком, а только возможные варианты. Вызовите контекстное меню при помощи правого клика. Выберите пункт «Присвоить имя…».

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

Повторяем те же самые действия и для остальных строчек. Выберите любую клетку, в которой будет расположен будущий список товаров. Откройте вкладку «Данные» и нажмите на инструмент «Проверка данных».

В этом окне необходимо выбрать пункт «Список».

Затем кликнуть на поле «Источник» и выбрать нужный диапазон ячеек.

Для сохранения используйте кнопку «OK».

Выберите вторую ячейку, в которой будет создан динамический список. Перейдите на вкладку «Данные» и повторите те же самые действия.

В графе «Тип данных» снова указываем «Список». В поле источник укажите следующую формулу.

=ДВССЫЛ(B11)

В качестве аргумента указываем ссылку на ту ячейку, в которой у нас подгружается ассортимент товаров. Более подробно о ней можно прочитать на сайте Microsoft.

Обязательно сохраните все внесенные изменения.

После нажатия на «OK» вы увидите ошибку источника данных. Ничего страшного тут нет. Кликните на «Да».

Дело в том, что в данный момент в клетке «Товар» у нас ничего не выбрано. Как только там будет какое-нибудь слово, нужный список автоматически загрузится.

Выберите что-нибудь из предлагаемых товаров.

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

Попробуйте указать что-то другое из вашего ассортимента. И вы увидите, что список сразу же изменится. Это очень удобно, поскольку ситуации, когда второе «меню» зависит от первого, бывают очень часто.

Связанные с поиском списки

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

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

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

Выберите любую клетку. Перейдите на вкладку «Данные» и нажмите на инструмент «Проверка данных».

Выбираем нужный тип данных. В источнике указываем необходимый диапазон. Сохраняем при помощи кнопки «OK».

Кликните на другую ячейку и повторите описанные ранее действия по вызову такого же окна.

Указываем точно такой же тип данных, но в источнике на этот раз указываем следующую формулу.

=СМЕЩ($B$1;ПОИСКПОЗ($F$6;$B:$B;0)-1;1;СЧЁТЕСЛИ($B:$B;$F$6);1)

После сохранения появится ошибка о том, что источник пустой. Нажимаем на кнопку «Да».

Выберите что-нибудь из предлагаемых вариантов.

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

Попробуйте выбрать другой город – список изменится автоматически.

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

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

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

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

В контекстном меню выберите пункт «Просмотреть код».

В появившемся окне вбейте следующий код.

Private Sub Worksheet_Change(ByVal Target As Range)

On Error Resume Next

If Not Intersect(Target, Range(«B11»)) Is Nothing And Target.Cells.Count = 1 Then

Application.EnableEvents = False

NewSelectWord = Target

Application.Undo

BeforeWord = Target

If Len(BeforeWord) <> 0 And BeforeWord <> NewSelectWord Then

Target = Target & «,» & NewSelectWord

Target = NewSelectWord

If Len(NewSelectWord) = 0 Then Target.ClearContents

Application.EnableEvents = True

Затем вернитесь к книге и выберите что-нибудь.

Повторите это действие еще раз.

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

Для очистки этой клетки нужно использовать клавишу Delete .

Во всех остальных случаях значения будут только накапливаться.

Заключение

В данной статье мы рассмотрели всевозможные способы для создания выпадающих списков в редакторе Excel. Если у вас что-то не получается, возможно, вы используете неподходящую таблицу. Еще раз внимательно посмотрите на используемые формулы, поскольку там могут быть опечатки. Также очень важно проверить правильность ссылок на ячейки. Если у вас отсутствует вкладка «Разработчик», значит, вы её неправильно активировали или же упустили этот момент из виду.

Видеоинструкция

Некоторым пользователям намного проще воспринимать информацию при помощи видео. Именно для них мы подготовили специальный ролик, в котором описываются все ранее указанные методы.

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

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

Второй способ создания выпадающего списка в Excel более изящный и универсальный. Выделяете диапазон данных для выпадающего списка, затем нажимаете на пункт меню Формула — Диспетчер имен — Создать . Заполняете поле Имя , и копируете его (оно Вам понадобится позже). Имя должно начинаться с буквы или символа подчеркивания, и не должно содержать пробелов. Нажимаете ОК . Закрываете окно.

Затем выбираете ячейку, в которой будет выпадающий список Excel (можно сразу выделить несколько ячеек, если в них будут одинаковые выпадающие списки). После этого выбираете пункт меню Данные — Проверка данных , затем в окошке Тип данных выберите строку Список , в поле Источник поставьте знак равно, и без пробела вставьте то, что Вы копировали (значение поля Имя ). Не забудьте про знак = , иначе ничего не получится. Выглядит надпись в поле Источник примерно так: =Имя_диапазона . Нажимаете ОК .

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

Это может быть опрос, тест, или анкета с заранее прописанными вариантами ответов. Можно, например, раздать файл Excel с анкетой группе людей, которых Вы хотите опросить, и получить назад уже файлы с ответами, или распечатанные ответы.

Для надежности можно лист с данными для выпадающих списков скрыть или защитить. Чтобы скрыть лист Excel , нажимаете на его название правой клавишей мыши, и выбираете Скрыть . Чтобы отобразить скрытые листы, нажимаете на название любого открытого листа Excel правой клавишей мыши, и выбираете Отобразить .

Чтобы защитить лист Excel , выбираете пункт меню Редактировать — Защитить лист , и прописываете пароль и действия, которые разрешены для пользователей.

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