Графики и диаграммы (5)
Работа с VB проектом (12)
Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (64)
Разное (41)
Баги и глюки Excel (4)

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

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

Статья помогла? Поделись ссылкой с друзьями! Видеоуроки

{"Bottom bar":{"textstyle":"static","textpositionstatic":"bottom","textautohide":true,"textpositionmarginstatic":0,"textpositiondynamic":"bottomleft","textpositionmarginleft":24,"textpositionmarginright":24,"textpositionmargintop":24,"textpositionmarginbottom":24,"texteffect":"slide","texteffecteasing":"easeOutCubic","texteffectduration":600,"texteffectslidedirection":"left","texteffectslidedistance":30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":"slide","texteffectslidedirection1":"right","texteffectslidedistance1":120,"texteffecteasing1":"easeOutCubic","texteffectduration1":600,"texteffectdelay1":1000,"texteffect2":"slide","texteffectslidedirection2":"right","texteffectslidedistance2":120,"texteffecteasing2":"easeOutCubic","texteffectduration2":600,"texteffectdelay2":1500,"textcss":"display:block; padding:12px; text-align:left;","textbgcss":"display:block; position:absolute; top:0px; left:0px; width:100%; height:100%; background-color:#333333; opacity:0.6; filter:alpha(opacity=60);","titlecss":"display:block; position:relative; font:bold 14px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff;","descriptioncss":"display:block; position:relative; font:12px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff; margin-top:8px;","buttoncss":"display:block; position:relative; margin-top:8px;","texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"font-size:12px;","descriptioncssresponsive":"display:none !important;","buttoncssresponsive":"","addgooglefonts":false,"googlefonts":"","textleftrightpercentforstatic":40}}

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

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

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

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

1. Выберите ячейку, в которую будете вставлять выпадающий список;
2. Переходим на вкладку ленты Данные ;
3. Выбираем команду Проверка данных ;
4. В выпадающем списке выбираем значение Список ;
5. В поле Источник указываем следующую формулу =ДВССЫЛ("Таблица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 - так как это ширина нашего столбца с подгруппами.

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

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

Стандартный способ

Потребуется выделить диапазон ячеек , из которых будет создан выпадающий список , после чего Вставка – Имя – Присвоить (Excel 2003). В более новой версии (2007, 2010, 2013, 2016) перейдите на вкладку Формулы , где в разделе Определенные имена обнаружите кнопку Диспетчер имен .

Жмем кнопку Создать , вносите имя, можно любое, после чего ОК .

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

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

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

Используем элементы управления

Метод основан на вставке элемента управления, называемом «поле со списком «, которое будет представлять собой диапазон данных.

Выберите вкладку Разработчик (для Excel 2007/2010), в других версиях потребуется активировать эту вкладку на ленте в параметрах – Настроить ленту .

Переходим на эту вкладку – жмем кнопку Вставить . В элементах управления выбираем Поле со списком (не ActiveX) и нажмите по значку. Нарисуйте прямоугольник .

Правой кнопкой по нему – Формат объекта .

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

Применение элементов ActiveX

Все, как и в предыдущем только выбираем Поле со списком (ActiveX).

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

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

Шаг 1. Перейдите во вкладку «Данные» , которая расположена на верхней панели, затем в блоке «Работа с данными» выберите инструмент проверки данных (на скриншоте показано, какой иконкой он изображен).

Шаг 2. Теперь откройте самую первую вкладку «Параметры», и установите «Список» в перечне типа данных.

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

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

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

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

Видео — Создание выпадающих списков в Excel

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

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

1. Выделите левой кнопкой мышки диапазон для списка (в данном примере это будет перечень деревьев), затем откройте вкладку «Главная» и выберите меню «Форматировать как таблицу» .

   

2. После этого откроется меню выбора стилей. Стиль никакой роли не играет, кроме визуальной, поэтому выбирайте любой по вашему вкусу.

   

3. Далее появится окно подтверждения, цель которого – убедиться в правильности введённого диапазона. Здесь важно установить галочку возле «Таблица с заголовками» , так как наличие заголовка в данном случае играет ключевую роль.

   

4. После проделанных процедур вы получите следующий вид диапазона.

   

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

   

6. В поле ввода «Источник» вам нужно вписать функцию с синтаксисом «=ДВССЫЛ(“Имя таблицы[Заголовок]”)» . На скриншоте указан более конкретный пример.

   

Итак, список готов. Выглядеть он будет вот так.

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

Давайте протестируем это. Для начала добавим в нашу новую отформатированную таблицу новую ячейку «ёлка». Как видите, это же значение добавилось в список.

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

На заметку! В этом способе мы имели дело с так называемой «умной таблицей». Она легко расширяется, и это её свойство полезно для многих манипуляций с таблицами Excel, в том числе и для создания выпадающего списка.

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

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

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

   

   

2. Введите имя и диапазон, затем нажмите «ОК» . Если вы перед этим выделяли нужные ячейки мышкой, то диапазон будет указан автоматически. Также примите к сведению, что имя диапазона должно быть уникальным и не должно содержать знаков препинания с пробелами.

   

3. По такой же методике сделайте столько именованных диапазонов, сколько логических зависимостей хотите создать. В данном примере это ещё два диапазона: «Кустарники» и «Травы» .

   

4. Откройте вкладку «Данные» (в первом способе указан путь к ней) и укажите в источнике названия именованных диапазонов, как это показано на скриншоте.

   

   

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

   

   

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

Видео — Связанные выпадающие списки: легко и быстро

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

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

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

Подробнее о сводных таблицах читайте .

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

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

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

Теперь Вы сможете выбрать нужные пункты из раскрывающегося списка и не ошибетесь с написанием.

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

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

Задача: когда мы выбираем категорию – в списке видов расходов должны быть лишь те, которые относятся к этой категории. Решение может показаться сложным, но это не так. Я постараюсь пояснить его максимально просто.

Cтруктурируем нашу таблицу видов расходов:

Теперь виды расходов разбиты по колонкам, соответствующим каждой из категорий. Далее мы будем использовать функцию ДВССЫЛ(текст) . Что она делает? Она пытается преобразовать введенный текст в ссылку на ячейки. Что будет, если записать такую формулу: =СУММ(ДВССЫЛ(«F1:F5»)) . Функция ДВССЫЛ распознает текст «F1:F5» , как диапазон ячеек и вернет его. А функция СУММ – просуммирует все значения в этом диапазоне.

То же самое произойдет, если мы диапазону «F1:F5» присвоим имя. Например, «структура». Формула =СУММ(ДВССЫЛ(«структура»)) даст аналогичный результат. Именно этой возможностью мы и воспользуемся.

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

Дадим имена всем столбцам с исходными данными. При этом, диапазон с видами должен именоваться точно так же, как и его категория. Например, диапазону J4:J8 дадим имя «Канцелярия ». Именуем:

Теперь, если кликнуть Формулы – Определенные имена – Диспетчер имен – можно увидеть все заданные имена. Если Вы где-то ошиблись, или список изменился, в этом окне можно внести исправления.

Еще раз настраиваем проверку данных:

Если все еще не понятен принцип, опишу по пунктам, как теперь будет работать наша таблица:

• С помощью обычного списка, в столбце B выбираем категорию товаров. Например, «Питание »
• Слово «Питание » попадает, как источник данных в столбец C , т.е. в виды расходов
• У нас есть диапазон данных L4:L8 , который называется Питание . Функция ДВССЫЛ это определяет и заменяет на слово «Питание » на диапазон L4:L8
• Теперь этот диапазон будет источником для списка вида расходов

Все перечисленное подтверждаю изображением ниже:

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

Думаю, Вы разобрались, как сделать зависимый список в Excel. Если не разобрались – пишите комментарии. А я статью закончил, продуктивной Вам работы!