Одной из наиболее полезных функций проверки данных в Excel является возможность создания выпадающего списка, который позволяет выбирать значения из заранее определенного набора. Однако, при использовании этой функции часто возникает проблема: как создать связанный выпадающий список Excel так, чтобы один выпадающий список зависел от значения, выбранного в другом.
Иначе говоря, как связать выпадающие списки в Excel? Вот примеры таких задач:
- наименования продуктов, которые входят в товарную группу.
- выбрать модель автомобиля в зависимости от его марки.
Такие списки называют зависимыми или связанными, потому что их содержимое зависит друг от друга. Эти сложные конструкции можно создавать разными способами.
Зависимые выпадающие списки при помощи ДВССЫЛ
Начнем с самого простого и стандартного подхода. Предположим, что вы уже умеете создавать обычные выпадающие списки. У нас по этому вопросу есть подробное руководство: Как создать выпадающий список в Excel а также Как изменить выпадающий список в Excel.
Используем именованные диапазоны и функцию ДВССЫЛ.
Рассмотрим небольшой пример. У нас есть список автомобилей различных марок. Разместим их в отдельных столбцах. В первой ячейке каждого столбца запишем производителя — Toyota, Ford, Nissan.
После выбора нужного нам производителя, например, Toyota, должны быть показаны только модели этой марки, и ничего более. Это и есть двухуровневый зависимый выпадающий список.
Связанный выпадающий список первого уровня
Для начала создадим именованные диапазоны с моделями автомобилей. Каждому диапазону присвоим имя, соответствующее марке авто. Важно, чтобы имя каждого диапазона точно соответствовало значению в первой строке соответствующего столбца.
Например, если создаем именованный диапазон из ячеек A2:A100, то его имя должно совпадать со значением в A1 (регистр символов значения не имеет).
Выделите нужный диапазон ячеек, затем в поле «Имя» (слева от строки формул) введите название диапазона (без пробелов, только буквы и цифры).
Итак, у нас получилось 3 именованных диапазона — «toyota», «ford», «nissan». Делать их статическими (фиксированными) или динамическими (автоматически обновляемыми) — решать вам. Подробнее о создании автоматически обновляемого списка можно узнать: Как создать динамический выпадающий список в Excel.
Далее в ячейке F3 создаем выпадающий список первого уровня с моделями автомобилей, как показано на рисунке. Источник данных — первые ячейки каждого столбца. Инструмент проверки данных отлично работает как с вертикальными (по строкам), так и с горизонтальными (по столбцам) данными.
Выбираем интересующего нас производителя, например, «Ford».
Связанный выпадающий список второго уровня
Затем в ячейке F6 создадим связанный выпадающий список Excel второго уровня и выберем определенную модель для этой марки. Перечень, который мы здесь увидим, зависит от выбранной ранее марки авто.
Связать выпадающие списки нам поможет функция ДВССЫЛ. Функция ДВССЫЛ (INDIRECT в английской версии) преобразует текст в стандартную ссылку Excel. Если мы запишем:
=ДВССЫЛ("F3")это будет равноценно записи в ячейке формулы:
=F3 
Зачем такие сложности? Дело в том, что некоторые формулы Excel не воспринимаются как источник данных для выпадающего списка. Поэтому приходится использовать такие хитрости. Функция ДВССЫЛ позволяет использовать текст так же, как обычную ссылку на ячейку. Это дает два ключевых преимущества:
- Вы можете создать текстовую ссылку (записать ее в виде текстовой переменной), что удобно для динамических ссылок.
- Вы можете выбрать текстовые значения на листе и использовать их как ссылку на ячейку в формуле.
Подробно и пошагово этот процесс рассмотрен в этой статье: Выпадающий список Excel при помощи ДВССЫЛ.
Как связать выпадающие списки
В примере мы объединяем эту идею с именованными диапазонами для создания многоуровневого выпадающего списка.
ДВССЫЛ преобразует текст в имя, которое затем становится обычной ссылкой и источником данных.
В этом примере мы берем текстовые значения из A1:C1, выбираем одно из них, например, «Ford». Так как это имя соответствует одному из именованных диапазонов, применяем ДВССЫЛ, чтобы преобразовать текст «Ford» в ссылку =ford.
Используем ее как источник для связанного выпадающего списка. В качестве источника значений применяем формулу:
=ДВССЫЛ($F$3) В ячейке F3 находится текстовое выражение, которое совпадает с именем именованного диапазона с марками автомобилей. Функция возвращает в нашу таблицу ссылку: =ford
Изменяя значения в F3, мы автоматически изменяем и ссылку-источник для списка в F6. Источник данных для зависимого списка в F6 динамически меняется в зависимости от выбора в F3. Если выбираем Ford, видим только каталог машин этой марки. То же самое для Toyota или Nissan.
Можно добавить третий уровень, содержащий наименования запасных частей для выбранной ранее модели. Так формируются сложные перечни, где каждый следующий уровень зависит от предыдущего.
Важное замечание. Если название группы товаров или категории содержит пробелы, именованные диапазоны не допускают пробелов в именах. Их заменяют символом подчеркивания «_». Как быть? Названия товарных категорий с подчеркиванием будут смотреться непривычно, например, «Молочные_товары». Можно забыть ввести нужный символ, и тогда формулы не будут работать.
Выход:
- Создавайте именованные списки, заменяя пробелы подчеркиванием.
- В значениях ячеек используйте обычные пробелы.
- Перед применением в формуле обрабатывайте их с помощью функции ПОДСТАВИТЬ.
Функция ПОДСТАВИТЬ может заменить пробелы подчёркиванием, чтобы правильно указать имя диапазона.
Вместо:
=ДВССЫЛ($F$3) запишем:
=ДВССЫЛ(ПОДСТАВИТЬ($F$3;" ";"")) Если в тексте нет пробелов и он состоит из одного слова, он будет возвращен «как есть».
Следите, чтобы в начале и в конце обрабатываемого значения не было пробелов. Для этого используйте функцию СЖПРОБЕЛЫ. Она уберет начальные и конечные пробелы. Получим:
=ДВССЫЛ(ПОДСТАВИТЬ(СЖПРОБЕЛЫ($F$3);" ";"_"))Связать выпадающие списки при помощи СМЕЩ и ПОИСКПОЗ
Рассмотрим еще один способ как создать связанный выпадающий список Excel.
У нас снова есть список марок и моделей автомобилей, но теперь он оформлен немного по-другому.
Первое, что нужно сделать, это убедиться, что исходные данные отсортированы по маркам, а внутри каждой марки — по моделям. То есть, сортировка должна быть сначала по столбцу А, затем по столбцу В.
Шаг 1: Создание первого выпадающего списка
В ячейке D1 создайте выпадающий список, содержащий марки автомобилей. Для этого в ячейках F1:F3 впишите названия марок и используйте их как источник данных.
Напомню, что для создания выпадающего списка нужно зайти в Меню > Данные > Проверка данных.
Шаг 2: Создание зависимого выпадающего списка
В ячейке D2 создайте второй уровень выпадающего списка, содержащий модели выбранной марки.
Используйте функцию СМЕЩ, которая может возвращать массив данных, чтобы наполнить второй список.
Ей нужны пять параметров:
- координаты верхней левой ячейки,
- количество строк для смещения вниз (A),
- количество столбцов для смещения вправо (B),
- высота массива (количество строк) (C),
- ширина массива (количество столбцов) (D).
Подробное описание функции СМЕЩ смотрите здесь: Секреты функции СМЕЩ в Excel: от простых к продвинутым задачам.
Традиционно в качестве точки отсчета для функции СМЕЩ возьмем ячейку A1. Теперь нужно определить, на сколько строк и столбцов нужно сместиться, чтобы указать верхний левый угол нового диапазона с моделями. Предположим, что в первой ячейке выбрано значение «Ford».
Определение смещения вниз: Для этого используем функцию ПОИСКПОЗ, которая найдет первую позицию вхождения «Ford»:
=ПОИСКПОЗ($D$1; $A$1:$A$22; 0)Если «Ford» находится на 7-й позиции, то вычитаем 1, чтобы получить количество шагов вниз (6 шагов).
Смещение вправо: Установим параметр равным 1, чтобы перейти из столбца A в столбец B.
Высота массива: Посчитаем количество моделей «Ford» в списке с помощью функции СЧЁТЕСЛИ:
=СЧЁТЕСЛИ($A$1:$A$22; $D$1)Теперь объединяем все это в функцию СМЕЩ:
=СМЕЩ($A$1; ПОИСКПОЗ($D$1; $A$1:$A$22; 0) - 1; 1; СЧЁТЕСЛИ($A$1:$A$22; $D$1); 1)Последний параметр (1) означает, что массив состоит из одной колонки.
В ячейке D2 создаем выпадающий список, используя это выражение. В нем будут отображаться только модели «Ford», поскольку эта марка была выбрана ранее.
Аналогичным образом можно и на следующем уровне связать выпадающие списки в Excel.
Множественный выбор в многоуровневых связанных выпадающих списках
Если нужно, можно организовать выбор нескольких значений в одной ячейке на последнем уровне зависимых списков. Однако учтите, что это рекомендуется делать только на самом нижнем уровне, иначе вы не сможете перейти на следующий уровень.
Часто задаваемые вопросы о связанном выпадающем списке (FAQ)
Таким образом, используя комбинацию функций СМЕЩ и ПОИСКПОЗ, вы можете создавать многоуровневый связанный выпадающий список Excel, обеспечивая удобство и автоматизацию работы с данными. Если у вас есть вопросы, как связать выпадающие списки в Excel, не стесняйтесь обращаться за помощью!
Думаю эти статьи также вам будут полезны: