Выпадающий список в Excel: создание, редактирование, удаление и расширенные операции
Выпадающий список похож на список, который позволяет пользователям выбрать одно значение из предложенного перечня. В этом руководстве показаны основные операции с выпадающими списками: создание, редактирование и удаление выпадающего списка в Excel. Кроме того, вы узнаете о расширенных возможностях работы с выпадающими списками для решения различных задач в Excel.
Содержание: [ Скрыть ]
Создать простой выпадающий список
Чтобы использовать выпадающий список, сначала нужно научиться его создавать. В этом разделе представлены6 способов создания выпадающего списка в Excel.
Создать выпадающий список из диапазона ячеек
Далее показаны шаги по созданию выпадающего списка из диапазона ячеек в Excel. Выполните следующие действия:
1. Выделите диапазон ячеек, в который будет добавлен выпадающий список.
Совет: Вы можете создать выпадающий список сразу для нескольких несмежных ячеек, удерживая клавишу "Ctrl" и поочередно выбирая нужные ячейки.
2. Перейдите на вкладку "Данные" > "Проверка данных" > "Проверка данных".
3. В диалоговом окне "Проверка данных" на вкладке "Параметры" выполните следующие настройки.
Примечания:
Теперь выпадающий список создан. При нажатии на ячейку со списком появится стрелка, щёлкните по ней, чтобы развернуть список и выбрать нужный элемент.
Создать динамический выпадающий список на основе таблицы
Вы можете преобразовать свой диапазон данных в таблицу Excel и затем создать динамический выпадающий список на основе диапазона таблицы.
1. Выделите исходный диапазон данных и нажмите сочетание клавиш "Ctrl" + "T".
2. В появившемся окне "Создание таблицы" нажмите "ОК". Теперь диапазон данных преобразован в таблицу.
3. Выделите диапазон ячеек для размещения выпадающего списка, затем перейдите на вкладку "Данные" > "Проверка данных" > "Проверка данных".
4. В диалоговом окне "Проверка данных" выполните следующие действия:
Теперь созданы динамические выпадающие списки. При добавлении или удалении данных из диапазона таблицы значения в выпадающем списке будут автоматически обновляться.
Создать динамический выпадающий список с помощью формул
Помимо создания динамического выпадающего списка на основе диапазона таблицы, вы также можете использовать формулу для создания динамического выпадающего списка в Excel.
1. Выделите ячейки, в которых будут выводиться выпадающие списки.
2. Перейдите на вкладку "Данные" > "Проверка данных" > "Проверка данных".
3. В диалоговом окне "Проверка данных" выполните следующие настройки.
=OFFSET($A$13,0,0,COUNTA($A$13:$A$24),1)
Теперь созданы динамические выпадающие списки. При добавлении или удалении данных из указанного диапазона значения в выпадающих списках будут обновляться автоматически.
Создать выпадающий список из именованного диапазона
Вы также можете создать выпадающий список на основе именованного диапазона в Excel.
1. Сначала создайте именованный диапазон. Выделите диапазон ячеек, на основе которого будет создан именованный диапазон, введите имя диапазона в поле "Имя" и нажмите клавишу "Enter".
2. Перейдите на вкладку "Данные" > "Проверка данных" > "Проверка данных".
3. В диалоговом окне "Проверка данных" выполните следующие настройки.
Теперь создан выпадающий список, использующий данные из именованного диапазона.
Создать выпадающий список из другой книги
Допустим, у вас есть книга с именем "SourceData", и вы хотите создать выпадающий список в другой книге на основе данных из этой книги "SourceData". Выполните следующие действия.
1. Откройте книгу "SourceData". В этой книге выделите данные, на основе которых будет создан выпадающий список, введите имя диапазона в поле "Имя" и нажмите клавишу "Enter".
Здесь я назову диапазон City.
2. Откройте лист, в который вы хотите вставить выпадающий список. Перейдите на вкладку "Формулы" > "Присвоить имя".
3. В диалоговом окне "Новое имя" создайте именованный диапазон на основе имени диапазона, который вы создали в книге "SourceData". Выполните следующие действия.
=SourceData.xlsx!City
Примечания:
4. Откройте книгу, в которую вы хотите вставить выпадающий список, выделите ячейки для выпадающего списка и перейдите на вкладку "Данные" > "Проверка данных" > "Проверка данных".
5. В диалоговом окне "Проверка данных" выполните следующие настройки.
Теперь выпадающие списки добавлены в выбранный диапазон. Значения для выпадающего списка берутся из другой книги.
Легко создать выпадающий список с помощью удобного инструмента
Здесь я рекомендую использовать функцию "Создать простой выпадающий список" в Kutools для Excel. С помощью этой функции вы сможете быстро создать выпадающий список на основе конкретных значений ячеек или создать выпадающий список с пользовательскими списками, заранее заданными в Excel.
1. Выделите ячейки, в которые хотите вставить выпадающий список, затем перейдите в "Kutools" > "Раскрывающийся список" > "Создать простой выпадающий список".
2. В диалоговом окне "Создать простой выпадающий список" выполните следующие настройки.
Примечание: Если вы хотите создать выпадающий список на основе пользовательского списка, заданного в Excel, выберите опцию "Пользовательский список" в разделе "Источник", выберите нужный список и нажмите "ОК".
Теперь выпадающие списки добавлены в выбранный диапазон.
Kutools для Excel - Усильте Excel более чем 300 необходимыми инструментами. Наслаждайтесь постоянно бесплатными функциями ИИ! Получите прямо сейчас
Редактировать выпадающий список
Если вам нужно отредактировать выпадающий список, используйте методы из этого раздела.
Редактировать выпадающий список на основе диапазона ячеек
Чтобы отредактировать выпадающий список, созданный на основе диапазона ячеек, выполните следующие действия.
1. Выделите ячейки с выпадающим списком, который хотите изменить, затем перейдите на вкладку "Данные" > "Проверка данных" > "Проверка данных".
2. В диалоговом окне "Проверка данных" измените ссылки на ячейки в поле "Источник" и нажмите "ОК".
Редактировать выпадающий список на основе именованного диапазона
Если вы добавили или удалили значения в именованном диапазоне, а выпадающий список создан на его основе, чтобы обновить значения в выпадающем списке, выполните следующие действия.
1. Перейдите на вкладку "Формулы" > "Диспетчер имен".
Совет: Окно "Диспетчер имен" можно открыть с помощью сочетания клавиш "Ctrl" + "F3".
2. В окне "Диспетчер имен" выполните следующие действия:

3. Затем появится диалоговое окно "Microsoft Excel", нажмите "Да", чтобы сохранить изменения.
Теперь выпадающие списки на основе этого именованного диапазона обновлены.
Удалить выпадающий список
В этом разделе рассказывается, как удалить выпадающий список в Excel.
Удалить выпадающий список с помощью стандартной функции Excel
В Excel есть встроенная функция для удаления выпадающего списка с листа. Выполните следующие действия.
1. Выделите диапазон ячеек с выпадающим списком, который хотите удалить.
2. Перейдите на вкладку "Данные" > "Проверка данных" > "Проверка данных".
3. В диалоговом окне "Проверка данных" нажмите кнопку "Очистить все", затем нажмите "ОК", чтобы сохранить изменения.
Теперь выпадающие списки удалены из выбранного диапазона.
Легко удалить выпадающие списки с помощью удобного инструмента
Kutools для Excel предлагает удобный инструмент — "Очистить ограничения проверки данных", который позволяет быстро удалить выпадающий список из одного или нескольких выбранных диапазонов. Выполните следующие действия.
1. Выделите диапазон ячеек с выпадающим списком, который хотите удалить.
2. Перейдите в "Kutools" > "Ограничить ввод" > "Очистить ограничения проверки данных". См. скриншот:
3. Появится диалоговое окно Kutools для Excel с вопросом, хотите ли вы очистить выпадающий список. Нажмите кнопку "ОК".
Теперь выпадающие списки в выбранном диапазоне удалены мгновенно.
Kutools для Excel - Усильте Excel более чем 300 необходимыми инструментами. Наслаждайтесь постоянно бесплатными функциями ИИ! Получите прямо сейчас
Добавить цвет в выпадающий список
В некоторых случаях может понадобиться сделать выпадающий список цветным, чтобы быстро различать данные в ячейках со списком. В этом разделе представлены два способа решения этой задачи.
Добавить цвет в выпадающий список с помощью условного форматирования
Вы можете создать условные правила для ячеек с выпадающим списком, чтобы они автоматически окрашивались. Выполните следующие действия.
1. Выделите ячейки с выпадающим списком, которые хотите сделать цветными.
2. Перейдите на вкладку "Главная" > "Условное форматирование" > "Управление правилами".
3. В диалоговом окне "Диспетчер правил условного форматирования" нажмите кнопку "Создать правило".
4. В диалоговом окне "Новое правило форматирования" выполните следующие действия.


5. После возврата в "Диспетчер правил условного форматирования" повторите шаги3 и4 для других элементов выпадающего списка. После назначения цветов нажмите "ОК" для сохранения изменений.
Теперь при выборе элемента из выпадающего списка ячейка будет выделяться указанным цветом фона в зависимости от выбранного текста.
Легко добавить цвет в выпадающий список с помощью удобного инструмента
Здесь мы рассмотрим функцию "Создать раскрывающийся список с цветом" в Kutools для Excel, которая позволяет легко добавить цвет в выпадающий список.
1. Выделите ячейки с выпадающим списком, которым хотите добавить цвет.
2. Перейдите в "Kutools" > "Раскрывающийся список" > "Создать раскрывающийся список с цветом".
3. В диалоговом окне "Создать раскрывающийся список с цветом" выполните следующие действия.
Совет: Если вы хотите выделять строки на основе выбора в выпадающем списке, выберите опцию "Вся строка" в разделе "Применить к" и затем укажите строки для выделения в поле "Выделенный диапазон строк".
Теперь выпадающие списки окрашены, как показано на скриншотах ниже.
Выделять ячейки на основе выбора в выпадающем списке
Выделять строки на основе выбора в выпадающем списке
Kutools для Excel - Усильте Excel более чем 300 необходимыми инструментами. Наслаждайтесь постоянно бесплатными функциями ИИ! Получите прямо сейчас
Создать зависимый выпадающий список в 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. В диалоговом окне "Изменить имя" выполните следующие действия.
=OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B$2:$B$50)-COUNTIF(Sheet1!$B$2:$B$50,""),1)
Теперь нужно создать выпадающий список на основе именованного диапазона. В данном случае я создам выпадающий список с поиском на листе Sheet2.
4. Откройте лист Sheet2, выделите диапазон ячеек для выпадающего списка и перейдите на вкладку "Данные" > "Проверка данных" > "Проверка данных".
5. В диалоговом окне "Проверка данных" выполните следующие действия.


6. Щёлкните правой кнопкой мыши по вкладке листа (Sheet2) и выберите "Просмотреть код" в контекстном меню.
7. В открывшемся окне "Microsoft Visual Basic for Applications" вставьте приведённый ниже код VBA в редактор кода.
Код VBA: создать выпадающий список с поиском в Excel
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.Calculate
End Sub
8. Нажмите "Alt" + "Q", чтобы закрыть окно "Microsoft Visual Basic for Applications".
Теперь выпадающие списки с поиском созданы. Чтобы выбрать элемент, введите одну или несколько последовательных букв этого элемента в ячейку с выпадающим списком, щёлкните по стрелке списка — и в выпадающем списке отобразятся элементы, соответствующие введённому тексту. См. скриншот:
Примечание: Этот способ чувствителен к регистру.
Создать выпадающий список с отображением других значений
Допустим, вы создали выпадающий список, и при выборе элемента хотите, чтобы в ячейке отображалось другое значение. Как показано в примере ниже: вы создали выпадающий список на основе списка стран, а при выборе страны хотите, чтобы в ячейке отображалась аббревиатура этой страны. В этом разделе представлен способ с использованием VBA для решения задачи.
1. Справа от исходных данных (столбец с названиями стран) создайте новый столбец с аббревиатурами стран, которые должны отображаться в ячейке выпадающего списка.
2. Выделите оба списка — с названиями стран и с аббревиатурами, введите имя в поле "Имя" и нажмите клавишу "Enter".
3. Выделите ячейки для выпадающего списка (например, D2:D8) и перейдите на вкладку "Данные" > "Проверка данных" > "Проверка данных".
4. В диалоговом окне "Проверка данных" выполните следующие действия.
5. После создания выпадающего списка щёлкните правой кнопкой мыши по вкладке листа и выберите "Просмотреть код" в контекстном меню.
6. В открывшемся окне "Microsoft Visual Basic for Applications" вставьте приведённый ниже код 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. Нажмите "Alt" + "Q", чтобы закрыть окно "Microsoft Visual Basic for Applications".
Теперь при выборе страны из выпадающего списка в ячейке будет отображаться соответствующая аббревиатура выбранной страны.
Создать выпадающий список с флажками
Многие пользователи Excel хотят создать выпадающий список с несколькими флажками, чтобы можно было выбрать несколько элементов из списка, просто установив соответствующие флажки.
Как показано на примере ниже, при нажатии на ячейку с выпадающим списком появляется список, в котором перед каждым элементом стоит флажок. Вы можете отметить нужные флажки, чтобы отобразить соответствующие элементы в ячейке.
Если вы хотите создать выпадающий список с флажками в Excel, ознакомьтесь с инструкцией Как создать выпадающий список с несколькими флажками в Excel?.
Добавить автозаполнение в выпадающий список
Если в выпадающем списке проверки данных много элементов, приходится прокручивать список или вводить слово полностью. Если выпадающий список будет автоматически подбирать варианты при вводе первой буквы, это значительно упростит работу.
Чтобы сделать выпадающий список с автозаполнением в Excel, ознакомьтесь с инструкцией Как включить автозаполнение при вводе в выпадающем списке Excel?.
Фильтровать данные на основе выбора в выпадающем списке
В этом разделе показано, как с помощью формул создать фильтр по выпадающему списку для извлечения данных на основе выбранного значения.
1. Сначала создайте выпадающий список с конкретными значениями, по которым будут извлекаться данные.
Совет: Следуйте вышеописанным шагам для создания выпадающего списка в Excel.
Создать выпадающий список с уникальными элементами
Если в вашем диапазоне есть дубликаты, и вы не хотите создавать выпадающий список с повторяющимися элементами, выполните следующие действия для создания уникального списка.
1) Скопируйте ячейки, на основе которых будет создан выпадающий список, с помощью "Ctrl" + "C" и вставьте их в новый диапазон.
2) Выделите ячейки в новом диапазоне, перейдите на вкладку "Данные" > "Удалить дубликаты".
3) В диалоговом окне "Удалить дубликаты" нажмите кнопку "ОК".
4) Появится окно Microsoft Excel с информацией о количестве удалённых дубликатов, нажмите "ОК".
Теперь у вас есть уникальный список элементов, и вы можете создать на его основе выпадающий список.
2. Затем создайте три вспомогательных столбца, как показано ниже.
=ROWS($A$2:A2)

=IF(A2=$H$2,D2,"")

=IFERROR(SMALL($E$2:$E$17,D2),"")

3. Создайте диапазон на основе исходного диапазона данных для вывода извлечённых данных с помощью приведённых ниже формул.
=IFERROR(INDEX($A$2:$C$17,$F2,COLUMNS($J$2:J2)),"")


Примечания:
Теперь фильтр по выпадающему списку создан, и вы можете легко извлекать данные из исходного диапазона на основе выбранного значения.
Выбрать несколько элементов из выпадающего списка
По умолчанию выпадающий список позволяет выбрать только один элемент в ячейке. При повторном выборе элемент заменяет предыдущий. Однако если требуется выбрать несколько элементов и отобразить их все в ячейке, как показано ниже, как это реализовать?
Чтобы выбрать несколько элементов из выпадающего списка в Excel, ознакомьтесь с инструкцией Как создать выпадающий список с несколькими выборами или значениями в Excel?. В этом руководстве подробно описаны два способа решения задачи.
Установить значение по умолчанию для выпадающего списка
По умолчанию ячейка с выпадающим списком отображается пустой, а стрелка появляется только при клике по ячейке. Как быстро определить, какие ячейки содержат выпадающие списки на листе?
В этом разделе показано, как установить значение по умолчанию для выпадающего списка в Excel. Выполните следующие действия.
Перед применением двух способов ниже создайте выпадающий список и выполните следующие настройки.
1. Выделите ячейки для выпадающего списка, перейдите на вкладку "Данные" > "Проверка данных" > "Проверка данных".
Совет: Если вы уже создали выпадающий список, выделите ячейки с ним и перейдите на вкладку "Данные" > "Проверка данных" > "Проверка данных".
2. В диалоговом окне "Проверка данных" выполните следующие действия.


После создания выпадающего списка воспользуйтесь одним из способов ниже для установки значения по умолчанию.
Установить значение по умолчанию для выпадающего списка с помощью формулы
Вы можете использовать приведённую ниже формулу для установки значения по умолчанию в выпадающем списке, как показано выше.
1. Выделите ячейку с выпадающим списком, введите формулу и нажмите "Enter", чтобы отобразить значение по умолчанию. Если ячейки идут подряд, протяните маркер заполнения для применения формулы к другим ячейкам.
=IF(C2="", "--Choose item from the list--")
Примечания:
Установить значение по умолчанию для всех выпадающих списков на листе сразу с помощью VBA
Если на листе много выпадающих списков в разных диапазонах, для установки значения по умолчанию для всех потребуется многократно применять формулу. Это неудобно. В этом разделе представлен полезный код VBA для массовой установки значения по умолчанию для всех выпадающих списков на листе.
1. Откройте лист с выпадающими списками, для которых нужно установить значение по умолчанию, нажмите "Alt" + "F11" для открытия окна "Microsoft Visual Basic for Applications".
2. В окне "Microsoft Visual Basic for Applications" выберите "Вставка" > "Модуль" и вставьте приведённый ниже код 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", появится окно "Макросы", убедитесь, что в поле "Имя макроса" выбран "DropDownListToDefault", затем нажмите кнопку "Выполнить" для запуска кода.
Теперь указанное значение по умолчанию сразу подставляется во все ячейки с выпадающими списками.
Увеличить размер шрифта выпадающего списка
Обычно выпадающий список имеет фиксированный размер шрифта. Если шрифт слишком мелкий, попробуйте увеличить его с помощью VBA.
1. Откройте лист с выпадающими списками, размер шрифта которых нужно увеличить, щёлкните правой кнопкой мыши по вкладке листа и выберите "Просмотреть код" в контекстном меню.
2. В окне "Microsoft Visual Basic for Applications" вставьте приведённый ниже код 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
Примечание: В коде "xZoom =130" означает, что масштаб всех выпадающих списков на текущем листе будет увеличен до130. При необходимости измените это значение.
3. Нажмите "Alt" + "Q", чтобы закрыть окно "Microsoft Visual Basic for Applications".
Теперь при нажатии на ячейку с выпадающим списком масштаб текущего листа увеличивается, и при открытии списка размер шрифта всех элементов также становится больше.
После выбора элемента из выпадающего списка щёлкните по любой другой ячейке, чтобы вернуть исходный масштаб листа.
Лучшие инструменты для повышения продуктивности работы с Office
Ускорьте работу в Excel с Kutools для Excel и ощутите новую степень эффективности. Kutools для Excel предлагает более300 расширенных функций для повышения продуктивности и экономии времени. Нажмите здесь, чтобы выбрать нужную вам функцию...
Office Tab добавляет вкладки в Office и делает вашу работу намного проще
- Включите редактирование и чтение с вкладками в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна, а не в отдельных окнах.
- Увеличьте свою продуктивность на50% и сократите сотни лишних кликов мышью каждый день!