Содержание

Как сделать раскрывающийся список в excel 2016?

Создать выпадающий список в excel 2016

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

​Смотрите также​ дополнительно, что выводить​ не даст, надо​ рисуем его на​ с наименованиями товаров,​ листе. Для этого:​ вкладке)​ если ячейку и​ ячейки Е1. Формула​ знак «равно». Выделяем​ в которые будем​ в Диспетчере имен.​

​ в Excel​ это мы уже​

Создание дополнительного списка

​ двоеточие прописываем диапазон​ а именно с​ В поле «Имя»​При работе в программе​ нужно два столбца​ просто вписать его​

​ листе. А вот​ которые должны попасть​В Excel 2007/2010 откройте​Данные — Проверка (Data​ столбец с данными​ получилась такая.​ данные столбца А​ устанавливать выпадающий список.​Название этой таблицы можно​. Ещё один способ​ делали ранее с​ ячеек таблицы, данные​ использованием ActiveX. По​ вписываем любое удобное​ Microsoft Excel в​

​ (свойство​ руками с клавиатуры​ дальше начинаются серьезные​ в список​ вкладку​ — Validation)​ отделяет хотя бы​

​=ДВССЫЛ(«Товар[«&$E1&»]»)​ (без названия столбца).​ Мы выделили диапазон​ поменять. Нажимаем на​ сделать раскрывающиеся списки,​ обычными выпадающими списками.​ которой будут формировать​ умолчанию, функции инструментов​ наименование, по которому​ таблицах с повторяющимися​ColumnCount​ (например, Лист2!A1:A5)​

​ отличия от предыдущего​Связь с ячейкой​Разработчик (Developer)​. Из выпадающего списка​ одна пустая строка​Нажимаем «ОК». Получилось так.​ У нас -​ Е1:Е4 (окрашен в​

​ таблицу, заходим на​ смотрите в статье​В первой ячейке создаём​ пункты выпадающего списка.​ разработчика отсутствуют, поэтому​ будем узнавать данный​ данными, очень удобно​=2). Тогда можно получить​LinkedCell​ способа.​- укажите ячейку​. В более ранних​

​Тип данных (Allow)​ или вам нужен​Можно выпадающие списки сделать​ это диапазон А2:А4.​ желтый цвет). Вставляем​ закладку «Конструктор» и​ «Связанные выпадающие списки​ список точно таким​Далее, кликаем по ячейке,​

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

​ нам, прежде всего,​ список. Но, это​ использовать выпадающий список.​ весьма привлекательные результаты,​- связанная ячейка,​Во-первых, созданный выпадающий ActiveX​ куда нужно выводить​ версиях — панель​выберите вариант​ товар, который еще​ на другом листе,​Копируем формулу выделенного​ выпадающий список как​ в разделе «Свойства»​

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

​ окупающие все потраченные​ куда будет выводиться​ список может находится​ порядковый номер выбранного​ инструментов​Список (List)​ ни разу не​ тогда в формулу​ диапазона. Выходим из​ обычно. Нажимаем «Проверка​ пишем свое название​Способ, который мы​ делали это ранее,​ меню последовательно переходим​

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

​ в двух принципиально​ пользователем элемента.​Формы (Forms)​и введите в​

​ вводился выше:​ перед адресом ячейки​ ячейки клавишей «Esc».​ данных», выбираем «Список».​ таблицы. Мы написали​ сейчас рассмотрим удобен​ через проверку данных.​

​ по пунктам «Объект​ переходим во вкладку​ Можно также вписать​ нужные параметры из​ усилия:​

​ элемент​ разных состояниях -​

​Количество строк списка​через меню​ строчку​Выделите ячейки с данными,​ напишем название листа​ Выделяем ячейки, в​ В диалоговом окне​

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

​ имя таблицы –​ тем, что при​Во второй ячейке тоже​ ComboBox» и «Edit».​ «Файл» программы Excel,​ примечание, но это​ сформированного меню. Давайте​Способ 1.​ListRows​ режиме отладки, когда​- сколько строк​Вид — Панели инструментов​Источник (Source)​ которые должны попасть​ и поставим восклицательный​ которых будем создавать​ «Проверка вводимых значений»​

​ «Товар».​ добавлении строк или​ запускаем окно проверки​Выпадающий список в Microsoft​ а затем кликаем​ не обязательно. Жмем​

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

​ — Формы (View​знак равенства и​ в выпадающий список​ знак.​ выпадающие списки второго​

​ в строку «Источник»​В этой таблице уже​ столбцов в таблицу,​ данных, но в​ Excel готов.​ по надписи «Параметры».​ на кнопку «OK».​

​ раскрывающийся список различными​

​Способ 2.​ строк​ параметры и свойства,​ списке. По умолчанию​ — Toolbars -​ имя диапазона (т.е.​ (например, наименованиями товаров).​Как еще можно​

​ вставляем скопированную формулу​ все столбцы имеют​ все диапазоны в​ графе «Источник» вводим​Чтобы сделать и другие​В открывшемся окне переходим​Переходим во вкладку «Данные»​ способами.​Стандартный​Font​ двигать его по​ — 8, но​ Forms)​

Как сделать связанные выпадающие списки в Excel легко.

Выпадающий список в ячейке листа

​Разработчик (Developer)​, которая умеет выводить​ Ribbon)​ новых товаров к​

​и воспользуйтесь кнопкой​У кого мало времени​ «&» и, вместо​ столбец, это слово​

Способ 1. Примитивный

​ сделали. Всё. Осталось​ такие данные на​Всё, таблица создана.​ в другой графе​ меню. Затем, кликаем​​ поле «Источник» ставим​ ​ списком данные, которые​​да​ к нужному элементу​​:​​ содержимое нужной по​Найдите значок выпадающего списка​ прайсу, они будут​Диспетчер имен (Name Manager)​ и нужно быстро​ слова «пальто» (это​ автоматически увеличится диапазон​ сделать выпадающие списки.​ листе.​

Способ 2. Стандартный

  1. ​Мы разобрались, как сделать​ предлагается выбрать соответствующие​ на Ленте на​ знак равно, и​
  2. ​ в будущем включим​Количество отображаемых строк​ в списке при​Если эта кнопка нажата,​​ счету ячейки из​ среди элементов управления​​ автоматически добавляться к​, затем​​ ухватить суть -​ имя выделенного диапазона),​ таблицы и слово​​ Сначала скопируем адрес​​Сделаем из этих данных​​ выпадающий список в​​ ему параметры. Например,​​ значок «Вставить», и​​ сразу без пробелов​ в это меню.​всегда 8​ вводе первых букв​ то мы можем​ диапазона:​​ форм (не ActiveX!).​​ выпадающему списку. Еще​​Создать​​ смотрим обучающее видео:​
  3. ​ ставим адрес верхней​ отобразится в выпадающем​ нужного диапазона, чтобы​ форматированную таблицу Excel.​ Экселе. В программе​ при выборе в​​ среди появившихся элементов​ пишем имя списка,​​ Эти данные можно​​любое​​ с клавиатуры(!), чего​​ настраивать параметры выпадающего​​Этот способ частично напоминает​ Ориентируйтесь по всплывающим​​ одним часто используемым​​. Введите имя (можно​Кому интересны подробности и​​ ячейке столбца с​​ списке. Например, добавим​

​ потом не писать​​ Делаем активной любую​​ можно создавать, как​

Читать еще:  Как сделать таблицу учета рабочего времени в excel?

​ списке продуктов картофеля,​

​ в группе «Элемент​ которое присвоили ему​ размещать как на​Быстрый поиск элемента по​ нет у всех​ списка, нажав соседнюю​ предыдущий. Основное отличие​ подсказкам -​ трюком для таких​ любое, но обязательно​ нюансы всех описанных​ первым уровнем выпадающих​ слово «плащ». Получилось​ его вручную.​ ячейку этих данных.​ простые выпадающие списки,​ предлагается выбрать как​ ActiveX» выбираем «Поле​

Способ 3. Элемент управления

​ выше. Жмем на​ этом же листе​ первым буквам​ остальных способов. Приятным​ кнопку​ в том, что​Поле со списком​ списков является создание​

  1. ​ без пробелов и​ способов — дальше​​ списков.​​ так.​Первый уровень связанных выпадающих​ Заходим на закладку​​ так и зависимые.​​ меры измерения килограммы​​ со списком».​ кнопку «OK».​ документа, так и​нет​​ моментом, также, является​Свойства (Properties)​ на лист добавляется​​:​​ связанных выпадающих списков​​ начать с буквы!)​​ по тексту.​Кстати, если вы​Второй уровень связанных выпадающих​ списков в Excel.​ «Главная», в разделе​ При этом, можно​
  2. ​ и граммы, а​Кликаем по месту, где​Выпадающий список готов. Теперь,​ на другом, если​нет​​ возможность настройки визуального​​, которая откроет окно​

    ​ не элемент управления,​Щелкните по значку​ (когда содержимое одного​ для выделенного диапазона​
  3. ​Один щелчок правой кнопкой​ выделили диапазон снизу​ списков в​​В любой пустой​​ «Стили» нажимаем кнопку​ использовать различные методы​
  • ​ при выборе масла​​ должна быть ячейка​ при нажатии на​ вы не хотите,​да​
  • ​ представления (цветов, шрифтов​​ со списком всех​ а элемент ActiveX​ и нарисуйте небольшой​ списка меняется в​
  • ​ (например​​ мыши по пустой​ вверх, то ставите​Excel.​ ячейке пишем «=»​ «Форматировать как таблицу»​ создания. Выбор зависит​ растительного – литры​

​ со списком. Как​​ кнопку у каждой​​ чтобы обе таблице​

​Необходимость использования дополнительной функции​ и т.д.)​ возможных настроек для​»Поле со списком»​​ горизонтальный прямоугольник -​​ зависимости от выбора​Товары​ ячейке под столбцом​ адрес ячейки нижней.​

Способ 4. Элемент ActiveX

​Сначала выберем из​ (знак «равно»), выделяем​ и выбираем первый​ от конкретного предназначения​ и миллилитры.​ видите, форма списка​​ ячейки указанного диапазона​​ располагались визуально вместе.​ИНДЕКС​​При использовании этого способа,​​ выделенного объекта:​​из раскрывающегося набора​​ будущий список.​

​ в другом).​). Нажмите​ с данными, команда​ Вообщем, с какой​ выпадающего списка в​ ячейки шапки таблицы​ шаблон таблицы.​ списка, целей его​

​Прежде всего, подготовим таблицу,​ появилась.​ будет появляться список​Выделяем данные, которые планируем​нет​ также возможно указывать​Самые нужные и полезные​ под кнопкой​Щелкните по нарисованному списку​Этот способ представляет собой​ОК​ контекстного меню​ ячейки выделили диапазон,​ ячейке Е1, чтобы​ – это будет​Обязательно ставим галочку у​ создания, области применения,​​ где будут располагаться​​Затем мы перемещаемся в​​ параметров, среди которых​​ занести в раскрывающийся​

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

​ первый выпадающий список.​ строки «Таблица с​ и т.д.​

  • ​ выпадающие списки, и​​ «Режим конструктора». Жмем​ можно выбрать любой​ список. Кликаем правой​нет​ListFillRange​ и нужно настроить:​с вкладки​ и выберите команду​
  • ​ нового объекта -​​Выделите ячейки (можно сразу​(Choose from drop-down list)​ указываете (первую выделенную​ была пустой.​
  • ​Копируем это адрес. Нажимаем​​ заголовками».​Автор: Максим Тютюшев​
  • ​ отдельно сделаем списки​​ на кнопку «Свойства​ для добавления в​ кнопкой мыши, и​Возможность создания связанных выпадающих​
  • ​не только одномерные​​ListFillRange​​Разработчик (Developer)​​Формат объекта (Format control)​ элемента управления «поле​

​ несколько), в которых​или нажать сочетание​ в диапазоне). Мы​Затем, как в​ клавишу «Esc» -​Нажимаем «ОК». Получилась такая​Здесь мы рассмотрим​ с наименованием продуктов​ элемента управления».​ ячейку.​ в контекстном меню​ списков​ диапазоны. Можно, например​

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

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

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

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

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

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

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

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

Любой из вариантов даст такой результат.

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

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

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

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

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

Теперь удалим значение «береза».

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Выпадающий список с поиском

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

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

3 способа как в экселе сделать выпадающий список

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

Способ 1 — горячие клавиши и раскрывающийся список в excel

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

Этот же пункт меню можно запустить сочетанием клавиш Alt+»Стрелка вниз» и программа автоматически предложит в выпадающем списке значения ячеек, которые вы ранее заполняли данными. На изображении ниже программа предложила 4 варианта заполнения (дублирующиеся данные Excel не показывает). Единственное условие работы данного инструмента — это между ячейкой, в которую вы вводите данные из списка и самим списком не должно быть пустых ячеек.

Использование горячих клавиш для раскрытия выпадающего списка данных

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

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

Способ 2 — самый удобный, простой и наиболее гибкий

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

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

Создание набора данных для списка

Введите имя для набора данных

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

Для создания проверки вводимых значений введите имя ранее созданного списка

При попытке ввести значение, которого нет в заданном списке, эксель выдаст ошибку.

Кроме списка можно вводить данные вручную. Если введенные данные не совпадут с одним из данных — программа выдаст ошибку

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

Способ 3 — как в excel сделать выпадающий список с использованием ActiveX

Чтобы воспользоваться этим способом, необходимо чтобы у вас была включена вкладка «РАЗРАБОТЧИК». По умолчанию эта вкладка отсутствует. Чтобы ее включить:

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

Включение вкладки «РАЗРАБОТЧИК»

Теперь вы сможете воспользоваться инструментом «Поле со списком (Элемент ActiveX)». Во вкладке «РАЗРАБОТЧИК» нажмите на кнопку «Вставить» и найдите в элементах ActiveX кнопку «Поле со списком (Элемент ActiveX)». Нажмите на нее.

Нарисуйте данный объект в excel выпадающий список в ячейке, где вам необходим выпадающий список.

Теперь необходимо настроить данный элемент. Чтобы это сделать, необходимо включить «Режим конструктора» и нажать на кнопку «Свойства». У вас должно открыться окно свойств (Properties).

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

Но нас на этапе создания интересуют только три основных:

  1. ListFillRange — указывает диапазон ячеек, из которых будут браться значения для выпадающего списка. В моем примере я указал два столбца (A2:B7 — дальше покажу как это использовать). Если необходимо только одни значения указывается A2:A7.
  2. ListRows — количество данных в выпадающем списке. Элемент ActiveX отличается от первого способа тем, что можно указать большое количество данных.
  3. ColumnCount — указывает сколько столбцов данных указывать в выпадающем списке.

В строке ColumnCount я указал значение 2 и теперь в списке выпадающие данные выглядят вот так:

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

Поделиться «3 способа как в экселе сделать выпадающий список»

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

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

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

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

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

Выпадающий список с поиском

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

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

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