Выпадающий список Excel: создание, редактирование, удаление и более сложные операции
Выпадающий список похож на поле списка, которое позволяет пользователям выбирать одно значение из списка выбора. В этом руководстве будут продемонстрированы основные операции для раскрывающегося списка: создание, редактирование и удаление раскрывающегося списка в Excel. Кроме того, в этом руководстве представлены расширенные операции для раскрывающегося списка, чтобы расширить его функциональные возможности и решить больше проблем с Excel.
Содержание: [ Скрывать ]
Создать простой раскрывающийся список
Чтобы использовать раскрывающийся список, вам необходимо сначала научиться его создавать. В этом разделе представлены 6 способов помочь вам создать раскрывающийся список в Excel.
Создать раскрывающийся список из диапазона ячеек
Здесь демонстрируются шаги по созданию раскрывающегося списка из диапазона ячеек в Excel. Пожалуйста, сделайте следующее
1. Выберите диапазон ячеек для поиска раскрывающегося списка.
Советы: Вы можете создать раскрывающийся список для нескольких несмежных ячеек одновременно, удерживая Ctrl при выборе ячеек одну за другой.
2. Нажмите Данные > проверка достоверности данных > проверка достоверности данных.
3. в проверка достоверности данных диалоговое окно под Настройки вкладку настройте следующим образом.
Заметки:
Теперь выпадающий список создан. Если щелкнуть ячейку раскрывающегося списка, рядом с ней отобразится стрелка, щелкните стрелку, чтобы развернуть список, а затем вы можете выбрать элемент из него.
Создать динамический раскрывающийся список из таблицы
Вы можете преобразовать свой диапазон данных в таблицу Excel, а затем создать динамический раскрывающийся список на основе диапазона таблицы.
1. Выберите исходный диапазон данных, а затем нажмите Ctrl + T ключи.
2. Нажмите OK в появлении Создать таблицу диалоговое окно. Затем диапазон данных преобразуется в таблицу.
3. Выберите диапазон ячеек для размещения раскрывающегося списка, а затем щелкните Данные > проверка достоверности данных > проверка достоверности данных.
4. в проверка достоверности данных диалоговое окно, вам необходимо:
Затем создаются динамические выпадающие списки. При добавлении или удалении данных из диапазона таблицы значения в раскрывающемся списке будут обновлены автоматически.
Создать динамический раскрывающийся список с формулами
Помимо создания динамического раскрывающегося списка из диапазона таблицы, вы также можете использовать формулу для создания динамического раскрывающегося списка в Excel.
1. Выберите ячейки для вывода раскрывающихся списков.
2. Нажмите Данные > проверка достоверности данных > проверка достоверности данных.
3. в проверка достоверности данных диалоговое окно, настройте следующим образом.
Затем создаются динамические выпадающие списки. При добавлении или удалении данных из определенного диапазона значения в раскрывающихся списках будут обновляться автоматически.
Создать раскрывающийся список из именованного диапазона
Вы также можете создать раскрывающийся список из именованного диапазона в Excel.
1. Сначала создайте именованный диапазон. Выберите диапазон ячеек, на основе которого вы создадите именованный диапазон, а затем введите имя диапазона в поле ФИО и нажмите Enter .
2. Нажмите Данные > проверка достоверности данных > проверка достоверности данных.
3. в проверка достоверности данных диалоговое окно, настройте следующим образом.
Теперь создается раскрывающийся список с использованием данных из именованного диапазона.
Создать раскрывающийся список из другой книги
Предположим, есть рабочая тетрадь с названием «Источник данных», И вы хотите создать раскрывающийся список в другой книге на основе данных в этом«Источник данных», Пожалуйста, сделайте следующее.
1. Откройте книгу «Исходные данные». В этой книге выберите данные, которые вы создадите в раскрывающемся списке на основе, введите имя диапазона в поле ФИО поле, а затем нажмите Enter .
Здесь я называю диапазон Городом.
2. Откройте лист, который вы вставите в раскрывающийся список. Нажмите Формулы > Определить имя.
3. в Новое имя В диалоговом окне вам необходимо создать именованный диапазон на основе имени диапазона, созданного в книге «SourceData», настройте следующим образом.
Заметки:
4. Откройте книгу, которую вы вставите в раскрывающийся список, выберите ячейки для раскрывающегося списка и нажмите Данные > проверка достоверности данных > проверка достоверности данных.
5. в проверка достоверности данных диалоговое окно, настройте следующим образом.
Теперь выпадающие списки вставлены в выбранный диапазон. И раскрывающиеся значения взяты из другой книги.
Легко создавайте раскрывающийся список с помощью замечательного инструмента
Здесь очень рекомендую Создать простой раскрывающийся список полезности Kutools for Excel. С помощью этой функции вы можете легко создать раскрывающийся список с определенными значениями ячеек или создать раскрывающийся список с настраиваемыми списками, предустановленными в Excel.
1. Выберите ячейки, которые вы хотите вставить, раскрывающийся список, а затем щелкните Кутулс > Раскрывающийся список > Создать простой раскрывающийся список.
2. в Создать простой раскрывающийся список диалоговое окно, настройте следующим образом.
Внимание: Если вы хотите создать раскрывающийся список на основе пользовательского списка, предустановленного в Excel, выберите Пользовательские списки вариант в Источник раздел, выберите настраиваемый список в Пользовательские списки поле, а затем щелкните OK .
Теперь выпадающие списки вставлены в выбранный диапазон.
Редактировать раскрывающийся список
Если вы хотите отредактировать раскрывающийся список, методы из этого раздела могут оказать вам услугу.
Редактировать раскрывающийся список на основе диапазона ячеек
Чтобы отредактировать раскрывающийся список на основе диапазона ячеек, сделайте следующее.
1. Выберите ячейки, содержащие раскрывающийся список, который нужно отредактировать, и нажмите Данные > проверка достоверности данных > проверка достоверности данных.
2. в проверка достоверности данных диалоговом окне измените ссылки на ячейки в Источник и нажмите OK .
Редактировать раскрывающийся список на основе именованного диапазона
Предположим, вы добавляете или удаляете значения в именованном диапазоне, и раскрывающийся список создается на основе этого именованного диапазона. Чтобы обновленные значения отображались в раскрывающихся списках, сделайте следующее.
1. Нажмите Формулы > Менеджер имен.
Советы: Вы можете открыть Менеджер имен окно, нажав кнопку Ctrl + F3 ключи.
2. в Менеджер имен окно необходимо настроить следующим образом:
3. Затем Microsoft Excel появится диалоговое окно, щелкните значок Да чтобы сохранить изменения.
Затем обновляются раскрывающиеся списки на основе этого именованного диапазона.
Удалить раскрывающийся список
В этом разделе говорится об удалении выпадающего списка в Excel.
Удалить раскрывающийся список со встроенной программой Excel
Excel предоставляет встроенную функцию, помогающую удалить раскрывающийся список с листа. Пожалуйста, сделайте следующее.
1. Выберите диапазон ячеек, содержащий раскрывающийся список, который вы хотите удалить.
2. Нажмите Данные > проверка достоверности данных > проверка достоверности данных.
3. в проверка достоверности данных диалогового окна, нажмите Очистить все и нажмите кнопку OK для сохранения изменений.
Теперь выпадающие списки удаляются из выбранного диапазона.
Легко удаляйте раскрывающиеся списки с помощью замечательного инструмента
Kutools for Excel предоставляет удобный инструмент - Очистить ограничение проверки данныхs чтобы упростить удаление раскрывающегося списка из одного или нескольких выбранных диапазонов одновременно. Пожалуйста, сделайте следующее.
1. Выберите диапазон ячеек, содержащий раскрывающийся список, который вы хотите удалить.
2. Нажмите Кутулс > Предотвратить ввод > Очистить ограничения проверки данных. Смотрите скриншот:
3. Затем Kutools for Excel всплывает диалоговое окно с вопросом, очистить ли раскрывающийся список, нажмите OK .
Затем выпадающие списки в этом выбранном диапазоне немедленно удаляются.
Добавить цвет в раскрывающийся список
В некоторых случаях вам может потребоваться составить раскрывающийся список с цветовым кодированием, чтобы сразу различать данные в ячейках раскрывающегося списка. В этом разделе представлены два метода, которые помогут детально решить проблему.
Добавить цвет в раскрывающийся список с условным форматированием
Вы можете создать условные правила для ячейки, содержащей раскрывающийся список, чтобы сделать его цветным. Пожалуйста, сделайте следующее.
1. Выберите ячейки, содержащие раскрывающийся список, для которого вы хотите сделать цветовую кодировку.
2. Нажмите Главная > Условное форматирование > Управление правилами.
3. в Диспетчер правил условного форматирования диалогового окна, нажмите Новое правило .
4. в Новое правило форматирования диалоговое окно, настройте следующим образом.
5. Когда он вернется в Диспетчер правил условного форматирования В диалоговом окне повторите шаги 3 и 4, чтобы указать цвета для других раскрывающихся элементов. После завершения указания цветов щелкните значок OK для сохранения изменений.
С этого момента при выборе элемента из раскрывающегося списка ячейка будет выделяться указанным цветом фона на основе выделенного текста.
Легко добавляйте цвет в раскрывающийся список с помощью замечательного инструмента
Здесь представьте Цветной раскрывающийся список особенность Kutools for Excel чтобы помочь вам легко добавить цвет в раскрывающийся список в Excel.
1. Выберите ячейки, содержащие раскрывающийся список, в который вы хотите добавить цвет.
2. Нажмите Кутулс > Раскрывающийся список > Цветной раскрывающийся список.
3. в Цветной раскрывающийся список диалоговое окно, сделайте следующее.
Советы: Если вы хотите выделить строки на основе выбора в раскрывающемся списке, выберите Строка диапазона данных вариант в Обращаться к раздел, а затем выберите строки, которые вы выделите в Выделить строки пунктом.
Теперь выпадающие списки имеют цветовую кодировку, как показано на скриншотах ниже.
Выделение ячеек в раскрывающемся списке
Выделение строк на основе выбора раскрывающегося списка
Создать зависимый выпадающий список в Excel или листе Google
Зависимый раскрывающийся список помогает отображать варианты выбора в зависимости от значения, выбранного в первом раскрывающемся списке. Если вам нужно создать зависимый (каскадный) раскрывающийся список на листе Excel или в листе Google, методы в этом разделе могут оказать вам услугу.
Создать зависимый раскрывающийся список на листе Excel
В приведенной ниже демонстрации отображается зависимый раскрывающийся список на листе Excel.
Пожалуйста, нажмите Как создать зависимый каскадный выпадающий список в Excel? для получения пошагового руководства.
Создайте зависимый раскрывающийся список в листе Google
Если вы хотите создать зависимый раскрывающийся список в листе Google, см. Как создать зависимый выпадающий список в листе Google?
Создание раскрывающихся списков с возможностью поиска
Для раскрывающихся списков, содержащих длинный список элементов на листе, вам нелегко выбрать определенный элемент из списка. Если вы помните начальные символы или несколько последовательных символов элемента, вы можете воспользоваться функцией поиска в раскрывающемся списке, чтобы легко отфильтровать его. В этом разделе будет показано, как создать раскрывающийся список с возможностью поиска в Excel.
Предположим, что исходные данные вы хотите создать раскрывающийся список на основе местоположений в столбце A Sheet1, как показано на скриншоте ниже. Чтобы создать раскрывающийся список с возможностью поиска в Excel с этими данными, сделайте следующее.
1. Во-первых, создайте вспомогательный столбец рядом со списком исходных данных с формулой массива.
В этом случае я выбираю ячейку B2, ввожу в нее приведенную ниже формулу и затем нажимаю Ctrl + Shift + Enter ключи, чтобы получить первый результат.
=IFERROR(INDEX($A$2:$A$50,SMALL(IFERROR(MATCH(IF(FIND(CELL("contents"),$A$2:$A$50)>0,$A$2:$A$50,""),$A$2:$A$50,0),""),ROW(A1))),"")
Выберите первую ячейку результата и перетащите ее Ручка заполнения полностью вниз, пока не достигнет конца списка.
Внимание: В этой формуле массива $ A $ 2: $ A $ 50 - это диапазон исходных данных, на основе которого вы создадите раскрывающийся список. Пожалуйста, измените его в зависимости от диапазона ваших данных.
2. Нажмите Формулы > Определить имя.
3. в Редактировать имя диалоговое окно, настройте следующим образом.
Теперь вам нужно создать раскрывающийся список на основе именованного диапазона. В этом случае я создам раскрывающийся список с возможностью поиска в Sheet2.
4. Откройте Sheet2, выберите диапазон ячеек для раскрывающегося списка и нажмите Данные > проверка достоверности данных > проверка достоверности данных.
5. в проверка достоверности данных диалоговое окно, сделайте следующее.
6. Щелкните правой кнопкой мыши вкладку листа (Sheet2) и выберите Просмотреть код из контекстного меню.
7. В дебюте Microsoft Visual Basic для приложений Скопируйте приведенный ниже код VBA в редактор кода.
Код VBA: создать раскрывающийся список с возможностью поиска в Excel
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.Calculate
End Sub
8. нажмите другой + Q ключи, чтобы закрыть Microsoft Visual Basic для приложений окно.
Теперь созданы раскрывающиеся списки с возможностью поиска. Если вы хотите подобрать элемент, просто введите один или несколько последовательных символов этого элемента в раскрывающуюся ячейку, щелкните стрелку раскрывающегося списка, и затем элементы, основанные на введенном содержимом, будут перечислены в раскрывающемся списке. Смотрите скриншот:
Внимание: Этот метод чувствителен к регистру.
Создать раскрывающийся список, но отображать разные значения
Предположим, вы создали раскрывающийся список, при выборе элемента из него вы хотите, чтобы в ячейке отображалось что-то еще. Как показано в приведенной ниже демонстрации, вы создали раскрывающийся список на основе списка названий стран, при выборе названия страны из раскрывающегося списка вы хотите отобразить аббревиатуру выбранного названия страны в раскрывающейся ячейке. В этом разделе представлен метод VBA, который поможет вам решить проблему.
1. В правой части исходных данных (столбец названия страны) создайте новый столбец, содержащий сокращенные названия стран, которые вы хотите отобразить в раскрывающейся ячейке.
2. Выберите и список названий стран, и список сокращений, введите название в ФИО поле, а затем нажмите Enter .
3. Выберите ячейки для раскрывающегося списка (здесь я выбираю D2: D8), а затем щелкните Данные > проверка достоверности данных > проверка достоверности данных.
4. в проверка достоверности данных диалоговое окно, настройте следующим образом.
5. После создания раскрывающегося списка щелкните правой кнопкой мыши вкладку листа и выберите Просмотреть код из контекстного меню.
6. В дебюте Microsoft Visual Basic для приложений Скопируйте приведенный ниже код VBA в редактор кода.
Код VBA: показывать разные значения в раскрывающемся списке
Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice 20201027
selectedNa = Target.Value
If Target.Column = 4 Then
selectedNum = Application.VLookup(selectedNa, ActiveSheet.Range("dropdown"), 2, False)
If Not IsError(selectedNum) Then
Target.Value = selectedNum
End If
End If
End Sub
Заметки:
7. нажмите другой + Q ключи, чтобы закрыть Microsoft Visual Basic для приложений окно.
Отныне при выборе названия страны из выпадающего списка в ячейке будет отображаться соответствующая аббревиатура названия выбранной страны.
Создайте раскрывающийся список с флажками
Многие пользователи Excel склонны создавать раскрывающийся список с несколькими флажками, чтобы они могли выбрать несколько элементов из списка, просто установив флажки.
Как показано в демонстрации ниже, при нажатии на ячейку, содержащую раскрывающийся список, появляется список. В списке есть флажок перед каждым элементом. Вы можете установить флажки, чтобы отобразить соответствующие элементы в ячейке.
Если вы хотите создать раскрывающийся список с флажками в Excel, см. Как создать раскрывающийся список с несколькими флажками в Excel?.
Добавить автозаполнение в раскрывающийся список
Если у вас есть раскрывающийся список проверки данных с большими элементами, вам нужно прокрутить список вверх и вниз, чтобы найти нужный, или ввести все слово напрямую в поле списка. Если выпадающий список можно заполнять автоматически при вводе в него первой буквы, все станет проще.
Чтобы сделать раскрывающийся список автозаполнением на листе в Excel, см. Как выполнить автозаполнение при вводе в раскрывающемся списке Excel?.
Фильтровать данные на основе выбора из раскрывающегося списка
В этом разделе будет показано, как применять формулы для создания фильтра раскрывающегося списка для извлечения данных на основе выбора из раскрывающегося списка.
1. Сначала вам нужно создать раскрывающийся список с конкретными значениями, на основе которых вы будете извлекать данные.
Советы: Выполните указанные выше действия, чтобы создать раскрывающийся список в Excel.
Создайте раскрывающийся список с уникальным списком элементов
Если в вашем диапазоне есть дубликаты, и вы не хотите создавать раскрывающийся список с повторением элемента, вы можете создать уникальный список элементов следующим образом.
1) Скопируйте ячейки, которые вы создадите в раскрывающемся списке на основе Ctrl + C ключи, а затем вставьте их в новый диапазон.
2) Выберите ячейки в новом диапазоне, нажмите Данные > Удалить дубликаты.
3). Удалить дубликаты диалогового окна, нажмите OK .
4) Тогда Microsoft Excel всплывает, чтобы сообщить, сколько дубликатов было удалено, нажмите OK.
Теперь вы получаете уникальный список элементов, теперь вы можете создать раскрывающийся список на основе этого уникального списка.
2. Затем вам нужно создать три вспомогательных столбца, как показано ниже.
3. Создайте диапазон на основе исходного диапазона данных для вывода извлеченных данных с помощью следующих формул.
Заметки:
Теперь создается фильтр раскрывающегося списка, вы можете легко извлечь данные из исходного диапазона данных на основе выбора из раскрывающегося списка.
Выберите несколько элементов из раскрывающегося списка
По умолчанию раскрывающийся список позволяет пользователям выбирать только один элемент за раз в ячейке. При повторном выборе элемента в раскрывающемся списке ранее выбранный элемент будет перезаписан. Однако, если вас просят выбрать несколько элементов из раскрывающегося списка и отобразить их все в раскрывающейся ячейке, как показано ниже, как вы можете это сделать?
Чтобы выбрать несколько элементов из раскрывающегося списка в Excel, см. Как создать выпадающий список с множественным выбором или значениями в Excel?. В этом руководстве подробно описаны два метода, которые помогут вам решить проблему.
Установить значение по умолчанию (предварительно выбранное) для раскрывающегося списка
По умолчанию ячейка раскрывающегося списка отображается как пустая, стрелка раскрывающегося списка появляется только при нажатии на ячейку. Как с первого взгляда определить, какие ячейки содержат раскрывающиеся списки на листе?
В этом разделе будет показано, как установить значение по умолчанию (предварительно выбранное) для раскрывающегося списка в Excel. Пожалуйста, сделайте следующее.
Перед применением двух методов ниже необходимо создать раскрывающийся список и выполнить следующие настройки.
1. Выберите ячейки для раскрывающегося списка, щелкните Данные > проверка достоверности данных > проверка достоверности данных.
Советы: Если вы уже создали раскрывающийся список, выберите ячейки, содержащие раскрывающийся список, а затем нажмите Данные > проверка достоверности данных > проверка достоверности данных.
2. в проверка достоверности данных диалоговое окно, настройте следующим образом.
После создания раскрывающегося списка примените один из следующих методов, чтобы установить для них значение по умолчанию.
Установить значение по умолчанию для раскрывающегося списка с формулой
Вы можете применить приведенную ниже формулу, чтобы установить значение по умолчанию для раскрывающегося списка, который вы создали, как показано выше.
1. Выберите ячейку раскрывающегося списка, введите в нее приведенную ниже формулу и нажмите Enter клавиша для отображения значения по умолчанию. Если ячейки раскрывающегося списка идут подряд, вы можете перетащить Ручка заполнения ячейки результата, чтобы применить формулу к другим ячейкам.
= IF (C2 = "", "--Выбрать элемент из списка--")
Заметки:
Установить значение по умолчанию для всех раскрывающихся списков на листе одновременно с кодом VBA
Предположим, что существует множество раскрывающихся списков, расположенных в разных диапазонах на вашем листе, для установки значения по умолчанию для всех из них вам необходимо повторно применять формулу. На это уходит много времени. В этом разделе представлен полезный код VBA, позволяющий установить значение по умолчанию для всех раскрывающихся списков на листе одновременно.
1. Откройте рабочий лист, содержащий раскрывающиеся списки, в которых вы хотите установить значение по умолчанию, нажмите кнопку другой + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.
2. в Microsoft Visual Basic для приложений окна, нажмите Вставить > Модули, а затем вставьте приведенный ниже код VBA в окно кода.
Код VBA: установить значение по умолчанию для всех раскрывающихся списков на листе одновременно
Sub SetDropDownListToDefaultValue()
'Updated by Extendoffice 20201026
Dim xWs As Worksheet
Dim xRg, xFRg As Range
Dim xET: xET = Null
Dim xStr As String
xStr = "- Choose from the list -"
Set xWs = Application.ActiveSheet
Set xRg = xWs.UsedRange.Cells
On Error Resume Next
For Each xFRg In xRg
xET = Null
xET = xFRg.Validation.Type
If Not IsNull(xET) Then
If xFRg.Validation.Type = 3 Then
xFRg.Value = "'" & xStr
End If
End If
Next
End Sub
Заметки: В приведенном выше коде - Выберите из списка - - значение по умолчанию для отображения в ячейке раскрывающегося списка. Вы также можете изменить значение по умолчанию в зависимости от ваших потребностей.
3. нажмите F5 нажмите кнопку, затем появится диалоговое окно "Макросы". Убедитесь, что Дропдаунлисттодефаулт выбрано в Имя макроса поле, а затем щелкните Run кнопку для запуска кода.
Затем указанное значение по умолчанию немедленно заполняется в ячейки раскрывающегося списка.
Увеличить размер шрифта раскрывающегося списка
Обычно раскрывающийся список имеет фиксированный размер шрифта, если размер шрифта настолько мал для чтения, вы можете попробовать следующий метод VBA, чтобы увеличить его.
1. Откройте рабочий лист, содержащий раскрывающиеся списки, размер шрифта которых вы хотите увеличить, щелкните правой кнопкой мыши вкладку листа и выберите Просмотреть код из контекстного меню.
2. в Microsoft Visual Basic для приложений Скопируйте приведенный ниже код VBA в редактор кода.
Код VBA: увеличить размер шрифта в раскрывающихся списках на листе
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'updateby Extendoffice 20201027
On Error GoTo LZoom
Dim xZoom As Long
xZoom = 100
If Target.Validation.Type = xlValidateList Then xZoom = 130
LZoom:
ActiveWindow.Zoom = xZoom
End Sub
Внимание: Вот хЗум = 130 в коде означает, что вы увеличите размер шрифта всех выпадающих списков на текущем листе до 130. Вы можете изменить его по своему усмотрению.
3. нажмите другой + Q ключи, чтобы закрыть Microsoft Visual Basic для приложений окно.
С этого момента при нажатии на раскрывающуюся ячейку уровень масштабирования текущего рабочего листа будет увеличен, щелкните стрелку раскрывающегося списка, вы увидите, что размер шрифта всех раскрывающихся элементов также увеличен.
После выбора элемента из раскрывающегося списка вы можете щелкнуть любую ячейку за пределами раскрывающейся ячейки, чтобы вернуться к исходному уровню масштабирования.
Лучшие инструменты для офисной работы
Улучшите свои навыки работы с Excel с помощью Kutools for Excel и почувствуйте эффективность, как никогда раньше. Kutools for Excel предлагает более 300 расширенных функций для повышения производительности и экономии времени. Нажмите здесь, чтобы получить функцию, которая вам нужна больше всего...
Вкладка Office: интерфейс с вкладками в Office и упрощение работы
- Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
- Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
- Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!