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

Автор: | 05.05.2022

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

Видеоурок

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

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

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

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

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

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

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

Проверить входные значения в 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 с автоматической подстановкой данных

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

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

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

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

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

  • В раскрывающемся меню выберите стиль таблицы:

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

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

Автоматическая замена данных в Excel

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

Таблица имен в Excel

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

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

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

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

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

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

Автоматическая замена данных в Excel

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

Автоматическая замена данных в выпадающем списке Excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Выпадающий список в Excel. Как найти все списки

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

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

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

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

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

Косвенная функция в Excel

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

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

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

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

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

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

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

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

кОСВЕННАЯ функция в Excel

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

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

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

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

раскрывающийся список в Excel-6

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