Как сделать выпадающий список в Excel

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

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

Видеоурок

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

Представьте, что у нас есть список фруктов:

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

• Выбираем ячейку, в которой хотим создать выпадающий список;
• Перейдите на вкладку «Данные» => раздел «Работа с данными» на панели инструментов => выберите пункт «Проверка данных«.

• Во всплывающем окне «Проверка входных значений» на вкладке «Параметры» типа данных выберите «Список«:

• В поле «Источник» введите диапазон названий фруктов =$A$2:$A$6 или просто поместите указатель мыши в поле ввода значения «Источник» и затем выберите диапазон данных с помощью мыши:

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

Как сделать выпадающий список в Excel используя ручной ввод данных

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

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

• Выбираем ячейку, в которой хотим создать выпадающий список;
• Перейдите на вкладку «Данные» => раздел «Работа с данными» панели инструментов => выберите пункт «Проверка данных«:

• Во всплывающем окне «Проверка входных значений» на вкладке «Параметры» типа данных выберите «Список«:

• В поле «Источник» введите значение «Да; Нет».
• Нажмите «ОК«

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

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

Как создать раскрывающийся список в Эксель с помощью функции СМЕЩ

Наряду с описанными выше методами вы также можете использовать формулу OFFSET для создания раскрывающихся списков.

Например, у нас есть список со списком фруктов:

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

• Выбираем ячейку, в которой хотим создать выпадающий список;
• Перейдите на вкладку «Данные» => раздел «Работа с данными» панели инструментов => выберите пункт «Проверка данных«:

• Во всплывающем окне «Проверка входных значений» на вкладке «Параметры» типа данных выберите «Список«:

• В поле «Источник» введите формулу: =СМЕЩЕНИЕ(A$2$;0;0;5)
• Нажмите «ОК«

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

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

В приведенном выше примере мы использовали формулу =СМЕЩ(ссылка,смещение строки,смещение столбца,[высота],[ширина]).

Эта функция принимает пять аргументов. Аргумент ссылки ($A$2 в примере) указывает, с какой ячейки должно начинаться смещение. В аргументах "смещение_по_строкам" и "смещение_по_столбцам" (в примере указано значение "0") - на сколько строк/столбцов нужно сдвинуть для отображения данных. Аргумент «[высота]» имеет значение «5», что является высотой диапазона ячеек. Мы не указываем аргумент «[ширина]», так как в нашем примере область состоит из одного столбца.

Используя эту формулу, система возвращает вам в качестве данных для выпадающего списка диапазон ячеек, начиная с ячейки $A$2, состоящий из 5 ячеек.

Как сделать выпадающий список в Excel с подстановкой данных (с использованием функции СМЕЩ)

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

Для создания списка необходимо:

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

В этой формуле в аргументе «[высота]» мы указываем в качестве аргумента, задающего высоту списка данных, — формулу СЧЁТЕСЛИ, которая вычисляет количество непустых ячеек в заданном диапазоне A2:A100.

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

Больше лайфхаков в нашем Telegram Подписывайтесь

Как создать выпадающий список в Excel с автоматической подстановкой данных

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

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

• На панели инструментов нажмите на пункт «Форматировать как таблицу«:

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

•  Нажав клавишу «ОК» во всплывающем окне, подтверждаем выбранный диапазон ячеек:

• Затем выберите диапазон данных таблицы для раскрывающегося списка и дайте ему имя в левом поле над столбцом «A»:

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

• Выбираем ячейку, в которой хотим создать список;
• Перейдите на вкладку «Данные» => раздел «Работа с данными» панели инструментов => выберите пункт «Проверка данных«:

• Во всплывающем окне «Проверка входных значений» на вкладке «Параметры» типа данных выберите «Список«:

• В поле источника введите = "название вашей таблицы". В нашем случае мы назвали его «Список«:

• Прозрачный! Выпадающий список создан, в нем отображаются все данные из указанной таблицы:

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

 

 

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

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

В Excel есть возможность скопировать созданные выпадающие списки. Например, в ячейке A1 у нас есть раскрывающийся список, который мы хотим скопировать в диапазон ячеек A2:A6.

Чтобы скопировать раскрывающийся список с текущим форматированием:

• щелкните левой кнопкой мыши по ячейке с выпадающим списком, которую хотите скопировать;
• нажмите комбинацию клавиш на клавиатуре CTRL+C;
• выберите ячейки в диапазоне A2:A6, куда вы хотите вставить раскрывающийся список;
• нажмите сочетание клавиш CTRL+V.

Итак, вы хотите скопировать раскрывающийся список и сохранить исходный формат списка (цвет, шрифт и т д.). Если вы хотите скопировать/вставить выпадающий список без сохранения формата, сделайте следующее:

• щелкните левой кнопкой мыши по ячейке с выпадающим списком, которую хотите скопировать;
• нажмите комбинацию клавиш на клавиатуре CTRL+C;
• выберите ячейку, куда вы хотите вставить раскрывающийся список;
• нажимаем правую кнопку мыши => вызываем выпадающее меню и нажимаем «Специальная вставка«;

 

 

• В появившемся окне в разделе «Вставка» выбираем пункт «условия значения«:

• Нажмите «ОК«

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

Как выделить все ячейки, содержащие выпадающий список в Экселе

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

• Щелкните вкладку «Главная» на панели инструментов;
• Нажмите «Найти и выделить» и выберите «Выбрать группу ячеек«:

 

 

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

• Нажмите «ОК«

Нажав «ОК», Excel выберет все ячейки с выпадающим списком на листе. Так вы сможете привести все списки одновременно к единому формату, обозначить границы и т.д.

Как сделать зависимые выпадающие списки в Excel

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

Предположим, у нас есть списки городов двух стран России и США:

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

• Создайте два именованных диапазона для ячеек "A2:A5" с именем "Россия" и для ячеек "B2:B5" с именем "США". Для этого нам нужно выбрать весь диапазон данных для выпадающих списков:

• Перейдите на вкладку «Формулы» => нажмите в разделе «Определенные имена» на пункт «Создать из выделения«:

• Во всплывающем окне «Создать имя из выбранного диапазона» установите флажок «на строку выше». При этом Excel создаст два именованных диапазона «Россия» и «США» со списками городов:

• Нажмите «ОК«
• В ячейке «D2» создайте выпадающий список для выбора стран «Россия» или «США». Итак, мы создадим первый выпадающий список, в котором пользователь сможет выбрать одну из двух стран.

Теперь, чтобы создать зависимый выпадающий список:

• Выберите ячейку E2 (или любую другую ячейку, в которой вы хотите создать зависимый выпадающий список);
• Нажмите на вкладку «Данные» => «Проверка данных”;
• Во всплывающем окне «Проверка входных значений» на вкладке «Параметры» в типе данных выберите «Список«:

• В разделе Источник введите ссылку: =ДВССЫЛ(D2) или =ДВССЫЛ(D2);

• Нажмите «ОК«

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

Больше лайфхаков в нашем ВК Подписывайся