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

Обновлено: 02.05.2024

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

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

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

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

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

Формула ДВССЫЛ возвращает ссылку на диапазон заголовков «умной» таблицы. Преимущество использования такой таблицы в том, что при добавлении столбцов, выпадающий список будет автоматически расширен.

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

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

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

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

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

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

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

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

Рекомендация: удобно в качестве источника указывать именованный диапазон. Для его создания откройте Диспетчер имен со вкладки Формулы и присвойте имя диапазону с уникальными значениями.

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

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

Два связанных выпадающих списка с формулой массива

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

тип автомобиля: Легковой, Фургон и Внедорожник (Категория)
производитель: Fiat, Volkswagen i Suzuki (Подкатегория) и
модель: . немножечко их есть :-) (Подподкатегория)

В то же время мы имеем следующие данные:

Этот список должен быть отсортирован в следующей очередности:

Он может быть любой длины. Что еще важно: стоит добавить к нему еще два меньших списка, необходимых для Типа и Производителя, то есть к категории (первый список) и подкатегории (второй список). Эти дополнительные списки списки выглядят следующим образом:

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

Первый и второй связанный выпадающий список: Тип и Производитель

Для ячеек, которые должны стать раскрывающимися списками в меню "Данные" выбираем "Проверка данных" и как тип данных выбираем "Список".

Для Типа как источник данных мы просто указываем диапазон B7:B9.

Для Производителя мы уже используем формулу, которая подробно описана здесь. Она выглядит так:

Модель - описание для этой записи сделаем таким же самым образом.

Третий связывающий выпадающий список: Модель

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

Мы будем перемещать ячейку H4 на столько строк, пока не найдем позицию первого легкового Fiatа. Поэтому в колонке Тип мы должны иметь значение Легковой, а в колонке Производитель должен быть Fiat. Если бы мы использовали промежуточный столбец (это было бы отличным решением, но хотели бы показать вам что-то более крутое ;-), то мы бы искали комбинацию этих данных: Легковой Fiat. Однако у нас нет такого столбца, но мы можем создать его «на лету», другими словами, используя формулу. Набирая эту формулу, вы можете себе представить, что такой промежуточный столбец существует, и вы увидите, что будет проще ;-)

Для определения положения Легковой Fiat, мы, конечно, будем использовать функцию ПОИСКПОЗ. Смотрите:

Вышеописанное означает, что мы хотим знать позицию Легкового Fiatа (отсюда и связь B4&C4). Где? В нашем воображаемом вспомогательном столбце, то есть: F5:F39&G5:G39. И здесь самая большая сложность всей формулы.

Остальное уже проще, а наибольшего внимания требует функция СЧЁТЕСЛИМН, которая проверяет, сколько есть Легковых Fiatов. В частности, она проверяет, сколько раз в списке встречаются такие записи, которые в столбце F5:F39 имеют значение Легковой, а в столбце G5:G39 - Fiat. Функция выглядит так:

А вся формула для именного диапазона раскрывающегося списка это:

Если вы планируете использовать эту формулу в нескольких ячейках - не забудьте обозначить ячейки как абсолютные ссылки!

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

Создаем новое имя. Для этого выберите инструмент: «ФОРМУЛЫ»-«Определенные имена»-«Диспетчер имен»-«Создать».
При создании имени в поле «Имя:» вводим слово – модель, а в поле «Диапазон:» вводим выше указанную формулу и нажимаем на всех открытых диалоговых окнах ОК:
Перейдите на ячейку D4 чтобы там создать выпадающий список, в котором на этот раз в поле ввода «Источник:» следует указать ссылку на выше созданное имя с формулой =модель.

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

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

Отдел – Сотрудники отдела . При выборе отдела из списка всех отделов компании, динамически формируется список, содержащий перечень фамилий всех сотрудников этого отдела (двухуровневая иерархия);
Город – Улица – Номер дома . При заполнении адреса проживания можно из списка выбрать город , затем из списка всех улиц этого города – улицу , затем, из списка всех домов на этой улице – номер дома (трехуровневая иерархия).

В этой статье рассмотрен только двухуровневый связанный список . Многоуровневый связанный список рассмотрен в одноименной статье Многоуровневый связанный список . Создание иерархических структур данных позволяет избежать неудобств выпадающих списков связанных со слишком большим количеством элементов. Связанный список можно реализовать в EXCEL, с помощью инструмента Проверка данных ( Данные/ Работа с данными/ Проверка данных ) с условием проверки Список (пример создания приведен в данной статье) или с помощью элемента управления формы Список (см. статью Связанный список на основе элемента управления формы ).

Создание Связанного списка на основе Проверки данных рассмотрим на конкретном примере.

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

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

Список регионов и перечни стран разместим на листе Списки .

Обратите внимание, что названия регионов (диапазон А2:А5 на листе Списки ) в точности должны совпадать с заголовками столбцов, содержащих названия соответствующих стран ( В1:Е1 ).

Присвоим имена диапазонам, содержащим Регионы и Страны (т.е. создадим Именованные диапазоны ). Быстрее всего это сделать так:

выделитьячейки А1:Е6 на листе Списки (т.е. диапазон, охватывающий все ячейки с названиями Регионов и Стран );
нажать кнопку «Создать из выделенного фрагмента» (пункт меню Формулы/ Определенные имена/ Создать из выделенного фрагмента );
Убедиться, что стоит только галочка «В строке выше»;
Нажать ОК.

Проверить правильность имени можно через Диспетчер Имен ( Формулы/ Определенные имена/ Диспетчер имен ). Должно быть создано 5 имен.

Можно подкорректировать диапазон у имени Регионы (вместо =списки!$A$2:$A$6 установить =списки!$A$2:$A$5 , чтобы не отображалась последняя пустая строка)

На листе Таблица , для ячеек A 5: A 22 сформируем выпадающий список для выбора Региона .

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

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

Тестируем. Выбираем с помощью выпадающего списка в ячейке A 5 Регион – Америка , вызываем связанный список в ячейке B 5 и балдеем – появился список стран для Региона Америка : США, Мексика …

Теперь заполняем следующую строку. Выбираем в ячейке A 6 Регион – Азия , вызываем связанный список в ячейке B 6 и опять балдеем: Китай, Индия …

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

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

Конечно, можно вручную откорректировать диапазоны или даже вместо Именованных диапазонов создать Динамические диапазоны . Но, при большом количестве имен делать это будет достаточно трудоемко. Кроме того, при добавлении новых Регионов придется вручную создавать именованные диапазоны для их Стран .

Чтобы не создавать десятки имен, нужно изменить сам подход при построении Связанного списка . Рассмотрим этот подход в другой статье: Расширяемый Связанный список .

Потребность в создании иерархических данных появляется при решении следующих задач:

Отдел – Сотрудники отдела . При выборе отдела из списка всех отделов компании, динамически должен формироваться список, содержащий всех сотрудников этого отдела (двухуровневая иерархия);
Город – Улица – Номер дома . При заполнении адреса проживания из списка городов нужно выбирать город , затем из списка всех улиц этого города – улицу , затем, из списка всех домов на этой улице – номер дома (трехуровневая иерархия).

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

Постановка задачи

Имеется перечень Регионов . Для каждого Региона имеется свой перечень Стран . Для каждой Страны имеется свой перечень Городов .

Пользователь должен иметь возможность, выбрав определенный Регион , в соседней ячейке выбрать из Выпадающего (раскрывающегося) списка нужную ему Страну из этого Региона . В другой соседней ячейке пользователь должен иметь возможность выбрать нужный ему Город из этой Страны (см. файл примера ).

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

Сначала выберем, например, Регион «Америка» с помощью Выпадающего списка .

Затем выберем Страну «США» из Региона «Америка».

Причем перечень стран в выпадающем списке будет содержать только страны из выбранного на предыдущем шаге Региона «Америка».

И, наконец, выберем Город «Атланта» из Страны «США».

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

Решение

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

Список Регионов и перечни Стран разместим на листе Страны .

Обратите внимание, что названия Регионов (диапазон А2:А12 на листе Страны ) в точности должны совпадать с заголовками столбцов, содержащих названия соответствующих Стран ( В1: L 1 ).

Это требование обеспечивается формулой (см. статьи о Транспонировании ). =ДВССЫЛ(АДРЕС(СТРОКА($A$1)-СТОЛБЕЦ($A$1)+СТОЛБЕЦ();1))

с помощью которой формируются заголовки столбцов. Введем ее в диапазон ячеек В1: L 1 .

Список Стран и перечни Городов разместим на листе Города .

Откуда же возьмется перечень стран на листе Города ? Очевидно, что после заполнения листа Страны названиями стран, необходимо, что они каким-то чудесным образом переместились на лист Города . Это чудесное перемещение организуем формулами. Список Стран сформируем на листе Города в столбце А с помощью решения приведенного в статье Объединение списков . Значения для этого списка будем брать из Именованного диапазона Диап_Стран (его нужно предварительно создать через Диспетчер имен ) . Именованный диапазон Диап_Стран образуем формулой:

Для формирования списка Стран нам также понадобится Именованная формула Строки_Столбцы_Стран

Окончательная формула в столбце А на листе Города выглядит так:

сформирует необходимый нам список Стран .

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

нажать кнопку меню « Присвоить имя » ( Формулы/ Определенные имена/ Присвоить имя );
в поле Имя ввести Регионы ;
в поле Диапазон ввести формулу

Формула подсчитывает количество элементов в столбце А на листе Страны (функция СЧЁТЗ() ) и определяет ссылку на последний элемент в столбце (функция ИНДЕКС() ), тем самым формируется диапазон, содержащий все значения Регионов . Пропуски в столбце А не допускаются.

Аналогичным образом создадим Динамический диапазон Список_Стран для формирования выпадающего списка содержащего названия стран:

Создадим Именованную формулу Позиция_региона для определения позиции, выбранного пользователем региона, в созданном выше диапазоне Регионы:

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

Аналогичным образом создадим именованную формулу для определения позиции, выбранной пользователем страны, в диапазоне Список_Стран =ПОИСКПОЗ(таблица!B5;Список_Стран;0) . Перед созданием формулы нужно сделать активной ячейку С5 на листе Таблица .

Создадим Именованные константы МаксСтран равную 20 и МаксГородов равную 30. Константы соответствует максимальному количеству стран в регионе и, соответственно, максимальному количеству городов в стране. Эти значения произвольны и их можно изменить.

Создадим именованный диапазон Выбранный_Регион для определения диапазона на листе Страны , содержащего страны выбранного региона:

Теперь, например, при выборе региона Америка функция СМЕЩ() вернет ссылку на диапазон страны!$B$2:$B$20

Создадим аналогичный диапазон Выбранная_Страна для определения диапазона на листе Города , содержащего города выбранного региона: =СМЕЩ(города!$A$2;;Позиция_страны;МаксГородов)

Создадим две последние именованные формулы Страны и Города : =СМЕЩ(страны!$A$2;;Позиция_региона;СЧЁТЗ(Выбранный_Регион)) =СМЕЩ(города!$A$2;;Позиция_страны;СЧЁТЗ(Выбранная_Страна))

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

Также создадим связанный выпадающий список для ячеек из столбца Город (диапазон С5:С22 , в поле Источник вводим: =Города )

На листе Таблица после выбора Региона и Страны теперь есть возможность выбора Города .

Для добавления новых Регионов и их Стран достаточно ввести новый Регион в столбец A (лист Страны ), в строке 1 автоматически отобразится соответствующий заголовок. Под появившимся заголовком в строке 1 введите страны нового Региона .Для добавления новых Городов, на листе Города в строке 1 найдите нужное название страны (оно автоматически появится там после добавления страны на листе Страны ). Под этим заголовком введите название города.

СОВЕТ: В этой статье города (и страны) размещены в нескольких столбцах. Обычно однотипные значения размещают в одном столбце (списке). В статье Многоуровневый связанный список в MS EXCEL на основе таблицы все исходные данные размещены на одном листе, а однотипные данные (названия городов) - в одном столбце. Это облегчает написание формул и позволяет создать списки с большим количеством уровней иерархии (4-6).

Для сложных иерархических структур с тремя и более уровнями создадим Многоуровневый связанный список типа Предок-Родитель. Теперь структуры типа: Регион-Страна-Город-Улица можно создавать в MS EXCEL.

В статье Многоуровневый связанный список в MS EXCEL мы уже создавали многоуровневый список. Но, у того списка было 2 недостатка:

слишком сложные формулы;
максимальное количество уровней - 3.

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

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

Решение

Выпадающие списки будем формировать с помощью одной большой Именованной формулы :

Эту формулу можно посмотреть через Диспетчер имен (имя формулы - Выбор ), но так как формула использует относительную адресацию в ссылках на ячейки Таблица!B$7 и Таблица!A8, то для ее просмотра нужно предварительно выделить ячейку В8 на листе Таблица. О том как работает эта формула см. в разделе статьи ниже.

ПРИМЕЧАНИЕ : Функция ЕСЛИ() нужна для формирования списка самого верхнего уровня иерархии - у него нет Родителя.

Для ввода формулы выделите ячейку B8 , вызовите Диспетчер имен , и введите формулу:

Опять выделите ячейку B8 , сформируйте выпадающий список на основе Проверки данных . В поле Тип данных укажите Список, в поле Источник введите формулу =Выбор .

С помощью Маркера заполнения скопируйте ячейку вправо и вниз (или вверх и вправо): выпадающие списки всех уровней будут сформированы автоматически.

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

Для наглядности различные уровни выделены Условным форматированием .

Заполнение таблицы на листе Списки нужно производить следующим образом:

Столбец Номер уровня содержит числовые коды уровней. Самый верхний уровень (0) содержит название Регионов. Можно создать любое количество уровней;
Столбец Родитель содержит названия из столбца Потомок более верхнего уровня;
Столбец Потомок связывает значений из 2-х соседних уровней. Нужно иметь ввиду, что если мы начали заполнять, например, все страны Америки, то нужно полностью закончить этот список, затем переходить к следующему региону. Выполнение этого требования необходимо для правильной работы вышеуказанной формулы. Выпадающие списки формируются именно из этого столбца.

Как работает эта формула?

Рассмотрим как формируется выпадающий список в ячейке B8 на листе Таблица (заполняем Регионы).

Выражение ЕСЛИ(Таблица!B$7=Списки!$F$5 ; проверяет заполняем ли мы столбец Регионы (самый верхний уровень с номером 0) или нет. В данном случае выражение вернет значение ИСТИНА;
Так как предыдущее выражение =ИСТИНА, то работает первое условие функции ЕСЛИ() , т.е. выражение СМЕЩ(Списки!$D$4;1;0;СЧЁТЕСЛИ(Списки!$B$5:$B$30;0))
функция СМЕЩ() выбирает из столбца D (Потомки) все значения, у которых уровень равен 0. Из этих значений формируется выпадающий список.

Рассмотрим как формируется выпадающий список в ячейке С8 на листе Таблица (заполняем Страны).

В этом случае формула будет несколько другая, т.к. в ней сработают относительные ссылки:

Выражение ЕСЛИ(Таблица!С$7=Списки!$F$5 ; в данном случае выражение вернет значение ЛОЖЬ;
Так как предыдущее выражение =ЛОЖЬ, то работает второе условие функции ЕСЛИ() , т.е. выражение СМЕЩ(Списки!$D$4;ПОИСКПОЗ(Таблица!B8;Списки!$C$5:$C$30;0);0;СЧЁТЕСЛИ(Списки!$C$5:$C$30;Таблица!B8))
функция СМЕЩ() выбирает из столбца D (Потомки) все значения, у которых Родитель = Америка.
Для этого выражение ПОИСКПОЗ(Таблица!B8;Списки!$C$5:$C$30;0) возвращает номер позиции первой ячейки в столбце Родитель, содержащей значение Америка. Это позиция номер 4. Именно с этой позиции, но из другого столбца, будет формироваться наш выпадающий список ;
Теперь для выпадающего списка осталось указать его длину. Для этого используем выражение СЧЁТЕСЛИ(Списки!$C$5:$C$30;Таблица!В8) .

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

Теперь представим ситуацию, что после заполнения таблицы с помощью выпадающих списков , было изменено значение ячейки С10 . Например, вместо США выберем Мексика. Естественно, значение в ячейке D10 не изменится (в ней останется один из городов США). Чтобы подсказать пользователю об ошибке, используем Условное форматирование .

Формула в правиле Условного форматирования ищет в таблице на листе Списки пару Родитель-Потомок. Если такая пара не найдена, то ячейка выделяется красной заливкой.

Читайте также: