Перейти к основному содержанию
 

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

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

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

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

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

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

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

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

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

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

2. Нажмите Данные > проверка достоверности данных > проверка достоверности данных.

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

3.1). Разрешить выпадающий список, выберите список;
3.2). Источник поле выберите диапазон ячеек, значения которого вы будете отображать в раскрывающемся списке;
3.3) Нажмите OK .

Заметки:

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

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

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

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

1. Выберите исходный диапазон данных, а затем нажмите Ctrl + T ключи.

2. Нажмите OK в появлении Создать таблицу диалоговое окно. Затем диапазон данных преобразуется в таблицу.

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

4. в проверка достоверности данных диалоговое окно, вам необходимо:

4.1) Выбрать Список в Разрешить раскрывающийся список;
4.2) Выберите диапазон таблицы (без заголовка) в Источник коробка;
4.3) Нажмите OK .

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

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

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

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

2. Нажмите Данные > проверка достоверности данных > проверка достоверности данных.

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

3.1). Разрешить выберите Список;
3.2). Источник поле введите в него формулу ниже;
= СМЕЩЕНИЕ (13,0,0 австралийских долларов; COUNTA (13 австралийских долларов: 24 австралийских доллара), 1)
Внимание: В этой формуле $ A $ 13 - первая ячейка диапазона данных, а $ A $ 13: $ A $ 24 - диапазон данных, на основе которого вы создадите раскрывающиеся списки.
3.3) Нажмите OK кнопка. Смотрите скриншот:

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

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

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

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

2. Нажмите Данные > проверка достоверности данных > проверка достоверности данных.

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

3.1). Разрешить выберите Список;
3.2) Нажмите на Источник поле, а затем нажмите F3 .
3.3). Вставить имя диалоговом окне выберите имя диапазона, который вы только что создали, а затем щелкните значок OK кнопка;
Советы: вы также можете ввести вручную = название диапазона в Источник коробка. В этом случае я введу = Город.
3.4) Нажмите OK когда он вернется в проверка достоверности данных диалоговое окно. Смотрите скриншот:

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

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

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

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

Здесь я называю диапазон Городом.

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

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

3.1) Введите имя в Фамилия коробка;
3.2). Относится к введите в него следующую формулу.
= SourceData.xlsx! Город
3.3) Нажмите OK чтобы спасти это

Заметки:

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

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

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

5.1). Разрешить выберите Список;
5.2) Нажмите на Источник поле, а затем нажмите F3 .
5.3). Вставить имя диалоговом окне выберите имя диапазона, который вы только что создали, а затем щелкните значок OK кнопка;
Советы: Вы также можете ввести вручную = название диапазона в Источник коробка. В этом случае я введу = Тест.
5.4) Нажмите OK когда он вернется в проверка достоверности данных диалоговое окно.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

1. Нажмите Формулы > Менеджер имен.

Советы: Вы можете открыть Менеджер имен окно, нажав кнопку Ctrl + F3 ключи.

2. в Менеджер имен окно необходимо настроить следующим образом:

2.1). Фамилия поле выберите именованный диапазон, который вы хотите обновить;
2.2). Относится к раздел, нажмите кнопку для выбора обновленного диапазона для выпадающего списка;
2.3) Нажмите Закрыть .

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. в Новое правило форматирования диалоговое окно, настройте следующим образом.

4.1). Выберите тип правила поле, выберите Форматировать только ячейки, содержащие вариант;
4.2). Форматировать только ячейки с раздел, выберите Конкретный текст из первого раскрывающегося списка выберите содержащие из второго раскрывающегося списка, а затем выберите первый элемент исходного списка в третьем поле;
Советы: Здесь я выбираю ячейку A16 в третьем текстовом поле. A16 - это первый элемент исходного списка, на основе которого я создал раскрывающийся список.
4.3) Нажмите Формат .
4.4). Формат ячеек диалогового окна, перейдите к Заполнять вкладку, выберите цвет фона для указанного текста, а затем щелкните значок OK кнопка. Или вы можете выбрать определенный цвет шрифта для текста, как вам нужно.
4.5) Нажмите OK кнопку, когда он вернется в Новое правило форматирования диалоговое окно.

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

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

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

Здесь представьте Цветной раскрывающийся список особенность Kutools for Excel чтобы помочь вам легко добавить цвет в раскрывающийся список в Excel.

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

2. Нажмите Кутулс > Раскрывающийся список > Цветной раскрывающийся список.

3. в Цветной раскрывающийся список диалоговое окно, сделайте следующее.

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

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

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

Выделение ячеек в раскрывающемся списке

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

Создать зависимый выпадающий список в 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. в Редактировать имя диалоговое окно, настройте следующим образом.

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) Нажмите OK кнопка. Смотрите скриншот:

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

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

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

5.1). Разрешить выберите Список;
5.2) Нажмите Источник поле, а затем нажмите F3 ключ;
5.3) Во всплывающем окне Вставить имя выберите именованный диапазон, созданный на шаге 3, и нажмите OK;
Советы: Вы можете напрямую ввести именованный диапазон как = именованный диапазон в Источник пунктом.
5.4) Нажмите Предупреждение об ошибке , снимите флажок Показывать предупреждение об ошибке после ввода неверных данных поле и, наконец, щелкните OK .

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. в проверка достоверности данных диалоговое окно, настройте следующим образом.

4.1). Разрешить выберите Список;
4.2). Источник в поле выберите диапазон исходных данных (в данном случае список названий стран);
4.3) Нажмите OK.

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

Заметки:

1) В коде цифра 4 в строке Если Target.Column = 4 Затем представляет собой номер столбца в раскрывающемся списке, созданном на шагах 3 и 4. Если раскрывающийся список находится в столбце F, замените номер 4 на 6;
2) «падатьВ пятой строке указано имя диапазона, созданного на шаге 2. Вы можете изменить его по своему усмотрению.

7. нажмите другой + Q ключи, чтобы закрыть Microsoft Visual Basic для приложений окно.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

3). Удалить дубликаты диалогового окна, нажмите OK .

4) Тогда Microsoft Excel всплывает, чтобы сообщить, сколько дубликатов было удалено, нажмите OK.

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

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

2.1) Для первого вспомогательного столбца (здесь я выбираю столбец D в качестве первого вспомогательного столбца) введите приведенную ниже формулу в первую ячейку (кроме заголовка столбца), а затем нажмите Enter ключ. Выберите ячейку результата и перетащите Ручка заполнения полностью вниз, пока не достигнет нижней границы диапазона.
= СТРОКИ ($ A $ 2: A2)
2.2) Для второго вспомогательного столбца (столбец E) введите приведенную ниже формулу в ячейку E2 и затем нажмите Enter ключ. Выберите E2 и перетащите Ручка заполнения в самый низ диапазона.
Примечание. Если в раскрывающемся списке не выбрано значение, результаты формул будут отображаться как пустые.
= ЕСЛИ (A2 = $ H $ 2; D2; "")
2.3) Для третьего вспомогательного столбца (столбец F) введите приведенную ниже формулу в F2 и затем нажмите Enter ключ. Выберите F2 и перетащите Ручка заполнения в самый низ диапазона.
Внимание: Если в раскрывающемся списке не выбрано значение, результаты формул будут пустыми.
= ЕСЛИОШИБКА (МАЛЕНЬКИЙ ($ E $ 2: $ E $ 17; D2); "")

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

3.1) Выберите первую выходную ячейку (здесь я выбираю J2), введите в нее формулу ниже и затем нажмите Enter .
=IFERROR(INDEX($A$2:$C$17,$F2,COLUMNS($J$2:J2)),"")
3.2) Выберите ячейку результата и перетащите Ручка заполнения Поперек правее две клетки.
3.3) Не снимая выделения с диапазона J2: l2, перетащите маркер заливки полностью вниз, пока он не достигнет нижней части диапазона.

Заметки:

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

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

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

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

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

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

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

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

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

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

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

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

2.1). Разрешить выберите Список;
2.2). Источник В поле выберите исходные данные, которые будут отображаться в раскрывающемся списке.
Советы: Для выпадающего списка, который вы уже создали, пропустите эти два шага.
2.3) Затем перейдите в Предупреждение об ошибке , снимите флажок Показывать предупреждение об ошибке после ввода неверных данных коробка;
2.4) Нажмите OK .

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

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

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

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

= IF (C2 = "", "--Выбрать элемент из списка--")

Заметки:

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

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

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

Лучшие инструменты для офисной работы

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

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

Описание

Вкладка Office: интерфейс с вкладками в Office и упрощение работы

  • Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
  • Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
  • Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
Comments (1)
No ratings yet. Be the first to rate!
通りすがりのわんこ
通りすがりのわんこ
This comment was minimized by the moderator on the site
こちらはOffice365ですが、どうやらそのコーディングでは動作しないようです。
代わりに初歩的ですが、以下にて動作を確認出来ました。

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim xZoom As Variant
If (Target.Row >= 11 And Target.Row <= 35 And Target.Column >= 3 And Target.Column <= 6) Then
ActiveWindow.zoom = 150
Else
ActiveWindow.zoom = 60
End If
End Sub
Reply
0
0
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations