Перейти к содержимому

Выпадающий список в Excel: создание, редактирование, удаление и расширенные операции

Author: Siluvia Last Modified: 2025-06-05

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

 Содержание: [ Скрыть ]

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

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

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

Создать выпадающий список из диапазона ячеек

Далее показаны шаги по созданию выпадающего списка из диапазона ячеек в Excel. Выполните следующие действия:

1. Выделите диапазон ячеек, в который будет добавлен выпадающий список.

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

2. Перейдите на вкладку "Данные" > "Проверка данных" > "Проверка данных".

A screenshot of the Data Validation option in Excel ribbon

3. В диалоговом окне "Проверка данных" на вкладке "Параметры" выполните следующие настройки.

3.1) В списке "Тип данных" выберите "Список";
3.2) В поле "Источник" выберите диапазон ячеек, значения которых будут отображаться в выпадающем списке;
3.3) Нажмите кнопку "ОК".

A screenshot showing the Settings tab in the Data Validation dialog box with List selected

Примечания:

1) В зависимости от того, как вы хотите обрабатывать пустые ячейки в выбранном диапазоне, установите или снимите флажок "Игнорировать пустые";
2) Убедитесь, что установлен флажок "Список в ячейке". Если он снят, стрелка выпадающего списка не появится при выборе ячейки.
3) В поле "Источник" можно вручную ввести значения, разделённые запятыми, как показано на скриншоте ниже.

A screenshot showing the Source box in Data Validation with manually typed values for a drop-down list

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

A screenshot of a created drop-down list in Excel

Создать динамический выпадающий список на основе таблицы

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

1. Выделите исходный диапазон данных и нажмите сочетание клавиш "Ctrl" + "T".

2. В появившемся окне "Создание таблицы" нажмите "ОК". Теперь диапазон данных преобразован в таблицу.

A screenshot of the Create Table dialog box in Excel used to convert a range to a table

3. Выделите диапазон ячеек для размещения выпадающего списка, затем перейдите на вкладку "Данные" > "Проверка данных" > "Проверка данных".

4. В диалоговом окне "Проверка данных" выполните следующие действия:

4.1) В списке "Тип данных" выберите "Список";
4.2) В поле "Источник" выберите диапазон таблицы (без заголовка);
4.3) Нажмите кнопку "ОК".

A screenshot of the Data Validation dialog box in Excel showing a table range selected for the drop-down list

Теперь созданы динамические выпадающие списки. При добавлении или удалении данных из диапазона таблицы значения в выпадающем списке будут автоматически обновляться.

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

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

1. Выделите ячейки, в которых будут выводиться выпадающие списки.

2. Перейдите на вкладку "Данные" > "Проверка данных" > "Проверка данных".

3. В диалоговом окне "Проверка данных" выполните следующие настройки.

3.1) В поле "Тип данных" выберите "Список";
3.2) В поле "Источник" введите приведённую ниже формулу;
=OFFSET($A$13,0,0,COUNTA($A$13:$A$24),1)
Примечание: В этой формуле $A$13 — это первая ячейка диапазона данных, а $A$13:$A$24 — диапазон данных, на основе которого вы создаёте выпадающие списки.
3.3) Нажмите кнопку "ОК". См. скриншот:

A screenshot of the Data Validation dialog box in Excel with the OFFSET formula entered for a dynamic drop-down list

Теперь созданы динамические выпадающие списки. При добавлении или удалении данных из указанного диапазона значения в выпадающих списках будут обновляться автоматически.

Создать выпадающий список из именованного диапазона

Вы также можете создать выпадающий список на основе именованного диапазона в Excel.

1. Сначала создайте именованный диапазон. Выделите диапазон ячеек, на основе которого будет создан именованный диапазон, введите имя диапазона в поле "Имя" и нажмите клавишу "Enter".

A screenshot of creating a named range in Excel by entering the range name into the Name box

2. Перейдите на вкладку "Данные" > "Проверка данных" > "Проверка данных".

3. В диалоговом окне "Проверка данных" выполните следующие настройки.

3.1) В поле "Тип данных" выберите "Список";
3.2) Щёлкните по полю "Источник" и нажмите клавишу "F3".
3.3) В диалоговом окне "Вставить имя" выберите только что созданное имя диапазона и нажмите кнопку "ОК";
Совет: Вы также можете вручную ввести "=имя диапазона" в поле "Источник". В данном случае я введу "=City".
3.4) Нажмите "ОК" после возврата к диалоговому окну "Проверка данных". См. скриншот:

A screenshot of the Data Validation dialog box in Excel with a named range selected for the drop-down list

Теперь создан выпадающий список, использующий данные из именованного диапазона.

Создать выпадающий список из другой книги

Допустим, у вас есть книга с именем "SourceData", и вы хотите создать выпадающий список в другой книге на основе данных из этой книги "SourceData". Выполните следующие действия.

1. Откройте книгу "SourceData". В этой книге выделите данные, на основе которых будет создан выпадающий список, введите имя диапазона в поле "Имя" и нажмите клавишу "Enter".

Здесь я назову диапазон City.

A screenshot of defining a range name in Excel for drop-down list data

2. Откройте лист, в который вы хотите вставить выпадающий список. Перейдите на вкладку "Формулы" > "Присвоить имя".

A screenshot of selecting the Define Name option in Excel

3. В диалоговом окне "Новое имя" создайте именованный диапазон на основе имени диапазона, который вы создали в книге "SourceData". Выполните следующие действия.

3.1) Введите имя в поле "Имя";
3.2) В поле "Ссылается на" введите приведённую ниже формулу.
=SourceData.xlsx!City
3.3) Нажмите "ОК" для сохранения

A screenshot of the New Name dialog box in Excel

Примечания:

1). В формуле "SourceData" — это имя книги, содержащей данные для выпадающего списка; "City" — имя диапазона, указанное в книге SourceData.
2). Если в имени исходной книги данных есть пробелы или другие символы, такие как -, # и т.д., заключите имя книги в одинарные кавычки, например: " ='Source Data.xlsx'! City".

4. Откройте книгу, в которую вы хотите вставить выпадающий список, выделите ячейки для выпадающего списка и перейдите на вкладку "Данные" > "Проверка данных" > "Проверка данных".

A screenshot of the Data Validation option in Excel ribbon

5. В диалоговом окне "Проверка данных" выполните следующие настройки.

5.1) В поле "Тип данных" выберите "Список";
5.2) Щёлкните по полю "Источник" и нажмите клавишу "F3".
5.3) В диалоговом окне "Вставить имя" выберите только что созданное имя диапазона и нажмите кнопку "ОК";
Совет: Вы также можете вручную ввести "=имя диапазона" в поле "Источник". В данном случае я введу "=Test".
5.4) Нажмите "ОК" после возврата к диалоговому окну "Проверка данных".

A screenshot of the Paste Name dialog box in Excel to select the range name for a drop-down list

Теперь выпадающие списки добавлены в выбранный диапазон. Значения для выпадающего списка берутся из другой книги.

A screenshot showing a drop-down list in Excel created from data in another workbook

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

Здесь я рекомендую использовать функцию "Создать простой выпадающий список" в Kutools для Excel. С помощью этой функции вы сможете быстро создать выпадающий список на основе конкретных значений ячеек или создать выпадающий список с пользовательскими списками, заранее заданными в Excel.

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

1. Выделите ячейки, в которые хотите вставить выпадающий список, затем перейдите в "Kutools" > "Раскрывающийся список" > "Создать простой выпадающий список".

A screenshot of the Kutools Create simple drop-down list option in Excel ribbon

2. В диалоговом окне "Создать простой выпадающий список" выполните следующие настройки.

3.1) В поле "Применить к" отображается выбранный диапазон. При необходимости вы можете изменить диапазон ячеек;
3.2) В разделе "Источник" выберите "Ввести значение или ссылку на ячейку", если хотите создать выпадающий список на основе диапазона ячеек или вручную ввести значения (через запятую);
3.3) Нажмите "ОК".

A screenshot of the Create simple drop-down list dialog box where you enter a range or values

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

A screenshot of the Create simple drop-down list dialog box with the Custom Lists option selected

Теперь выпадающие списки добавлены в выбранный диапазон.

Kutools для Excel - Усильте Excel более чем 300 необходимыми инструментами. Наслаждайтесь постоянно бесплатными функциями ИИ! Получите прямо сейчас


Редактировать выпадающий список

Если вам нужно отредактировать выпадающий список, используйте методы из этого раздела.

Редактировать выпадающий список на основе диапазона ячеек

Чтобы отредактировать выпадающий список, созданный на основе диапазона ячеек, выполните следующие действия.

1. Выделите ячейки с выпадающим списком, который хотите изменить, затем перейдите на вкладку "Данные" > "Проверка данных" > "Проверка данных".

2. В диалоговом окне "Проверка данных" измените ссылки на ячейки в поле "Источник" и нажмите "ОК".

A screenshot of the Data Validation dialog box in Excel where the Source box is edited to update a drop-down list

Редактировать выпадающий список на основе именованного диапазона

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

1. Перейдите на вкладку "Формулы" > "Диспетчер имен".

Совет: Окно "Диспетчер имен" можно открыть с помощью сочетания клавиш "Ctrl" + "F3".

A screenshot of the Name Manager option in Excel ribbon

2. В окне "Диспетчер имен" выполните следующие действия:

2.1) В поле "Имя" выберите именованный диапазон, который хотите обновить;
2.2) В разделе "Ссылается на" нажмите кнопку Range selection button и выберите обновлённый диапазон для вашего выпадающего списка;
2.3) Нажмите кнопку "Закрыть".

A screenshot of selecting a new range in the Name Manager for a drop-down list update in Excel

3. Затем появится диалоговое окно "Microsoft Excel", нажмите "Да", чтобы сохранить изменения.

A screenshot of the Microsoft Excel dialog box confirming saving changes to the named range for a drop-down list

Теперь выпадающие списки на основе этого именованного диапазона обновлены.


Удалить выпадающий список

В этом разделе рассказывается, как удалить выпадающий список в Excel.

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

В Excel есть встроенная функция для удаления выпадающего списка с листа. Выполните следующие действия.

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

2. Перейдите на вкладку "Данные" > "Проверка данных" > "Проверка данных".

3. В диалоговом окне "Проверка данных" нажмите кнопку "Очистить все", затем нажмите "ОК", чтобы сохранить изменения.

A screenshot showing the Clear All option in the Data Validation dialog box

Теперь выпадающие списки удалены из выбранного диапазона.

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

Kutools для Excel предлагает удобный инструмент — "Очистить ограничения проверки данных", который позволяет быстро удалить выпадающий список из одного или нескольких выбранных диапазонов. Выполните следующие действия.

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

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

2. Перейдите в "Kutools" > "Ограничить ввод" > "Очистить ограничения проверки данных". См. скриншот:

A screenshot of the Kutools for Excel menu with the Clear Data Validation Restrictions option

3. Появится диалоговое окно Kutools для Excel с вопросом, хотите ли вы очистить выпадающий список. Нажмите кнопку "ОК".

A screenshot of the Kutools dialog box asking to confirm the removal of a drop-down list

Теперь выпадающие списки в выбранном диапазоне удалены мгновенно.

Kutools для Excel - Усильте Excel более чем 300 необходимыми инструментами. Наслаждайтесь постоянно бесплатными функциями ИИ! Получите прямо сейчас


Добавить цвет в выпадающий список

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

Добавить цвет в выпадающий список с помощью условного форматирования

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

1. Выделите ячейки с выпадающим списком, которые хотите сделать цветными.

2. Перейдите на вкладку "Главная" > "Условное форматирование" > "Управление правилами".

3. В диалоговом окне "Диспетчер правил условного форматирования" нажмите кнопку "Создать правило".

A screenshot of the Conditional Formatting Rules Manager with the New Rule button highlighted

4. В диалоговом окне "Новое правило форматирования" выполните следующие действия.

4.1) В поле "Тип правила" выберите "Форматировать только ячейки, содержащие";
4.2) В разделе "Форматировать только ячейки с" выберите "Определённый текст" в первом выпадающем списке, "содержащий" — во втором, и выберите первый элемент исходного списка в третьем поле;
Совет: Здесь я выбираю ячейку A16 в третьем поле. A16 — это первый элемент исходного списка, на основе которого создан выпадающий список.
4.3) Нажмите кнопку "Формат".
A screenshot of the New Formatting Rule dialog box with specific text formatting options
4.4) В диалоговом окне "Формат ячеек" перейдите на вкладку "Заливка", выберите цвет фона для указанного текста и нажмите "ОК". При необходимости можно также выбрать цвет шрифта.
A screenshot of the Format Cells dialog box showing the Fill tab with background color selection
4.5) Нажмите "ОК" после возврата к диалоговому окну "Новое правило форматирования".

5. После возврата в "Диспетчер правил условного форматирования" повторите шаги3 и4 для других элементов выпадающего списка. После назначения цветов нажмите "ОК" для сохранения изменений.

A screenshot of the Conditional Formatting Rules Manager after specifying colors for drop-down list items

Теперь при выборе элемента из выпадающего списка ячейка будет выделяться указанным цветом фона в зависимости от выбранного текста.

An animated example showing a drop-down list with color-coded selections in Excel

Легко добавить цвет в выпадающий список с помощью удобного инструмента

Здесь мы рассмотрим функцию "Создать раскрывающийся список с цветом" в Kutools для Excel, которая позволяет легко добавить цвет в выпадающий список.

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

1. Выделите ячейки с выпадающим списком, которым хотите добавить цвет.

2. Перейдите в "Kutools" > "Раскрывающийся список" > "Создать раскрывающийся список с цветом".

A screenshot of the Colored Drop-down List option in the Kutools for Excel menu

3. В диалоговом окне "Создать раскрывающийся список с цветом" выполните следующие действия.

3.1) В разделе "Применить к" выберите опцию "Ячейка";
3.2) В поле "Диапазон данных проверки (последовательность)" отображаются выбранные ссылки на ячейки. При необходимости вы можете изменить диапазон;
3.3) В поле "Элемент списка" (здесь отображаются все элементы выпадающего списка в выбранном диапазоне) выберите элемент, для которого хотите задать цвет;
3.4) В разделе "Выбрать цвет" выберите цвет фона;
Примечание: Повторите шаги3.3 и3.4 для назначения разных цветов другим элементам;
3.5) Нажмите кнопку "ОК". См. скриншот:

A screenshot of the Colored Drop-down List dialog box

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

A screenshot of the option to highlight rows based on drop-down list selection

Теперь выпадающие списки окрашены, как показано на скриншотах ниже.

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

An animated example showing drop-down list items color-coded in Excel

Выделять строки на основе выбора в выпадающем списке

An animated example showing rows highlighted based on drop-down list selection in Excel

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 screenshot showing the helper column with an array formula in Excel

Примечание: В этой формуле массива $A$2:$A$50 — это диапазон исходных данных, на основе которого вы создаёте выпадающий список. Измените его в соответствии с вашим диапазоном.

2. Перейдите на вкладку "Формулы" > "Присвоить имя".

A screenshot of the Define Name dialog box in Excel for creating a named range

3. В диалоговом окне "Изменить имя" выполните следующие действия.

3.1) В поле "Имя" введите имя для именованного диапазона;
3.2) В поле "Ссылается на" введите приведённую ниже формулу;
=OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B$2:$B$50)-COUNTIF(Sheet1!$B$2:$B$50,""),1)
3.3) Нажмите кнопку "ОК". См. скриншот:

A screenshot of the Edit Name dialog box in Excel for defining a named range formula

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

4. Откройте лист Sheet2, выделите диапазон ячеек для выпадающего списка и перейдите на вкладку "Данные" > "Проверка данных" > "Проверка данных".

A screenshot of the Data Validation option in Excel ribbon

5. В диалоговом окне "Проверка данных" выполните следующие действия.

5.1) В поле "Тип данных" выберите "Список";
5.2) Щёлкните по полю "Источник" и нажмите клавишу "F3";
5.3) В появившемся диалоговом окне "Вставить имя" выберите созданный на шаге3 именованный диапазон и нажмите "ОК";
A screenshot of the Paste Name dialog box in Excel showing a named range
Совет: Можно напрямую ввести именованный диапазон как "=имя диапазона" в поле "Источник".
5.4) Перейдите на вкладку "Сообщение об ошибке", снимите флажок "Показывать сообщение об ошибке при вводе недопустимых данных" и нажмите "ОК".
A screenshot of the Error Alert tab in the Data Validation dialog box in Excel

6. Щёлкните правой кнопкой мыши по вкладке листа (Sheet2) и выберите "Просмотреть код" в контекстном меню.

A screenshot showing the option to view the code in the sheet tab in Excel

7. В открывшемся окне "Microsoft Visual Basic for Applications" вставьте приведённый ниже код VBA в редактор кода.

Код VBA: создать выпадающий список с поиском в Excel

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.Calculate
End Sub

A screenshot of the Microsoft Visual Basic for Applications editor in Excel with VBA code

8. Нажмите "Alt" + "Q", чтобы закрыть окно "Microsoft Visual Basic for Applications".

Теперь выпадающие списки с поиском созданы. Чтобы выбрать элемент, введите одну или несколько последовательных букв этого элемента в ячейку с выпадающим списком, щёлкните по стрелке списка — и в выпадающем списке отобразятся элементы, соответствующие введённому тексту. См. скриншот:

A screenshot of a searchable drop-down list in Excel with items filtered by entering characters

Примечание: Этот способ чувствителен к регистру.


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

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

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

A screenshot of country name and abbreviation columns in Excel

2. Выделите оба списка — с названиями стран и с аббревиатурами, введите имя в поле "Имя" и нажмите клавишу "Enter".

A screenshot of the Name box in Excel used to define a range

3. Выделите ячейки для выпадающего списка (например, D2:D8) и перейдите на вкладку "Данные" > "Проверка данных" > "Проверка данных".

A screenshot of the Data Validation option in Excel ribbon

4. В диалоговом окне "Проверка данных" выполните следующие действия.

4.1) В поле "Тип данных" выберите "Список";
4.2) В поле "Источник" выберите диапазон исходных данных (в данном случае — список стран);
4.3) Нажмите "ОК".

A screenshot of the Data Validation configuration for drop-down list in Excel

5. После создания выпадающего списка щёлкните правой кнопкой мыши по вкладке листа и выберите "Просмотреть код" в контекстном меню.

A screenshot of the View Code option in Excel's sheet tab

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

Примечания:

1) В коде число4 в строке "If Target.Column =4" означает номер столбца, в котором находится выпадающий список, созданный на шагах3 и4. Если ваш список находится в столбце F, замените4 на6;
2) "dropdown" в пятой строке — это имя диапазона, созданное на шаге2. При необходимости измените его.

7. Нажмите "Alt" + "Q", чтобы закрыть окно "Microsoft Visual Basic for Applications".

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

A screenshot showing a drop-down list with country names selected and abbreviations displayed


Создать выпадающий список с флажками

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

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

Если вы хотите создать выпадающий список с флажками в Excel, ознакомьтесь с инструкцией Как создать выпадающий список с несколькими флажками в Excel?.


Добавить автозаполнение в выпадающий список

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

Чтобы сделать выпадающий список с автозаполнением в Excel, ознакомьтесь с инструкцией Как включить автозаполнение при вводе в выпадающем списке Excel?.


Фильтровать данные на основе выбора в выпадающем списке

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

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

Совет: Следуйте вышеописанным шагам для создания выпадающего списка в Excel.

Создать выпадающий список с уникальными элементами

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

1) Скопируйте ячейки, на основе которых будет создан выпадающий список, с помощью "Ctrl" + "C" и вставьте их в новый диапазон.

2) Выделите ячейки в новом диапазоне, перейдите на вкладку "Данные" > "Удалить дубликаты".

A screenshot of the Remove Duplicates option in Excel ribbon

3) В диалоговом окне "Удалить дубликаты" нажмите кнопку "ОК".

A screenshot of the 'Remove Duplicates' dialog box in Excel

4) Появится окно Microsoft Excel с информацией о количестве удалённых дубликатов, нажмите "ОК".

A screenshot of a drop-down list filter in Excel displaying data based on selection

Теперь у вас есть уникальный список элементов, и вы можете создать на его основе выпадающий список.

2. Затем создайте три вспомогательных столбца, как показано ниже.

2.1) Для первого вспомогательного столбца (например, столбец D) введите приведённую ниже формулу в первую ячейку (не считая заголовка), затем нажмите "Enter". Выделите ячейку с результатом и протяните маркер заполнения вниз до конца диапазона.
=ROWS($A$2:A2)
A screenshot of the first helper column formula in Excel for a drop-down list filter
2.2) Для второго вспомогательного столбца (столбец E) введите формулу в ячейку E2, нажмите "Enter", выделите E2 и протяните маркер заполнения вниз до конца диапазона.
Примечание: Если в выпадающем списке не выбрано значение, формулы будут возвращать пустые значения.
=IF(A2=$H$2,D2,"")
A screenshot of the second helper column formula in Excel for a drop-down list filter
2.3) Для третьего вспомогательного столбца (столбец F) введите формулу в F2, нажмите "Enter", выделите F2 и протяните маркер заполнения вниз до конца диапазона.
Примечание: Если в выпадающем списке не выбрано значение, формулы будут возвращать пустые значения.
=IFERROR(SMALL($E$2:$E$17,D2),"")
A screenshot of the third helper column formula in Excel for a drop-down list filter

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

3.1) Выделите первую ячейку для вывода результата (например, J2), введите формулу и нажмите "Enter".
=IFERROR(INDEX($A$2:$C$17,$F2,COLUMNS($J$2:J2)),"")
3.2) Выделите ячейку с результатом и протяните маркер заполнения вправо на две ячейки.
A screenshot of the first output cell formula in Excel for data extraction based on drop-down list selection
3.3) Оставив диапазон J2:L2 выделенным, протяните маркер заполнения вниз до конца диапазона.
A screenshot of Excel's Fill Handle used to extend formulas for drop-down list filtering

Примечания:

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

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

A screenshot of a drop-down list filter in Excel displaying data based on selection


Выбрать несколько элементов из выпадающего списка

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

Чтобы выбрать несколько элементов из выпадающего списка в Excel, ознакомьтесь с инструкцией Как создать выпадающий список с несколькими выборами или значениями в Excel?. В этом руководстве подробно описаны два способа решения задачи.


Установить значение по умолчанию для выпадающего списка

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

В этом разделе показано, как установить значение по умолчанию для выпадающего списка в Excel. Выполните следующие действия.

Перед применением двух способов ниже создайте выпадающий список и выполните следующие настройки.

1. Выделите ячейки для выпадающего списка, перейдите на вкладку "Данные" > "Проверка данных" > "Проверка данных".

Совет: Если вы уже создали выпадающий список, выделите ячейки с ним и перейдите на вкладку "Данные" > "Проверка данных" > "Проверка данных".

A screenshot of the Data Validation option in Excel ribbon

2. В диалоговом окне "Проверка данных" выполните следующие действия.

2.1) В поле "Тип данных" выберите "Список";
2.2) В поле "Источник" выберите исходные данные для выпадающего списка.
Совет: Для уже созданного выпадающего списка эти два шага можно пропустить.
A screenshot of the 'Data Validation' dialog box in Excel showing the 'Allow List' option
2.3) Перейдите на вкладку "Сообщение об ошибке", снимите флажок "Показывать сообщение об ошибке при вводе недопустимых данных";
2.4) Нажмите кнопку "ОК".
A screenshot of the 'Error Alert' tab in Excel's 'Data Validation' dialog box

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

Установить значение по умолчанию для выпадающего списка с помощью формулы

Вы можете использовать приведённую ниже формулу для установки значения по умолчанию в выпадающем списке, как показано выше.

1. Выделите ячейку с выпадающим списком, введите формулу и нажмите "Enter", чтобы отобразить значение по умолчанию. Если ячейки идут подряд, протяните маркер заполнения для применения формулы к другим ячейкам.

=IF(C2="", "--Choose item from the list--")

A screenshot of a formula applied to set a default value in a drop-down list in Excel

Примечания:

1) В формуле "C2" — это пустая ячейка рядом с ячейкой выпадающего списка, вы можете указать любую другую пустую ячейку.
2) "--Выберите элемент из списка--" — это значение по умолчанию, отображаемое в ячейке выпадающего списка. Вы можете изменить его по своему усмотрению.
3) Формула работает только до выбора элемента из списка. После выбора значение по умолчанию будет заменено, а формула удалена.
Установить значение по умолчанию для всех выпадающих списков на листе сразу с помощью 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

A screenshot showing the Microsoft Visual Basic for Applications window with VBA code pasted into a module

Примечание: В приведённом выше коде "- Выберите из списка -" — это значение по умолчанию, отображаемое в ячейке выпадающего списка. Вы можете изменить его по своему усмотрению.

3. Нажмите клавишу "F5", появится окно "Макросы", убедитесь, что в поле "Имя макроса" выбран "DropDownListToDefault", затем нажмите кнопку "Выполнить" для запуска кода.

A screenshot of the Macros dialog box in Excel with the 'DropDownListToDefault' macro selected

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

A screenshot showing the default value populated into the drop-down list cells in Excel


Увеличить размер шрифта выпадающего списка

Обычно выпадающий список имеет фиксированный размер шрифта. Если шрифт слишком мелкий, попробуйте увеличить его с помощью VBA.

1. Откройте лист с выпадающими списками, размер шрифта которых нужно увеличить, щёлкните правой кнопкой мыши по вкладке листа и выберите "Просмотреть код" в контекстном меню.

A screenshot showing the 'View Code' option in Excel's sheet tab menu

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

A screenshot showing the Microsoft Visual Basic for Applications window with VBA code for enlarging drop-down list font size

Примечание: В коде "xZoom =130" означает, что масштаб всех выпадающих списков на текущем листе будет увеличен до130. При необходимости измените это значение.

3. Нажмите "Alt" + "Q", чтобы закрыть окно "Microsoft Visual Basic for Applications".

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

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

A screenshot of a zoomed-in drop-down list in Excel with enlarged font size

Лучшие инструменты для повышения продуктивности работы с Office

🤖 Kutools AI Aide: Совершенно новый подход к анализу данных на основе: Интеллектуальное выполнение |  Генерация кода  |  Создание пользовательских формул |  Анализ данных и построение диаграмм  |  Вызов Расширенных функций Kutools
Популярные функции: Найти, выделить или отметить дубликаты | Удалить пустые строки | Объединить столбцы или ячейки без потери данных |   Округлить без формулы ...
Супер ПОИСК: VLOOKUP по нескольким критериям | VLOOKUP с несколькими значениями | Многолистовой поиск | Распознавание нечетких соответствий ....
Расширенный раскрывающийся список: Быстро создать раскрывающийся список | Зависимый раскрывающийся список | Множественный выбор в раскрывающемся списке ....
Менеджер столбцов: Добавить определённое количество столбцов | Переместить столбцы | Переключить видимость скрытых столбцов | Сравнить диапазоны и столбцы ...
Избранные функции: Сетка фокусировки | Дизайн листа | Улучшенная строка формулы | Управление книгами и листами | Библиотека автотекста | Выбор даты | Объединить данные | Шифрование/расшифровка ячеек | Отправить письмо по списку | Супер фильтр | Специальный фильтр (фильтр ячеек с жирным/курсивом/зачёркнутым...) ...
Топ-15 наборов инструментов:12 текстовых инструментов (Добавить текст, Удалить определенные символы, ...) |50+ типов диаграмм (Диаграмма Ганта, ...) |40+ практических формул (Расчет возраста на основе даты рождения, ...) |19 инструментов вставки (Вставить QR-код, Вставить изображение по пути, ...) |12 инструментов преобразования (Преобразовать в слова, Конвертация валюты, ...) |7 инструментов для объединения и разделения (Расширенное объединение строк, Разделить ячейки, ...) | ... и многое другое

Ускорьте работу в Excel с Kutools для Excel и ощутите новую степень эффективности. Kutools для Excel предлагает более300 расширенных функций для повышения продуктивности и экономии времени. Нажмите здесь, чтобы выбрать нужную вам функцию...


Office Tab добавляет вкладки в Office и делает вашу работу намного проще

  • Включите редактирование и чтение с вкладками в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
  • Открывайте и создавайте несколько документов во вкладках одного окна, а не в отдельных окнах.
  • Увеличьте свою продуктивность на50% и сократите сотни лишних кликов мышью каждый день!