Как сделать сложный выпадающий список в excel? Создание раскрывающегося списка Что такое список в эксель.
Чтобы упростить работу пользователей с листом, добавьте в ячейки раскрывающиеся списки. Раскрывающиеся списки позволяют пользователям выбирать элементы из созданного вами списка.
Видео
-
Скачивание примеров
Предлагаем скачать образец книги с несколькими примерами проверки данных, аналогичными примеру в этой статье. Вы можете воспользоваться ими или создать собственные сценарии проверки данных. Скачать примеры проверки данных Excel .
Ввод данных станет быстрее и точнее, если ограничить значения в ячейке вариантами из раскрывающегося списка.
Сначала создайте на листе список допустимых элементов, а затем выполните сортировку или расположите их в нужном порядке. В дальнейшем эти элементы могут служить источником для раскрывающегося списка данных. Если список небольшой, на него можно легко ссылаться и вводить элементы прямо в средстве проверки данных.
См. также
Почему данные следует поместить в таблицу? Потому что в этом случае при добавлении и удалении элементов все раскрывающиеся списки, созданные на основе этой таблицы, будут обновляться автоматически. Дополнительные действия не требуются.
На новом листе введите данные, которые должны отображаться в раскрывающемся списке. Желательно, чтобы элементы списка содержались в таблице Excel .
Примечания:
таблице Excel . Если это не так, список можно быстро преобразовать в таблицу, выделив любую ячейку диапазона и нажав клавиши CTRL+T .
Примечания:
Под выпадающим списком понимается содержание в одной ячейке нескольких значений. Когда пользователь щелкает по стрелочке справа, появляется определенный перечень. Можно выбрать конкретное.
Очень удобный инструмент Excel для проверки введенных данных. Повысить комфорт работы с данными позволяют возможности выпадающих списков: подстановка данных, отображение данных другого листа или файла, наличие функции поиска и зависимости.
Создание раскрывающегося списка
Путь: меню «Данные» - инструмент «Проверка данных» - вкладка «Параметры». Тип данных – «Список».
Ввести значения, из которых будет складываться выпадающий список, можно разными способами:
Любой из вариантов даст такой результат.
Выпадающий список в Excel с подстановкой данных
Необходимо сделать раскрывающийся список со значениями из динамического диапазона. Если вносятся изменения в имеющийся диапазон (добавляются или удаляются данные), они автоматически отражаются в раскрывающемся списке.
Протестируем. Вот наша таблица со списком на одном листе:
Добавим в таблицу новое значение «елка».
Теперь удалим значение «береза».
Осуществить задуманное нам помогла «умная таблица», которая легка «расширяется», меняется.
Теперь сделаем так, чтобы можно было вводить новые значения прямо в ячейку с этим списком. И данные автоматически добавлялись в диапазон.
Когда мы введем в пустую ячейку выпадающего списка новое наименование, появится сообщение: «Добавить введенное имя баобаб в выпадающий список?».
Нажмем «Да» и добавиться еще одна строка со значением «баобаб».
Выпадающий список в Excel с данными с другого листа/файла
Когда значения для выпадающего списка расположены на другом листе или в другой книге, стандартный способ не работает. Решить задачу можно с помощью функции ДВССЫЛ: она сформирует правильную ссылку на внешний источник информации.
- Делаем активной ячейку, куда хотим поместить раскрывающийся список.
- Открываем параметры проверки данных. В поле «Источник» вводим формулу: =ДВССЫЛ(“[Список1.xlsx]Лист1!$A$1:$A$9”).
Имя файла, из которого берется информация для списка, заключено в квадратные скобки. Этот файл должен быть открыт. Если книга с нужными значениями находится в другой папке, нужно указывать путь полностью.
Как сделать зависимые выпадающие списки
Возьмем три именованных диапазона:
Это обязательное условие. Выше описано, как сделать обычный список именованным диапазоном (с помощью «Диспетчера имен»). Помним, что имя не может содержать пробелов и знаков препинания.
- Создадим первый выпадающий список, куда войдут названия диапазонов.
- Когда поставили курсор в поле «Источник», переходим на лист и выделяем попеременно нужные ячейки.
- Теперь создадим второй раскрывающийся список. В нем должны отражаться те слова, которые соответствуют выбранному в первом списке названию. Если «Деревья», то «граб», «дуб» и т.д. Вводим в поле «Источник» функцию вида =ДВССЫЛ(E3). E3 – ячейка с именем первого диапазона.
- Создаем стандартный список с помощью инструмента «Проверка данных». Добавляем в исходный код листа готовый макрос. Как это делать, описано выше. С его помощью справа от выпадающего списка будут добавляться выбранные значения. Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Not Intersect(Target, Range("Е2:Е9" )) Is Nothing And Target.Cells.Count = 1 Then Application.EnableEvents = False If Len(Target.Offset(0, 1)) = 0 Then Target.Offset(0, 1) = Target Else Target.End (xlToRight).Offset(0, 1) = Target End If Target.ClearContents Application.EnableEvents = True End If End Sub
- Чтобы выбранные значения показывались снизу, вставляем другой код обработчика. Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Not Intersect(Target, Range("Н2:К2" )) Is Nothing And Target.Cells.Count = 1 Then Application.EnableEvents = False If Len(Target.Offset(1, 0)) = 0 Then Target.Offset(1, 0) = Target Else Target.End (xlDown).Offset(1, 0) = Target End If Target.ClearContents Application.EnableEvents = True End If End Sub
- Чтобы выбираемые значения отображались в одной ячейке, разделенные любым знаком препинания, применим такой модуль.
Выбор нескольких значений из выпадающего списка Excel
Бывает, когда из раскрывающегося списка необходимо выбрать сразу несколько элементов. Рассмотрим пути реализации задачи.
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Range("C2:C5" )) Is Nothing And Target.Cells.Count = 1 Then
Application.EnableEvents = False
newVal = Target
Application.Undo
oldval = Target
If Len(oldval) <> 0 And oldval <> newVal Then
Target = Target & "," & newVal
Else
Target = newVal
End If
If Len(newVal) = 0 Then Target.ClearContents
Application.EnableEvents = True
End If
End Sub
Не забываем менять диапазоны на «свои». Списки создаем классическим способом. А всю остальную работу будут делать макросы.
Выпадающий список с поиском
При вводе первых букв с клавиатуры высвечиваются подходящие элементы. И это далеко не все приятные моменты данного инструмента. Здесь можно настраивать визуальное представление информации, указывать в качестве источника сразу два столбца.
Выберите для задания выпадающего списка. В меню откройте пункты «Данные» - «Проверка». После чего перейдите в новом окне во вкладку «Параметры» и в открывшемся поле «Тип данных» установите строку «Список». При этом в том же окне появится поле «Источник». Введите в него символ «=» и имя выделенного диапазона, которое было задано ячейкам с данными. Для применения параметров нажмите «Enter» или «Ок». Это вариант самого простого выпадающего списка.
При этом в том же окне появится поле «Источник». Введите в него символ «=» и имя выделенного диапазона, которое было задано ячейкам с данными. Для применения установленных параметров нажмите «Enter» или «Ок». Это вариант самого простого выпадающего списка.
В Excel существует возможности для задания выпадающего списка, обладающего более сложной конструкцией. Для этого применяют элемент управления под названием «поле со списком», вставляемый на рабочий лист Excel. Чтобы его установить, откройте в меню пункты «Вид», затем «Панели инструментов» и подпункт «Формы».
Выберите на открывшейся панели элементов управления значок «поле со списком» - это и есть выпадающий список. Нарисуйте мышкой прямоугольник в форме поля. Выделите правой кнопкой мыши нарисованный список и укажите команду «Формат объекта…».
В появившемся диалоговом окне в поле «Формировать список по диапазону» задайте нужный диапазон ячеек. Для этого мышкой выделите ячейки, которые должны попасть в данный выпадающий список Excel. В поле «Связь с ячейкой» установите номер ячейки для вывода порядкового номера элемента, выбранного в списке. Задайте требуемое количество строк создаваемого списка. Кнопка «Ок» применит все установленные параметры, и список готов к использованию.
Выпадающий список в Excel – это довольно удобная функция, которая поможет создать более сложные документы и сделать работу удобной для визуального восприятия пользователя.
Несколько наиболее распространенных типов выпадающих списков, которые можно создать в программе Excel:
- С функцией мультивыбора;
- С наполнением;
- С добавлением новых элементов;
- С выпадающими фото;
- Другие типы.
Сделать список в Эксель с мультивыбором
Создать перечень в ячейке программы можно с помощью встроенных инструментов панели управления.
Рассмотрим подробнее все основные и самые распространенные типы, и процесс их создание на практике.
Совет! Классический всплывающий список программы имеет функцию мультивыбора, то есть пользователь, нажав на соответствующую группу, может выбрать для нее соответствующий вариант.
Мультивыбор необходим, когда с помощью одного перечня нужно задать значение нескольким ячейкам документа.
Чтобы создать такой, следуйте инструкции:
- Выделите ячейки. Если посмотреть на рисунок, то выделять нужно начиная с C2 и заканчивая C5;
- Найдите вкладку «Данные», которая расположена на главной панели инструментов в окне программы. Затем нажмите на клавишу проверки данных, как показано на рисунке ниже;
- В открывшемся окне откройте самую первую вкладку и выберите элемент, как показано на рисунке. Таким образом в данных местах создастся перечень. В текстовом поле укажите диапазон тех ячеек, которые будут заполняться при каждом выборе элементов.
Пр имер заполнения:
Таким образом вы создадите классический горизонтальный список с функцией мультивыбора.
Однако, чтобы он заполнялся автоматически, необходимо создать макрос со следующим исходным кодом, который показан на рисунке.
Создать список в Экселе с наполнением
Стандартный перечень с наполнением позволит вам автоматизировать процесс ввода информации. При нажатии на ее, появится выбор ее возможных значений.
Пользователю остается только выбрать необходимое значение для заполнения.
Самый простой способ создать такой перечень – использовать «умные таблицы программы».
С их помощью можно легко и быстро форматировать необходимые вам виды списков с наполнением:
- Выделите необходимые ячейки и нажмите в главной вкладке на клавишу «Форматировать как таблицу»;
Пример форматирования и расположение клавиш:
- С помощью встроенного конструктора можно изменять столбцы таблицы и их предназначение.
Создать раскрывающийся список в ячейке (версия программы 2010)
Также можно создавать перечень в .
Пример указан на рисунке ниже:
Пример в ячейке листа
Выпадающий список в Excel – это довольно удобная функция, которая поможет создать более сложные документы и сделать работу удобной для визуального восприятия пользователя.
Несколько наиболее распространенных типов выпадающих списков, которые можно создать в программе Excel:
Сделать список в Эксель с мультивыбором
Создать перечень в ячейке программы можно с помощью встроенных инструментов панели управления.
Рассмотрим подробнее все основные и самые распространенные типы, и процесс их создание на практике.
Совет! Классический всплывающий список программы имеет функцию мультивыбора, то есть пользователь, нажав на соответствующую группу, может выбрать для нее соответствующий вариант.
Мультивыбор необходим, когда с помощью одного перечня нужно задать значение нескольким ячейкам документа.
Внешний вид с мультивыбором для заполнения сразу нескольких ячеек
- Выделите ячейки. Если посмотреть на рисунок, то выделять нужно начиная с C2 и заканчивая C5;
- Найдите вкладку «Данные», которая расположена на главной панели инструментов в окне программы. Затем нажмите на клавишу проверки данных, как показано на рисунке ниже;
Проверка данных
- В открывшемся окне откройте самую первую вкладку и выберите элемент, как показано на рисунке. Таким образом в данных местах создастся перечень. В текстовом поле укажите диапазон тех ячеек, которые будут заполняться при каждом выборе элементов.
Заполнение информации для создания выпадающего списка с мультивыбором значений ячеек
Пр имер заполнения:
Таким образом вы создадите классический горизонтальный список с функцией мультивыбора.
Однако, чтобы он заполнялся автоматически, необходимо создать макрос со следующим исходным кодом, который показан на рисунке.
Программный код для создания макроса
- Горячие клавиши Excel - Самые необходимые варианты
- Формулы EXCEL с примерами - Инструкция по применению
- Как построить график в Excel - Инструкция
Создать список в Экселе с наполнением
Стандартный перечень с наполнением позволит вам автоматизировать процесс ввода информации. При нажатии на ее, появится выбор ее возможных значений.
Пользователю остается только выбрать необходимое значение для заполнения.
Пользовательский список с наполнением
Самый простой способ создать такой перечень – использовать «умные таблицы программы».
С их помощью можно легко и быстро форматировать необходимые вам виды списков с наполнением:
- Выделите необходимые ячейки и нажмите в главной вкладке на клавишу «Форматировать как таблицу»;
Пример форматирования и расположение клавиш:
Процесс форматирования
- С помощью встроенного конструктора можно изменять столбцы таблицы и их предназначение.
Форматирование перечня с наполнением с помощью «умных таблиц»
Создать раскрывающийся список в ячейке (версия программы 2010)
Также можно создавать перечень в ячейке листа.
Пример указан на рисунке ниже:
Пример в ячейке листа
Чтобы создать такой, следуйте инструкции:
Заполнение и проверка вводимых значений в процессе создания списка с заполнением ячейки листа
Также вам может быть интересно:
- Округление в Excel - Пошаговая инструкция
- Таблица Эксель - Cоздание и настройка
Итоги
В статье были рассмотрены основные типы выпадающих списков и способы их создания. Помните, что процесс их создания идентичен в таких версиях программы: 2007, 2010, 2013.
Выпадающие списки помогут вам не только настроить более быструю работу всего документа, но и сделать его адаптированным для работы с большими проектами.
Тематические видеоролики к статье:
Создание выпадающих списков в Excel
4 способа создать выпадающий список на листе Excel.
Как сделать раскрывающийся список в Excel
Как в ексель сделать выпадающий список Как добавить всплывающий список в ексель Как создать в ексел раскрывающийся список