Создание выпадающего списка в excel 2019: для чего нужен и как сделать / Skillbox Media

Содержание

Выпадающий список в Excel — подробное пошаговое руководство

ГлавнаяРабота на компьютере

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

Выпадающий список в Excel

Содержание

Создание выпадающего списка
- Видео — Создание выпадающих списков в Excel
Раскрывающийся список с подстановкой данных
Зависимые раскрывающиеся списки
- Видео — Связанные выпадающие списки: легко и быстро

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

Для начала рассмотрим самый простой способ, который поможет вам сделать раскрывающийся список. Чтобы осуществить его, делайте всё как в инструкции ниже:

Шаг 1.

Перейдите во вкладку «Данные», которая расположена на верхней панели, затем в блоке «Работа с данными» выберите инструмент проверки данных (на скриншоте показано, какой иконкой он изображен).

Переходим во вкладку «Данные», которая расположена на верхней панели, затем в блоке «Работа с данными» выбираем инструмент проверки данных

Шаг 2. Теперь откройте самую первую вкладку «Параметры», и установите «Список» в перечне типа данных.

В первой вкладке «Параметры», в разделе «Тип данных» выставляем «Список»

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

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

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

Результат сделанного выпадающего списка

На заметку! Есть ещё один способ указать значение в источнике – написать в поле ввода имя диапазона. Этот способ самый быстрый, но прежде чем прибегать к нему, нужно создать именованный диапазон. О том, как это сделать, мы поговорим позже.

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

Видео — Создание выпадающих списков в Excel

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

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

Выделите левой кнопкой мышки диапазон для списка (в данном примере это будет перечень деревьев), затем откройте вкладку «Главная» и выберите меню «Форматировать как таблицу».
Выделяем левой кнопкой мышки диапазон для списка, открываем вкладку «Главная» и выбираем меню «Форматировать как таблицу»
После этого откроется меню выбора стилей. Стиль никакой роли не играет, кроме визуальной, поэтому выбирайте любой по вашему вкусу.
Выбираем любой понравившийся стиль
Далее появится окно подтверждения, цель которого – убедиться в правильности введённого диапазона. Здесь важно установить галочку возле «Таблица с заголовками», так как наличие заголовка в данном случае играет ключевую роль.
Устанавливаем галочку возле «Таблица с заголовками», нажимаем «ОК»
После проделанных процедур вы получите следующий вид диапазона.
Результат отформатированной таблицы
Теперь выделите левым кликом мыши ту ячейку, в которой будет расположен выпадающий список, и перейдите во вкладку «Данные» (в предыдущем способе сказано, как это сделать).
Выделяем левым кликом мыши ту ячейку, в которой будет расположен выпадающий список, и переходим во вкладку «Данные»
В поле ввода «Источник» вам нужно вписать функцию с синтаксисом «=ДВССЫЛ(“Имя таблицы[Заголовок]”)». На скриншоте указан более конкретный пример.
В поле «Источник» печатаем функцию «=ДВССЫЛ(“Имя таблицы[Заголовок]”)», подставляя свои данные, как на примере

Итак, список готов. Выглядеть он будет вот так.

Готовый список

На заметку! У этого способа есть принципиальное отличие от первого – в качестве списка используется готовая таблица, а не диапазон значений. Это значит, что любые изменения в таблице будут отображаться и в выпадающем списке.

Давайте протестируем это. Для начала добавим в нашу новую отформатированную таблицу новую ячейку «ёлка». Как видите, это же значение добавилось в список.

Добавляем в таблицу строку с новым значением, она автоматически появится в выпадающем списке

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

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

На заметку! В этом способе мы имели дело с так называемой «умной таблицей». Она легко расширяется, и это её свойство полезно для многих манипуляций с таблицами Excel, в том числе и для создания выпадающего списка.

Зависимые раскрывающиеся списки

Чтобы создать зависимые выпадающие списки, следуйте дальнейшей пошаговой инструкции:

Для начала вам нужно создать именованный диапазон. Перейдите во вкладку «Формулы», затем выберите «Диспетчер имён» и «Создать».
Выделяем диапазон ячеек со значением, открываем вкладку «Формулы», нажимаем «Диспетчер имен»
Нажимаем «Создать»
Введите имя и диапазон, затем нажмите «ОК». Если вы перед этим выделяли нужные ячейки мышкой, то диапазон будет указан автоматически. Также примите к сведению, что имя диапазона должно быть уникальным и не должно содержать знаков препинания с пробелами.
Пишем имя «Деревья», нажимаем «ОК»
По такой же методике сделайте столько именованных диапазонов, сколько логических зависимостей хотите создать.

В данном примере это ещё два диапазона: «Кустарники» и «Травы».
Создаем таким же способом остальные диапазоны
Откройте вкладку «Данные» (в первом способе указан путь к ней) и укажите в источнике названия именованных диапазонов, как это показано на скриншоте.
В поле «Источник» указываем ячейки с названием диапазонов, нажимаем «ОК»
Выпадающий список с названием диапазона ячеек
Теперь вам нужно создать дополнительный раскрывающийся список по той же схеме. В этом списке будут отражаться те слова, которые соответствуют заголовку. Например, если вы выбрали «Дерево», то это будут «береза», «липа», «клен» и так далее. Чтобы осуществить это, повторите вышеуказанные шаги, но в поле ввода «Источник» введите функцию «=ДВССЫЛ(E1)». В данном случае «E1» – это адрес ячейки с именем первого диапазона. По такому же способу вы сможете создавать столько взаимосвязанных списков, сколько вам потребуется.

В поле ввода «Источник» вводим функцию «=ДВССЫЛ(E1)», нажимаем «ОК»
Результат выпадающего связанного списка

Теперь вам известны несколько способов, как создать выпадающее меню в Excel-таблице. Это может пригодиться вам в ряде случаев, а особенно – в проверке данных. Выбор правильного способа должен осуществляться в зависимости от того, с каким типом таблицы вы работаете. Если это «одноразовая» таблица, то подойдёт первый способ – он быстрый и лёгкий. Если же таблица требует постоянных изменений, то лучше всего будет воспользоваться тем способом, который включает в себя создание «умных таблиц» и именованных диапазонов.

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

Понравилась статья?
Сохраните, чтобы не потерять!

Рекомендуем похожие статьи

«Как сделать выпадающий список в Excel?» — Яндекс Кью

Как создать раскрывающийся список в Excel (единственное необходимое руководство)

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

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

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

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

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

В этом руководстве рассматриваются:

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

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

Использование данных из ячеек.
Ввод данных вручную.
Использование формулы СМЕЩ.

#1 Использование данных из ячеек

Допустим, у вас есть список элементов, как показано ниже:

Вот шаги для создания раскрывающегося списка Excel:

Выберите ячейку, в которой вы хотите создать раскрывающийся список.
Перейдите в раздел «Данные» -> «Инструменты данных» -> «Проверка данных».
В диалоговом окне «Проверка данных» на вкладке «Настройки» выберите «Список» в качестве критериев проверки.
- Как только вы выберете Список, появится исходное поле.
В поле источника введите =$A$2:$A$6 или просто нажмите в поле Источник, выберите ячейки с помощью мыши и нажмите OK. Это добавит раскрывающийся список в ячейку C2.
- Убедитесь, что установлен флажок в раскрывающемся списке «В ячейке» (который установлен по умолчанию). Если этот параметр не отмечен, в ячейке не отображается раскрывающийся список, однако вы можете вручную ввести значения в список.

Примечание: Если вы хотите создать раскрывающиеся списки в нескольких ячейках за один раз, выберите все ячейки, в которых вы хотите его создать, а затем выполните описанные выше шаги. Убедитесь, что ссылки на ячейки являются абсолютными (например, $A$2), а не относительными (например, A2, A$2 или $A2).

#2 Путем ввода данных вручную

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

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

Выберите ячейку, в которой вы хотите создать раскрывающийся список (ячейка C2 в этом примере).
Перейдите в раздел «Данные» -> «Инструменты данных» -> «Проверка данных».
В диалоговом окне «Проверка данных» на вкладке «Настройки» выберите «Список» в качестве критериев проверки.
- Как только вы выберете Список, появится исходное поле.
В поле источника введите Да, Нет
- Убедитесь, что установлен флажок в раскрывающемся списке В ячейке.
Нажмите кнопку ОК.

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

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

#3 Использование формул Excel

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

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

Например, предположим, что у вас есть набор данных, как показано ниже:

Вот шаги для создания раскрывающегося списка Excel с использованием функции СМЕЩ:

Выберите ячейку, в которой вы хотите создать раскрывающийся список (ячейка C2 в этом примере).
Перейдите в раздел «Данные» -> «Инструменты данных» -> «Проверка данных».
В диалоговом окне «Проверка данных» на вкладке «Настройки» выберите «Список» в качестве критериев проверки.
- Как только вы выберете Список, появится поле источника.
В поле Источник введите следующую формулу: =СМЕЩЕНИЕ($A$2,0,0,5)
- Убедитесь, что установлен флажок в раскрывающемся списке В ячейке.
Нажмите кнопку ОК.

Будет создан раскрывающийся список, в котором перечислены все названия фруктов (как показано ниже).

Примечание: Если вы хотите создать раскрывающийся список в нескольких ячейках за один раз, выберите все ячейки, в которых вы хотите его создать, а затем выполните описанные выше шаги. Убедитесь, что ссылки на ячейки являются абсолютными (например, $A$2), а не относительными (например, A2, или A$2, или $A2).

Как работает эта формула??

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

Возвращает список элементов из диапазона A2:A6.

Вот синтаксис функции СМЕЩ: =СМЕЩ(ссылка, строки, столбцы, [высота], [ширина])

Принимает пять аргументов, где мы указали ссылку как A2 (начальная точка списка) . Строки/столбцы указаны как 0, так как мы не хотим смещать ссылочную ячейку. Высота указана как 5, так как в списке пять элементов.

Теперь, когда вы используете эту формулу, она возвращает массив со списком пяти фруктов в A2:A6. Обратите внимание: если вы введете формулу в ячейку, выделите ее и нажмите F9, вы увидите, что она возвращает массив названий фруктов.

Создание динамического раскрывающегося списка в Excel (с использованием смещения)

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

Вот способ сделать его динамическим (и это всего лишь небольшая корректировка формулы):

Выберите ячейку, в которой вы хотите создать раскрывающийся список (ячейка C2 в этом примере).
Перейдите в раздел «Данные» -> «Инструменты данных» -> «Проверка данных».
В диалоговом окне «Проверка данных» на вкладке «Настройки» выберите «Список» в качестве критериев проверки. Как только вы выберете Список, появится исходное поле.
В исходном поле введите следующую формулу: =СМЕЩ($A$2,0,0,СЧЁТЕСЛИ($A$2:$A$100,»<>«))
Убедитесь, что установлен флажок в раскрывающемся списке В ячейке.
Нажмите кнопку ОК.

В этой формуле я заменил аргумент 5 на СЧЁТЕСЛИ($A$2:$A$100,»<>«).

Функция СЧЁТЕСЛИ подсчитывает непустые ячейки в диапазоне A2:A100. Таким образом, функция СМЕЩ настраивается таким образом, чтобы включать все непустые ячейки.

Примечание:

Чтобы это работало, между заполненными ячейками НЕ должно быть пустых ячеек.
Если вы хотите создать раскрывающийся список в нескольких ячейках за один раз, выберите все ячейки, в которых вы хотите его создать, а затем выполните описанные выше шаги. Убедитесь, что ссылки на ячейки являются абсолютными (например, $A$2), а не относительными (например, A2, A$2 или $A2).

Копирование и вставка раскрывающихся списков в Excel

Вы можете скопировать и вставить ячейки с проверкой данных в другие ячейки, и это также скопирует проверку данных.

Например, если у вас есть раскрывающийся список в ячейке C2, и вы хотите применить его также к ячейке C3:C6, просто скопируйте ячейку C2 и вставьте ее в ячейку C3:C6. Это скопирует раскрывающийся список и сделает его доступным в C3: C6 (вместе с раскрывающимся списком также будет скопировано форматирование).

Если вы хотите скопировать только раскрывающийся список, а не форматирование, выполните следующие действия:

Скопируйте ячейку с раскрывающимся списком.
Выберите ячейки, в которые вы хотите скопировать раскрывающийся список.
Перейдите на главную страницу -> Вставить -> Специальная вставка.
В диалоговом окне «Специальная вставка» выберите «Проверка в параметрах вставки».
Нажмите кнопку ОК.

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

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

Будьте осторожны при работе с раскрывающимися списками в Excel.

При копировании ячейки (не содержащей раскрывающегося списка) поверх ячейки, содержащей раскрывающийся список, раскрывающийся список теряется.

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

Как выбрать все ячейки, в которых есть раскрывающийся список

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

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

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

Перейти на главную –> Найти и выбрать –> Перейти к специальному.
В диалоговом окне «Перейти к специальному» выберите «Проверка данных».
- Проверка данных имеет два варианта: Все и То же. All выберет все ячейки, к которым применено правило проверки данных. То же будет выбирать только те ячейки, которые имеют то же правило проверки данных, что и активная ячейка.
Нажмите кнопку ОК.

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

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

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

Создание зависимого/условного выпадающего списка Excel

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

Если вы предпочитаете читать, а не смотреть видео, продолжайте читать.

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

Это так называемые зависимые или условные выпадающие списки.

Ниже приведен пример условного/зависимого раскрывающегося списка:

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

Теперь давайте посмотрим, как это создать.

Вот шаги для создания зависимого/условного раскрывающегося списка в Excel:

Выберите ячейку, в которой вы хотите разместить первый (основной) раскрывающийся список.
Перейдите в раздел «Данные» -> «Проверка данных». Это откроет диалоговое окно проверки данных.
В диалоговом окне проверки данных на вкладке настроек выберите Список.
В поле Источник укажите диапазон, содержащий элементы, которые должны отображаться в первом раскрывающемся списке.
Нажмите кнопку ОК. Это создаст раскрывающийся список 1.
Выберите весь набор данных (в данном примере A1:B6).
Перейдите в Формулы -> Определенные имена -> Создать из выделения (или можно использовать сочетание клавиш Control + Shift + F3).
В диалоговом окне «Создать имя из выделенного» установите флажок «Верхняя строка» и снимите все остальные. При этом создается 2 диапазона имен («Фрукты» и «Овощи»). Именованный диапазон фруктов относится ко всем фруктам в списке, а названный диапазон овощей относится ко всем овощам в списке.
Нажмите кнопку ОК.
Выберите ячейку, в которой вы хотите разместить зависимый/условный раскрывающийся список (E3 в этом примере).
Перейдите в раздел «Данные» -> «Проверка данных».
В диалоговом окне «Проверка данных» на вкладке настроек убедитесь, что выбран параметр «Список».
В поле Источник введите формулу =ДВССЫЛ(D3). Здесь D3 — это ячейка, содержащая основной раскрывающийся список.
Нажмите кнопку ОК.

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

Загрузить файл примера

Как это работает? – условный раскрывающийся список (в ячейке E3) ссылается на =ДВССЫЛ(D3). Это означает, что когда вы выбираете «Фрукты» в ячейке D3, раскрывающийся список в E3 ссылается на именованный диапазон «Фрукты» (через функцию ДВССЫЛ) и, следовательно, перечисляет все элементы в этой категории.

Важное примечание при работе с условными выпадающими списками в Excel:

Если вы сделаете выбор, а затем измените родительский раскрывающийся список, зависимый раскрывающийся список не изменится и, следовательно, будет неправильным вводом. Например, если вы выберете США в качестве страны, а затем Флориду в качестве штата, а затем вернетесь и измените страну на Индию, штат останется Флоридой. Вот отличный учебник Дебры по очистке зависимых (условных) выпадающих списков в Excel при изменении выбора.
Если основная категория состоит из более чем одного слова (например, «Сезонные фрукты» вместо «Фрукты»), вам нужно использовать формулу =ДВССЫЛ(ПОДСТАВИТЬ(D3″, «,»_»)), вместо простой функции ДВССЫЛ, показанной выше. Причина этого в том, что Excel не допускает пробелов в именованных диапазонах. Поэтому, когда вы создаете именованный диапазон, используя более одного слова, Excel автоматически вставляет знак подчеркивания между словами. Таким образом, именованный диапазон «Сезонные фрукты» будет «Сезонные_фрукты». Использование функции ПОДСТАВИТЬ в функции ДВССЫЛ гарантирует, что пробелы — это , преобразованные в символы подчеркивания.

Вам также могут понравиться следующие учебные пособия по Excel:

Извлечение данных из раскрывающегося списка в Excel.
Выберите несколько элементов из раскрывающегося списка в Excel.
Создание поля поиска динамического фильтра Excel.
Отображение основной и подкатегории в раскрывающемся списке в Excel.
Как вставить флажок в Excel.
Использование переключателя (кнопки выбора) в Excel.
Как удалить раскрывающийся список в Excel?

Динамические выпадающие списки в Excel

Главная > Microsoft Excel > Как добавить динамические выпадающие списки в Excel — 4 простых шага и Word для Microsoft 365.

Цель

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

В этом руководстве рассматриваются:

Оглавление

Цель
Видеоруководство – Динамические раскрывающиеся списки в Excel
Динамические раскрывающиеся списки в Excel – Обзор
4 простых шага для создания динамических раскрывающихся списков 4
Часто задаваемые вопросы

Видеоруководство — Динамические раскрывающиеся списки в Excel

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

Создание динамических списков проверки данных в Excel Видео (9:47)

Динамические раскрывающиеся списки в Excel – обзор

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

Родственный:

Импорт и очистка данных в Excel

Суммирование с несколькими столбцами – Функция суммирования в Excel

Использование функции Let в Excel

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

4 простых шага для создания динамических выпадающих списков в Excel

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

Проверка данных в Excel
Создание динамического списка
Настройка списка проверки данных
Обновление списка

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

Проверка данных в Excel.

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

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

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

Проверка данных в Excel — выпадающие списки

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

Основной список клиентов

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

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

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

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

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

Щелкните любую ячейку в списке клиентов.
Нажмите CTRL+A , чтобы выбрать все.
На вкладке Главная в группе Стили нажмите Форматировать как таблицу .
Выберите любой стиль таблицы из вариантов.

Отформатируйте список как таблицу

Появится диалоговое окно «Форматировать как таблицу». Убедитесь, что выбран правильный диапазон данных. Если ваши данные содержат заголовки, убедитесь, что стоит галочка в строке « В моей таблице есть заголовки 9».0060’ коробка.

Нажмите OK .

Выберите источник для таблицы
Данные теперь содержатся в таблице. Назовите таблицу.
На вкладке Table Design в группе Properties щелкните поле Table Name .
Введите имя таблицы без пробелов и нажмите Введите .
Читайте также:
Надстройка Excel Solver: пятиминутное руководство
Функции динамического массива в Excel (Xlookup, Filter, Unique, Xmatch): запись веб-семинара
Создание информационной панели Excel за 5 минут
Назовите свою таблицу и выберите Преобразовать в диапазон у вас может быть таблица, содержащая много столбцов. Чтобы создать динамический список, вам нужно указать, какой столбец таблицы содержит информацию, которая будет использоваться в раскрывающемся списке, назвав диапазон ячеек.
Я хочу, чтобы в раскрывающемся списке отображались компании A и H.
Выберите ячейки от A2 до A9 .
Дайте диапазону ячеек имя, введя его в поле имени чуть выше.
Дайте диапазону ячеек имя
Настройка списка проверки данных
Пришло время настроить список проверки данных.
Перейдите на рабочий лист и щелкните ячейку, в которой вы хотите, чтобы отображались динамические раскрывающиеся списки.
На вкладке Data в Data Tools , щелкните Проверка данных .
Откройте окно проверки данных
В разделе Критерии проверки щелкните стрелку раскрывающегося списка под Разрешить и выберите Список .
Теперь мы собираемся указать Excel использовать созданный нами именованный диапазон для заполнения списка.
В поле Источник введите знак = .
Нажмите клавишу F3 .
ПРИМЕЧАНИЕ . Клавиша F3 — это сочетание клавиш, которое отображает все именованные диапазоны, созданные в книге, чтобы упростить их выбор.
Выберите именованный диапазон.
Нажмите OK .
Выберите именованный диапазон в качестве источника для раскрывающегося списка
Нажмите OK еще раз.
Теперь в выбранной ячейке появится стрелка раскрывающегося списка, в которой будет отображаться список клиентов.
Ваш динамический раскрывающийся список готов
Обновление раскрывающегося списка
Преимущество такой настройки раскрывающегося списка заключается в том, что при добавлении новых элементов в основной список в этом случае список клиентов будет автоматически обновляться. включить новую запись.
В этом примере я добавил двух новых клиентов « Company I » и « Company J » в список клиентов, и поскольку список клиентов представляет собой таблицу, он автоматически расширяется, чтобы сделать новые записи частью списка клиентов. стол.
Теперь вы можете обновить список, добавив строки данных в диапазон таблицы
Новые записи будут автоматически включены в раскрывающийся список Excel. Умно не правда ли?
Выпадающий список обновляется автоматически.
Вот и все. Это самый простой способ создать динамический раскрывающийся список Excel. Продолжайте посещать для получения более подробных качественных статей о расширенных функциях Excel и формулах Excel
Предлагаемое чтение:
Функция фильтра Excel
Динамические массивы в Excel: восемь обязательных формул
Использование функции Let в Excel
Подробнее о динамических раскрывающихся списках и других методах данных
Excel Campus — Как автоматически добавлять новые строки в раскрывающиеся списки — Динамические списки проверки данных
ExcelJet — Руководство по проверке данных Excel
Часто задаваемые вопросы
Что такое динамический раскрывающийся список в Excel?
«Динамические выпадающие списки» — это самый простой способ реализации проверки данных с возможностью расширения списка позже без изменения исходных данных.
Как проверить динамические данные?
В Excel вы можете проверять динамические данные с помощью динамического раскрывающегося списка.