Выпадающий список в Excel — пожалуй, один из самых удобных способов работы с данными. Вы можете использовать их как для заполнения форм, так и для создания громоздких информационных панелей и таблиц. Выпадающие списки часто используются в приложениях на смартфонах, веб-сайтах, они интуитивно понятны для обычного пользователя.
Видеоурок
Как создать выпадающий список в Экселе на основе данных из перечня
Допустим, у нас есть список фруктов:
Чтобы создать выпадающий список, вам необходимо выполнить следующие действия:
- Выберите ячейку, в которой мы хотим создать раскрывающийся список;
- Перейдите на вкладку «Данные» => раздел «Работа с данными» на панели инструментов => выберите пункт «Проверить данные“.
- Во всплывающем окне «Проверка введенных значений» во вкладке «Параметры» выберите «Список» в типе данных“:
- В поле «Источник» введите диапазон названий фруктов = $ A $ 2: $ A $ 6 или просто поместите курсор мыши в поле ввода значения «Источник» и затем выберите диапазон данных с помощью мыши:
Если вы хотите создать раскрывающиеся списки в нескольких ячейках одновременно, выберите все ячейки, в которых вы хотите их создать, а затем выполните указанные выше действия. Важно убедиться, что ссылки на ячейки являются абсолютными (например, $ A $ 2), а не относительными (например, A2 или A $ 2 или $ A2).
Как сделать выпадающий список в Excel используя ручной ввод данных
В приведенном выше примере мы ввели список данных для раскрывающегося списка, выделив диапазон ячеек. Помимо этого метода, вы можете ввести данные, чтобы вручную создать раскрывающийся список (вам не нужно хранить его в какой-либо ячейке).
Например, предположим, что мы хотим отразить два слова «Да» и «Нет» в раскрывающемся меню. Для этого нам понадобятся:
- Выберите ячейку, в которой мы хотим создать раскрывающийся список;
- Перейдите на вкладку «Данные» => раздел «Работа с данными» на панели инструментов => выберите пункт «Проверка данных“:
- Во всплывающем окне «Проверка введенных значений» во вкладке «Параметры» выберите «Список» в типе данных“:
- В поле «Источник» введите значение «Да; Нет”.
- Нажмите «ОК“
Далее система создаст выпадающий список в выбранной ячейке. Все элементы, перечисленные в поле «Источник», разделенные точкой с запятой, будут отображаться в разных строках раскрывающегося меню.
Если вы хотите создать раскрывающийся список сразу в нескольких ячейках, выберите необходимые ячейки и следуйте приведенным выше инструкциям.
Как создать раскрывающийся список в Эксель с помощью функции СМЕЩ
Наряду с методами, описанными выше, вы также можете использовать формулу СМЕЩЕНИЕ для создания раскрывающихся списков.
Например, у нас есть список со списком фруктов:
Чтобы создать раскрывающийся список с использованием формулы СМЕЩЕНИЕ, вам необходимо сделать следующее:
- Выберите ячейку, в которой мы хотим создать раскрывающийся список;
- Перейдите на вкладку «Данные» => раздел «Работа с данными» на панели инструментов => выберите пункт «Проверка данных“:
- Во всплывающем окне «Проверка введенных значений» во вкладке «Параметры» выберите «Список» в типе данных“:
- В поле «Источник» введите формулу: = СМЕЩЕНИЕ (A $ 2 $; 0; 0; 5)
- Нажмите «ОК“
Система создаст выпадающий список со списком фруктов.
Как эта формула работает?
В приведенном выше примере мы использовали формулу = OFFSET (ref; row_offset; column_offset; [height]; [width]).
Эта функция содержит пять аргументов. Аргумент «ссылка» ($ A $ 2 в примере) указывает, с какой ячейки начинать смещение. В аргументах «offset_by_strings» и «offset_by_columns» (в примере указано значение «0») — сколько строк / столбцов необходимо переместить для отображения данных. Аргумент [высота] указывает значение «5», которое обозначает высоту диапазона ячеек. Мы не указываем аргумент «[ширина]», поскольку в нашем примере диапазон состоит из одного столбца.
Используя эту формулу, система возвращает в качестве данных для раскрывающегося списка диапазон ячеек, начинающийся с ячейки $ A $ 2, состоящий из 5 ячеек.
Как сделать выпадающий список в Excel с подстановкой данных (с использованием функции СМЕЩ)
Если вы используете формулу СМЕЩЕНИЕ в предыдущем примере для создания списка, вы создаете список фиксированных данных в определенном диапазоне ячеек. Если вы хотите добавить значение как элемент списка, вам нужно будет вручную отредактировать формулу. Ниже вы узнаете, как создать динамический раскрывающийся список, в который новые данные будут автоматически загружаться для просмотра.
Для создания списка вам понадобятся:
- Выберите ячейку, в которой мы хотим создать раскрывающийся список;
- Перейдите на вкладку «Данные» => раздел «Работа с данными» на панели инструментов => выберите пункт «Проверить данные“;
- Во всплывающем окне «Проверка введенных значений» на вкладке «Параметры» выберите «Список» в типе данных“;
- В поле «Источник» введите формулу: = СМЕЩЕНИЕ (A $ 2 $; 0; 0; СЧЁТЕСЛИ ($ A $ 2: $ A $ 100;””))
- Нажмите «ОК“
В этой формуле в аргументе «[высота]» мы указываем формулу СЧЁТЕСЛИ в качестве аргумента, обозначающего высоту списка с данными, который вычисляет количество непустых ячеек в заданном диапазоне A2: A100.
Примечание. Для правильной работы формулы важно, чтобы в списке данных не было пустых строк, отображаемых в раскрывающемся меню.
Как создать выпадающий список в 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);
- Нажмите «ОК“
Теперь, если вы выберете страну «Россия» в первом раскрывающемся списке, во втором раскрывающемся списке появятся только города, принадлежащие этой стране. Даже если вы выберете «США» из первого раскрывающегося списка.
