Выпадающий список в Excel с помощью инструментов или макросов
Под выпадающим списком понимается содержание в одной ячейке нескольких значений. Когда пользователь щелкает по стрелочке справа, появляется определенный перечень. Можно выбрать конкретное.
Очень удобный инструмент Excel для проверки введенных данных. Повысить комфорт работы с данными позволяют возможности выпадающих списков: подстановка данных, отображение данных другого листа или файла, наличие функции поиска и зависимости.
Создание раскрывающегося списка
Путь: меню «Данные» — инструмент «Проверка данных» — вкладка «Параметры». Тип данных – «Список».
Ввести значения, из которых будет складываться выпадающий список, можно разными способами:
- Вручную через «точку-с-запятой» в поле «Источник».
- Ввести значения заранее. А в качестве источника указать диапазон ячеек со списком.
- Назначить имя для диапазона значений и в поле источник вписать это имя.
Любой из вариантов даст такой результат.
Выпадающий список в Excel с подстановкой данных
Необходимо сделать раскрывающийся список со значениями из динамического диапазона. Если вносятся изменения в имеющийся диапазон (добавляются или удаляются данные), они автоматически отражаются в раскрывающемся списке.
- Выделяем диапазон для выпадающего списка. В главном меню находим инструмент «Форматировать как таблицу».
- Откроются стили. Выбираем любой. Для решения нашей задачи дизайн не имеет значения. Наличие заголовка (шапки) важно. В нашем примере это ячейка А1 со словом «Деревья». То есть нужно выбрать стиль таблицы со строкой заголовка. Получаем следующий вид диапазона:
- Ставим курсор в ячейку, где будет находиться выпадающий список. Открываем параметры инструмента «Проверка данных» (выше описан путь). В поле «Источник» прописываем такую функцию:
Протестируем. Вот наша таблица со списком на одном листе:
Добавим в таблицу новое значение «елка».
Теперь удалим значение «береза».
Осуществить задуманное нам помогла «умная таблица», которая легка «расширяется», меняется.
Теперь сделаем так, чтобы можно было вводить новые значения прямо в ячейку с этим списком. И данные автоматически добавлялись в диапазон.
- Сформируем именованный диапазон. Путь: «Формулы» — «Диспетчер имен» — «Создать». Вводим уникальное название диапазона – ОК.
- Создаем раскрывающийся список в любой ячейке. Как это сделать, уже известно. Источник – имя диапазона: =деревья.
- Снимаем галочки на вкладках «Сообщение для ввода», «Сообщение об ошибке». Если этого не сделать, Excel не позволит нам вводить новые значения.
- Вызываем редактор Visual Basic. Для этого щелкаем правой кнопкой мыши по названию листа и переходим по вкладке «Исходный текст». Либо одновременно нажимаем клавиши Alt + F11. Копируем код (только вставьте свои параметры).
- Сохраняем, установив тип файла «с поддержкой макросов».
- Переходим на лист со списком. Вкладка «Разработчик» — «Код» — «Макросы». Сочетание клавиш для быстрого вызова – Alt + F8. Выбираем нужное имя. Нажимаем «Выполнить».
Private Sub Worksheet_Change(ByVal Target As Range) Dim lReply As Long If Target.Cells.Count > 1 Then Exit Sub If Target.Address = "$C$2" Then If IsEmpty(Target) Then Exit Sub If WorksheetFunction.CountIf(Range("Деревья"), Target) = 0 Then lReply = MsgBox("Добавить введенное имя " & _ Target & " в выпадающий список?", vbYesNo + vbQuestion) If lReply = vbYes Then Range("Деревья").Cells(Range("Деревья").Rows.Count + 1, 1) = Target End If End If End If End Sub
Когда мы введем в пустую ячейку выпадающего списка новое наименование, появится сообщение: «Добавить введенное имя баобаб в выпадающий список?».
Нажмем «Да» и добавиться еще одна строка со значением «баобаб».
Выпадающий список в Excel с данными с другого листа/файла
Когда значения для выпадающего списка расположены на другом листе или в другой книге, стандартный способ не работает.
- Делаем активной ячейку, куда хотим поместить раскрывающийся список.
- Открываем параметры проверки данных. В поле «Источник» вводим формулу: =ДВССЫЛ(“[Список1.xlsx]Лист1!$A$1:$A$9”).
Имя файла, из которого берется информация для списка, заключено в квадратные скобки. Этот файл должен быть открыт. Если книга с нужными значениями находится в другой папке, нужно указывать путь полностью.
Как сделать зависимые выпадающие списки
Возьмем три именованных диапазона:
Это обязательное условие. Выше описано, как сделать обычный список именованным диапазоном (с помощью «Диспетчера имен»). Помним, что имя не может содержать пробелов и знаков препинания.
- Создадим первый выпадающий список, куда войдут названия диапазонов.
- Когда поставили курсор в поле «Источник», переходим на лист и выделяем попеременно нужные ячейки.
- Теперь создадим второй раскрывающийся список.
В нем должны отражаться те слова, которые соответствуют выбранному в первом списке названию. Если «Деревья», то «граб», «дуб» и т.д. Вводим в поле «Источник» функцию вида =ДВССЫЛ(E3). E3 – ячейка с именем первого диапазона.
- Создаем стандартный список с помощью инструмента «Проверка данных». Добавляем в исходный код листа готовый макрос. Как это делать, описано выше. С его помощью справа от выпадающего списка будут добавляться выбранные значения.
- Чтобы выбранные значения показывались снизу, вставляем другой код обработчика.
- Чтобы выбираемые значения отображались в одной ячейке, разделенные любым знаком препинания, применим такой модуль.
- На вкладке «Разработчик» находим инструмент «Вставить» – «ActiveX». Здесь нам нужна кнопка «Поле со списком» (ориентируемся на всплывающие подсказки).
- Щелкаем по значку – становится активным «Режим конструктора». Рисуем курсором (он становится «крестиком») небольшой прямоугольник – место будущего списка.
- Вписываем диапазон в строку ListFillRange (руками). Ячейку, куда будет выводиться выбранное значение – в строку LinkedCell. Для изменения шрифта и размера – Font.
Выбор нескольких значений из выпадающего списка Excel
Бывает, когда из раскрывающегося списка необходимо выбрать сразу несколько элементов. Рассмотрим пути реализации задачи.
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
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
Не забываем менять диапазоны на «свои». Списки создаем классическим способом. А всю остальную работу будут делать макросы.
Выпадающий список с поиском
Скачать пример выпадающего списка
При вводе первых букв с клавиатуры высвечиваются подходящие элементы. И это далеко не все приятные моменты данного инструмента. Здесь можно настраивать визуальное представление информации, указывать в качестве источника сразу два столбца.
Как сделать выпадающий список в Excel
18 ноября 2022 Ликбез Технологии
Лайфхакер собрал семь способов: от простых к сложным.
Как сделать простой выпадающий список в Excel через функцию вставки
Элементарный вариант: не нуждается в какой-либо предварительной настройке и доступен благодаря стандартной функции вставки. Он позволяет выбрать из списка ранее введённые значения и быстро добавить их. Однако это работает, только если данные содержатся в столбце без пропусков, а ввод осуществляется по порядку.
Поставьте курсор в следующую пустую ячейку диапазона. В нашем примере это B7.
Сделайте правый клик мышью и укажите «Выбрать из раскрывающегося списка» либо просто нажмите на клавиатуре Alt + «стрелка вниз».
После этого появится список всех введённых выше значений без повторов, а вам останется лишь выбрать желаемое и кликнуть по нему.
Как сделать выпадающий список в Excel из диапазона данных
Следующий способ позволяет использовать для выпадающего списка какой угодно диапазон данных, который находится в любом месте листа, а также на ином листе или даже в другом файле (об этом далее). Список не будет обновляться, если вы добавите информацию ниже указанного диапазона, но если вставлять её не в конец, а между строк — всё будет работать. При удалении же элементов списка он изменится в любом случае.
Выделите ячейку, в которой должен быть раскрывающийся список (у нас это h4), переключитесь на вкладку «Данные» и кликните «Проверка данных».
В открывшемся окне выберите тип данных «Список», кликните в поле «Источник» и выделите нужные ячейки.
Нажмите OK, и в заданной ячейке появится выпадающий список с нужными значениями.
Как сделать выпадающий список в Excel из именованного диапазона
Любой диапазон ячеек можно сделать именованным, присвоив ему название. Это позволит ссылаться на него и упростит запоминание диапазонов, с которыми вы часто работаете. В остальном такой вариант выпадающего списка не отличается от предыдущего: он будет обновляться лишь в том случае, если вы удалите одно из значений или добавите строки внутрь диапазона.
Для начала создайте именованный диапазон. Для этого выделите ячейки с будущими пунктами выпадающего списка и озаглавьте, вписав имя в поле с названиями ячеек, а затем нажмите Enter. Обязательное условие: первым символом должна быть буква, нельзя использовать пробелы (ставьте вместо них «_»).
Теперь поставьте курсор в ячейку, где хотите сделать список, и на вкладке «Данные» нажмите «Проверка данных».
В графе «Тип данных» выберите «Список», а в качестве источника укажите название диапазона, поставив перед ним «=». В нашем примере вышло «=бренды».
Выпадающий список с элементами из именованного диапазона готов.
Как сделать выпадающий список в Excel с данными другого листа или файла
Иногда нужно использовать внешние данные, которые расположены на другом листе или вообще в отдельной книге. В этом случае поможет функция ДВССЫЛ, которая позволит сослаться на них. Поскольку в качестве источника используется диапазон ячеек, список будет обновляться лишь в том случае, если вы впишете внутрь диапазона новые значения или удалите их. Кроме того, при ссылке на другой файл тот должен быть открыт.
Откройте оба файла, затем выделите ячейку с будущим списком и на вкладке «Данные» кликните «Проверка данных».
Выберите «Список», в качестве источника укажите такую формулу: =ДВССЫЛ("[имя_файла]имя_листа!диапазон")
То есть внутри кавычек должно быть название файла с данными для списка в квадратных скобках, потом без пробела название листа, а затем так же без пробела восклицательный знак и обозначение диапазона. В нашем примере будет так:
=ДВССЫЛ("[Книга1]Лист1!A3:A9")
Список готов. Чтобы использовать данные из другого файла, он в это время тоже должен быть открыт.
Как сделать выпадающий список в Excel из таблицы
Следующий способ чуть сложнее, но гораздо универсальнее и правильнее. В его основе лежат так называемые умные таблицы, они динамические. А значит, любые изменения, будь то добавление новых пунктов или их удаление, моментально отразятся в выпадающем списке.
Выделите диапазон с элементами списка и их категорией, а затем на вкладке «Главная» кликните «Форматировать как таблицу».
Выберите любой вариант дизайна, отметьте в открывшемся окошке опцию «Таблица с заголовками» и нажмите OK.
Поставьте курсор в одну из ячеек таблицы и на появившейся вкладке «Конструктор таблиц» введите имя списка, подтвердив его нажатием Enter.
Кликните по ячейке, в которой должен быть список, и нажмите «Проверка данных» на вкладке «Данные».
Укажите «Список» как тип данных, а для источника введите такую формулу: =ДВССЫЛ("имя_таблицы")
В нашем примере будет так: =ДВССЫЛ("Двери")
Список сразу же заработает и будет обновляться при удалении и добавлении элементов.
Как сделать выпадающий список в Excel с поиском
Если список достаточно большой, то можно облегчить ввод значений подсказками, которые ускорят поиск нужных элементов. Сделать это можно через инструменты разработчика ActiveX.
Если вкладка «Разработчик» не отображается, для начала следует включить её. Для этого раскройте меню и выберите «Другие команды…».
Кликните «Настроить ленту», затем включите пункт «Разработчик» и нажмите OK.
На вкладке «Разработчик» кликните «Вставить» и выберите «Поле со списком» из элементов ActiveX.
«Нарисуйте» с помощью курсора ячейку списка, после чего кликните по ней правой кнопкой мыши и выберите «Свойства».
Найдите строку ListFillRange, введите в неё диапазон значений для списка и нажмите Enter. В нашем примере это B6:B9.
Отключите режим конструктора, кликнув по одноимённой кнопке. Теперь список работает, и при вводе будут подсвечиваться подходящие пункты.
Как сделать связанный выпадающий список в Excel
Весьма полезный вид списка, который встречается довольно часто. После выбора строки в одном списке во втором будут доступны разные наборы значений в зависимости от предыдущего. Например, при указании категории товаров в первом перечне во втором меняются модели. Рассмотрим реализацию такого списка на основе таблиц, чтобы данные автоматически обновлялись при добавлении или удалении пунктов.
Подготовьте столбцы с данными: в шапках пункты первого, родительского списка, а под ними пункты связанного с ним второго. Выделите первый столбец с шапкой и на вкладке «Главная» кликните «Форматировать как таблицу».
Выберите любой из стилей, а затем в появившемся окошке включите опцию «Таблица с заголовками» и нажмите OK.
Кликните по любой из ячеек таблицы, а затем присвойте ей имя, вписав его в поле «Имя таблицы» и нажав Enter. Это будет пункт первого списка, в нашем примере — «Компьютеры». Таким же образом создайте таблицы для остальных столбцов и переименуйте их.
Поставьте курсор в ячейку, где будет первый выпадающий список, и на вкладке «Данные» кликните «Проверка данных».
Укажите в качестве типа «Список», а в качестве источника выделите шапки столбцов с категориями, которые станут пунктами родительского перечня.
Далее установите курсор в ячейку, в которой будет второй список, кликните «Проверка данных», выберите вариант «Список», а в поле «Источник» впишите =ДВССЫЛ и адрес ячейки родительского списка в скобках. То есть =ДВССЫЛ(h4) для нашего примера.
Теперь при выборе пункта родительского списка в связанном с ним будут отображаться только соответствующие варианты. Например, если нажать на строку «Ноутбуки», то Excel покажет только модели лэптопов.
А если указать «Наушники» — будут отображены соответствующие названия аксессуаров из одноимённого столбца.
Читайте также 🧐
- 10 формул в Excel, которые облегчат вам жизнь
- Как сделать диаграмму в Excel
- Как сделать или убрать разрыв страницы в Word и Excel
- Как в Excel закрепить область при прокрутке
- 12 простых приёмов для ускоренной работы в Excel
Видео: Параметры раскрывающегося списка — Служба поддержки Майкрософт
Для определения параметров в раскрывающемся списке можно использовать список с разделителями-запятыми, диапазон ячеек или именованный диапазон.
В предыдущем видео мы использовали список, разделенный запятыми.
Вы можете использовать такой список, если имеется всего несколько значений и маловероятно, что они изменятся.
Если вам нужно изменить элементы списка, например добавить или удалить элементы, управление этим типом раскрывающегося списка требует больше времени.
Мы рассмотрим управление раскрывающимися списками в видео 4.
Список, разделенный запятыми, также чувствителен к регистру.
Это может быть проблемой, когда кто-то вводит запись, а не выбирает ее из списка.
Например, ввод YES всеми заглавными буквами возвращает ошибку, если сообщения об ошибках включены, что является значением по умолчанию.
Чтобы избежать этой проблемы, давайте использовать диапазон ячеек для записей в раскрывающемся списке.
Выберите ячейку, в которой вы хотите разместить раскрывающийся список.
Перейдите на вкладку ДАННЫЕ и щелкните Проверка данных .
В диалоговом окне Проверка данных установите Разрешить на Список ; это включает список в ячейке.
Оставить раскрывающийся список в ячейке выбранным; это включает раскрывающийся список в ячейке.
Оставить Игнорировать пустое выбрано; мы расскажем об этом в следующем видео.
Чтобы указать параметры в раскрывающемся списке, щелкните Источник и выберите диапазон ячеек, содержащий параметры.
Он может находиться на другом рабочем листе, как в этом примере, что обеспечивает большую гибкость в настройке и защите рабочих листов. Мы расскажем об этом в видео 4.
Диапазон должен быть одной строкой или столбцом, и нажмите OK .
Убедитесь, что ячейка содержит раскрывающийся список с параметрами, предоставляемыми диапазоном ячеек.
Чтобы использовать этот раскрывающийся список в других местах, скопируйте его в другие ячейки.
Выберите ячейку.
Если отображается текст или число, нажмите Удалить, чтобы очистить его.
Таким образом, текст и числа не будут отображаться в ячейках назначения, поэтому не будет казаться, что запись уже выбрана.
Вы можете использовать сочетание клавиш Ctrl+C, чтобы скопировать ячейку.
Затем выберите ячейки назначения и нажмите Ctrl+V, чтобы вставить их.
Эти ячейки теперь имеют раскрывающийся список.
Именованный диапазон, например Fruits, легче запомнить, чем диапазон ячеек, например A2:A37.
Чтобы использовать именованный диапазон для опций в раскрывающемся списке, вы должны начать с его создания.
Выберите диапазон ячеек, которому вы хотите присвоить имя.
В поле имени введите имя для диапазона. Например, «_Veggies».
Первым символом имени должна быть буква или знак подчеркивания.
Остальная часть имени может состоять из букв, цифр, точек и знаков подчеркивания.
Имя не может содержать пробелы.
И вы не можете использовать предопределенные операторы, такие как истина или ложь, или ссылки на ячейки, такие как A1.
При выборе ячеек именованного диапазона вы увидите имя в поле имени.
Теперь вы готовы создать раскрывающийся список, использующий именованный диапазон.
Выберите ячейку, в которой вы хотите открыть раскрывающийся список. Щелкните Data Validation , выберите List , щелкните Source , нажмите F3, выберите имя, щелкните OK и еще раз щелкните OK .
Убедитесь, что ячейка содержит раскрывающийся список с записями, предоставленными именованным диапазоном, и скопируйте список в другие ячейки.
Далее, Ввод и сообщения об ошибках .
Как быстро и легко создать раскрывающийся список в Excel
Дом и офис
Дом Домашний офис Рабочая жизнь Производительность
Хотите стать более эффективным и опытным в Excel? Вот краткое пошаговое руководство по созданию раскрывающихся списков.
Автор Мария Диаз, штатный писатель наПроверено Мин Шин
simpson33/iStock/Getty Images PlusНезависимо от того, ведете ли вы бизнес или ведете домашнее хозяйство, поддержание порядка является ключом к успеху. В настоящее время существует множество приложений и веб-сайтов, которые помогут вам оставаться организованным, и, хотя я пробовал многие, я всегда возвращаюсь к старому верному Microsoft Excel.
Также: Как добавить раскрывающийся список в Google Таблицы
Microsoft Excel — это больше, чем компьютерная программа, которую ваши родители использовали на работе. При эффективном использовании он может стать полезным инструментом в вашем арсенале, независимо от того, являетесь ли вы владельцем бизнеса, профессионалом, вам нужно управлять финансами или расписанием домашних хозяйств, или вам просто нужен бюджет. Раскрывающиеся списки — это один из способов упростить ввод данных в Excel, что полезно, когда время стоит денег.
Как легко добавлять раскрывающиеся списки в Excel
Выбранные ячейки для добавления в раскрывающийся список.
Мария Диас/ZDNETПерейдите к данным и выберите Проверка данных.
Мария Диаз/ZDNETВ разделе Разрешить выберите Список.
Мария Диаз/ZDNETВведите данные для раскрывающегося списка.
Мария Диаз/ZDNETРаскрывающийся список.
Мария Диаз/ZDNETЧасто задаваемые вопросы
Как создать раскрывающийся список «да/нет» в Excel?
Создание раскрывающегося списка «да/нет» в Excel использует процесс, который мы использовали для примера выше. Все, что вам нужно сделать, это выполнить следующие шаги:
- Выберите ячейки, которые вы хотите содержать в раскрывающихся списках.
- Щелкните Данные Проверка .