Выпадающая таблица в excel

Microsoft Excel: выпадающие списки

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

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

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

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

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

Открывается форма создания имени. В поле «Имя» вписываем любое удобное наименование, по которому будем узнавать данный список. Но, это наименование должно начинаться обязательно с буквы. Можно также вписать примечание, но это не обязательно. Жмем на кнопку «OK».

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

Открывается окно проверки вводимых значений. Во вкладке «Параметры» в поле «Тип данных» выбираем параметр «Список». В поле «Источник» ставим знак равно, и сразу без пробелов пишем имя списка, которое присвоили ему выше. Жмем на кнопку «OK».

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

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

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

В открывшемся окне переходим в подраздел «Настройка ленты», и ставим флажок напротив значения «Разработчик». Жмем на кнопку «OK».

После этого, на ленте появляется вкладка с названием «Разработчик», куда мы и перемещаемся. Чертим в Microsoft Excel список, который должен стать выпадающим меню. Затем, кликаем на Ленте на значок «Вставить», и среди появившихся элементов в группе «Элемент ActiveX» выбираем «Поле со списком».

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

Затем мы перемещаемся в «Режим конструктора». Жмем на кнопку «Свойства элемента управления».

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

Далее, кликаем по ячейке, и в контекстном меню последовательно переходим по пунктам «Объект ComboBox» и «Edit».

Выпадающий список в Microsoft Excel готов.

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

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

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

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

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

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

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

Как видим, список создан.

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

Всё, таблица создана.

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

Отблагодарите автора, поделитесь статьей в социальных сетях.

Выпадающая таблица в Excel

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

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

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

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

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

Читать еще: Excel таблица для учета товара

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

В той же вкладке перейдите на вкладку «Режим конструктора», нажмите кнопку «Свойства».
Откроется много параметров, но основными являются только три, которые следует настроить.
ListFillRange — здесь необходимо задать диапазон используемых значений.
ListRows — тут задается количество данных для выпадающего меню.
ColumnCount — здесь указывается количество столбцов, которые будут использованы.

С функцией мультивыбора

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

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

Напротив ячеек будет возможность мультивыбора, данные из них будут заполняться на той же строке автоматически. Чтобы функция работала верно, нужно использовать программный код, который будет автоматически добавлять значения на ту же строку. Для этого по ярлычку списка кликните правой кнопкой и выберите «Исходный код». Без этого куска на языке программирования функция добавления работать не будет. Появится окно редактора Visual Basic, вставьте следующий кусок кода:

Private Sub Worksheet_Change(ByVal Target As Range)

On Error Resume Next

If Not Intersect(Target, Range(«C2:C5«)) Is Nothing And Target.Cells.Count = 1 Then

If Len(Target.Offset(0, 1)) = 0 Then

Target.Offset(0, 1) = Target

Target.End(xlToRight).Offset(0, 1) = Target

С наполнением

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

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

Раскрывающийся список в ячейке excel

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

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

Видеоурок: как сделать выпадающий список в excel

Выпадающий список с наполнением

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

Способ 1. Если у вас Excel 2007 или новее

Простой и удобный способ почти без формул. Использует новую возможность последних версий Microsoft Excel начиная с 2007 версии — «Умные Таблицы». Суть его в том, что любой диапазон можно выделить и отформатировать как Таблицу. Тогда он превращается, упрощенно говоря, в «резиновый», то есть сам начинает отслеживать изменения своих размеров, автоматически растягиваясь-сжимаясь при добавлении-удалении в него данных.

Выделите диапазон вариантов для выпадающего списка (A1:A5 в нашем примере выше) и на Главной (Home) вкладке нажмите кнопку Форматировать как таблицу (Home — Format as Table). Дизайн можно выбрать любой — это роли не играет:

Обратите внимание на то, что таблица должна иметь строку заголовка (в нашем случае это А1 со словом Сотрудники). Первая ячейка играет роль «шапки» и содержит название столбца. На появившейся после превращения в Таблицу вкладке Конструктор (Design) можно изменить стандартное имя таблицы на свое (без пробелов!). По этому имени мы сможем потом адресоваться к таблице на любом листе этой книги:

Теперь выделите ячейки где вы хотите создать выпадающие списки (в нашем примере выше — это D2) и выберите в старых версиях Excel в меню Данные — Проверка (Data — Validation), а в новых нажмите кнопку Проверка данных (Data Validation) на вкладке Данные (Data). В открывшемся окне на вкладке Параметры (Settings) выберите вариант Список (List) и введите в поле Источник (Source) вот такую формулу:

Смысл этой формулы прост. Выражение Таблица1[Сотрудники] — это ссылка на столбец с данными для списка из нашей умной таблицы. Но проблема в том, что Excel почему-то не хочет понимать прямых ссылок в поле Источник (Source), т.е. нельзя написать в поле Источник выражение вида =Таблица1[Сотрудники]. Поэтому мы идем на тактическую хитрость — вводим ссылку как текст (в кавычках) и используем функцию ДВССЫЛ (INDIRECT), которая преобразовывает текстовую ссылку в настоящую, живую.

Читать еще: Excel vba список файлов в папке

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

Если вам лень возиться с вводом формулы ДВССЫЛ, то можно чуть упростить процесс. После создания умной таблицы просто выделите мышью диапазон с элементами для выпадающего списка (A2:A5) и введите в поле адреса имя для этого диапазона (без пробелов), например Стажеры, и нажмите на Enter:

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

Способ 2. Если у вас Excel 2003 или старше

В старых версиях Excel до 2007 года не было замечательных «умных таблиц», поэтому придется их имитировать своими силами. Это можно сделать с помощью именованного диапазона и функции СМЕЩ (OFFSET), которая умеет выдавать ссылку на динамический диапазон заданного размера.

Откройте меню Вставка — Имя — Присвоить (Insert — Name — Define) или нажмите Ctrl+F3. В открывшемся окне нажмите кнопку Добавить (New), введите имя диапазона (любое, но без пробелов и начинающееся с буквы, например — Люди) и в поле Ссылка (Reference) введите вот такую формулу:

Функция СЧЁТЗ (COUNTA) подсчитывает количество непустых ячеек в столбце с фамилиями, т.е. количество строк в диапазоне для выпадающего списка. Функция СМЕЩ (OFFSET) формирует ссылку на диапазон с нужными нам именами и использует следующие аргументы:

A2 — начальная ячейка
— сдвиг начальной ячейки по вертикали вниз на заданное количество строк
— сдвиг начальной ячейки по горизонтали вправо на заданное количество столбцов
СЧЁТЗ(A2:A100) — размер получаемого на выходе диапазона по вертикали, т.е. столько строк, сколько у нас занятых ячеек в списке
1 — размер получаемого на выходе диапазона по горизонтали, т.е. один столбец

Теперь выделите ячейки, где вы хотите создать выпадающие списки, и выберите в старых версиях Excel в меню Данные — Проверка (Data — Validation). В открывшемся окне на вкладке Параметры (Settings) выберите вариант Список (List) и введите в поле Источник (Source) вот такую формулу:

После нажатия на ОК ваш динамический список в выделенных ячейках готов к работе.

Двухуровневый выпадающий список в Excel

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

Первый способ создания двухуровнего списка

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

Теперь приступим к созданию первого выпадающего списка группы (в моем случае — список стран):

Выберите ячейку, в которую будете вставлять выпадающий список;
Переходим на вкладку ленты Данные;
Выбираем команду Проверка данных;
В выпадающем списке выбираем значение Список;
В поле Источник указываем следующую формулу =ДВССЫЛ(«Таблица1[#Заголовки]»).

Осталось создать второй зависимый выпадающий список – список подгрупп.

Смело повторяем 4 первых пункта описанных выше. Источником в окне Проверка данных для второго выпадающего списка будет служить формула =ДВССЫЛ(«Таблица1[«&F2&»]»). Ячейка F2 в данном случае — значение первого выпадающего списка.

Второй способ создания двухуровнего списка

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

ВАЖНО! Перед созданием зависимого списка по подгруппам необходимо отсортировать исходную таблицу по первому столбцу (столбец с группой) далее будет понятно зачем это делается.

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

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

Теперь самая сложная часть — указать в Источнике динамическую ссылку на диапазон со значениями второго выпадающего списка (списка подгрупп). Решать ее будем с помощью функции СМЕЩ(ссылка, смещ_по_строкам, смещ_по_столбцам, [высота], [ширина]), которая возвращает ссылку на диапазон, отстоящий от ячейки или диапазона ячеек на заданное число строк и столбцов.

Ссылка в нашем случае — $A$1 — верхний левый угол исходной таблицы;
Смещ_по_строкам — ПОИСКПОЗ(F3;$A$1:$A$67;0)-1 — номер строки со значением искомой группы (в моем случае страны ячейка F3) минус единица;
Cмещ_по_столбцам — 1 — так как нам необходим столбец с подгруппами (городами);
[Высота] — СЧЁТЕСЛИ($A$1:$A$67;F3) — количество подгрупп в искомой группе (количество городов в стране F3);
[Ширина] — 1 — так как это ширина нашего столбца с подгруппами.

Выпадающий список в Excel с помощью инструментов или макросов

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

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

Читать еще: Как убрать пустые ячейки в столбце excel

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

Путь: меню «Данные» — инструмент «Проверка данных» — вкладка «Параметры». Тип данных – «Список».

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

Вручную через «точку-с-запятой» в поле «Источник».
Ввести значения заранее. А в качестве источника указать диапазон ячеек со списком.
Назначить имя для диапазона значений и в поле источник вписать это имя.

Любой из вариантов даст такой результат.

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

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

Выделяем диапазон для выпадающего списка. В главном меню находим инструмент «Форматировать как таблицу».
Откроются стили. Выбираем любой. Для решения нашей задачи дизайн не имеет значения. Наличие заголовка (шапки) важно. В нашем примере это ячейка А1 со словом «Деревья». То есть нужно выбрать стиль таблицы со строкой заголовка. Получаем следующий вид диапазона:
Ставим курсор в ячейку, где будет находиться выпадающий список. Открываем параметры инструмента «Проверка данных» (выше описан путь). В поле «Источник» прописываем такую функцию:

Протестируем. Вот наша таблица со списком на одном листе:

Добавим в таблицу новое значение «елка».

Теперь удалим значение «береза».

Осуществить задуманное нам помогла «умная таблица», которая легка «расширяется», меняется.

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

Сформируем именованный диапазон. Путь: «Формулы» — «Диспетчер имен» — «Создать». Вводим уникальное название диапазона – ОК.
Создаем раскрывающийся список в любой ячейке. Как это сделать, уже известно. Источник – имя диапазона: =деревья.
Снимаем галочки на вкладках «Сообщение для ввода», «Сообщение об ошибке». Если этого не сделать, Excel не позволит нам вводить новые значения.
Вызываем редактор Visual Basic. Для этого щелкаем правой кнопкой мыши по названию листа и переходим по вкладке «Исходный текст». Либо одновременно нажимаем клавиши Alt + F11. Копируем код (только вставьте свои параметры).
Сохраняем, установив тип файла «с поддержкой макросов».
Переходим на лист со списком. Вкладка «Разработчик» — «Код» — «Макросы». Сочетание клавиш для быстрого вызова – Alt + F8. Выбираем нужное имя. Нажимаем «Выполнить».

Когда мы введем в пустую ячейку выпадающего списка новое наименование, появится сообщение: «Добавить введенное имя баобаб в выпадающий список?».

Нажмем «Да» и добавиться еще одна строка со значением «баобаб».

Выпадающий список в Excel с данными с другого листа/файла

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

Делаем активной ячейку, куда хотим поместить раскрывающийся список.
Открываем параметры проверки данных. В поле «Источник» вводим формулу: =ДВССЫЛ(“[Список1.xlsx]Лист1!$A$1:$A$9”).

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

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

Возьмем три именованных диапазона:

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

Создадим первый выпадающий список, куда войдут названия диапазонов.
Когда поставили курсор в поле «Источник», переходим на лист и выделяем попеременно нужные ячейки.
Теперь создадим второй раскрывающийся список. В нем должны отражаться те слова, которые соответствуют выбранному в первом списке названию. Если «Деревья», то «граб», «дуб» и т.д. Вводим в поле «Источник» функцию вида =ДВССЫЛ(E3). E3 – ячейка с именем первого диапазона.

Выбор нескольких значений из выпадающего списка Excel

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

Создаем стандартный список с помощью инструмента «Проверка данных». Добавляем в исходный код листа готовый макрос. Как это делать, описано выше. С его помощью справа от выпадающего списка будут добавляться выбранные значения.
Чтобы выбранные значения показывались снизу, вставляем другой код обработчика.
Чтобы выбираемые значения отображались в одной ячейке, разделенные любым знаком препинания, применим такой модуль.
Private Sub Worksheet_Change( ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Range( «C2:C5» )) Is Nothing And Target.Cells.Count = 1 Then
Application.EnableEvents = False
newVal = Target
Application.Undo
oldval = Target
If Len(oldval) <> 0 And oldval <> newVal Then
Target = Target & «,» & newVal
Else
Target = newVal
End If
If Len(newVal) = 0 Then Target.ClearContents
Application.EnableEvents = True
End If
End Sub

Не забываем менять диапазоны на «свои». Списки создаем классическим способом. А всю остальную работу будут делать макросы.

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

На вкладке «Разработчик» находим инструмент «Вставить» – «ActiveX». Здесь нам нужна кнопка «Поле со списком» (ориентируемся на всплывающие подсказки).
Щелкаем по значку – становится активным «Режим конструктора». Рисуем курсором (он становится «крестиком») небольшой прямоугольник – место будущего списка.
Жмем «Свойства» – открывается перечень настроек.
Вписываем диапазон в строку ListFillRange (руками). Ячейку, куда будет выводиться выбранное значение – в строку LinkedCell. Для изменения шрифта и размера – Font.

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