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

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

Создание дополнительного списка

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

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

Далее создаем дополнительный список с повторяющимися данными (для примера возьмём поле «Название товара»). Вносим в дополнительный список все значения.

Дополнительный список можно создавать также на отдельном листе, чтобы он не портил внешний вид главной страницы.

Выделяем заполненные поля с наименованием товаров, нажимаем правой клавишей и выбираем пункт «Присвоить имя».

В нашем случае все устройства можно отнести к категории Электроника, поэтому заполняем поле «Имя» и нажимаем кнопку «ОК».

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

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

Проверяем работу

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

Связанные списки

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

Для примера мы попробуем к нашему списку добавить связанный список, а именно возьмем поле «Количество», присвоим значения от 1 до 6.

Далее в основной таблице нажимаем на пустую ячейку в поле «Количество», переходим снова в «Проверку данных», выбираем список, в поле «Источник» вписываем следующее значение: =ДВССЫЛ($B3).

Теперь можно сразу занести в таблицу количество каждого товара:

Вывод

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

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

Как в Excel создать выпадающий список для выбора изображений товаров — Сводные таблицы Excel 2010

Теперь надо дать имя нашему каталогу, чтобы ссылаться на него в будущем. Для этого используем кнопку Диспетчер имен (Name Manager) на вкладке Формулы (Formulas). Создаем новый диапазон, вводим имя (например, Фотоальбом) и в качестве адреса указываем формулу:
=СМЕЩ(Лист1!$A200;1;0;СЧЁТЗ(Лист1!$A:$A)-1;1)
=OFFSET(Лист1!$A200;1;0;COUNTA(Лист1!$A:$A)-1;1)

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

Теперь надо дать имя нашему каталогу, чтобы ссылаться на него в будущем. Для этого используем кнопку Диспетчер имен (Name Manager) на вкладке Формулы (Formulas). Создаем новый диапазон, вводим имя (например, Фотоальбом) и в качестве адреса указываем формулу:
=СМЕЩ(Лист1!$A$1;1;0;СЧЁТЗ(Лист1!$A:$A)-1;1)
=OFFSET(Лист1!$A$1;1;0;COUNTA(Лист1!$A:$A)-1;1)

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

Эта формула определяет последнюю занятую ячейку в столбце А и выдает на выходе диапазон с А2 до этой найденной ячейки. Такая относительно сложная конструкция нужна, чтобы впоследствии дописывать новые модели к нашему списку и не думать об исправлении диапазона. Если дописывать точно ничего не придется, то можете вместо ввода этой страшноватой формулы просто указать =A2:A5.

Шаг 2. Выпадающий список для выбора модели

Шаг 3. Копируем фотографию

Перенесем первую фотографию из фотоальбома к выпадающему списку. Надо выделить ячейку с первой фотографией (не сам рисунок, а ячейку!) и развернуть выпадающий список под кнопкой Копировать (Copy) на Главной (Home) вкладке:

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

Microsoft Excel 2007 больше ничего не спросит, а в Excel 2010-2016 появится еще одно дополнительное окно с выбором типа создаваемого изображения:

В нем нужно выбрать варианты «как на экране» и «растровый». Копируем, переходим на Лист 2 к выпадающему списку и в любую пустую ячейку недалеко от него вставляем наш мини-скриншот ячейки с фотографией (на вкладке Главная – Правка – Вставить или обычное CTRL+V).

Шаг 4. Создаем динамическую ссылку на выбранную фотографию

Теперь необходимо сделать ссылку, которая будет указывать на ячейку с выбранной фотографией. Снова открываем Диспетчер имен (Name Manager) на вкладке Формулы (Formulas) и создаем еще один именованный диапазон:

В английском варианте: =OFFSET(Лист1!$B$2;MATCH(Выбор;Фотоальбом;0)-1;0;1;1) .

Технически функция ПОИСКПОЗ (MATCH) находит ячейку с нужной моделью в каталоге по названию, а функция СМЕЩ (OFFSET) затем выдает ссылку на соседнюю справа от найденного названия ячейку, т.е. ячейку с фотографией товара.

Шаг 5. Привязываем фотографию к ссылке

Осталось выделить скопированную фотографию на Листе 2 и вписать в строку формул =Фото и нажать Enter.

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

Выпадающий список в Excel — Инструкция по созданию

На примере ячейки F2 рассмотрим формулу, аналогичную для всего столбца Статус (столбец F ). Из F2 формулу можно протягивать вниз до конца, а в случае умной таблицы Excel это сделает за вас. Это также относится ко всем формулам, которые мы будем обсуждать в этой статье.

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

Как в Экселе сделать выпадающий список, Excel 2007, 2010, 2013 и 2016 | Интернет и компьютер

[@ Город ] — на языке структурных формул умных таблиц это ссылка на ячейку столбца Город в той же строке, в которой находится сама формула. Поскольку ссылка идёт внутри таблицы, то имя самой таблицы в формуле можно не использовать. В остальном всё — тоже самое.