Как сделать раскрывающийся перечень в Excel

Как создать раскрывающийся список в Excel

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

Вариант 1: Группировка имеющегося перечня

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

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

Перейдите на вкладку «‎Данные» и изберите инструмент «‎Проверка данных».Переход в окно для создания выпадающего списка в Excel

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

В качестве источника задайте те же ячейки, выделив их левой клавишей мыши. Нажмите «‎ОК» для внедрения опций.Выбор диапазона для группы значений для создания выпадающего списка в Excel

Сейчас напротив каждой ячейки вы видите клавишу со стрелкой вниз, которая и отвечает за вызов раскрывающегося списка.Проверка создания выпадающего списка в Excel из группы значений

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

Вариант 2: Ручное добавление частей перечня

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

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

Изберите в качестве типа данных перечень, а в «‎Источник» перечислите значения, которые желаете в него добавить. Используйте «‎;», чтоб отделить одно значение от другого.Заполнение данных выпадающего списка в Excel для пустых ячеек

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

Вариант 3: Выбор массива с именованием

В Excel есть функция, позволяющая присвоить массиву имя и в предстоящем выбирать его при использовании формул. Как вы лицезрели в прошлых вариантах, объявление «‎Источника» при формировании выпадающего перечня тоже похоже на создание формулы, а это означает, что в нем можно применять массив, за ранее объявив его.

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

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

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

В качестве источника укажите сделанный массив, написав его заглавие опосля «‎=».Указание названия массива данных для создания выпадающего списка в Excel

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

Вариант 4: Создание выпадающего перечня из таблицы

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

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

Изберите пригодный вам тип дизайна.Создание таблицы для создания выпадающего списка в Excel

При возникновении окна с расположением данных просто нажмите Enter, так как спектр избран за ранее.Указание диапазона таблицы для создания выпадающего списка в Excel

Вы автоматом окажетесь на вкладке «‎Конструктор таблицы», где сможете поменять ее имя на хоть какое комфортное, начинающееся с буковкы. Изменение названия таблицы для создания выпадающего списка в Excel

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

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

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

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

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

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

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

Интересно почитать:  Как перемещать ячейки в excel

Создание раскрывающегося перечня

Путь: меню «Данные» — инструмент «Проверка данных» — вкладка «Характеристики». Тип данных – «Перечень».

Создание выпадающего списка.

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

  1. Вручную через «точку-с-запятой» в поле «Источник». Ввод значений.
  2. Ввести значения заблаговременно. А в качестве источника указать спектр ячеек со списком. Проверка вводимых значений.
  3. Назначить имя для спектра значений и в поле источник вписать это имя.

Хоть какой из вариантов даст таковой итог.

Выпадающий перечень в Excel с подстановкой данных

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

  1. Выделяем спектр для выпадающего перечня. В главном меню находим инструмент «Форматировать как таблицу». Форматировать как таблицу.
  2. Раскроются стили. Избираем хоть какой. Для решения нашей задачки дизайн не имеет значения. Наличие заголовка (шапки) принципиально. В нашем примере это ячейка А1 со словом «Деревья». Другими словами необходимо избрать стиль таблицы со строчкой заголовка. Получаем последующий вид спектра:
  3. Ставим курсор в ячейку, где будет находиться выпадающий перечень. Открываем характеристики инструмента «Проверка данных» (выше описан путь). В поле «Источник» прописываем такую функцию:

Протестируем. Вот наша таблица со списком на одном листе:

Список и таблица.

Добавим в таблицу новое значение «елка».

Добавлено значение елка.

Сейчас удалим значение «береза».

Удалено значение береза.

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

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

  1. Сформируем именованный спектр. Путь: «Формулы» — «Диспетчер имен» — «Сделать». Вводим неповторимое заглавие спектра – ОК. Создание имени.
  2. Создаем раскрывающийся перечень в хоть какой ячейке. Как это создать, уже понятно. Источник – имя спектра: =деревья.
  3. Снимаем галочки на вкладках «Сообщение для ввода», «Сообщение о ошибке». Если этого не создать, Excel не дозволит нам вводить новейшие значения. Сообщение об ошибке.
  4. Вызываем редактор Visual Basic. Для этого щелкаем правой клавишей мыши по наименованию листа и перебегаем по вкладке «Начальный текст». Или сразу жмем клавиши Alt + F11. Копируем код (лишь вставьте свои характеристики).
  5. Сохраняем, установив тип файла «с поддержкой макросов». Сообщение об ошибке.
  6. Перебегаем на лист со списком. Вкладка «Разраб» — «Код» — «Макросы». Сочетание кнопок для резвого вызова – Alt + F8. Избираем необходимое имя. Жмем «Выполнить».

Когда мы введем в пустую ячейку выпадающего перечня новое наименование, покажется сообщение: «Добавить введенное имя баобаб в выпадающий перечень?».

Нажмем «Да» и добавиться еще одна строчка со значением «баобаб».

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

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

  1. Делаем активной ячейку, куда желаем поместить раскрывающийся перечень.
  2. Открываем характеристики проверки данных. В поле «Источник» вводим формулу: =ДВССЫЛ(“[Список1.xlsx]Лист1!$A$1:$A$9”).

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

Как создать зависимые выпадающие списки

Возьмем три именованных спектра:

Три именованных диапазона.

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

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

Выбор нескольких значений из выпадающего перечня Excel

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

  1. Создаем обычный перечень с помощью инструмента «Проверка данных». Добавляем в начальный код листа готовый макрос. Как это созодать, описано выше. С его помощью справа от выпадающего перечня будут добавляться избранные значения.
  2. Чтоб избранные значения показывались снизу, вставляем иной код обработчика.
  3. Чтоб избираемые значения показывались в одной ячейке, разбитые хоть каким знаком препинания, применим таковой модуль.

Не забываем поменять спектры на «свои». Списки создаем традиционным методом. А всю остальную работу будут созодать макросы.

Выпадающий перечень с поиском

  1. На вкладке «Разраб» находим инструмент «Вставить» – «ActiveX». Тут нам нужна клавиша «Поле со списком» (ориентируемся на всплывающие подсказки). Вставить ActiveX.
  2. Щелкаем по значку – становится активным «Режим конструктора». Рисуем курсором (он становится «крестиком») маленькой прямоугольник – пространство грядущего перечня.
  3. Нажимаем «Характеристики» – раскрывается список опций. Свойства ActiveX.
  4. Вписываем спектр в строчку ListFillRange (руками). Ячейку, куда будет выводиться выбранное значение – в строчку LinkedCell. Для конфигурации шрифта и размера – Font.

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

Выпадающий перечень в Excel: как создать

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

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

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

Выпадающий перечень через контекстное меню

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

  1. Во вспомогательной таблице пишем список всех наименований – любой с новейшей строчки в отдельной ячейке. В итоге должен получиться один столбец с заполненными данными.Выпадающий список через контекстное меню
  2. Потом отмечаем все эти ячейки, жмем в любом месте отмеченного спектра правой клавишей мыши и в открывшемся перечне кликаем по функции “Присвоить имя..”.Выпадающий список через контекстное меню
  3. На дисплее покажется окно “Создание имени”. Называем перечень так, как охото, но с условием – первым эмблемой обязана быть буковка, также не допускается внедрение определенных знаков. Тут же предусмотрена возможность прибавления списку примечания в соответственном текстовом поле. По готовности жмем OK.Выпадающий список через контекстное меню
  4. Переключаемся во вкладку “Данные” в главном окне программки. Отмечаем группу ячеек, для которых желаем задать выбор из нашего перечня и жмем на значок “Проверка данных” в подразделе “Работа с данными”.Выпадающий список через контекстное меню

Создание перечня с применением инструментов разраба

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

  1. В первую очередь, эти инструменты необходимо отыскать и активировать, потому что по дефлоту они выключены. Перебегаем в меню “Файл”.Создание списка с применением инструментов разработчика
  2. В списке слева находим в самом низу пункт “Характеристики” и щелкаем по нему.Создание списка с применением инструментов разработчика
  3. Перебегаем в раздел “Настроить ленту” и в области “Главные вкладки” ставим галочку напротив пт “Разраб”. Инструменты разраба будут добавлены на ленту программки. Кликаем OK, чтоб сохранить опции.Создание списка с применением инструментов разработчика
  4. Сейчас в программке есть новенькая вкладка под заглавием “Разраб”. Через нее мы и будем работать. Поначалу создаем столбец с элементами, которые будут источниками значений для нашего выпадающего перечня.Создание списка с применением инструментов разработчика

Связанный перечень

У юзеров также есть возможность создавать и наиболее сложные взаимозависимые списки (связанные). Это означает, что перечень в одной ячейке будет зависеть от того, какое значение мы избрали в иной. К примеру, в единицах измерения продукта мы можем задать килограммы либо литры. Если вы выберем в первой ячейке напиток, во 2-ой на выбор будет предложено два варианта – литры либо миллилитры. А если в первую ячейки мы остановимся на яблоках, во 2-ой у нас будет выбор из кг либо граммов.

  1. Для этого необходимо приготовить как минимум три столбца. В первом будут заполнены наименования продуктов, а во 2-м и 3-ем – их вероятные единицы измерения. Столбцов с вероятными вариантами единиц измерения быть может и больше.Связанный список
  2. Поначалу создаем один общий перечень для всех наименований товаров, выделив все строчки столбца “Наименование”, через контекстное меню выделенного спектра.Связанный список
  3. Задаем ему имя, к примеру, “Питание”.Связанный список
  4. Потом таковым же образом формируем отдельные списки для всякого продукта с надлежащими единицами измерения. Для большей наглядности возьмем в качестве примера первую позицию – “Лук”. Отмечаем ячейки, содержащие все единицы измерения для этого продукта, через контекстное меню присваиваем имя, которое вполне обязано совпадать с наименованием.Связанный списокТаковым же образом создаем отдельные списки для всех других товаров в нашем списке.
  5. Опосля этого вставляем общий перечень с продуктами в верхнюю ячейку первого столбца главный таблицы – как и в описанной чуть повыше примере, через клавишу “Проверка данных” (вкладка “Данные”). Связанный список
  6. В качестве источника указываем “=Питание” (согласно нашему наименованию).Связанный список
  7. Потом кликаем по верхней ячейке столбца с единицами измерения, также заходим в окно проверки данных и в источнике указываем формулу “=ДВССЫЛ(A2)“, где A2 – номер ячейки с подходящим продуктом.Связанный список
  8. Списки готовы. Осталось его лишь растянуть их все строчки таблицы, как для столбца A, так и для столбца B.Связанный список

Заключение

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

Microsoft Excel: выпадающие списки

Выпадающий список в Microsoft Excel

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

Создание доп перечня

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

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

tablitsa-zagotovka-i-spisok-v-microsoft-excel

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

Присвоение имени в Microsoft Excel

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

Создание имени в Microsoft Excel

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

Проверка данных в Microsoft Excel

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

Параметры вводимых значений в Microsoft Excel

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

Выпадающий список в программе Microsoft Excel

Создание выпадающего перечня с помощью инструментов разраба

2-ой метод подразумевает создание выпадающего перечня с помощью инструментов разраба, а конкретно с внедрением ActiveX. По дефлоту, функции инструментов разраба отсутствуют, потому нам, до этого всего, необходимо будет их включить. Для этого, перебегаем во вкладку «Файл» программки Excel, а потом кликаем по надписи «Характеристики».

Переход в параметры Microsoft Excel

В открывшемся окне перебегаем в подраздел «Настройка ленты», и ставим флаг напротив значения «Разраб». Нажимаем на клавишу «OK».

Включение режима разработчика в Microsoft Excel

Опосля этого, на ленте возникает вкладка с заглавием «Разраб», куда мы и перемещаемся. Чертим в Microsoft Excel перечень, который должен стать выпадающим меню. Потом, кликаем на Ленте на значок «Вставить», и посреди показавшихся частей в группе «Элемент ActiveX» избираем «Поле со списком».

Выбор поля со списком в Microsoft Excel

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

Форма списка в Microsoft Excel

Потом мы перемещаемся в «Режим конструктора». Нажимаем на клавишу «Характеристики элемента управления».

Переход в свойства элемента управления в Microsoft Excel

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

Свойства элемента управления в Microsoft Excel

Дальше, кликаем по ячейке, и в контекстном меню поочередно перебегаем по пт «Объект ComboBox» и «Edit».

Редактирование в Microsoft Excel

Выпадающий перечень в Microsoft Excel готов.

Выпадающий список в приложении Microsoft Excel

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

Протягивание выпадающего списка в Microsoft Excel

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

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

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

Таблицы в Microsoft Excel

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

Присваивание имени в Microsoft Excel

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

Ввод данных в Microsoft Excel

Во 2-ой ячейке тоже запускаем окно проверки данных, но в графе «Источник» вводим функцию «=ДВССЫЛ» и адресок первой ячейки. К примеру, =ДВССЫЛ($B3).

Ввод данных для второй ячейки в Microsoft Excel

Как лицезреем, перечень сотворен.

Список создан в Microsoft Excel

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

Таблица создана в Microsoft Excel

Всё, таблица сотворена.

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

Мы рады, что смогли посодействовать Для вас в решении задачи.

Кроме данной нам статьи, на веб-сайте еще 12327 инструкций.
Добавьте веб-сайт Lumpics.ru в закладки (CTRL+D) и мы буквально еще пригодимся для вас.

Отблагодарите создателя, поделитесь статьей в соц сетях.

Опишите, что у вас не вышло. Наши спецы постараются ответить очень стремительно.

Ссылка на основную публикацию
Adblock
detector