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

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

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

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

Для этого, в ячейки А1:А7 вписываем данные, которые будут отображаться в списке. Теперь выделим ячейку, в которой создадим выпадающий список – В2.

Переходим на вкладку «Данные» и кликаем по кнопочке «Проверка данных».

На вкладке «Параметры» в поле «Тип данных» выбираем «Список». В поле «Источник» можно ввести значения различными способами:

1 – вводим значения для списка вручную, через точку с запятой;

2 – указываем диапазон ячеек, в которые введены данные для выпадающего списка;

3 – выделяем ячейки с именами, кликаем по ним правой кнопкой мыши и выбираем из меню «Присвоить имя».

Выделяем ячейку В2 и в поле «Источник» ставим «=», затем пишем созданное имя.

Таким образом, мы создали простой выпадающий список в Excel.

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

Добавляем значения в выпадающий список – динамический список

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

Выделяем диапазон ячеек – D1:D8, затем на вкладке «Главная» нажимаем «Форматировать как таблицу» и выбираем любой стиль.

Подтверждаем расположение данных и ставим галочку в поле «Таблица с заголовками».

Вверху пишем заголовок таблицы – «Сотрудники», и заполняем ее данными.

Выделяем ячейку, в которой будет выпадающий список и кликаем по кнопочке «Проверка данных». В следующем окне, в поле «Источник», пишем следующее: =ДВССЫЛ(«Таблица1»). У меня одна таблица на листе, поэтому пишу «Таблица1», если будет вторая – «Таблица2», и так далее.

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

Выпадающий список со значениями с другого листа

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

На Листе 2, выделяем одну ячейку или диапазон ячеек, затем кликаем по кнопочке «Проверка данных».

Переходим на Лист 1, ставим курсор в поле «Источник» и выделяем нужный диапазон ячеек.

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

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

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

Первый называем «Имя», второй – «Фамилия», третий – «Отч».

Сделаем еще один диапазон, в котором будут прописаны присвоенные имена. Назовем его «Сотрудники».

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

В поле «Тип данных» выберите «Список», в поле источник – или введите «=Сотрудники», или выделите диапазон ячеек, которому присвоено имя.

Первый выпадающий список создан. Теперь в ячейке F2 создадим второй список, который должен зависеть от первого. Если в первом выберем «Имя», во втором отобразится список имен, если выберем «Фамилия» – список фамилий.

Выделяем ячейку и кликаем по кнопочке «Проверка данных». В поле «Тип данных» выбираем «Список», в поле источник прописываем следующее: =ДВССЫЛ($Е$1). Здесь Е1 – это ячейка с первым выпадающим списком.

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

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

Теперь Вы знаете, как сделать раскрывающийся список в Excel.

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

Спасибо, всё получилось.

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

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

Пример создания зависимого выпадающего списка в ячейке Excel

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

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

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

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

Так, например, если мы выберем категорию Развлечения, то в списке подкатегорий должно быть: Кинотеатр, Театр, Бассейн. Очень быстрое решение, если в своем домашнем бюджете вы хотите проанализировать более подробную информацию.

Список категорий и подкатегорий в зависимом выпадающем списке Excel

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

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

Зависимый выпадающий список подкатегорий

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

Рабочая исходная таблица Excel

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

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

Ну хорошо. Теперь, по очереди я опишу шаги создания зависимого выпадающего списка.

1. Имена диапазонов ячеек

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

Присвоим имена двум диапазонам. Список всех категорий и рабочий список категорий. Это будут диапазоны A3:A5 (список категорий в зеленой таблице на первом изображении) и G3:G15 (список повторяющихся категорий в фиолетовой рабочей таблице).

Для того чтобы назвать список категорий:

1. Выберите диапазон A3:A5.
2. В поле имени (поле слева от строки формулы) введите название «Категория».
3. Подтвердите с помощью клавиши Enter.

Такое же действие совершите для диапазона рабочего списка категорий G3:G15, который вы можете вызвать «Рабочий_Список». Этот диапазон мы будем использовать в формуле.

2. Создание раскрывающегося списка для категории

Это будет просто:

1. Выберите ячейку, в которую вы хотите поместить список. В моем случае это A12.
2. В меню «ДАННЫЕ» выберите инструмент «Проверка данных». Появится окно «Проверка вводимых значений».
3. В качестве типа данных выберите «Список».
4. В качестве источника введите: =Категория (рисунок ниже).
5. Подтвердите с помощью OK.

Результат следующий:

Раскрывающийся список для категории.

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

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

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

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

Вид окна «Проверка вводимых значений»:

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

Как видите, весь трюк зависимого списка состоит в использовании функции СМЕЩ. Ну хорошо, почти весь. Помогают ей функции ПОИСКПОЗ и СЧЕТЕСЛИ. Функция СМЕЩ позволяет динамически определять диапазоны. Вначале мы определяем ячейку, от которой должен начинаться сдвиг диапазона, а в последующих аргументах определяем его размеры.

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

Поскольку рабочая таблица отсортирована по Категории, то диапазон, который должен быть источником для раскрывающегося списка, будет начинаться там, где впервые встречается выбранная категория. Например, для категории Питание мы хотим отобразить диапазон H6:H11, для Транспорта - диапазон H12: H15 и т. д. Обратите внимание, что все время мы перемещаемся по столбцу H, а единственное, что изменяется, это начало диапазона и его высота (то есть количество элементов в списке).

Начало диапазона будет перемещено относительно ячейки H2 на такое количество ячеек вниз (по числу), сколько составляет номер позиции первой встречающейся категории в столбце Категория. Проще будет понять на примере: диапазон для категории Питание перемещен на 4 ячейки вниз относительно ячейки H2 (начинается с 4 ячейки от H2). В 4-ой ячейке столбца Подкатегория (не включая заголовок, так как речь идет о диапазоне с именем Рабочий_Список), есть слово Питание (его первое появление). Мы используем этот факт собственно для определения начала диапазона. Послужит нам для этого функция ПОИСКПОЗ (введенная в качестве второго аргумента функции СМЕЩ):

Высоту диапазона определяет функция СЧЕТЕСЛИ. Она считает все встречающиеся повторения в категории, то есть слово Питание. Сколько раз встречается это слово, сколько и будет позиций в нашем диапазоне. Количество позиций в диапазоне - это его высота. Вот функция:

Конечно же, обе функции уже включены в функцию СМЕЩ, которая описана выше. Кроме того, обратите внимание, что как в функции ПОИСКПОЗ, так и в СЧЕТЕСЛИ, есть ссылка на диапазон названный Рабочий_Список. Как я уже упоминал ранее, не обязательно использовать имена диапазонов, можно просто ввести $H3: $H15. Однако использование имен диапазонов в формуле делает ее проще и легко читаемой.

Вот и все:

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

Одна формула, ну не такая уж и простая, но облегчающая работу и защищает от ошибок при вводе данных!

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

Для этой цели существует несколько способов. Рассмотрим их более внимательно. Первым делом необходимо создать таблицу с небольшим перечнем.

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

1. Перейдите на первую пустую клетку после вашего списка.

1. Сделайте правый клик. Затем выберите указанный пункт.

1. В результате этого появится следующий список.

1. Для перехода по нему достаточно нажать на горячие клавиши Alt +↓ .

Эту комбинацию можно будет использовать всегда. В дальнейшем необязательно вызывать контекстное меню.

1. Затем для выбора можно использовать только стрелочки (↓ и ). Для того чтобы вставить нужный продукт (в нашем случае), достаточно нажать на клавишу Enter .

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

Обратите внимание на то, что этот метод не работает, если вы выберите клетку, выше которой нет никакой информации.

Стандартный

В этом случае необходимо:

1. Выделить нужные ячейки. Перейти на вкладку «Формулы». Нажать на кнопку «Определенные имена». Выбрать пункт «Диспетчер имён».

1. Затем кликнуть на «Создать».

1. Далее нужно будет указать желаемое имя (нельзя использовать символ тире или пробел). В графе диапазон произойдет автозаполнение, поскольку нужные ячейки были выделены в самом начале. Для сохранения нажмите на «OK».

1. Затем закройте это окно.

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

1. Нажмите на «Тип данных». Необходимо задать значение «Список».

1. Вследствие этого появится поле «Источник». Кликните туда.

1. Затем выделите нужные ячейки. Ранее созданное имя автоматически подставится. Для продолжения нажимаем на «OK».

1. Благодаря этим действиям вы увидите вот такой элемент.

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

Как включить режим разработчика

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

1. Нажмите на меню «Файл».

1. Перейдите в раздел «Параметры».

1. Откройте категорию «Настроить ленту». Затем поставьте галочку напротив пункта «Разработчик». Для сохранения информации кликните на «OK».

Элементы управления

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

1. Выделите свою таблицу данных. Перейдите на вкладку «Разработчик». Кликните на иконку «Вставить». Нажмите на указанный элемент.

1. Также изменится иконка указателя.

1. Выделите какой-нибудь прямоугольник. Именно таких размеров и будет ваша будущая кнопка. Её необязательно делать слишком большой. В нашем случае это только пример.

1. После этого сделайте правый клик мышкой по этому элементу. Затем выберите пункт «Формат объекта».

1. В окне «Форматирование объекта» необходимо:
   • Указать диапазон значений для формирования списка.
   • Выбрать ячейку, в которую будет выводиться результат.
   • Указать количество строк будущего списка.
   • Нажать на «OK» для сохранения.

1. Кликните на этот элемент. После этого вы увидите варианты для выбора.

1. Вследствие этого вы увидите какое-нибудь число. 1 – соответствует первому слову, а 2 – второму. То есть в этой ячейке выводится лишь порядковый номер выбранного слова.

ActiveX

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

1. Перейдите на вкладку «Разработчик». Нажмите на иконку «Вставить». На этот раз выберите другой инструмент. Он выглядит точно так же, но находится в другой группе.

1. Обратите внимание на то, что у вас включится режим конструктора. Кроме этого, изменится внешний вид указателя.

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

1. Кликните на указанную иконку.

1. Благодаря этому в правой части экрана появится окно «Properties», в котором вы сможете изменить различные настройки для выбранного элемента.

При этом значения и свойства будут меняться в зависимости от того, какой элемент будет активен в данный момент. Здесь вы сможете изменить всё, даже шрифт.

1. В поле «ListFilRange» укажите диапазон ячеек, в котором находятся ваши данные для будущего списка. Заполнение данных должно быть очень аккуратным. Достаточно указать одну неправильную букву, и вы увидите ошибку.

1. Далее необходимо кликнуть правой кнопкой мыши по созданному элементу. Выберите «Объект Combobox». Затем – «Edit».

1. Благодаря этим действиям вы увидите, что внешний вид объекта стал другим. Исчезнет возможность изменения размера.

1. Теперь вы можете спокойно выбрать что-нибудь из этого списка.

1. Для завершения необходимо отключить «Режим конструктора». После этого книга примет стандартный внешний вид.

1. Также необходимо закрыть окно свойств.

Убрать объекты ActiveX довольно просто.

1. Перейдите на вкладку «Разработчик».
2. Активируйте «Режим конструктора».

1. Кликните на этот объект.

1. Нажмите на горячую клавишу Delete .
2. И всё сразу же исчезнет.

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

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

1. Создайте какую-нибудь похожую таблицу. Главное условие – нужно добавить для каждого пункта несколько дополнительных вариантов выбора.

1. Затем выделите первую строку. Не целиком, а только возможные варианты. Вызовите контекстное меню при помощи правого клика. Выберите пункт «Присвоить имя…».

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

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

1. В этом окне необходимо выбрать пункт «Список».

1. Затем кликнуть на поле «Источник» и выбрать нужный диапазон ячеек.

1. Для сохранения используйте кнопку «OK».

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

В графе «Тип данных» снова указываем «Список». В поле источник укажите следующую формулу.

=ДВССЫЛ(B11)

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

1. Обязательно сохраните все внесенные изменения.

После нажатия на «OK» вы увидите ошибку источника данных. Ничего страшного тут нет. Кликните на «Да».

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

1. Выберите что-нибудь из предлагаемых товаров.

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

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

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

В вышеописанном методе нужно было создавать дополнительные «имена» для каждого пункта. Но можно сделать и иначе. Представьте, что у вас на листе расположено огромное количество данных. Кроме этого, эта информация может меняться, дополняться или удаляться. Как быть в этом случае? Вручную каждый раз всё настраивать заново – очень плохая затея.

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

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

1. Выберите любую клетку. Перейдите на вкладку «Данные» и нажмите на инструмент «Проверка данных».

1. Выбираем нужный тип данных. В источнике указываем необходимый диапазон. Сохраняем при помощи кнопки «OK».

1. Кликните на другую ячейку и повторите описанные ранее действия по вызову такого же окна.

1. Указываем точно такой же тип данных, но в источнике на этот раз указываем следующую формулу.

=СМЕЩ($B$1;ПОИСКПОЗ($F$6;$B:$B;0)-1;1;СЧЁТЕСЛИ($B:$B;$F$6);1)

После сохранения появится ошибка о том, что источник пустой. Нажимаем на кнопку «Да».

1. Выберите что-нибудь из предлагаемых вариантов.

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

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

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

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

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

1. Сделайте правый клик по названию листа, на котором расположена таблица и будущий выпадающий список.

1. В контекстном меню выберите пункт «Просмотреть код».

1. В появившемся окне вбейте следующий код.

Private Sub Worksheet_Change(ByVal Target As Range)

On Error Resume Next

If Not Intersect(Target, Range(«B11»)) Is Nothing And Target.Cells.Count = 1 Then

Application.EnableEvents = False

NewSelectWord = Target

Application.Undo

BeforeWord = Target

If Len(BeforeWord) <> 0 And BeforeWord <> NewSelectWord Then

Target = Target & «,» & NewSelectWord

Target = NewSelectWord

If Len(NewSelectWord) = 0 Then Target.ClearContents

Application.EnableEvents = True

1. Затем вернитесь к книге и выберите что-нибудь.

1. Повторите это действие еще раз.

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

1. Для очистки этой клетки нужно использовать клавишу Delete .

Во всех остальных случаях значения будут только накапливаться.

Заключение

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

Видеоинструкция

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

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

Электронную таблицу можно превратить в инструмент для анализа данных

Одной из самых распространённых причин для создания всплывающего списка является использование данных из ячейки в формуле Excel. Предусмотреть конечное количество вариантов проще, поэтому целесообразно будет дать выбор из нескольких значений, чтобы пользователь мог выбрать из готового набора . Кроме того, может быть ещё и другая причина: заранее заданный стиль документа. Например, для отчётов или других официальных документов. Одно и то же название отдела можно написать по-разному. Если этот документ позже будет обрабатываться машиной, более правильно будет использовать единый стиль заполнения, а не ставить перед ней задачу распознавания, к примеру, по ключевым словам. Это может внести элемент неточности в её работу.

Техническая сторона вопроса

Перед тем, как сделать выпадающий список в Excel, сформируйте на листе в диапазоне ячеек необходимые варианты. Проследите за тем, чтобы в этом перечне не было пустых строк, иначе Эксель не сможет создать нужный объект на листе. Введённые значения в строках можно отсортировать по алфавиту. Для этого найдите в Ленте настроек вкладку данные и нажмите на «Сортировку». Когда работа с данными у вас закончится, выделите нужный диапазон. В нём не должно быть пустых строк, это важно! Программа не сможет создать список с пустым элементом внутри себя, потому что пустая строка не будет восприниматься в качестве данных для выбора. При этом перечень данных вы можете сформировать и на другом листе, не только на том, где будет располагаться поле ввода. Допустим, вы не хотите, чтобы они были доступны для редактирования другим пользователям. Тогда имеет смысл расположить их на скрытом листе.

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

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

Удаление списка

Когда выпадающий список больше не нужен, его можно удалить из документа. Для этого выделите ячейку на листе Excel, содержащую его, и перейдите в Ленте настроек на вкладку «Данные» - «Проверка данных». Там во вкладке параметров нажмите на кнопку «Очистить всё». Объект будет удалён, но при этом диапазон данных останется без изменений, то есть значения не будут удалены.

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

Несколько наиболее распространенных типов выпадающих списков, которые можно создать в программе Excel:

1. С функцией мультивыбора;
2. С наполнением;
3. С добавлением новых элементов;
4. С выпадающими фото;
5. Другие типы.

Сделать список в Эксель с мультивыбором

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

Рассмотрим подробнее все основные и самые распространенные типы, и процесс их создание на практике.

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

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

Чтобы создать такой, следуйте инструкции:

• Выделите ячейки. Если посмотреть на рисунок, то выделять нужно начиная с C2 и заканчивая C5;
• Найдите вкладку «Данные», которая расположена на главной панели инструментов в окне программы. Затем нажмите на клавишу проверки данных, как показано на рисунке ниже;

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

Пр имер заполнения:

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

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

Создать список в Экселе с наполнением

Стандартный перечень с наполнением позволит вам автоматизировать процесс ввода информации. При нажатии на ее, появится выбор ее возможных значений.

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

Самый простой способ создать такой перечень – использовать «умные таблицы программы».

С их помощью можно легко и быстро форматировать необходимые вам виды списков с наполнением:

• Выделите необходимые ячейки и нажмите в главной вкладке на клавишу «Форматировать как таблицу»;

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

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

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

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

Теперь выберите ячейку, в которую вы хотите добавить раскрывающийся список, и перейдите на вкладку «Данные».

В разделе «Инструменты данных» на вкладке Данные нажмите кнопку Проверка данных .

Откроется диалоговое окно «Проверка данных». На вкладке «Параметры» выберите «Список» в раскрывающемся списке «Тип данных».

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

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

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

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

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

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

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

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

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